<?php
session_start();
require_once 'system/conn.php';

// 1. Centralized Date & Session Logic
if (empty($_SESSION['branch'])) {
    die("Access Denied: No branch session found.");
}

$branch_id = $_SESSION['branch'];
$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'));

// 2. Fetch Branch Name safely
$br_name = 'UNKNOWN BRANCH';
$br_query = $conn->prepare("SELECT name FROM branch WHERE id = ?");
$br_query->execute([$branch_id]);
$br_res = $br_query->fetch(PDO::FETCH_ASSOC);
if ($br_res) { $br_name = $br_res['name']; }

// 3. Load PhpSpreadsheet
require "excel_test/vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Professional Tax Sheet');

// 4. Formatting & Header Styling
$sheet->mergeCells('A1:F1');
foreach (range('A', 'F') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

$headerStyle = [
    'font' => ['bold' => true],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FFE0E0E0'],
    ]
];

// 5. Unified Secure SQL Query
$sql = "SELECT emp_salary.emp_id, emp_salary.name, emp_salary.designation, emp_salary.gross_pay, emp_salary.tax
        FROM emp_salary
        WHERE branch = ? AND month = ? AND year = ?
        UNION ALL
        SELECT '', 'z', 'TOTAL', SUM(gross_pay), SUM(tax)
        FROM emp_salary 
        WHERE branch = ? AND month = ? AND year = ?
        ORDER BY name ASC";

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

// 6. Set Excel Headers
$sheet->setCellValue('A1', $br_name . ' CURRENT PROFESSIONAL TAX WAGE SHEET FOR ' . $monthName . ' ' . $year);
$sheet->getStyle('A1')->applyFromArray($headerStyle);

$sheet->setCellValue('A3', 'SR NO');
$sheet->setCellValue('B3', 'NAME');
$sheet->setCellValue('C3', 'DESIGNATION');
$sheet->setCellValue('D3', 'EMP ID');
$sheet->setCellValue('E3', 'GROSS');
$sheet->setCellValue('F3', 'TAX');
$sheet->getStyle('A3:F3')->applyFromArray(['font' => ['bold' => true]]);

// 7. Populate Data
$row_num = 4;
$sr_no = 1;

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $isTotal = ($row['name'] === 'z');
    
    $sheet->setCellValue('A' . $row_num, $isTotal ? '' : $sr_no);
    $sheet->setCellValue('B' . $row_num, $isTotal ? 'TOTAL' : $row['name']);
    $sheet->setCellValue('C' . $row_num, $row['designation']);
    $sheet->setCellValue('D' . $row_num, $row['emp_id']);
    $sheet->setCellValue('E' . $row_num, $row['gross_pay']);
    $sheet->setCellValue('F' . $row_num, $row['tax']);
    
    // Bold the total row
    if ($isTotal) {
        $sheet->getStyle('A' . $row_num . ':F' . $row_num)->getFont()->setBold(true);
    }
    
    $row_num++;
    if (!$isTotal) $sr_no++;
}

// 8. Output to Browser
$filename = "Professional_Tax_Sheet_" . str_replace(' ', '_', $br_name) . ".xlsx";

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

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