<?php
session_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 4) {
    header("Location: ../../index.php");
    exit();
}

include('../../includes/config.php');
include('../../includes/header.php');

// Filters
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_filter = isset($_GET['store_id']) ? intval($_GET['store_id']) : 0;
$cashier_filter = isset($_GET['cashier_id']) ? intval($_GET['cashier_id']) : 0;

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) $date_from = date('Y-m-01');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) $date_to = date('Y-m-d');

$stores = $conn->query("SELECT id, store_name FROM stores WHERE deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00' ORDER BY store_name")->fetch_all(MYSQLI_ASSOC);
$cashiers = $conn->query("SELECT id, CONCAT(first_name, ' ', last_name) AS name FROM users WHERE role_id = 3")->fetch_all(MYSQLI_ASSOC);

$where = "WHERE DATE(st.created_at) BETWEEN ? AND ?";
$params = [$date_from, $date_to];
$types = "ss";

if ($store_filter > 0) {
    $where .= " AND st.store_id = ?";
    $params[] = $store_filter;
    $types .= "i";
}
if ($cashier_filter > 0) {
    $where .= " AND st.sold_by = ?";
    $params[] = $cashier_filter;
    $types .= "i";
}

$stmt = $conn->prepare("
    SELECT st.*, s.store_name, u.first_name, u.last_name
    FROM sales_transactions st
    JOIN stores s ON st.store_id = s.id
    JOIN users u ON st.sold_by = u.id
    $where
    ORDER BY st.created_at DESC
");
$stmt->bind_param($types, ...$params);
$stmt->execute();
$sales = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

$total_vat_collected = 0;
$store_vat_summary = [];
$cashier_vat_summary = [];

foreach ($sales as $sale) {
    $total_vat_collected += $sale['vat_total'];

    $store = $sale['store_name'];
    if (!isset($store_vat_summary[$store])) {
        $store_vat_summary[$store] = ['vat' => 0, 'txns' => 0];
    }
    $store_vat_summary[$store]['vat'] += $sale['vat_total'];
    $store_vat_summary[$store]['txns']++;

    $cashier = $sale['first_name'] . ' ' . $sale['last_name'];
    if (!isset($cashier_vat_summary[$cashier])) {
        $cashier_vat_summary[$cashier] = ['vat' => 0, 'txns' => 0];
    }
    $cashier_vat_summary[$cashier]['vat'] += $sale['vat_total'];
    $cashier_vat_summary[$cashier]['txns']++;
}
$page_title = "VAT Collection Report";
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title><?= htmlspecialchars($page_title) ?></title>
    <link rel="icon" type="image/png" href="../../images/logo.png" />
    <style>
        body {
            margin: 0;
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            font-family: 'Segoe UI', sans-serif;
            color: #eee;
        }

        .content {
            margin: 2rem auto 0 auto;
            padding: 45px;
            max-width: 1850px;
        }

        .page-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 1rem;
        }

        h1.page-title {
            color: #ffa500;
            margin: 0;
            display: flex;
            align-items: center;
            gap: 10px;
            font-weight: 700;
            font-size: 2rem;
        }

        h1.page-title img {
            height: 40px;
            width: auto;
        }

        a.back-link {
            color: #ffa500;
            text-decoration: none;
            font-weight: 600;
            border: 1.5px solid #ffa500;
            padding: 6px 14px;
            border-radius: 8px;
            transition: background-color 0.3s ease, color 0.3s ease;
            white-space: nowrap;
        }

        a.back-link:hover {
            background-color: #ffa500;
            color: #222;
        }

        .filter-form {
            margin: 20px 0;
            display: flex;
            gap: 15px;
            flex-wrap: wrap;
        }

        .filter-form input, .filter-form select, .filter-form button, .filter-form a {
            padding: 10px 14px;
            border-radius: 8px;
            border: none;
            font-weight: 500;
            min-width: 160px;
        }

        .filter-form button {
            background: #ffa500;
            color: #222;
            cursor: pointer;
        }

        .filter-form a {
            background: rgba(255,255,255,0.15);
            color: #ffa500;
            text-decoration: none;
            font-weight: 500;
            display: inline-block;
            line-height: 1.5;
        }

        .filter-form a:hover {
            background: #ffa500;
            color: #222;
        }

        .card {
            background: rgba(255,255,255,0.1);
            border-radius: 15px;
            padding: 25px;
            margin-top: 20px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 15px;
            cursor: default;
        }

        table th, table td {
            padding: 12px 20px;
            border-bottom: 1px solid rgba(255,255,255,0.2);
            vertical-align: middle;
            text-align: left;
        }

        table th {
            background: rgba(255,165,0,0.3);
            color: #fff;
            font-weight: 700;
        }

        table.summary-table tbody tr:hover {
            background: rgba(255,165,0,0.25);
            cursor: pointer;
        }

        .total-row {
            font-weight: bold;
            background: rgba(255,255,255,0.05);
        }

        h3 {
            color: #ffa500;
            margin-top: 40px;
        }

        #drillClearBtn {
            margin-top: 15px;
            background: transparent;
            border: 1.5px solid #ffa500;
            color: #ffa500;
            padding: 6px 14px;
            border-radius: 8px;
            cursor: pointer;
            font-weight: 600;
            transition: background-color 0.3s ease, color 0.3s ease;
        }
        #drillClearBtn:hover {
            background-color: #ffa500;
            color: #222;
        }

        @media print {
            .filter-form, .back-link, #drillClearBtn {
                display: none;
            }
            body {
                background: #fff;
                color: #000;
            }
            table th {
                background: #eee;
                color: #000;
            }
        }
    </style>
