const Database = require('better-sqlite3');
const path = require('path');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

console.log('Running migration: Adding Teacher Tools and Student Social Documents...');

db.exec(`
  -- =============================================
  -- SYLLABUS TABLE
  -- =============================================
  CREATE TABLE IF NOT EXISTS syllabuses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    subject_id INTEGER NOT NULL REFERENCES subjects_new(id),
    title TEXT NOT NULL,
    description TEXT,
    file_path TEXT,
    version TEXT,
    is_active INTEGER DEFAULT 1,
    created_by INTEGER REFERENCES users(id),
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- =============================================
  -- SCHEMES OF WORK (SCHEMAS)
  -- =============================================
  CREATE TABLE IF NOT EXISTS schemes_of_work (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    subject_id INTEGER NOT NULL REFERENCES subjects_new(id),
    teacher_id INTEGER NOT NULL REFERENCES users(id),
    term TEXT,
    academic_year TEXT,
    content TEXT, -- JSON structure for weeks/topics
    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'submitted', 'approved', 'rejected')),
    approved_by INTEGER REFERENCES users(id),
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- =============================================
  -- FOCUS POINTS
  -- =============================================
  CREATE TABLE IF NOT EXISTS focus_points (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    scheme_id INTEGER NOT NULL REFERENCES schemes_of_work(id),
    topic TEXT NOT NULL,
    objectives TEXT,
    key_concepts TEXT,
    resources_needed TEXT,
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- =============================================
  -- LESSON PLANS
  -- =============================================
  CREATE TABLE IF NOT EXISTS lesson_plans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    teacher_id INTEGER NOT NULL REFERENCES users(id),
    subject_id INTEGER NOT NULL REFERENCES subjects_new(id),
    scheme_id INTEGER REFERENCES schemes_of_work(id),
    focus_point_id INTEGER REFERENCES focus_points(id),
    date DATE,
    topic TEXT NOT NULL,
    objectives TEXT,
    introduction TEXT,
    development TEXT,
    conclusion TEXT,
    assessment_method TEXT,
    homework TEXT,
    reflections TEXT,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'cancelled')),
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- =============================================
  -- STUDENT SOCIAL DOCUMENTS
  -- =============================================
  CREATE TABLE IF NOT EXISTS student_social_docs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    student_id INTEGER NOT NULL REFERENCES users(id),
    learner_type TEXT, -- visual, auditory, kinesthetic, etc.
    past_behavior TEXT,
    family_size INTEGER,
    parents_available TEXT, -- both, father, mother, guardian, none
    home_environment TEXT,
    medical_notes TEXT,
    social_strengths TEXT,
    social_challenges TEXT,
    interests TEXT,
    other_data TEXT, -- JSON for additional flexible data
    updated_by INTEGER REFERENCES users(id),
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- Indexes for performance
  CREATE INDEX IF NOT EXISTS idx_syllabuses_subject ON syllabuses(subject_id);
  CREATE INDEX IF NOT EXISTS idx_schemes_subject ON schemes_of_work(subject_id);
  CREATE INDEX IF NOT EXISTS idx_schemes_teacher ON schemes_of_work(teacher_id);
  CREATE INDEX IF NOT EXISTS idx_lesson_plans_teacher ON lesson_plans(teacher_id);
  CREATE INDEX IF NOT EXISTS idx_lesson_plans_date ON lesson_plans(date);
  CREATE INDEX IF NOT EXISTS idx_social_docs_student ON student_social_docs(student_id);
`);

console.log('Migration complete!');
process.exit(0);
