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


$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
$selected_tax_rate = $_GET['tax_rate'] ?? ''; // This is the tax rate ID

$start_sql = mysqli_real_escape_string($conn, $start_date);
$end_sql = mysqli_real_escape_string($conn, $end_date);
$tax_rate_sql = mysqli_real_escape_string($conn, $selected_tax_rate);

// Fetch tax rate options
$tax_options_result = mysqli_query($conn, "SELECT id, name, rate FROM tax_rates ORDER BY rate");
$tax_options = [];
while ($row = mysqli_fetch_assoc($tax_options_result)) {
    $tax_options[] = $row;
}

// Build the condition
$conditions = [];
if ($start_date && $end_date) {
    $conditions[] = "s.sale_date BETWEEN '$start_sql 00:00:00' AND '$end_sql 23:59:59'";
}
if ($selected_tax_rate !== '') {
    $conditions[] = "i.tax_rate_id = '$tax_rate_sql'";
}
$where_clause = count($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

// Main query (filtered by tax rate and date)
$sql = "
    SELECT 
        DATE(s.sale_date) AS sale_day,
        COUNT(DISTINCT s.id) AS invoice_count,
        SUM(si.total_price) AS net_sale,
        si.tax_rate,
        SUM(si.total_price * si.tax_rate / 100) AS tax_value
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN items i ON si.item_id = i.id
    $where_clause
    GROUP BY sale_day, si.tax_rate
    ORDER BY sale_day DESC, si.tax_rate
";
$result = mysqli_query($conn, $sql);
?>

<!-- HTML Starts -->
<div class="page-wrapper">
    <div class="content">
        <div class="page-header d-flex justify-content-between align-items-center">
            <div class="page-title">
                <h4>Day-wise Sales Summary</h4>
                <h6>With Tax Rate Filter</h6>
            </div>
        </div>

        <!-- Filter Form -->
        <form method="get" class="mb-4 d-flex align-items-end gap-3">
            <div>
                <label for="start_date" class="form-label">Start Date</label>
                <input type="date" name="start_date" id="start_date" class="form-control" value="<?= htmlspecialchars($start_date) ?>" required>
            </div>
            <div>
                <label for="end_date" class="form-label">End Date</label>
                <input type="date" name="end_date" id="end_date" class="form-control" value="<?= htmlspecialchars($end_date) ?>" required>
            </div>
            <div>
                <label for="tax_rate" class="form-label">Tax Rate</label>
                <select name="tax_rate" id="tax_rate" class="form-control">
                    <option value="">All</option>
                    <?php foreach ($tax_options as $opt): ?>
                        <option value="<?= $opt['id'] ?>" <?= $selected_tax_rate == $opt['id'] ? 'selected' : '' ?>>
                            <?= $opt['rate'] ?>% (<?= htmlspecialchars($opt['name']) ?>)
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div>
                <button type="submit" class="btn btn-primary">Apply Filter</button>
            </div>
        </form>
<div>
    <a href="export_excel.php?start_date=<?= urlencode($start_date) ?>&end_date=<?= urlencode($end_date) ?>&tax_rate=<?= urlencode($selected_tax_rate) ?>&report=daily_sales" class="btn btn-success">
        Export to Excel
    </a>
</div>

        <!-- Report Table -->
        <div class="card">
            <div class="card-header"><strong>Sales Summary</strong></div>
            <div class="card-body table-responsive">
                <table class="table table-bordered table-striped">
                    <thead class="table-light">
                        <tr>
                            <th>Date</th>
                            <th>No. of Invoices</th>
                            <th>Net Sale</th>
                            <th>Tax Rate</th>
                            <th>Tax Value</th>
                            <th>Gross Sale</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $total_invoices = 0;
                        $total_net = 0;
                        $total_tax = 0;
                        if (mysqli_num_rows($result) > 0):
                            while ($row = mysqli_fetch_assoc($result)):
                                $gross_sale = $row['net_sale'] + $row['tax_value'];
                                $total_invoices += $row['invoice_count'];
                                $total_net += $row['net_sale'];
                                $total_tax += $row['tax_value'];
                        ?>
                        <tr>
                            <td><?= $row['sale_day'] ?></td>
                            <td><?= $row['invoice_count'] ?></td>
                            <td><?= number_format($row['net_sale'], 2) ?></td>
                            <td><?= number_format($row['tax_rate'], 2) ?>%</td>
                            <td><?= number_format($row['tax_value'], 2) ?></td>
                            <td><?= number_format($gross_sale, 2) ?></td>
                        </tr>
                        <?php endwhile; else: ?>
                        <tr><td colspan="6" class="text-center">No data available</td></tr>
                        <?php endif; ?>
                    </tbody>
                    <?php if ($total_net > 0): ?>
                    <tfoot class="table-secondary fw-bold">
                        <tr>
                            <td class="text-end">Total:</td>
                            <td><?= $total_invoices ?></td>
                            <td><?= number_format($total_net, 2) ?></td>
                            <td>-</td>
                            <td><?= number_format($total_tax, 2) ?></td>
                            <td><?= number_format($total_net + $total_tax, 2) ?></td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>
    </div>
</div>
