<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once __DIR__ . '/../../config/database.php';
$conn = getConnection();

if (!function_exists('e')) {
    function e($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('coaching_employee_name_from_db')) {
    function coaching_employee_name_from_db(mysqli $conn, int $id): string {
        $sql = "SELECT COALESCE(NULLIF(full_name,''), NULLIF(nama,''), CONCAT('Staff #', id)) AS full_name FROM employees WHERE id = ? LIMIT 1";
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $res = $stmt->get_result();
            $row = $res ? $res->fetch_assoc() : null;
            $stmt->close();
            return (string)($row['full_name'] ?? '-');
        }
        return '-';
    }
}

$month = trim((string)($_GET['month'] ?? date('Y-m')));
$unitFilter = (int)($_GET['unit_filter'] ?? 0);
$coachFilter = (int)($_GET['coach_filter'] ?? 0);
$where = ["DATE_FORMAT(cs.coaching_date, '%Y-%m') = '" . $conn->real_escape_string($month) . "'"];
if ($unitFilter > 0) $where[] = 'ca.unit_id = ' . $unitFilter;
if ($coachFilter > 0) $where[] = 'cs.coach_employee_id = ' . $coachFilter;
$sql = "SELECT cs.coaching_date, cs.topic_title, cc.competency_name, COALESCE(u.name,'-') AS unit_name,
        cs.coach_employee_id, cs.staff_employee_id, csa.score_clinical, csa.score_softskill, csa.score_documentation,
        csa.weighted_score, csa.competency_status
        FROM coaching_sessions cs
        LEFT JOIN coaching_staff_assessments csa ON csa.session_id = cs.id
        LEFT JOIN coaching_competencies cc ON cc.id = cs.competency_id
        LEFT JOIN coaching_assignments ca ON ca.id = cs.assignment_id
        LEFT JOIN units u ON u.id = ca.unit_id
        WHERE " . implode(' AND ', $where) . "
        ORDER BY cs.coaching_date DESC, cs.id DESC";
$res = $conn->query($sql);
$filename = 'Rekap_Coaching_' . preg_replace('/[^0-9\-]/', '', $month) . '.xls';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
echo "<table border='1'>";
echo "<tr><th>Tanggal</th><th>Coach</th><th>Staff Baru</th><th>Topik</th><th>Kompetensi</th><th>Unit</th><th>Skor Klinis</th><th>Skor Soft Skill</th><th>Skor Dokumentasi</th><th>Weighted Score</th><th>Status</th></tr>";
while ($res && $row = $res->fetch_assoc()) {
    echo '<tr>';
    echo '<td>' . e($row['coaching_date']) . '</td>';
    echo '<td>' . e(coaching_employee_name_from_db($conn, (int)$row['coach_employee_id'])) . '</td>';
    echo '<td>' . e(coaching_employee_name_from_db($conn, (int)$row['staff_employee_id'])) . '</td>';
    echo '<td>' . e($row['topic_title']) . '</td>';
    echo '<td>' . e($row['competency_name']) . '</td>';
    echo '<td>' . e($row['unit_name']) . '</td>';
    echo '<td>' . e($row['score_clinical']) . '</td>';
    echo '<td>' . e($row['score_softskill']) . '</td>';
    echo '<td>' . e($row['score_documentation']) . '</td>';
    echo '<td>' . e($row['weighted_score']) . '</td>';
    echo '<td>' . e((string)$row['competency_status']) . '</td>';
    echo '</tr>';
}
echo "</table>";
exit;
