/**
 * Adds the exam_review_audit table.
 *
 * One row per action on the exam-review workflow. The shape is:
 *
 *   id               INTEGER PK
 *   uid              TEXT UNIQUE           — sync-friendly surrogate
 *   exam_group_id    INTEGER FK -> exam_groups(id) ON DELETE CASCADE
 *   schedule_id      INTEGER FK -> exam_schedules(id) ON DELETE SET NULL
 *   action           TEXT  CHECK in (
 *                       'submit',          — teacher pushed to pending_review
 *                       'reject',          — admin rejected with reason
 *                       'publish',         — admin published (approved group)
 *                     )
 *   actor_id         INTEGER FK -> users(id)  — who performed the action
 *   reason           TEXT                   — only set for 'reject'
 *   metadata         TEXT                   — JSON blob for future use
 *   created_at       DATETIME               — when the action happened
 *   last_synced_at   DATETIME
 *   sync_status      TEXT DEFAULT 'pending'
 *   is_deleted       INTEGER DEFAULT 0
 *
 * Idempotent: CREATE TABLE IF NOT EXISTS is a no-op on re-runs.
 * Append the table to SyncEngine.tablesToSync separately if you
 * need it pushed to Supabase.
 */

exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS exam_review_audit (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      exam_group_id INTEGER NOT NULL REFERENCES exam_groups(id) ON DELETE CASCADE,
      schedule_id INTEGER REFERENCES exam_schedules(id) ON DELETE SET NULL,
      action TEXT NOT NULL CHECK (action IN ('submit','reject','publish')),
      actor_id INTEGER NOT NULL REFERENCES users(id),
      reason TEXT,
      metadata TEXT,
      created_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 INDEX IF NOT EXISTS idx_exam_review_audit_group
      ON exam_review_audit(exam_group_id, created_at DESC)
  `);
  console.log('[migrate:2026-07-21] exam_review_audit created (or already present)');
};

exports.down = async function (knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS exam_review_audit');
};
