<!DOCTYPE html>
<html lang="en">
<?php
include("layout.php");
include("db.php");

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

$query = "
    SELECT 
        s.id AS shift_id,
        u.username AS user,
        s.start_time,
        s.end_time,
        s.opening_cash,
        s.closing_cash,
        s.expected_cash,
        s.actual_cash,
        s.discrepancy,
        s.status,
        COALESCE(SUM(sa.net_amount), 0) AS net_sales,
        COALESCE(SUM(sa.net_amount + sa.tax_amount), 0) AS gross_sales
    FROM shifts s
    JOIN users u ON s.user_id = u.id
    LEFT JOIN sales sa ON sa.shift_id = s.id
    WHERE 1 = 1
";

if (!empty($start_date) && !empty($end_date)) {
    $query .= " AND s.start_time BETWEEN '" . mysqli_real_escape_string($conn, $start_date) . " 00:00:00'
                 AND '" . mysqli_real_escape_string($conn, $end_date) . " 23:59:59'";
}

$query .= " GROUP BY s.id ORDER BY s.end_time DESC";

$result = mysqli_query($conn, $query);
if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="add-item d-flex">
                <div class="page-title">
                    <h4>Shift Report</h4>
                    <h6>Overview of all shifts</h6>
                </div>
            </div>
        </div>

        <!-- 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>
        </form>

        <!-- Report Table -->
        <div class="card table-list-card">
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table datanew" id="shiftReportTable">
                        <thead>
                            <tr>
                                <th>Shift ID</th>
                                <th>User</th>
                                <th>Start Time</th>
                                <th>End Time</th>
                                <th>Opening Cash</th>
                                <th>Closing Cash</th>
                                <th>Expected Cash</th>
                                <th>Actual Cash</th>
                                <th>Discrepancy</th>
                                <th>Status</th>
                                <th>Net Sales</th>
                                <th>Gross Sales</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($row = mysqli_fetch_assoc($result)) :
                                $discrepancy = (float)$row['discrepancy'];
                                $formattedDiscrepancy = ($discrepancy > 0 ? '+' : ($discrepancy < 0 ? '-' : '')) . number_format(abs($discrepancy), 2);

                                $status = ucfirst($row['status']); // Open or Closed
                                $statusBadge = $status === 'Closed'
                                    ? '<span class="badge bg-secondary">Closed</span>'
                                    : '<span class="badge bg-primary">Open</span>';
                            ?>
                                <tr>
                                    <td><?= $row['shift_id'] ?></td>
                                    <td><?= htmlspecialchars($row['user']) ?></td>
                                    <td><?= date('Y-m-d H:i:s', strtotime($row['start_time'])) ?></td>
                                    <td><?= date('Y-m-d H:i:s', strtotime($row['end_time'])) ?></td>
                                    <td><?= number_format($row['opening_cash'], 2) ?></td>
                                    <td><?= number_format($row['closing_cash'], 2) ?></td>
                                    <td><?= number_format($row['expected_cash'], 2) ?></td>
                                    <td><?= number_format($row['actual_cash'], 2) ?></td>
                                    <td><?= $formattedDiscrepancy ?></td>
                                    <td><?= $statusBadge ?></td>
                                    <td><?= number_format($row['net_sales'], 2) ?></td>
                                    <td><?= number_format($row['gross_sales'], 2) ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

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