<?php 
session_start();
include 'includes/header.php'; 
include 'includes/sidebar.php'; 
include 'includes/connect.php';

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* ================= QUERY ================= */
$query = "
SELECT 
    c.id,
    c.category_name,
    COUNT(DISTINCT im.variety_id) as total_varieties,
    SUM(im.tray_no) as total_trays,
    AVG(DATEDIFF(CURDATE(), im.creation_date)) as avg_days
FROM category c
LEFT JOIN inventory_management im ON im.category_id = c.id
GROUP BY c.id
ORDER BY LOWER(c.category_name) ASC
";

$result = mysqli_query($conn, $query);
?>

<style>

/* ===== PAGE BG ===== */
.main-content-wrap {
    background: #f8fafc;
}

/* ===== BREADCRUMB ===== */
.breadcrumb {
    display: flex;
    align-items: center;
    flex-wrap: wrap;
    gap: 8px;
    font-size: 15px;
    margin-bottom: 22px;
    padding: 14px 18px;
    background: linear-gradient(135deg, #ffffff, #f9fafb);
    border-radius: 12px;
    box-shadow: 0 6px 18px rgba(0,0,0,0.06);
    font-family: "Segoe UI", Roboto, Arial, sans-serif;
}

/* links */
.breadcrumb a {
    text-decoration: none;
    color: #ff7a18;
    font-weight: 600;
    font-size: 15px;
    transition: all 0.2s ease;
}

.breadcrumb a:hover {
    color: #e65c00;
    text-decoration: underline;
}

/* text */
.breadcrumb span {
    color: #374151;
    font-weight: 500;
}

/* divider */
.breadcrumb .divider {
    color: #9ca3af;
    font-size: 16px;
    margin: 0 6px;
}

/* icon styling */
.breadcrumb .home-icon {
    font-size: 16px;
}

/* ===== GRID ===== */
.product-grid {
    display: grid;
    grid-template-columns: repeat(auto-fill, minmax(270px, 1fr));
    gap: 22px;
}

/* ===== CARD ===== */
.product-card {
    background: linear-gradient(135deg, #ffffff, #f9fafb);
    border-radius: 16px;
    padding: 20px;
    box-shadow: 0 8px 25px rgba(0,0,0,0.06);
    transition: all 0.3s ease;
    position: relative;
    border: 1px solid rgba(0,0,0,0.04);
}

/* Hover Effect */
.product-card:hover {
    transform: translateY(-6px) scale(1.01);
    box-shadow: 0 15px 35px rgba(0,0,0,0.12);
}

/* Top Accent */
.product-card::before {
    content: "";
    position: absolute;
    height: 4px;
    width: 100%;
    top: 0;
    left: 0;
    border-radius: 16px 16px 0 0;
    background: linear-gradient(90deg, #37ff5a, #198754);
}

/* ===== HEADER ===== */
.product-header {
    display: flex;
    align-items: center;
    gap: 14px;
    margin-bottom: 18px;
}

/* ICON */
.product-icon {
    width: 46px;
    height: 46px;
    border-radius: 12px;
    background: linear-gradient(135deg, #fff3e0, #ffe0b2);
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 20px;
}

/* TITLE */
.product-title {
    font-size: 17px;
    font-weight: 600;
    color: #111827;
}

/* ===== STATS ===== */
.product-stat {
    display: flex;
    justify-content: space-between;
    align-items: center;
    padding: 8px 0;
    border-bottom: 1px dashed #e5e7eb;
    font-size: 13px;
}

.product-stat:last-child {
    border-bottom: none;
}

/* LABEL TEXT */
.product-stat span:first-child {
    color: #6b7280;
}

/* ===== BADGES ===== */
.badge {
    padding: 6px 12px;
    border-radius: 8px;
    font-size: 11px;
    font-weight: 600;
    letter-spacing: 0.3px;
}

.badge-green {
    background: #dcfce7;
    color: #166534;
}

.badge-blue {
    background: #dbeafe;
    color: #1e40af;
}

.badge-orange {
    background: #fef3c7;
    color: #92400e;
}

/* EMPTY */
.no-data {
    text-align: center;
    padding: 30px;
    font-size: 14px;
    color: #6b7280;
}

.product-card {
    position: relative;
    cursor: pointer;
    overflow: hidden;
}

/* overlay layer */
.product-card::after {
    content: "View Details →";
    position: absolute;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
    background: rgb(42 93 29 / 22%);
    color: #fff;
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 16px;
    font-weight: 600;
    opacity: 0;
    transition: all 0.3s ease;
    border-radius: 16px;
}

/* show overlay on hover */
.product-card:hover::after {
    opacity: 1;
}

/* slight lift + zoom */
.product-card:hover {
    transform: translateY(-6px) scale(1.03);
    box-shadow: 0 20px 45px rgba(0,0,0,0.15);
}

</style>

<div class="main-content-inner">
<div class="main-content-wrap">

<!-- ================= BREADCRUMB ================= -->
<div class="breadcrumb">
    <span class="home-icon">🏠</span>
    <a href="index.php">Home</a>
    <span class="divider">›</span>
    <span>Products</span>
</div>

<div class="flex items-center flex-wrap justify-between gap20 mb-30">
    <h3 style="font-size:20px; font-weight:600;">Our Products</h3>
</div>

<div class="product-grid">

<?php if(mysqli_num_rows($result) > 0){ ?>

<?php while($row = mysqli_fetch_assoc($result)){ 

    $crop = $row['category_name'];
    $varieties = $row['total_varieties'] ?? 0;
    $trays = $row['total_trays'] ?? 0;
    $days = round($row['avg_days'] ?? 0);

?>

<div class="product-card" onclick="window.location.href='varieties.php?category_id=<?php echo $row['id']; ?>'">

    <div class="product-header">
        <div class="product-icon">🌱</div>
        <div class="product-title">
            <?php echo htmlspecialchars($crop); ?>
        </div>
    </div>

    <div class="product-stat">
        <span>Varieties</span>
        <span class="badge badge-blue"><?php echo $varieties; ?></span>
    </div>

    <div class="product-stat">
        <span>Total Trays</span>
        <span class="badge badge-green"><?php echo $trays; ?></span>
    </div>

    <div class="product-stat">
        <span>Avg Growing</span>
        <span class="badge badge-orange"><?php echo $days; ?> days</span>
    </div>

</div>

<?php } ?>

<?php } else { ?>

<div class="no-data">
    No products found
</div>

<?php } ?>

</div>

</div>
</div>

<?php include 'includes/footer.php'; ?>