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

$pageTitle = 'Front Office POS Sales';

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('money_fmt')) {
    function money_fmt($amount): string
    {
        return number_format((float)$amount, 2);
    }
}

if (!function_exists('fo_table_exists')) {
    function fo_table_exists(PDO $pdo, string $table): bool
    {
        static $cache = [];
        if (array_key_exists($table, $cache)) {
            return $cache[$table];
        }
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :t");
            $stmt->execute([':t' => $table]);
            $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$table] = false;
        }
        return $cache[$table];
    }
}

if (!function_exists('fo_column_exists')) {
    function fo_column_exists(PDO $pdo, string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c");
            $stmt->execute([':t' => $table, ':c' => $column]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$key] = false;
        }
        return $cache[$key];
    }
}

$errors = [];
$today = date('Y-m-d');
$dateFrom = trim((string)($_GET['date_from'] ?? $today));
$dateTo = trim((string)($_GET['date_to'] ?? $today));
$propertyId = (int)($_GET['property_id'] ?? 0);
$status = trim((string)($_GET['status'] ?? ''));
$paymentStatus = trim((string)($_GET['payment_status'] ?? ''));
$q = trim((string)($_GET['q'] ?? ''));

if ($dateFrom === '' || strtotime($dateFrom) === false) {
    $dateFrom = $today;
}
if ($dateTo === '' || strtotime($dateTo) === false) {
    $dateTo = $today;
}
if (strtotime($dateFrom) > strtotime($dateTo)) {
    $tmp = $dateFrom;
    $dateFrom = $dateTo;
    $dateTo = $tmp;
}

$required = ['restaurant_orders', 'restaurant_order_items', 'restaurant_payments'];
foreach ($required as $tbl) {
    if (!fo_table_exists($pdo, $tbl)) {
        $errors[] = 'Required POS table missing: ' . $tbl;
    }
}

$properties = [];
if (!$errors && fo_table_exists($pdo, 'properties')) {
    try {
        $properties = $pdo->query("SELECT id, name, code FROM properties WHERE COALESCE(is_active,1)=1 ORDER BY name ASC")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $properties = [];
    }
}

$hasCashierName = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'cashier_name');
$hasOrderType = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'order_type');
$hasGroupName = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'group_name');
$hasRoomNo = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'room_no');
$hasLinkedOrder = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'linked_order_id');
$hasProperty = !$errors && fo_column_exists($pdo, 'restaurant_orders', 'property_id');

$sales = [];
$summary = [
    'sales_count' => 0,
    'subtotal' => 0,
    'tax_total' => 0,
    'total_amount' => 0,
    'paid_amount' => 0,
    'balance' => 0,
    'cash_total' => 0,
    'card_total' => 0,
    'folio_total' => 0,
];
$paymentBreakdown = [];
$itemBreakdown = [];

