<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

$username = $_SESSION['username'] ?? 'Guest';

// ================= DATE RANGE =================
$from_date = $_GET['from'] ?? date('Y-m-01');
$to_date   = $_GET['to'] ?? date('Y-m-d');

// Escape dates for security
$from = $conn->real_escape_string($from_date);
$to   = $conn->real_escape_string($to_date);

// ================= FETCH SALES =================
$sales = $conn->query("
    SELECT s.*, g.name AS guest_name, u.username AS created_by
    FROM sales s
    LEFT JOIN guests g ON s.customer_id=g.id
    LEFT JOIN users u ON s.created_by=u.id
    WHERE s.sales_date BETWEEN '$from' AND '$to'
      AND rest=0
");

// ================= RECONCILIATION TABLE =================
// Make sure the table exists
$conn->query("
    CREATE TABLE IF NOT EXISTS sales_reconciliation (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sale_id INT NOT NULL,
        guest_name VARCHAR(255),
        total_amount DECIMAL(10,2),
        paid_amount DECIMAL(10,2),
        balance DECIMAL(10,2),
        stock_discrepancy INT,
        payment_discrepancy INT,
        created_by VARCHAR(100),
        reconciled_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
");

// ================= PROCESS RECONCILIATION =================
$total_sales = 0;
$total_amount = 0;
$total_paid = 0;
$total_balance = 0;
$total_stock_discrepancy = 0;
$total_payment_discrepancy = 0;

while($s = $sales->fetch_assoc()){
    // Fetch sale items
    $items = $conn->query("SELECT * FROM sale_items WHERE sale_id={$s['id']}");
    $sale_total = 0;
    $stock_discrepancy = 0;

    while($item = $items->fetch_assoc()){
        $sale_total += $item['price'] * $item['qty'];

        // Check stock
        if(!empty($item['product_id'])){
            $available = $conn->query("SELECT quantity FROM products WHERE id={$item['product_id']}")->fetch_assoc()['quantity'] ?? 0;
            if($available < $item['qty']) $stock_discrepancy++;
        }
    }

    // Payments
    $paid = floatval($conn->query("
        SELECT COALESCE(SUM(amount),0) AS total_paid 
        FROM payments 
        WHERE id={$s['id']} AND status='Paid'
    ")->fetch_assoc()['total_paid']);

    $balance = $sale_total - $paid;
    $payment_discrepancy = $balance != 0 ? 1 : 0;

    // Insert into reconciliation table
    $stmt = $conn->prepare("
        INSERT INTO sales_reconciliation 
        (sale_id, guest_name, total_amount, paid_amount, balance, stock_discrepancy, payment_discrepancy, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->bind_param(
        "isdddiis",
        $s['id'],
        $s['guest_name'],
        $sale_total,
        $paid,
        $balance,
        $stock_discrepancy,
        $payment_discrepancy,
        $s['created_by']
    );
    $stmt->execute();

    // Update totals
    $total_sales++;
    $total_amount += $sale_total;
    $total_paid += $paid;
    $total_balance += $balance;
    $total_stock_discrepancy += $stock_discrepancy;
    $total_payment_discrepancy += $payment_discrepancy;
}

?>

<div class="container mt-4">
    <h3 class="text-center text-success">✅ Sales Reconciliation Completed</h3>
    <hr>

    <div class="alert alert-info">
        <strong>Date Range:</strong> <?= $from_date ?> to <?= $to_date ?><br>
        <strong>Total Sales Reconciled:</strong> <?= $total_sales ?><br>
        <strong>Total Amount:</strong> <?= number_format($total_amount,2) ?> MK<br>
        <strong>Total Paid:</strong> <?= number_format($total_paid,2) ?> MK<br>
        <strong>Total Balance:</strong> <?= number_format($total_balance,2) ?> MK<br>
        <strong>Stock Discrepancies:</strong> <?= $total_stock_discrepancy ?><br>
        <strong>Payment Discrepancies:</strong> <?= $total_payment_discrepancy ?>
    </div>

    <a href="bar_analytics.php?from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-primary">Back to Analytics</a>
</div>

<?php include 'includes/footer.php'; ?>