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

$pageTitle = 'Fast Food Tax Schedule';

if (!function_exists('h')) {
    function h($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}
if (!function_exists('fft_money')) {
    function fft_money($value): string {
        return number_format((float)$value, 2, '.', ',');
    }
}
if (!function_exists('fft_qty')) {
    function fft_qty($value): string {
        return rtrim(rtrim(number_format((float)$value, 4, '.', ''), '0'), '.');
    }
}
if (!function_exists('fft_table_exists')) {
    function fft_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=:table_name");
            $stmt->execute([':table_name' => $table]);
            return $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$table] = false;
        }
    }
}
if (!function_exists('fft_column_exists')) {
    function fft_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=:table_name AND column_name=:column_name");
            $stmt->execute([':table_name' => $table, ':column_name' => $column]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$key] = false;
        }
    }
}

/*
 * Requested tax: 0.05% of taxable sales.
 * 0.05% = 0.0005 as a decimal multiplier.
 * To use 5% instead, change this constant to 0.05.
 */
const FAST_FOOD_TAX_RATE = 0.01;
const FAST_FOOD_TAX_LABEL = '1%';

$dateFrom = trim((string)($_GET['date_from'] ?? date('Y-m-01')));
$dateTo   = trim((string)($_GET['date_to'] ?? date('Y-m-d')));
$propertyId = max(0, (int)($_GET['property_id'] ?? 0));
$export = strtolower(trim((string)($_GET['export'] ?? '')));

if (strtotime($dateFrom) === false) $dateFrom = date('Y-m-01');
if (strtotime($dateTo) === false) $dateTo = date('Y-m-d');
$dateFrom = date('Y-m-d', strtotime($dateFrom));
$dateTo = date('Y-m-d', strtotime($dateTo));
if ($dateTo < $dateFrom) {
    [$dateFrom, $dateTo] = [$dateTo, $dateFrom];
}

$errors = [];
$properties = [];
$rows = [];

