/**
 * 2026-07-29 — Timetable module (FR-SMS1 timetable sub-feature, UAT
 * TC-SMS-008/009/010).
 *
 * Two new tables:
 *   - timetable_periods  — school-day period definitions (e.g. Period 1:
 *                          08:00-08:40, Morning Break 10:00-10:20).
 *   - timetable_entries  — actual schedule rows: class x subject x
 *                          teacher x day x period (+ optional room,
 *                          academic_year, term).
 *
 * Conflict detection is done at the application layer (see
 * server/src/controllers/timetable.controller.js) rather than via UNIQUE
 * indexes, because a soft-deleted entry should not block re-adding the
 * same slot. The check considers:
 *   1. class double-booking   (same class, same day, same period, != entry)
 *   2. teacher double-booking (same teacher, same day, same period, != entry)
 *   3. room double-booking    (same room, same day, same period, != entry)
 *
 * Append the two new table names to SyncEngine.tablesToSync (after
 * `subjects` and `users` for FK dependencies).
 */
exports.up = async function (knex) {
  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS timetable_periods (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      start_time TEXT NOT NULL,
      end_time TEXT NOT NULL,
      period_order INTEGER NOT NULL,
      is_break 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
    );
  `);

  await knex.schema.raw(`
    CREATE TABLE IF NOT EXISTS timetable_entries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      class_id INTEGER NOT NULL REFERENCES classes(id),
      subject_id INTEGER NOT NULL REFERENCES subjects(id),
      teacher_id INTEGER NOT NULL REFERENCES users(id),
      day_of_week INTEGER NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
      period_id INTEGER NOT NULL REFERENCES timetable_periods(id),
      room TEXT,
      academic_year TEXT,
      term TEXT,
      notes TEXT,
      created_by INTEGER REFERENCES users(id),
      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
    );
  `);

  // Indexes that the read paths and conflict checks rely on.
  await knex.schema.raw(`CREATE INDEX IF NOT EXISTS idx_tt_entries_class_day    ON timetable_entries(class_id, day_of_week, period_id);`);
  await knex.schema.raw(`CREATE INDEX IF NOT EXISTS idx_tt_entries_teacher_day ON timetable_entries(teacher_id, day_of_week, period_id);`);
  await knex.schema.raw(`CREATE INDEX IF NOT EXISTS idx_tt_entries_room_day    ON timetable_entries(room, day_of_week, period_id);`);
  await knex.schema.raw(`CREATE INDEX IF NOT EXISTS idx_tt_entries_active      ON timetable_entries(is_deleted);`);
  await knex.schema.raw(`CREATE INDEX IF NOT EXISTS idx_tt_periods_order      ON timetable_periods(period_order, is_deleted);`);
};

exports.down = async function (knex) {
  await knex.schema.raw(`DROP INDEX IF EXISTS idx_tt_periods_order;`);
  await knex.schema.raw(`DROP INDEX IF EXISTS idx_tt_entries_active;`);
  await knex.schema.raw(`DROP INDEX IF EXISTS idx_tt_entries_room_day;`);
  await knex.schema.raw(`DROP INDEX IF EXISTS idx_tt_entries_teacher_day;`);
  await knex.schema.raw(`DROP INDEX IF EXISTS idx_tt_entries_class_day;`);
  await knex.schema.raw(`DROP TABLE IF EXISTS timetable_entries;`);
  await knex.schema.raw(`DROP TABLE IF EXISTS timetable_periods;`);
};
