import { getDatabase } from "./db";
import { getCurrentAcademicYear } from "./academicYear";
import {
  DhuhaAttendanceRecord,
  DhuhaStudentItem,
  DhuhaClassStats,
  DhuhaAttendanceStatus,
  getClosestFriday,
} from "./dhuhaTypes";

export * from "./dhuhaTypes";

/**
 * Inisialisasi tabel dhuha_attendances
 */
export async function initDhuhaTables(): Promise<void> {
  const db = await getDatabase();

  await db.exec(`
    CREATE TABLE IF NOT EXISTS dhuha_attendances (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      student_id INTEGER NOT NULL,
      student_nis TEXT NOT NULL,
      student_nisn TEXT DEFAULT '',
      student_name TEXT NOT NULL,
      class_name TEXT NOT NULL,
      gender TEXT NOT NULL DEFAULT 'Laki-laki',
      date TEXT NOT NULL,
      academic_year TEXT DEFAULT '2026/2027',
      semester TEXT DEFAULT 'Ganjil',
      status TEXT NOT NULL DEFAULT 'Hadir',
      time TEXT DEFAULT '06:45',
      notes TEXT DEFAULT '',
      recorder TEXT DEFAULT 'Guru PAI / Pembina Kesiswaan',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(student_id, date)
    );
    CREATE INDEX IF NOT EXISTS idx_dhuha_date_class ON dhuha_attendances(date, class_name);
    CREATE INDEX IF NOT EXISTS idx_dhuha_nis ON dhuha_attendances(student_nis);
  `);

  // Cek apakah tabel masih kosong, jika ya buatkan sample data untuk Jumat terdekat di kelas X-1
  const countRow = await db.get<{ cnt: number }>(
    "SELECT COUNT(*) as cnt FROM dhuha_attendances"
  );
  if (!countRow || countRow.cnt === 0) {
    await seedInitialDhuhaData();
  }
}

/**
 * Seeding data awal presensi dhuha untuk simulasi langsung di kelas X-1
 */