try {
    if (!fft_table_exists($pdo, 'restaurant_orders') || !fft_table_exists($pdo, 'restaurant_order_items')) {
        throw new RuntimeException('Restaurant order tables were not found.');
    }

    if (fft_table_exists($pdo, 'properties')) {
        $properties = $pdo->query("SELECT id, name FROM properties ORDER BY name ASC")->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    $orderDateColumn = fft_column_exists($pdo, 'restaurant_orders', 'closed_at')
        ? 'ro.closed_at'
        : (fft_column_exists($pdo, 'restaurant_orders', 'opened_at') ? 'ro.opened_at' : 'ro.created_at');

    $qtyExpression = fft_column_exists($pdo, 'restaurant_order_items', 'qty')
        ? "COALESCE(NULLIF(roi.qty,0), NULLIF(roi.quantity,0), 0)"
        : "COALESCE(NULLIF(roi.quantity,0),0)";

    $lineAmountExpression = fft_column_exists($pdo, 'restaurant_order_items', 'line_total')
        ? "COALESCE(roi.line_total, ({$qtyExpression}) * COALESCE(roi.unit_price,0))"
        : "({$qtyExpression}) * COALESCE(roi.unit_price,0)";

    $roleExpression = fft_column_exists($pdo, 'restaurant_order_items', 'printer_role')
        ? "LOWER(TRIM(COALESCE(roi.printer_role,'')))"
        : "''";

    $where = [
        "DATE({$orderDateColumn}) BETWEEN :date_from AND :date_to",
        "LOWER(COALESCE(ro.status,'')) = 'closed'",
        "LOWER(COALESCE(roi.status,'active')) NOT IN ('void','voided','cancelled','canceled','deleted')",
        "{$roleExpression} IN ('kitchen','bar')",
        "COALESCE(roi.is_condiment,0)=0",
    ];
    $params = [
        ':date_from' => $dateFrom,
        ':date_to' => $dateTo,
    ];

    if ($propertyId > 0 && fft_column_exists($pdo, 'restaurant_orders', 'property_id')) {
        $where[] = 'ro.property_id = :property_id';
        $params[':property_id'] = $propertyId;
    }

    /*
     * Use a derived table before aggregation. This keeps the query compatible
     * with MySQL/MariaDB ONLY_FULL_GROUP_BY because the outer query groups only
     * by plain derived columns rather than aliases whose expressions reference
     * restaurant_order_items columns.
     */
    $sql = "
        SELECT
            src.item_section,
            src.menu_item_id,
            src.item_name,
            SUM(src.line_qty) AS total_qty,
            SUM(src.line_amount) AS total_amount
        FROM (
            SELECT
                CASE WHEN {$roleExpression}='bar' THEN 'BAR' ELSE 'KITCHEN' END AS item_section,
                COALESCE(roi.menu_item_id,0) AS menu_item_id,
                TRIM(COALESCE(
                    NULLIF(roi.item_name,''),
                    CONCAT('Menu Item #',COALESCE(roi.menu_item_id,0))
                )) AS item_name,
                {$qtyExpression} AS line_qty,
                {$lineAmountExpression} AS line_amount
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro ON ro.id=roi.restaurant_order_id
            WHERE " . implode(' AND ', $where) . "
        ) src
        GROUP BY src.item_section, src.menu_item_id, src.item_name
        HAVING ABS(SUM(src.line_qty))>0.0001
            OR ABS(SUM(src.line_amount))>0.0049
        ORDER BY FIELD(src.item_section,'KITCHEN','BAR'), src.item_name ASC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

$sections = [
    'KITCHEN' => [],
    'BAR' => [],
];
$summary = [
    'KITCHEN' => ['qty' => 0.0, 'amount' => 0.0, 'zta' => 0.0, 'vat' => 0.0, 'net' => 0.0, 'tax' => 0.0],
    'BAR'     => ['qty' => 0.0, 'amount' => 0.0, 'zta' => 0.0, 'vat' => 0.0, 'net' => 0.0, 'tax' => 0.0],
];

/*
   Split a VAT+ZTA-inclusive gross into ZTA levy and VAT, IDENTICAL to
   fiscal_tax_invoice.php / accounting.php so every screen reconciles:
     ZTA = round( (2 / 117.5) * gross , 2 )   (removed first)
     amt = gross - ZTA
     VAT = round( (15.5 / 115.5) * amt , 2 )
     net = amt - VAT                          (amount less BOTH taxes)
   The Fast Food Tax is then levied on NET (amount excluding VAT and ZTA).
*/
if (!function_exists('fft_tax_split')) {
    function fft_tax_split($grossInclusive): array {
        $gross = (float)$grossInclusive;
        $zta = round((2.0 / 117.5) * $gross, 2);
        $amt = round($gross - $zta, 2);
        $vat = round((15.5 / 115.5) * $amt, 2);
        $net = round($amt - $vat, 2);
        return ['zta' => $zta, 'vat' => $vat, 'net' => $net];
    }
}

foreach ($rows as $row) {
    $section = strtoupper((string)($row['item_section'] ?? 'KITCHEN'));
    if (!isset($sections[$section])) continue;

    $qty = round((float)($row['total_qty'] ?? 0), 4);
    $amount = round((float)($row['total_amount'] ?? 0), 2);

    $split = fft_tax_split($amount);
    $zta = $split['zta'];
    $vat = $split['vat'];
    $net = $split['net'];

    // Fast Food Tax now on NET (amount less VAT and ZTA), not gross.
    $tax = round($net * FAST_FOOD_TAX_RATE, 2);

    $row['total_qty'] = $qty;
    $row['total_amount'] = $amount;
    $row['zta'] = $zta;
    $row['vat'] = $vat;
    $row['net_amount'] = $net;
    $row['fast_food_tax'] = $tax;
    $sections[$section][] = $row;

    $summary[$section]['qty'] += $qty;
    $summary[$section]['amount'] += $amount;
    $summary[$section]['zta'] += $zta;
    $summary[$section]['vat'] += $vat;
    $summary[$section]['net'] += $net;
    $summary[$section]['tax'] += $tax;
}

foreach ($summary as $key => $value) {
    $summary[$key]['qty'] = round($value['qty'], 4);
    $summary[$key]['amount'] = round($value['amount'], 2);
    $summary[$key]['zta'] = round($value['zta'], 2);
    $summary[$key]['vat'] = round($value['vat'], 2);
    /* Section net & tax from the section-level split to avoid row-rounding drift. */
    $secSplit = fft_tax_split($summary[$key]['amount']);
    $summary[$key]['net'] = $secSplit['net'];
    $summary[$key]['tax'] = round($summary[$key]['net'] * FAST_FOOD_TAX_RATE, 2);
}

$grandQty = round($summary['KITCHEN']['qty'] + $summary['BAR']['qty'], 4);
$grandAmount = round($summary['KITCHEN']['amount'] + $summary['BAR']['amount'], 2);
$grandZta = round($summary['KITCHEN']['zta'] + $summary['BAR']['zta'], 2);
$grandVat = round($summary['KITCHEN']['vat'] + $summary['BAR']['vat'], 2);
$grandNet = round($summary['KITCHEN']['net'] + $summary['BAR']['net'], 2);
$grandTax = round($grandNet * FAST_FOOD_TAX_RATE, 2);

$propertyName = 'All Properties';
if ($propertyId > 0) {
    foreach ($properties as $property) {
        if ((int)$property['id'] === $propertyId) {
            $propertyName = (string)$property['name'];
            break;
        }
    }
}

if ($export === 'excel' && !$errors) {
    $filename = 'fast_food_tax_schedule_' . $dateFrom . '_to_' . $dateTo . '.xls';
    header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo "\xEF\xBB\xBF";
    echo '<html><head><meta charset="UTF-8"><style>';
    echo 'body{font-family:Arial,sans-serif;color:#111827}';
    echo 'table{border-collapse:collapse;width:100%;font-size:11px}';
    echo 'th,td{border:1px solid #d1d5db;padding:6px}';
    echo 'th{background:#22586a;color:#fff;text-align:left;font-weight:bold}';
    echo '.title{font-size:20px;font-weight:bold;margin-bottom:3px}';
    echo '.sub{font-style:italic;color:#4b5563;margin-bottom:3px}';
    echo '.section{background:#dbeafe;font-weight:bold;font-size:12px}';
    echo '.right{text-align:right}.total{font-weight:bold;background:#f3f4f6}';
    echo '.grand{font-weight:bold;background:#22586a;color:#fff}';
    echo '</style></head><body>';
    echo '<div class="title">Fast Food Tax Schedule</div>';
    echo '<div class="sub">Period: ' . h($dateFrom) . ' to ' . h($dateTo) . '</div>';
    echo '<div class="sub">Property: ' . h($propertyName) . '</div>';
    echo '<div class="sub">Generated ' . h(date('Y-m-d H:i')) . ' - Tax rate ' . h(FAST_FOOD_TAX_LABEL) . '</div><br>';

    echo '<table><thead><tr>';
    foreach (['Section','Item ID','Item Description','Quantity','Amount','ZTA','VAT','Net (excl VAT & ZTA)','Fast Food Tax (' . FAST_FOOD_TAX_LABEL . ')'] as $heading) {
        echo '<th>' . h($heading) . '</th>';
    }
    echo '</tr></thead><tbody>';

    foreach (['KITCHEN','BAR'] as $section) {
        echo '<tr><td class="section" colspan="9">' . h($section . ' ITEMS') . '</td></tr>';
        if (!$sections[$section]) {
            echo '<tr><td colspan="9">No ' . h(strtolower($section)) . ' items found.</td></tr>';
        }
        foreach ($sections[$section] as $row) {
            echo '<tr>';
            echo '<td>' . h(ucwords(strtolower($section))) . '</td>';
            echo '<td>' . h((int)$row['menu_item_id']) . '</td>';
            echo '<td>' . h($row['item_name']) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['total_qty'],4,'.','')) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['total_amount'],2,'.','')) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['zta'],2,'.','')) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['vat'],2,'.','')) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['net_amount'],2,'.','')) . '</td>';
            echo '<td class="right">' . h(number_format((float)$row['fast_food_tax'],2,'.','')) . '</td>';
            echo '</tr>';
        }
        echo '<tr class="total">';
        echo '<td colspan="3">' . h($section . ' TOTAL') . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['qty'],4,'.','')) . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['amount'],2,'.','')) . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['zta'],2,'.','')) . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['vat'],2,'.','')) . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['net'],2,'.','')) . '</td>';
        echo '<td class="right">' . h(number_format($summary[$section]['tax'],2,'.','')) . '</td>';
        echo '</tr>';
    }

    echo '<tr class="grand">';
    echo '<td colspan="3">GRAND TOTAL</td>';
    echo '<td class="right">' . h(number_format($grandQty,4,'.','')) . '</td>';
    echo '<td class="right">' . h(number_format($grandAmount,2,'.','')) . '</td>';
    echo '<td class="right">' . h(number_format($grandZta,2,'.','')) . '</td>';
    echo '<td class="right">' . h(number_format($grandVat,2,'.','')) . '</td>';
    echo '<td class="right">' . h(number_format($grandNet,2,'.','')) . '</td>';
    echo '<td class="right">' . h(number_format($grandTax,2,'.','')) . '</td>';
    echo '</tr>';
    echo '</tbody></table></body></html>';
    exit;
}

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

