<?php
require_once '../../config.php';
require('../admin_penilaian/fpdf/fpdf.php');

$conn = connectDB();

if (!isset($_GET['cabang']) || !is_numeric($_GET['cabang'])) {
    die("ID cabang lomba tidak valid.");
}
$id_cabang_lomba = (int)$_GET['cabang'];

// Ambil domisili parameter (default: kabupaten_semarang)
$domisili_filter = isset($_GET['domisili']) ? sanitizeInput($_GET['domisili']) : 'kabupaten_semarang';
if (!in_array($domisili_filter, ['kabupaten_semarang', 'kabupaten_demak'])) {
    $domisili_filter = 'kabupaten_semarang';
}

// Ambil nama cabang lomba
$stmt = $conn->prepare("SELECT Bidang_Lomba FROM cabang_lomba WHERE ID = ?");
$stmt->bind_param("i", $id_cabang_lomba);
$stmt->execute();
$stmt->bind_result($bidang_lomba);
$stmt->fetch();
$stmt->close();

// Ambil aspek penilaian
$kriteria_list = [];
$stmt = $conn->prepare("SELECT aspek FROM aspek_penilaian WHERE bidang_lomba = ? ORDER BY id");
$stmt->bind_param("s", $bidang_lomba);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    $kriteria_list[] = trim($row['aspek']);
}
$stmt->close();

// Ambil data juri
$juri_list = [];
$stmt = $conn->prepare("SELECT DISTINCT u.id, u.nama_lengkap 
                        FROM penilaian p 
                        JOIN users u ON p.id_juri = u.id 
                        WHERE p.id_cabang_lomba = ?
                        ORDER BY u.id 
                        LIMIT 3");
$stmt->bind_param("i", $id_cabang_lomba);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    $juri_list[] = $row;
}
$stmt->close();

// Ambil data penilaian khusus domisili kabupaten
$sql = "SELECT 
            p.nisn_peserta,
            COALESCE(dk.Nama_Peserta, dkb.Nama_Peserta, dkb2.Nama_Peserta) AS nama_peserta,
            COALESCE(dk.Nama_Sekolah, dkb.Nama_Sekolah, dkb2.Nama_Sekolah) AS nama_sekolah,
            CASE WHEN p.domisili='kabupaten_semarang' THEN 'Kabupaten Semarang' WHEN p.domisili='kabupaten_demak' THEN 'Kabupaten Demak' ELSE 'Kabupaten' END AS domisili,
            p.id_juri AS juri_id,
            p.nilai_total,
            d.aspek,
            d.nilai
        FROM penilaian p
        JOIN penilaian_detail d ON p.id = d.id_penilaian
        LEFT JOIN domisilikota dk ON p.nisn_peserta = dk.NISN AND p.domisili IN ('kabupaten_semarang', 'kabupaten_demak')
        LEFT JOIN domisilikabupaten dkb ON p.nisn_peserta = dkb.NISN AND p.domisili = 'kabupaten_semarang'
        LEFT JOIN domisilikabupaten2 dkb2 ON p.nisn_peserta = dkb2.NISN AND p.domisili = 'kabupaten_demak'
        WHERE p.id_cabang_lomba = ? AND p.domisili = ?
        ORDER BY p.nisn_peserta, p.id_juri, d.aspek";

$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $id_cabang_lomba, $domisili_filter);
$stmt->execute();
$result = $stmt->get_result();

$data = [];
while ($row = $result->fetch_assoc()) {
    $nisn = $row['nisn_peserta'];
    $juri_id = $row['juri_id'];
    $kriteria = trim($row['aspek']);
    $nilai = (float)$row['nilai'];

    if (!isset($data[$nisn])) {
        $data[$nisn] = [
            'nama' => $row['nama_peserta'],
            'sekolah' => $row['nama_sekolah'],
            'domisili' => $row['domisili'],
            'juri' => [],
            'total_akhir' => 0
        ];
    }

    if (!isset($data[$nisn]['juri'][$juri_id])) {
        $data[$nisn]['juri'][$juri_id] = [
            'nilai_kriteria' => [],
            'nilai_total' => (float)$row['nilai_total']
        ];
    }

    $data[$nisn]['juri'][$juri_id]['nilai_kriteria'][$kriteria] = $nilai;
}
$stmt->close();

// Hitung total nilai akhir setiap peserta
foreach ($data as $nisn => &$peserta) {
    $total_akhir = 0;
    foreach ($juri_list as $juri) {
        $id_juri = $juri['id'];
        $total_akhir += $peserta['juri'][$id_juri]['nilai_total'] ?? 0;
    }
    $peserta['total_akhir'] = $total_akhir;
}
unset($peserta);

// Urutkan berdasarkan total_akhir (descending)
uasort($data, function ($a, $b) {
    return $b['total_akhir'] <=> $a['total_akhir'];
});

