<?php
/*
|--------------------------------------------------------------------------
| company/sp_expenses.php — Customer Expenses overview.
| Lists every customer with their total expense (stock-out + manual) for a
| selected year. Click a row to open that customer's detailed expense page.
|--------------------------------------------------------------------------
*/
ob_start();
session_start();
require_once '../config/database.php';
require_once '../config/modules.php';
require_once '../config/module_access.php';

if (!isset($_SESSION['user_id']) || empty($_SESSION['company_id'])) { header('Location: ../auth/login.php'); exit; }
$companyId   = (int)$_SESSION['company_id'];
$userId      = (int)$_SESSION['user_id'];
$role        = $_SESSION['role'] ?? '';
$companyName = $_SESSION['company_name'] ?? 'Company';
$enabled = company_enabled_modules($pdo, $companyId);
if (!module_on($enabled, 'customer')) { http_response_code(403); exit('Customer Management is not enabled for your company.'); }
if (!mod_can_view($pdo, $companyId, $userId, $role, 'customer')) { http_response_code(403); exit('You do not have access.'); }
if (!function_exists('esc')) { function esc($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); } }
function money($n){ return number_format((float)$n, 0) . ' LAK'; }

$year = (int)($_GET['year'] ?? date('Y'));

// years present in either source
$years = [];
try {
    foreach ($pdo->query("SELECT DISTINCT YEAR(move_date) y FROM sp_stock_movements WHERE company_id={$companyId} AND direction='Out' AND move_date IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN) as $y) { if ($y) $years[(int)$y]=true; }
    foreach ($pdo->query("SELECT DISTINCT YEAR(expense_date) y FROM sp_manual_expenses WHERE company_id={$companyId} AND expense_date IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN) as $y) { if ($y) $years[(int)$y]=true; }
} catch (Throwable $e) {}
$years = array_keys($years);
rsort($years);
if (!$years) { $years = [(int)date('Y')]; }
if (!in_array($year, $years, true)) { $year = $years[0]; }

// stock-out totals per customer (sd_no)
$totals = []; // sd_no => amount
$s1 = $pdo->prepare("SELECT sd_no, SUM(qty*cost_price) t FROM sp_stock_movements
                     WHERE company_id=? AND direction='Out' AND sd_no IS NOT NULL AND sd_no<>'' AND YEAR(move_date)=? GROUP BY sd_no");
$s1->execute([$companyId, $year]);
foreach ($s1->fetchAll(PDO::FETCH_ASSOC) as $r) { $totals[$r['sd_no']] = (float)$r['t']; }
try {
    $s2 = $pdo->prepare("SELECT sd_no, SUM(amount) t FROM sp_manual_expenses
                         WHERE company_id=? AND sd_no IS NOT NULL AND sd_no<>'' AND YEAR(expense_date)=? GROUP BY sd_no");
    $s2->execute([$companyId, $year]);
    foreach ($s2->fetchAll(PDO::FETCH_ASSOC) as $r) { $totals[$r['sd_no']] = ($totals[$r['sd_no']] ?? 0) + (float)$r['t']; }
} catch (Throwable $e) {}

// customer names
$names = [];
foreach ($pdo->query("SELECT sd_no, customer_name FROM sp_customers WHERE company_id={$companyId}")->fetchAll(PDO::FETCH_ASSOC) as $r) {
    $names[$r['sd_no']] = $r['customer_name'];
}
arsort($totals);
$grand = array_sum($totals);
?>
<!DOCTYPE html>
<html lang="en"><head>
    <meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Customer Expenses</title>
    <link href="https://fonts.googleapis.com/css2?family=Noto+Sans+Lao:wght@400;500;600;700&family=Inter:wght@400;500;600;700&display=swap" rel="stylesheet">
    <style>
        * { box-sizing:border-box; }
        body { margin:0; font-family:'Noto Sans Lao','Inter',Arial,sans-serif; background:#eef2f9; color:#0f172a; }
        .wrap { max-width:1280px; margin:0 auto; padding:10px 16px 16px; }
        h1 { font-size:22px; font-weight:800; margin:4px 0 12px; }
        .card { background:#fff; border:1px solid #e6eaf2; border-radius:16px; box-shadow:0 12px 30px rgba(30,41,59,.08); padding:18px; }
        .filter { display:flex; gap:10px; align-items:flex-end; margin-bottom:14px; }
        .filter label { display:block; font-size:12px; font-weight:700; color:#64748b; text-transform:uppercase; margin-bottom:5px; }
        .filter select { padding:9px 12px; border:1px solid #e2e8f0; border-radius:10px; font-family:inherit; }
        .btn { background:#2563eb; color:#fff; border:none; padding:10px 16px; border-radius:10px; font-weight:700; cursor:pointer; font-family:inherit; }
        table { width:100%; border-collapse:collapse; }
        thead th { background:#f8fafc; color:#64748b; text-transform:uppercase; font-size:11px; letter-spacing:.04em; font-weight:700; padding:11px 10px; text-align:left; border-bottom:1px solid #e6eaf2; }
        tbody td { padding:11px 10px; border-bottom:1px solid #eef2f7; font-size:14px; }
        tbody tr:hover { background:#f9fafe; cursor:pointer; }
        .num { text-align:right; font-weight:700; }
        .pill { background:#dcfce7; color:#166534; padding:3px 9px; border-radius:6px; font-weight:700; font-size:12px; }
        .grand { font-size:18px; font-weight:800; color:#1e40af; text-align:right; }
        .muted { color:#94a3b8; }
    </style>
</head><body>
    <div class="wrap">
        <?php $spActive='expenses'; include 'sp_nav.php'; ?>
        <h1>Customer Expenses &mdash; <?= esc($year) ?></h1>
        <div class="card">
            <form method="GET" class="filter">
                <div>
                    <label>Year</label>
                    <select name="year" onchange="this.form.submit()">
                        <?php foreach ($years as $y): ?><option value="<?= $y ?>" <?= $y===$year?'selected':'' ?>><?= $y ?></option><?php endforeach; ?>
                    </select>
                </div>
                <noscript><button type="submit" class="btn">Show</button></noscript>
            </form>
            <table>
                <thead><tr><th>Customer</th><th>SD No</th><th class="num">Total Expense (<?= esc($year) ?>)</th></tr></thead>
                <tbody>
                    <?php if ($totals): ?>
                        <?php foreach ($totals as $sd => $amt): ?>
                            <tr onclick="location.href='sp_customer_expenses.php?sd=<?= urlencode($sd) ?>&year=<?= $year ?>'">
                                <td><strong><?= esc($names[$sd] ?? '(unknown)') ?></strong></td>
                                <td><span class="pill"><?= esc($sd) ?></span></td>
                                <td class="num"><?= money($amt) ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <tr><td colspan="2" class="num"><strong>Grand total</strong></td><td class="grand"><?= money($grand) ?></td></tr>
                    <?php else: ?>
                        <tr><td colspan="3" class="muted" style="text-align:center;">No expenses recorded for <?= esc($year) ?>.</td></tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</body></html>