-- Diagnostic SQL Queries for Production Availability Checking
-- Use these queries to investigate availability issues for production orders

-- ============================================================================
-- 1. Check Product Details for "Test Cotton Fabric 1768376270872"
-- ============================================================================
SELECT 
    id,
    name,
    sku,
    product_type,
    track_by_dimensions,
    unit_of_measure,
    created_at
FROM products
WHERE name LIKE '%Test Cotton Fabric%' OR sku LIKE '%1768376270872%'
ORDER BY id DESC
LIMIT 5;

-- ============================================================================
-- 2. Check RM Inventory Pieces for the Product (Dimension-Based)
-- ============================================================================
-- Replace PRODUCT_ID with actual product ID from query 1
SET @product_id = (SELECT id FROM products WHERE sku LIKE '%1768376270872%' LIMIT 1);

SELECT 
    rmp.id,
    rmp.product_id,
    p.name AS product_name,
    rmp.status,
    rmp.length,
    rmp.width,
    rmp.unit,
    rmp.usable_length,
    rmp.usable_width,
    (rmp.length * rmp.width) AS full_area,
    CASE 
        WHEN rmp.status = 'FULL' THEN (rmp.length * rmp.width)
        WHEN rmp.status = 'USABLE' AND rmp.usable_length IS NOT NULL AND rmp.usable_width IS NOT NULL 
            THEN (rmp.usable_length * rmp.usable_width)
        ELSE 0
    END AS usable_area,
    rmp.source,
    rmp.source_reference_id,
    rmp.created_at
FROM rm_inventory_pieces rmp
JOIN products p ON rmp.product_id = p.id
WHERE rmp.product_id = @product_id
ORDER BY rmp.status, rmp.created_at DESC;

-- ============================================================================
-- 3. Summary of Available Area by Status
-- ============================================================================
SELECT 
    rmp.status,
    COUNT(*) AS piece_count,
    SUM(rmp.length * rmp.width) AS total_full_area,
    SUM(
        CASE 
            WHEN rmp.status = 'FULL' THEN (rmp.length * rmp.width)
            WHEN rmp.status = 'USABLE' AND rmp.usable_length IS NOT NULL AND rmp.usable_width IS NOT NULL 
                THEN (rmp.usable_length * rmp.usable_width)
            ELSE 0
        END
    ) AS total_usable_area,
    rmp.unit
FROM rm_inventory_pieces rmp
WHERE rmp.product_id = @product_id
GROUP BY rmp.status, rmp.unit;

-- ============================================================================
-- 4. Check Production Order 18 Details
-- ============================================================================
SELECT 
    po.id,
    po.fg_variant_id,
    po.quantity,
    po.status,
    pv.sku AS variant_sku,
    p.name AS product_name,
    po.created_at
FROM production_orders po
LEFT JOIN product_variants pv ON po.fg_variant_id = pv.id
LEFT JOIN products p ON pv.product_id = p.id
WHERE po.id = 18;

-- ============================================================================
-- 5. Check BOM Items for Production Order 18
-- ============================================================================
SET @variant_id = (SELECT fg_variant_id FROM production_orders WHERE id = 18);

SELECT 
    bom.id AS bom_id,
    bom.variant_id,
    bi.id AS bom_item_id,
    bi.rm_product_id,
    p.name AS rm_product_name,
    p.track_by_dimensions,
    bi.use_dimensions,
    bi.required_length,
    bi.required_width,
    bi.dimension_unit,
    bi.quantity_per_unit,
    (bi.required_length * bi.required_width) AS area_per_unit,
    (bi.required_length * bi.required_width * (SELECT quantity FROM production_orders WHERE id = 18)) AS total_area_required
FROM boms bom
JOIN bom_items bi ON bom.id = bi.bom_id
JOIN products p ON bi.rm_product_id = p.id
WHERE bom.variant_id = @variant_id;

