<?php
// GET /api/jobcards/financial_report.php?date_from=&date_to=&status=&type=&internal=&token=XXX
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

// Token auth (opened in new tab)
$token = $_GET['token'] ?? '';
if ($token) {
    $db = getDB();
    $ts = $db->prepare("SELECT u.id, u.role_id, u.full_name FROM user_tokens ut JOIN users u ON u.id = ut.user_id WHERE ut.token = ? AND ut.expires_at > NOW() AND u.is_active = 1");
    $ts->execute([$token]);
    $user = $ts->fetch();
    if (!$user) {
        http_response_code(401);
        die('Unauthorised');
    }
} else {
    $user = requireAuth();
}
// Admins only
if (!in_array((int) $user['role_id'], [1, 2, 5])) {
    http_response_code(403);
    die('Forbidden');
}

$db = getDB();
$dateFrom = $_GET['date_from'] ?? '';
$dateTo = $_GET['date_to'] ?? '';
$status = $_GET['status'] ?? '';
$type = $_GET['type'] ?? '';
$internal = $_GET['internal'] ?? '';

// ── Build WHERE ──────────────────────────────────────────────
$where = ['1=1'];
$params = [];

if ($dateFrom) {
    $where[] = 'DATE(jc.scheduled_date) >= ?';
    $params[] = $dateFrom;
}
if ($dateTo) {
    $where[] = 'DATE(jc.scheduled_date) <= ?';
    $params[] = $dateTo;
}
if ($status === 'active') {
    $where[] = "jc.status IN ('travelling','on_site','working')";
} elseif ($status === 'pending') {
    $where[] = "jc.status IN ('draft','assigned')";
} elseif ($status) {
    $where[] = 'jc.status = ?';
    $params[] = $status;
}
if ($type) {
    $where[] = 'jc.job_type = ?';
    $params[] = $type;
}
if ($internal !== '') {
    $where[] = 'jc.is_internal = ?';
    $params[] = (int) $internal;
}

$whereStr = implode(' AND ', $where);

