<?php
/*
|--------------------------------------------------------------------------
| company/sp_customer_expenses.php
| Expense tracker for one customer, built from STOCK-OUT movements.
| Amount per line = qty x cost_price. Shows a grand total, a monthly
| breakdown, and Year/Month filters. Company-scoped.
|--------------------------------------------------------------------------
*/
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 to this customer.'); }
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'; }
$canEdit = mod_can_edit($pdo, $companyId, $userId, $role, 'customer');

$sd = trim($_POST['sd'] ?? $_GET['sd'] ?? '');
if ($sd === '') { header('Location: sp_customers.php'); exit; }

/* Manual expenses table (other expenses keyed in by hand) */
$pdo->exec("
    CREATE TABLE IF NOT EXISTS sp_manual_expenses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_id INT NOT NULL,
        sd_no VARCHAR(50) NOT NULL,
        expense_date DATE DEFAULT NULL,
        machine_no VARCHAR(100) DEFAULT NULL,
        description VARCHAR(255) DEFAULT NULL,
        packing_no VARCHAR(100) DEFAULT NULL,
        qty INT NOT NULL DEFAULT 0,
        cost_price DECIMAL(15,2) NOT NULL DEFAULT 0,
        amount DECIMAL(15,2) NOT NULL DEFAULT 0,
        note VARCHAR(255) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX (company_id, sd_no)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$redirBack = 'sp_customer_expenses.php?sd=' . urlencode($sd)
           . '&year=' . urlencode((string)($_POST['year'] ?? $_GET['year'] ?? date('Y')))
           . '&month=' . urlencode((string)($_POST['month'] ?? $_GET['month'] ?? 0));

/* Add a manual expense */
if (isset($_POST['add_expense'])) {
    if (!$canEdit) { http_response_code(403); exit('You do not have permission to add expenses.'); }
    $qty  = (int)($_POST['qty'] ?? 0);
    $cost = (float)($_POST['cost_price'] ?? 0);
    $amt  = (float)($_POST['amount'] ?? 0);
    if ($amt <= 0 && $qty > 0 && $cost > 0) { $amt = $qty * $cost; }
    $pdo->prepare("INSERT INTO sp_manual_expenses
        (company_id, sd_no, expense_date, machine_no, description, packing_no, qty, cost_price, amount, note)
        VALUES (?,?,?,?,?,?,?,?,?,?)")
        ->execute([$companyId, $sd, ($_POST['expense_date'] ?? '') ?: null,
            trim($_POST['machine_no'] ?? ''), trim($_POST['description'] ?? ''), trim($_POST['packing_no'] ?? ''),
            $qty, $cost, $amt, trim($_POST['note'] ?? '')]);
    header('Location: ' . $redirBack); exit;
}

/* Delete a manual expense */
if (isset($_GET['del_exp'])) {
    if (!$canEdit) { http_response_code(403); exit('No permission.'); }
    $pdo->prepare("DELETE FROM sp_manual_expenses WHERE id=? AND company_id=? AND sd_no=?")
        ->execute([(int)$_GET['del_exp'], $companyId, $sd]);
    header('Location: ' . $redirBack); exit;
}

/* Customer name */
$cst = $pdo->prepare("SELECT customer_name FROM sp_customers WHERE company_id=? AND sd_no=? LIMIT 1");
$cst->execute([$companyId, $sd]);
$customerName = $cst->fetchColumn() ?: '';

/* Filter values */
$year  = (int)($_GET['year'] ?? date('Y'));
$month = (int)($_GET['month'] ?? 0); // 0 = all months

/* Years that have stock-out data for this customer */
$yst = $pdo->prepare("SELECT DISTINCT YEAR(move_date) y FROM sp_stock_movements
                      WHERE company_id=? AND sd_no=? AND direction='Out' AND move_date IS NOT NULL ORDER BY y DESC");
$yst->execute([$companyId, $sd]);
$years = array_map('intval', $yst->fetchAll(PDO::FETCH_COLUMN));
if (!$years) { $years = [ (int)date('Y') ]; }
if (!in_array($year, $years, true)) { $year = $years[0]; }

/* Monthly totals for the selected year */
$mst = $pdo->prepare("
    SELECT MONTH(move_date) m, SUM(qty * cost_price) total
    FROM sp_stock_movements
    WHERE company_id=? AND sd_no=? AND direction='Out' AND YEAR(move_date)=?
    GROUP BY MONTH(move_date)
");
$mst->execute([$companyId, $sd, $year]);
$monthTotals = [];
foreach ($mst->fetchAll(PDO::FETCH_ASSOC) as $r) { $monthTotals[(int)$r['m']] = (float)$r['total']; }

/* add manual expenses to the monthly totals */
$mmt = $pdo->prepare("SELECT MONTH(expense_date) m, SUM(amount) total FROM sp_manual_expenses
                      WHERE company_id=? AND sd_no=? AND YEAR(expense_date)=? GROUP BY MONTH(expense_date)");
$mmt->execute([$companyId, $sd, $year]);
foreach ($mmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $mi=(int)$r['m']; $monthTotals[$mi] = ($monthTotals[$mi] ?? 0) + (float)$r['total']; }

$yearTotal = array_sum($monthTotals);

/* Detail rows for the selected filter */
$sql = "SELECT m.move_date, m.machine_no, m.packing_no, m.qty, m.cost_price,
               (m.qty * m.cost_price) AS amount,
               i.order_code, i.short_code, i.description
        FROM sp_stock_movements m
        LEFT JOIN sp_stock_items i ON i.id = m.item_id AND i.company_id = m.company_id
        WHERE m.company_id=? AND m.sd_no=? AND m.direction='Out' AND YEAR(m.move_date)=?";
$params = [$companyId, $sd, $year];
if ($month >= 1 && $month <= 12) { $sql .= " AND MONTH(m.move_date)=?"; $params[] = $month; }
$sql .= " ORDER BY m.move_date DESC, m.id DESC";
$st = $pdo->prepare($sql);
$st->execute($params);
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as &$r0) { $r0['is_manual'] = false; $r0['exp_id'] = 0; $r0['sort_date'] = $r0['move_date']; }
unset($r0);

/* manual expenses for the same period, merged in */
$msql = "SELECT id, expense_date, machine_no, description, packing_no, qty, cost_price, amount, note
         FROM sp_manual_expenses WHERE company_id=? AND sd_no=? AND YEAR(expense_date)=?";
$mparams = [$companyId, $sd, $year];
if ($month >= 1 && $month <= 12) { $msql .= " AND MONTH(expense_date)=?"; $mparams[] = $month; }
$mstmt = $pdo->prepare($msql);
$mstmt->execute($mparams);
foreach ($mstmt->fetchAll(PDO::FETCH_ASSOC) as $mr) {
    $rows[] = [
        'move_date'  => $mr['expense_date'],
        'sort_date'  => $mr['expense_date'],
        'machine_no' => $mr['machine_no'],
        'order_code' => $mr['description'], 'short_code' => '', 'description' => $mr['description'],
        'packing_no' => $mr['packing_no'],
        'qty'        => $mr['qty'],
        'cost_price' => $mr['cost_price'],
        'amount'     => $mr['amount'],
        'is_manual'  => true,
        'exp_id'     => (int)$mr['id'],
    ];
}
usort($rows, function ($a, $b) { return strcmp((string)$b['sort_date'], (string)$a['sort_date']); });

$periodTotal = 0;
foreach ($rows as $r) { $periodTotal += (float)$r['amount']; }

$monthNames = [1=>'January',2=>'February',3=>'March',4=>'April',5=>'May',6=>'June',7=>'July',8=>'August',9=>'September',10=>'October',11=>'November',12=>'December'];
?>
<!DOCTYPE html>
<html lang="en"><head>
    <meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Customer Expenses - <?= esc($customerName ?: $sd) ?></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; }
        .topbar { background:#0f172a; color:#fff; padding:14px 20px; display:flex; align-items:center; gap:16px; flex-wrap:wrap; }
        .topbar a { color:#93c5fd; text-decoration:none; font-weight:600; }
        .wrap { max-width:1100px; margin:24px auto; padding:0 16px 48px; }
        .hero { background:linear-gradient(135deg,#1e40af,#172a6b); color:#fff; border-radius:18px; padding:22px 24px; margin-bottom:18px; }
        .hero h1 { margin:0; font-size:24px; font-weight:800; }
        .hero .pills { margin-top:12px; display:flex; gap:10px; flex-wrap:wrap; }
        .pill { background:rgba(255,255,255,.2); border:1px solid rgba(255,255,255,.3); padding:6px 12px; border-radius:999px; font-weight:700; font-size:13px; }
        .pill.total { background:#fff; color:#1e40af; }
        .card { background:#fff; border:1px solid #e6eaf2; border-radius:16px; box-shadow:0 12px 30px rgba(30,41,59,.08); padding:20px; margin-bottom:18px; }
        .filter { display:flex; gap:10px; flex-wrap:wrap; align-items:flex-end; }
        .filter label { display:block; font-size:12px; font-weight:700; color:#64748b; text-transform:uppercase; margin-bottom:5px; }
        .filter select { padding:10px 12px; border:1px solid #e2e8f0; border-radius:10px; font-size:14px; font-family:inherit; background:#fff; }
        .btn { background:#2563eb; color:#fff; border:none; padding:11px 18px; border-radius:10px; cursor:pointer; font-weight:700; font-size:14px; font-family:inherit; text-decoration:none; }
        .btn:hover { background:#1d4ed8; }
        h2 { font-size:16px; font-weight:800; margin:0 0 12px; }
        .table-scroll { overflow-x:auto; border-radius:12px; }
        table { width:100%; border-collapse:collapse; min-width:720px; }
        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; white-space:nowrap; }
        tbody td { padding:11px 10px; border-bottom:1px solid #eef2f7; font-size:13px; }
        tbody tr:hover { background:#f9fafe; }
        .num { text-align:right; }
        .month-grid { display:grid; grid-template-columns:repeat(4,1fr); gap:10px; }
        .mbox { border:1px solid #e6eaf2; border-radius:12px; padding:12px 14px; }
        .mbox.active { border-color:#2563eb; background:#eff6ff; }
        .mbox .mn { font-size:12px; color:#64748b; font-weight:700; }
        .mbox .mv { font-size:16px; font-weight:800; margin-top:3px; }
        .mbox a { text-decoration:none; color:inherit; display:block; }
        .grand { text-align:right; font-size:18px; font-weight:800; color:#1e40af; }
        .muted { color:#94a3b8; }
        @media (max-width:700px){ .month-grid{ grid-template-columns:repeat(2,1fr);} }
    </style>
</head><body>
    <?php $spActive = 'expenses'; include 'sp_nav.php'; ?>
    <div style="max-width:1100px; margin:0 auto; padding:0 16px 4px;">
        <a href="sp_customer_view.php?sd=<?= urlencode($sd) ?>" style="color:#2563eb; font-weight:700; text-decoration:none; font-size:13px;">&larr; Back to this customer</a>
    </div>

    <div class="wrap">
        <div class="hero">
            <h1><?= esc($customerName ?: '(no name)') ?> — Expenses</h1>
            <div class="pills">
                <span class="pill">SD No: <?= esc($sd) ?></span>
                <span class="pill total">Year <?= esc($year) ?> total: <?= money($yearTotal) ?></span>
                <?php if ($month >=1 && $month <=12): ?>
                    <span class="pill"><?= esc($monthNames[$month]) ?> total: <?= money($periodTotal) ?></span>
                <?php endif; ?>
            </div>
        </div>

        <div class="card">
            <form method="GET" class="filter">
                <input type="hidden" name="sd" value="<?= esc($sd) ?>">
                <div>
                    <label>Year</label>
                    <select name="year">
                        <?php foreach ($years as $y): ?><option value="<?= $y ?>" <?= $y===$year?'selected':'' ?>><?= $y ?></option><?php endforeach; ?>
                    </select>
                </div>
                <div>
                    <label>Month</label>
                    <select name="month">
                        <option value="0" <?= $month===0?'selected':'' ?>>All months</option>
                        <?php foreach ($monthNames as $mi=>$mn): ?><option value="<?= $mi ?>" <?= $month===$mi?'selected':'' ?>><?= $mn ?></option><?php endforeach; ?>
                    </select>
                </div>
                <button type="submit" class="btn">Show</button>
            </form>
        </div>

        <!-- Monthly breakdown for the year -->
        <div class="card">
            <h2>Monthly totals — <?= esc($year) ?></h2>
            <div class="month-grid">
                <?php for ($mi=1; $mi<=12; $mi++): $mt = $monthTotals[$mi] ?? 0; ?>
                    <div class="mbox <?= $month===$mi?'active':'' ?>">
                        <a href="sp_customer_expenses.php?sd=<?= urlencode($sd) ?>&year=<?= $year ?>&month=<?= $mi ?>">
                            <div class="mn"><?= esc($monthNames[$mi]) ?></div>
                            <div class="mv"><?= $mt>0 ? money($mt) : '<span class="muted">—</span>' ?></div>
                        </a>
                    </div>
                <?php endfor; ?>
            </div>
        </div>

        <!-- Detail lines -->
        <div class="card">
            <h2>Expense details<?= ($month>=1&&$month<=12) ? ' — ' . esc($monthNames[$month]) . ' ' . esc($year) : ' — ' . esc($year) . ' (all months)' ?></h2>
            <div class="table-scroll">
                <table>
                    <thead>
                        <tr>
                            <th>Date</th><th>Machine No</th><th>Item / Description</th><th>Packing No</th>
                            <th class="num">Qty</th><th class="num">Cost Price</th><th class="num">Amount</th><th>Source</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($rows): ?>
                            <?php foreach ($rows as $r): ?>
                                <tr>
                                    <td><?= $r['move_date'] ? esc(date('d/m/Y', strtotime($r['move_date']))) : '' ?></td>
                                    <td><?= esc($r['machine_no']) ?></td>
                                    <td><?= esc($r['order_code'] ?: $r['short_code'] ?: $r['description']) ?></td>
                                    <td><?= esc($r['packing_no']) ?></td>
                                    <td class="num"><?= esc($r['qty']) ?></td>
                                    <td class="num"><?= money($r['cost_price']) ?></td>
                                    <td class="num"><strong><?= money($r['amount']) ?></strong></td>
                                    <td>
                                        <?php if ($r['is_manual']): ?>
                                            <span style="background:#fef3c7;color:#92400e;padding:2px 8px;border-radius:999px;font-size:11px;font-weight:700;">Manual</span>
                                            <?php if ($canEdit): ?>
                                                <a href="sp_customer_expenses.php?del_exp=<?= (int)$r['exp_id'] ?>&sd=<?= urlencode($sd) ?>&year=<?= $year ?>&month=<?= $month ?>" onclick="return confirm('Delete this expense?')" style="color:#dc2626;text-decoration:none;font-weight:800;margin-left:6px;">&times;</a>
                                            <?php endif; ?>
                                        <?php else: ?>
                                            <span style="background:#dbeafe;color:#1d4ed8;padding:2px 8px;border-radius:999px;font-size:11px;font-weight:700;">Stock</span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            <tr>
                                <td colspan="6" class="num"><strong>Total expenses</strong></td>
                                <td class="num grand"><?= money($periodTotal) ?></td>
                                <td></td>
                            </tr>
                        <?php else: ?>
                            <tr><td colspan="8" style="text-align:center;" class="muted">No expenses for this period.</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <?php if ($canEdit): ?>
        <!-- Add a manual expense -->
        <div class="card">
            <h2>Add other expense</h2>
            <form method="POST">
                <input type="hidden" name="sd" value="<?= esc($sd) ?>">
                <input type="hidden" name="year" value="<?= esc($year) ?>">
                <input type="hidden" name="month" value="<?= esc($month) ?>">
                <div style="display:grid; grid-template-columns:repeat(4,1fr); gap:12px;">
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Date</label><input type="date" name="expense_date" value="<?= date('Y-m-d') ?>" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Machine No</label><input type="text" name="machine_no" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div style="grid-column:span 2;"><label style="font-size:12px;font-weight:700;color:#64748b;">Description</label><input type="text" name="description" placeholder="What is this expense?" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Packing No</label><input type="text" name="packing_no" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Qty</label><input type="number" name="qty" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Cost Price</label><input type="number" step="0.01" name="cost_price" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                    <div><label style="font-size:12px;font-weight:700;color:#64748b;">Amount</label><input type="number" step="0.01" name="amount" placeholder="auto = qty × cost" style="width:100%;padding:9px 11px;border:1px solid #e2e8f0;border-radius:9px;font-family:inherit;"></div>
                </div>
                <div style="margin-top:12px;">
                    <button type="submit" name="add_expense" class="btn">+ Add Expense</button>
                    <span class="muted" style="font-size:13px;margin-left:8px;">If Amount is blank, it will be Qty × Cost Price.</span>
                </div>
            </form>
        </div>
        <?php endif; ?>
    </div>
</body></html>