-- ============================================================================
-- 6. Check Availability for Production Order 18 - Dimension-Based Items
-- ============================================================================
-- For each dimension-based BOM item, check available pieces
SELECT 
    bi.rm_product_id,
    p.name AS rm_product_name,
    bi.required_length,
    bi.required_width,
    bi.dimension_unit,
    (SELECT quantity FROM production_orders WHERE id = 18) AS production_quantity,
    (bi.required_length * bi.required_width) AS area_per_unit,
    (bi.required_length * bi.required_width * (SELECT quantity FROM production_orders WHERE id = 18)) AS total_area_required,
    COUNT(rmp.id) AS available_pieces_count,
    SUM(
        CASE 
            WHEN rmp.status = 'FULL' THEN (rmp.length * rmp.width)
            WHEN rmp.status = 'USABLE' AND rmp.usable_length IS NOT NULL AND rmp.usable_width IS NOT NULL 
                THEN (rmp.usable_length * rmp.usable_width)
            ELSE 0
        END
    ) AS total_available_area,
    SUM(
        CASE 
            WHEN rmp.status = 'FULL' THEN (rmp.length * rmp.width)
            WHEN rmp.status = 'USABLE' AND rmp.usable_length IS NOT NULL AND rmp.usable_width IS NOT NULL 
                THEN (rmp.usable_length * rmp.usable_width)
            ELSE 0
        END
    ) - (bi.required_length * bi.required_width * (SELECT quantity FROM production_orders WHERE id = 18)) AS shortfall
FROM bom_items bi
JOIN products p ON bi.rm_product_id = p.id
JOIN boms bom ON bi.bom_id = bom.id
LEFT JOIN rm_inventory_pieces rmp ON (
    rmp.product_id = bi.rm_product_id 
    AND rmp.status IN ('FULL', 'USABLE')
)
WHERE bom.variant_id = @variant_id
    AND bi.use_dimensions = 1
GROUP BY bi.id, bi.rm_product_id, p.name, bi.required_length, bi.required_width, bi.dimension_unit;

-- ============================================================================
-- 7. Check Availability for Production Order 18 - Quantity-Based Items
-- ============================================================================
SELECT 
    bi.rm_product_id,
    p.name AS rm_product_name,
    bi.quantity_per_unit,
    (SELECT quantity FROM production_orders WHERE id = 18) AS production_quantity,
    (bi.quantity_per_unit * (SELECT quantity FROM production_orders WHERE id = 18)) AS total_quantity_required,
    COALESCE(inv.quantity, 0) AS available_quantity,
    (bi.quantity_per_unit * (SELECT quantity FROM production_orders WHERE id = 18)) - COALESCE(inv.quantity, 0) AS shortfall
FROM bom_items bi
JOIN products p ON bi.rm_product_id = p.id
JOIN boms bom ON bi.bom_id = bom.id
LEFT JOIN inventory inv ON (
    inv.product_id = bi.rm_product_id 
    AND inv.variant_id IS NULL
)
WHERE bom.variant_id = @variant_id
    AND bi.use_dimensions = 0;

-- ============================================================================
-- 8. Detailed Piece Analysis for Dimension-Based RM
-- ============================================================================
-- Shows each piece and whether it can satisfy the requirement
SELECT 
    rmp.id AS piece_id,
    rmp.status,
    rmp.length AS piece_length,
    rmp.width AS piece_width,
    rmp.unit AS piece_unit,
    CASE 
        WHEN rmp.status = 'FULL' THEN rmp.length
        ELSE COALESCE(rmp.usable_length, 0)
    END AS available_length,
    CASE 
        WHEN rmp.status = 'FULL' THEN rmp.width
        ELSE COALESCE(rmp.usable_width, 0)
    END AS available_width,
    bi.required_length,
    bi.required_width,
    bi.dimension_unit,
    CASE 
        WHEN rmp.status = 'FULL' THEN (rmp.length * rmp.width)
        WHEN rmp.status = 'USABLE' AND rmp.usable_length IS NOT NULL AND rmp.usable_width IS NOT NULL 
            THEN (rmp.usable_length * rmp.usable_width)
        ELSE 0
    END AS usable_area,
    (bi.required_length * bi.required_width) AS required_area_per_unit,
    CASE 
        WHEN rmp.status = 'FULL' AND rmp.length >= bi.required_length AND rmp.width >= bi.required_width THEN 'CAN_FIT'
        WHEN rmp.status = 'USABLE' AND COALESCE(rmp.usable_length, 0) >= bi.required_length 
            AND COALESCE(rmp.usable_width, 0) >= bi.required_width THEN 'CAN_FIT'
        ELSE 'CANNOT_FIT'
    END AS can_satisfy_requirement
FROM rm_inventory_pieces rmp
JOIN products p ON rmp.product_id = p.id
JOIN bom_items bi ON bi.rm_product_id = p.id
JOIN boms bom ON bi.bom_id = bom.id
WHERE bom.variant_id = @variant_id
    AND bi.use_dimensions = 1
    AND rmp.status IN ('FULL', 'USABLE')
ORDER BY rmp.product_id, rmp.status, rmp.id;
