/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.952Z
 *
 * Track D (P1-9) — Knex migration for the Academics 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) {
  // attendance
  await knex.schema.raw("-- =============================================\r\n  -- ATTENDANCE TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS attendance (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    class_id INTEGER REFERENCES classes(id),\r\n    subject_id INTEGER REFERENCES subjects(id),\r\n    date DATE NOT NULL,\r\n    status TEXT NOT NULL CHECK(status IN ('present', 'absent', 'late', 'excused')),\r\n    remarks TEXT,\r\n    marked_by INTEGER REFERENCES users(id),\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  );");

  // exam_groups
  await knex.schema.raw("-- =============================================\r\n  -- EXAM GROUPS\r\n  -- =============================================\r\n  -- grading_scale is a per-exam override (NULL = inherit from\r\n  -- school_settings.default_grading_scale at read time).\r\n  CREATE TABLE IF NOT EXISTS exam_groups (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    description TEXT,\r\n    exam_type TEXT CHECK(exam_type IN ('quiz', 'test', 'midterm', 'final', 'practice')),\r\n    duration_minutes INTEGER DEFAULT 60,\r\n    total_marks REAL DEFAULT 100,\r\n    passing_marks REAL DEFAULT 50,\r\n    is_random_order INTEGER DEFAULT 0,\r\n    show_results INTEGER DEFAULT 1,\r\n    allow_review INTEGER DEFAULT 1,\r\n    max_attempts INTEGER DEFAULT 1,\r\n    academic_year TEXT,\r\n    term TEXT,\r\n    grading_scale TEXT,\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  );");

  // hostels
  await knex.schema.raw("-- =============================================\r\n  -- HOSTELS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS hostels (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    type TEXT NOT NULL CHECK(type IN ('boys', 'girls', 'co-ed')),\r\n    address TEXT,\r\n    warden_id INTEGER REFERENCES users(id),\r\n    phone TEXT,\r\n    description TEXT,\r\n    total_rooms INTEGER DEFAULT 0,\r\n    capacity INTEGER DEFAULT 0,\r\n    is_active INTEGER DEFAULT 1,\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  );");

  // rooms
  await knex.schema.raw("-- =============================================\r\n  -- ROOMS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS rooms (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    hostel_id INTEGER NOT NULL REFERENCES hostels(id),\r\n    room_type_id INTEGER REFERENCES room_types(id),\r\n    room_number TEXT NOT NULL,\r\n    floor INTEGER DEFAULT 0,\r\n    bed_count INTEGER DEFAULT 1,\r\n    status TEXT DEFAULT 'available' CHECK(status IN ('available', 'occupied', 'maintenance', 'reserved')),\r\n    description TEXT,\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  );");

  // room_assignments
  await knex.schema.raw("-- =============================================\r\n  -- ROOM ASSIGNMENTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS room_assignments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    room_id INTEGER NOT NULL REFERENCES rooms(id),\r\n    bed_number INTEGER DEFAULT 1,\r\n    start_date DATE NOT NULL,\r\n    end_date DATE,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'transferred', 'expelled')),\r\n    remarks TEXT,\r\n    assigned_by INTEGER REFERENCES users(id),\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  );");

  // homework
  await knex.schema.raw("-- =============================================\r\n  -- §C — HOMEWORK TABLE (parallel to assignments)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS homework (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    subject_id INTEGER NOT NULL REFERENCES subjects(id),\r\n    class_id INTEGER REFERENCES classes(id),\r\n    title TEXT NOT NULL,\r\n    description TEXT,\r\n    instructions TEXT,\r\n    due_date DATETIME,\r\n    max_score REAL DEFAULT 100,\r\n    allow_late_submission INTEGER DEFAULT 0,\r\n    is_published INTEGER DEFAULT 0,\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft','published','closed')),\r\n    created_by INTEGER REFERENCES users(id),\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' CHECK(sync_status IN ('synced','pending','conflict')),\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // homework_submissions
  await knex.schema.raw("-- =============================================\r\n  -- §C — HOMEWORK_SUBMISSIONS / TEST_SUBMISSIONS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS homework_submissions (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    homework_id INTEGER NOT NULL REFERENCES homework(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    content TEXT,\r\n    submitted_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    status TEXT DEFAULT 'submitted' CHECK(status IN ('submitted', 'graded', 'returned', 'late')),\r\n    grade REAL,\r\n    feedback TEXT,\r\n    graded_by INTEGER REFERENCES users(id),\r\n    graded_at DATETIME,\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' CHECK(sync_status IN ('synced','pending','conflict')),\r\n    is_deleted INTEGER DEFAULT 0,\r\n    UNIQUE(homework_id, student_id)\r\n  );");

  // library_books
  await knex.schema.raw("-- =============================================\r\n  -- LIBRARY BOOKS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS library_books (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    isbn TEXT,\r\n    title TEXT NOT NULL,\r\n    author TEXT,\r\n    category TEXT,\r\n    publisher TEXT,\r\n    year_published INTEGER,\r\n    total_copies INTEGER DEFAULT 1,\r\n    available_copies INTEGER DEFAULT 1,\r\n    shelf_location TEXT,\r\n    status TEXT DEFAULT 'available',\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // clubs
  await knex.schema.raw("-- =============================================\r\n  -- SPORTS & CLUBS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS clubs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    type TEXT DEFAULT 'club' CHECK(type IN ('club', 'sport')),\r\n    description TEXT,\r\n    teacher_in_charge_id INTEGER REFERENCES users(id),\r\n    schedule TEXT,\r\n    max_members INTEGER,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive')),\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  );");

  // club_attendance
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS club_attendance (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    club_id INTEGER REFERENCES clubs(id),\r\n    student_id INTEGER REFERENCES users(id),\r\n    date DATE NOT NULL,\r\n    status TEXT DEFAULT 'present' CHECK(status IN ('present', 'absent', 'excused')),\r\n    marked_by INTEGER REFERENCES users(id),\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  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "club_attendance" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "clubs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "library_books" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "homework_submissions" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "homework" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "room_assignments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "rooms" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "hostels" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "exam_groups" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "attendance" + ';');
};
