/**
 * Adds the education_level_configs table.
 *
 * Originally introduced on the feature/shapeshift branch (schoolSettings
 * controller + SyncEngine.tablesToSync), but the migration that should
 * have accompanied the controller's first commit was missed. The
 * SyncEngine performs a strict "table exists" check on startup and
 * throws if the table is missing, so a fresh `npm run db:init` left the
 * server unable to boot — including for `verify-restore.sh`, which
 * spins the server up against a snapshot.
 *
 * Idempotent: CREATE TABLE IF NOT EXISTS is a no-op on already-migrated
 * DBs. Seeding is INSERT OR IGNORE so re-running is safe.
 *
 * Schema columns are inferred from the controller code:
 *   - schoolSettings.controller.js INSERTs use
 *     (uid, level, grading_scale, display_name, sort_order, is_active[, is_default])
 *   - dashboard.controller.js SELECTs `level`
 *   - All sync-aware columns (uid, last_synced_at, sync_status,
 *     is_deleted) follow the project's SQL contract per .harness/AGENTS.md.
 *
 * Default seeds mirror the canonical Primary / Secondary / High School /
 * Tertiary list the controller exposes via /education-levels, with
 * Secondary flagged as the default (matches the existing
 * DEFAULT_EDUCATION_LEVEL constant in schoolSettings.controller.js).
 */

exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS education_level_configs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      level TEXT NOT NULL CHECK(level IN ('primary','secondary','high_school','tertiary')),
      grading_scale TEXT,
      display_name TEXT,
      sort_order INTEGER DEFAULT 0,
      is_active INTEGER DEFAULT 1,
      is_default INTEGER DEFAULT 0,
      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
    )
  `);

  // Idempotent default seeds so first-boot schools have a usable default.
  await knex.schema.raw(`
    INSERT OR IGNORE INTO education_level_configs
      (uid, level, grading_scale, display_name, sort_order, is_active, is_default)
    VALUES
      ('uid-elc-primary',    'primary',    'zimsec_primary',    'Primary',                10, 1, 0),
      ('uid-elc-secondary',  'secondary',  'zimsec_olevel',     'Secondary (O-Level)',    20, 1, 1),
      ('uid-elc-highschool', 'high_school','zimsec_alevel',     'High School (A-Level)',  30, 1, 0),
      ('uid-elc-tertiary',   'tertiary',   'cambridge_alevel',  'Tertiary',               40, 1, 0)
  `);
  console.log('[migrate:2026-07-18] education_level_configs created (or already present)');
};

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