/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.951Z
 *
 * Track D (P1-9) — Knex migration for the Core 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) {
  // users
  await knex.schema.raw("-- =============================================\r\n  -- CORE USERS TABLE WITH SYNC FIELDS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS users (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    email TEXT UNIQUE NOT NULL,\r\n    password TEXT NOT NULL,\r\n    role TEXT NOT NULL CHECK(role IN ('systems_admin', 'school_admin', 'teacher', 'student', 'parent', 'accountant', 'librarian', 'nurse', 'clubs_head', 'principal', 'hr', 'bursar', 'dining_staff')),\r\n    first_name TEXT NOT NULL,\r\n    last_name TEXT NOT NULL,\r\n    phone TEXT,\r\n    address TEXT,\r\n    date_of_birth DATE,\r\n    gender TEXT CHECK(gender IN ('male', 'female', 'other')),\r\n    id_number TEXT,\r\n    profile_image TEXT,\r\n    emergency_contact TEXT,\r\n    emergency_phone TEXT,\r\n    department_id INTEGER REFERENCES departments(id),\r\n    is_active INTEGER DEFAULT 1,\r\n    last_login 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  );");

  // classes
  await knex.schema.raw("-- =============================================\r\n  -- CLASSES TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS classes (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    section TEXT,\r\n    capacity INTEGER DEFAULT 40,\r\n    class_teacher_id INTEGER REFERENCES users(id),\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  );");

  // subjects
  await knex.schema.raw("-- =============================================\r\n  -- SUBJECTS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS subjects (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    class_id INTEGER REFERENCES classes(id),\r\n    teacher_id INTEGER REFERENCES users(id),\r\n    description TEXT,\r\n    credit_hours 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  );");

  // enrollments
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT ENROLLMENTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS enrollments (\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 NOT NULL REFERENCES classes(id),\r\n    section TEXT,\r\n    roll_number TEXT,\r\n    admission_date DATE DEFAULT (date('now', 'localtime')),\r\n    academic_year TEXT,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'transferred', 'graduated')),\r\n    request_status TEXT DEFAULT 'pending' CHECK(request_status IN ('pending','approved','rejected','withdrawn')),\r\n    requested_at DATETIME,\r\n    approved_by INTEGER REFERENCES users(id),\r\n    approved_at DATETIME,\r\n    rejection_reason 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  );");

  // messages
  await knex.schema.raw("-- =============================================\r\n  -- MESSAGES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS messages (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    sender_id INTEGER REFERENCES users(id),\r\n    recipient_id INTEGER REFERENCES users(id),\r\n    subject TEXT NOT NULL,\r\n    body TEXT,\r\n    is_read INTEGER DEFAULT 0,\r\n    is_announcement INTEGER DEFAULT 0,\r\n    priority TEXT DEFAULT 'normal' CHECK(priority IN ('low', 'normal', 'high', 'urgent')),\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  );");

  // events
  await knex.schema.raw("-- =============================================\r\n  -- EVENTS/CALENDAR\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS events (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    title TEXT NOT NULL,\r\n    description TEXT,\r\n    event_date DATE NOT NULL,\r\n    end_date DATE,\r\n    event_type TEXT CHECK(event_type IN ('holiday', 'exam', 'meeting', 'activity', 'deadline', 'other')),\r\n    venue TEXT,\r\n    created_by INTEGER REFERENCES users(id),\r\n    is_public INTEGER DEFAULT 1,\r\n    academic_year 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  );");

  // grades
  await knex.schema.raw("-- =============================================\r\n  -- GRADES/RESULTS\r\n  -- =============================================\r\n  -- grading_scale is a per-row override (NULL = inherit from\r\n  -- school_settings.default_grading_scale at read time). Letter grades\r\n  -- are derived via the resolver in server/src/utils/gradeScales.js.\r\n  CREATE TABLE IF NOT EXISTS grades (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    subject_id INTEGER NOT NULL REFERENCES subjects(id),\r\n    exam_type TEXT CHECK(exam_type IN ('test', 'quiz', 'midterm', 'final', 'assignment')),\r\n    marks REAL NOT NULL,\r\n    total_marks REAL DEFAULT 100,\r\n    grade TEXT,\r\n    grading_scale TEXT,\r\n    remarks TEXT,\r\n    graded_by INTEGER REFERENCES users(id),\r\n    graded_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    academic_year TEXT,\r\n    term 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  );");

  // departments
  await knex.schema.raw("-- =============================================\r\n  -- DEPARTMENTS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS departments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    head_teacher_id INTEGER REFERENCES users(id),\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' CHECK(sync_status IN ('synced', 'pending', 'conflict')),\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // courses
  await knex.schema.raw("-- =============================================\r\n  -- COURSES TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS courses (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    subject_id INTEGER REFERENCES subjects(id),\r\n    teacher_id INTEGER REFERENCES users(id),\r\n    description TEXT,\r\n    schedule TEXT,\r\n    start_date DATE,\r\n    end_date DATE,\r\n    max_students INTEGER DEFAULT 30,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'completed', 'cancelled')),\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  );");

  // course_enrolments
  await knex.schema.raw("-- =============================================\r\n  -- COURSE ENROLMENTS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS course_enrolments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    course_id INTEGER NOT NULL REFERENCES courses(id),\r\n    enrolled_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'dropped', 'completed')),\r\n    progress REAL DEFAULT 0,\r\n    completed_at DATETIME,\r\n    request_status TEXT DEFAULT 'pending' CHECK(request_status IN ('pending','approved','rejected','withdrawn')),\r\n    requested_at DATETIME,\r\n    approved_by INTEGER REFERENCES users(id),\r\n    approved_at DATETIME,\r\n    rejection_reason 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  );");

  // assignments
  await knex.schema.raw("-- =============================================\r\n  -- ASSIGNMENTS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS assignments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    course_id INTEGER NOT NULL REFERENCES subjects(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    submission_type TEXT DEFAULT 'file' CHECK(submission_type IN ('file', 'text', 'online', 'offline')),\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_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  );");

  // submissions
  await knex.schema.raw("-- =============================================\r\n  -- SUBMISSIONS TABLE\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS submissions (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    assignment_id INTEGER NOT NULL REFERENCES assignments(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    content TEXT,\r\n    file_path 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',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "submissions" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "assignments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "course_enrolments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "courses" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "departments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "grades" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "events" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "messages" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "enrollments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "subjects" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "classes" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "users" + ';');
};