// ── Fetch jobs ───────────────────────────────────────────────
$stmt = $db->prepare("
    SELECT jc.*, c.company_name AS client_name, u.full_name AS assigned_name
    FROM job_cards jc
    LEFT JOIN clients c ON c.id = jc.client_id
    LEFT JOIN users u   ON u.id = jc.assigned_to
    WHERE $whereStr
    ORDER BY jc.scheduled_date ASC, jc.created_at DESC
");
$stmt->execute($params);
$jobs = $stmt->fetchAll();

// ── Enrich each job with exact costing (same engine as costing tab) ──
require_once __DIR__ . '/_costing_engine.php';

$defaultDays = 21.67;
$defaultHours = 8;
try {
    foreach ($db->query("SELECT setting_key, setting_value FROM settings WHERE setting_group IN ('hr','finance')")->fetchAll() as $s) {
        if ($s['setting_key'] === 'working_days_per_month')
            $defaultDays = (float) $s['setting_value'];
        if ($s['setting_key'] === 'working_hours_per_day')
            $defaultHours = (float) $s['setting_value'];
    }
} catch (Exception $ignored) {
}

foreach ($jobs as &$row) {
    $costs = calcJobCostSummary($db, (int) $row['id'], $defaultDays, $defaultHours);
    $row['labour_cost'] = $costs['labour_cost'];
    $row['travel_cost'] = $costs['travel_cost'];
    $row['stock_cost'] = $costs['consumables_cost'];
    $row['slips_cost'] = $costs['slips_total'];
    $row['total_cost'] = $costs['total_cost'];
    $row['net_profit'] = $costs['net_profit'];
}
unset($row);

// ── Company settings ─────────────────────────────────────────
$sets = [];
try {
    foreach ($db->query("SELECT setting_key, setting_value FROM settings WHERE setting_group='general'")->fetchAll() as $s) {
        $sets[$s['setting_key']] = $s['setting_value'];
    }
} catch (Exception $e) {
}
$company = $sets['company_name'] ?? 'Elegant Work';
$logoPath = $sets['company_logo'] ?? null;
$logoBase64 = null;
if ($logoPath) {
    $abs = __DIR__ . '/../../' . $logoPath;
    if (file_exists($abs)) {
        $ext = strtolower(pathinfo($abs, PATHINFO_EXTENSION));
        $mime = ['jpg' => 'image/jpeg', 'jpeg' => 'image/jpeg', 'png' => 'image/png', 'webp' => 'image/webp', 'svg' => 'image/svg+xml'][$ext] ?? 'image/png';
        $logoBase64 = 'data:' . $mime . ';base64,' . base64_encode(file_get_contents($abs));
    }
}

// ── Totals ───────────────────────────────────────────────────
$totInvoice = array_sum(array_column($jobs, 'invoice_amount'));
$totCost = array_sum(array_column($jobs, 'total_cost'));
$totLabour = array_sum(array_column($jobs, 'labour_cost'));
$totTravel = array_sum(array_column($jobs, 'travel_cost'));
$totStock = array_sum(array_column($jobs, 'stock_cost'));
$totSlips = array_sum(array_column($jobs, 'slips_cost'));
$netTotal = $totInvoice - $totCost;
$invoicedCnt = count(array_filter($jobs, fn($j) => $j['invoice_amount'] > 0));

$statusLabels = [
    'draft' => 'Draft',
    'assigned' => 'Assigned',
    'travelling' => 'Travelling',
    'on_site' => 'On Site',
    'working' => 'Working',
    'completed' => 'Completed',
    'internal_complete' => 'Complete (Internal)',
    'invoiced' => 'Invoiced',
    'no_charge' => 'No Charge',
    'cancelled' => 'Cancelled'
];

function fr($n)
{
    return 'R ' . number_format((float) $n, 2, '.', ',');
}
function frd($d)
{
    return $d ? date('d M Y', strtotime($d)) : '—';
}
function esc2($s)
{
    return htmlspecialchars($s ?? '', ENT_QUOTES);
}

$periodLabel = '';
if ($dateFrom && $dateTo)
    $periodLabel = frd($dateFrom) . ' – ' . frd($dateTo);
elseif ($dateFrom)
    $periodLabel = 'From ' . frd($dateFrom);
elseif ($dateTo)
    $periodLabel = 'Up to ' . frd($dateTo);
else
    $periodLabel = 'All time';

header('Content-Type: text/html; charset=utf-8');
?><!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Financial Report — <?= esc2($company) ?></title>
    <style>
        * {
            box-sizing: border-box;
            margin: 0;
            padding: 0;
        }

        body {
            font-family: Arial, Helvetica, sans-serif;
            font-size: 10pt;
            color: #1a1a2e;
            background: #fff;
        }

        .page {
            max-width: 1050px;
            margin: 0 auto;
            padding: 28px 32px;
        }

        /* Header */
        .header {
            display: flex;
            justify-content: space-between;
            align-items: flex-start;
            padding-bottom: 16px;
            border-bottom: 3px solid #1a3a6b;
            margin-bottom: 20px;
        }

        .company-name {
            font-size: 20pt;
            font-weight: 800;
            color: #1a3a6b;
        }

        .doc-title {
            font-size: 14pt;
            font-weight: 700;
            color: #1a3a6b;
            text-align: right;
        }

        .doc-meta {
            font-size: 9pt;
            color: #666;
            text-align: right;
            margin-top: 3px;
        }

        /* Summary cards */
        .summary-grid {
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 10px;
            margin-bottom: 22px;
        }

        .sum-card {
            border: 1px solid #d1d5db;
            border-radius: 6px;
            padding: 12px 14px;
        }

        .sum-card .label {
            font-size: 8pt;
            color: #6b7280;
            text-transform: uppercase;
            letter-spacing: .04em;
            margin-bottom: 4px;
        }

        .sum-card .value {
            font-size: 16pt;
            font-weight: 800;
        }

        .sum-card.profit .value {
            color: #16a34a;
        }

        .sum-card.loss .value {
            color: #dc2626;
        }

        .sum-card.invoice .value {
            color: #1a3a6b;
        }

        .sum-card.cost .value {
            color: #374151;
        }

        /* Cost breakdown row */
        .breakdown {
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 8px;
            margin-bottom: 22px;
        }

        .bk-item {
            border: 1px solid #e5e7eb;
            border-radius: 5px;
            padding: 8px 12px;
            background: #f9fafb;
            text-align: center;
        }

        .bk-item .bk-label {
            font-size: 8pt;
            color: #6b7280;
            margin-bottom: 3px;
        }

        .bk-item .bk-value {
            font-size: 11pt;
            font-weight: 700;
        }

        /* Table */
        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 9pt;
            margin-bottom: 24px;
        }

        thead tr {
            background: #1a3a6b;
            color: #fff;
        }

        thead th {
            padding: 7px 8px;
            text-align: left;
            font-weight: 600;
            white-space: nowrap;
        }

        tbody tr:nth-child(even) {
            background: #f9fafb;
        }

        tbody tr:hover {
            background: #eff6ff;
        }

        td {
            padding: 14px 0px;
            border-bottom: 1px solid #e5e7eb;
            vertical-align: middle;
        }

        tfoot td {
            padding: 8px;
            font-weight: 700;
            border-top: 2px solid #1a3a6b;
            background: #f0f4ff;
        }

        .badge {
            display: inline-block;
            padding: 2px 6px;
            border-radius: 99px;
            font-size: 8pt;
            font-weight: 600;
        }

        .badge-green {
            background: #dcfce7;
            color: #15803d;
        }

        .badge-red {
            background: #fee2e2;
            color: #b91c1c;
        }

        .badge-blue {
            background: #dbeafe;
            color: #1d4ed8;
        }

        .badge-gray {
            background: #f3f4f6;
            color: #4b5563;
        }

        .badge-orange {
            background: #fff7ed;
            color: #c2410c;
        }

        .badge-yellow {
            background: #fef9c3;
            color: #854d0e;
        }

        .profit {
            color: #16a34a;
            font-weight: 700;
        }

        .loss {
            color: #dc2626;
            font-weight: 700;
        }

        .muted {
            color: #9ca3af;
            font-style: italic;
        }

        .right {
            text-align: right;
        }

        .footer {
            border-top: 1px solid #d1d5db;
            padding-top: 12px;
            margin-top: 8px;
            font-size: 8pt;
            color: #9ca3af;
            display: flex;
            justify-content: space-between;
        }

        .section-title {
            font-size: 11pt;
            font-weight: 700;
            color: #1a3a6b;
            margin-bottom: 10px;
            padding-bottom: 6px;
            border-bottom: 1px solid #e5e7eb;
        }

        @media print {
            body {
                font-size: 9pt;
            }

            .page {
                padding: 16px;
            }

            .no-print {
                display: none;
            }

            thead {
                display: table-header-group;
            }

            tr {
                page-break-inside: avoid;
            }
        }
    </style>
