<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// Warehouse name mapping
$warehouse_names = [
    1 => 'Sidoarjo',
    2 => 'Tangerang',
    3 => 'Makassar',
    4 => 'Bali',
    5 => 'Semarang',
    6 => 'Bandung'
];

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set default date filter to today
$today = date('Y-m-d');
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : $today;
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : $today;

// Build SQL query with optional date filter
$sql = "SELECT id_warehouse, COUNT(DISTINCT resi) as total_unique_resi 
        FROM resi 
        WHERE 1=1";

// Add date filter if provided
if (!empty($start_date)) {
    $sql .= " AND DATE(scan_in) >= '" . $conn->real_escape_string($start_date) . "'";
}
if (!empty($end_date)) {
    $sql .= " AND DATE(scan_in) <= '" . $conn->real_escape_string($end_date) . "'";
}

$sql .= " GROUP BY id_warehouse 
          ORDER BY id_warehouse";

$result = $conn->query($sql);

// Get total unique resi across all warehouses
$total_sql = "SELECT COUNT(DISTINCT resi) as grand_total FROM resi";
if (!empty($start_date) || !empty($end_date)) {
    $total_sql .= " WHERE 1=1";
    if (!empty($start_date)) {
        $total_sql .= " AND DATE(scan_in) >= '" . $conn->real_escape_string($start_date) . "'";
    }
    if (!empty($end_date)) {
        $total_sql .= " AND DATE(scan_in) <= '" . $conn->real_escape_string($end_date) . "'";
    }
}
$total_result = $conn->query($total_sql);
$grand_total = $total_result->fetch_assoc()['grand_total'];
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Resi Check Report</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            background-color: #f5f5f5;
        }
        .container {
            max-width: 1000px;
            margin: 0 auto;
            background-color: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        h2 {
            color: #333;
            margin-bottom: 20px;
        }
        .summary-card {
            background-color: #e8f5e9;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            display: flex;
            justify-content: space-between;
            flex-wrap: wrap;
        }
        .summary-item {
            margin: 5px 10px;
        }
        .summary-value {
            font-size: 1.2em;
            font-weight: bold;
            color: #2e7d32;
        }
        .filter-form {
            background-color: #f9f9f9;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .filter-form label {
            margin-right: 10px;
            font-weight: bold;
        }
        .filter-form input[type="date"] {
            padding: 5px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        .filter-form button {
            padding: 5px 15px;
            background-color: #4CAF50;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .filter-form button:hover {
            background-color: #45a049;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 12px;
            text-align: left;
        }
        th {
            background-color: #4CAF50;
            color: white;
        }
        tr:nth-child(even) {
            background-color: #f2f2f2;
        }
        .no-results {
            padding: 20px;
            text-align: center;
            color: #666;
        }
        .reset-btn {
            padding: 5px 15px;
            background-color: #f44336;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            margin-left: 10px;
        }
        .reset-btn:hover {
            background-color: #d32f2f;
        }
    </style>
</head>
<body>
    <div class="container">
        <h2>Total Unique Resi per Warehouse</h2>
        
        <div class="summary-card">
            <div class="summary-item">
                <div>Date Range:</div>
                <div class="summary-value">
                    <?php 
                    echo htmlspecialchars($start_date == $end_date ? $start_date : "$start_date to $end_date");
                    ?>
                </div>
            </div>
            <div class="summary-item">
                <div>Total Warehouses:</div>
                <div class="summary-value"><?php echo $result->num_rows; ?></div>
            </div>
            <div class="summary-item">
                <div>Total Unique Resi:</div>
                <div class="summary-value"><?php echo $grand_total; ?></div>
            </div>
        </div>
        
        <div class="filter-form">
            <form method="get" action="">
                <label for="start_date">From Date:</label>
                <input type="date" id="start_date" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                
                <label for="end_date">To Date:</label>
                <input type="date" id="end_date" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                
                <button type="submit">Filter</button>
                <a href="resi_check.php" class="reset-btn">Reset</a>
            </form>
        </div>

        <?php
        if ($result->num_rows > 0) {
            echo "<table>";
            echo "<tr><th>Warehouse ID</th><th>Warehouse Name</th><th>Total Unique Resi</th></tr>";
            
            // Output data of each row
            while($row = $result->fetch_assoc()) {
                $warehouse_id = $row["id_warehouse"];
                $warehouse_name = isset($warehouse_names[$warehouse_id]) ? $warehouse_names[$warehouse_id] : 'Unknown';
                
                echo "<tr>";
                echo "<td>" . htmlspecialchars($warehouse_id) . "</td>";
                echo "<td>" . htmlspecialchars($warehouse_name) . "</td>";
                echo "<td>" . htmlspecialchars($row["total_unique_resi"]) . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        } else {
            echo "<div class='no-results'>No results found for the selected date range</div>";
        }
        ?>
    </div>
</body>
</html>

<?php
// Close connection
$conn->close();
?>