// PDF Setup
$pdf = new FPDF('L', 'mm', 'A4');
$pdf->AddPage();
// Header
$pdf->SetFont('Arial', 'B', 16);
$kabupaten_label = ($domisili_filter === 'kabupaten_demak') ? 'KABUPATEN DEMAK' : 'KABUPATEN SEMARANG';
$pdf->Cell(0, 8, 'REKAP NILAI FLS3N TINGKAT CABANG DINAS PENDIDIKAN WILAYAH I - ' . $kabupaten_label, 0, 1, 'C');
$pdf->SetFont('Arial', 'B', 14);
$pdf->Cell(0, 6, 'Lomba: ' . strtoupper($bidang_lomba), 0, 1, 'C');
$pdf->Ln(5);

// Informasi tambahan
$pdf->SetFont('Arial', '', 10);
$pdf->Cell(0, 5, 'Tanggal Cetak: ' . date('d F Y, H:i:s'), 0, 1, 'R');
$pdf->Ln(3);


// Lebar kolom
$lebar_no = 8;
$lebar_nama = 42;
$lebar_sekolah = 34; // Diperlebar dari 24
$lebar_domisili = 22;  // Diperlebar dari 12 untuk menampilkan "Kabupaten Demak"
$lebar_kriteria = 6;
$lebar_total_juri = 8;
$lebar_total_akhir = 10;

$jumlah_juri = count($juri_list);
$jumlah_kriteria = count($kriteria_list);
$lebar_juri = ($lebar_kriteria * $jumlah_kriteria + $lebar_total_juri) * $jumlah_juri;
$total_width = $lebar_no + $lebar_nama + $lebar_sekolah + $lebar_domisili + $lebar_juri + $lebar_total_akhir;
$page_width = $pdf->GetPageWidth();
$marginX = ($page_width - $total_width) / 2;
if ($marginX < 5) $marginX = 5;

$pdf->SetX($marginX);
$pdf->SetFillColor(220, 220, 220);

// Header Baris 1
$pdf->SetFont('Arial', 'B', 6);
$pdf->Cell($lebar_no, 6, 'No', 1, 0, 'C', true);
$pdf->Cell($lebar_nama, 6, 'Nama', 1, 0, 'C', true);
$pdf->Cell($lebar_sekolah, 6, 'Sekolah', 1, 0, 'C', true);
$pdf->Cell($lebar_domisili, 6, 'Domisili', 1, 0, 'C', true);
foreach ($juri_list as $index => $juri) {
    $pdf->Cell(($lebar_kriteria * $jumlah_kriteria) + $lebar_total_juri, 6, 'Juri ' . ($index + 1), 1, 0, 'C', true);
}
$pdf->Cell($lebar_total_akhir, 6, 'Total', 1, 0, 'C', true);
$pdf->Ln();

// Header Baris 2
$pdf->SetX($marginX);
$pdf->Cell($lebar_no, 5, '', 1, 0, 'C', true);
$pdf->Cell($lebar_nama, 5, '', 1, 0, 'C', true);
$pdf->Cell($lebar_sekolah, 5, '', 1, 0, 'C', true);
$pdf->Cell($lebar_domisili, 5, '', 1, 0, 'C', true);
foreach ($juri_list as $index => $juri) {
    foreach ($kriteria_list as $i => $kriteria) {
        $pdf->Cell($lebar_kriteria, 5, 'K' . ($i + 1), 1, 0, 'C', true);
    }
    $pdf->Cell($lebar_total_juri, 5, 'Tot', 1, 0, 'C', true);
}
$pdf->Cell($lebar_total_akhir, 5, '', 1, 0, 'C', true);
$pdf->Ln();

// Data Peserta
$pdf->SetFont('Arial', '', 5.5);
$no = 1;
foreach ($data as $peserta) {
    $pdf->SetX($marginX);
    $pdf->Cell($lebar_no, 5, $no++, 1, 0, 'C');
    $pdf->Cell($lebar_nama, 5, $peserta['nama'], 1, 0, 'L');
    $pdf->Cell($lebar_sekolah, 5, $peserta['sekolah'], 1, 0, 'L');
    $pdf->Cell($lebar_domisili, 5, $peserta['domisili'], 1, 0, 'C');

    $total_akhir = 0;
    foreach ($juri_list as $juri) {
        $id_juri = $juri['id'];
        $nilai_juri = $peserta['juri'][$id_juri]['nilai_kriteria'] ?? [];
        $nilai_total = $peserta['juri'][$id_juri]['nilai_total'] ?? 0;

        foreach ($kriteria_list as $kriteria) {
            $nilai = isset($nilai_juri[$kriteria]) ? $nilai_juri[$kriteria] : 0;
            $pdf->Cell($lebar_kriteria, 5, number_format($nilai, 1), 1, 0, 'C');
        }

        $pdf->Cell($lebar_total_juri, 5, number_format($nilai_total, 1), 1, 0, 'C');
        $total_akhir += $nilai_total;
    }

    $pdf->Cell($lebar_total_akhir, 5, number_format($total_akhir, 1), 1, 0, 'C');
    $pdf->Ln();
}

$pdf->Output("I", "Rekap Nilai FLS3N $bidang_lomba - kabupaten.pdf");
