<?php
// Include database connection
require_once "../config/connection.php";

// Set header to return JSON
header('Content-Type: application/json');

// Get all colors with related information
$query = "
    SELECT 
        mcp.id, 
        mcp.code_color, 
        mcp.deskripsi, 
        mcp.status, 
        mcp.lemari,
        b.nama_buyer, 
        c.nama_collection_surabaya 
    FROM master_color_panel_surabaya mcp
    JOIN buyers_surabaya b ON mcp.buyer_id = b.id
    JOIN collection_surabayas c ON mcp.collection_surabaya_id = c.id
    ORDER BY b.nama_buyer, mcp.code_color
";
$result = $conn->query($query);

$colors = [];

while ($row = $result->fetch_assoc()) {
    // Get components for each color
    $component_query = "
        SELECT 
            csi.id, 
            csi.kode_barang, 
            csi.status, 
            csi.lokasi, 
            csi.rack_no,
            csi.tanggal_pembuatan,
            csi.tanggal_kedaluwarsa,
            cc.component_name
        FROM color_stock_items_surabaya csi
        JOIN color_components_surabaya cc ON csi.component_id = cc.id
        WHERE csi.color_id = ?
        ORDER BY csi.rack_no
    ";
    $stmt = $conn->prepare($component_query);
    $stmt->bind_param("i", $row['id']);
    $stmt->execute();
    $component_result = $stmt->get_result();
    
    $components = [];
    while ($comp_row = $component_result->fetch_assoc()) {
        $components[] = $comp_row;
    }
    $stmt->close();
    
    // Add components to color data
    $row['components'] = $components;
    $colors[] = $row;
}

echo json_encode($colors);
?>