<?php

include("db.php");
include("layout.php");
include 'auth.php';
require_right('Sales Report');

$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

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

// Query to fetch each transaction
$sql = "
    SELECT 
        s.invoice_number,
        s.sale_date,
        s.customer_name,
        s.tax_amount,
        s.discount_amount,
        s.net_amount,
        s.payment_method,
        u.username AS shift_user
    FROM sales s
    LEFT JOIN shifts sh ON s.shift_id = sh.id
    LEFT JOIN users u ON sh.user_id = u.id
";

if ($start_date && $end_date) {
    $sql .= " WHERE s.sale_date BETWEEN '$start_sql 00:00:00' AND '$end_sql 23:59:59'";
}

$sql .= " ORDER BY s.sale_date DESC";

$result = mysqli_query($conn, $sql);
if (!$result) die("Query failed: " . mysqli_error($conn));

// Totals
$total_tax = 0;
$total_discount = 0;
$total_net = 0;
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header d-flex justify-content-between align-items-center">
            <div class="page-title">
                <h4>Sales Report</h4>
                <h6>Detailed view of each sale transaction</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>
                <button type="submit" class="btn btn-primary">Apply Filter</button>
            </div>
        </form>
<a href="export_excel.php?report=sales_report&start_date=<?= urlencode($start_date) ?>&end_date=<?= urlencode($end_date) ?>" 
   class="btn btn-success">
    Export to Excel
</a>



        <!-- Sales Table -->
        <div class="card">
            <div class="card-header"><strong>Sales Transactions</strong></div>
            <div class="card-body table-responsive">
                <table class="table table-bordered table-striped">
                    <thead class="table-light">
                        <tr>
                            <th>Invoice #</th>
                            <th>Sale Date</th>
                            <th>Customer</th>
                            <th>Tax</th>
                            <th>Discount</th>
                            <th>Net Amount</th>
                            <th>Payment</th>
                            <th>Shift User</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (mysqli_num_rows($result) > 0): ?>
                            <?php while ($row = mysqli_fetch_assoc($result)): 
                                $total_tax += $row['tax_amount'];
                                $total_discount += $row['discount_amount'];
                                $total_net += $row['net_amount'];
                            ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['invoice_number']) ?></td>
                                    <td><?= date('Y-m-d H:i', strtotime($row['sale_date'])) ?></td>
                                    <td><?= htmlspecialchars($row['customer_name']) ?></td>
                                    <td><?= number_format($row['tax_amount'], 2) ?></td>
                                    <td><?= number_format($row['discount_amount'], 2) ?></td>
                                    <td><?= number_format($row['net_amount'], 2) ?></td>
                                    <td><?= ucfirst($row['payment_method']) ?></td>
                                    <td><?= htmlspecialchars($row['shift_user'] ?? 'N/A') ?></td>
                                </tr>
                            <?php endwhile; ?>
                        <?php else: ?>
                            <tr><td colspan="8" class="text-center">No records found.</td></tr>
                        <?php endif; ?>
                    </tbody>
                    <?php if (mysqli_num_rows($result) > 0): ?>
                    <tfoot class="table-secondary fw-bold">
                        <tr>
                            <td colspan="3" class="text-end">Totals:</td>
                            <td><?= number_format($total_tax, 2) ?></td>
                            <td><?= number_format($total_discount, 2) ?></td>
                            <td><?= number_format($total_net, 2) ?></td>
                            <td colspan="2"></td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>
    </div>
</div>
