/**
 * Adds the exam-review workflow columns to exam_groups.
 *
 * Background: the exam module on dev lets a teacher create exam content
 * (questions, marks, duration) and lets anyone with auth create a
 * schedule, but there's no admin workflow to approve/publish/reject a
 * teacher's exam. This migration introduces the four columns needed
 * to model that workflow:
 *
 *   status           — draft / pending_review / approved / rejected
 *   rejection_reason — set when status='rejected', cleared on resubmit
 *   reviewed_at      — when an admin last took an action
 *   reviewed_by      — which admin user took the action
 *
 * Backfill: existing rows are defaulted to 'approved' so legacy
 * groups don't suddenly surface in the admin queue. This preserves
 * current behavior for everything already created.
 *
 * Idempotent: every ALTER is gated on knex.schema.hasColumn() so
 * re-running the migration against an already-migrated DB is a no-op.
 *
 * The CHECK constraint is added via raw SQL because the knex schema
 * builder does not express CHECK constraints portably. Wrapping the
 * ADD CONSTRAINT in a try-block keeps it idempotent — re-running on
 * an already-constrained column is a SQLite "duplicate column name"
 * error, which we swallow.
 */

exports.up = async function (knex) {
  // 1. status column
  if (!(await knex.schema.hasColumn('exam_groups', 'status'))) {
    await knex.schema.alterTable('exam_groups', (table) => {
      table.text('status').defaultTo('approved');
    });
    // Backfill any pre-existing rows that landed before the column existed.
    // (Empty on a fresh init; meaningful only on an existing DB.)
    await knex.schema.raw(
      "UPDATE exam_groups SET status = 'approved' WHERE status IS NULL"
    );
    // CHECK constraint — knex schema builder can't express this portably.
    // SQLite doesn't support `ADD CONSTRAINT IF NOT EXISTS`, so we wrap
    // the failure case. The migration is idempotent because the column
    // is also gated by hasColumn() above.
    try {
      await knex.schema.raw(
        "ALTER TABLE exam_groups ADD CONSTRAINT exam_groups_status_check " +
          "CHECK (status IN ('draft','pending_review','approved','rejected'))"
      );
    } catch (e) {
      // Constraint already present from a previous run — safe to ignore.
    }
  }

  // 2. rejection_reason
  if (!(await knex.schema.hasColumn('exam_groups', 'rejection_reason'))) {
    await knex.schema.alterTable('exam_groups', (table) => {
      table.text('rejection_reason').nullable();
    });
  }

  // 3. reviewed_at
  if (!(await knex.schema.hasColumn('exam_groups', 'reviewed_at'))) {
    await knex.schema.alterTable('exam_groups', (table) => {
      table.dateTime('reviewed_at').nullable();
    });
  }

  // 4. reviewed_by (FK to users.id)
  if (!(await knex.schema.hasColumn('exam_groups', 'reviewed_by'))) {
    await knex.schema.alterTable('exam_groups', (table) => {
      table.integer('reviewed_by').unsigned().nullable();
    });
  }

  console.log('[migrate:2026-07-21] exam_groups status columns ensured (or already present)');
};

exports.down = async function (knex) {
  // No-op: dropping these columns would silently lose any rejected
  // groups' reasons. If a real rollback is needed, do it manually
  // with a backfill of the rejection_reason into a notes field.
};
