<?php
session_start();
$_SESSION['report_progress'] = 0;

require '../../vendor/autoload.php';
include '../../connection.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

function updateProgress($value) {
    $_SESSION['report_progress'] = $value;
    session_write_close();
    usleep(500000); // 0.5s delay to simulate time
    session_start();
}

if (!isset($_POST['generate'])) {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request']);
    exit;
}

// ✅ Create Spreadsheet
$spreadsheet = new Spreadsheet();
$headerStyle = [
    'font' => ['bold' => true, 'size' => 12, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4CAF50']],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER]
];

/* ---------- SHEET 1: Transactions ---------- */
$sheet1 = $spreadsheet->getActiveSheet();
$sheet1->setTitle("Transactions");

$headers = ['#', 'Resident Name', 'Description', 'Status', 'Date Created'];
$col = 'A';
foreach ($headers as $header) $sheet1->setCellValue($col++ . '1', $header);
$sheet1->getStyle('A1:E1')->applyFromArray($headerStyle);

$result = $conn->query("
    SELECT 
        t.UniqueID,
        CONCAT(r.last_name, ', ', r.first_name, ' ', r.middle_name) AS fullname,
        t.description,
        t.status,
        t.date_created
    FROM tbl_transaction t
    LEFT JOIN tbl_resident r ON t.resident_id = r.resident_id
    ORDER BY t.date_created DESC
");

$row = 2; $count = 1;
while ($r = $result->fetch_assoc()) {
    $sheet1->setCellValue("A$row", $count++);
    $sheet1->setCellValue("B$row", $r['fullname']);
    $sheet1->setCellValue("C$row", $r['description']);
    $sheet1->setCellValue("D$row", ucfirst($r['status']));
    $sheet1->setCellValue("E$row", date("F d, Y h:i A", strtotime($r['date_created'])));
    $row++;
}
foreach (range('A', 'E') as $col) $sheet1->getColumnDimension($col)->setAutoSize(true);

/* ---------- SHEET 2: Officials ---------- */
$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle("Officials");
$sheet2->fromArray(['Full Name', 'Position', 'Contact'], NULL, 'A1');
$sheet2->getStyle('A1:C1')->applyFromArray($headerStyle);

$res2 = $conn->query("
    SELECT CONCAT(u.last_name, ', ', u.first_name, ' ', u.middle_name) AS fullname,
           os.position, u.contact
    FROM tbl_organizational_structure os
    LEFT JOIN tbl_user u ON os.user_id = u.user_id
");
$row = 2;
while ($o = $res2->fetch_assoc()) {
    $sheet2->setCellValue("A$row", $o['fullname']);
    $sheet2->setCellValue("B$row", 'Barangay ' . $o['position']);
    $sheet2->setCellValue("C$row", $o['contact']);
    $row++;
}
foreach (range('A', 'C') as $col) $sheet2->getColumnDimension($col)->setAutoSize(true);

/* ---------- SHEET 3: Complaints ---------- */
$sheet3 = $spreadsheet->createSheet();
$sheet3->setTitle("Complaints Report");
$sheet3->fromArray(['Month', 'Complaint Type', 'Total Complaints'], NULL, 'A1');
$sheet3->getStyle('A1:C1')->applyFromArray($headerStyle);

$res3 = $conn->query("
    SELECT DATE_FORMAT(t.incident_date, '%M %Y') AS month_label,
           c.complain_name,
           COUNT(t.UniqueID) AS total_complaints
    FROM tbl_transaction t
    LEFT JOIN tbl_complaint_types c ON t.complaint_type_id = c.complaint_type_id
    GROUP BY month_label, c.complain_name
    ORDER BY STR_TO_DATE(month_label, '%M %Y') DESC
");
$row = 2;
while ($c = $res3->fetch_assoc()) {
    $sheet3->setCellValue("A$row", $c['month_label']);
    $sheet3->setCellValue("B$row", $c['complain_name']);
    $sheet3->setCellValue("C$row", $c['total_complaints']);
    $row++;
}
foreach (range('A', 'C') as $col) $sheet3->getColumnDimension($col)->setAutoSize(true);

/* ---------- SAVE FILE ---------- */
$reportsDir = realpath(__DIR__ . '/../../') . '/reports/';
if (!is_dir($reportsDir)) mkdir($reportsDir, 0777, true);

$filename = 'Barangay_Report_' . date('Ymd_His') . '.xlsx';
$filePath = $reportsDir . $filename;

$writer = new Xlsx($spreadsheet);
$writer->save($filePath);

echo json_encode([
    'status' => 'success',
    'file' => '/e_complaint/reports/' . $filename
]);
?>
