<?php
session_start();

// 1. Strict Session Check
if (empty($_SESSION['branch'])) {
    header("Location: index.php");
    die();
}

require_once 'system/conn.php';

// 2. Centralized Date Logic (Target: Previous Month)
$date = explode('-', date("Y-m-d", strtotime("last day of previous month")));
$month = $date[1];
$year  = $date[0];

$dateObj   = DateTime::createFromFormat('!m', $month);
$monthName = strtoupper($dateObj->format('F'));
$branch_id = $_SESSION['branch'];

// 3. Fetch Branch Name Safely
$query = $conn->prepare("SELECT name FROM branch WHERE id = ?");
$query->execute([$branch_id]);
$result = $query->fetch(PDO::FETCH_ASSOC);
$br = $result ? $result['name'] : 'BRANCH';

// 4. Load PhpSpreadsheet
require "excel_test/vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('OT sheet');

// Formatting & Column Auto-sizing
$sheet->mergeCells('A1:F1');
foreach (range('A', 'F') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// 5. Styles Configuration (Professional Blue Header)
$styleMainHeader = [
    'font' => [
        'bold' => true,
        'color' => ['argb' => 'FFFFFFFF'], // White Text
        'size' => 12
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FF0056B3'], // Professional Blue
    ],
];

$styleSubHeader = [
    'font' => ['bold' => true],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    'borders' => [
        'bottom' => ['borderStyle' => Border::BORDER_THIN],
    ],
];

$styleTotalRow = [
    'font' => ['bold' => true],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FFF2F2F2'], // Light Grey
    ],
];

// Apply Styles
$sheet->getStyle('A1')->applyFromArray($styleMainHeader);
$sheet->getRowDimension('1')->setRowHeight(30); // Taller header
$sheet->getStyle('A3:F3')->applyFromArray($styleSubHeader);

// 6. Secure SQL Query with UNION for Total
$sql = "SELECT `emp_name`, `designation`, `emp_id`, `ot_days`, `amount` 
        FROM ot_details 
        WHERE `branch` = ? AND `month` = ? AND `year` = ? 
        UNION ALL
        SELECT 'z', '', 'TOTAL', SUM(ot_days), SUM(amount) 
        FROM ot_details 
        WHERE `branch` = ? AND `month` = ? AND `year` = ? 
        ORDER BY emp_name ASC";

$stmt = $conn->prepare($sql);
$stmt->execute([$branch_id, $month, $year, $branch_id, $month, $year]);

// 7. Set Cell Headers
$sheet->setCellValue('A1', $br . ' OT GROSS SHEET FOR ' . $monthName . ' ' . $year);
$sheet->setCellValue('A3', 'SR NO');
$sheet->setCellValue('B3', 'NAME');
$sheet->setCellValue('C3', 'DESIGNATION');
$sheet->setCellValue('D3', 'EMPID');
$sheet->setCellValue('E3', 'OT DAYS');
$sheet->setCellValue('F3', 'AMOUNT');

// 8. Populate Data Rows
$i = 4;
$sr_no = 1;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $isTotal = ($row['emp_name'] === 'z');
    
    $sheet->setCellValue('A' . $i, $isTotal ? '' : $sr_no);
    $sheet->setCellValue('B' . $i, $isTotal ? 'TOTAL' : $row['emp_name']);
    $sheet->setCellValue('C' . $i, $row['designation']);
    $sheet->setCellValue('D' . $i, $row['emp_id']);
    $sheet->setCellValue('E' . $i, $row['ot_days']);
    $sheet->setCellValue('F' . $i, $row['amount']);
    
    // Apply bold and light grey background to total row
    if ($isTotal) {
        $sheet->getStyle('A'.$i.':F'.$i)->applyFromArray($styleTotalRow);
    }
    
    $i++;
    if (!$isTotal) $sr_no++;
}

// 9. Browser Output
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="OT-Gross_Employee_' . str_replace(' ', '_', $br) . '.xlsx"');
header('Cache-Control: max-age=0');
header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;