<?php
require_once '../includes/auth.php';
require_once '../includes/functions.php';

if (!isAdmin()) {
    redirect('../login.php');
}

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Ambil parameter filter
$ujian_id = $_POST['ujian_id'] ?? '';
$peserta_id = $_POST['peserta_id'] ?? '';
$station_id = $_POST['station_id'] ?? '';
$export_type = $_POST['export_type'] ?? 'summary';
$include_notes = $_POST['include_notes'] ?? 1;

// Build where conditions
$where_conditions = [];
$params = [];

if (!empty($ujian_id)) {
    $where_conditions[] = "n.ujian_id = ?";
    $params[] = $ujian_id;
}

if (!empty($peserta_id)) {
    $where_conditions[] = "p.id = ?";
    $params[] = $peserta_id;
}

if (!empty($station_id)) {
    $where_conditions[] = "s.id = ?";
    $params[] = $station_id;
}

$where_sql = '';
if (!empty($where_conditions)) {
    $where_sql = "WHERE " . implode(" AND ", $where_conditions);
}

try {
    // Query data berdasarkan tipe export
    if ($export_type === 'detail') {
        $query = "
            SELECT 
                u.nama_ujian,
                u.tanggal_ujian,
                p.nim,
                p.nama as peserta_nama,
                p.program_studi,
                s.nama_station,
                s.total_maksimal,
                ap.nama_aspek,
                ap.nomor_urut,
                n.nilai,
                n.catatan,  -- CATATAN DIAMBIL DARI TABEL PENILAIAN
                us.username as penguji_username,
                n.created_at as waktu_penilaian,
                pg.global_performance
            FROM penilaian n
            INNER JOIN ujian u ON n.ujian_id = u.id
            INNER JOIN peserta p ON n.peserta_id = p.id
            INNER JOIN stations s ON n.station_id = s.id
            INNER JOIN aspek_penilaian ap ON n.aspek_id = ap.id
            INNER JOIN users us ON n.penguji_id = us.id
            LEFT JOIN penilaian_global pg ON (
                n.ujian_id = pg.ujian_id 
                AND n.peserta_id = pg.peserta_id 
                AND n.station_id = pg.station_id 
                AND n.penguji_id = pg.penguji_id
            )
            $where_sql
            ORDER BY u.tanggal_ujian DESC, p.nama, s.nama_station, ap.nomor_urut
        ";
    } else {
        // Untuk summary, kita perlu mengambil catatan jika ada
        if ($include_notes) {
            $query = "
                SELECT 
                    u.nama_ujian,
                    u.tanggal_ujian,
                    p.nim,
                    p.nama as peserta_nama,
                    p.program_studi,
                    s.nama_station,
                    s.total_maksimal,
                    COUNT(DISTINCT n.aspek_id) as total_aspek,
                    SUM(n.nilai) as total_nilai,
                    MAX(n.created_at) as waktu_penilaian,
                    us.username as penguji_username,
                    pg.global_performance,
                    GROUP_CONCAT(DISTINCT n.catatan SEPARATOR ' | ') as catatan_gabungan
                FROM penilaian n
                INNER JOIN ujian u ON n.ujian_id = u.id
                INNER JOIN peserta p ON n.peserta_id = p.id
                INNER JOIN stations s ON n.station_id = s.id
                INNER JOIN users us ON n.penguji_id = us.id
                LEFT JOIN penilaian_global pg ON (
                    n.ujian_id = pg.ujian_id 
                    AND n.peserta_id = pg.peserta_id 
                    AND n.station_id = pg.station_id 
                    AND n.penguji_id = pg.penguji_id
                )
                $where_sql
                GROUP BY n.ujian_id, n.peserta_id, n.station_id, n.penguji_id
                ORDER BY u.tanggal_ujian DESC, p.nama, s.nama_station
            ";
        } else {
            $query = "
                SELECT 
                    u.nama_ujian,
                    u.tanggal_ujian,
                    p.nim,
                    p.nama as peserta_nama,
                    p.program_studi,
                    s.nama_station,
                    s.total_maksimal,
                    COUNT(DISTINCT n.aspek_id) as total_aspek,
                    SUM(n.nilai) as total_nilai,
                    MAX(n.created_at) as waktu_penilaian,
                    us.username as penguji_username,
                    pg.global_performance
                FROM penilaian n
                INNER JOIN ujian u ON n.ujian_id = u.id
                INNER JOIN peserta p ON n.peserta_id = p.id
                INNER JOIN stations s ON n.station_id = s.id
                INNER JOIN users us ON n.penguji_id = us.id
                LEFT JOIN penilaian_global pg ON (
                    n.ujian_id = pg.ujian_id 
                    AND n.peserta_id = pg.peserta_id 
                    AND n.station_id = pg.station_id 
                    AND n.penguji_id = pg.penguji_id
                )
                $where_sql
                GROUP BY n.ujian_id, n.peserta_id, n.station_id, n.penguji_id
                ORDER BY u.tanggal_ujian DESC, p.nama, s.nama_station
            ";
        }
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    $data = $stmt->fetchAll();

    if (empty($data)) {
        die("Tidak ada data untuk di-export.");
    }

    // Set header untuk download CSV
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="penilaian_osce_' . date('Y-m-d_H-i') . '.csv"');

    // Output CSV langsung
    $output = fopen('php://output', 'w');

    // Add BOM for UTF-8
    fputs($output, $bom = ( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

    // Header CSV
    if ($export_type === 'detail') {
        $headers = [
            'No', 
            'Ujian', 
            'Tanggal Ujian', 
            'NIM', 
            'Nama Peserta', 
            'Program Studi', 
            'Station', 
            'Aspek Penilaian', 
            'Nilai', 
            'Total Maksimal Skor',
            'Catatan',  // HEADER CATATAN
            'Global Performance', 
            'Penguji', 
            'Waktu Penilaian'
        ];
    } else {
        if ($include_notes) {
            $headers = [
                'No', 
                'Ujian', 
                'Tanggal Ujian', 
                'NIM', 
                'Nama Peserta', 
                'Program Studi', 
                'Station', 
                'Jumlah Aspek', 
                'Total Nilai', 
                'Total Maksimal Skor',
                'Persentase (%)',
                'Global Performance', 
                'Penguji', 
                'Waktu Penilaian',
                'Catatan'  // HEADER CATATAN UNTUK SUMMARY
            ];
        } else {
            $headers = [
                'No', 
                'Ujian', 
                'Tanggal Ujian', 
                'NIM', 
                'Nama Peserta', 
                'Program Studi', 
                'Station', 
                'Jumlah Aspek', 
                'Total Nilai', 
                'Total Maksimal Skor',
                'Persentase (%)',
                'Global Performance', 
                'Penguji', 
                'Waktu Penilaian'
            ];
        }
    }

    fputcsv($output, $headers, ';');

    // Data CSV
    $no = 1;
    foreach ($data as $item) {
        if ($export_type === 'detail') {
            // Pastikan catatan tidak NULL
            $catatan = $item['catatan'] ?? '';
            $catatan = trim($catatan);
            
            $rowData = [
                $no++,
                $item['nama_ujian'],
                formatTanggal($item['tanggal_ujian']),
                $item['nim'],
                $item['peserta_nama'],
                $item['program_studi'],
                $item['nama_station'],
                $item['nama_aspek'],
                $item['nilai'],
                $item['total_maksimal'] ?? 0,
                $catatan,  // CATATAN DITAMPILKAN
                getGlobalPerformanceText($item['global_performance']),
                $item['penguji_username'],
                formatTanggal($item['waktu_penilaian'])
            ];
        } else {
            // Hitung persentase
            $persentase = 0;
            if (($item['total_maksimal'] ?? 0) > 0) {
                $persentase = ($item['total_nilai'] / $item['total_maksimal']) * 100;
            }
            
            if ($include_notes) {
                // Ambil catatan gabungan untuk summary
                $catatan_gabungan = $item['catatan_gabungan'] ?? '';
                $catatan_gabungan = trim($catatan_gabungan);
                
                $rowData = [
                    $no++,
                    $item['nama_ujian'],
                    formatTanggal($item['tanggal_ujian']),
                    $item['nim'],
                    $item['peserta_nama'],
                    $item['program_studi'],
                    $item['nama_station'],
                    $item['total_aspek'],
                    $item['total_nilai'],
                    $item['total_maksimal'] ?? 0,
                    number_format($persentase, 2),
                    getGlobalPerformanceText($item['global_performance']),
                    $item['penguji_username'],
                    formatTanggal($item['waktu_penilaian']),
                    $catatan_gabungan  // CATATAN GABUNGAN DITAMPILKAN
                ];
            } else {
                $rowData = [
                    $no++,
                    $item['nama_ujian'],
                    formatTanggal($item['tanggal_ujian']),
                    $item['nim'],
                    $item['peserta_nama'],
                    $item['program_studi'],
                    $item['nama_station'],
                    $item['total_aspek'],
                    $item['total_nilai'],
                    $item['total_maksimal'] ?? 0,
                    number_format($persentase, 2),
                    getGlobalPerformanceText($item['global_performance']),
                    $item['penguji_username'],
                    formatTanggal($item['waktu_penilaian'])
                ];
            }
        }
        
        fputcsv($output, $rowData, ';');
    }

    fclose($output);
    exit;
} catch (\Throwable $e) {
    // Tutup resource jika terbuka
    if (isset($output) && is_resource($output)) {
        fclose($output);
    }

    // Kirim status 500 dan pesan error sederhana
    if (!headers_sent()) {
        http_response_code(500);
        header('Content-Type: text/plain; charset=utf-8');
    }

    // Untuk produksi sebaiknya hanya log error, bukan menampilkan detail ke pengguna
    echo 'Terjadi kesalahan saat mengekspor data: ' . $e->getMessage();
    exit;
}

// Helper function
function getGlobalPerformanceText($global_performance) {
    if (empty($global_performance)) {
        return 'Belum Dinilai';
    }
    
    switch($global_performance) {
        case 'tidak_lulus': return 'Tidak Lulus';
        case 'borderline': return 'Borderline';
        case 'lulus': return 'Lulus';
        default: return 'Belum Dinilai';
    }
}


?>