database_scripts/03_views.sql

-- ============================================================================
-- Views for Trade Sales Reporting
-- ============================================================================

-- View: Trade Sales Summary by Client
CREATE OR REPLACE VIEW trans.v_trade_sales_summary AS
SELECT 
    ts.client_id,
    COUNT(DISTINCT ts.shipment_id) as total_shipments,
    COUNT(DISTINCT ts.order_id) as total_orders,
    COALESCE(SUM(tsi.shipped_qty), 0) as total_qty_shipped,
    COUNT(DISTINCT tsi.sku) as unique_skus_shipped,
    MIN(ts.shipment_date) as first_shipment_date,
    MAX(ts.shipment_date) as last_shipment_date,
    COUNT(DISTINCT CASE WHEN ts.status = 'shipped' THEN ts.shipment_id END) as shipped_count,
    COUNT(DISTINCT CASE WHEN ts.status = 'draft' THEN ts.shipment_id END) as draft_count
FROM trans.scm_trade_shipment ts
LEFT JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
GROUP BY ts.client_id;

-- View: Order Execution Status
CREATE OR REPLACE VIEW trans.v_order_execution_status AS
WITH po_summary AS (
    SELECT 
        po_id, 
        SUM(ord_qty) as total_ordered_qty
    FROM trans.scm_po_item
    GROUP BY po_id
),
shipment_summary AS (
    SELECT 
        poi.po_id,
        SUM(tsi.shipped_qty) as total_shipped_qty,
        COUNT(DISTINCT tsi.shipment_id) as shipment_count,
        MAX(ts.shipment_date) as last_shipment_date
    FROM trans.scm_trade_shipment_item tsi
    JOIN trans.scm_po_item poi ON tsi.po_item_id = poi.po_item_id
    JOIN trans.scm_trade_shipment ts ON tsi.shipment_id = ts.shipment_id
    GROUP BY poi.po_id
)
SELECT 
    po.po_id as order_id,
    po.po_no as order_no,
    po.buyer_id as client_id,
    po.supplier_id,
    po.po_date as order_date,
    COALESCE(pos.total_ordered_qty, 0) as total_ordered_qty,
    COALESCE(ss.total_shipped_qty, 0) as total_shipped_qty,
    COALESCE(pos.total_ordered_qty, 0) - COALESCE(ss.total_shipped_qty, 0) as pending_qty,
    CASE 
        WHEN COALESCE(ss.total_shipped_qty, 0) = 0 THEN 'pending'
        WHEN COALESCE(ss.total_shipped_qty, 0) < COALESCE(pos.total_ordered_qty, 0) THEN 'partial'
        WHEN COALESCE(ss.total_shipped_qty, 0) >= COALESCE(pos.total_ordered_qty, 0) THEN 'completed'
        ELSE 'pending'
    END as execution_status,
    COALESCE(ss.shipment_count, 0) as shipment_count,
    ss.last_shipment_date
FROM trans.scm_po po
LEFT JOIN po_summary pos ON po.po_id = pos.po_id
LEFT JOIN shipment_summary ss ON po.po_id = ss.po_id
WHERE po.supplier_type = 'cnf' AND po.buyer_type = 'distributor';