<?php
namespace App\Controllers;

use Core\Database;
use PDO;

class DownloadDataKompreController
{
    private $db;

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

    public function index()
    {
        $tanggal = $_GET['tanggal'] ?? null;

        // Validasi input tanggal (format YYYY-MM-DD)
        if (!$tanggal || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $tanggal)) {
            die('Parameter tanggal tidak valid');
        }

        $start = "$tanggal 00:00:00";
        $end   = "$tanggal 23:59:59";

        $query = "
            SELECT 
                uk.nim, uk.waktu_mulai, uk.waktu_selesai, uk.skor,
                tk.token,
                m.nama, m.jenis_kelamin, m.alamat, m.telpon, m.id_prodi
            FROM tb_ujiankompre uk
            JOIN tb_token_kompre tk ON uk.nim COLLATE utf8mb4_unicode_ci = tk.nim COLLATE utf8mb4_unicode_ci
            JOIN tb_mahasiswa m ON uk.nim COLLATE utf8mb4_unicode_ci = m.nim COLLATE utf8mb4_unicode_ci
            WHERE uk.waktu_mulai BETWEEN :start AND :end
            ORDER BY uk.waktu_mulai DESC
        ";

        $stmt = $this->db->prepare($query);
        $stmt->execute(['start' => $start, 'end' => $end]);
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Nama file Excel
        $nama_file = 'Data_Kompre_' . date('d_M_Y', strtotime($tanggal)) . '.xls';

        // Header Excel
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename={$nama_file}");

        // Fungsi ubah nama bulan ke Indonesia
        function tanggalIndo($datetime) {
            $bulan = [
                1 => 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
                'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
            ];
            $tgl = date('d', strtotime($datetime));
            $bln = (int)date('m', strtotime($datetime));
            $thn = date('Y', strtotime($datetime));
            return "$tgl " . $bulan[$bln] . " $thn";
        }

        // Tabel Excel
        echo "<table border='1'>";
        echo "<tr>
            <th>No</th><th>NIM</th><th>Nama</th><th>Prodi</th><th>JK</th>
            <th>No HP</th><th>Alamat</th><th>Waktu Mulai</th><th>Waktu Selesai</th>
            <th>Skor</th><th>Token</th>
        </tr>";

        foreach ($data as $i => $row) {
            if ($row['id_prodi'] === '86206') {
                $prodi = 'PGSD';
            } elseif ($row['id_prodi'] === '86207') {
                $prodi = 'PGPAUD';
            } else {
                $prodi = 'Lainnya';
            }

            $jk = $row['jenis_kelamin'] === 'Laki-laki' ? 'L' :
                  ($row['jenis_kelamin'] === 'Perempuan' ? 'P' : $row['jenis_kelamin']);

            $tglMulai   = tanggalIndo($row['waktu_mulai']);
            $tglSelesai = tanggalIndo($row['waktu_selesai']);

            echo "<tr>
                <td>" . ($i + 1) . "</td>
                <td>{$row['nim']}</td>
                <td>{$row['nama']}</td>
                <td>{$prodi}</td>
                <td>{$jk}</td>
                <td>{$row['telpon']}</td>
                <td>{$row['alamat']}</td>
                <td>{$tglMulai}</td>
                <td>{$tglSelesai}</td>
                <td>{$row['skor']}</td>
                <td>{$row['token']}</td>
            </tr>";
        }

        echo "</table>";
        exit;
    }
}
