<?php
namespace App\Models;

use Core\Database;
use Core\Model;

class Absensi
{
    private $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }

    public function getMahasiswaByMatkul($idMatkul)
    {
        $this->db->query("SELECT m.nim, m.nama FROM tb_krs k
                          JOIN tb_mahasiswa m ON k.nim = m.nim
                          WHERE k.id_matkul = :id");
        $this->db->bind('id', $idMatkul);
        return $this->db->resultSet();
    }

    public function simpan($idMatkul, $nim, $pertemuan, $status)
    {
        $this->db->query("REPLACE INTO tb_presensi (id_matkul, nim, pertemuan, status)
                          VALUES (:id_matkul, :nim, :pertemuan, :status)");
        $this->db->bind('id_matkul', $idMatkul);
        $this->db->bind('nim', $nim);
        $this->db->bind('pertemuan', $pertemuan);
        $this->db->bind('status', $status);
        $this->db->execute();
    }

	public function getLastPertemuan($idDmk)
	{
		$detail = $this->getDetailDmk($idDmk);
		if (!$detail) {
			return 0;
		}

		$stmt = $this->db->prepare("
			SELECT MAX(pertemuan_ke) as last_pertemuan 
			FROM tb_absensi 
			WHERE kode_mk = :kode_mk 
			  AND kode_kelas = :kode_kelas 
			  AND tahun_akademik = :tahun_akademik
		");
		$stmt->execute([
			'kode_mk' => $detail['kode_mk'],
			'kode_kelas' => $detail['kode_kelas'],
			'tahun_akademik' => $detail['tahun_akademik']
		]);
		$row = $stmt->fetch();
		return $row['last_pertemuan'] ?? 0;
	}

	public function hapusDuplikatSimpanTertua($idKrs, $kodeMk, $kodeKelas, $pertemuan)
	{
		$sql = "
			DELETE FROM tb_absensi
			WHERE id_krs = :id_krs
			AND kode_mk = :kode_mk
			AND kode_kelas = :kode_kelas
			AND pertemuan_ke = :pertemuan
			AND id_absensi NOT IN (
				SELECT id_absensi FROM (
					SELECT MIN(id_absensi) AS id_absensi
					FROM tb_absensi
					WHERE id_krs = :id_krs
					AND kode_mk = :kode_mk
					AND kode_kelas = :kode_kelas
					AND pertemuan_ke = :pertemuan
					GROUP BY id_krs, kode_mk, kode_kelas, pertemuan_ke
				) AS temp
			)
		";

		$stmt = $this->db->prepare($sql);
		$stmt->execute([
			'id_krs' => $idKrs,
			'kode_mk' => $kodeMk,
			'kode_kelas' => $kodeKelas,
			'pertemuan' => $pertemuan
		]);
	}

	public function simpanAbsensi($data)
	{
		// Hapus duplikat terlebih dahulu (simpan yang paling lama/tanggal tertua)
		$this->hapusDuplikatSimpanTertua($data['id_krs'], $data['kode_mk'], $data['kode_kelas'], $data['pertemuan_ke']);

		// Hitung nilai_absensi berdasarkan status_absensi
		$nilai_absensi = ($data['status'] === 'Hadir') ? (100 / 16) : 0;

		$stmt = $this->db->prepare("
			INSERT INTO tb_absensi 
			(id_krs, kode_mk, kode_kelas, pertemuan_ke, status_absensi, tanggal_absensi, nilai_absensi, tahun_akademik, latitude, longitude, keterangan_lokasi)
			VALUES
			(:id_krs, :kode_mk, :kode_kelas, :pertemuan_ke, :status_absensi, :tanggal_absensi, :nilai_absensi, :tahun_akademik, :latitude, :longitude, :keterangan_lokasi)
		");

		$stmt->execute([
			'id_krs' => $data['id_krs'],
			'kode_mk' => $data['kode_mk'],
			'kode_kelas' => $data['kode_kelas'],
			'pertemuan_ke' => $data['pertemuan_ke'],
			'status_absensi' => $data['status'], // map ke status_absensi
			'tanggal_absensi' => $data['tanggal_absensi'],
			'nilai_absensi' => $nilai_absensi, // pakai nilai yang dihitung
			'tahun_akademik' => $data['tahun_akademik'],
			'latitude' => $data['latitude'],
			'longitude' => $data['longitude'],
			'keterangan_lokasi' => $data['keterangan_lokasi']
		]);
	}

	public function getMahasiswaByDmk($id_dmk)
	{
		$stmt = $this->db->prepare("
			SELECT 
				mhs.nim, 
				mhs.nama, 
				krs.id_krs, 
				kelas.kode_kelas
			FROM tb_dosen_mk dmk
			JOIN tb_krs krs 
				ON krs.kode_mk = dmk.kode_mk 
				AND krs.tahun_akademik = dmk.tahun_akademik
				-- Jangan join pake krs.kode_kelas karena kolomnya ga ada
			JOIN tb_mahasiswa mhs ON krs.nim = mhs.nim
			JOIN tb_kelas kelas 
				ON kelas.nim = mhs.nim 
				AND kelas.tahun_akademik = dmk.tahun_akademik
				AND kelas.kode_kelas = dmk.kode_kelas
			WHERE dmk.id_dmk = :id_dmk
		");
		$stmt->bindValue(':id_dmk', $id_dmk);
		$stmt->execute();
		return $stmt->fetchAll();
	}

	public function getIdKrs($nim, $kode_mk, $tahun_akademik)
	{
		$stmt = $this->db->prepare("SELECT id_krs FROM tb_krs WHERE nim = ? AND kode_mk = ? AND tahun_akademik = ?");
		$stmt->execute([$nim, $kode_mk, $tahun_akademik]);
		$row = $stmt->fetch();
		return $row ? $row['id_krs'] : null;
	}

	public function getDetailDmk($idDmk)
	{
		$stmt = $this->db->prepare("SELECT kode_mk, kode_kelas, tahun_akademik FROM tb_dosen_mk WHERE id_dmk = ?");
		$stmt->execute([$idDmk]);
		return $stmt->fetch();
	}

	public function getAbsensiMahasiswaByPertemuan($idDmk, $pertemuanKe)
	{
		$detail = $this->getDetailDmk($idDmk);
		if (!$detail) return [];

		$stmt = $this->db->prepare("
			SELECT 
				m.nim, 
				m.nama, 
				a.id_absensi, 
				a.status_absensi
			FROM tb_krs k
			INNER JOIN tb_mahasiswa m ON k.nim = m.nim
			INNER JOIN tb_kelas kl ON kl.nim = m.nim
			LEFT JOIN tb_absensi a 
				ON a.id_krs = k.id_krs 
				AND a.pertemuan_ke = :pertemuan_ke
			WHERE k.kode_mk = :kode_mk
			  AND k.tahun_akademik = :tahun_akademik
			  AND kl.kode_kelas = :kode_kelas
			  AND kl.tahun_akademik = :tahun_akademik
			ORDER BY m.nim
		");
		$stmt->execute([
			'pertemuan_ke' => $pertemuanKe,
			'kode_mk' => $detail['kode_mk'],
			'kode_kelas' => $detail['kode_kelas'],
			'tahun_akademik' => $detail['tahun_akademik'],
		]);
		return $stmt->fetchAll();
	}

	public function getAbsensiForEdit($idDmk, $pertemuan, $tahunAkademik)
	{
		$detail = $this->getDetailDmk($idDmk);

		$stmt = $this->db->prepare("
			SELECT 
				mhs.nim, 
				mhs.nama, 
				a.status_absensi 
			FROM tb_krs k
			JOIN tb_mahasiswa mhs ON k.nim = mhs.nim
			JOIN tb_kelas kelas ON kelas.nim = mhs.nim
				AND kelas.kode_kelas = :kode_kelas 
				AND kelas.tahun_akademik = :tahun_akademik
			LEFT JOIN tb_absensi a ON k.id_krs = a.id_krs AND a.pertemuan_ke = :pertemuan
			WHERE k.kode_mk = :kode_mk 
				AND k.tahun_akademik = :tahun_akademik
			ORDER BY mhs.nim
		");
		$stmt->execute([
			'kode_mk' => $detail['kode_mk'],
			'kode_kelas' => $detail['kode_kelas'],
			'tahun_akademik' => $tahunAkademik,
			'pertemuan' => $pertemuan
		]);

		return $stmt->fetchAll();
	}

	public function updateAbsensiMahasiswa($idKrs, $pertemuan, $status, $latitude, $longitude, $keterangan)
	{
		// Ambil kode_mk dan tahun_akademik dari tb_krs
		$stmt = $this->db->prepare("SELECT kode_mk, tahun_akademik, nim FROM tb_krs WHERE id_krs = ?");
		$stmt->execute([$idKrs]);
		$row = $stmt->fetch();

		if ($row) {
			$kodeMk = $row['kode_mk'];
			$tahunAkademik = $row['tahun_akademik'];
			$nim = $row['nim'];

			// Ambil kode_kelas dari tb_kelas berdasarkan nim dan tahun_akademik
			$stmt = $this->db->prepare("SELECT kode_kelas FROM tb_kelas WHERE nim = ? AND tahun_akademik = ?");
			$stmt->execute([$nim, $tahunAkademik]);
			$kelas = $stmt->fetch();

			if ($kelas) {
				$kodeKelas = $kelas['kode_kelas'];

				// Hapus duplikat absensi jika perlu
				$this->hapusDuplikatSimpanTertua($idKrs, $kodeMk, $kodeKelas, $pertemuan);
			}
		}

		// Hitung nilai_absensi berdasarkan status_absensi
		$nilai_absensi = ($status === 'Hadir') ? (100 / 16) : 0;

		// Update data absensi termasuk nilai_absensi
		$stmt = $this->db->prepare("
			UPDATE tb_absensi
			SET 
				status_absensi = :status,
				nilai_absensi = :nilai_absensi,
				keterangan_lokasi = :keterangan
			WHERE id_krs = :id_krs
			AND pertemuan_ke = :pertemuan
		");

		return $stmt->execute([
			'status' => $status,
			'nilai_absensi' => $nilai_absensi,
			'keterangan' => $keterangan,
			'id_krs' => $idKrs,
			'pertemuan' => $pertemuan
		]);
	}

	public function checkAbsensiExists($idKrs, $pertemuan)
	{
		$stmt = $this->db->prepare("
			SELECT 1 FROM tb_absensi 
			WHERE id_krs = :id_krs AND pertemuan_ke = :pertemuan
		");
		$stmt->execute([
			'id_krs' => $idKrs,
			'pertemuan' => $pertemuan
		]);
		return $stmt->fetch() !== false;
	}

public function getRekapAbsensi16($idDmk, $tahunAkademik)
{
    $detail = $this->getDetailDmk($idDmk);
    if (!$detail) {
        return [];
    }

    $sql = "
        SELECT 
            mhs.nim,
            mhs.nama,

            MAX(CASE WHEN a.pertemuan_ke = 1  THEN a.status_absensi END) AS p1,
            MAX(CASE WHEN a.pertemuan_ke = 2  THEN a.status_absensi END) AS p2,
            MAX(CASE WHEN a.pertemuan_ke = 3  THEN a.status_absensi END) AS p3,
            MAX(CASE WHEN a.pertemuan_ke = 4  THEN a.status_absensi END) AS p4,
            MAX(CASE WHEN a.pertemuan_ke = 5  THEN a.status_absensi END) AS p5,
            MAX(CASE WHEN a.pertemuan_ke = 6  THEN a.status_absensi END) AS p6,
            MAX(CASE WHEN a.pertemuan_ke = 7  THEN a.status_absensi END) AS p7,
            MAX(CASE WHEN a.pertemuan_ke = 8  THEN a.status_absensi END) AS p8,
            MAX(CASE WHEN a.pertemuan_ke = 9  THEN a.status_absensi END) AS p9,
            MAX(CASE WHEN a.pertemuan_ke = 10 THEN a.status_absensi END) AS p10,
            MAX(CASE WHEN a.pertemuan_ke = 11 THEN a.status_absensi END) AS p11,
            MAX(CASE WHEN a.pertemuan_ke = 12 THEN a.status_absensi END) AS p12,
            MAX(CASE WHEN a.pertemuan_ke = 13 THEN a.status_absensi END) AS p13,
            MAX(CASE WHEN a.pertemuan_ke = 14 THEN a.status_absensi END) AS p14,
            MAX(CASE WHEN a.pertemuan_ke = 15 THEN a.status_absensi END) AS p15,
            MAX(CASE WHEN a.pertemuan_ke = 16 THEN a.status_absensi END) AS p16

        FROM tb_krs k
        JOIN tb_mahasiswa mhs ON k.nim = mhs.nim
        JOIN tb_kelas kl 
            ON kl.nim = mhs.nim
            AND kl.kode_kelas = :kode_kelas
            AND kl.tahun_akademik = :tahun_akademik
        LEFT JOIN tb_absensi a 
            ON a.id_krs = k.id_krs

        WHERE k.kode_mk = :kode_mk
          AND k.tahun_akademik = :tahun_akademik

        GROUP BY mhs.nim, mhs.nama
        ORDER BY mhs.nim
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->execute([
        'kode_mk' => $detail['kode_mk'],
        'kode_kelas' => $detail['kode_kelas'],
        'tahun_akademik' => $tahunAkademik
    ]);

    return $stmt->fetchAll();
}

}
