import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

const SCHEMA_VERSION = 5;
const SCHEMA_VERSION_KEY = 'schema_version';

let db: any = null;
const ctx: Worker = self as any;

const initPromise = (async () => {
  try {
    const sqlite3 = await (sqlite3InitModule as any)({
      locateFile: (file: string) => {
        if (file.endsWith('.wasm')) return '/sqlite3.wasm';
        return file;
      }
    });

    console.log('Worker: Running SQLite3 version', sqlite3.version.libVersion);

    if ('opfs' in sqlite3) {
      db = new sqlite3.oo1.OpfsDb('/school.db');
      console.log('Worker: Using OPFS for persistence');
    } else {
      db = new sqlite3.oo1.DB('/school.db', 'ct');
      console.log('Worker: Using transient storage (no OPFS support)');
    }

    db.exec(`CREATE TABLE IF NOT EXISTS sync_config (
      key TEXT PRIMARY KEY,
      value TEXT
    )`);

    const stored = db.exec({
      sql: 'SELECT value FROM sync_config WHERE key = ?',
      bind: [SCHEMA_VERSION_KEY],
      returnValue: 'resultRows',
    });
    const currentVersion = stored.length > 0 ? parseInt(stored[0][0], 10) : 0;

    if (currentVersion < SCHEMA_VERSION) {
      console.warn(`Worker: schema v${currentVersion} < v${SCHEMA_VERSION}, rebuilding local cache`);
      dropAllTables(db);
      createSchema(db);
      db.exec({
        sql: 'INSERT OR REPLACE INTO sync_config (key, value) VALUES (?, ?)',
        bind: [SCHEMA_VERSION_KEY, String(SCHEMA_VERSION)],
      });
    } else {
      createSchema(db);
    }

    return db;
  } catch (err) {
    console.error('Worker: Failed to initialize SQLite WASM', err);
    throw err;
  }
})();

function dropAllTables(db: any) {
  const rows = db.exec({
    sql: "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'",
    returnValue: 'resultRows',
  });
  for (const [name] of rows) {
    db.exec(`DROP TABLE IF EXISTS "${name}"`);
  }
}

