<?php
session_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 1) {
    header("Location: ../../index.php");
    exit();
}
include('../../includes/config.php');
include('../../includes/header.php');
include('../../includes/footer.php');
$store_id = $_GET['store_id'] ?? '';
$cashier_id = $_GET['cashier_id'] ?? '';
$payment = $_GET['payment_method'] ?? '';
$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$export = $_GET['export'] ?? '';

$where = ['1'];
$params = [];
$types = '';

if ($store_id !== '') { $where[] = "st.store_id=?"; $params[] = $store_id; $types .= 'i'; }
if ($cashier_id !== '') { $where[] = "st.sold_by=?"; $params[] = $cashier_id; $types .= 'i'; }
if ($payment !== '') { $where[] = "st.payment_method=?"; $params[] = $payment; $types .= 's'; }
if ($date_from) { $where[] = "DATE(st.created_at) >= ?"; $params[] = $date_from; $types .= 's'; }
if ($date_to) { $where[] = "DATE(st.created_at) <= ?"; $params[] = $date_to; $types .= 's'; }

// Fetch sales transactions
$sql = "SELECT st.*, u.first_name, u.last_name, s.store_name 
        FROM sales_transactions st 
        LEFT JOIN users u ON st.sold_by = u.id
        LEFT JOIN stores s ON st.store_id = s.id
        WHERE " . implode(' AND ', $where) . " 
        ORDER BY st.created_at DESC";

$stmt = $conn->prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$transactions = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Prepare summary totals
$total_sales = $total_discount = $total_vat = 0;
foreach ($transactions as $t) {
    $total_sales += $t['total_amount'];
    $total_discount += $t['discount_total'];
    $total_vat += $t['vat_total'];
}

// --- Grouped summaries for daily and monthly ---
$daily_summary = [];
$monthly_summary = [];

foreach ($transactions as $t) {
    $date = date('Y-m-d', strtotime($t['created_at']));
    $month = date('Y-m', strtotime($t['created_at']));
    
    if (!isset($daily_summary[$date])) {
        $daily_summary[$date] = ['total_sales' => 0, 'total_discount' => 0, 'total_vat' => 0, 'count' => 0];
    }
    if (!isset($monthly_summary[$month])) {
        $monthly_summary[$month] = ['total_sales' => 0, 'total_discount' => 0, 'total_vat' => 0, 'count' => 0];
    }
    
    $daily_summary[$date]['total_sales'] += $t['total_amount'];
    $daily_summary[$date]['total_discount'] += $t['discount_total'];
    $daily_summary[$date]['total_vat'] += $t['vat_total'];
    $daily_summary[$date]['count']++;
    
    $monthly_summary[$month]['total_sales'] += $t['total_amount'];
    $monthly_summary[$month]['total_discount'] += $t['discount_total'];
    $monthly_summary[$month]['total_vat'] += $t['vat_total'];
    $monthly_summary[$month]['count']++;
}

// Fetch stores and cashiers for filters
$stores = $conn->query("SELECT id, store_name FROM stores");
$cashiers = $conn->query("SELECT id, first_name, last_name FROM users WHERE role_id = 3"); // role 3 = cashier

