Gauntlet-Incept MCP

-- Create schema for the CCC database CREATE SCHEMA IF NOT EXISTS ccc; -- Create tables for QTI content CREATE TABLE IF NOT EXISTS ccc.questions ( id SERIAL PRIMARY KEY, qti_id VARCHAR(255) NOT NULL UNIQUE, content TEXT NOT NULL, subject VARCHAR(50) NOT NULL, grade VARCHAR(10) NOT NULL, standard VARCHAR(50) NOT NULL, lesson VARCHAR(255) NOT NULL, difficulty INTEGER NOT NULL, source VARCHAR(50), is_example BOOLEAN DEFAULT FALSE, expected_result VARCHAR(10), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ccc.articles ( id SERIAL PRIMARY KEY, qti_id VARCHAR(255) NOT NULL UNIQUE, content TEXT NOT NULL, subject VARCHAR(50) NOT NULL, grade VARCHAR(10) NOT NULL, standard VARCHAR(50) NOT NULL, lesson VARCHAR(255) NOT NULL, source VARCHAR(50), is_example BOOLEAN DEFAULT FALSE, expected_result VARCHAR(10), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ccc.courses ( id SERIAL PRIMARY KEY, qti_id VARCHAR(255) NOT NULL UNIQUE, title VARCHAR(255) NOT NULL, description TEXT, subject VARCHAR(50) NOT NULL, grade VARCHAR(10) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ccc.lessons ( id SERIAL PRIMARY KEY, qti_id VARCHAR(255) NOT NULL UNIQUE, course_id INTEGER REFERENCES ccc.courses(id) ON DELETE CASCADE, title VARCHAR(255) NOT NULL, description TEXT, standard VARCHAR(50) NOT NULL, sequence_number INTEGER NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ccc.lesson_articles ( id SERIAL PRIMARY KEY, lesson_id INTEGER REFERENCES ccc.lessons(id) ON DELETE CASCADE, article_id INTEGER REFERENCES ccc.articles(id) ON DELETE CASCADE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS ccc.lesson_questions ( id SERIAL PRIMARY KEY, lesson_id INTEGER REFERENCES ccc.lessons(id) ON DELETE CASCADE, question_id INTEGER REFERENCES ccc.questions(id) ON DELETE CASCADE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); -- Create indexes for performance CREATE INDEX IF NOT EXISTS idx_questions_subject_grade ON ccc.questions(subject, grade); CREATE INDEX IF NOT EXISTS idx_questions_standard ON ccc.questions(standard); CREATE INDEX IF NOT EXISTS idx_questions_lesson ON ccc.questions(lesson); CREATE INDEX IF NOT EXISTS idx_questions_difficulty ON ccc.questions(difficulty); CREATE INDEX IF NOT EXISTS idx_questions_is_example ON ccc.questions(is_example); CREATE INDEX IF NOT EXISTS idx_articles_subject_grade ON ccc.articles(subject, grade); CREATE INDEX IF NOT EXISTS idx_articles_standard ON ccc.articles(standard); CREATE INDEX IF NOT EXISTS idx_articles_lesson ON ccc.articles(lesson); CREATE INDEX IF NOT EXISTS idx_articles_is_example ON ccc.articles(is_example); CREATE INDEX IF NOT EXISTS idx_courses_subject_grade ON ccc.courses(subject, grade); -- Create function to update timestamp CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Create triggers to update timestamp CREATE TRIGGER update_questions_timestamp BEFORE UPDATE ON ccc.questions FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_articles_timestamp BEFORE UPDATE ON ccc.articles FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_courses_timestamp BEFORE UPDATE ON ccc.courses FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_lessons_timestamp BEFORE UPDATE ON ccc.lessons FOR EACH ROW EXECUTE FUNCTION update_timestamp();