/**
 * user_roles — auxiliary (or scoped) roles beyond users.role.
 *
 * Phase 1 PR 1 (2026-07-22) — auxiliary roles thin slice.
 *
 * Three usage patterns, all on one table:
 *   1. App-wide auxiliary role. scope_* = NULL. Example: "Mrs Moyo is also a librarian".
 *      Behaves as if users.role includes the auxiliary role.
 *   2. Time-scoped cover. scope_class_id = X, expires_at = future. Example: "Sports coach
 *      covers Form 3A until 2026-07-29 because the regular teacher is on sick leave".
 *      The RBAC helper filters by expires_at > now so the grant auto-expires without
 *      a cron; a daily cleanup prunes old rows for hygiene.
 *   3. Resource-scoped. scope_subject_id / scope_cohort_id. Future-proofs the table
 *      for "this teacher is also the cohort leader of X" without a schema change.
 *
 * users.role stays as the primary role. The JWT carries an `effective_roles` array
 * (primary + non-revoked, non-expired user_roles) so route gating can check the
 * effective set without a per-request DB hit.
 *
 * Schema (in addition to the standard sync columns):
 *   id                  INTEGER PK
 *   uid                 TEXT UNIQUE
 *   user_id             INTEGER FK -> users(id)
 *   role                TEXT  CHECK in (the 19 supported roles)
 *   scope_class_id      INTEGER NULL FK -> classes(id)
 *   scope_subject_id    INTEGER NULL FK -> subjects(id)
 *   scope_cohort_id     INTEGER NULL FK -> student_cohorts(id) — FK is added
 *                                                  later in the cohorts migration;
 *                                                  declared here so the column
 *                                                  doesn't move.
 *   starts_at           DATETIME NULL
 *   expires_at          DATETIME NULL
 *   reason              TEXT
 *   granted_by          INTEGER NULL FK -> users(id)
 *   granted_at          DATETIME DEFAULT now
 *   revoked_by          INTEGER NULL FK -> users(id)
 *   revoked_at          DATETIME NULL
 *   revoke_reason       TEXT
 *   (standard sync columns)
 *
 * Indexes:
 *   - one covering index for the active-grant lookup used by rbac.getEffectiveRoles
 *   - one partial unique index ensuring one live grant per (user, role, scope) tuple
 *
 * Append the table to SyncEngine.tablesToSync in a separate edit (after `users`).
 */

exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS user_roles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      user_id INTEGER NOT NULL REFERENCES users(id),
      role TEXT NOT NULL CHECK(role IN (
        'systems_admin','school_admin','teacher','student','parent','accountant',
        'librarian','nurse','clubs_head','principal','hr','bursar','dining_staff',
        'driver','groundsman','matron','boarding_master','security','janitor'
      )),
      scope_class_id INTEGER REFERENCES classes(id),
      scope_subject_id INTEGER REFERENCES subjects(id),
      scope_cohort_id INTEGER,
      starts_at DATETIME,
      expires_at DATETIME,
      reason TEXT,
      granted_by INTEGER REFERENCES users(id),
      granted_at DATETIME DEFAULT (datetime('now','localtime')),
      revoked_by INTEGER REFERENCES users(id),
      revoked_at DATETIME,
      revoke_reason TEXT,
      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
    )
  `);

  // Covering index for the hot path: "what active roles does user X have right now?"
  // rbac.getEffectiveRoles runs this query on every login and every refresh-roles call.
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_user_roles_active_lookup
      ON user_roles(user_id, role, expires_at)
      WHERE is_deleted = 0 AND revoked_at IS NULL
  `);

  // Scope lookup for the cover-teacher case: "who is currently covering class X?"
  await knex.schema.raw(`
    CREATE INDEX IF NOT EXISTS idx_user_roles_class_scope
      ON user_roles(scope_class_id, role, expires_at)
      WHERE is_deleted = 0 AND revoked_at IS NULL
  `);

  // Partial unique index: one live grant per (user, role, scope) tuple.
  // NULL scopes are coalesced to 0 so unscoped grants also dedupe correctly.
  // (SQLite treats NULLs as distinct, so COALESCE is required to make this work.)
  await knex.schema.raw(`
    CREATE UNIQUE INDEX IF NOT EXISTS idx_user_roles_active_unique
      ON user_roles(user_id, role,
        COALESCE(scope_class_id, 0),
        COALESCE(scope_subject_id, 0),
        COALESCE(scope_cohort_id, 0))
      WHERE revoked_at IS NULL AND is_deleted = 0
  `);

  console.log('[migrate:2026-07-22] user_roles created (or already present)');
};

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