<?php
require_once __DIR__ . '/../config/user_functions.php';
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
require_once __DIR__ . '/../config/users_local.php';
require_once __DIR__ . '/../config/db_remote.php';

if (function_exists('user_has_permission') && !user_has_permission('reports')) {
    http_response_code(403);
    include '../includes/header.php';
    include '../includes/sidebar.php';
    echo "<div class='container-fluid py-4'><h3 class='text-danger'>Access denied</h3></div>";
    include '../includes/footer.php';
    exit;
}

include '../includes/header.php';
include '../includes/sidebar.php';

$from   = $_GET['from']   ?? date('Y-m-01');
$to     = $_GET['to']     ?? date('Y-m-d');
$grower = $_GET['grower'] ?? '';

$from_dt = $from . ' 00:00:00';
$to_dt   = $to   . ' 23:59:59';

$sql = "
    SELECT
        grower,
        SKU,
        COUNT(*) AS produced_cases
    FROM casecodes
    WHERE DateTime BETWEEN ? AND ?
";

if ($grower !== '') {
    $sql .= " AND grower = ? ";
}

$sql .= "
    GROUP BY grower, SKU
    ORDER BY grower, produced_cases DESC
";

$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    die("SQL prepare error: " . $mysqli->error);
}

if ($grower !== '') {
    $stmt->bind_param('sss', $from_dt, $to_dt, $grower);
} else {
    $stmt->bind_param('ss', $from_dt, $to_dt);
}

$stmt->execute();
$result = $stmt->get_result();
?>
<div class="container-fluid py-4">
    <h3 class="mb-3">Grower / SKU Production Report</h3>

    <form method="get" class="row g-2 mb-4">
        <div class="col-auto">
            <label class="form-label">From</label>
            <input type="date" name="from" value="<?= htmlspecialchars($from) ?>" class="form-control">
        </div>
        <div class="col-auto">
            <label class="form-label">To</label>
            <input type="date" name="to" value="<?= htmlspecialchars($to) ?>" class="form-control">
        </div>
        <div class="col-auto">
            <label class="form-label">Grower</label>
            <input type="text" name="grower" value="<?= htmlspecialchars($grower) ?>" class="form-control" placeholder="All growers">
        </div>
        <div class="col-auto align-self-end">
            <button type="submit" class="btn btn-primary">Filter</button>
        </div>
    </form>

    <table class="table table-sm table-bordered table-striped">
        <thead class="table-light">
            <tr>
                <th>Grower</th>
                <th>SKU</th>
                <th class="text-end">Produced Cases</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($result->num_rows === 0): ?>
            <tr><td colspan="3" class="text-center text-muted">No data for selected filters</td></tr>
        <?php else: ?>
            <?php while ($row = $result->fetch_assoc()): ?>
            <tr>
                <td><?= htmlspecialchars($row['grower']) ?></td>
                <td><?= htmlspecialchars($row['SKU']) ?></td>
                <td class="text-end"><?= (int)$row['produced_cases'] ?></td>
            </tr>
            <?php endwhile; ?>
        <?php endif; ?>
        </tbody>
    </table>
</div>
<?php
$stmt->close();
include '../includes/footer.php';
