<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

ini_set('display_errors', 1);
error_reporting(E_ALL);

$host = "localhost";
$db   = "school_checkin";
$user = "root";
$pass = "";
$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

$gradeOptions = ["Nursery", "Kindergarten", "Grade 1", "Grade 2", "Grade 3", "Grade 4", "Grade 5", "Grade 6"];
$monthOptions = ["June","July","August","September","October","November","December","January","February","March"];
$schoolYearOptions = [];
for ($y = 2020; $y <= date("Y")+1; $y++) $schoolYearOptions[] = $y."-".($y+1);

$school_id   = "404833";
$school_name = "Siocon District Adventist Elementary";
$principal   = "WINDEL BALANSAG DUMAYO";
$templateFile = "SF2_TEMPLATE.xls";

// Section options
$sectionOptions = [];
$res2 = $conn->query("SELECT DISTINCT section FROM students WHERE section IS NOT NULL AND section <> '' ORDER BY section");
if ($res2) {
    while ($row = $res2->fetch_assoc()) {
        $sectionOptions[] = $row['section'];
    }
}

// Helper function for attendance label: P if both present, A otherwise
function getAttendanceLabel($am_absent, $pm_absent) {
    if ($am_absent == 0 && $pm_absent == 0) return 'P';
    return 'A';
}

