<?php
// Enable only while debugging
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

ob_start();

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

function formatJobNo($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;
}



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

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$year = isset($_GET['year']) ? (int) $_GET['year'] : (int) date('Y');
$monthFrom = isset($_GET['month_from']) ? (int) $_GET['month_from'] : (int) date('n');
$monthTo   = isset($_GET['month_to']) ? (int) $_GET['month_to'] : (int) date('n');

if ($year <= 0) {
    exit('Invalid year.');
}

if ($monthFrom < 1 || $monthFrom > 12) {
    $monthFrom = (int) date('n');
}

if ($monthTo < 1 || $monthTo > 12) {
    $monthTo = (int) date('n');
}

if ($monthFrom > $monthTo) {
    [$monthFrom, $monthTo] = [$monthTo, $monthFrom];
}

$startDate = sprintf('%04d-%02d-01 00:00:00', $year, $monthFrom);
$endDate = date('Y-m-d H:i:s', strtotime(sprintf('%04d-%02d-01 00:00:00', $year, $monthTo) . ' +1 month'));

/*
|--------------------------------------------------------------------------
| Staff Payment Summary
|--------------------------------------------------------------------------
*/
$sqlSummary = "
    SELECT
        s.id,
        s.full_name,
        s.username,
        s.status,
        COUNT(CASE WHEN j.status = 'closed' THEN 1 END) AS total_closed_jobs,
        COALESCE(SUM(
            CASE
                WHEN j.status = 'closed' THEN
                    ROUND(
                        j.staff_fee / NULLIF((
                            SELECT COUNT(*)
                            FROM job_staff_assignments jsa2
                            WHERE jsa2.job_id = j.id
                        ), 0),
                    0)
                ELSE 0
            END
        ), 0) AS total_earned
    FROM staff s
    LEFT JOIN job_staff_assignments jsa ON s.id = jsa.staff_id
    LEFT JOIN jobs j 
        ON j.id = jsa.job_id
        AND j.status = 'closed'
        AND j.closed_at IS NOT NULL
        AND j.closed_at >= :start_date
        AND j.closed_at < :end_date
    GROUP BY s.id, s.full_name, s.username, s.status
    ORDER BY total_earned DESC, s.full_name ASC
";

$stmtSummary = $pdo->prepare($sqlSummary);
$stmtSummary->execute([
    ':start_date' => $startDate,
    ':end_date'   => $endDate
]);
$staffSummaries = $stmtSummary->fetchAll(PDO::FETCH_ASSOC);

/*
|--------------------------------------------------------------------------
| Staff Job Details
|--------------------------------------------------------------------------
*/
$sqlJobs = "
    SELECT
        s.id AS staff_id,
        s.full_name,
        s.username,
        j.id AS job_id,
        j.job_no,
        j.issue_title,
        j.status AS job_status,
        j.staff_fee,
        j.closed_at,
        COALESCE(m.customer_name, c.customer_name) AS customer_name,
        m.unique_machine_no,
        ROUND(
            j.staff_fee / NULLIF((
                SELECT COUNT(*)
                FROM job_staff_assignments jsa2
                WHERE jsa2.job_id = j.id
            ), 0),
        0) AS share_amount
    FROM staff s
    LEFT JOIN job_staff_assignments jsa ON s.id = jsa.staff_id
    LEFT JOIN jobs j ON j.id = jsa.job_id
    LEFT JOIN customers c ON j.customer_id = c.id
    LEFT JOIN machines m ON j.machine_id = m.id
    WHERE j.id IS NOT NULL
      AND j.status = 'closed'
      AND j.closed_at IS NOT NULL
      AND j.closed_at >= :start_date
      AND j.closed_at < :end_date
    ORDER BY s.full_name ASC, j.id DESC
";

$stmtJobs = $pdo->prepare($sqlJobs);
$stmtJobs->execute([
    ':start_date' => $startDate,
    ':end_date'   => $endDate
]);
$staffJobs = $stmtJobs->fetchAll(PDO::FETCH_ASSOC);

/*
|--------------------------------------------------------------------------
| Group jobs by staff_id
|--------------------------------------------------------------------------
*/
$staffJobsMap = [];
foreach ($staffJobs as $job) {
    $staffId = (int) $job['staff_id'];
    if (!isset($staffJobsMap[$staffId])) {
        $staffJobsMap[$staffId] = [];
    }
    $staffJobsMap[$staffId][] = $job;
}

/*
|--------------------------------------------------------------------------
| Create spreadsheet
|--------------------------------------------------------------------------
*/
$spreadsheet = new Spreadsheet();

/*
|--------------------------------------------------------------------------
| Summary Sheet
|--------------------------------------------------------------------------
*/
$summarySheet = $spreadsheet->getActiveSheet();
$summarySheet->setTitle('Summary');

$title = "Staff Payment Summary - {$year}/" 
    . str_pad((string) $monthFrom, 2, '0', STR_PAD_LEFT)
    . " to "
    . str_pad((string) $monthTo, 2, '0', STR_PAD_LEFT);
$summarySheet->setCellValue('A1', $title);

