-- Create the v_purchase_item_tracking view
-- Run this SQL in your database

CREATE OR REPLACE VIEW `v_purchase_item_tracking` AS
SELECT 
    pi.purchase_item_id,
    pi.purchase_id,
    COALESCE(p.purchase_supplier_invoice_number, p.purchase_notes) as purchase_supplier_invoice_number,
    p.purchase_date,
    s.supplier_id,
    s.supplier_name,
    pi.item_id,
    i.item_scientific_name,
    i.item_brand,
    i.item_barcode,
    it.item_type_name,
    pi.purchase_item_batch_number,
    pi.purchase_item_quantity as qty_purchased,
    pi.purchase_item_remaining_quantity as qty_remaining,
    -- Sold = purchased - remaining - returned to supplier
    GREATEST(0, pi.purchase_item_quantity - pi.purchase_item_remaining_quantity - COALESCE(sr_totals.qty_returned_to_supplier, 0)) as qty_sold,
    COALESCE(sr_totals.qty_returned_to_supplier, 0) as qty_returned_to_supplier,
    pi.purchase_item_cost_price,
    pi.purchase_item_sell_price,
    (pi.purchase_item_quantity * pi.purchase_item_cost_price) as total_cost_value,
    (pi.purchase_item_remaining_quantity * pi.purchase_item_cost_price) as remaining_cost_value,
    pi.purchase_item_expiry_date,
    pi.purchase_item_is_active,
    pi.purchase_item_created_at
FROM purchase_items pi
LEFT JOIN purchases p ON pi.purchase_id = p.purchase_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
LEFT JOIN items i ON pi.item_id = i.item_id
LEFT JOIN item_types it ON i.item_type_id = it.item_type_id
LEFT JOIN (
    SELECT 
        purchase_item_id,
        SUM(supplier_return_item_quantity) as qty_returned_to_supplier
    FROM supplier_return_items
    GROUP BY purchase_item_id
) sr_totals ON pi.purchase_item_id = sr_totals.purchase_item_id;