<?php
include("layout.php");
include("db.php");

// Get filter dates from URL or set defaults (last 30 days)
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$end_date = $_GET['end_date'] ?? date('Y-m-d');

// Fetch all tax rates from tax_rates table
$tax_rates = [];
$tax_rate_query = mysqli_query($conn, "SELECT rate FROM tax_rates ORDER BY rate ASC");
while ($tr = mysqli_fetch_assoc($tax_rate_query)) {
    $tax_rates[] = $tr['rate'];
}

// Prepare WHERE clause
$where = "";
if ($start_date && $end_date) {
    $where = "WHERE s.sale_date BETWEEN '" . mysqli_real_escape_string($conn, $start_date) . " 00:00:00' 
              AND '" . mysqli_real_escape_string($conn, $end_date) . " 23:59:59'";
}

// Build dynamic SQL for each tax rate (handle included/excluded/nil)
$tax_columns = "";
foreach ($tax_rates as $rate) {
    $alias = "tax_" . str_replace('.', '_', $rate);
    $tax_columns .= ", SUM(
        CASE 
            WHEN i.tax_type = 'included' AND si.tax_rate = $rate THEN (si.total_price - (si.total_price / (1 + ($rate/100))))
            WHEN i.tax_type = 'excluded' AND si.tax_rate = $rate THEN (si.total_price * ($rate/100))
            ELSE 0
        END
    ) AS `$alias`";
}

// Main query: group by date, sum from sale_items only, handle tax_type
$query = "
    SELECT
        DATE(s.sale_date) AS sale_date,
        SUM(
            CASE 
                WHEN i.tax_type = 'included' THEN si.total_price
                WHEN i.tax_type = 'excluded' THEN si.total_price + (si.total_price * (si.tax_rate/100))
                ELSE si.total_price
            END
        ) AS gross_sales,
        SUM(
            CASE 
                WHEN i.tax_type = 'included' THEN si.total_price / (1 + (si.tax_rate/100))
                ELSE si.total_price
            END
        ) AS net_sales,
        SUM(
            CASE 
                WHEN i.tax_type = 'included' THEN (si.total_price - (si.total_price / (1 + (si.tax_rate/100))))
                WHEN i.tax_type = 'excluded' THEN (si.total_price * (si.tax_rate/100))
                ELSE 0
            END
        ) AS total_tax
        $tax_columns,
        COUNT(DISTINCT s.id) AS total_invoices
    FROM
        sale_items si
        JOIN sales s ON si.sale_id = s.id
        JOIN items i ON si.item_id = i.id
    $where
    GROUP BY
        DATE(s.sale_date)
    ORDER BY
        sale_date ASC
";
$result = mysqli_query($conn, $query);
if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

// For totals row
$totals = [
    'gross_sales' => 0,
    'total_tax' => 0,
    'net_sales' => 0,
    'total_invoices' => 0
];
foreach ($tax_rates as $rate) {
    $totals["tax_" . str_replace('.', '_', $rate)] = 0;
}
?>
<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="add-item d-flex">
                <div class="page-title">
                    <h4>Tax Wise Sales Report</h4>
                    <h6>Summary of sales and tax by date (handles included/excluded)</h6>
                </div>
            </div>
        </div>

        <!-- Date Filter Form -->
        <form method="get" class="mb-3">
            <label for="start_date">Start Date:</label>
            <input type="date" name="start_date" id="start_date" value="<?= htmlspecialchars($start_date) ?>" required>
            <label for="end_date" style="margin-left: 10px;">End Date:</label>
            <input type="date" name="end_date" id="end_date" value="<?= htmlspecialchars($end_date) ?>" required>
            <button type="submit" class="btn btn-primary" style="margin-left: 10px;">Filter</button>
            <a href="export_excel.php?report=tax_wise_report&start_date=<?= urlencode($start_date) ?>&end_date=<?= urlencode($end_date) ?>" class="btn btn-success" style="margin-left: 10px;">Export to Excel</a>
        </form>

        <!-- Tax Wise Sales Report Table -->
        <div class="card table-list-card">
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table datanew" id="taxWiseReportTable">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Gross Sales (PKR)</th>
                                <?php foreach ($tax_rates as $rate): ?>
                                    <th><?= htmlspecialchars($rate) ?>% Tax (PKR)</th>
                                <?php endforeach; ?>
                                <th>Total Tax (PKR)</th>
                                <th>Net Sales (PKR)</th>
                                <th>Total Invoices</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($row = mysqli_fetch_assoc($result)) : 
                                $totals['gross_sales'] += $row['gross_sales'];
                                foreach ($tax_rates as $rate) {
                                    $alias = "tax_" . str_replace('.', '_', $rate);
                                    $totals[$alias] += $row[$alias];
                                }
                                $totals['total_tax'] += $row['total_tax'];
                                $totals['net_sales'] += $row['net_sales'];
                                $totals['total_invoices'] += $row['total_invoices'];
                            ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['sale_date']) ?></td>
                                    <td><?= number_format($row['gross_sales'], 2) ?></td>
                                    <?php foreach ($tax_rates as $rate): 
                                        $alias = "tax_" . str_replace('.', '_', $rate);
                                    ?>
                                        <td><?= number_format($row[$alias], 2) ?></td>
                                    <?php endforeach; ?>
                                    <td><?= number_format($row['total_tax'], 2) ?></td>
                                    <td><?= number_format($row['net_sales'], 2) ?></td>
                                    <td><?= $row['total_invoices'] ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                        <tfoot>
                            <tr style="font-weight:bold;">
                                <td>Total</td>
                                <td><?= number_format($totals['gross_sales'], 2) ?></td>
                                <?php foreach ($tax_rates as $rate): 
                                    $alias = "tax_" . str_replace('.', '_', $rate);
                                ?>
                                    <td><?= number_format($totals[$alias], 2) ?></td>
                                <?php endforeach; ?>
                                <td><?= number_format($totals['total_tax'], 2) ?></td>
                                <td><?= number_format($totals['net_sales'], 2) ?></td>
                                <td><?= $totals['total_invoices'] ?></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- DataTables JS -->
<script>
    $(document).ready(function () {
        if (!$.fn.DataTable.isDataTable('#taxWiseReportTable')) {
            $('#taxWiseReportTable').DataTable({
                stateSave: true
            });
        }
    });
</script>