<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Curio Consignment Sales Report';

const CURIO_CATEGORY_ID = 1010;

if (!function_exists('h')) {
    function h($v): string { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($a): string { return number_format((float)$a, 2); }
}
if (!function_exists('ccr_table_exists')) {
    function ccr_table_exists(PDO $pdo, string $t): bool {
        $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=:t");
        $s->execute([':t' => $t]);
        return (int)$s->fetchColumn() > 0;
    }
}
if (!function_exists('ccr_col')) {
    function ccr_col(PDO $pdo, string $t, array $cands): ?string {
        foreach ($cands as $c) {
            $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=:t AND column_name=:c");
            $s->execute([':t' => $t, ':c' => $c]);
            if ((int)$s->fetchColumn() > 0) return $c;
        }
        return null;
    }
}

$errors = [];

/* Date range: default to current month. */
$from = trim((string)($_GET['from'] ?? date('Y-m-01')));
$to   = trim((string)($_GET['to'] ?? date('Y-m-d')));
if (strtotime($from) === false) $from = date('Y-m-01');
if (strtotime($to) === false)   $to = date('Y-m-d');
$supplierFilter = (int)($_GET['supplier_id'] ?? 0);

$hasConsign = ccr_table_exists($pdo, 'curio_consignment');
$hasOrders  = ccr_table_exists($pdo, 'restaurant_orders');
$hasItems   = ccr_table_exists($pdo, 'restaurant_order_items');
$hasInv     = ccr_table_exists($pdo, 'inventory_items');
$hasSup     = ccr_table_exists($pdo, 'inventory_suppliers');

/* Supplier dropdown for the filter. */
$suppliers = [];
if ($hasSup) {
    $suppliers = $pdo->query("SELECT id, supplier_name FROM inventory_suppliers WHERE COALESCE(is_active,1)=1 ORDER BY supplier_name ASC")->fetchAll(PDO::FETCH_ASSOC);
}

$rows = [];
$soldToDate = [];
if ($hasConsign && $hasOrders && $hasItems && $hasInv) {
    $invNameCol = ccr_col($pdo, 'inventory_items', ['item_name','name','description']) ?: 'item_name';
    $invCatCol  = ccr_col($pdo, 'inventory_items', ['category_id','inventory_category_id','category']) ?: 'category_id';

    /*
       Sales come from closed/completed front-office orders only, joined to the
       curio consignment map by inventory item. Curios with no configured
       supplier still appear (Unassigned) so nothing is silently dropped.
    */
    $params = [
        ':from' => $from . ' 00:00:00',
        ':to'   => $to . ' 23:59:59',
        ':cat'  => CURIO_CATEGORY_ID,
    ];
    $supWhere = '';
    if ($supplierFilter > 0) {
        $supWhere = " AND cc.supplier_id = :sup ";
        $params[':sup'] = $supplierFilter;
    }

    $sql = "
        SELECT
            COALESCE(cc.supplier_id, 0)                AS supplier_id,
            COALESCE(sup.supplier_name, 'Unassigned')  AS supplier_name,
            ii.id                                      AS item_id,
            ii.`$invNameCol`                           AS item_name,
            COALESCE(cc.unit_cost, 0)                  AS unit_cost,
            COALESCE(cc.commission_pct, 0)             AS commission_pct,
            COALESCE(cc.qty_received, 0)               AS qty_received,
            SUM(roi.qty)                               AS qty_sold,
            SUM(roi.line_total)                        AS gross_sales
        FROM restaurant_order_items roi
        INNER JOIN restaurant_orders ro
            ON ro.id = roi.restaurant_order_id
           AND ro.order_type = 'front_office'
           AND ro.status = 'closed'
        INNER JOIN inventory_items ii
            ON ii.id = roi.inventory_item_id
           AND ii.`$invCatCol` = :cat
        LEFT JOIN curio_consignment cc
            ON cc.item_id = ii.id AND COALESCE(cc.is_active,1) = 1
        LEFT JOIN inventory_suppliers sup
            ON sup.id = cc.supplier_id
        WHERE COALESCE(roi.status,'active') = 'active'
          AND roi.line_total > 0
          AND COALESCE(ro.closed_at, ro.updated_at, ro.opened_at) BETWEEN :from AND :to
          $supWhere
        GROUP BY cc.supplier_id, sup.supplier_name, ii.id, ii.`$invNameCol`, cc.unit_cost, cc.commission_pct, cc.qty_received
        ORDER BY supplier_name ASC, item_name ASC
    ";

    try {
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not build report: ' . $e->getMessage();
    }

    /*
       All-time sold per curio (ignores the date filter and supplier filter) so
       "On Hand" is a true lifetime stock balance: received-to-date minus
       sold-to-date. The dated query above still drives the sales/payable
       figures for the chosen period.
    */
    $soldToDate = [];
    try {
        $st2 = $pdo->prepare("
            SELECT roi.inventory_item_id AS item_id, SUM(roi.qty) AS qty
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro
                ON ro.id = roi.restaurant_order_id
               AND ro.order_type = 'front_office'
               AND ro.status = 'closed'
            INNER JOIN inventory_items ii
                ON ii.id = roi.inventory_item_id
               AND ii.`$invCatCol` = :cat
            WHERE COALESCE(roi.status,'active') = 'active'
              AND roi.line_total > 0
            GROUP BY roi.inventory_item_id
        ");
        $st2->execute([':cat' => CURIO_CATEGORY_ID]);
        foreach ($st2->fetchAll(PDO::FETCH_ASSOC) as $r2) {
            $soldToDate[(int)$r2['item_id']] = (float)$r2['qty'];
        }
    } catch (Throwable $e) {
        /* Non-fatal: on-hand will fall back to period sold. */
    }
} else {
    $errors[] = 'Required tables are missing. Configure curios and record at least one curio POS sale first.';
}

/* Group rows by supplier for subtotals. */
$bySupplier = [];
foreach ($rows as $r) {
    $sid = (int)$r['supplier_id'];
    if (!isset($bySupplier[$sid])) {
        $bySupplier[$sid] = ['name' => $r['supplier_name'], 'items' => [], 'qty'=>0, 'gross'=>0, 'pay_cost'=>0, 'pay_comm'=>0, 'has_comm'=>false, 'received'=>0, 'on_hand'=>0];
    }
    $qty   = (float)$r['qty_sold'];
    $gross = (float)$r['gross_sales'];
    $cost  = (float)$r['unit_cost'];
    $comm  = (float)$r['commission_pct'];
    $recv  = (float)$r['qty_received'];

    /* On hand uses all-time sold (falls back to period sold if unavailable). */
    $soldAll = isset($soldToDate[(int)$r['item_id']]) ? (float)$soldToDate[(int)$r['item_id']] : $qty;
    $onHand  = round($recv - $soldAll, 2);

    $payCost = round($cost * $qty, 2);
    $payComm = $comm > 0 ? round($gross * (1 - $comm/100), 2) : null;

    $bySupplier[$sid]['items'][] = [
        'item_name' => $r['item_name'],
        'qty' => $qty, 'gross' => $gross,
        'unit_cost' => $cost, 'commission_pct' => $comm,
        'pay_cost' => $payCost, 'pay_comm' => $payComm,
        'received' => $recv, 'on_hand' => $onHand,
    ];
    $bySupplier[$sid]['qty']   += $qty;
    $bySupplier[$sid]['gross'] += $gross;
    $bySupplier[$sid]['pay_cost'] += $payCost;
    $bySupplier[$sid]['received'] += $recv;
    $bySupplier[$sid]['on_hand'] += $onHand;
    if ($payComm !== null) { $bySupplier[$sid]['pay_comm'] += $payComm; $bySupplier[$sid]['has_comm'] = true; }
}

$grandQty = $grandGross = $grandPayCost = $grandPayComm = 0;
foreach ($bySupplier as $s) {
    $grandQty += $s['qty']; $grandGross += $s['gross'];
    $grandPayCost += $s['pay_cost']; $grandPayComm += $s['pay_comm'];
}

/* CSV export. */
if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="curio_consignment_' . date('Ymd_His') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Supplier','Curio','Qty Sold','Received','On Hand (all-time)','Gross Sales','Unit Cost','Payable (Cost)','Commission %','Payable (Commission)','Lodge Margin (Cost basis)']);
    foreach ($bySupplier as $s) {
        foreach ($s['items'] as $it) {
            fputcsv($out, [
                $s['name'], $it['item_name'], $it['qty'],
                $it['received'], $it['on_hand'],
                number_format($it['gross'],2,'.',''),
                number_format($it['unit_cost'],2,'.',''), number_format($it['pay_cost'],2,'.',''),
                $it['commission_pct'] > 0 ? number_format($it['commission_pct'],2,'.','') : '',
                $it['pay_comm'] !== null ? number_format($it['pay_comm'],2,'.','') : '',
                number_format($it['gross'] - $it['pay_cost'],2,'.',''),
            ]);
        }
        fputcsv($out, [$s['name'].' TOTAL','', $s['qty'], $s['received'], $s['on_hand'], number_format($s['gross'],2,'.',''),'', number_format($s['pay_cost'],2,'.',''),'', $s['has_comm']?number_format($s['pay_comm'],2,'.','') : '', number_format($s['gross']-$s['pay_cost'],2,'.','')]);
    }
    fclose($out);
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-graph-up"></i> Consignment</div>
            <div class="hero-title mt-2">Curio Consignment Sales — by Supplier</div>
            <div class="hero-subtitle mt-2">Curios sold in the Front Office POS, grouped by the supplier holding them on consignment.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="curio_consignment.php" class="btn btn-light"><i class="bi bi-gear me-2"></i>Configure Suppliers</a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger"><ul class="mb-0"><?php foreach ($errors as $e): ?><li><?php echo h($e); ?></li><?php endforeach; ?></ul></div>
<?php endif; ?>

<div class="card panel-card mb-3">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-3"><label class="form-label">From</label><input type="date" name="from" class="form-control" value="<?php echo h($from); ?>"></div>
            <div class="col-md-3"><label class="form-label">To</label><input type="date" name="to" class="form-control" value="<?php echo h($to); ?>"></div>
            <div class="col-md-3">
                <label class="form-label">Supplier</label>
                <select name="supplier_id" class="form-select">
                    <option value="0">All suppliers</option>
                    <?php foreach ($suppliers as $s): ?>
                        <option value="<?php echo (int)$s['id']; ?>" <?php echo $supplierFilter === (int)$s['id'] ? 'selected' : ''; ?>><?php echo h($s['supplier_name']); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3 d-flex gap-2">
                <button class="btn btn-primary flex-fill"><i class="bi bi-search me-1"></i>Run</button>
                <button name="export" value="csv" class="btn btn-success"><i class="bi bi-file-earmark-spreadsheet"></i></button>
            </div>
        </form>
    </div>
</div>

<?php if (!$errors): ?>
    <div class="row g-3 mb-3">
        <div class="col-md-3"><div class="metric-card"><div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-bag"></i></div><div><div class="metric-label">Qty Sold</div><div class="metric-value"><?php echo money_fmt($grandQty); ?></div></div></div></div>
        <div class="col-md-3"><div class="metric-card"><div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-stack"></i></div><div><div class="metric-label">Gross Sales</div><div class="metric-value small-value"><?php echo money_fmt($grandGross); ?></div></div></div></div>
        <div class="col-md-3"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-truck"></i></div><div><div class="metric-label">Payable (Cost)</div><div class="metric-value small-value"><?php echo money_fmt($grandPayCost); ?></div></div></div></div>
        <div class="col-md-3"><div class="metric-card"><div class="metric-icon bg-info-subtle text-info"><i class="bi bi-percent"></i></div><div><div class="metric-label">Payable (Commission)</div><div class="metric-value small-value"><?php echo money_fmt($grandPayComm); ?></div></div></div></div>
    </div>

    <?php foreach ($bySupplier as $sid => $sup): ?>
        <div class="card panel-card mb-3">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-truck me-2"></i><?php echo h($sup['name']); ?><?php echo $sid === 0 ? ' <span class="badge text-bg-warning ms-1">No supplier set</span>' : ''; ?></h5>
                <span class="badge text-bg-light"><?php echo count($sup['items']); ?> curio(s)</span>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Curio</th>
                                <th class="text-end">Qty Sold</th>
                                <th class="text-end">Received</th>
                                <th class="text-end">On Hand<br><span class="small text-muted">(all-time)</span></th>
                                <th class="text-end">Gross Sales</th>
                                <th class="text-end">Unit Cost</th>
                                <th class="text-end">Payable (Cost)</th>
                                <th class="text-end">Commission %</th>
                                <th class="text-end">Payable (Commission)</th>
                                <th class="text-end">Lodge Margin<br><span class="small text-muted">(cost basis)</span></th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($sup['items'] as $it): ?>
                                <tr>
                                    <td><?php echo h($it['item_name']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($it['qty']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($it['received']); ?></td>
                                    <td class="text-end <?php echo $it['on_hand'] < 0 ? 'text-danger fw-bold' : ''; ?>"><?php echo money_fmt($it['on_hand']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($it['gross']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($it['unit_cost']); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($it['pay_cost']); ?></td>
                                    <td class="text-end"><?php echo $it['commission_pct'] > 0 ? money_fmt($it['commission_pct']).'%' : '<span class="text-muted">—</span>'; ?></td>
                                    <td class="text-end fw-bold"><?php echo $it['pay_comm'] !== null ? money_fmt($it['pay_comm']) : '<span class="text-muted">—</span>'; ?></td>
                                    <td class="text-end"><?php echo money_fmt($it['gross'] - $it['pay_cost']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr class="table-light fw-bold">
                                <td><?php echo h($sup['name']); ?> Total</td>
                                <td class="text-end"><?php echo money_fmt($sup['qty']); ?></td>
                                <td class="text-end"><?php echo money_fmt($sup['received']); ?></td>
                                <td class="text-end <?php echo $sup['on_hand'] < 0 ? 'text-danger' : ''; ?>"><?php echo money_fmt($sup['on_hand']); ?></td>
                                <td class="text-end"><?php echo money_fmt($sup['gross']); ?></td>
                                <td></td>
                                <td class="text-end"><?php echo money_fmt($sup['pay_cost']); ?></td>
                                <td></td>
                                <td class="text-end"><?php echo $sup['has_comm'] ? money_fmt($sup['pay_comm']) : '<span class="text-muted">—</span>'; ?></td>
                                <td class="text-end"><?php echo money_fmt($sup['gross'] - $sup['pay_cost']); ?></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
    <?php endforeach; ?>

    <?php if (!$bySupplier): ?>
        <div class="alert alert-light border">No curio sales found in this period.</div>
    <?php endif; ?>
<?php endif; ?>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>