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

// 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);
}

// Query to get SKU and quantity summary
$sql = "SELECT 
            seller_sku, 
            SUM(CAST(quantity AS DECIMAL(10,2))) as total_quantity,
            COUNT(order_id) as order_count
        FROM order_import
        GROUP BY seller_sku
        ORDER BY total_quantity DESC";

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

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SKU Quantity Summary</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
        h1 {
            color: #333;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
            position: sticky;
            top: 0;
        }
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        tr:hover {
            background-color: #f1f1f1;
        }
        .total-row {
            font-weight: bold;
            background-color: #e6f7ff;
        }
    </style>
</head>
<body>
    <h1>SKU Quantity Summary</h1>
    
    <?php if ($result && $result->num_rows > 0): ?>
        <table>
            <thead>
                <tr>
                    <th>No</th>
                    <th>Seller SKU</th>
                    <th>Total Quantity</th>
                    <th>Order Count</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $counter = 1;
                $grand_total = 0;
                $grand_order_count = 0;
                while($row = $result->fetch_assoc()): 
                    $grand_total += $row['total_quantity'];
                    $grand_order_count += $row['order_count'];
                ?>
                <tr>
                    <td><?php echo $counter++; ?></td>
                    <td><?php echo htmlspecialchars($row['seller_sku']); ?></td>
                    <td><?php echo number_format($row['total_quantity'], 2); ?></td>
                    <td><?php echo $row['order_count']; ?></td>
                </tr>
                <?php endwhile; ?>
                <tr class="total-row">
                    <td colspan="2">Grand Total</td>
                    <td><?php echo number_format($grand_total, 2); ?></td>
                    <td><?php echo $grand_order_count; ?></td>
                </tr>
            </tbody>
        </table>
    <?php else: ?>
        <p>No data found in the order_import table.</p>
    <?php endif; ?>
    
    <?php $conn->close(); ?>
</body>
</html>