</head>

<body>
    <div class="page">

        <!-- Header -->
        <div class="header">
            <div>
                <?php if ($logoBase64): ?>
                    <img src="<?= $logoBase64 ?>" style="height:48px;margin-bottom:6px;display:block">
                <?php endif; ?>
                
            </div>
            <div>
                <div class="doc-title">Financial Overview Report</div>
                <div class="doc-meta">Period: <strong><?= esc2($periodLabel) ?></strong></div>
                <div class="doc-meta">Generated: <?= date('d M Y H:i') ?> · <?= esc2($user['full_name']) ?></div>
                <div class="doc-meta"><?= count($jobs) ?> job cards · <?= $invoicedCnt ?> invoiced</div>
            </div>
        </div>

        <!-- Summary KPI cards -->
        <div class="summary-grid">
            <div class="sum-card invoice">
                <div class="label">Total Invoiced</div>
                <div class="value"><?= fr($totInvoice) ?></div>
                <div style="font-size:8pt;color:#6b7280;margin-top:3px"><?= $invoicedCnt ?> of <?= count($jobs) ?> jobs
                </div>
            </div>
            <div class="sum-card cost">
                <div class="label">Total Cost</div>
                <div class="value"><?= fr($totCost) ?></div>
                <div style="font-size:8pt;color:#6b7280;margin-top:3px">All expenses combined</div>
            </div>
            <div class="sum-card <?= $netTotal >= 0 ? 'profit' : 'loss' ?>">
                <div class="label">Net <?= $netTotal >= 0 ? 'Profit' : 'Loss' ?></div>
                <div class="value"><?= ($netTotal >= 0 ? '+' : '') . fr($netTotal) ?></div>
                <div style="font-size:8pt;color:#6b7280;margin-top:3px">On invoiced jobs only</div>
            </div>
            <div class="sum-card" style="border-color:<?= $totInvoice > 0 ? '#16a34a' : '#d1d5db' ?>">
                <div class="label">Margin</div>
                <div class="value"
                    style="color:<?= $totInvoice > 0 ? ($netTotal >= 0 ? '#16a34a' : '#dc2626') : '#9ca3af' ?>">
                    <?= $totInvoice > 0 ? number_format(($netTotal / $totInvoice) * 100, 1) . '%' : '—' ?>
                </div>
                <div style="font-size:8pt;color:#6b7280;margin-top:3px">Net / Invoice</div>
            </div>
        </div>

        <!-- Cost breakdown -->
        <div class="section-title">Cost Breakdown</div>
        <div class="breakdown">
            <div class="bk-item">
                <div class="bk-label">🔧 Labour</div>
                <div class="bk-value"><?= fr($totLabour) ?></div>
            </div>
            <div class="bk-item">
                <div class="bk-label">🚗 Travel</div>
                <div class="bk-value"><?= fr($totTravel) ?></div>
            </div>
            <div class="bk-item">
                <div class="bk-label">📦 Stock / Parts</div>
                <div class="bk-value"><?= fr($totStock) ?></div>
            </div>
            <div class="bk-item">
                <div class="bk-label">🧾 Expense Slips</div>
                <div class="bk-value"><?= fr($totSlips) ?></div>
            </div>
        </div>

        <!-- Job Cards table -->
        <div class="section-title" style="margin-top:4px">Job Cards Detail</div>
        <?php if (empty($jobs)): ?>
            <p style="color:#6b7280;text-align:center;padding:24px">No job cards found for the selected period and filters.
            </p>
        <?php else: ?>
            <table>
                <thead>
                    <tr>
                        <th>Job #</th>
                        <th>Title</th>
                        <th>Client</th>
                        <th>Scheduled</th>
                        <th>Assigned To</th>
                        <th>Status</th>
                        <th class="right">Invoice</th>
                        <th class="right">Cost</th>
                        <th class="right">Labour</th>
                        <th class="right">Travel</th>
                        <th class="right">Net P/L</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($jobs as $j):
                        $statusBadgeClass = match ($j['status']) {
                            'completed', 'internal_complete', 'invoiced' => 'badge-green',
                            'working', 'on_site' => 'badge-blue',
                            'travelling' => 'badge-orange',
                            'draft', 'assigned' => 'badge-gray',
                            'no_charge' => 'badge-yellow',
                            'cancelled' => 'badge-red',
                            default => 'badge-gray',
                        };
                        $netProfit = $j['net_profit'];
                        $invoice = (float) ($j['invoice_amount'] ?? 0);
                        ?>
                        <tr>
                            <td><code
                                    style="font-size:8.5pt;background:#f3f4f6;padding:1px 4px;border-radius:3px"><?= esc2($j['job_number']) ?></code>
                            </td>
                            <td style="max-width:180px">
                                <?= esc2($j['title']) ?>        <?= $j['is_internal'] ? ' <span class="badge badge-gray" style="font-size:7pt">INT</span>' : '' ?>
                            </td>
                            <td style="max-width:120px;font-size:8.5pt"><?= esc2($j['client_name'] ?? '—') ?></td>
                            <td style="white-space:nowrap;font-size:8.5pt"><?= frd($j['scheduled_date']) ?></td>
                            <td style="font-size:8.5pt"><?= esc2($j['assigned_name'] ?? '—') ?></td>
                            <td><span
                                    class="badge <?= $statusBadgeClass ?>"><?= esc2($statusLabels[$j['status']] ?? $j['status']) ?></span>
                            </td>
                            <td class="right"><?= $invoice > 0 ? fr($invoice) : '<span class="muted">—</span>' ?></td>
                            <td class="right"><?= fr($j['total_cost']) ?></td>
                            <td class="right" style="font-size:8.5pt"><?= fr($j['labour_cost']) ?></td>
                            <td class="right" style="font-size:8.5pt"><?= fr($j['travel_cost']) ?></td>
                            <td class="right">
                                <?php if ($netProfit !== null): ?>
                                    <span
                                        class="<?= $netProfit >= 0 ? 'profit' : 'loss' ?>"><?= ($netProfit >= 0 ? '+' : '') . fr($netProfit) ?></span>
                                <?php else: ?>
                                    <span class="muted">No invoice</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="6">TOTALS (<?= count($jobs) ?> jobs)</td>
                        <td class="right"><?= fr($totInvoice) ?></td>
                        <td class="right"><?= fr($totCost) ?></td>
                        <td class="right"><?= fr($totLabour) ?></td>
                        <td class="right"><?= fr($totTravel) ?></td>
                        <td class="right <?= $netTotal >= 0 ? 'profit' : 'loss' ?>">
                            <?= ($netTotal >= 0 ? '+' : '') . fr($netTotal) ?></td>
                    </tr>
                </tfoot>
            </table>
        <?php endif; ?>

        <div class="footer">
            <span><?= esc2($company) ?> · Financial Report · <?= esc2($periodLabel) ?></span>
            <span>Generated <?= date('d M Y H:i') ?> · Labour costs are estimated — see individual job card costing for
                full detail</span>
        </div>

    </div>
    <script>window.onload = () => window.print();</script>
</body>

</html>