</head>
<body>

<div class="content">

    <div class="page-header">
        <h1 class="page-title">
            <img src="../../images/logo.png" alt="Logo" />
            <?= htmlspecialchars($page_title) ?>
        </h1>
        <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
    </div>

    <form method="GET" class="filter-form" novalidate>
        <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" required />
        <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" required />
        <select name="store_id">
            <option value="0">All Stores</option>
            <?php foreach ($stores as $s): ?>
                <option value="<?= $s['id'] ?>" <?= $store_filter == $s['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($s['store_name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <select name="cashier_id">
            <option value="0">All Cashiers</option>
            <?php foreach ($cashiers as $c): ?>
                <option value="<?= $c['id'] ?>" <?= $cashier_filter == $c['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($c['name']) ?>
                </option>
            <?php endforeach; ?>
        </select>
        <button type="submit">Apply Filters</button>
        <button type="button" onclick="window.print()">🖨 Print</button>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'excel'])) ?>">📊 Export Excel</a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'pdf'])) ?>">📄 Export PDF</a>
    </form>

    <div class="card">
        <table>
            <thead>
                <tr>
                    <th colspan="2">Summary</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Total VAT Collected (MWK)</td>
                    <td><?= number_format($total_vat_collected, 2) ?></td>
                </tr>
            </tbody>
        </table>
    </div>

    <h3>VAT Summary by Store</h3>
    <div class="card">
        <table class="summary-table" id="storeSummaryTable">
            <thead>
                <tr>
                    <th>Store Name</th>
                    <th>VAT Collected (MWK)</th>
                    <th>Total Transactions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($store_vat_summary as $name => $s): ?>
                    <tr data-type="store" data-name="<?= htmlspecialchars($name, ENT_QUOTES) ?>">
                        <td><?= htmlspecialchars($name) ?></td>
                        <td><?= number_format($s['vat'], 2) ?></td>
                        <td><?= $s['txns'] ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <h3>VAT Summary by Cashier</h3>
    <div class="card">
        <table class="summary-table" id="cashierSummaryTable">
            <thead>
                <tr>
                    <th>Cashier Name</th>
                    <th>VAT Collected (MWK)</th>
                    <th>Total Transactions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($cashier_vat_summary as $name => $s): ?>
                    <tr data-type="cashier" data-name="<?= htmlspecialchars($name, ENT_QUOTES) ?>">
                        <td><?= htmlspecialchars($name) ?></td>
                        <td><?= number_format($s['vat'], 2) ?></td>
                        <td><?= $s['txns'] ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <h3>Detailed VAT Transactions</h3>
    <button id="drillClearBtn" style="display:none;">Show All Transactions</button>
    <div class="card">
        <table id="detailsTable">
            <thead>
                <tr>
                    <th>Transaction Date</th>
                    <th>Receipt Number</th>
                    <th>Store</th>
                    <th>Cashier</th>
                    <th>VAT Collected (MWK)</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($sales as $sale): ?>
                    <tr data-store="<?= htmlspecialchars($sale['store_name'], ENT_QUOTES) ?>" data-cashier="<?= htmlspecialchars($sale['first_name'] . ' ' . $sale['last_name'], ENT_QUOTES) ?>">
                        <td><?= htmlspecialchars(date('Y-m-d', strtotime($sale['created_at']))) ?></td>
                        <td><?= htmlspecialchars($sale['receipt_number']) ?></td>
                        <td><?= htmlspecialchars($sale['store_name']) ?></td>
                        <td><?= htmlspecialchars($sale['first_name'] . ' ' . $sale['last_name']) ?></td>
                        <td><?= number_format($sale['vat_total'], 2) ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