$summarySheet->setCellValue('A3', 'Staff Name');
$summarySheet->setCellValue('B3', 'Username');
$summarySheet->setCellValue('C3', 'Status');
$summarySheet->setCellValue('D3', 'Closed Jobs');
$summarySheet->setCellValue('E3', 'Total Earned (LAK)');

$row = 4;
$grandTotal = 0;

foreach ($staffSummaries as $staff) {
    $summarySheet->setCellValue("A{$row}", $staff['full_name']);
    $summarySheet->setCellValue("B{$row}", $staff['username']);
    $summarySheet->setCellValue("C{$row}", $staff['status']);
    $summarySheet->setCellValue("D{$row}", (int) $staff['total_closed_jobs']);
    $summarySheet->setCellValue("E{$row}", (float) $staff['total_earned']);

    $grandTotal += (float) $staff['total_earned'];
    $row++;
}

$summarySheet->setCellValue("D{$row}", 'Grand Total');
$summarySheet->setCellValue("E{$row}", $grandTotal);

/*
|--------------------------------------------------------------------------
| Format summary sheet
|--------------------------------------------------------------------------
*/
$summarySheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$summarySheet->getStyle('A3:E3')->getFont()->setBold(true);
$summarySheet->getStyle("D{$row}:E{$row}")->getFont()->setBold(true);
$summarySheet->getStyle("E4:E{$row}")->getNumberFormat()->setFormatCode('#,##0');

foreach (range('A', 'E') as $col) {
    $summarySheet->getColumnDimension($col)->setAutoSize(true);
}

/*
|--------------------------------------------------------------------------
| Create one sheet per staff
|--------------------------------------------------------------------------
*/
foreach ($staffSummaries as $staff) {
    $baseSheetName = preg_replace('/[\\\\\\/\\?\\*\\[\\]\\:]/', '-', (string) $staff['full_name']);
    $baseSheetName = trim($baseSheetName);
    if ($baseSheetName === '') {
        $baseSheetName = 'Staff';
    }

    $sheetName = mb_substr($baseSheetName, 0, 25) . '-' . $staff['id'];

    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle($sheetName);

    $sheet->setCellValue('A1', 'Staff Payment Detail');
    $sheet->setCellValue('A3', 'Staff Name');
    $sheet->setCellValue('B3', $staff['full_name']);

    $sheet->setCellValue('A4', 'Username');
    $sheet->setCellValue('B4', $staff['username']);

    $sheet->setCellValue('A5', 'Status');
    $sheet->setCellValue('B5', $staff['status']);

    $sheet->setCellValue('A6', 'Closed Jobs');
    $sheet->setCellValue('B6', (int) $staff['total_closed_jobs']);

    $sheet->setCellValue('A7', 'Total Earned (LAK)');
    $sheet->setCellValue('B7', (float) $staff['total_earned']);

    $sheet->setCellValue('A9', 'Job No');
    $sheet->setCellValue('B9', 'Customer');
    $sheet->setCellValue('C9', 'Machine');
    $sheet->setCellValue('D9', 'Issue');
    $sheet->setCellValue('E9', 'Job Staff Fee (LAK)');
    $sheet->setCellValue('F9', 'This Staff Share (LAK)');
    $sheet->setCellValue('G9', 'Closed At');

    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
    $sheet->getStyle('A3:A7')->getFont()->setBold(true);
    $sheet->getStyle('A9:G9')->getFont()->setBold(true);
    $sheet->getStyle('B7')->getNumberFormat()->setFormatCode('#,##0');

    $jobRow = 10;
    $currentStaffJobs = $staffJobsMap[(int) $staff['id']] ?? [];

    if (!empty($currentStaffJobs)) {
        foreach ($currentStaffJobs as $job) {
            $sheet->setCellValue("A{$jobRow}", formatJobNo($job['job_no']));
            $sheet->setCellValue("B{$jobRow}", $job['customer_name']);
            $sheet->setCellValue("C{$jobRow}", $job['unique_machine_no']);
            $sheet->setCellValue("D{$jobRow}", $job['issue_title']);
            $sheet->setCellValue("E{$jobRow}", (float) $job['staff_fee']);
            $sheet->setCellValue("F{$jobRow}", (float) $job['share_amount']);
            $sheet->setCellValue("G{$jobRow}", $job['closed_at']);
            $jobRow++;
        }

        $sheet->getStyle("E10:F" . ($jobRow - 1))
            ->getNumberFormat()
            ->setFormatCode('#,##0');
    } else {
        $sheet->setCellValue("A{$jobRow}", 'No closed jobs for this period.');
    }

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

/*
|--------------------------------------------------------------------------
| Open workbook on Summary sheet
|--------------------------------------------------------------------------
*/
$spreadsheet->setActiveSheetIndex(0);

/*
|--------------------------------------------------------------------------
| Output file
|--------------------------------------------------------------------------
*/
$filename = "staff_payment_summary_{$year}_"
    . str_pad((string) $monthFrom, 2, '0', STR_PAD_LEFT)
    . "_to_"
    . str_pad((string) $monthTo, 2, '0', STR_PAD_LEFT)
    . ".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;