if (isset($_POST['download_sf2'])) {
    $grade       = $_POST['grade'];
    $section     = $_POST['section'] ?? "";
    $month       = $_POST['month'];
    $school_year = $_POST['school_year'];

    if (!file_exists($templateFile)) die("ERROR: Template '$templateFile' not found.");
    $spreadsheet = IOFactory::load($templateFile);
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValue('F3', $school_id);
    $sheet->setCellValue('H3', $school_year);
    $sheet->setCellValue('D4', $school_name);
    $sheet->setCellValue('M3', $month);
    $sheet->setCellValue('AA4', $grade);
    $sheet->setCellValue('U4', $section);
    $sheet->setCellValue('AM4', '');

    // Gender summary
    $sql_gender = "SELECT 
        SUM(CASE WHEN LOWER(sex) IN ('male', 'm') THEN 1 ELSE 0 END) as males,
        SUM(CASE WHEN LOWER(sex) IN ('female', 'f') THEN 1 ELSE 0 END) as females,
        COUNT(*) as total
        FROM students WHERE grade = ?";
    $types_gender = "s";
    $params_gender = [$grade];
    if ($section) {
        $sql_gender .= " AND section = ?";
        $types_gender .= "s";
        $params_gender[] = $section;
    }
    $stmt_gender = $conn->prepare($sql_gender);
    if ($stmt_gender) {
        $stmt_gender->bind_param($types_gender, ...$params_gender);
        $stmt_gender->execute();
        $stmt_gender->bind_result($enrol_male, $enrol_female, $enrol_total);
        $stmt_gender->fetch();
        $stmt_gender->close();
    } else {
        $enrol_male = $enrol_female = $enrol_total = 0;
    }

    // List students for main body
    $sql = "SELECT lrn, first_name, middle_name, last_name, grade, sex, section
            FROM students
            WHERE grade = ?";
    $types = "s";
    $params = [$grade];
    if ($section) {
        $sql .= " AND section = ?";
        $types .= "s";
        $params[] = $section;
    }
    $sql .= " ORDER BY 
        CASE WHEN LOWER(sex) IN ('male', 'm') THEN 0 
             WHEN LOWER(sex) IN ('female', 'f') THEN 1 
             ELSE 2 END,
        TRIM(last_name), TRIM(first_name), TRIM(middle_name)";
    
    $stmt = $conn->prepare($sql);
    if (!$stmt) die("Prepare failed: ".$conn->error);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();

    // Get month days
    $school_days = 0;
    $year = in_array($month, ["June", "July", "August", "September", "October", "November", "December"]) 
        ? substr($school_year, 0, 4) : substr($school_year, 5, 4);
    $month_num = array_search($month, $monthOptions) + 1;
    $num_days = cal_days_in_month(CAL_GREGORIAN, $month_num, $year);
    for ($i = 1; $i <= $num_days; $i++) {
        $date = date_create("$year-" . sprintf("%02d", $month_num) . "-" . sprintf("%02d", $i));
        if (date_format($date, "N") < 6) $school_days++;
    }

    // Fetch attendance logs for the month and section
    $attendance = []; // $attendance[lrn][day] = ['am_absent'=>0/1, 'pm_absent'=>0/1]
    $first_day = "$year-" . sprintf("%02d", $month_num) . "-01";
    $last_day = "$year-" . sprintf("%02d", $month_num) . "-$num_days";

    $sql_att = "SELECT lrn, DAY(date) as d, am_absent, pm_absent
                FROM sf2
                WHERE date BETWEEN ? AND ?";
    $params_att = [$first_day, $last_day];

    if ($section) {
        $sql_att .= " AND lrn IN (SELECT lrn FROM students WHERE grade = ? AND section = ?)";
        $params_att[] = $grade;
        $params_att[] = $section;
    } else {
        $sql_att .= " AND lrn IN (SELECT lrn FROM students WHERE grade = ?)";
        $params_att[] = $grade;
    }

    $stmt_att = $conn->prepare($sql_att);
    $stmt_att->bind_param(str_repeat("s", count($params_att)), ...$params_att);
    $stmt_att->execute();
    $res_att = $stmt_att->get_result();
    while ($row_att = $res_att->fetch_assoc()) {
        $attendance[$row_att['lrn']][intval($row_att['d'])] = [
            'am_absent' => $row_att['am_absent'],
            'pm_absent' => $row_att['pm_absent']
        ];
    }
    $stmt_att->close();

    // Write students and attendance to Excel
    $row = 8;
    $num = 1;
    $male_count = 0;
    $female_count = 0;
    while ($stud = $result->fetch_assoc()) {
        $fullname = trim($stud['last_name']) . ', ' . trim($stud['first_name']) . (trim($stud['middle_name']) ? ' ' . trim($stud['middle_name']) : '');

        $sheet->setCellValue("A$row", $num);
        $sheet->setCellValue("B$row", (strtolower($stud['grade']) == "nursery") ? "" : $stud['lrn']);
        $sheet->setCellValue("C$row", $fullname);
        $sheet->setCellValue("D$row", !empty($stud['sex']) ? ucfirst(strtolower($stud['sex'])) : "");

        // Attendance by day: P if both present, A if at least 1 absent
        $col = 'E';
        for ($i = 1; $i <= 31; $i++) {
            if ($i <= $num_days) {
                $att = $attendance[$stud['lrn']][$i] ?? null;
                if ($att) {
                    $label = getAttendanceLabel($att['am_absent'], $att['pm_absent']);
                    $sheet->setCellValue($col . $row, $label);
                } else {
                    $sheet->setCellValue($col . $row, '');
                }
            } else {
                $sheet->setCellValue($col . $row, '');
            }
            $col++;
        }

        $student_sex = strtolower($stud['sex']);
        if ($student_sex === 'male' || $student_sex === 'm') {
            $male_count++;
        } elseif ($student_sex === 'female' || $student_sex === 'f') {
            $female_count++;
        }

        $row++;
        $num++;
    }

    // Clear remaining rows
    for ($i = $row; $i <= 48; $i++) {
        $sheet->setCellValue("A$i", "");
        $sheet->setCellValue("B$i", "");
        $sheet->setCellValue("C$i", "");
        $sheet->setCellValue("D$i", "");
        $col = 'E';
        for ($j = 0; $j < 31; $j++) {
            $sheet->setCellValue($col.$i, '');
            $col++;
        }
    }

    // --- FINAL SF2 SUMMARY TABLE (NO OVERLAP, EXACT LAYOUT LIKE SCREENSHOT) ---
    foreach (range(53, 62) as $r) {
        foreach (['AR','AS','AT','AU'] as $col) {
            $cell = $col.$r;
            if ($sheet->getCell($cell)->isInMergeRange()) {
                $sheet->unmergeCells($sheet->getCell($cell)->getMergeRange());
            }
            $sheet->setCellValueExplicit($cell, '', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
        }
    }

    $sheet->setCellValue('AR53', 'Enrolment as of (1st Friday of June)');
    $sheet->setCellValue('AS53', $enrol_male);
    $sheet->setCellValue('AT53', $enrol_female);
    $sheet->setCellValue('AU53', $enrol_total);

    $sheet->setCellValue('AR54', 'Late enrolment during the month');
    $sheet->setCellValue('AS54', 0);
    $sheet->setCellValue('AT54', 0);
    $sheet->setCellValue('AU54', 0);

    $sheet->setCellValue('AR55', 'Registered Learners as of end of month');
    $sheet->setCellValue('AS55', $enrol_male);
    $sheet->setCellValue('AT55', $enrol_female);
    $sheet->setCellValue('AU55', $enrol_total);

    $sheet->setCellValue('AR56', 'Percentage of Enrolment as of end of month');
    $sheet->setCellValue('AS56', '');
    $sheet->setCellValue('AT56', '');
    $sheet->setCellValue('AU56', '');

    $sheet->setCellValue('AR57', 'Average Daily Attendance');
    $sheet->setCellValue('AS57', '');
    $sheet->setCellValue('AT57', '');
    $sheet->setCellValue('AU57', '');

    $sheet->setCellValue('AR58', 'Percentage of Attendance for the month');
    $sheet->setCellValue('AS58', '');
    $sheet->setCellValue('AT58', '');
    $sheet->setCellValue('AU58', '');

    $sheet->setCellValue('AR59', 'Number of students absent for 5 consecutive days');
    $sheet->setCellValue('AS59', '');
    $sheet->setCellValue('AT59', '');
    $sheet->setCellValue('AU59', '');

    $sheet->setCellValue('AR60', 'Dropped out');
    $sheet->setCellValue('AS60', '');
    $sheet->setCellValue('AT60', '');
    $sheet->setCellValue('AU60', '');

    $sheet->setCellValue('AR61', 'Transferred out');
    $sheet->setCellValue('AS61', '');
    $sheet->setCellValue('AT61', '');
    $sheet->setCellValue('AU61', '');

    $sheet->setCellValue('AR62', 'Transferred in');
    $sheet->setCellValue('AS62', '');
    $sheet->setCellValue('AT62', '');
    $sheet->setCellValue('AU62', '');

    $sheet->getStyle('AS53:AU62')->getFont()->getColor()->setRGB('000000');

    $sheet->setCellValue('AW72', '');
    $sheet->setCellValue('AW76', '');
    $sheet->setCellValue('AN82', $principal);

    // ✅ OUTPUT
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="SF2_' . $grade . '_' . $section . '_' . $month . '.xls"');
    header('Cache-Control: max-age=0');
    $writer = IOFactory::createWriter($spreadsheet, 'Xls');
    $writer->save('php://output');
    exit;
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Download SF2</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background: #222;
            color: #fff;
            padding: 32px;
            transition: background 0.4s, color 0.4s;
        }
        .container {
            max-width: 520px;
            margin: auto;
            background: #333;
            border-radius: 14px;
            padding: 32px 24px 18px 24px;
            box-shadow: 0 4px 20px #0007;
        }
        h2 { text-align: center; }
        label { display: block; margin-top: 16px; margin-bottom: 8px; }
        select, button {
            padding: 7px 12px;
            font-size: 1em;
            border-radius: 6px;
            border: none;
            margin-bottom: 12px;
        }
        button {
            background: #1fd655;
            color: #222;
            font-weight: bold;
            cursor: pointer;
        }
        button:hover { background: #11a13f; }
        .toggle-dark {
            float: right;
            margin-top: -35px;
            margin-bottom: 15px;
            border-radius: 20px;
            border: none;
            background: #555;
            color: #ffd93d;
            font-size: 1em;
            padding: 6px 16px;
            cursor: pointer;
            transition: background 0.3s, color 0.3s;
        }
        .toggle-dark:hover {
            background: #ffd93d;
            color: #222;
        }
        body.dark-mode { background: #fff; color: #222; }
        body.dark-mode .container { background: #f3f3f3; color: #222; }
        body.dark-mode button { background: #333; color: #ffd93d; }
        body.dark-mode .toggle-dark { background: #ffd93d; color: #222; }
        body.dark-mode .toggle-dark:hover { background: #333; color: #ffd93d; }
    </style>
    <script>
    function toggleDarkMode() {
        document.body.classList.toggle("dark-mode");
        localStorage.setItem("sf2dm", document.body.classList.contains("dark-mode") ? "1" : "0");
    }
    window.onload = function() {
        if(localStorage.getItem("sf2dm")==="1") {
            document.body.classList.add("dark-mode");
        }
    }
    </script>
</head>
<body>
    <div class="container">
        <button class="toggle-dark" type="button" onclick="toggleDarkMode()">🌗 Toggle Dark/Light</button>
        <h2>Download SF2</h2>
        <form method="POST">
            <label for="grade">Grade:</label>
            <select name="grade" required>
                <option value="">-- Select Grade --</option>
                <?php foreach ($gradeOptions as $g): ?>
                    <option value="<?= htmlspecialchars($g) ?>"><?= htmlspecialchars($g) ?></option>
                <?php endforeach; ?>
            </select>
            <?php if (!empty($sectionOptions)): ?>
            <label for="section">Section:</label>
            <select name="section">
                <option value="">-- Select Section --</option>
                <?php foreach ($sectionOptions as $s): ?>
                    <option value="<?= htmlspecialchars($s) ?>"><?= htmlspecialchars($s) ?></option>
                <?php endforeach; ?>
            </select>
            <?php endif; ?>
            <label for="month">Month:</label>
            <select name="month" required>
                <?php foreach ($monthOptions as $m): ?>
                    <option value="<?= htmlspecialchars($m) ?>"><?= htmlspecialchars($m) ?></option>
                <?php endforeach; ?>
            </select>
            <label for="school_year">School Year:</label>
            <select name="school_year" required>
                <?php foreach ($schoolYearOptions as $sy): ?>
                    <option value="<?= htmlspecialchars($sy) ?>"><?= htmlspecialchars($sy) ?></option>
                <?php endforeach; ?>
            </select>
            <button type="submit" name="download_sf2">Download SF2</button>
        </form>
    </div>
</body>
</html>