/**
 * Adds the notifications table for in-app + queued email/SMS stubs.
 *
 * Scope (2026-07-21): the exam-review workflow fires a 'reject'
 * notification to the submitting teacher. There is no real
 * email/SMS provider wired in the repo — this table is the durable
 * record + an inbox the teacher can read in-app. When the school
 * plugs in a real provider, the NotificationService will dispatch
 * to the provider AND write a row here with status='sent'.
 *
 * Schema:
 *   id               INTEGER PK
 *   uid              TEXT UNIQUE
 *   recipient_id     INTEGER FK -> users(id)  — who to notify
 *   type             TEXT  CHECK in ('in_app','email','sms')
 *   subject          TEXT                  — short title
 *   body             TEXT                  — full message
 *   related_resource TEXT                  — e.g. 'exam_group:5'
 *                                          (no FK — keep the table
 *                                          generic for future use)
 *   status           TEXT  CHECK in ('pending','sent','failed','read')
 *                    DEFAULT 'pending'
 *   sent_at          DATETIME nullable
 *   read_at          DATETIME nullable
 *   error_message    TEXT nullable         — for failed sends
 *   metadata         TEXT                  — JSON blob for future use
 *   created_at       DATETIME DEFAULT now
 *   last_synced_at   DATETIME
 *   sync_status      TEXT DEFAULT 'pending'
 *   is_deleted       INTEGER DEFAULT 0
 *
 * Append the table to SyncEngine.tablesToSync in a separate edit.
 */

exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS notifications (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      recipient_id INTEGER NOT NULL REFERENCES users(id),
      type TEXT NOT NULL CHECK (type IN ('in_app','email','sms')),
      subject TEXT,
      body TEXT,
      related_resource TEXT,
      status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','sent','failed','read')),
      sent_at DATETIME,
      read_at DATETIME,
      error_message 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_notifications_recipient_unread
      ON notifications(recipient_id, status, created_at DESC)
      WHERE is_deleted = 0
  `);
  console.log('[migrate:2026-07-21] notifications created (or already present)');
};

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