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

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

// ================= DEFAULT =================
$from_date  = $_GET['from'] ?? '';
$to_date    = $_GET['to'] ?? '';
$method     = $_GET['method'] ?? '';
$outlet     = $_GET['outlet'] ?? '';
$search     = $_GET['search'] ?? '';
$created_by = $_GET['created_by'] ?? '';
$include_invoices = $_GET['include_invoices'] ?? '1';

// ================= WHERE (SALES) =================
$where_sales = [];

if($from_date && $to_date){
    $where_sales[] = "DATE(sales_date) BETWEEN '$from_date' AND '$to_date'";
} elseif($from_date){
    $where_sales[] = "DATE(sales_date) >= '$from_date'";
} elseif($to_date){
    $where_sales[] = "DATE(sales_date) <= '$to_date'";
}

if($method){
    $where_sales[] = "payment_method='$method'";
}

if($outlet !== ''){
    $where_sales[] = "rest='$outlet'";
}

if($search){
    $where_sales[] = "(customer_id LIKE '%$search%' OR created_by LIKE '%$search%')";
}

if($created_by){
    $where_sales[] = "created_by='$created_by'";
}

$where_sales_sql = $where_sales ? "WHERE ".implode(" AND ", $where_sales) : "";

// ================= WHERE (INVOICES) =================
$where_inv = [];

if($from_date && $to_date){
    $where_inv[] = "DATE(issue_date) BETWEEN '$from_date' AND '$to_date'";
}

if($created_by){
    $where_inv[] = "created_by='$created_by'";
}

$where_inv_sql = $where_inv ? "WHERE ".implode(" AND ", $where_inv) : "";

// ================= SALES =================
$sales = $conn->query("
    SELECT 
        sales_date AS date,
        'SALE' AS type,
        rest AS outlet,
        payment_method,
        customer_id,
        created_by,
        total,
        vat_total,
        discount
    FROM sales
    $where_sales_sql
");

// ================= INVOICES =================
$invoices = [];
if($include_invoices == '1'){
    $invoices = $conn->query("
        SELECT 
            issue_date AS date,
            'INVOICE' AS type,
            'ACCOMMODATION' AS outlet,
            'N/A' AS payment_method,
            guest_id AS customer_id,
            created_by,
            total_amount AS total,
            vat,
            manual_discount AS discount
        FROM invoices
        $where_inv_sql
    ");
}

// ================= MERGE DATA =================
$data = [];

// SALES
while($row = $sales->fetch_assoc()){
    $data[] = $row;
}

// INVOICES
if($include_invoices == '1'){
    while($row = $invoices->fetch_assoc()){
        $data[] = $row;
    }
}

// SORT BY DATE DESC
usort($data, function($a, $b){
    return strtotime($b['date']) - strtotime($a['date']);
});

// ================= SUMMARY =================
$total_amount = 0;
$total_count = count($data);

foreach($data as $d){
    $total_amount += $d['total'];
}
?>

<style>
.report-card{border-radius:12px;}
.no-print{display:none;}
</style>

<div class="container-fluid mt-3">
    <button class="btn btn-secondary no-print" onclick="history.back()">
    <i class="fa fa-arrow-left"></i> Back
</button>

<h3>📊 Combined Sales & Accommodation Report</h3>

<!-- ================= FILTERS ================= -->
<form method="GET" class="row g-2 mb-3">

<div class="col-md-2">
    <input type="date" name="from" class="form-control" value="<?= $from_date ?>">
</div>

<div class="col-md-2">
    <input type="date" name="to" class="form-control" value="<?= $to_date ?>">
</div>

<div class="col-md-2">
    <select name="created_by" class="form-control">
        <option value="">All Users</option>
        <?php
        $users = $conn->query("SELECT DISTINCT created_by FROM sales UNION SELECT DISTINCT created_by FROM invoices");
        while($u = $users->fetch_assoc()):
        ?>
            <option value="<?= $u['created_by'] ?>" <?= $created_by==$u['created_by']?'selected':'' ?>>
                <?= $u['created_by'] ?>
            </option>
        <?php endwhile; ?>
    </select>
</div>

<div class="col-md-2">
    <select name="include_invoices" class="form-control">
        <option value="1" <?= $include_invoices=='1'?'selected':'' ?>>Include Accommodation</option>
        <option value="0" <?= $include_invoices=='0'?'selected':'' ?>>Sales Only</option>
    </select>
</div>

<div class="col-md-2">
    <button class="btn btn-primary w-100">Filter</button>
</div>

<div class="col-md-2">
    <a href="sales_report.php" class="btn btn-secondary w-100">Reset</a>
</div>

</form>

<!-- ================= SUMMARY ================= -->
<div class="alert alert-info">
    Total Records: <b><?= $total_count ?></b> |
    Total Value: <b><?= number_format($total_amount,2) ?></b>
</div>

<!-- ================= TABLE ================= -->
<div class="table-responsive">
<table class="table table-bordered table-striped">

<thead class="table-dark">
<tr>
    <th>Date</th>
    <th>Type</th>
    <th>Outlet</th>
    <th>Payment</th>
    <th>Customer</th>
    <th>Created By</th>
    <th>Total</th>
    <th>VAT</th>
    <th>Discount</th>
</tr>
</thead>

<tbody>

<?php if(count($data) > 0): ?>
<?php foreach($data as $r): ?>
<tr>
    <td><?= $r['date'] ?></td>
    <td>
        <span class="badge bg-<?= $r['type']=='SALE'?'success':'dark' ?>">
            <?= $r['type'] ?>
        </span>
    </td>
 <td>
    <?php
        if($r['outlet'] == 0){
            echo '<span class="badge bg-success">Bar</span>';
        } elseif($r['outlet'] == 2){
            echo '<span class="badge bg-dark">Restaurant</span>';
        } else {
            echo '<span class="badge bg-secondary">Accomodation</span>';
        }
    ?>
</td>
    <td><?= $r['payment_method'] ?></td>
    <td><?= $r['customer_id'] ?></td>
    <td><?= $r['created_by'] ?></td>
    <td><?= number_format($r['total'],2) ?></td>
    <td><?= number_format($r['vat_total'] ?? $r['vat'] ?? 0,2) ?></td>
    <td><?= number_format($r['discount'],2) ?></td>
</tr>
<?php endforeach; ?>
<?php else: ?>
<tr>
    <td colspan="9" class="text-center text-danger">No records found</td>
</tr>
<?php endif; ?>

</tbody>

</table>
</div>

</div>

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