docs/database/migrations/migration_trade_sales_tables.sql

-- Trade Sales (B2B) Module Database Migration
-- Creates tables for cnf → distributor outbound sales operations
-- Run this migration after existing PO/GRN tables are in place

-- ============================================================================
-- Trade Shipment Header Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS scm_trade_shipment (
    shipment_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    shipment_no     VARCHAR(40) UNIQUE NOT NULL,
    
    -- Order reference
    order_id        UUID NOT NULL REFERENCES scm_po(po_id),
    
    -- Parties
    supplier_id     UUID NOT NULL,  -- cnf merchant ID
    client_id       UUID NOT NULL,  -- distributor merchant ID
    
    -- Warehouse
    warehouse_id    UUID NOT NULL,
    
    -- Status and dates
    status          VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'shipped', 'closed')),
    shipment_date   DATE NOT NULL,
    
    -- Logistics
    transporter     VARCHAR(100),
    lr_no           VARCHAR(50),
    vehicle_no      VARCHAR(20),
    
    -- Audit
    created_by      VARCHAR(64),
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes for trade shipment
CREATE INDEX IF NOT EXISTS idx_trade_shipment_order_id ON scm_trade_shipment(order_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_supplier_id ON scm_trade_shipment(supplier_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_client_id ON scm_trade_shipment(client_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_date ON scm_trade_shipment(shipment_date);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_status ON scm_trade_shipment(status);

-- ============================================================================
-- Trade Shipment Items Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS scm_trade_shipment_item (
    item_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    shipment_id     UUID NOT NULL REFERENCES scm_trade_shipment(shipment_id) ON DELETE CASCADE,
    
    -- PO item reference
    po_item_id      UUID NOT NULL REFERENCES scm_po_item(po_item_id),
    
    -- Product details
    sku             VARCHAR(64) NOT NULL,
    batch_no        VARCHAR(50) NOT NULL,
    
    -- Quantities
    ordered_qty     NUMERIC(12,3) NOT NULL,
    shipped_qty     NUMERIC(12,3) NOT NULL,
    balance_qty     NUMERIC(12,3) NOT NULL,
    
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT chk_trade_shipment_item_qty CHECK (
        shipped_qty >= 0 AND 
        ordered_qty >= 0 AND 
        balance_qty >= 0 AND
        shipped_qty <= ordered_qty
    )
);

-- Indexes for trade shipment items
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_shipment_id ON scm_trade_shipment_item(shipment_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_po_item_id ON scm_trade_shipment_item(po_item_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_sku ON scm_trade_shipment_item(sku);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_batch ON scm_trade_shipment_item(batch_no);

-- ============================================================================
-- Update Trigger for Trade Shipment
-- ============================================================================

CREATE OR REPLACE FUNCTION update_trade_shipment_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_trade_shipment_updated_at
    BEFORE UPDATE ON scm_trade_shipment
    FOR EACH ROW
    EXECUTE FUNCTION update_trade_shipment_updated_at();

-- ============================================================================
-- Sequence for Shipment Numbers (Optional - can use application logic)
-- ============================================================================

CREATE SEQUENCE IF NOT EXISTS seq_trade_shipment_no
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    CACHE 1;

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

-- View: Trade Sales Summary by Client
CREATE OR REPLACE VIEW 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,
    SUM(tsi.shipped_qty) 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(CASE WHEN ts.status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN ts.status = 'draft' THEN 1 END) as draft_count
FROM scm_trade_shipment ts
LEFT JOIN 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 v_order_execution_status AS
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,
    SUM(poi.ord_qty) as total_ordered_qty,
    COALESCE(SUM(tsi.shipped_qty), 0) as total_shipped_qty,
    SUM(poi.ord_qty) - COALESCE(SUM(tsi.shipped_qty), 0) as pending_qty,
    CASE 
        WHEN COALESCE(SUM(tsi.shipped_qty), 0) = 0 THEN 'pending'
        WHEN COALESCE(SUM(tsi.shipped_qty), 0) < SUM(poi.ord_qty) THEN 'partial'
        WHEN COALESCE(SUM(tsi.shipped_qty), 0) >= SUM(poi.ord_qty) THEN 'completed'
        ELSE 'pending'
    END as execution_status,
    COUNT(DISTINCT ts.shipment_id) as shipment_count,
    MAX(ts.shipment_date) as last_shipment_date
FROM scm_po po
LEFT JOIN scm_po_item poi ON po.po_id = poi.po_id
LEFT JOIN scm_trade_shipment_item tsi ON poi.po_item_id = tsi.po_item_id
LEFT JOIN scm_trade_shipment ts ON tsi.shipment_id = ts.shipment_id
WHERE po.supplier_type = 'cnf' AND po.buyer_type = 'distributor'
GROUP BY po.po_id, po.po_no, po.buyer_id, po.supplier_id, po.po_date;

-- ============================================================================
-- Stored Procedure: Get Trade Sales Analytics
-- ============================================================================

CREATE OR REPLACE FUNCTION get_trade_sales_analytics(
    p_supplier_id VARCHAR(64) DEFAULT NULL,
    p_client_id VARCHAR(64) DEFAULT NULL,
    p_date_from DATE DEFAULT NULL,
    p_date_to DATE DEFAULT NULL
)
RETURNS TABLE (
    metric_name VARCHAR(50),
    metric_value NUMERIC,
    metric_unit VARCHAR(20)
) AS $$
BEGIN
    -- Total shipments
    RETURN QUERY
    SELECT 
        'total_shipments'::VARCHAR(50) as metric_name,
        COUNT(1)::NUMERIC as metric_value,
        'count'::VARCHAR(20) as metric_unit
    FROM scm_trade_shipment ts
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Total quantity shipped
    RETURN QUERY
    SELECT 
        'total_qty_shipped'::VARCHAR(50) as metric_name,
        COALESCE(SUM(tsi.shipped_qty), 0)::NUMERIC as metric_value,
        'units'::VARCHAR(20) as metric_unit
    FROM scm_trade_shipment ts
    JOIN scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Unique SKUs shipped
    RETURN QUERY
    SELECT 
        'unique_skus'::VARCHAR(50) as metric_name,
        COUNT(DISTINCT tsi.sku)::NUMERIC as metric_value,
        'count'::VARCHAR(20) as metric_unit
    FROM scm_trade_shipment ts
    JOIN scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Average shipment size
    RETURN QUERY
    SELECT 
        'avg_shipment_size'::VARCHAR(50) as metric_name,
        COALESCE(AVG(shipment_totals.total_qty), 0)::NUMERIC as metric_value,
        'units'::VARCHAR(20) as metric_unit
    FROM (
        SELECT ts.shipment_id, SUM(tsi.shipped_qty) as total_qty
        FROM scm_trade_shipment ts
        JOIN scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
        WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
          AND (p_client_id IS NULL OR ts.client_id = p_client_id)
          AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
          AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to)
        GROUP BY ts.shipment_id
    ) shipment_totals;
    
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- Comments for Documentation
-- ============================================================================

COMMENT ON TABLE scm_trade_shipment IS 'Trade shipments for B2B outbound sales (cnf → distributor)';
COMMENT ON TABLE scm_trade_shipment_item IS 'Items in trade shipments with batch and quantity details';

COMMENT ON COLUMN scm_trade_shipment.order_id IS 'Reference to client order (PO from distributor)';
COMMENT ON COLUMN scm_trade_shipment.supplier_id IS 'cnf merchant ID (seller)';
COMMENT ON COLUMN scm_trade_shipment.client_id IS 'distributor merchant ID (buyer)';
COMMENT ON COLUMN scm_trade_shipment.status IS 'Shipment status: draft, shipped, closed';

COMMENT ON COLUMN scm_trade_shipment_item.po_item_id IS 'Reference to original PO item';
COMMENT ON COLUMN scm_trade_shipment_item.shipped_qty IS 'Quantity shipped in this shipment';
COMMENT ON COLUMN scm_trade_shipment_item.balance_qty IS 'Remaining quantity to be shipped';

-- ============================================================================
-- Grant Permissions (Adjust as needed for your environment)
-- ============================================================================

-- Grant permissions to application user (replace 'scm_app_user' with actual username)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON scm_trade_shipment TO scm_app_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON scm_trade_shipment_item TO scm_app_user;
-- GRANT SELECT ON v_trade_sales_summary TO scm_app_user;
-- GRANT SELECT ON v_order_execution_status TO scm_app_user;
-- GRANT EXECUTE ON FUNCTION get_trade_sales_analytics TO scm_app_user;

-- ============================================================================
-- Migration Complete
-- ============================================================================

-- Verify tables were created
SELECT 
    table_name, 
    table_type
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_name IN ('scm_trade_shipment', 'scm_trade_shipment_item')
ORDER BY table_name;