</div>

<script>
    (() => {
        const storeSummaryTable = document.getElementById('storeSummaryTable');
        const cashierSummaryTable = document.getElementById('cashierSummaryTable');
        const detailsTable = document.getElementById('detailsTable');
        const drillClearBtn = document.getElementById('drillClearBtn');

        // Utility to normalize text for case-insensitive comparison
        function normalizeText(text) {
            return text.trim().toLowerCase();
        }

        function filterDetails(type, name) {
            const normalizedName = normalizeText(name);
            const rows = detailsTable.querySelectorAll('tbody tr');
            let shownCount = 0;

            rows.forEach(row => {
                const attrValue = normalizeText(row.getAttribute(type));
                if(attrValue === normalizedName) {
                    row.style.display = '';
                    shownCount++;
                } else {
                    row.style.display = 'none';
                }
            });

            if(shownCount === 0) {
                // Show message row if needed
                if(!detailsTable.querySelector('.no-data-row')) {
                    const tr = document.createElement('tr');
                    tr.classList.add('no-data-row');
                    tr.innerHTML = `<td colspan="5" style="text-align:center; color:#ffa500;">No transactions found for this selection.</td>`;
                    detailsTable.querySelector('tbody').appendChild(tr);
                }
            } else {
                // Remove no data row if exists
                const noDataRow = detailsTable.querySelector('.no-data-row');
                if(noDataRow) noDataRow.remove();
            }

            drillClearBtn.style.display = 'inline-block';
            drillClearBtn.focus();
        }

        function clearFilter() {
            const rows = detailsTable.querySelectorAll('tbody tr');
            rows.forEach(row => row.style.display = '');
            const noDataRow = detailsTable.querySelector('.no-data-row');
            if(noDataRow) noDataRow.remove();
            drillClearBtn.style.display = 'none';
        }

        // Add click listeners to summary tables
        [storeSummaryTable, cashierSummaryTable].forEach(table => {
            table.addEventListener('click', e => {
                let tr = e.target.closest('tr');
                if(!tr || tr.querySelectorAll('th').length > 0) return; // Ignore header row

                const type = tr.getAttribute('data-type');
                const name = tr.getAttribute('data-name');
                if(type && name) {
                    filterDetails(type === 'store' ? 'data-store' : 'data-cashier', name);
                }
            });
        });

        drillClearBtn.addEventListener('click', clearFilter);
    })();
</script>

<?php include('../../includes/footer.php'); ob_end_flush(); ?>
</body>
</html>