/**
 * migration_assignments_fk.js
 *
 * Repoints assignments.course_id from REFERENCES courses(id) to
 * REFERENCES subjects(id).
 *
 * Why:
 *   feature/teacher-assignments flipped the assignments controller's JOIN
 *   target from courses to subjects and the teacher page now POSTs
 *   subjects.id values (>= 4) that don't exist in courses. init.js still
 *   declared REFERENCES courses(id), so any teacher whose owned subject
 *   has subjects.id >= 4 hit a 500 at INSERT time (FK rejection at the DB
 *   layer). Teachers with subjects.id >= 4 in the subjects seed are
 *   affected (covers teachers 4 and 5 in the subjects_new seed and every
 *   teacher owning subjects 4-17 in the subjects seed).
 *
 * Direction chosen: A. Repoint FK to subjects(id). The 14 existing rows
 * all have course_id in {1,2,3}, which is present in BOTH subjects.id and
 * courses.id, so the rebuild is lossless. Direction B (back the
 * controller to courses) leaves the page picker empty because
 * courses.teacher_id is null for all three seed rows, so it is not
 * viable.
 *
 * Status column:
 *   The incoming controller writes a `status` column on assignments
 *   ('draft' | 'published' | 'closed') but init.js never declared one,
 *   and the live DB doesn't have one either. The fresh-install block in
 *   init.js is updated to declare it. For existing rows we backfill
 *   status from is_published (1 -> 'published', else -> 'draft') so the
 *   status tabs on the teacher page continue to show them.
 *
 * Strategy:
 *   SQLite cannot ALTER TABLE ... DROP CONSTRAINT or change FK targets.
 *   The sibling migration_roles_hierarchy.js uses
 *   RENAME -> CREATE -> INSERT -> DROP, but SQLite's RENAME rewrites
 *   inbound FK references in referencing tables (e.g.
 *   submissions.assignment_id ends up pointing at assignments_old),
 *   which then blocks DROP TABLE assignments_old with FOREIGN KEY
 *   constraint failed. So this migration keeps foreign_keys = ON for
 *   the prep work (pre-flight FK check + idempotency check), turns
 *   foreign_keys = OFF for the rebuild itself, then re-enables it for
 *   the post-flight FK check. References in submissions stay pointing
 *   at the literal name "assignments" the whole time and snap back into
 *   effect when foreign_keys is re-enabled. PRAGMA foreign_key_check
 *   after re-enabling validates that no orphans were introduced.
 *
 * Backup (run BEFORE this script, manually):
 *   cp server/data/school.db server/data/school.db.bak-pre-assignments-fk
 *   (on Windows:
 *    copy server\data\school.db server\data\school.db.bak-pre-assignments-fk)
 *
 * Rollback (manual):
 *   1. Stop the API.
 *   2. Restore the backup over the live DB.
 *   3. Restart the API.
 *   The schema is altered in place; do not attempt to roll forward past
 *   this point without re-running the migration first.
 *
 * Idempotence: re-running is safe. The script uses
 * PRAGMA foreign_key_list(assignments) to confirm the existing FK on
 * column course_id already references subjects(id), and bails out
 * cleanly otherwise. It also drops a leftover `assignments_new` from a
 * half-completed prior run before starting.
 *
 * Invocation (manual; not wired into npm scripts):
 *   cd server && node src/database/migration_assignments_fk.js
 */

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');

const preOrphans = db.pragma('foreign_key_check');
if (preOrphans.length > 0) {
  console.error('Aborting: assignments has foreign-key orphans before migration. Backup and investigate first.');
  console.error(JSON.stringify(preOrphans, null, 2));
  db.close();
  process.exit(1);
}

const courseIdFk = db.prepare('PRAGMA foreign_key_list(assignments)').all()
  .find((fk) => fk.from === 'course_id');
const alreadySubjectsFK = courseIdFk && courseIdFk.table === 'subjects' && courseIdFk.to === 'id';

if (alreadySubjectsFK) {
  console.log('assignments.course_id already references subjects(id). Nothing to do.');
  db.close();
  process.exit(0);
}

db.pragma('foreign_keys = OFF');

const cleanupLeftover = db.prepare(
  "SELECT name FROM sqlite_master WHERE name='assignments_new'"
).get();
if (cleanupLeftover) {
  db.exec('DROP TABLE assignments_new');
  console.log('Dropped leftover assignments_new from a prior half-completed run.');
}

console.log('Repointing assignments.course_id FK courses(id) -> subjects(id) ...');

try {
  db.transaction(() => {
    db.exec(`
      CREATE TABLE assignments_new (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE,
        course_id INTEGER NOT NULL REFERENCES subjects(id),
        title TEXT NOT NULL,
        description TEXT,
        instructions TEXT,
        due_date DATETIME,
        max_score REAL DEFAULT 100,
        submission_type TEXT DEFAULT 'file' CHECK(submission_type IN ('file', 'text', 'online', 'offline')),
        allow_late_submission INTEGER DEFAULT 0,
        is_published INTEGER DEFAULT 0,
        status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'published', 'closed')),
        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
      )
    `);

    db.exec(`
      INSERT INTO assignments_new (
        id, uid, course_id, title, description, instructions, due_date, max_score,
        submission_type, allow_late_submission, is_published,
        created_at, updated_at, last_synced_at, sync_status, is_deleted
      )
      SELECT
        id, uid, course_id, title, description, instructions, due_date, max_score,
        submission_type, allow_late_submission, is_published,
        created_at, updated_at, last_synced_at, sync_status, is_deleted
      FROM assignments
    `);

    db.exec(`
      UPDATE assignments_new
      SET status = CASE WHEN is_published = 1 THEN 'published' ELSE 'draft' END
    `);

    db.exec('DROP TABLE assignments');

    db.exec('ALTER TABLE assignments_new RENAME TO assignments');

    db.exec('CREATE INDEX IF NOT EXISTS idx_assignments_sync ON assignments(sync_status, is_deleted)');
    db.exec('CREATE INDEX IF NOT EXISTS idx_assignments_course ON assignments(course_id)');
    db.exec('CREATE INDEX IF NOT EXISTS idx_assignments_due_date ON assignments(due_date)');
    db.exec('CREATE INDEX IF NOT EXISTS idx_assignments_due_date_deleted ON assignments(due_date, is_deleted)');
  })();

  db.pragma('foreign_keys = ON');

  const postOrphans = db.pragma('foreign_key_check');
  if (postOrphans.length > 0) {
    console.error('Migration produced orphans:', JSON.stringify(postOrphans, null, 2));
    db.close();
    process.exit(1);
  }

  const count = db.prepare('SELECT COUNT(*) c FROM assignments').get().c;
  const finalCourseIdFk = db.prepare('PRAGMA foreign_key_list(assignments)').all()
    .find((fk) => fk.from === 'course_id');
  const fkOk = finalCourseIdFk && finalCourseIdFk.table === 'subjects' && finalCourseIdFk.to === 'id';

  console.log(`Migration complete. assignments row count: ${count}; course_id FK -> subjects(id): ${fkOk ? 'yes' : 'NO'}`);
} catch (error) {
  console.error('Migration failed:', error);
  db.close();
  process.exit(1);
}

db.close();
process.exit(0);