export async function seedInitialDhuhaData(): Promise<void> {
  const db = await getDatabase();
  const sampleFriday = getClosestFriday();

  try {
    const studentsX1 = await db.all<any[]>(
      "SELECT id, nis, nisn, nama_lengkap, jenis_kelamin, rombel FROM students WHERE rombel = 'X-1' ORDER BY nama_lengkap ASC"
    );

    if (studentsX1 && studentsX1.length > 0) {
      for (let i = 0; i < studentsX1.length; i++) {
        const s = studentsX1[i];
        const isFemale = s.jenis_kelamin === "Perempuan";
        let status: DhuhaAttendanceStatus = "Hadir";
        let time = "06:45";
        let notes = "";

        // Buat variasi realistis: siswi ada yang halangan, sedikit izin/sakit
        if (i === 3 && isFemale) {
          status = "Halangan";
          notes = "Udzur Syar'i (Haid)";
        } else if (i === 7) {
          status = "Sakit";
          notes = "Demam, surat dokter";
        } else if (i === 11) {
          status = "Izin";
          notes = "Dispensasi lomba Paskibra";
        } else if (i === 15 && isFemale) {
          status = "Halangan";
          notes = "Udzur Syar'i (Haid)";
        } else {
          time = `06:${String(40 + (i % 15)).padStart(2, "0")}`;
        }

        await db.run(
          `INSERT OR IGNORE INTO dhuha_attendances (
            student_id, student_nis, student_nisn, student_name, class_name,
            gender, date, academic_year, semester, status, time, notes, recorder
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            s.id,
            s.nis,
            s.nisn || "",
            s.nama_lengkap,
            s.rombel,
            s.jenis_kelamin || "Laki-laki",
            sampleFriday,
            "2026/2027",
            "Ganjil",
            status,
            time,
            notes,
            "Guru PAI / Pembina Kesiswaan",
          ]
        );
      }
    }
  } catch (err) {
    console.error("Gagal seeding awal presensi dhuha:", err);
  }
}

/**
 * Mengambil daftar siswa per kelas beserta status presensi dhuha pada tanggal terpilih
 */
export async function getClassStudentsWithDhuhaAttendance(
  className: string,
  date: string
): Promise<{ students: DhuhaStudentItem[]; stats: DhuhaClassStats }> {
  const db = await getDatabase();
  await initDhuhaTables();

  // 1. Ambil data master siswa aktif di kelas tersebut
  const students = await db.all<any[]>(
    `SELECT id, nis, nisn, nama_lengkap, jenis_kelamin, rombel
     FROM students 
     WHERE rombel = ? AND status = 'Aktif'
     ORDER BY nama_lengkap ASC`,
    [className]
  );

  // 2. Ambil data presensi yang sudah tercatat pada tanggal tersebut
  const attendances = await db.all<DhuhaAttendanceRecord[]>(
    `SELECT * FROM dhuha_attendances WHERE class_name = ? AND date = ?`,
    [className, date]
  );

  const attendanceMap = new Map<number, DhuhaAttendanceRecord>();
  for (const att of attendances) {
    attendanceMap.set(att.student_id, att);
  }

  // 3. Gabungkan data
  let countHadir = 0;
  let countSakit = 0;
  let countIzin = 0;
  let countAlpha = 0;
  let countHalangan = 0;

  const result: DhuhaStudentItem[] = students.map((s) => {
    const existing = attendanceMap.get(s.id);
    const gender = s.jenis_kelamin === "Perempuan" ? "Perempuan" : "Laki-laki";
    const status: DhuhaAttendanceStatus = existing ? existing.status : "Hadir";
    const time = existing ? existing.time : "06:45";
    const notes = existing ? existing.notes : "";
    const isSaved = !!existing;

    // Hitung statistik
    if (status === "Hadir") countHadir++;
    else if (status === "Sakit") countSakit++;
    else if (status === "Izin") countIzin++;
    else if (status === "Alpha") countAlpha++;
    else if (status === "Halangan") countHalangan++;

    return {
      student_id: s.id,
      student_nis: s.nis,
      student_nisn: s.nisn || "",
      student_name: s.nama_lengkap,
      class_name: s.rombel,
      gender,
      status,
      time,
      notes,
      is_saved: isSaved,
    };
  });

  const totalStudents = result.length;
  const attendanceRate =
    totalStudents > 0 ? Math.round((countHadir / totalStudents) * 100) : 0;

  const stats: DhuhaClassStats = {
    total_students: totalStudents,
    count_hadir: countHadir,
    count_sakit: countSakit,
    count_izin: countIzin,
    count_alpha: countAlpha,
    count_halangan: countHalangan,
    attendance_rate: attendanceRate,
  };

  return { students: result, stats };
}

/**
 * Menyimpan atau memperbarui data presensi dhuha secara batch
 */
export async function saveBatchDhuhaAttendance(
  items: Array<{
    student_id: number;
    student_nis: string;
    student_nisn: string;
    student_name: string;
    class_name: string;
    gender: "Laki-laki" | "Perempuan";
    date: string;
    status: DhuhaAttendanceStatus;
    time?: string;
    notes?: string;
    academic_year?: string;
    semester?: string;
    recorder?: string;
  }>
): Promise<number> {
  const db = await getDatabase();
  await initDhuhaTables();

  let savedCount = 0;

  for (const item of items) {
    try {
      await db.run(
        `INSERT INTO dhuha_attendances (
          student_id, student_nis, student_nisn, student_name, class_name,
          gender, date, academic_year, semester, status, time, notes, recorder, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(student_id, date) DO UPDATE SET
          status = excluded.status,
          time = excluded.time,
          notes = excluded.notes,
          recorder = excluded.recorder,
          updated_at = CURRENT_TIMESTAMP`,
        [
          item.student_id,
          item.student_nis,
          item.student_nisn || "",
          item.student_name,
          item.class_name,
          item.gender,
          item.date,
          item.academic_year || getCurrentAcademicYear(),
          item.semester || "Ganjil",
          item.status,
          item.time || "06:45",
          item.notes || "",
          item.recorder || "Guru PAI / Pembina Kesiswaan",
        ]
      );
      savedCount++;
    } catch (e) {
      console.error(`Gagal menyimpan presensi siswa ID ${item.student_id}:`, e);
    }
  }

  return savedCount;
}

/**
 * Mengambil daftar kelas yang tersedia dari master students
 */
export async function getDhuhaAvailableClasses(): Promise<
  Array<{ rombel: string; tingkat: string; total_students: number }>
> {
  const db = await getDatabase();
  return db.all<any[]>(
    `SELECT rombel, tingkat, COUNT(*) as total_students 
     FROM students 
     WHERE status = 'Aktif' 
     GROUP BY rombel, tingkat 
     ORDER BY tingkat ASC, rombel ASC`
  );
}