/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.954Z
 *
 * Track D (P1-9) — Knex migration for the Settings concern.
 *
 * Each CREATE TABLE statement is the original SQL from
 * server/src/database/init.js, executed via knex.schema.raw() to
 * preserve CHECK constraints, FTS5 virtual tables, triggers, and
 * other constructs the Knex DSL cannot easily express.
 *
 * Future PRs may convert individual tables to knex.schema.createTable()
 * with the schema-builder DSL.
 */

exports.up = async function(knex) {
  // system_settings
  await knex.schema.raw("-- =============================================\r\n  -- SYSTEM SETTINGS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS system_settings (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    key TEXT UNIQUE NOT NULL,\r\n    value TEXT,\r\n    description TEXT,\r\n    category TEXT DEFAULT 'general',\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // settings
  await knex.schema.raw("-- =============================================\r\n  -- SETTINGS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS settings (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    key TEXT UNIQUE NOT NULL,\r\n    value TEXT,\r\n    description TEXT,\r\n    category TEXT DEFAULT 'general',\r\n    is_encrypted INTEGER DEFAULT 0,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // school_settings
  await knex.schema.raw("-- =============================================\r\n  -- SCHOOL-WIDE SETTINGS (k/v with stable uid)\r\n  -- =============================================\r\n  -- Settings the school_admin / systems_admin can change. Distinct from\r\n  -- system_settings (which holds sync / Paynow / system-wide credentials)\r\n  -- because we want a lighter-weight surface with a stable uid for sync.\r\n  -- Today this table holds the default grading scale; tomorrow it can absorb\r\n  -- school_name, term labels, etc., as the team moves them out of legacy\r\n  -- system_settings.\r\n  -- See server/src/utils/gradeScales.js for the resolver and\r\n  -- server/src/controllers/schoolSettings.controller.js for the API.\r\n  CREATE TABLE IF NOT EXISTS school_settings (\r\n    key TEXT PRIMARY KEY,\r\n    uid TEXT UNIQUE,\r\n    value TEXT,\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced', 'pending', 'conflict')),\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "school_settings" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "settings" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "system_settings" + ';');
};
