<?php

require_once __DIR__ . '/../library/auth.php';
 use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

allow_roles(['admin', 'accountant']);

$page_title = "Staff Attendance Report";

$current_session_id = get_current_session_id($pdo);

$school_id = $_SESSION['school_id'];

/*
|--------------------------------------------------------------------------
| MONTH FILTER
|--------------------------------------------------------------------------
*/

$month_filter = $_GET['month'] ?? date('Y-m');

$month = trim($month_filter);

if (!preg_match('/^\d{4}-\d{2}$/', $month)) {

    $month = date('Y-m');
}

/*
|--------------------------------------------------------------------------
| DATE RANGE
|--------------------------------------------------------------------------
*/

$start_date = date('Y-m-01', strtotime($month));

$end_date = date('Y-m-t', strtotime($month));

$total_days = (int) date('t', strtotime($month));

/*
|--------------------------------------------------------------------------
| FETCH STAFF
|--------------------------------------------------------------------------
*/

$staffStmt = $pdo->prepare("
    SELECT
        id,
        name,
        salary
    FROM teachers
    WHERE school_id = ?
    AND status = 'Active'
    ORDER BY name ASC
");

$staffStmt->execute([$school_id]);

$staff_users = $staffStmt->fetchAll(PDO::FETCH_ASSOC);

/*
|--------------------------------------------------------------------------
| FETCH ATTENDANCE
|--------------------------------------------------------------------------
*/

$attendance = [];

$stmt = $pdo->prepare("
    SELECT
        t.id,
        t.name,
        sa.attendance_date,
        sa.status

    FROM teachers t

    LEFT JOIN staff_attendance sa
        ON sa.school_id = t.school_id
        AND sa.staff_id = t.id
        AND sa.session_id = ?
        AND sa.attendance_date BETWEEN ? AND ?

    WHERE t.school_id = ?
    AND t.status = 'Active'

    ORDER BY t.name ASC
");

$stmt->execute([
    $current_session_id,
    $start_date,
    $end_date,
    $school_id
]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    $staff_id = (int) $row['id'];

    if (!isset($attendance[$staff_id])) {

        $attendance[$staff_id] = [
            'name' => $row['name'],
            'days' => array_fill(0, $total_days, '-')
        ];
    }

    if (!empty($row['attendance_date'])) {

        $day_index = (int) date('d', strtotime($row['attendance_date'])) - 1;

        $attendance[$staff_id]['days'][$day_index] = $row['status'];
    }
}

/*
|--------------------------------------------------------------------------
| EXCEL EXPORT
|--------------------------------------------------------------------------
*/

if (
    isset($_GET['export'])
    &&
    $_GET['export'] === 'excel'
) {

    require_once __DIR__ . '/../vendor/autoload.php';

   

    $spreadsheet = new Spreadsheet();

    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setTitle('Attendance Report');

    /*
    |--------------------------------------------------------------------------
    | HEADER
    |--------------------------------------------------------------------------
    */

    $sheet->setCellValue('A1', 'Staff Attendance Report');

    $sheet->setCellValue(
        'A2',
        'Month : ' . date('F Y', strtotime($month))
    );

    /*
    |--------------------------------------------------------------------------
    | TABLE HEADER
    |--------------------------------------------------------------------------
    */

    $row_index = 4;

    $col = 'A';

    $sheet->setCellValue($col++ . $row_index, 'SN');

    $sheet->setCellValue($col++ . $row_index, 'Staff Name');

    for ($d = 1; $d <= $total_days; $d++) {

        $sheet->setCellValue($col++ . $row_index, $d);
    }

    $sheet->setCellValue($col++ . $row_index, 'P');

    $sheet->setCellValue($col++ . $row_index, 'A');

    /*
    |--------------------------------------------------------------------------
    | TABLE BODY
    |--------------------------------------------------------------------------
    */

    $row_index++;

    $sn = 1;

    foreach ($staff_users as $staff) {

        $staff_id = $staff['id'];

        $days = $attendance[$staff_id]['days']
            ?? array_fill(0, $total_days, '-');

        $present_count = 0;

        $absent_count = 0;

        $col = 'A';

        $sheet->setCellValue($col++ . $row_index, $sn++);

        $sheet->setCellValue(
            $col++ . $row_index,
            $staff['name']
        );

        foreach ($days as $day) {

            $short = '-';

            if ($day === 'Present') {

                $short = 'P';

                $present_count++;
            }

            elseif ($day === 'Absent') {

                $short = 'A';

                $absent_count++;
            }

            elseif ($day === 'Half Day') {

                $short = 'H';

                $present_count++;
            }

            elseif ($day === 'Leave') {

                $short = 'L';

                $present_count++;
            }
			elseif ($day === 'Holiday') {

				$short = 'HO';

				$present_count++;
			}

			elseif ($day === 'Weekly Off') {

				$short = 'WO';

				$present_count++;
			}

            $sheet->setCellValue(
                $col++ . $row_index,
                $short
            );
        }

        $sheet->setCellValue(
            $col++ . $row_index,
            $present_count
        );

        $sheet->setCellValue(
            $col++ . $row_index,
            $absent_count
        );

        $row_index++;
    }

    /*
    |--------------------------------------------------------------------------
    | DOWNLOAD
    |--------------------------------------------------------------------------
    */

    if (ob_get_length()) {

        ob_end_clean();
    }

    header(
        'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );

    header(
        'Content-Disposition: attachment; filename="Staff_Attendance_' . $month . '.xlsx"'
    );

    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);

    $writer->save('php://output');

    exit;
}

/*
|--------------------------------------------------------------------------
| PDF EXPORT
|--------------------------------------------------------------------------
*/

if (
    isset($_GET['export'])
    &&
    $_GET['export'] === 'pdf'
) {

    require_once __DIR__ . '/../vendor/autoload.php';

    $pdf = new FPDF('L', 'mm', 'A3');

    $pdf->AddPage();

    $pdf->SetAutoPageBreak(true, 10);

    /*
    |--------------------------------------------------------------------------
    | HEADER
    |--------------------------------------------------------------------------
    */

    $pdf->SetFont('Arial', 'B', 18);

    $pdf->Cell(
        0,
        12,
        'Staff Attendance Report',
        0,
        1,
        'C'
    );

    $pdf->SetFont('Arial', '', 11);

    $pdf->Cell(
        0,
        8,
        'Month : ' . date('F Y', strtotime($month)),
        0,
        1,
        'C'
    );

    $pdf->Ln(5);

    /*
    |--------------------------------------------------------------------------
    | TABLE HEADER
    |--------------------------------------------------------------------------
    */

    $pdf->SetFont('Arial', 'B', 8);

    $pdf->SetFillColor(52, 58, 64);

    $pdf->SetTextColor(255, 255, 255);

    $day_width = 8;

    $pdf->Cell(12, 10, 'SN', 1, 0, 'C', true);

    $pdf->Cell(50, 10, 'Staff Name', 1, 0, 'C', true);

    for ($d = 1; $d <= $total_days; $d++) {

        $pdf->Cell(
            $day_width,
            10,
            $d,
            1,
            0,
            'C',
            true
        );
    }

    $pdf->Cell(18, 10, 'P', 1, 0, 'C', true);

    $pdf->Cell(18, 10, 'A', 1, 1, 'C', true);

    /*
    |--------------------------------------------------------------------------
    | TABLE BODY
    |--------------------------------------------------------------------------
    */

    $pdf->SetFont('Arial', '', 8);

    $pdf->SetTextColor(0, 0, 0);

    $sn = 1;

    foreach ($staff_users as $staff) {

        $staff_id = $staff['id'];

        $days = $attendance[$staff_id]['days']
            ?? array_fill(0, $total_days, '-');

        $present_count = 0;

        $absent_count = 0;

        $pdf->Cell(12, 8, $sn++, 1, 0, 'C');

        $pdf->Cell(
            50,
            8,
            substr($staff['name'], 0, 25),
            1
        );

        foreach ($days as $day) {

            $short = '-';

            if ($day === 'Present') {

                $pdf->SetFillColor(200, 255, 200);

                $short = 'P';

                $present_count++;
            }

            elseif ($day === 'Absent') {

                $pdf->SetFillColor(255, 200, 200);

                $short = 'A';

                $absent_count++;
            }

            elseif ($day === 'Half Day') {

                $pdf->SetFillColor(255, 255, 180);

                $short = 'H';

                $present_count++;
            }

            elseif ($day === 'Leave') {

                $pdf->SetFillColor(200, 220, 255);

                $short = 'L';

                $present_count++;
            }
			elseif ($day === 'Holiday') {

				$pdf->SetFillColor(120, 170, 255);

				$short = 'HO';

				$present_count++;
			}

			elseif ($day === 'Weekly Off') {

				$pdf->SetFillColor(80, 80, 80);

				$pdf->SetTextColor(255, 255, 255);

				$short = 'WO';

				$present_count++;
			}

            else {

                $pdf->SetFillColor(240, 240, 240);
            }
			$pdf->SetTextColor(0, 0, 0);

            $pdf->Cell(
                $day_width,
                8,
                $short,
                1,
                0,
                'C',
                true
            );
        }

        $pdf->SetFillColor(220, 255, 220);

        $pdf->Cell(
            18,
            8,
            $present_count,
            1,
            0,
            'C',
            true
        );

        $pdf->SetFillColor(255, 220, 220);

        $pdf->Cell(
            18,
            8,
            $absent_count,
            1,
            1,
            'C',
            true
        );
    }

    /*
    |--------------------------------------------------------------------------
    | DOWNLOAD
    |--------------------------------------------------------------------------
    */

    if (ob_get_length()) {

        ob_end_clean();
    }

    $pdf->Output(
        'D',
        'Staff_Attendance_' . $month . '.pdf'
    );

    exit;
}

/*
|--------------------------------------------------------------------------
| HTML VIEW
|--------------------------------------------------------------------------
*/

require_once __DIR__ . '/../include/header.php';

require_once __DIR__ . '/../include/sidebar.php';
?>
<div class="content-wrapper">

    <section class="content-header">
        <div class="container-fluid">
            <div class="row mb-3">
                <div class="col-sm-6">
                    <h1 class="m-0">Staff Attendance Report</h1>
                </div>
                <div class="col-sm-6 text-right">
                    <a href="staff_attendance.php" class="btn btn-secondary mr-2">
                        <i class="fas fa-user-check"></i>
                        Mark Daily Attendance
                    </a>
                    <a href="view_staff_attendance.php" class="btn btn-info mr-2">
                        <i class="fas fa-calendar-alt"></i>
                        View Daily Attendance
                    </a>
                </div>
            </div>
        </div>
    </section>

    <section class="content">
        <div class="container-fluid">

            <!-- Month Filter Form -->
            <div class="card card-outline card-info mb-4">
                <div class="card-body">
                    <form method="GET">
                        <div class="row align-items-center">
                            <div class="col-md-3">
                                <label>Month</label>
                                <input
                                    type="month"
                                    name="month"
                                    class="form-control"
                                    required
                                    value="<?php echo htmlspecialchars($month_filter); ?>"
                                >
                            </div>
                            <div class="col-md-2">
                                <button type="submit" class="btn btn-primary mt-4">
                                    <i class="fas fa-search"></i>
                                    View Report
                                </button>
                            </div>
                        </div>
                    </form>
                </div>
            </div>

            <?php if (empty($staff_users)): ?>
                <div class="alert alert-warning">
                    No active staff found.
                </div>
            <?php elseif ($month && $start_date && $end_date): ?>

                <?php if (!empty($attendance)): ?>
                    <div class="card card-outline card-primary">
                        <div class="card-header">
                            <h3 class="card-title">
                                Attendance Report for <?php echo date('F Y', strtotime($month)); ?>
                                (28–31 days shown per month width)
                            </h3>
                            <div class="card-tools">
                                <div class="btn-group">
                                    <a href="staff_attendance_report.php?month=<?php echo urlencode($month); ?>&export=excel"
                                       class="btn btn-sm btn-success">
                                        <i class="fas fa-file-excel"></i>
                                        Excel
                                    </a>
                                    <a href="staff_attendance_report.php?month=<?php echo urlencode($month); ?>&export=pdf"
                                       class="btn btn-sm btn-danger">
                                        <i class="fas fa-file-pdf"></i>
                                        PDF
                                    </a>
                                </div>
                            </div>
                        </div>
                        <div class="card-body table-responsive">
                            <table class="table table-bordered table-sm table-striped">
                                <thead class="bg-dark text-white text-center">
                                    <tr>
                                        <th rowspan="2" style="vertical-align: middle;">SN</th>
                                        <th rowspan="2" style="vertical-align: middle;">Staff Name</th>
                                        <th colspan="<?php echo $total_days; ?>" class="text-center">
                                            Days (1 to <?php echo $total_days; ?>)
                                        </th>
                                    </tr>
                                    <tr>
                                        <?php for ($d = 1; $d <= $total_days; $d++): ?>
                                            <th><?php echo $d; ?></th>
                                        <?php endfor; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php $sn = 1; ?>
                                    <?php foreach ($staff_users as $staff): $id = $staff['id']; ?>
                                        <tr>
                                            <td><?php echo $sn++; ?></td>
                                            <td><strong><?php echo htmlspecialchars($staff['name']); ?></strong></td>
                                            <?php
                                            $days = $attendance[$id]['days'] ?? array_fill(0, $total_days, 'N/A');
                                            for ($i = 0; $i < $total_days; $i++):
                                                $day = $days[$i];
                                                $bg = '';
                                                if ($day === 'Present')      $bg = 'bg-success text-white';
                                                elseif ($day === 'Half Day')  $bg = 'bg-warning text-dark';
                                                elseif ($day === 'Absent')    $bg = 'bg-danger text-white';
                                                elseif ($day === 'Leave')     $bg = 'bg-info text-white';
												elseif ($day === 'Holiday')   $bg = 'bg-primary text-white';

												elseif ($day === 'Weekly Off') $bg = 'bg-dark text-white';
                                                else                         $bg = 'bg-light text-secondary';
                                            ?>
                                                <td class="text-center <?php echo $bg; ?> p-1">
                                                    <?php echo $day; ?>
                                                </td>
                                            <?php endfor; ?>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                        <div class="card-footer small text-muted">
                            <strong>Legend:</strong>
                            <span class="px-2 bg-success text-white rounded">Present</span>
                            <span class="px-2 bg-warning text-dark rounded">Half Day</span>
                            <span class="px-2 bg-danger text-white rounded">Absent</span>
                            <span class="px-2 bg-info text-white rounded">Leave</span>
							<span class="px-2 bg-primary text-white rounded">

							Holiday

						</span>

						<span class="px-2 bg-dark text-white rounded">

							Weekly Off

						</span>
                        </div>
                    </div>
                <?php else: ?>
                    <div class="alert alert-info">
                        No attendance records found for selected month.
                    </div>
                <?php endif; ?>

            <?php endif; ?>

        </div>
    </section>

</div>

<?php require_once __DIR__ . '/../include/footer.php'; ?>