<?php
include_once("includes/config.php");
header('Content-Type: text/html');

$limit = 20; 
$offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
$categoryFilter = isset($_GET['category']) ? (int)$_GET['category'] : null;
$brandFilter = isset($_GET['brand']) ? $_GET['brand'] : null;
$searchTerm = isset($_GET['search']) ? $_GET['search'] : null;

try {
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    $query = "SELECT 
        p.id AS product_id,
        p.product_code,
        p.name AS product_name,
        p.image AS product_image,
        p.barcode,
        c.id AS category_id,
        c.name AS category_name,
        b.name AS brand_name,
        COALESCE(purch.total_purchased,0) AS total_purchased,
        COALESCE(sale.total_unit_sold,0) AS total_unit_sold,
        COALESCE(ret.total_unit_returned,0) AS total_unit_returned,
        COALESCE(dmg.total_defective,0) AS total_defective,
        u.actual_name AS unit_name,
        v.default_sell_price
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN brands b ON p.brand_id = b.id
    LEFT JOIN units u ON p.unit_id = u.id
    LEFT JOIN variations v ON p.id = v.product_id
    LEFT JOIN (
        SELECT product_id, SUM(quantity) AS total_purchased
        FROM purchase_lines
        GROUP BY product_id
    ) purch ON p.id = purch.product_id
    LEFT JOIN (
        SELECT product_id, SUM(sale_qty) AS total_unit_sold
        FROM stock_sale
        GROUP BY product_id
    ) sale ON p.id = sale.product_id
    LEFT JOIN (
        SELECT product_id, SUM(sale_qty) AS total_unit_returned
        FROM return_sale
        GROUP BY product_id
    ) ret ON p.id = ret.product_id
    LEFT JOIN (
        SELECT product_id, SUM(sale_qty) AS total_defective
        FROM defective_sale
        GROUP BY product_id
    ) dmg ON p.id = dmg.product_id
    WHERE p.business_id = :business_id";

    if ($categoryFilter) $query .= " AND c.id = :category_id";
    if ($brandFilter) $query .= " AND b.name = :brand_name";
    if ($searchTerm) {
        $query .= " AND (p.name LIKE :search_term OR p.product_code LIKE :search_term OR p.barcode LIKE :search_term)";  
    }

    $query .= " ORDER BY p.name ASC LIMIT :limit OFFSET :offset";

    $stmt = $conn->prepare($query);

    if ($categoryFilter) $stmt->bindParam(':category_id', $categoryFilter, PDO::PARAM_INT);
    if ($brandFilter) $stmt->bindParam(':brand_name', $brandFilter, PDO::PARAM_STR);
    if ($searchTerm) { 
        $searchTerm = "%$searchTerm%"; 
        $stmt->bindParam(':search_term', $searchTerm, PDO::PARAM_STR); 
    }

    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindValue(':business_id', 3, PDO::PARAM_INT);

    $stmt->execute();
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $rows) {
       
        $current_stock_qty = $rows['total_purchased'] - $rows['total_unit_sold'] + $rows['total_unit_returned'] - $rows['total_defective'];
        ?>
<div class="col-lg-3 col-md-4 col-sm-6 col-6 mt-2 product-card"
     data-product-name="<?php echo htmlspecialchars($rows["product_name"]); ?>"
     data-product-barcode="<?php echo htmlspecialchars($rows["barcode"]); ?>"
     data-product-code="<?php echo (int)$rows["product_id"]; ?>"
     data-product-category="<?php echo htmlspecialchars($rows["category_id"]); ?>"
     data-product-brand="<?php echo htmlspecialchars($rows["brand_name"]); ?>"
     data-current-stock="<?php echo $current_stock_qty; ?>"
     data-sale-price="<?php echo $rows['default_sell_price']; ?>">

    <div class="card ultra-compact-card shadow-sm rounded-3 add-to-cart" data-product-code="<?php echo (int)$rows["product_id"]; ?>">

        <!-- Top badges -->
        <div class="d-flex justify-content-between mb-1 px-1">
            <span class="badge bg-success stock-badge"> <?php echo $current_stock_qty; ?></span>
            <span class="badge text-white price-badge" style="background-color: #000;">RS <?php echo $rows['default_sell_price']; ?></span>
        </div>

        <!-- Product Image -->
        <?php
        $imagePath = !empty($rows["product_image"]) ? "img/" . $rows["product_image"] : "dummy-image-square.jpg";  
        ?>
        <div class="product-image-wrapper mb-1">
            <img src="<?php echo $imagePath; ?>" 
                 class="card-img-top product-image" 
                 alt="<?php echo htmlspecialchars($rows["product_name"]); ?>">
        </div>

        <!-- Product Title -->
        <div class="card-body d-flex flex-column justify-content-between text-center p-1">
            <p class="product-title"><?php echo htmlspecialchars($rows["product_name"]); ?></p>
        </div>
    </div>
</div>

        <?php
    }
} catch (PDOException $e) {
    echo 'Database error: ' . $e->getMessage();
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}
?>


<style>
.product-card {
    padding: 4px;
}

@media (min-width: 1200px) { /* large desktops */
  .col-lg-2 {
    flex: 0 0 20%;
    max-width: 20%;
  }
}

.ultra-compact-card {
    background-color: #fff;
    height: auto; /* Auto height to adjust content */
    padding: 6px;
    display: flex;
    flex-direction: column;
    justify-content: flex-start; 
    transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
    border: 1px solid #e2e2e2;
    border-radius: 10px;
}

.product-cards-scroll {
    height: 600px; /* adjust as needed */
    overflow-y: auto;
    padding: 5px;
    display: flex;
    flex-wrap: wrap;
    gap: 5px;
}

.ultra-compact-card:hover {
    transform: scale(1.05);
    box-shadow: 0 6px 16px rgba(0, 0, 0, 0.15);
}

/* Product image */
.product-image-wrapper {
    width: 100%;
    height: 94px;
    margin-bottom: 4px;
}

.product-image {
    width: 100%;
    height: 100%;
    object-fit: contain;
    border-radius: 6px;
    background-color: #f9f9f9;
}

/* Badges on top of card, not on image */
.stock-badge, .price-badge {
    position: relative;
    font-size: 0.7rem;
    padding: 2px 5px;
    border-radius: 4px;
    display: inline-block;
}

.stock-badge {
    background-color: #198754; /* bootstrap success */
    color: #fff;
}

.price-badge {
    background-color: #000;
    color: #fff;
}

/* Flex container for badges */
.badge-container {
    display: flex;
    justify-content: space-between;
    margin-bottom: 4px;
    padding: 0 4px;
}

.product-title {
    font-size: 0.8rem;
    font-weight: 600;
    line-height: 1.2;
    white-space: normal;      
    word-break: break-word;   
    overflow: hidden;
    text-overflow: unset;     
    margin: 4px 0 2px 0;
    text-align: center;
    display: block;
    max-height: 2.4em;        
}

@media (max-width: 992px) { /* laptops & tablets */
  .ultra-compact-card {
    height: auto;
  }
}

@media (max-width: 768px) { /* tablets */
  .col-sm-4 {
    flex: 0 0 33.3333%;
    max-width: 33.3333%;
  }
}

@media (max-width: 576px) { /* mobile */
  .col-6 {
    flex: 0 0 50%;
    max-width: 50%;
  }
}
</style>
