<?php
require_once 'db.php';
requireAdmin();
require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$paymentTypeRows = $pdo->query("
    SELECT code, label
    FROM payment_types
    WHERE status = 'active'
    ORDER BY sort_order ASC, label ASC
")->fetchAll();

$paymentTypeMap = [];
foreach ($paymentTypeRows as $row) {
    $paymentTypeMap[$row['code']] = $row['label'];
}

function paymentTypeLabel($paymentType, $paymentTypeMap)
{
    return $paymentTypeMap[$paymentType]
        ?? ucwords(str_replace('_', ' ', $paymentType ?: 'Other'));
}

function formatJobNoExport($jobNo)
{
    if (preg_match('/JOB-(\d{8})(\d{6})/', $jobNo, $matches)) {
        $dateRaw = $matches[1];
        $time    = $matches[2];

        $dateObj = DateTime::createFromFormat('Ymd', $dateRaw);
        $dateFormatted = $dateObj ? $dateObj->format('d/m/Y') : $dateRaw;

        return $dateFormatted . ' (' . $time . ')';
    }

    return $jobNo;
}

$closedJobSearch = trim($_GET['closed_job_search'] ?? '');
$closedPaymentTypes = $_GET['closed_payment_types'] ?? [];
$closedJobMonth = (int)($_GET['closed_job_month'] ?? 0);
$currentYear = (int)date('Y');

if (!is_array($closedPaymentTypes)) {
    $closedPaymentTypes = [];
}

$closedPaymentTypes = array_values(array_filter(array_map('trim', $closedPaymentTypes)));

if ($closedJobMonth < 1 || $closedJobMonth > 12) {
    $closedJobMonth = 0;
}

$sql = "
    SELECT
        j.id,
        j.job_no,
        j.payment_type,
        j.job_type,
        j.issue_title,
        j.status,
        j.created_at,
        j.closed_at,
        j.customer_id,
        m.unique_machine_no,
        m.machine_model,
        m.customer_name AS machine_customer_name,
        c.customer_name AS normal_customer_name
    FROM jobs j
    LEFT JOIN machines m ON j.machine_id = m.id
    LEFT JOIN customers c ON j.customer_id = c.id
    WHERE j.status = 'closed'
";

$params = [];

if ($closedJobSearch !== '') {
    $sql .= "
        AND (
            j.job_no LIKE ?
            OR j.issue_title LIKE ?
            OR j.payment_type LIKE ?
            OR m.unique_machine_no LIKE ?
            OR m.machine_model LIKE ?
            OR m.customer_name LIKE ?
            OR c.customer_name LIKE ?
        )
    ";

    $searchLike = '%' . $closedJobSearch . '%';
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
    $params[] = $searchLike;
}

if (!empty($closedPaymentTypes)) {
    $validClosedTypes = array_values(array_intersect($closedPaymentTypes, array_keys($paymentTypeMap)));

    if (!empty($validClosedTypes)) {
        $placeholders = implode(',', array_fill(0, count($validClosedTypes), '?'));
        $sql .= " AND j.payment_type IN ($placeholders)";
        foreach ($validClosedTypes as $type) {
            $params[] = $type;
        }
    }
}

if ($closedJobMonth > 0) {
    $sql .= " AND MONTH(j.closed_at) = ? AND YEAR(j.closed_at) = ?";
    $params[] = $closedJobMonth;
    $params[] = $currentYear;
}

$sql .= " ORDER BY j.closed_at DESC, j.id DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

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

$headers = [
    'A1' => 'Job No',
    'B1' => 'Type',
    'C1' => 'Customer',
    'D1' => 'Machine No',
    'E1' => 'Machine Model',
    'F1' => 'Issue',
    'G1' => 'Status',
    'H1' => 'Closed At'
];

foreach ($headers as $cell => $value) {
    $sheet->setCellValue($cell, $value);
}

$rowNumber = 2;

foreach ($rows as $job) {
    $displayCustomer = ($job['job_type'] ?? '') === 'machine'
        ? ($job['machine_customer_name'] ?? '')
        : ($job['normal_customer_name'] ?? '');

    $sheet->setCellValue('A' . $rowNumber, formatJobNoExport($job['job_no'] ?? ''));
    $sheet->setCellValue('B' . $rowNumber, paymentTypeLabel($job['payment_type'] ?? '', $paymentTypeMap));
    $sheet->setCellValue('C' . $rowNumber, $displayCustomer ?: '-');
    $sheet->setCellValue('D' . $rowNumber, $job['unique_machine_no'] ?? '-');
    $sheet->setCellValue('E' . $rowNumber, $job['machine_model'] ?? '-');
    $sheet->setCellValue('F' . $rowNumber, $job['issue_title'] ?? '-');
    $sheet->setCellValue('G' . $rowNumber, ucwords(str_replace('_', ' ', $job['status'] ?? '')));
    $sheet->setCellValue('H' . $rowNumber, $job['closed_at'] ?? '');
    $rowNumber++;
}

$sheet->getStyle('A1:H1')->getFont()->setBold(true);

foreach (range('A', 'H') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

$filename = 'closed_jobs_' . date('Ymd_His') . '.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;