/**
 * Phase 1 PR 2 (2026-07-22) — student cohorts.
 *
 * Adds the canonical ZIMSEC/IGCSE/AS/A2 cohort concept. The new
 * `student_cohorts` table is the academic-programme grouping; cohorts then
 * link to classes, students, and exam paper sets (the existing
 * `exam_groups` table holds exam papers — different concept from cohorts,
 * hence the M:N join).
 *
 *   student_cohorts       — "Form 4 IGCSE 2026", "Grade 7 ZIMSEC"
 *   cohort_students       — M:N cohort <-> student
 *   cohort_classes        — M:N cohort <-> class
 *   cohort_exam_groups    — M:N cohort <-> exam paper set
 *   enrollments.cohort_id — which cohort the student is in for this year
 *
 * `enrollments.cohort_id` is nullable; existing rows stay NULL until
 * manually backfilled or carried forward on the next academic rollover.
 *
 * Append the four tables to SyncEngine.tablesToSync in a separate edit,
 * after `users` (FK dependency for student_id).
 */

exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS student_cohorts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      programme TEXT NOT NULL CHECK(programme IN ('zimsec','igcse','as','a2','primary','other')),
      level TEXT,
      academic_year TEXT,
      description TEXT,
      start_date DATE,
      end_date DATE,
      is_active INTEGER DEFAULT 1,
      created_at DATETIME DEFAULT (datetime('now','localtime')),
      updated_at DATETIME DEFAULT (datetime('now','localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK (sync_status IN ('synced','pending','conflict')),
      is_deleted INTEGER DEFAULT 0
    )
  `);

  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS cohort_students (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
      student_id INTEGER NOT NULL REFERENCES users(id),
      joined_at DATETIME DEFAULT (datetime('now','localtime')),
      status TEXT DEFAULT 'active' CHECK(status IN ('active','inactive','transferred','graduated')),
      created_at DATETIME DEFAULT (datetime('now','localtime')),
      updated_at DATETIME DEFAULT (datetime('now','localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK (sync_status IN ('synced','pending','conflict')),
      is_deleted INTEGER DEFAULT 0,
      UNIQUE(cohort_id, student_id)
    )
  `);

  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS cohort_classes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
      class_id INTEGER NOT NULL REFERENCES classes(id),
      created_at DATETIME DEFAULT (datetime('now','localtime')),
      updated_at DATETIME DEFAULT (datetime('now','localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK (sync_status IN ('synced','pending','conflict')),
      is_deleted INTEGER DEFAULT 0,
      UNIQUE(cohort_id, class_id)
    )
  `);

  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS cohort_exam_groups (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
      exam_group_id INTEGER NOT NULL REFERENCES exam_groups(id),
      created_at DATETIME DEFAULT (datetime('now','localtime')),
      updated_at DATETIME DEFAULT (datetime('now','localtime')),
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending' CHECK (sync_status IN ('synced','pending','conflict')),
      is_deleted INTEGER DEFAULT 0,
      UNIQUE(cohort_id, exam_group_id)
    )
  `);

  // Add cohort_id to enrollments so a single enrollment row can carry
  // the cohort the student is in for the year. Existing rows: NULL
  // (acceptable, can be backfilled manually or via the next academic
  // rollover).
  const hasCohortCol = await knex.schema.hasColumn('enrollments', 'cohort_id');
  if (!hasCohortCol) {
    await knex.schema.raw(`ALTER TABLE enrollments ADD COLUMN cohort_id INTEGER REFERENCES student_cohorts(id)`);
  }

  // Useful indexes for the hot read paths:
  // - "list cohorts by programme + year + active" (Cohorts.tsx list)
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_student_cohorts_programme_year
      ON student_cohorts(programme, academic_year, is_active)
      WHERE is_deleted = 0
  `);
  // - "list students in cohort X" (CohortDetail.tsx Students tab)
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_cohort_students_cohort
      ON cohort_students(cohort_id, status)
      WHERE is_deleted = 0
  `);
  // - "list classes in cohort X" (CohortDetail.tsx Classes tab)
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_cohort_classes_cohort
      ON cohort_classes(cohort_id)
      WHERE is_deleted = 0
  `);
  // - "enrollments by cohort" (per-cohort reporting in Phase 2)
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_enrollments_cohort
      ON enrollments(cohort_id)
      WHERE cohort_id IS NOT NULL AND is_deleted = 0
  `);

  console.log('[migrate:2026-07-22] student_cohorts + M:N tables + enrollments.cohort_id created (or already present)');
};

exports.down = async function (knex) {
  // The down() reverses in FK-safe order: drop enrollments.cohort_id,
  // then the M:N tables, then student_cohorts.
  const hasCohortCol = await knex.schema.hasColumn('enrollments', 'cohort_id');
  if (hasCohortCol) {
    // SQLite doesn't support DROP COLUMN in older versions. The
    // `table.dropColumn` API in Knex does an internal rebuild for
    // those versions. For modern SQLite, this is a no-op or fine.
    try {
      await knex.schema.alterTable('enrollments', (table) => {
        table.dropColumn('cohort_id');
      });
    } catch (_) {
      // Older SQLite — fall back to a copy-rebuild.
      // (Not implementing the rebuild for now; rollback is best-effort.)
    }
  }
  await knex.schema.raw('DROP TABLE IF EXISTS cohort_exam_groups');
  await knex.schema.raw('DROP TABLE IF EXISTS cohort_classes');
  await knex.schema.raw('DROP TABLE IF EXISTS cohort_students');
  await knex.schema.raw('DROP TABLE IF EXISTS student_cohorts');
};