if (!$errors) {
    try {
        $where = [];
        $params = [];

        $where[] = "DATE(ro.opened_at) BETWEEN :date_from AND :date_to";
        $params[':date_from'] = $dateFrom;
        $params[':date_to'] = $dateTo;

        // Front Office POS orders created by front_office_pos.php should use one of these order types/notes.
        // This keeps restaurant table orders out of this report.
        if ($hasOrderType) {
            $where[] = "(ro.order_type IN ('front_office','frontoffice','spa','curio','walkin','room') AND (ro.table_id IS NULL OR ro.table_id = 0))";
        } else {
            $where[] = "(ro.table_id IS NULL OR ro.table_id = 0)";
        }

        if ($propertyId > 0 && $hasProperty) {
            $where[] = "ro.property_id = :property_id";
            $params[':property_id'] = $propertyId;
        }

        if ($status !== '') {
            $where[] = "ro.status = :status";
            $params[':status'] = $status;
        }

        if ($paymentStatus !== '') {
            $where[] = "ro.payment_status = :payment_status";
            $params[':payment_status'] = $paymentStatus;
        }

        if ($q !== '') {
            $where[] = "(
                ro.order_no LIKE :q
                OR COALESCE(ro.guest_name,'') LIKE :q
                OR COALESCE(ro.notes,'') LIKE :q
                " . ($hasRoomNo ? " OR COALESCE(ro.room_no,'') LIKE :q " : "") . "
                " . ($hasCashierName ? " OR COALESCE(ro.cashier_name,'') LIKE :q " : "") . "
            )";
            $params[':q'] = '%' . $q . '%';
        }

        $propertySelect = $hasProperty ? "p.name AS property_name, p.code AS property_code," : "'' AS property_name, '' AS property_code,";
        $cashierSelect = $hasCashierName ? "ro.cashier_name" : "'' AS cashier_name";
        $roomNoSelect = $hasRoomNo ? "ro.room_no" : "'' AS room_no";
        $groupNameSelect = $hasGroupName ? "ro.group_name" : "'' AS group_name";
        $linkedOrderSelect = $hasLinkedOrder ? "ro.linked_order_id" : "NULL AS linked_order_id";

        $sql = "
            SELECT
                ro.id,
                ro.order_no,
                " . ($hasOrderType ? "ro.order_type" : "'frontoffice' AS order_type") . ",
                {$linkedOrderSelect},
                ro.guest_name,
                {$roomNoSelect},
                {$groupNameSelect},
                ro.status,
                ro.payment_status,
                ro.subtotal,
                ro.tax_total,
                ro.total_amount,
                ro.opened_at,
                ro.closed_at,
                {$cashierSelect},
                {$propertySelect}
                COALESCE(pay.paid_amount,0) AS paid_amount,
                COALESCE(items.item_count,0) AS item_count,
                COALESCE(items.qty_total,0) AS qty_total,
                COALESCE(methods.payment_methods,'') AS payment_methods
            FROM restaurant_orders ro
            " . ($hasProperty ? "LEFT JOIN properties p ON p.id = ro.property_id" : "") . "
            LEFT JOIN (
                SELECT restaurant_order_id, SUM(amount) AS paid_amount
                FROM restaurant_payments
                WHERE status = 'posted'
                GROUP BY restaurant_order_id
            ) pay ON pay.restaurant_order_id = ro.id
            LEFT JOIN (
                SELECT restaurant_order_id, COUNT(*) AS item_count, SUM(qty) AS qty_total
                FROM restaurant_order_items
                WHERE status = 'active'
                GROUP BY restaurant_order_id
            ) items ON items.restaurant_order_id = ro.id
            LEFT JOIN (
                SELECT restaurant_order_id, GROUP_CONCAT(DISTINCT payment_method ORDER BY payment_method SEPARATOR ', ') AS payment_methods
                FROM restaurant_payments
                WHERE status = 'posted'
                GROUP BY restaurant_order_id
            ) methods ON methods.restaurant_order_id = ro.id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY ro.opened_at DESC, ro.id DESC
            LIMIT 1000
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($sales as $row) {
            $summary['sales_count']++;
            $summary['subtotal'] += (float)$row['subtotal'];
            $summary['tax_total'] += (float)$row['tax_total'];
            $summary['total_amount'] += (float)$row['total_amount'];
            $summary['paid_amount'] += (float)$row['paid_amount'];
        }
        $summary['balance'] = $summary['total_amount'] - $summary['paid_amount'];

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

        $stmt = $pdo->prepare("
            SELECT rp.payment_method, SUM(rp.amount) AS amount
            FROM restaurant_payments rp
            INNER JOIN restaurant_orders ro ON ro.id = rp.restaurant_order_id
            " . ($hasProperty ? "LEFT JOIN properties p ON p.id = ro.property_id" : "") . "
            WHERE rp.status = 'posted'
              AND {$whereForSub}
            GROUP BY rp.payment_method
            ORDER BY amount DESC
        ");
        $stmt->execute($params);
        $paymentBreakdown = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($paymentBreakdown as $pb) {
            $methodLower = strtolower((string)$pb['payment_method']);
            $amount = (float)$pb['amount'];
            if (strpos($methodLower, 'cash') !== false) {
                $summary['cash_total'] += $amount;
            } elseif (strpos($methodLower, 'card') !== false || strpos($methodLower, 'pos') !== false || strpos($methodLower, 'visa') !== false || strpos($methodLower, 'master') !== false) {
                $summary['card_total'] += $amount;
            } elseif (strpos($methodLower, 'folio') !== false || strpos($methodLower, 'room') !== false || strpos($methodLower, 'guest') !== false || strpos($methodLower, 'master') !== false) {
                $summary['folio_total'] += $amount;
            }
        }

        $stmt = $pdo->prepare("
            SELECT roi.item_name, SUM(roi.qty) AS qty, SUM(roi.line_total + roi.tax_amount) AS amount
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id
            " . ($hasProperty ? "LEFT JOIN properties p ON p.id = ro.property_id" : "") . "
            WHERE roi.status = 'active'
              AND {$whereForSub}
            GROUP BY roi.item_name
            ORDER BY amount DESC
            LIMIT 30
        ");
        $stmt->execute($params);
        $itemBreakdown = $stmt->fetchAll(PDO::FETCH_ASSOC);

    } catch (Throwable $e) {
        $errors[] = 'Could not load Front Office POS sales: ' . $e->getMessage();
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'front_office_pos_sales_' . date('Ymd_His') . '.csv';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Order No', 'Date', 'Guest', 'Room', 'Type', 'Status', 'Payment Status', 'Items', 'Subtotal', 'Tax', 'Total', 'Paid', 'Balance', 'Methods', 'Cashier']);
    foreach ($sales as $row) {
        $balance = (float)$row['total_amount'] - (float)$row['paid_amount'];
        fputcsv($out, [
            $row['order_no'],
            $row['opened_at'],
            $row['guest_name'],
            $row['room_no'],
            $row['order_type'],
            $row['status'],
            $row['payment_status'],
            $row['item_count'],
            number_format((float)$row['subtotal'], 2, '.', ''),
            number_format((float)$row['tax_total'], 2, '.', ''),
            number_format((float)$row['total_amount'], 2, '.', ''),
            number_format((float)$row['paid_amount'], 2, '.', ''),
            number_format($balance, 2, '.', ''),
            $row['payment_methods'],
            $row['cashier_name'],
        ]);
    }
    fclose($out);
    exit;
}

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

<div class="hero-band hero-compact">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-bag-check"></i> Front Office POS</div>
            <div class="hero-title mt-2">Spa & Curio Sales</div>
            <div class="hero-subtitle mt-2">Review Front Office POS sales, direct payments, PMS folio postings, and daily cashier activity.</div>
        </div>
        <div class="col-lg-4 text-lg-end d-flex gap-2 justify-content-lg-end flex-wrap">
            <a href="front_office_pos.php" class="btn btn-light"><i class="bi bi-cash-register me-2"></i>Open POS</a>
            <a href="sales_list.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-outline-light"><i class="bi bi-download me-2"></i>CSV</a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="card panel-card mb-4">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2">
                <label class="form-label">From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>
            <div class="col-md-2">
                <label class="form-label">To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </div>
            <div class="col-md-2">
                <label class="form-label">Property</label>
                <select name="property_id" class="form-select">
                    <option value="0">All properties</option>
                    <?php foreach ($properties as $p): ?>
                        <option value="<?php echo (int)$p['id']; ?>" <?php echo $propertyId === (int)$p['id'] ? 'selected' : ''; ?>>
                            <?php echo h($p['name']); ?><?php echo !empty($p['code']) ? ' - ' . h($p['code']) : ''; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Order Status</label>
                <select name="status" class="form-select">
                    <option value="">All</option>
                    <?php foreach (['open','sent','closed','void'] as $s): ?>
                        <option value="<?php echo h($s); ?>" <?php echo $status === $s ? 'selected' : ''; ?>><?php echo h(ucwords(str_replace('_',' ', $s))); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Payment</label>
                <select name="payment_status" class="form-select">
                    <option value="">All</option>
                    <?php foreach (['unpaid','part_paid','paid','room_charged','void'] as $ps): ?>
                        <option value="<?php echo h($ps); ?>" <?php echo $paymentStatus === $ps ? 'selected' : ''; ?>><?php echo h(ucwords(str_replace('_',' ', $ps))); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Search</label>
                <input type="text" name="q" class="form-control" value="<?php echo h($q); ?>" placeholder="Guest, room, order no...">
            </div>
            <div class="col-md-12 text-end">
                <button class="btn btn-primary"><i class="bi bi-funnel me-2"></i>Apply Filters</button>
                <a href="sales_list.php" class="btn btn-light">Reset</a>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-receipt"></i></div><div><div class="metric-label">Sales</div><div class="metric-value"><?php echo (int)$summary['sales_count']; ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><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">Total</div><div class="metric-value"><?php echo money_fmt($summary['total_amount']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-info-subtle text-info"><i class="bi bi-wallet2"></i></div><div><div class="metric-label">Paid</div><div class="metric-value"><?php echo money_fmt($summary['paid_amount']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-credit-card"></i></div><div><div class="metric-label">Card / POS</div><div class="metric-value"><?php echo money_fmt($summary['card_total']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-door-open"></i></div><div><div class="metric-label">Folio</div><div class="metric-value"><?php echo money_fmt($summary['folio_total']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-circle"></i></div><div><div class="metric-label">Balance</div><div class="metric-value"><?php echo money_fmt($summary['balance']); ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-xl-8">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Sales History</h5></div>
            <div class="card-body p-0">
                <div class="table-responsive table-freeze">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                        <tr>
                            <th>Sale</th>
                            <th>Guest / Room</th>
                            <th>Property</th>
                            <th>Status</th>
                            <th>Methods</th>
                            <th class="text-end">Items</th>
                            <th class="text-end">Total</th>
                            <th class="text-end">Paid</th>
                            <th class="text-end">Balance</th>
                            <th class="text-end">Action</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php if (!$sales): ?>
                            <tr><td colspan="10" class="text-center text-muted py-4">No Front Office POS sales found.</td></tr>
                        <?php else: ?>
                            <?php foreach ($sales as $row): ?>
                                <?php
                                $balance = (float)$row['total_amount'] - (float)$row['paid_amount'];
                                $propertyLabel = trim((string)($row['property_name'] ?? ''));
                                if (!empty($row['property_code'])) $propertyLabel .= ' (' . $row['property_code'] . ')';
                                $payClass = 'secondary';
                                if (($row['payment_status'] ?? '') === 'paid' || ($row['payment_status'] ?? '') === 'room_charged') $payClass = 'success';
                                elseif (($row['payment_status'] ?? '') === 'part_paid') $payClass = 'warning text-dark';
                                elseif (($row['payment_status'] ?? '') === 'void') $payClass = 'dark';
                                ?>
                                <tr>
                                    <td>
                                        <div class="data-stack">
                                            <strong><?php echo h($row['order_no']); ?></strong>
                                            <small><?php echo h($row['opened_at']); ?></small>
                                        </div>
                                    </td>
                                    <td>
                                        <div class="data-stack">
                                            <strong><?php echo h($row['guest_name'] ?: 'Walk-in Guest'); ?></strong>
                                            <small><?php echo !empty($row['room_no']) ? 'Room ' . h($row['room_no']) : h(ucwords((string)$row['order_type'])); ?></small>
                                        </div>
                                    </td>
                                    <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                    <td>
                                        <span class="badge text-bg-<?php echo h($payClass); ?>"><?php echo h(ucwords(str_replace('_',' ', (string)$row['payment_status']))); ?></span>
                                        <div class="small text-muted mt-1"><?php echo h(ucwords((string)$row['status'])); ?></div>
                                    </td>
                                    <td><?php echo h($row['payment_methods'] ?: '—'); ?></td>
                                    <td class="text-end"><?php echo (int)$row['item_count']; ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($row['total_amount']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['paid_amount']); ?></td>
                                    <td class="text-end <?php echo $balance > 0.0001 ? 'text-danger fw-bold' : 'text-success fw-bold'; ?>"><?php echo money_fmt($balance); ?></td>
                                    <td class="text-end">
                                        <div class="btn-group btn-group-sm">
                                            <a class="btn btn-outline-primary" href="front_office_pos.php?front_order_id=<?php echo (int)$row['id']; ?>">Open</a>
                                            <a class="btn btn-outline-secondary" href="receipt_print.php?front_order_id=<?php echo (int)$row['id']; ?>" target="_blank">Receipt</a>
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card mb-4">
            <div class="card-header"><h5 class="mb-0">Payment Breakdown</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0 table-sticky">
                    <tbody>
                    <?php if (!$paymentBreakdown): ?>
                        <tr><td class="text-center text-muted py-4">No payments found.</td></tr>
                    <?php else: ?>
                        <?php foreach ($paymentBreakdown as $pb): ?>
                            <tr>
                                <td><strong><?php echo h($pb['payment_method']); ?></strong></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($pb['amount']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Top Items</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0 table-sticky">
                    <thead><tr><th>Item</th><th class="text-end">Qty</th><th class="text-end">Amount</th></tr></thead>
                    <tbody>
                    <?php if (!$itemBreakdown): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No items found.</td></tr>
                    <?php else: ?>
                        <?php foreach ($itemBreakdown as $ib): ?>
                            <tr>
                                <td><?php echo h($ib['item_name']); ?></td>
                                <td class="text-end"><?php echo money_fmt($ib['qty']); ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($ib['amount']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
<style>
    .hero-compact{
    padding:14px 22px !important;
    margin-bottom:15px !important;
}

.hero-compact .hero-title{
    font-size:1.45rem !important;
    line-height:1.1;
    margin-bottom:2px !important;
}

.hero-compact .hero-subtitle{
    font-size:.85rem !important;
    opacity:.9;
    margin-top:2px !important;
}

.hero-compact .page-eyebrow{
    font-size:.72rem !important;
}
.table-freeze{
    max-height:650px;
    overflow:auto;
}

.table-freeze thead th{
    position:sticky;
    top:0;
    z-index:10;
    background:#ffffff;
    box-shadow:0 1px 0 rgba(0,0,0,.08);
}
.table-sticky thead th{
    position:sticky;
    top:0;
    background:#fff;
    z-index:5;
}
</style>
<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
