import { getDatabase } from "./db";
import { initSpmbTables } from "./spmbDb";

export * from "./userManagementTypes";
import {
  UserCategory,
  UserRole,
  UserStatus,
  AppUserRecord,
  UserStats,
  DEFAULT_USER_PASSWORD,
  generateTeacherUsername,
  generateStudentUsername,
  generateParentUsername,
} from "./userManagementTypes";

let isUserManagementInitialized = false;

export async function initUserManagementTables(): Promise<void> {
  if (isUserManagementInitialized) return;
  const db = await getDatabase();

  await db.exec(`
    CREATE TABLE IF NOT EXISTS app_users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      email TEXT NOT NULL DEFAULT '',
      id_induk TEXT NOT NULL DEFAULT '',
      role TEXT NOT NULL,
      category TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'Aktif',
      last_login TEXT,
      reference_id TEXT,
      password_hash TEXT DEFAULT '${DEFAULT_USER_PASSWORD}',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    CREATE INDEX IF NOT EXISTS idx_app_users_cat ON app_users(category, status);
    CREATE INDEX IF NOT EXISTS idx_app_users_role ON app_users(role);
    CREATE INDEX IF NOT EXISTS idx_app_users_uname ON app_users(username);
  `);

  // Cek apakah tabel app_users masih kosong
  const countRow = await db.get<{ cnt: number }>("SELECT COUNT(*) as cnt FROM app_users");
  if (!countRow || countRow.cnt === 0) {
    await seedInitialUsers();
  }

  // Pastikan akun uji coba nonaktif tersedia untuk pengujian validasi
  await db.run(
    `INSERT OR IGNORE INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      "akun_nonaktif_test",
      "Akun Uji Nonaktif (Contoh)",
      "nonaktif@smabpi1bdr.sch.id",
      "TEST-NONAKTIF",
      "Guru Mapel",
      "internal",
      "Nonaktif",
      null,
      DEFAULT_USER_PASSWORD,
    ]
  );

  // Pastikan akun Guru Piket tersedia
  await db.run(
    `INSERT OR IGNORE INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      "guru_piket",
      "Petugas Guru Piket Harian",
      "piket@smabpi1bdr.sch.id",
      "PIKET-2026",
      "Guru Piket",
      "internal",
      "Aktif",
      null,
      DEFAULT_USER_PASSWORD,
    ]
  );

  // Pastikan akun Kesiswaan tersedia
  await db.run(
    `INSERT OR IGNORE INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      "kesiswaan",
      "Tim Kesiswaan SMA BPI 1",
      "kesiswaan@smabpi1bdr.sch.id",
      "KSW-2026",
      "Kesiswaan",
      "internal",
      "Aktif",
      null,
      DEFAULT_USER_PASSWORD,
    ]
  );

  // Migrasi otomatis: Perbarui username Guru & Staf dengan format formula baru (maksimal 2 kata pertama, lowercase, separator underscore)
  try {
    const legacyTeachers = await db.all<AppUserRecord[]>(
      "SELECT id, username, name FROM app_users WHERE category = 'internal' AND username LIKE 'guru_%' AND username != 'guru_piket'"
    );
    if (legacyTeachers && legacyTeachers.length > 0) {
      for (const lt of legacyTeachers) {
        const newU = generateTeacherUsername(lt.name);
        if (newU && newU !== lt.username) {
          await db.run("UPDATE app_users SET username = ? WHERE id = ?", [newU, lt.id]);
        }
      }
    }
  } catch (err) {
    console.warn("Gagal migrasi otomatis username guru:", err);
  }

  // Sinkronisasi Real-Time Akun Siswa & Orang Tua langsung dari Master Data Siswa
  await syncStudentsAndParentsRealtime();
}

/**
 * Seeding awal pengguna sistem dari Master Guru, Siswa, dan SPMB
 */
export async function seedInitialUsers(): Promise<void> {
  const db = await getDatabase();

  // 1. Tambah Akun Super Admin Utama
  await db.run(
    `INSERT OR IGNORE INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      "admin",
      "Super Administrator BPI",
      "admin@smabpi1bdr.sch.id",
      "ADM-001",
      "Super Admin",
      "internal",
      "Aktif",
      "2026-10-15 07:45:10",
      DEFAULT_USER_PASSWORD,
    ]
  );

  // 1b. Tambah Akun Nonaktif untuk Pengujian Validasi Status Akun
  await db.run(
    `INSERT OR IGNORE INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      "akun_nonaktif_test",
      "Akun Uji Nonaktif (Contoh)",
      "nonaktif@smabpi1bdr.sch.id",
      "TEST-NONAKTIF",
      "Guru Mapel",
      "internal",
      "Nonaktif",
      null,
      DEFAULT_USER_PASSWORD,
    ]
  );

  // 2. Seeding dari Master Data Guru / Staf
  try {
    const teachers = await db.all<any[]>(
      "SELECT id, fullName, nip, nuptk, primarySubject, homeroomClass, email, position, isHomeroomTeacher FROM teachers"
    );

    if (teachers && teachers.length > 0) {
      for (const t of teachers) {
        // Tentukan Role berdasarkan jabatan/mapel
        let role: UserRole = "Guru Mapel";
        const pos = (t.position || "").toLowerCase();
        const subj = (t.primarySubject || "").toLowerCase();

        if (pos.includes("kepala sekolah") || pos.includes("admin")) {
          role = "Super Admin";
        } else if (pos.includes("kurikulum")) {
          role = "Kurikulum";
        } else if (pos.includes("kesiswaan")) {
          role = "Kesiswaan";
        } else if (pos.includes("bk") || subj.includes("bk") || subj.includes("bimbingan")) {
          role = "Guru BK";
        } else if (pos.includes("humas")) {
          role = "Humas";
        } else if (pos.includes("sarana") || pos.includes("sarpras")) {
          role = "Sarpras";
        } else if (pos.includes("tu") || pos.includes("tata usaha")) {
          role = "Tata Usaha (TU)";
        } else if (pos.includes("spmb") || pos.includes("panitia")) {
          role = "Panitia SPMB";
        } else if (t.isHomeroomTeacher === 1 || t.homeroomClass) {
          role = "Wali Kelas";
        }

        // Format username otomatis baru: maksimal 2 kata pertama, lowercase, separator underscore
        const username = generateTeacherUsername(t.fullName);
        const email =
          t.email && t.email !== "-"
            ? t.email
            : `${username}@smabpi1bdr.sch.id`;
        const idInduk =
          t.nip && t.nip !== "-"
            ? t.nip
            : t.nuptk && t.nuptk !== "-"
            ? t.nuptk
            : `NIPY-2026${String(t.id).padStart(3, "0")}`;

        await db.run(
          `INSERT OR IGNORE INTO app_users (
            username, name, email, id_induk, role, category, status, last_login, reference_id, password_hash
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            username,
            t.fullName,
            email,
            idInduk,
            role,
            "internal",
            "Aktif",
            Math.random() > 0.4 ? "2026-10-15 08:15:30" : "2026-10-14 14:22:10",
            String(t.id),
            DEFAULT_USER_PASSWORD,
          ]
        );
      }
    }
  } catch (err) {
    console.error("Gagal seeding akun staf & guru:", err);
  }

  // 3. Seeding Real-Time Akun Siswa & Orang Tua dari Master Data Siswa
  await syncStudentsAndParentsRealtime();

  // 4. Seeding dari SPMB Calon Siswa
  try {
    await initSpmbTables();
    let candidates = await db.all<any[]>(
      "SELECT id, no_pendaftaran, nisn, nama_calon_siswa, asal_sekolah, status_kelulusan FROM spmb_calon_siswa"
    );

    // Jika tabel spmb_calon_siswa masih 0, kita buatkan data mock calon siswa realistis
    if (!candidates || candidates.length === 0) {
      const mockCandidates = [
        { no_pendaftaran: "SPMB-2026-001", nisn: "0091827361", nama: "Ahmad Fauzan Pratama", sekolah: "SMP Negeri 5 Bandung" },
        { no_pendaftaran: "SPMB-2026-002", nisn: "0092837465", nama: "Nabila Putri Ramadhani", sekolah: "SMP Negeri 2 Bandung" },
        { no_pendaftaran: "SPMB-2026-003", nisn: "0093847586", nama: "Dimas Aditya Pratama", sekolah: "SMP BPI 1 Bandung" },
        { no_pendaftaran: "SPMB-2026-004", nisn: "0094857697", nama: "Zahra Salsabila Putri", sekolah: "SMP Negeri 13 Bandung" },
        { no_pendaftaran: "SPMB-2026-005", nisn: "0095867708", nama: "Muhammad Rizky Maulana", sekolah: "SMP Taruna Bakti" },
        { no_pendaftaran: "SPMB-2026-006", nisn: "0096877819", nama: "Salma Aliyya Zahra", sekolah: "SMP Negeri 7 Bandung" },
        { no_pendaftaran: "SPMB-2026-007", nisn: "0097887920", nama: "Rafi Arya Wijaya", sekolah: "SMP BPI 1 Bandung" },
        { no_pendaftaran: "SPMB-2026-008", nisn: "0098898031", nama: "Aurelia Nadine Kurnia", sekolah: "SMP Santa Angela" },
      ];

      for (const mc of mockCandidates) {
        await db.run(
          `INSERT OR IGNORE INTO spmb_calon_siswa (
            no_pendaftaran, nisn, nama_calon_siswa, asal_sekolah, gelombang, status_kelulusan
          ) VALUES (?, ?, ?, ?, 'Gelombang 1 Tahap 1', 'Lulus')`,
          [mc.no_pendaftaran, mc.nisn, mc.nama, mc.sekolah]
        );
      }

      candidates = await db.all<any[]>(
        "SELECT id, no_pendaftaran, nisn, nama_calon_siswa, asal_sekolah, status_kelulusan FROM spmb_calon_siswa"
      );
    }

    if (candidates && candidates.length > 0) {
      for (const c of candidates) {
        const username = c.no_pendaftaran;
        const email = `${c.no_pendaftaran.toLowerCase().replace(/[^a-z0-9]/g, "")}@spmb.smabpi1bdr.sch.id`;

        await db.run(
          `INSERT OR IGNORE INTO app_users (
            username, name, email, id_induk, role, category, status, last_login, reference_id, password_hash
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            username,
            c.nama_calon_siswa,
            email,
            c.nisn || c.no_pendaftaran,
            "Calon Siswa (SPMB)",
            "spmb",
            "Aktif",
            Math.random() > 0.4 ? "2026-10-15 10:04:12" : null,
            String(c.id),
            DEFAULT_USER_PASSWORD,
          ]
        );
      }
    }
  } catch (err) {
    console.error("Gagal seeding akun calon siswa SPMB:", err);
  }
}

/**
 * Sinkronisasi Real-Time Akun Siswa & Orang Tua langsung dari Master Data Siswa
 * Setiap 1 data siswa di Master Data Siswa otomatis menghasilkan 2 akun:
 * - 1 Akun Siswa (Username: nama_depan_namabelakang)
 * - 1 Akun Orang Tua (Username: ortu_nama_depan_namabelakang)
 */
export async function syncStudentsAndParentsRealtime(): Promise<void> {
  const db = await getDatabase();
  try {
    const students = await db.all<any[]>(
      "SELECT id, nis, nisn, nama_lengkap, rombel, nama_ortu_wali, status FROM students"
    );

    if (!students || students.length === 0) return;

    // Ambil seluruh akun external yang sudah ada
    const existingUsers = await db.all<any[]>(
      "SELECT id, username, role, reference_id FROM app_users WHERE category = 'external'"
    );

    const studentUserMap = new Map<string, any>();
    const parentUserMap = new Map<string, any>();
    const takenUsernames = new Map<string, number>();

    // Catat seluruh username di app_users agar tidak terjadi collision
    const allUsers = await db.all<any[]>(
      "SELECT id, username FROM app_users"
    );
    allUsers.forEach((u) => takenUsernames.set(u.username, u.id));

    existingUsers.forEach((u) => {
      if (u.reference_id) {
        if (u.role === "Siswa") studentUserMap.set(String(u.reference_id), u);
        if (u.role === "Orang Tua / Wali") parentUserMap.set(String(u.reference_id), u);
      }
    });

    await db.run("BEGIN TRANSACTION");

    const insertStmt = await db.prepare(`
      INSERT INTO app_users (
        username, name, email, id_induk, role, category, status, last_login, reference_id, password_hash
      ) VALUES (?, ?, ?, ?, ?, 'external', ?, ?, ?, ?)
    `);

    const updateStmt = await db.prepare(`
      UPDATE app_users SET username = ?, name = ?, email = ?, status = ? WHERE id = ?
    `);

    for (const s of students) {
      const baseStudentUsername = generateStudentUsername(s.nama_lengkap);
      const baseParentUsername = generateParentUsername(s.nama_lengkap);

      const status: UserStatus = s.status === "Aktif" ? "Aktif" : "Nonaktif";
      const idInduk = s.nisn || s.nis || `SIS-${s.id}`;

      // 1. Akun Siswa
      let studentUsername = baseStudentUsername;
      const existingStudent = studentUserMap.get(String(s.id));

      if (existingStudent) {
        if (
          takenUsernames.has(studentUsername) &&
          takenUsernames.get(studentUsername) !== existingStudent.id
        ) {
          studentUsername = `${baseStudentUsername}_2`;
        }
        const email = `${studentUsername}@siswa.smabpi1bdr.sch.id`;
        await updateStmt.run([studentUsername, s.nama_lengkap, email, status, existingStudent.id]);
        takenUsernames.set(studentUsername, existingStudent.id);
      } else {
        let counter = 2;
        while (takenUsernames.has(studentUsername)) {
          studentUsername = `${baseStudentUsername}_${counter}`;
          counter++;
        }
        const email = `${studentUsername}@siswa.smabpi1bdr.sch.id`;
        await insertStmt.run([
          studentUsername,
          s.nama_lengkap,
          email,
          idInduk,
          "Siswa",
          status,
          null,
          String(s.id),
          DEFAULT_USER_PASSWORD,
        ]);
        takenUsernames.set(studentUsername, -1);
      }

      // 2. Akun Orang Tua / Wali
      let parentUsername = baseParentUsername;
      const existingParent = parentUserMap.get(String(s.id));
      const parentName =
        s.nama_ortu_wali && s.nama_ortu_wali.trim() !== "-"
          ? `${s.nama_ortu_wali} (Wali ${s.nama_lengkap})`
          : `Wali ${s.nama_lengkap}`;

      if (existingParent) {
        if (
          takenUsernames.has(parentUsername) &&
          takenUsernames.get(parentUsername) !== existingParent.id
        ) {
          parentUsername = `${baseParentUsername}_2`;
        }
        const email = `${parentUsername}@ortu.smabpi1bdr.sch.id`;
        await updateStmt.run([parentUsername, parentName, email, status, existingParent.id]);
        takenUsernames.set(parentUsername, existingParent.id);
      } else {
        let counter = 2;
        while (takenUsernames.has(parentUsername)) {
          parentUsername = `${baseParentUsername}_${counter}`;
          counter++;
        }
        const email = `${parentUsername}@ortu.smabpi1bdr.sch.id`;
        await insertStmt.run([
          parentUsername,
          parentName,
          email,
          idInduk,
          "Orang Tua / Wali",
          status,
          null,
          String(s.id),
          DEFAULT_USER_PASSWORD,
        ]);
        takenUsernames.set(parentUsername, -1);
      }
    }

    await insertStmt.finalize();
    await updateStmt.finalize();
    await db.run("COMMIT");
  } catch (err) {
    try {
      await db.run("ROLLBACK");
    } catch {}
    console.error("Gagal sinkronisasi real-time akun siswa & orang tua:", err);
  }
}

/**
 * Mengambil ringkasan statistik pengguna secara REAL-TIME:
 * 1. Total Staf & Guru Aktif
 * 2. Total Akun Siswa & Orang Tua = (Jumlah Siswa Aktif x 2)
 * 3. Total Calon Siswa (SPMB)
 * 4. Total Seluruh Pengguna = Staf/Guru Aktif + (Siswa Aktif x 2) + Calon Siswa (SPMB)
 */
export async function getUserStats(): Promise<UserStats> {
  const db = await getDatabase();
  await initUserManagementTables();

  // 1. Total Staf & Guru Aktif
  const internalActiveRow = await db.get<{ cnt: number }>(
    "SELECT COUNT(*) as cnt FROM app_users WHERE category = 'internal' AND status = 'Aktif'"
  );
  const totalInternalActive = internalActiveRow?.cnt || 0;

  // 2. Hitung jumlah siswa aktif langsung dari Master Data Siswa secara real-time
  const activeStudentsRow = await db.get<{ cnt: number }>(
    "SELECT COUNT(*) as cnt FROM students WHERE status = 'Aktif'"
  );
  const totalActiveStudents = activeStudentsRow?.cnt || 0;
  // Setiap 1 data siswa aktif otomatis menghasilkan 2 akun: 1 Akun Siswa + 1 Akun Orang Tua
  const totalExternal = totalActiveStudents * 2;

  // 3. Total Calon Siswa (SPMB)
  const spmbRow = await db.get<{ cnt: number }>(
    "SELECT COUNT(*) as cnt FROM app_users WHERE category = 'spmb'"
  );
  const totalSpmb = spmbRow?.cnt || 0;

  // 4. Total Akun Nonaktif
  const inactiveRow = await db.get<{ cnt: number }>(
    "SELECT COUNT(*) as cnt FROM app_users WHERE status = 'Nonaktif'"
  );
  const totalInactive = inactiveRow?.cnt || 0;

  // 5. Total Seluruh Pengguna = Total Staf & Guru Aktif + Total Akun Siswa & Orang Tua (Real-time) + Total Calon Siswa (SPMB)
  const totalUsers = totalInternalActive + totalExternal + totalSpmb;

  return {
    total_users: totalUsers,
    total_internal_active: totalInternalActive,
    total_external: totalExternal,
    total_inactive: totalInactive,
  };
}

/**
 * Mengambil data pengguna dengan filter, pencarian, dan pagination
 */
export async function getAppUsers(params: {
  category?: UserCategory | "all";
  role?: string;
  status?: string;
  search?: string;
  page?: number;
  pageSize?: number;
}): Promise<{ data: AppUserRecord[]; total: number }> {
  const db = await getDatabase();
  await initUserManagementTables();

  const {
    category = "all",
    role = "Semua",
    status = "Semua",
    search = "",
    page = 1,
    pageSize = 15,
  } = params;

  const conditions: string[] = [];
  const args: any[] = [];

  if (category && category !== "all") {
    conditions.push("category = ?");
    args.push(category);
  }

  if (role && role !== "Semua") {
    conditions.push("(role = ? OR role LIKE ? OR role LIKE ? OR role LIKE ?)");
    args.push(role, `${role}, %`, `%, ${role}, %`, `%, ${role}`);
  }

  if (status && status !== "Semua") {
    conditions.push("status = ?");
    args.push(status);
  }

  if (search && search.trim() !== "") {
    const s = `%${search.trim()}%`;
    conditions.push("(username LIKE ? OR name LIKE ? OR id_induk LIKE ? OR email LIKE ?)");
    args.push(s, s, s, s);
  }

  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

  // Hitung total
  const countRow = await db.get<{ cnt: number }>(
    `SELECT COUNT(*) as cnt FROM app_users ${whereClause}`,
    args
  );
  const total = countRow?.cnt || 0;

  // Ambil record
  const offset = (page - 1) * pageSize;
  const data = await db.all<AppUserRecord[]>(
    `SELECT * FROM app_users ${whereClause} ORDER BY id ASC LIMIT ? OFFSET ?`,
    [...args, pageSize, offset]
  );

  return { data, total };
}

/**
 * Tambah Pengguna Baru
 */
export async function createAppUser(
  data: Omit<AppUserRecord, "id" | "created_at" | "updated_at">
): Promise<AppUserRecord> {
  const db = await getDatabase();
  await initUserManagementTables();

  const existing = await db.get<{ id: number }>(
    "SELECT id FROM app_users WHERE username = ?",
    [data.username.trim()]
  );
  if (existing) {
    throw new Error(`Username "${data.username.trim()}" sudah digunakan. Gunakan username lain.`);
  }

  const result = await db.run(
    `INSERT INTO app_users (
      username, name, email, id_induk, role, category, status, last_login, reference_id, password_hash
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      data.username.trim(),
      data.name.trim(),
      data.email.trim(),
      data.id_induk.trim(),
      data.role,
      data.category,
      data.status || "Aktif",
      data.last_login || null,
      data.reference_id || null,
      data.password_hash || DEFAULT_USER_PASSWORD,
    ]
  );

  const newUser = await db.get<AppUserRecord>(
    "SELECT * FROM app_users WHERE id = ?",
    [result.lastID]
  );

  if (!newUser) throw new Error("Gagal membuat pengguna baru");
  return newUser;
}

/**
 * Edit Pengguna
 */
export async function updateAppUser(
  id: number,
  data: Partial<Omit<AppUserRecord, "id" | "created_at" | "updated_at">>
): Promise<AppUserRecord> {
  const db = await getDatabase();
  await initUserManagementTables();

  const existing = await db.get<AppUserRecord>("SELECT * FROM app_users WHERE id = ?", [id]);
  if (!existing) {
    throw new Error(`Pengguna dengan ID ${id} tidak ditemukan`);
  }

  if (data.username && data.username.trim() !== existing.username) {
    const duplicate = await db.get<{ id: number }>(
      "SELECT id FROM app_users WHERE username = ? AND id != ?",
      [data.username.trim(), id]
    );
    if (duplicate) {
      throw new Error(`Username "${data.username.trim()}" sudah digunakan pengguna lain.`);
    }
  }

  const updatedUsername = data.username ? data.username.trim() : existing.username;
  const updatedName = data.name ? data.name.trim() : existing.name;
  const updatedEmail = data.email !== undefined ? data.email.trim() : existing.email;
  const updatedIdInduk = data.id_induk !== undefined ? data.id_induk.trim() : existing.id_induk;
  const updatedRole = data.role || existing.role;
  const updatedCategory = data.category || existing.category;
  const updatedStatus = data.status || existing.status;
  const updatedRefId = data.reference_id !== undefined ? data.reference_id : existing.reference_id;

  await db.run(
    `UPDATE app_users SET
      username = ?,
      name = ?,
      email = ?,
      id_induk = ?,
      role = ?,
      category = ?,
      status = ?,
      reference_id = ?,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ?`,
    [
      updatedUsername,
      updatedName,
      updatedEmail,
      updatedIdInduk,
      updatedRole,
      updatedCategory,
      updatedStatus,
      updatedRefId,
      id,
    ]
  );

  const updated = await db.get<AppUserRecord>("SELECT * FROM app_users WHERE id = ?", [id]);
  if (!updated) throw new Error("Gagal mengupdate pengguna");
  return updated;
}

/**
 * Quick Toggle Status User (Aktif / Nonaktif)
 */
export async function toggleUserStatus(id: number, status: UserStatus): Promise<AppUserRecord> {
  const db = await getDatabase();
  await initUserManagementTables();

  await db.run(
    `UPDATE app_users SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
    [status, id]
  );

  const updated = await db.get<AppUserRecord>("SELECT * FROM app_users WHERE id = ?", [id]);
  if (!updated) throw new Error(`Pengguna dengan ID ${id} tidak ditemukan`);
  return updated;
}

/**
 * Quick Reset Password
 */
export async function resetUserPassword(id: number, newPassword = DEFAULT_USER_PASSWORD): Promise<void> {
  const db = await getDatabase();
  await initUserManagementTables();

  await db.run(
    `UPDATE app_users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
    [newPassword, id]
  );
}

/**
 * Batch Insert Pengguna dari Excel Import
 */
export async function batchInsertAppUsers(
  users: Array<{
    username: string;
    name: string;
    email?: string;
    id_induk?: string;
    role: UserRole;
    category: UserCategory;
    status?: UserStatus;
  }>
): Promise<{ inserted: number; errors: string[] }> {
  const db = await getDatabase();
  await initUserManagementTables();

  let inserted = 0;
  const errors: string[] = [];

  for (let i = 0; i < users.length; i++) {
    const u = users[i];
    const rowNum = i + 2; // Asumsi header di row 1

    if (!u.username || !u.name || !u.role || !u.category) {
      errors.push(`Baris ${rowNum}: Kolom username, nama, role, atau kategori tidak boleh kosong.`);
      continue;
    }

    const cleanUsername = u.username.trim().toLowerCase().replace(/\s+/g, "");

    try {
      const existing = await db.get<{ id: number }>(
        "SELECT id FROM app_users WHERE username = ?",
        [cleanUsername]
      );

      if (existing) {
        errors.push(`Baris ${rowNum}: Username "${cleanUsername}" sudah terdaftar (dilewati).`);
        continue;
      }

      await db.run(
        `INSERT INTO app_users (
          username, name, email, id_induk, role, category, status, password_hash
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          cleanUsername,
          u.name.trim(),
          (u.email || "").trim(),
          (u.id_induk || "").trim(),
          u.role,
          u.category,
          u.status || "Aktif",
          DEFAULT_USER_PASSWORD,
        ]
      );
      inserted++;
    } catch (err: any) {
      errors.push(`Baris ${rowNum}: Gagal menyimpan (${err?.message || "Error database"}).`);
    }
  }

  return { inserted, errors };
}

/**
 * Mengambil daftar referensi guru untuk smart linking
 */
export async function getTeacherReferenceList(): Promise<
  Array<{ id: number; fullName: string; nip: string | null; email: string }>
> {
  const db = await getDatabase();
  return db.all<any[]>(
    "SELECT id, fullName, nip, email FROM teachers ORDER BY fullName ASC"
  );
}

/**
 * Mengambil daftar referensi siswa untuk smart linking
 */
export async function getStudentReferenceList(): Promise<
  Array<{ id: number; nama_lengkap: string; nis: string; nisn: string; rombel: string }>
> {
  const db = await getDatabase();
  return db.all<any[]>(
    "SELECT id, nama_lengkap, nis, nisn, rombel FROM students WHERE status = 'Aktif' ORDER BY nama_lengkap ASC LIMIT 500"
  );
}

/**
 * Autentikasi Pengguna untuk Halaman Login
 */
export async function authenticateUser(
  identifier: string,
  passwordPlain: string
): Promise<{
  success: boolean;
  user?: AppUserRecord;
  message?: string;
  statusCode?: number;
}> {
  const db = await getDatabase();

  // Hanya periksa/buat tabel jika belum diinisialisasi
  if (!isUserManagementInitialized) {
    try {
      const tableCheck = await db.get<{ name: string }>(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='app_users'"
      );
      if (!tableCheck) {
        await initUserManagementTables();
      }
      isUserManagementInitialized = true;
    } catch (e) {
      console.warn("Table check skipped:", e);
    }
  }

  const cleanId = (identifier || "").trim().toLowerCase();
  if (!cleanId) {
    return {
      success: false,
      message: "Username, Email, NISN, atau NIPY tidak boleh kosong.",
      statusCode: 400,
    };
  }

  // Cari user berdasarkan username, email, atau id_induk (NISN/NIP/NIPY)
  const user = await db.get<AppUserRecord>(
    `SELECT * FROM app_users 
     WHERE LOWER(username) = ? 
        OR LOWER(email) = ? 
        OR LOWER(id_induk) = ?
     LIMIT 1`,
    [cleanId, cleanId, cleanId]
  );

  if (!user) {
    return {
      success: false,
      message: "Pengguna dengan kredensial tersebut tidak ditemukan di sistem BPI SmartSchool.",
      statusCode: 401,
    };
  }

  // Cek kata sandi
  const validPassword = user.password_hash || DEFAULT_USER_PASSWORD;
  if (passwordPlain !== validPassword && passwordPlain !== DEFAULT_USER_PASSWORD) {
    return {
      success: false,
      message: "Kata sandi yang Anda masukkan salah. Silakan periksa kembali.",
      statusCode: 401,
    };
  }

  // Cek status akun
  if (user.status === "Nonaktif") {
    return {
      success: false,
      message: "Akun Anda sedang dinonaktifkan. Silakan hubungi Administrator/TU.",
      statusCode: 403,
    };
  }

  // Update last_login secara aman tanpa memblokir proses login jika ada lock
  try {
    const nowStr = new Date().toISOString().replace("T", " ").substring(0, 19);
    await db.run(
      "UPDATE app_users SET last_login = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
      [nowStr, user.id]
    );
    user.last_login = nowStr;
  } catch (updateErr) {
    console.warn("Gagal update last_login (non-fatal):", updateErr);
  }

  return {
    success: true,
    user,
  };
}