/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.954Z
 *
 * Track D (P1-9) — Knex migration for the Misc 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) {
  // sync_logs
  await knex.schema.raw("-- =============================================\r\n  -- SYNC LOGS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS sync_logs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    table_name TEXT NOT NULL,\r\n    record_id INTEGER,\r\n    operation TEXT NOT NULL CHECK(operation IN ('push', 'pull', 'conflict', 'error')),\r\n    status TEXT NOT NULL CHECK(status IN ('success', 'failed', 'pending')),\r\n    error_message TEXT,\r\n    retry_count INTEGER DEFAULT 0,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    synced_at DATETIME\r\n  );");

  // sync_metadata
  await knex.schema.raw("-- =============================================\r\n  -- SYNC METADATA\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS sync_metadata (\r\n    uid TEXT PRIMARY KEY,\r\n    table_name TEXT NOT NULL,\r\n    last_modified_by_role TEXT,\r\n    last_modified_at DATETIME\r\n  );");

  // sync_config
  await knex.schema.raw("-- =============================================\r\n  -- SYNC CONFIG\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS sync_config (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    key TEXT UNIQUE NOT NULL,\r\n    value TEXT,\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime'))\r\n  );");

  // audit_logs
  await knex.schema.raw("-- =============================================\r\n  -- AUDIT LOGS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS audit_logs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    user_id INTEGER,\r\n    user_email TEXT,\r\n    action TEXT NOT NULL,\r\n    entity_type TEXT,\r\n    entity_id TEXT,\r\n    old_data TEXT,\r\n    new_data TEXT,\r\n    ip_address TEXT,\r\n    user_agent TEXT,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime'))\r\n  );");

  // staff_roles
  await knex.schema.raw("-- =============================================\r\n  -- STAFF ROLES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS staff_roles (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    description TEXT,\r\n    is_teaching_role INTEGER DEFAULT 0,\r\n    permissions 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  );");

  // salary_grades
  await knex.schema.raw("-- =============================================\r\n  -- SALARY GRADES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS salary_grades (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    grade_level TEXT,\r\n    basic_salary REAL NOT NULL,\r\n    housing_allowance REAL DEFAULT 0,\r\n    transport_allowance REAL DEFAULT 0,\r\n    medical_allowance REAL DEFAULT 0,\r\n    other_allowances REAL DEFAULT 0,\r\n    description TEXT,\r\n    effective_date DATE,\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  );");

  // question_banks
  await knex.schema.raw("-- =============================================\r\n  -- QUESTION BANKS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS question_banks (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    exam_group_id INTEGER REFERENCES exam_groups(id),\r\n    subject_id INTEGER REFERENCES subjects(id),\r\n    question_type TEXT NOT NULL CHECK(question_type IN ('multiple-choice', 'true-false', 'short-answer', 'essay', 'fill-blank')),\r\n    question TEXT NOT NULL,\r\n    options TEXT,\r\n    correct_answer TEXT NOT NULL,\r\n    marks REAL DEFAULT 1,\r\n    explanation TEXT,\r\n    difficulty TEXT DEFAULT 'medium' CHECK(difficulty IN ('easy', 'medium', 'hard')),\r\n    tags 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  );");

  // exam_schedules
  await knex.schema.raw("-- =============================================\r\n  -- EXAM SCHEDULES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS exam_schedules (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    exam_group_id INTEGER NOT NULL REFERENCES exam_groups(id),\r\n    class_id INTEGER REFERENCES classes(id),\r\n    subject_id INTEGER REFERENCES subjects(id),\r\n    start_time DATETIME NOT NULL,\r\n    end_time DATETIME,\r\n    duration_minutes INTEGER,\r\n    instructions TEXT,\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  );");

  // exam_attempts
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT EXAM ATTEMPTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS exam_attempts (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    exam_group_id INTEGER NOT NULL REFERENCES exam_groups(id),\r\n    schedule_id INTEGER REFERENCES exam_schedules(id),\r\n    started_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    submitted_at DATETIME,\r\n    time_spent_seconds INTEGER,\r\n    total_marks REAL DEFAULT 0,\r\n    obtained_marks REAL DEFAULT 0,\r\n    percentage REAL DEFAULT 0,\r\n    status TEXT DEFAULT 'in-progress' CHECK(status IN ('in-progress', 'completed', 'submitted', 'graded')),\r\n    ip_address TEXT,\r\n    user_agent 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  );");

  // exam_answers
  await knex.schema.raw("-- =============================================\r\n  -- EXAM ANSWERS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS exam_answers (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    attempt_id INTEGER NOT NULL REFERENCES exam_attempts(id),\r\n    question_id INTEGER NOT NULL REFERENCES question_banks(id),\r\n    answer TEXT,\r\n    is_correct INTEGER DEFAULT 0,\r\n    marks_obtained REAL DEFAULT 0,\r\n    answered_at DATETIME DEFAULT (datetime('now', 'localtime')),\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_types
  await knex.schema.raw("-- =============================================\r\n  -- ROOM TYPES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS room_types (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    description TEXT,\r\n    bed_count INTEGER DEFAULT 1,\r\n    price_per_semester REAL DEFAULT 0,\r\n    facilities 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  );");

  // hostel_fees
  await knex.schema.raw("-- =============================================\r\n  -- HOSTEL FEES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS hostel_fees (\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 REFERENCES rooms(id),\r\n    amount REAL NOT NULL,\r\n    period TEXT,\r\n    due_date DATE,\r\n    paid_amount REAL DEFAULT 0,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'partial', 'paid', 'waived')),\r\n    paid_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  );");

  // vehicles
  await knex.schema.raw("-- =============================================\r\n  -- VEHICLES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS vehicles (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    registration_number TEXT NOT NULL,\r\n    model TEXT,\r\n    make TEXT,\r\n    year INTEGER,\r\n    color TEXT,\r\n    capacity INTEGER DEFAULT 0,\r\n    vehicle_type TEXT CHECK(vehicle_type IN ('bus', 'minibus', 'van', 'car', 'other')),\r\n    fuel_type TEXT,\r\n    insurance_expiry DATE,\r\n    roadworthy_expiry DATE,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'maintenance', 'retired', 'sold')),\r\n    last_service_date DATE,\r\n    next_service_date DATE,\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  );");

  // routes
  await knex.schema.raw("-- =============================================\r\n  -- ROUTES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS routes (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    description TEXT,\r\n    start_point TEXT NOT NULL,\r\n    end_point TEXT NOT NULL,\r\n    distance_km REAL,\r\n    estimated_time_minutes INTEGER,\r\n    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'suspended')),\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  );");

  // pickup_points
  await knex.schema.raw("-- =============================================\r\n  -- PICKUP POINTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS pickup_points (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    route_id INTEGER NOT NULL REFERENCES routes(id),\r\n    name TEXT NOT NULL,\r\n    address TEXT,\r\n    latitude REAL,\r\n    longitude REAL,\r\n    pickup_time TIME,\r\n    dropoff_time TIME,\r\n    sequence_order 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  );");

  // vehicle_routes
  await knex.schema.raw("-- =============================================\r\n  -- VEHICLE ROUTE ASSIGNMENTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS vehicle_routes (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    vehicle_id INTEGER NOT NULL REFERENCES vehicles(id),\r\n    route_id INTEGER NOT NULL REFERENCES routes(id),\r\n    driver_id INTEGER REFERENCES users(id),\r\n    conductor_id INTEGER REFERENCES users(id),\r\n    start_time TIME,\r\n    end_time TIME,\r\n    days_of_week TEXT,\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  );");

  // transport_allocations
  await knex.schema.raw("-- =============================================\r\n  -- TRANSPORT ALLOCATIONS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS transport_allocations (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    route_id INTEGER NOT NULL REFERENCES routes(id),\r\n    pickup_point_id INTEGER REFERENCES pickup_points(id),\r\n    vehicle_route_id INTEGER REFERENCES vehicle_routes(id),\r\n    fee_amount REAL DEFAULT 0,\r\n    is_active INTEGER DEFAULT 1,\r\n    effective_from DATE,\r\n    effective_to DATE,\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  );");

  // admission_enquiries
  await knex.schema.raw("-- =============================================\r\n  -- ADMISSION ENQUIRIES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS admission_enquiries (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    full_name TEXT NOT NULL,\r\n    email TEXT,\r\n    phone TEXT NOT NULL,\r\n    address TEXT,\r\n    class_interested TEXT,\r\n    source TEXT,\r\n    enquiry_date DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    follow_up_date DATE,\r\n    status TEXT DEFAULT 'new' CHECK(status IN ('new', 'contacted', 'visited', 'enrolled', 'not-interested', 'closed')),\r\n    assigned_to INTEGER REFERENCES users(id),\r\n    notes TEXT,\r\n    next_action 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  );");

  // visitor_logs
  await knex.schema.raw("-- =============================================\r\n  -- VISITOR LOGS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS visitor_logs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    visitor_name TEXT NOT NULL,\r\n    visitor_type TEXT CHECK(visitor_type IN ('parent', 'vendor', 'government', 'media', 'other')),\r\n    phone TEXT,\r\n    email TEXT,\r\n    id_number TEXT,\r\n    purpose TEXT,\r\n    person_to_visit TEXT,\r\n    check_in DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    check_out DATETIME,\r\n    badge_number TEXT,\r\n    remarks TEXT,\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',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // phone_call_logs
  await knex.schema.raw("-- =============================================\r\n  -- PHONE CALL LOGS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS phone_call_logs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    caller_name TEXT,\r\n    caller_phone TEXT,\r\n    caller_type TEXT CHECK(caller_type IN ('parent', 'student', 'vendor', 'government', 'other')),\r\n    direction TEXT DEFAULT 'incoming' CHECK(direction IN ('incoming', 'outgoing')),\r\n    call_time DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    duration_seconds INTEGER,\r\n    purpose TEXT,\r\n    response TEXT,\r\n    follow_up_required INTEGER DEFAULT 0,\r\n    follow_up_date DATE,\r\n    handled_by INTEGER REFERENCES users(id),\r\n    notes 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  );");

  // postal_dispatch
  await knex.schema.raw("-- =============================================\r\n  -- POSTAL DISPATCH\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS postal_dispatch (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    type TEXT DEFAULT 'dispatch' CHECK(type IN ('dispatch', 'receive')),\r\n    reference_number TEXT UNIQUE,\r\n    sender TEXT NOT NULL,\r\n    receiver TEXT NOT NULL,\r\n    address TEXT,\r\n    courier TEXT,\r\n    tracking_number TEXT,\r\n    dispatch_date DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    received_date DATETIME,\r\n    received_by INTEGER REFERENCES users(id),\r\n    description TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'delivered', 'returned')),\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',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // complaints
  await knex.schema.raw("-- =============================================\r\n  -- COMPLAINTS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS complaints (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    complainant_name TEXT NOT NULL,\r\n    complainant_type TEXT CHECK(complainant_type IN ('parent', 'student', 'staff', 'visitor', 'other')),\r\n    contact_phone TEXT,\r\n    contact_email TEXT,\r\n    category TEXT NOT NULL,\r\n    priority TEXT DEFAULT 'medium' CHECK(priority IN ('low', 'medium', 'high', 'urgent')),\r\n    subject TEXT NOT NULL,\r\n    description TEXT NOT NULL,\r\n    date_received DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    assigned_to INTEGER REFERENCES users(id),\r\n    status TEXT DEFAULT 'open' CHECK(status IN ('open', 'investigating', 'resolved', 'closed', 'rejected')),\r\n    resolution TEXT,\r\n    resolved_by INTEGER REFERENCES users(id),\r\n    resolved_at DATETIME,\r\n    rating INTEGER,\r\n    feedback 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  );");

  // attachments
  await knex.schema.raw("-- =============================================\r\n  -- §A — ATTACHMENTS TABLE (polymorphic)\r\n  -- =============================================\r\n  -- One table for every uploaded file: syllabuses, course materials,\r\n  -- assignment supplementary files, homework sheets, test papers,\r\n  -- student submission files, and teacher-uploaded marked scripts.\r\n  -- The parent pointer is (parent_kind, parent_id); no FK because the\r\n  -- parent could live in any of several tables. Lookup pattern is\r\n  -- WHERE parent_kind = ? AND parent_id = ?, covered by the index below.\r\n  CREATE TABLE IF NOT EXISTS attachments (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    parent_kind TEXT NOT NULL CHECK(parent_kind IN (\r\n      'subject',\r\n      'assignment',\r\n      'homework',\r\n      'test',\r\n      'exam_paper',\r\n      'submission',\r\n      'marked_script'\r\n    )),\r\n    parent_id INTEGER NOT NULL,\r\n    original_filename TEXT NOT NULL,\r\n    stored_filename TEXT NOT NULL,\r\n    mime_type TEXT NOT NULL,\r\n    size_bytes INTEGER NOT NULL,\r\n    uploaded_by INTEGER REFERENCES users(id),\r\n    uploaded_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    caption TEXT,\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  );");

  // tests
  await knex.schema.raw("-- =============================================\r\n  -- §C — TESTS TABLE (parallel to assignments, with test_date/duration)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS tests (\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    test_date DATETIME,\r\n    duration_minutes INTEGER,\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  );");

  // test_submissions
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS test_submissions (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    test_id INTEGER NOT NULL REFERENCES tests(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(test_id, student_id)\r\n  );");

  // course_grades
  await knex.schema.raw("-- =============================================\r\n  -- COURSE GRADES TABLE\r\n  -- =============================================\r\n  -- Course-level final grade (term/year aggregate, separate from per-\r\n  -- assessment grades rows). grading_scale is a per-row override\r\n  -- (NULL = inherit from school_settings.default_grading_scale).\r\n  CREATE TABLE IF NOT EXISTS course_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    course_id INTEGER NOT NULL REFERENCES courses(id),\r\n    score REAL,\r\n    letter_grade TEXT,\r\n    grading_scale TEXT,\r\n    term TEXT,\r\n    academic_year TEXT,\r\n    remarks TEXT,\r\n    graded_by INTEGER REFERENCES users(id),\r\n    graded_at DATETIME DEFAULT (datetime('now', 'localtime')),\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  );");

  // notices
  await knex.schema.raw("-- =============================================\r\n  -- NOTICE BOARD\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS notices (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    title TEXT NOT NULL,\r\n    content TEXT NOT NULL,\r\n    category TEXT DEFAULT 'Academic',\r\n    priority TEXT DEFAULT 'Normal'\r\n      CHECK(priority IN ('Normal','Important','Urgent')),\r\n    audience TEXT DEFAULT 'All',\r\n    is_pinned INTEGER DEFAULT 0,\r\n    created_by INTEGER REFERENCES users(id),\r\n    expiry_date DATE,\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  );");

  // library_issues
  await knex.schema.raw("-- =============================================\r\n  -- LIBRARY ISSUES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS library_issues (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    book_id INTEGER REFERENCES library_books(id),\r\n    user_id INTEGER REFERENCES users(id),\r\n    issue_date DATE,\r\n    due_date DATE,\r\n    return_date DATE,\r\n    status TEXT DEFAULT 'issued',\r\n    issued_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    is_deleted INTEGER DEFAULT 0\r\n  );");

  // library_reservations
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS library_reservations (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    book_id INTEGER REFERENCES library_books(id),\r\n    user_id INTEGER REFERENCES users(id),\r\n    reservation_date DATE DEFAULT CURRENT_DATE,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'fulfilled', 'cancelled')),\r\n    notes TEXT,\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  );");

  // library_fines
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS library_fines (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    issue_id INTEGER REFERENCES library_issues(id),\r\n    user_id INTEGER REFERENCES users(id),\r\n    amount REAL NOT NULL,\r\n    reason TEXT,\r\n    status TEXT DEFAULT 'unpaid' CHECK(status IN ('unpaid', 'paid', 'waived')),\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  );");

  // club_memberships
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS club_memberships (\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    role TEXT DEFAULT 'member',\r\n    join_date DATE DEFAULT CURRENT_DATE,\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_announcements
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS club_announcements (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    club_id INTEGER REFERENCES clubs(id),\r\n    title TEXT NOT NULL,\r\n    content TEXT NOT NULL,\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',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // chat_groups
  await knex.schema.raw("-- Chat / group conversations (referenced by /api/messages/conversations\r\n  -- and the ExtracurricularChat component). The original schema was missing\r\n  -- these — the standalone migration_chat_groups.js backfilled them; the\r\n  -- blocks here keep fresh DBs self-contained.\r\n  CREATE TABLE IF NOT EXISTS chat_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    created_by INTEGER REFERENCES users(id),\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  );");

  // chat_group_members
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS chat_group_members (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    group_id INTEGER NOT NULL REFERENCES chat_groups(id),\r\n    user_id INTEGER NOT NULL REFERENCES users(id),\r\n    role TEXT DEFAULT 'member',\r\n    joined_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    UNIQUE(group_id, user_id)\r\n  );");

  // club_chats
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS club_chats (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    club_id INTEGER,\r\n    sender_id INTEGER REFERENCES users(id),\r\n    message TEXT,\r\n    image_url 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  );");

  // chat_messages
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS chat_messages (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    conversation_id TEXT,\r\n    sender_id INTEGER REFERENCES users(id),\r\n    recipient_id INTEGER REFERENCES users(id),\r\n    message TEXT,\r\n    read_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  );");

  // club_chats
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS club_chats (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    club_id INTEGER REFERENCES clubs(id),\r\n    sender_id INTEGER REFERENCES users(id),\r\n    message TEXT,\r\n    image_url 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  );");

  // hostel_attendance
  await knex.schema.raw("-- =============================================\r\n  -- EXTENDED ROLL CALL (HOSTEL & TRANSPORT)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS hostel_attendance (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    hostel_id INTEGER REFERENCES hostels(id),\r\n    room_id INTEGER REFERENCES rooms(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  );");

  // transport_attendance
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS transport_attendance (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    route_id INTEGER REFERENCES routes(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  );");

  // parent_students
  await knex.schema.raw("-- =============================================\r\n  -- PARENT-STUDENT RELATIONSHIPS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS parent_students (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    parent_id INTEGER NOT NULL REFERENCES users(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    relationship TEXT DEFAULT 'guardian' CHECK(relationship IN ('father', 'mother', 'guardian', 'other')),\r\n    is_primary 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    UNIQUE(parent_id, student_id)\r\n  );");

  // syllabuses
  await knex.schema.raw("-- =============================================\r\n  -- TEACHER TOOLS & SOCIAL DOCS (MIGRATIONS INCORPORATED)\r\n  -- =============================================\r\n\r\n  -- SYLLABUSES\r\n  CREATE TABLE IF NOT EXISTS syllabuses (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    subject_id INTEGER NOT NULL REFERENCES subjects(id),\r\n    title TEXT NOT NULL,\r\n    description TEXT,\r\n    file_path TEXT,\r\n    version TEXT,\r\n    is_active INTEGER DEFAULT 1,\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  );");

  // schemes_of_work
  await knex.schema.raw("-- SCHEMES OF WORK\r\n  CREATE TABLE IF NOT EXISTS schemes_of_work (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    subject_id INTEGER NOT NULL REFERENCES subjects(id),\r\n    teacher_id INTEGER NOT NULL REFERENCES users(id),\r\n    term TEXT,\r\n    academic_year TEXT,\r\n    content TEXT, -- JSON structure for weeks/topics\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'submitted', 'approved', 'rejected')),\r\n    approved_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  );");

  // focus_points
  await knex.schema.raw("-- FOCUS POINTS\r\n  CREATE TABLE IF NOT EXISTS focus_points (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    scheme_id INTEGER NOT NULL REFERENCES schemes_of_work(id),\r\n    topic TEXT NOT NULL,\r\n    objectives TEXT,\r\n    key_concepts TEXT,\r\n    resources_needed 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  );");

  // lesson_plans
  await knex.schema.raw("-- LESSON PLANS\r\n  CREATE TABLE IF NOT EXISTS lesson_plans (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    teacher_id INTEGER NOT NULL REFERENCES users(id),\r\n    subject_id INTEGER NOT NULL REFERENCES subjects(id),\r\n    scheme_id INTEGER REFERENCES schemes_of_work(id),\r\n    focus_point_id INTEGER REFERENCES focus_points(id),\r\n    date DATE,\r\n    topic TEXT NOT NULL,\r\n    objectives TEXT,\r\n    introduction TEXT,\r\n    development TEXT,\r\n    conclusion TEXT,\r\n    assessment_method TEXT,\r\n    homework TEXT,\r\n    reflections TEXT,\r\n    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', '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' CHECK(sync_status IN ('synced', 'pending', 'conflict')),\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // student_social_docs
  await knex.schema.raw("-- STUDENT SOCIAL DOCUMENTS\r\n  CREATE TABLE IF NOT EXISTS student_social_docs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    learner_type TEXT, -- visual, auditory, kinesthetic, etc.\r\n    past_behavior TEXT,\r\n    family_size INTEGER,\r\n    parents_available TEXT, -- both, father, mother, guardian, none\r\n    home_environment TEXT,\r\n    medical_notes TEXT,\r\n    social_strengths TEXT,\r\n    social_challenges TEXT,\r\n    interests TEXT,\r\n    other_data TEXT, -- JSON for additional flexible data\r\n    updated_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  );");

  // student_medical_profiles
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT MEDICAL PROFILES (one row per student)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS student_medical_profiles (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL UNIQUE REFERENCES users(id),\r\n    blood_group TEXT CHECK(blood_group IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-', 'Unknown')),\r\n    allergies TEXT,               -- free text: food/drug allergies\r\n    dietary_restrictions TEXT,   -- Vegetarian, Halal, Diabetic, etc.\r\n    contraindications TEXT,      -- medications the student MUST NOT receive\r\n    chronic_conditions TEXT,     -- Asthma, Epilepsy, Diabetes, etc.\r\n    emergency_instructions TEXT, -- specific steps to take during an episode\r\n    updated_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  );");

  // student_medication_logs
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT MEDICATION LOGS (administered at school)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS student_medication_logs (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    medication_name TEXT NOT NULL,\r\n    dosage TEXT NOT NULL,\r\n    administered_at DATETIME NOT NULL,\r\n    administered_by INTEGER NOT NULL REFERENCES users(id),\r\n    reason TEXT,\r\n    notes 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  );");

  // student_medical_history
  await knex.schema.raw("-- =============================================\r\n  -- STUDENT MEDICAL HISTORY (illness / injury / hospitalization)\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS student_medical_history (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    event_type TEXT NOT NULL CHECK(event_type IN ('illness', 'injury', 'hospitalization')),\r\n    description TEXT NOT NULL,\r\n    severity TEXT DEFAULT 'moderate' CHECK(severity IN ('mild', 'moderate', 'severe')),\r\n    onset_date DATE,\r\n    resolution_date DATE,\r\n    treatment_details TEXT,\r\n    doctor_notes TEXT,             -- private nurse/doctor-only notes\r\n    is_visible_to_teachers INTEGER DEFAULT 1,\r\n    reported_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  );");

  // crossword_games
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS crossword_games (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    title TEXT NOT NULL,\r\n    description TEXT,\r\n    subject_id INTEGER REFERENCES subjects(id),\r\n    class_id INTEGER REFERENCES classes(id),\r\n    created_by INTEGER NOT NULL REFERENCES users(id),\r\n    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'ready', 'archived')),\r\n    grid_layout TEXT,\r\n    word_count INTEGER DEFAULT 0,\r\n    difficulty TEXT DEFAULT 'medium' CHECK(difficulty IN ('easy','medium','hard')),\r\n    reward_points INTEGER DEFAULT 10,\r\n    time_limit_seconds INTEGER,\r\n    is_published 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  );");

  // crossword_clues
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS crossword_clues (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    game_id INTEGER NOT NULL REFERENCES crossword_games(id),\r\n    clue_text TEXT NOT NULL,\r\n    answer TEXT NOT NULL,\r\n    direction TEXT NOT NULL CHECK(direction IN ('across','down')),\r\n    start_row INTEGER, start_col INTEGER, clue_number INTEGER,\r\n    hint 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  );");

  // crossword_sessions
  await knex.schema.raw("CREATE TABLE IF NOT EXISTS crossword_sessions (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    game_id INTEGER NOT NULL REFERENCES crossword_games(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    current_state TEXT,\r\n    correct_count INTEGER DEFAULT 0,\r\n    total_count INTEGER DEFAULT 0,\r\n    time_taken_seconds INTEGER,\r\n    completed_at DATETIME,\r\n    points_earned 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  );");

  // student_groups
  await knex.schema.raw("-- ============== Fee engine ==============\r\n\r\n  -- 9. Student groups (manual or class-derived; type narrows UX).\r\n  CREATE TABLE IF NOT EXISTS student_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    type TEXT DEFAULT 'custom' CHECK(type IN ('class','trip','hostel','transport','club','fee','custom')),\r\n    color TEXT,\r\n    class_id INTEGER REFERENCES classes(id),\r\n    capacity INTEGER,\r\n    auto_track_class INTEGER DEFAULT 0,\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',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // student_group_members
  await knex.schema.raw("-- 10. Student group members (junction; manual, or class auto-derivation).\r\n  CREATE TABLE IF NOT EXISTS student_group_members (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    group_id INTEGER NOT NULL REFERENCES student_groups(id),\r\n    student_id INTEGER NOT NULL REFERENCES users(id),\r\n    joined_at DATETIME DEFAULT (datetime('now','localtime')),\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    UNIQUE(group_id, student_id)\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_group_members" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_groups" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "crossword_sessions" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "crossword_clues" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "crossword_games" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_medical_history" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_medication_logs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_medical_profiles" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "student_social_docs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "lesson_plans" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "focus_points" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "schemes_of_work" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "syllabuses" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "parent_students" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "transport_attendance" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "hostel_attendance" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "club_chats" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "chat_messages" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "club_chats" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "chat_group_members" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "chat_groups" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "club_announcements" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "club_memberships" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "library_fines" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "library_reservations" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "library_issues" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "notices" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "course_grades" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "test_submissions" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "tests" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "attachments" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "complaints" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "postal_dispatch" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "phone_call_logs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "visitor_logs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "admission_enquiries" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "transport_allocations" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "vehicle_routes" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "pickup_points" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "routes" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "vehicles" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "hostel_fees" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "room_types" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "exam_answers" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "exam_attempts" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "exam_schedules" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "question_banks" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "salary_grades" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "staff_roles" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "audit_logs" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "sync_config" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "sync_metadata" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "sync_logs" + ';');
};