<div class="hero-band compact-tax-hero">
    <div class="d-flex justify-content-between align-items-center flex-wrap gap-3">
        <div>
            <div class="page-eyebrow text-white-50"><i class="bi bi-receipt-cutoff"></i> Restaurant Tax Reporting</div>
            <div class="hero-title mt-1">Fast Food Tax Schedule</div>
            <div class="hero-subtitle mt-1">Kitchen and bar sales quantities, values and fast food tax.</div>
        </div>
        <a class="btn btn-light" href="orders.php"><i class="bi bi-arrow-left me-1"></i>Restaurant Orders</a>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <strong>Could not prepare the report.</strong>
        <ul class="mb-0 mt-2"><?php foreach ($errors as $error): ?><li><?php echo h($error); ?></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-3">
                <label class="form-label">From Date</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>" required>
            </div>
            <div class="col-md-3">
                <label class="form-label">To Date</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>" required>
            </div>
            <div class="col-md-3">
                <label class="form-label">Property</label>
                <select name="property_id" class="form-select">
                    <option value="0">All Properties</option>
                    <?php foreach ($properties as $property): ?>
                        <option value="<?php echo (int)$property['id']; ?>" <?php echo $propertyId === (int)$property['id'] ? 'selected' : ''; ?>>
                            <?php echo h($property['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-funnel me-1"></i>Run Report</button>
                <a class="btn btn-success flex-fill" href="?<?php echo h(http_build_query(['date_from'=>$dateFrom,'date_to'=>$dateTo,'property_id'=>$propertyId,'export'=>'excel'])); ?>">
                    <i class="bi bi-file-earmark-excel me-1"></i>Excel
                </a>
            </div>
        </form>
        <div class="form-text mt-2">Tax rate: <?php echo h(FAST_FOOD_TAX_LABEL); ?> of the <strong>net</strong> amount (item amount less VAT 15.5% and ZTA 2%). Kitchen and bar classification uses the current order item printer role.</div>
    </div>
</div>

<div class="row g-3 mb-4">
    <?php foreach ([
        ['Kitchen Sales', $summary['KITCHEN']['amount']],
        ['Kitchen Tax', $summary['KITCHEN']['tax']],
        ['Bar Sales', $summary['BAR']['amount']],
        ['Bar Tax', $summary['BAR']['tax']],
        ['Total Sales', $grandAmount],
        ['Total Tax', $grandTax],
    ] as $metric): ?>
        <div class="col-6 col-lg-2">
            <div class="metric-card h-100">
                <div class="metric-label"><?php echo h($metric[0]); ?></div>
                <div class="metric-value"><?php echo fft_money($metric[1]); ?></div>
            </div>
        </div>
    <?php endforeach; ?>
</div>

<div class="card panel-card">
    <div class="card-header bg-white">
        <h5 class="mb-0">Fast Food Tax Schedule</h5>
        <div class="small text-muted"><?php echo h($dateFrom); ?> to <?php echo h($dateTo); ?> • <?php echo h($propertyName); ?></div>
    </div>
    <div class="table-responsive">
        <table class="table table-modern align-middle mb-0">
            <thead>
            <tr>
                <th>Item ID</th>
                <th>Item Description</th>
                <th class="text-end">Quantity</th>
                <th class="text-end">Amount</th>
                <th class="text-end">ZTA</th>
                <th class="text-end">VAT</th>
                <th class="text-end">Net</th>
                <th class="text-end">Fast Food Tax (<?php echo h(FAST_FOOD_TAX_LABEL); ?>)</th>
            </tr>
            </thead>
            <tbody>
            <?php foreach (['KITCHEN','BAR'] as $section): ?>
                <tr class="section-row"><td colspan="8"><?php echo h($section); ?> ITEMS</td></tr>
                <?php if (!$sections[$section]): ?>
                    <tr><td colspan="8" class="text-center text-muted py-4">No <?php echo h(strtolower($section)); ?> items found.</td></tr>
                <?php else: ?>
                    <?php foreach ($sections[$section] as $row): ?>
                        <tr>
                            <td><?php echo (int)$row['menu_item_id']; ?></td>
                            <td><?php echo h($row['item_name']); ?></td>
                            <td class="text-end"><?php echo h(fft_qty($row['total_qty'])); ?></td>
                            <td class="text-end"><?php echo h(fft_money($row['total_amount'])); ?></td>
                            <td class="text-end text-muted"><?php echo h(fft_money($row['zta'])); ?></td>
                            <td class="text-end text-muted"><?php echo h(fft_money($row['vat'])); ?></td>
                            <td class="text-end"><?php echo h(fft_money($row['net_amount'])); ?></td>
                            <td class="text-end"><?php echo h(fft_money($row['fast_food_tax'])); ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                <tr class="subtotal-row">
                    <td colspan="2"><?php echo h($section); ?> TOTAL</td>
                    <td class="text-end"><?php echo h(fft_qty($summary[$section]['qty'])); ?></td>
                    <td class="text-end"><?php echo h(fft_money($summary[$section]['amount'])); ?></td>
                    <td class="text-end"><?php echo h(fft_money($summary[$section]['zta'])); ?></td>
                    <td class="text-end"><?php echo h(fft_money($summary[$section]['vat'])); ?></td>
                    <td class="text-end"><?php echo h(fft_money($summary[$section]['net'])); ?></td>
                    <td class="text-end"><?php echo h(fft_money($summary[$section]['tax'])); ?></td>
                </tr>
            <?php endforeach; ?>
            <tr class="grand-row">
                <td colspan="2">GRAND TOTAL</td>
                <td class="text-end"><?php echo h(fft_qty($grandQty)); ?></td>
                <td class="text-end"><?php echo h(fft_money($grandAmount)); ?></td>
                <td class="text-end"><?php echo h(fft_money($grandZta)); ?></td>
                <td class="text-end"><?php echo h(fft_money($grandVat)); ?></td>
                <td class="text-end"><?php echo h(fft_money($grandNet)); ?></td>
                <td class="text-end"><?php echo h(fft_money($grandTax)); ?></td>
            </tr>
            </tbody>
        </table>
    </div>
</div>

<style>
.compact-tax-hero{padding:14px 18px!important;margin-bottom:14px!important}
.panel-card{border-radius:16px;border:1px solid #e5e7eb;box-shadow:0 12px 30px rgba(15,23,42,.07)}
.metric-card{padding:16px;border-radius:14px;border:1px solid #e5e7eb;background:#fff;box-shadow:0 8px 20px rgba(15,23,42,.05)}
.metric-label{font-size:.75rem;text-transform:uppercase;letter-spacing:.04em;color:#64748b;font-weight:700}
.metric-value{font-size:1.35rem;font-weight:800;color:#0f172a;margin-top:3px}
.table-modern thead th{background:#22586a;color:#fff;font-size:.75rem;text-transform:uppercase;letter-spacing:.04em;white-space:nowrap}
.table-modern td,.table-modern th{padding:.68rem .8rem;border-color:#e5e7eb}
.section-row td{background:#dbeafe;font-weight:800;color:#1e3a5f;letter-spacing:.04em}
.subtotal-row td{background:#f3f4f6;font-weight:800}
.grand-row td{background:#22586a;color:#fff;font-weight:800;font-size:1rem}
</style>

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