function createSchema(db: any) {
  const tables = [
    `CREATE TABLE IF NOT EXISTS sync_config (
      key TEXT PRIMARY KEY,
      value TEXT
    )`,
    `CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      id_number TEXT,
      email TEXT UNIQUE NOT NULL,
      password TEXT,
      role TEXT NOT NULL,
      first_name TEXT NOT NULL,
      last_name TEXT NOT NULL,
      phone TEXT,
      address TEXT,
      gender TEXT CHECK(gender IN ('male', 'female', 'other')),
      date_of_birth DATE,
      emergency_contact TEXT,
      emergency_phone TEXT,
      department_id INTEGER,
      is_active INTEGER DEFAULT 1,
      last_login DATETIME,
      archive_status TEXT CHECK(archive_status IN ('active_alumni','inactive_alumni','former_staff')),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS departments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      code TEXT,
      head_teacher_id INTEGER,
      description TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS classes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      section TEXT,
      capacity INTEGER DEFAULT 40,
      class_teacher_id INTEGER,
      description TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS subjects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      code TEXT,
      class_id INTEGER,
      teacher_id INTEGER,
      description TEXT,
      credit_hours INTEGER DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS enrollments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      class_id INTEGER NOT NULL,
      section TEXT,
      roll_number TEXT,
      admission_date DATE,
      academic_year TEXT,
      status TEXT DEFAULT 'active',
      request_status TEXT DEFAULT 'pending' CHECK(request_status IN ('pending','approved','rejected','withdrawn')),
      cohort_id INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS attendance (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      class_id INTEGER,
      date DATE NOT NULL,
      status TEXT NOT NULL,
      remarks TEXT,
      marked_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS fee_groups (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      description TEXT,
      amount REAL NOT NULL,
      type TEXT,
      frequency TEXT,
      academic_year TEXT,
      due_date DATE,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS student_fees (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      fee_group_id INTEGER NOT NULL,
      amount REAL NOT NULL,
      paid_amount REAL DEFAULT 0,
      discount_amount REAL DEFAULT 0,
      fine_amount REAL DEFAULT 0,
      status TEXT DEFAULT 'pending',
      due_date DATE,
      paid_at DATETIME,
      plan_id INTEGER,
      applied_by INTEGER,
      academic_year TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS payments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_fee_id INTEGER NOT NULL,
      amount REAL NOT NULL,
      payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
      payment_method TEXT,
      reference_number TEXT,
      transaction_id TEXT,
      paynow_poll_url TEXT,
      collected_by INTEGER,
      notes TEXT,
      status TEXT,
      paid_at DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      sender_id INTEGER,
      recipient_id INTEGER,
      group_id INTEGER,
      subject TEXT NOT NULL,
      body TEXT,
      is_read INTEGER DEFAULT 0,
      is_announcement INTEGER DEFAULT 0,
      priority TEXT DEFAULT 'normal',
      attachment_url TEXT,
      attachment_name TEXT,
      attachment_type TEXT,
      is_delivered INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      title TEXT NOT NULL,
      description TEXT,
      event_date DATE NOT NULL,
      end_date DATE,
      event_type TEXT,
      venue TEXT,
      created_by INTEGER,
      is_public INTEGER DEFAULT 1,
      academic_year TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS grades (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      subject_id INTEGER NOT NULL,
      exam_type TEXT,
      marks REAL NOT NULL,
      total_marks REAL DEFAULT 100,
      grade TEXT,
      remarks TEXT,
      graded_by INTEGER,
      graded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      academic_year TEXT,
      term TEXT,
      grading_scale TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS courses (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      subject_id INTEGER,
      teacher_id INTEGER,
      description TEXT,
      schedule TEXT,
      start_date DATE,
      end_date DATE,
      max_students INTEGER DEFAULT 30,
      status TEXT DEFAULT 'active',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS assignments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      course_id INTEGER NOT NULL,
      title TEXT NOT NULL,
      description TEXT,
      instructions TEXT,
      due_date DATETIME,
      max_score REAL DEFAULT 100,
      submission_type TEXT DEFAULT 'file',
      allow_late_submission INTEGER DEFAULT 0,
      is_published INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS submissions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      assignment_id INTEGER NOT NULL,
      student_id INTEGER NOT NULL,
      content TEXT,
      file_path TEXT,
      submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      status TEXT DEFAULT 'submitted',
      grade REAL,
      feedback TEXT,
      graded_by INTEGER,
      graded_at DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS hostels (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      code TEXT,
      type TEXT NOT NULL,
      address TEXT,
      warden_id INTEGER,
      phone TEXT,
      description TEXT,
      total_rooms INTEGER DEFAULT 0,
      capacity INTEGER DEFAULT 0,
      is_active INTEGER DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS parent_students (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      parent_id INTEGER NOT NULL,
      student_id INTEGER NOT NULL,
      relationship TEXT DEFAULT 'guardian',
      is_primary INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0,
      UNIQUE(parent_id, student_id)
    )`,
    `CREATE TABLE IF NOT EXISTS rooms (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      hostel_id INTEGER NOT NULL,
      room_type_id INTEGER,
      room_number TEXT NOT NULL,
      floor INTEGER DEFAULT 0,
      bed_count INTEGER DEFAULT 1,
      status TEXT DEFAULT 'available',
      description TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS routes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      code TEXT,
      description TEXT,
      start_point TEXT NOT NULL,
      end_point TEXT NOT NULL,
      distance_km REAL,
      estimated_time_minutes INTEGER,
      status TEXT DEFAULT 'active',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_groups (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      name TEXT NOT NULL,
      description TEXT,
      exam_type TEXT,
      duration_minutes INTEGER DEFAULT 60,
      total_marks REAL DEFAULT 100,
      passing_marks REAL DEFAULT 50,
      is_random_order INTEGER DEFAULT 0,
      show_results INTEGER DEFAULT 1,
      allow_review INTEGER DEFAULT 1,
      max_attempts INTEGER DEFAULT 1,
      academic_year TEXT,
      term TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS question_banks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      exam_group_id INTEGER,
      subject_id INTEGER,
      question_type TEXT NOT NULL,
      question TEXT NOT NULL,
      options TEXT,
      correct_answer TEXT NOT NULL,
      marks REAL DEFAULT 1,
      explanation TEXT,
      difficulty TEXT DEFAULT 'medium',
      tags TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_schedules (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      exam_group_id INTEGER NOT NULL,
      class_id INTEGER,
      subject_id INTEGER,
      start_time DATETIME NOT NULL,
      end_time DATETIME,
      duration_minutes INTEGER,
      instructions TEXT,
      is_active INTEGER DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_attempts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      exam_group_id INTEGER NOT NULL,
      schedule_id INTEGER,
      started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      submitted_at DATETIME,
      time_spent_seconds INTEGER,
      total_marks REAL DEFAULT 0,
      obtained_marks REAL DEFAULT 0,
      percentage REAL DEFAULT 0,
      status TEXT DEFAULT 'in-progress',
      ip_address TEXT,
      user_agent TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_answers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      attempt_id INTEGER NOT NULL,
      question_id INTEGER NOT NULL,
      answer TEXT,
      is_correct INTEGER DEFAULT 0,
      marks_obtained REAL DEFAULT 0,
      answered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    // Student Medical module — mirrors server/src/database/init.js tables
    // (client/src/lib/sync.ts push list also names these three so local edits
    // flow up to the hub).
    `CREATE TABLE IF NOT EXISTS student_medical_profiles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL UNIQUE,
      blood_group TEXT,
      allergies TEXT,
      dietary_restrictions TEXT,
      contraindications TEXT,
      chronic_conditions TEXT,
      emergency_instructions TEXT,
      updated_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS student_medication_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      medication_name TEXT NOT NULL,
      dosage TEXT NOT NULL,
      administered_at DATETIME NOT NULL,
      administered_by INTEGER NOT NULL,
      reason TEXT,
      notes TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS student_medical_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      student_id INTEGER NOT NULL,
      event_type TEXT NOT NULL,
      description TEXT NOT NULL,
      severity TEXT DEFAULT 'moderate',
      onset_date DATE,
      resolution_date DATE,
      treatment_details TEXT,
      doctor_notes TEXT,
      is_visible_to_teachers INTEGER DEFAULT 1,
      reported_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`,
    // Notice Board — mirrors server/src/controllers/notices.controller.js.
    // `audience` is one of 'All', 'Teachers', 'Students', 'Parents' (the
    // explicit allowlist in api.ts filters by this column). Added in
    // WT-B so the explicit /notices offline handler can return rows
    // from a real local table instead of crashing with "no such table".
    `CREATE TABLE IF NOT EXISTS notices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE,
      title TEXT NOT NULL,
      body TEXT,
      audience TEXT DEFAULT 'All',
      priority TEXT DEFAULT 'normal',
      is_pinned INTEGER DEFAULT 0,
      posted_by INTEGER,
      expires_at DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_synced_at DATETIME,
      sync_status TEXT DEFAULT 'pending',
      is_deleted INTEGER DEFAULT 0
    )`
  ];

  for (const sql of tables) {
    try {
      db.exec(sql);
    } catch (e) {
      console.error('Worker Schema Error (Table):', e);
    }
  }

  // Schema Migrations
  const migrateTable = (tableName: string, colName: string, colType: string) => {
    try {
      db.exec(`ALTER TABLE ${tableName} ADD COLUMN ${colName} ${colType}`);
      console.log(`Worker Schema: Added ${colName} to ${tableName}`);
    } catch (e: any) {
      if (e.message?.includes('duplicate column name')) {
        // Already exists
      } else {
        console.error(`Worker Schema Error (Column ${colName} in ${tableName}):`, e);
      }
    }
  };

  migrateTable('users', 'phone', 'TEXT');
  migrateTable('users', 'address', 'TEXT');
  migrateTable('users', 'date_of_birth', 'DATE');
  migrateTable('users', 'gender', "TEXT CHECK(gender IN ('male', 'female', 'other'))");
  db.exec("UPDATE users SET gender = LOWER(gender) WHERE gender IS NOT NULL AND LOWER(gender) IN ('male', 'female', 'other')");
  db.exec("UPDATE users SET gender = NULL WHERE gender IS NOT NULL AND gender NOT IN ('male', 'female', 'other')");
  db.exec(`
    CREATE TRIGGER IF NOT EXISTS users_gender_insert_check
    BEFORE INSERT ON users
    WHEN NEW.gender IS NOT NULL AND NEW.gender NOT IN ('male', 'female', 'other')
    BEGIN
      SELECT RAISE(ABORT, 'invalid gender');
    END;
  `);
  db.exec(`
    CREATE TRIGGER IF NOT EXISTS users_gender_update_check
    BEFORE UPDATE OF gender ON users
    WHEN NEW.gender IS NOT NULL AND NEW.gender NOT IN ('male', 'female', 'other')
    BEGIN
      SELECT RAISE(ABORT, 'invalid gender');
    END;
  `);
  migrateTable('users', 'id_number', 'TEXT');
  migrateTable('users', 'profile_image', 'TEXT');
  migrateTable('users', 'emergency_contact', 'TEXT');
  migrateTable('users', 'emergency_phone', 'TEXT');
  migrateTable('users', 'last_synced_at', 'DATETIME');

  migrateTable('attendance', 'subject_id', 'INTEGER');
  migrateTable('attendance', 'remarks', 'TEXT');
  migrateTable('attendance', 'marked_by', 'INTEGER');
  migrateTable('attendance', 'last_synced_at', 'DATETIME');

  // Columns the server schema has that the worker client schema was missing.
  // Without these, the sync engine pulls server rows with extra columns and
  // the local SQLite (which only has the columns in createSchema) throws
  // 'no such column: X' on every pull.
  migrateTable('departments', 'code', 'TEXT');
  migrateTable('enrollments', 'request_status', "TEXT DEFAULT 'pending' CHECK(request_status IN ('pending','approved','rejected','withdrawn'))");
  migrateTable('enrollments', 'requested_at', 'DATETIME');
  migrateTable('enrollments', 'approved_by', 'INTEGER');
  migrateTable('enrollments', 'approved_at', 'DATETIME');
  migrateTable('enrollments', 'rejection_reason', 'TEXT');
  migrateTable('assignments', 'status', "TEXT DEFAULT 'draft' CHECK(status IN ('draft','published','closed'))");
  migrateTable('messages', 'group_id', 'INTEGER');
}

// Handle incoming query/execute commands
ctx.onmessage = async (event) => {
  const { type, id, sql, params } = event.data;
  try {
    const database = await initPromise;
    if (type === 'query') {
      const rows = database.exec({
        sql,
        bind: params || [],
        returnValue: 'resultRows',
        rowMode: 'object'
      });
      ctx.postMessage({ id, success: true, result: rows });
    } else if (type === 'execute') {
      database.exec({
        sql,
        bind: params || []
      });
      ctx.postMessage({ id, success: true });
    } else if (type === 'verifyPassword') {
      const bcryptmod = await import('bcryptjs');
      const bcrypt = (bcryptmod.default || bcryptmod) as typeof import('bcryptjs');
      const [email, password] = params;
      const rows = database.exec({
        sql: 'SELECT * FROM users WHERE email = ? AND is_deleted = 0 LIMIT 1',
        bind: [email],
        returnValue: 'resultRows',
        rowMode: 'object',
      });
      if (!rows || rows.length === 0) {
        ctx.postMessage({ id, success: true, result: { ok: false, reason: 'No local credentials' } });
        return;
      }
      const user = rows[0] as any;
      if (!user.password) {
        ctx.postMessage({ id, success: true, result: { ok: false, reason: 'No cached credentials' } });
        return;
      }
      const ok = bcrypt.compareSync(password, user.password);
      ctx.postMessage({
        id,
        success: true,
        result: ok
          ? {
              ok: true,
              user: {
                id: user.id,
                email: user.email,
                role: user.role,
                first_name: user.first_name,
                last_name: user.last_name,
                gender: user.gender,
                profile_image: user.profile_image,
              },
            }
          : { ok: false, reason: 'Invalid credentials' },
      });
    } else if (type === 'cacheHash') {
      const [email, passwordHash] = params;
      database.exec({
        sql: 'UPDATE users SET password = ?, last_synced_at = CURRENT_TIMESTAMP WHERE email = ?',
        bind: [passwordHash, email],
      });
      ctx.postMessage({ id, success: true });
    } else if (type === 'createLocalUser') {
      const bcryptmod = await import('bcryptjs');
      const bcrypt = (bcryptmod.default || bcryptmod) as typeof import('bcryptjs');
      const [userData] = params;
      if (!userData?.email || !userData?.password) {
        ctx.postMessage({ id, success: true, result: { ok: false, reason: 'email and password required' } });
        return;
      }
      const gender = userData.gender || null;
      if (gender && !['male', 'female', 'other'].includes(gender)) {
        ctx.postMessage({ id, success: true, result: { ok: false, reason: 'invalid gender' } });
        return;
      }
      const existing = database.exec({
        sql: 'SELECT id FROM users WHERE email = ? AND is_deleted = 0 LIMIT 1',
        bind: [userData.email],
        returnValue: 'resultRows',
        rowMode: 'object',
      });
      if (existing && existing.length > 0) {
        ctx.postMessage({ id, success: true, result: { ok: false, reason: 'email already exists' } });
        return;
      }
      const passwordHash = bcrypt.hashSync(userData.password, 10);
      const uid = userData.uid || (crypto.randomUUID && crypto.randomUUID()) || `local-${Date.now()}`;
      const row = {
        uid,
        email: userData.email,
        password: passwordHash,
        role: userData.role || 'teacher',
        first_name: userData.first_name || '',
        last_name: userData.last_name || '',
        phone: userData.phone || null,
        gender,
        department_id: userData.department_id || null,
      };
      database.exec({
        sql: `INSERT INTO users (uid, email, password, role, first_name, last_name, phone, gender, department_id, sync_status, is_deleted)
              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 0)`,
        bind: [row.uid, row.email, row.password, row.role, row.first_name, row.last_name, row.phone, row.gender, row.department_id],
      });
      const idRow = database.exec({
        sql: 'SELECT id FROM users WHERE uid = ?',
        bind: [row.uid],
        returnValue: 'resultRows',
      });
      ctx.postMessage({
        id,
        success: true,
        result: {
          ok: true,
          user: {
            id: idRow[0]?.[0],
            uid: row.uid,
            email: row.email,
            role: row.role,
            first_name: row.first_name,
            last_name: row.last_name,
            phone: row.phone,
            gender: row.gender,
            department_id: row.department_id,
          },
        },
      });
    } else {
      ctx.postMessage({ id, success: false, error: 'Unknown message type: ' + type });
    }
  } catch (err: any) {
    ctx.postMessage({ id, success: false, error: err?.message || String(err) });
  }
};