// --- Excel Export Handler ---
if ($export === 'excel') {
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=sales_report_" . date('YmdHis') . ".xls");
    echo "<table border='1'>";
    echo "<tr>
        <th>#</th><th>Store</th><th>Receipt</th><th>Customer</th><th>Total</th><th>Discount</th><th>VAT</th><th>Payment</th><th>Cashier</th><th>Date</th>
    </tr>";
    foreach ($transactions as $i => $row) {
        echo "<tr>";
        echo "<td>" . ($i + 1) . "</td>";
        echo "<td>" . htmlspecialchars($row['store_name']) . "</td>";
        echo "<td>" . $row['receipt_number'] . "</td>";
        echo "<td>" . htmlspecialchars($row['customer_name']) . "</td>";
        echo "<td>" . number_format($row['total_amount'], 2) . "</td>";
        echo "<td>" . number_format($row['discount_total'], 2) . "</td>";
        echo "<td>" . number_format($row['vat_total'], 2) . "</td>";
        echo "<td>" . $row['payment_method'] . "</td>";
        echo "<td>" . htmlspecialchars($row['first_name'] . ' ' . $row['last_name']) . "</td>";
        echo "<td>" . date('d M Y H:i', strtotime($row['created_at'])) . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    exit();
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Sales Report</title>
    <style>
        body {
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            color: white;
            font-family: 'Segoe UI', sans-serif;
        }
        .overlay { position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.6); z-index: -1; }
        .content {
            margin: 100px auto 40px auto;
            max-width: 1300px;
            padding: 30px;
            background: rgba(255,255,255,0.08);
            backdrop-filter: blur(15px);
            border-radius: 20px;
        }
        h2 { text-align: center; margin-bottom: 20px; }
        .filters, .summary { display: flex; gap: 15px; flex-wrap: wrap; justify-content: space-between; margin-bottom: 20px; }
        .filters select, .filters input[type="date"], .filters button {
            padding: 8px 12px; border-radius: 8px; background: rgba(255,255,255,0.2); border: none; color: white;
        }
        .filters button { background: #ff6f00; cursor: pointer; }
        .summary-box {
            flex: 1; background: rgba(255,255,255,0.08); padding: 15px;
            border-radius: 12px; text-align: center;
        }
        .summary-box strong { color: #ffa500; font-size: 18px; display: block; }
        table {
            width: 100%; border-collapse: collapse;
            background: rgba(0,0,0,0.3); border-radius: 12px;
        }
        th, td {
            padding: 12px 14px; border-bottom: 1px solid rgba(255,255,255,0.1);
        }
        th { background: rgba(255,111,0,0.9); }
        tr.clickable:hover { background: rgba(255, 111, 0, 0.5); cursor: pointer; }
        .export-btn {
            background: #ff6f00; padding: 8px 14px; border-radius: 8px;
            color: white; border: none; margin-left: 10px;
            cursor: pointer;
        }
        img.graph { max-width: 400px; margin: 20px auto; display: block; border-radius: 10px; }

        /* Modal styles */
        .modal {
            display: none;
            position: fixed; z-index: 1000;
            left: 0; top: 0; width: 100%; height: 100%;
            overflow: auto;
            background-color: rgba(0,0,0,0.6);
        }
        .modal-content {
            background: rgba(255,255,255,0.1);
            backdrop-filter: blur(15px);
            margin: 50px auto;
            padding: 20px;
            border-radius: 15px;
            width: 80%;
            max-width: 900px;
            color: white;
            max-height: 80vh;
            overflow-y: auto;
        }
        .modal-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 15px;
        }
        .modal-header h3 {
            margin: 0;
        }
        .close-btn {
            background: #ff6f00;
            border: none;
            color: white;
            font-weight: bold;
            font-size: 20px;
            border-radius: 8px;
            width: 32px; height: 32px;
            cursor: pointer;
        }
        .detail-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 10px;
            background: rgba(0,0,0,0.5);
        }
        .detail-table th, .detail-table td {
            border: 1px solid rgba(255,255,255,0.3);
            padding: 8px 10px;
        }
        .detail-table th {
            background: rgba(255,111,0,0.8);
        }
         .back-link {
    display: inline-block;
    margin-bottom: 20px;
    padding: 10px 20px;
    background:rgba(255, 111, 0, 0);
    color: white;
    font-weight: 600;
    border-radius: 8px;
    text-decoration: none;
    transition: background-color 0.3s ease;
}
.back-link:hover {
    background: #e65c00;
}

    </style>
</head>
<body>
<div class="overlay"></div>
<div class="content">
            <a href="reports.php" class="back-link">&larr; Back to Reports</a>

    <h2>Sales Report</h2>

    <form class="filters" method="GET" id="filterForm">
        <select name="store_id">
            <option value="">All Stores</option>
            <?php 
            $stores->data_seek(0); // reset pointer
            while ($s = $stores->fetch_assoc()): ?>
                <option value="<?= $s['id'] ?>" <?= ($store_id == $s['id']) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($s['store_name']) ?>
                </option>
            <?php endwhile; ?>
        </select>

        <select name="cashier_id">
            <option value="">All Cashiers</option>
            <?php 
            $cashiers->data_seek(0);
            while ($c = $cashiers->fetch_assoc()): ?>
                <option value="<?= $c['id'] ?>" <?= ($cashier_id == $c['id']) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($c['first_name'] . ' ' . $c['last_name']) ?>
                </option>
            <?php endwhile; ?>
        </select>

        <select name="payment_method">
            <option value="">All Payment Types</option>
            <option value="Cash" <?= $payment == 'Cash' ? 'selected' : '' ?>>Cash</option>
            <option value="Card" <?= $payment == 'Card' ? 'selected' : '' ?>>Card</option>
            <option value="Mobile" <?= $payment == 'Mobile' ? 'selected' : '' ?>>Mobile</option>
        </select>

        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>">
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>">

        <button type="submit">Filter</button>

        <button type="button" class="export-btn" id="exportExcelBtn">Export Excel</button>
        <button type="button" class="export-btn" onclick="window.print()">Export PDF</button>
    </form>

    <div class="summary">
        <div class="summary-box"><strong><?= count($transactions) ?></strong> Total Transactions</div>
        <div class="summary-box"><strong>MWK <?= number_format($total_sales, 2) ?></strong> Total Sales</div>
        <div class="summary-box"><strong>MWK <?= number_format($total_discount, 2) ?></strong> Total Discounts</div>
        <div class="summary-box"><strong>MWK <?= number_format($total_vat, 2) ?></strong> Total VAT</div>
    </div>

    <h3>Daily Summaries</h3>
    <table>
        <thead>
            <tr>
                <th>Date</th>
                <th>Transactions</th>
                <th>Total Sales</th>
                <th>Total Discounts</th>
                <th>Total VAT</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($daily_summary): 
                ksort($daily_summary);
                foreach ($daily_summary as $date => $vals): ?>
            <tr>
                <td><?= date('d M Y', strtotime($date)) ?></td>
                <td><?= $vals['count'] ?></td>
                <td>MWK <?= number_format($vals['total_sales'], 2) ?></td>
                <td>MWK <?= number_format($vals['total_discount'], 2) ?></td>
                <td>MWK <?= number_format($vals['total_vat'], 2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="5" style="text-align:center;">No daily summaries available.</td></tr>
            <?php endif; ?>
        </tbody>
    </table>

    <h3>Monthly Summaries</h3>
    <table>
        <thead>
            <tr>
                <th>Month</th>
                <th>Transactions</th>
                <th>Total Sales</th>
                <th>Total Discounts</th>
                <th>Total VAT</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($monthly_summary): 
                ksort($monthly_summary);
                foreach ($monthly_summary as $month => $vals): ?>
            <tr>
                <td><?= date('M Y', strtotime($month . '-01')) ?></td>
                <td><?= $vals['count'] ?></td>
                <td>MWK <?= number_format($vals['total_sales'], 2) ?></td>
                <td>MWK <?= number_format($vals['total_discount'], 2) ?></td>
                <td>MWK <?= number_format($vals['total_vat'], 2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="5" style="text-align:center;">No monthly summaries available.</td></tr>
            <?php endif; ?>
        </tbody>
    </table>

    <?php
    // Graph image (optional)
    ob_start();
    include('generate_sales_graph.php');
    $graph_img = ob_get_clean();
    ?>
    <img class="graph" src="data:image/png;base64,<?= $graph_img ?>" alt="Sales Summary Graph">

    <h3>Sales Transactions</h3>
    <table id="salesTable">
        <thead>
            <tr>
                <th>#</th>
                <th>Store</th>
                <th>Receipt</th>
                <th>Customer</th>
                <th>Total</th>
                <th>Discount</th>
                <th>VAT</th>
                <th>Payment</th>
                <th>Cashier</th>
                <th>Date</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($transactions): foreach ($transactions as $i => $row): ?>
            <tr class="clickable" data-transaction-id="<?= $row['id'] ?>">
                <td><?= $i+1 ?></td>
                <td><?= htmlspecialchars($row['store_name']) ?></td>
                <td><?= $row['receipt_number'] ?></td>
                <td><?= htmlspecialchars($row['customer_name']) ?></td>
                <td>MWK <?= number_format($row['total_amount'],2) ?></td>
                <td>MWK <?= number_format($row['discount_total'],2) ?></td>
                <td>MWK <?= number_format($row['vat_total'],2) ?></td>
                <td><?= $row['payment_method'] ?></td>
                <td><?= htmlspecialchars($row['first_name'] . ' ' . $row['last_name']) ?></td>
                <td><?= date('d M Y H:i', strtotime($row['created_at'])) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="10" style="text-align:center;">No sales records found.</td></tr>
            <?php endif; ?>
        </tbody>
    </table>
</div>

<!-- Modal for drill down sale items -->
<div id="saleItemsModal" class="modal">
    <div class="modal-content">
        <div class="modal-header">
            <h3>Sale Items - Receipt: <span id="modalReceiptNumber"></span></h3>
            <button class="close-btn" id="closeModalBtn">&times;</button>
        </div>
        <table class="detail-table" id="saleItemsTable">
            <thead>
                <tr>
                    <th>#</th>
                    <th>Product</th>
                    <th>Quantity</th>
                    <th>Unit Price</th>
                    <th>Discount</th>
                    <th>VAT</th>
                    <th>Total</th>
                </tr>
            </thead>
            <tbody>
                <tr><td colspan="7" style="text-align:center;">Loading...</td></tr>
            </tbody>
        </table>
    </div>
</div>

<script>
// Export Excel button triggers page reload with export param
document.getElementById('exportExcelBtn').addEventListener('click', () => {
    const form = document.getElementById('filterForm');
    const urlParams = new URLSearchParams(new FormData(form));
    urlParams.set('export', 'excel');
    window.location.search = urlParams.toString();
});

// Drill-down modal logic
const modal = document.getElementById('saleItemsModal');
const closeModalBtn = document.getElementById('closeModalBtn');
const modalReceiptNumber = document.getElementById('modalReceiptNumber');
const saleItemsTableBody = document.querySelector('#saleItemsTable tbody');

document.querySelectorAll('#salesTable tr.clickable').forEach(row => {
    row.addEventListener('click', () => {
        const transactionId = row.getAttribute('data-transaction-id');
        modalReceiptNumber.textContent = row.children[2].textContent;
        saleItemsTableBody.innerHTML = '<tr><td colspan="7" style="text-align:center;">Loading...</td></tr>';
        modal.style.display = 'block';

        fetch(`fetch_sale_items.php?transaction_id=${transactionId}`)
        .then(res => res.json())
        .then(data => {
            if (data.success) {
                if (data.items.length === 0) {
                    saleItemsTableBody.innerHTML = '<tr><td colspan="7" style="text-align:center;">No items found for this sale.</td></tr>';
                    return;
                }
                let html = '';
                data.items.forEach((item, i) => {
                    html += `<tr>
                        <td>${i+1}</td>
                        <td>${item.product_name}</td>
                        <td>${item.quantity}</td>
                        <td>MWK ${parseFloat(item.unit_price).toFixed(2)}</td>
                        <td>MWK ${parseFloat(item.discount).toFixed(2)}</td>
                        <td>MWK ${parseFloat(item.vat).toFixed(2)}</td>
                        <td>MWK ${parseFloat(item.total).toFixed(2)}</td>
                    </tr>`;
                });
                saleItemsTableBody.innerHTML = html;
            } else {
                saleItemsTableBody.innerHTML = `<tr><td colspan="7" style="text-align:center;">Error loading items: ${data.message}</td></tr>`;
            }
        })
        .catch(() => {
            saleItemsTableBody.innerHTML = '<tr><td colspan="7" style="text-align:center;">Failed to load sale items.</td></tr>';
        });
    });
});

closeModalBtn.addEventListener('click', () => {
    modal.style.display = 'none';
});

window.onclick = function(event) {
    if (event.target === modal) {
        modal.style.display = 'none';
    }
};
</script>

</body>

</html>
