migrations/phase_2_t2_education_schema.sql

-- Phase 2 T2 Education Schema Migration
-- Adds T2 education extraction fields and new tables for syllabus/PYQ analysis
-- Safe to run more than once. Mirrors startup guards in app.core.database
-- Compatible with PostgreSQL/Supabase and SQLite

-- ============================================================================
-- DOCUMENT TABLE T2 COLUMNS
-- ============================================================================

ALTER TABLE IF EXISTS documents
  ADD COLUMN IF NOT EXISTS education_extraction_status TEXT NOT NULL DEFAULT 'uploaded',
  ADD COLUMN IF NOT EXISTS education_extraction_error TEXT,
  ADD COLUMN IF NOT EXISTS education_warnings_json JSON DEFAULT '[]',
  ADD COLUMN IF NOT EXISTS syllabus_items_count INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS pyq_questions_count INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS pyq_years_json JSON DEFAULT '[]';

-- ============================================================================
-- PREVIOUS_QUESTION TABLE T2 COLUMNS  
-- ============================================================================

ALTER TABLE IF EXISTS previous_questions
  ADD COLUMN IF NOT EXISTS answer_type TEXT,
  ADD COLUMN IF NOT EXISTS formula_needed BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS diagram_needed BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS extracted_answer_if_available TEXT,
  ADD COLUMN IF NOT EXISTS confidence FLOAT NOT NULL DEFAULT 0.0,
  ADD COLUMN IF NOT EXISTS source_origin TEXT NOT NULL DEFAULT 'user_uploaded';

-- ============================================================================
-- NEW T2 TABLES
-- ============================================================================

-- Syllabus Items Table
CREATE TABLE IF NOT EXISTS syllabus_items (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    source_id TEXT,
    board TEXT NOT NULL DEFAULT '',
    class_level TEXT NOT NULL DEFAULT '',
    stream TEXT NOT NULL DEFAULT '',
    subject TEXT NOT NULL DEFAULT '',
    chapter TEXT NOT NULL DEFAULT '',
    topic TEXT NOT NULL DEFAULT '',
    subtopic TEXT NOT NULL DEFAULT '',
    formulas JSON DEFAULT '[]' NOT NULL,
    derivations JSON DEFAULT '[]' NOT NULL,
    proofs JSON DEFAULT '[]' NOT NULL,
    numerical_patterns JSON DEFAULT '[]' NOT NULL,
    diagrams JSON DEFAULT '[]' NOT NULL,
    board_keywords JSON DEFAULT '[]' NOT NULL,
    common_mistakes JSON DEFAULT '[]' NOT NULL,
    official_status TEXT NOT NULL DEFAULT 'user_uploaded',
    confidence FLOAT NOT NULL DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for syllabus_items
CREATE INDEX IF NOT EXISTS ix_syllabus_items_user_id ON syllabus_items(user_id);
CREATE INDEX IF NOT EXISTS ix_syllabus_items_source_id ON syllabus_items(source_id);
CREATE INDEX IF NOT EXISTS ix_syllabus_items_subject_chapter ON syllabus_items(subject, chapter);

-- Chapter Patterns Table
CREATE TABLE IF NOT EXISTS chapter_patterns (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    board TEXT NOT NULL DEFAULT '',
    class_level TEXT NOT NULL DEFAULT '',
    subject TEXT NOT NULL DEFAULT '',
    chapter TEXT NOT NULL DEFAULT '',
    total_questions INTEGER NOT NULL DEFAULT 0,
    total_marks INTEGER NOT NULL DEFAULT 0,
    repeated_topics JSON DEFAULT '[]' NOT NULL,
    answer_type_distribution JSON DEFAULT '{}' NOT NULL,
    years_seen JSON DEFAULT '[]' NOT NULL,
    confidence FLOAT NOT NULL DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for chapter_patterns
CREATE INDEX IF NOT EXISTS ix_chapter_patterns_user_id ON chapter_patterns(user_id);
CREATE INDEX IF NOT EXISTS ix_chapter_patterns_subject_chapter ON chapter_patterns(subject, chapter);
CREATE INDEX IF NOT EXISTS ix_chapter_patterns_board_class_subject ON chapter_patterns(board, class_level, subject);

-- ============================================================================
-- POSTGRESQL-SPECIFIC UPDATES (run only on Postgres/Supabase)
-- ============================================================================
-- Note: The migration above uses SQLite-compatible syntax. 
-- For PostgreSQL/Supabase, also run these specific updates:

-- PostgreSQL comment syntax (ignored by SQLite)
COMMENT ON COLUMN documents.education_extraction_status IS 'T2: Status of education extraction (uploaded, processing, completed, failed)';
COMMENT ON COLUMN documents.education_extraction_error IS 'T2: Error message if extraction failed';
COMMENT ON COLUMN documents.education_warnings_json IS 'T2: JSON array of extraction warnings';
COMMENT ON COLUMN documents.syllabus_items_count IS 'T2: Number of syllabus items extracted from this document';
COMMENT ON COLUMN documents.pyq_questions_count IS 'T2: Number of PYQ questions extracted from this document';
COMMENT ON COLUMN documents.pyq_years_json IS 'T2: JSON array of years found in PYQ extraction';

COMMENT ON COLUMN previous_questions.answer_type IS 'T2: Type of answer (derivation, numerical, proof, etc.)';
COMMENT ON COLUMN previous_questions.formula_needed IS 'T2: Whether this question requires formula derivation';
COMMENT ON COLUMN previous_questions.diagram_needed IS 'T2: Whether this question requires diagram drawing';
COMMENT ON COLUMN previous_questions.extracted_answer_if_available IS 'T2: Extracted answer key if available';
COMMENT ON COLUMN previous_questions.confidence IS 'T2: Confidence score for extraction (0.0 to 1.0)';
COMMENT ON COLUMN previous_questions.source_origin IS 'T2: Origin of this question (user_uploaded, catalog_seed, etc.)';

COMMENT ON TABLE syllabus_items IS 'T2: Extracted syllabus items from uploaded syllabus documents';
COMMENT ON TABLE chapter_patterns IS 'T2: Aggregated PYQ patterns per chapter for trend analysis';

-- ============================================================================
-- MIGRATION NOTES
-- ============================================================================
-- This migration is idempotent and safe to run multiple times.
-- It mirrors the startup guard logic in app.core.database init_db().
-- For production Supabase deployment:
-- 1. Run this migration via Supabase SQL Editor or migration tool
-- 2. Verify tables created with: SELECT * FROM information_schema.tables WHERE table_name IN ('syllabus_items', 'chapter_patterns');
-- 3. Verify columns added with: SELECT column_name FROM information_schema.columns WHERE table_name = 'documents' AND column_name LIKE 'education_%';
-- 4. The application startup guards will also apply these changes automatically as fallback.