<?php
// PHP code to display products from the database, grouped by user_id

$servername = "localhost";
$username = "wintelsoft_vinod";
$password = "vinodbhaskar69*";
$dbname = "wintelsoft_svf";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Handle filtering
$filter_status = isset($_GET['status']) ? $_GET['status'] : 'all';

// Modified SQL to include user_id and order by user_id and product_code
$sql = "SELECT user_id, product_code, date_of_order, customer_name, item_description, measurements_and_specification, quantity, scheduled_date_of_delivery, product_image, despatched_status FROM products";

// Apply the despatch status filter
if ($filter_status == 'Yes') {
    $sql .= " WHERE despatched_status = 'Yes'";
} elseif ($filter_status == 'No') {
    $sql .= " WHERE despatched_status = 'No'";
}

// Group by user_id first, then sort items by product_code within each user
$sql .= " ORDER BY user_id ASC, product_code ASC";

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

// Array to hold products grouped by user_id
$grouped_products = [];
$total_products = 0;

if ($result && $result->num_rows > 0) {
    $total_products = $result->num_rows;
    while ($row = $result->fetch_assoc()) {
        // Populate the grouped_products array
        $grouped_products[$row['user_id']][] = $row;
    }
}

// Close the database connection
if (isset($conn) && $conn->ping()) {
    $conn->close();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>View Products - Grouped by User</title>
    <style>
        @import url('https://fonts.googleapis.com/css2?family=Roboto:wght@400;700&display=swap');
        
        body {
            font-family: 'Roboto', sans-serif;
            background-color: #f0f2f5;
            color: #333;
            line-height: 1.6;
            margin: 0;
            padding: 20px;
        }

        .container {
            max-width: 1200px;
            margin: 40px auto;
            background-color: #ffffff;
            padding: 40px;
            border-radius: 12px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, 0.1);
            overflow-x: auto;
        }

        h2 {
            text-align: center;
            color: #ffffff;
            background-color: #3498db;
            padding: 20px;
            border-radius: 10px;
            margin-bottom: 30px;
            font-weight: 700;
            letter-spacing: 1px;
        }

        /* NEW STYLE: Styling for the user_id text - normal font size, bold, and spaced */
        .user-id-label {
            display: block;
            font-size: 1em; /* Normal size */
            font-weight: bold;
            color: #2c3e50;
            margin-top: 25px;
            margin-bottom: 5px;
            padding: 5px 0;
            border-bottom: 2px solid #ccc; /* Subtle separation line */
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 30px;
            min-width: 800px;
        }

        th, td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #34495e;
            color: white;
            font-weight: bold;
        }

        tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        tr:hover {
            background-color: #e9e9e9;
        }

        .product-image {
            max-width: 100px;
            height: auto;
            border-radius: 4px;
        }

        .filter-container {
            margin-bottom: 20px;
            text-align: right;
        }

        .filter-container label, .filter-container select {
            font-size: 16px;
            margin-right: 10px;
        }
        
        a {
            color: #3498db;
            text-decoration: none;
            font-weight: bold;
        }

        a:hover {
            text-decoration: underline;
        }

        @media (max-width: 768px) {
            .container {
                padding: 20px;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <h2>View Products - Grouped by User</h2>

        <div class="filter-container">
            <form method="get" action="">
                <label for="status">Filter by Despatch Status:</label>
                <select name="status" id="status" onchange="this.form.submit()">
                    <option value="all" <?php echo ($filter_status == 'all') ? 'selected' : ''; ?>>All</option>
                    <option value="Yes" <?php echo ($filter_status == 'Yes') ? 'selected' : ''; ?>>Yes</option>
                    <option value="No" <?php echo ($filter_status == 'No') ? 'selected' : ''; ?>>No</option>
                </select>
            </form>
        </div>

        <?php if ($total_products > 0): ?>
            
            <?php foreach ($grouped_products as $user_id => $products): ?>
                
                <p class="user-id-label">User ID: <?php echo htmlspecialchars($user_id); ?> (<?php echo count($products); ?> Products)</p>

                <table>
                    <thead>
                        <tr>
                            <th>Product Code</th>
                            <th>Scheduled Date of Delivery</th>
                            <th>Date of Order</th>
                            <th>Customer Name</th>
                            <th>Item Description</th>
                            <th>Measurements & Specification</th>
                            <th>Quantity</th>
                            <th>Product Image</th>
                            <th>Despatched Status</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($products as $row): ?>
                            <tr>
                                <td><a href='view_cost_report.php?product_code=<?php echo urlencode($row["product_code"]); ?>'><?php echo htmlspecialchars($row["product_code"]); ?></a></td>
                                <td><?php echo date('d/m/Y', strtotime($row["scheduled_date_of_delivery"])); ?></td>
                                <td><?php echo date('d/m/Y', strtotime($row["date_of_order"])); ?></td>
                                <td><?php echo htmlspecialchars($row["customer_name"]); ?></td>
                                <td><?php echo htmlspecialchars($row["item_description"]); ?></td>
                                <td><?php echo htmlspecialchars($row["measurements_and_specification"]); ?></td>
                                <td><?php echo htmlspecialchars($row["quantity"]); ?></td>
                                
                                <td>
                                    <?php if (!empty($row["product_image"])): ?>
                                        <img src='uploads/<?php echo htmlspecialchars($row["product_image"]); ?>' alt='Product Image' class='product-image'>
                                    <?php else: ?>
                                        No Image
                                    <?php endif; ?>
                                </td>
                                
                                <td><?php echo htmlspecialchars($row["despatched_status"]); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php endforeach; ?>
        <?php else: ?>
            <p style="text-align: center; font-size: 1.2em; padding: 20px; border: 1px dashed #ccc; border-radius: 8px;">No products found matching the current criteria.</p>
        <?php endif; ?>
    </div>
</body>
</html>