-- Fix v_batch_stock and v_batch_profit views to include supplier returns
-- Run this after supplier_returns_v2.sql migration

-- =====================================================
-- 1. Update v_batch_stock view to include supplier returns
-- =====================================================
DROP VIEW IF EXISTS `v_batch_stock`;

CREATE VIEW `v_batch_stock` AS
SELECT 
    pi.purchase_item_id AS batch_id, 
    pi.item_id AS item_id, 
    i.item_scientific_name AS item_scientific_name, 
    i.item_company_name AS item_company_name, 
    i.item_dose AS item_dose, 
    pi.purchase_id AS purchase_id, 
    pi.purchase_item_quantity AS total_purchased, 
    pi.purchase_item_remaining_quantity AS remaining_quantity, 
    pi.purchase_item_cost_price AS cost_price, 
    pi.purchase_item_sell_price AS sell_price, 
    pi.purchase_item_expiry_date AS expiry_date, 
    pi.purchase_item_is_active AS is_active, 
    COALESCE(sold.total_sold, 0) AS total_sold, 
    COALESCE(sup_ret.total_returned, 0) AS total_returned
FROM purchase_items pi
JOIN items i ON i.item_id = pi.item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(invoice_item_quantity) AS total_sold 
    FROM invoice_items 
    GROUP BY purchase_item_id
) sold ON sold.purchase_item_id = pi.purchase_item_id
LEFT JOIN (
    SELECT purchase_item_id, SUM(supplier_return_item_quantity) AS total_returned 
    FROM supplier_return_items 
    GROUP BY purchase_item_id
) sup_ret ON sup_ret.purchase_item_id = pi.purchase_item_id;


-- =====================================================
-- 2. Update v_batch_profit view to include supplier returns
-- =====================================================
DROP VIEW IF EXISTS `v_batch_profit`;

CREATE VIEW `v_batch_profit` AS
SELECT 
    pi.purchase_item_id AS batch_id, 
    pi.item_id AS item_id, 
    i.item_scientific_name AS item_scientific_name, 
    pi.purchase_item_cost_price AS cost_price, 
    pi.purchase_item_sell_price AS sell_price, 
    pi.purchase_item_quantity AS total_purchased, 
    COALESCE(s.qty_sold, 0) AS qty_sold,
    COALESCE(sr.qty_returned, 0) AS qty_returned,
    (COALESCE(s.qty_sold, 0)) AS net_sold, 
    COALESCE(s.revenue, 0) AS gross_revenue, 
    0 AS total_refunded, 
    COALESCE(s.revenue, 0) AS net_revenue, 
    (COALESCE(s.qty_sold, 0) * pi.purchase_item_cost_price) AS total_cost, 
    (COALESCE(s.revenue, 0) - (COALESCE(s.qty_sold, 0) * pi.purchase_item_cost_price)) AS profit
FROM purchase_items pi
JOIN items i ON i.item_id = pi.item_id
LEFT JOIN (
    SELECT 
        purchase_item_id, 
        SUM(invoice_item_quantity) AS qty_sold, 
        SUM(invoice_item_line_total) AS revenue 
    FROM invoice_items 
    GROUP BY purchase_item_id
) s ON s.purchase_item_id = pi.purchase_item_id
LEFT JOIN (
    SELECT 
        purchase_item_id, 
        SUM(supplier_return_item_quantity) AS qty_returned
    FROM supplier_return_items 
    GROUP BY purchase_item_id
) sr ON sr.purchase_item_id = pi.purchase_item_id;

-- =====================================================
-- Note: total_returned / qty_returned = Supplier returns (items returned TO suppliers)
-- =====================================================