-- Stock Management Stored Procedures
-- Centralized inventory mutation logic in PostgreSQL

-- Simplified stock movement function with idempotency
CREATE OR REPLACE FUNCTION apply_stock_movement(
    p_merchant_id TEXT,
    p_warehouse_id TEXT,
    p_sku TEXT,
    p_batch_no TEXT,
    p_qty NUMERIC,
    p_txn_type TEXT,
    p_ref_type TEXT,
    p_ref_id UUID,
    p_ref_no TEXT,
    p_user TEXT
) RETURNS VOID AS $$
BEGIN
    -- Idempotency check - prevent duplicate processing
    IF EXISTS (
        SELECT 1 FROM scm_stock_ledger 
        WHERE ref_type = p_ref_type AND ref_id = p_ref_id AND sku = p_sku AND batch_no = p_batch_no
    ) THEN
        RETURN;
    END IF;
    
    -- Insert ledger entry (immutable audit trail)
    INSERT INTO scm_stock_ledger (
        ledger_id, merchant_id, warehouse_id, sku, batch_no,
        qty, txn_type, ref_type, ref_id, ref_no, created_by
    ) VALUES (
        gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
        p_qty, p_txn_type, p_ref_type, p_ref_id, p_ref_no, p_user
    );
    
    -- Stock snapshot upsert (create or update)
    INSERT INTO scm_stock (
        stock_id, merchant_id, warehouse_id, sku, batch_no,
        qty_on_hand, qty_reserved, qty_available
    ) VALUES (
        gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
        p_qty, 0, p_qty
    )
    ON CONFLICT (merchant_id, warehouse_id, sku, batch_no)
    DO UPDATE SET
        qty_on_hand = scm_stock.qty_on_hand + p_qty,
        qty_available = scm_stock.qty_available + p_qty,
        last_updated_at = NOW();
END;
$$ LANGUAGE plpgsql;

-- Function to process multiple stock movements atomically (for GRN completion)
CREATE OR REPLACE FUNCTION apply_bulk_stock_movements(
    p_movements JSONB
) RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_movement JSONB;
    v_ledger_id UUID;
    v_results JSONB := '[]'::JSONB;
    v_result JSONB;
BEGIN
    -- Process each movement in the array
    FOR v_movement IN SELECT * FROM jsonb_array_elements(p_movements)
    LOOP
        -- Apply individual stock movement
        SELECT apply_stock_movement(
            (v_movement->>'merchant_id')::VARCHAR(64),
            (v_movement->>'warehouse_id')::VARCHAR(64),
            (v_movement->>'catalogue_id')::UUID,
            (v_movement->>'sku')::VARCHAR(64),
            (v_movement->>'batch_no')::VARCHAR(50),
            (v_movement->>'exp_dt')::DATE,
            (v_movement->>'qty')::NUMERIC(14,3),
            (v_movement->>'uom')::VARCHAR(10),
            (v_movement->>'txn_type')::VARCHAR(30),
            (v_movement->>'ref_type')::VARCHAR(30),
            (v_movement->>'ref_id')::UUID,
            (v_movement->>'ref_no')::VARCHAR(50),
            (v_movement->>'remarks')::TEXT,
            (v_movement->>'created_by')::VARCHAR(64)
        ) INTO v_ledger_id;
        
        -- Build result object
        v_result := jsonb_build_object(
            'ledger_id', v_ledger_id,
            'sku', v_movement->>'sku',
            'qty', v_movement->>'qty',
            'success', true
        );
        
        -- Add to results array
        v_results := v_results || v_result;
    END LOOP;
    
    RETURN v_results;
END;
$$;

-- Function to reserve stock (for sales orders)
CREATE OR REPLACE FUNCTION reserve_stock(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64),
    p_sku VARCHAR(64),
    p_batch_no VARCHAR(50),
    p_qty NUMERIC(14,3),
    p_ref_id UUID,
    p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_available_qty NUMERIC(14,3);
BEGIN
    -- Get current available quantity
    SELECT qty_available
    INTO v_available_qty
    FROM scm_stock
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    -- Check if sufficient stock available
    IF v_available_qty IS NULL OR v_available_qty < p_qty THEN
        RAISE EXCEPTION 'Insufficient available stock for SKU: %. Available: %, Required: %', 
            p_sku, COALESCE(v_available_qty, 0), p_qty;
    END IF;
    
    -- Update stock reservation
    UPDATE scm_stock
    SET qty_reserved = qty_reserved + p_qty,
        qty_available = qty_available - p_qty,
        last_updated_at = NOW()
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    RETURN TRUE;
END;
$$;

-- Function to release stock reservation
CREATE OR REPLACE FUNCTION release_stock_reservation(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64),
    p_sku VARCHAR(64),
    p_batch_no VARCHAR(50),
    p_qty NUMERIC(14,3),
    p_ref_id UUID,
    p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Update stock reservation
    UPDATE scm_stock
    SET qty_reserved = GREATEST(qty_reserved - p_qty, 0),
        qty_available = qty_on_hand - GREATEST(qty_reserved - p_qty, 0),
        last_updated_at = NOW()
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    RETURN TRUE;
END;
$$;

-- Function to get current stock summary
CREATE OR REPLACE FUNCTION get_stock_summary(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64) DEFAULT NULL,
    p_sku VARCHAR(64) DEFAULT NULL
) RETURNS TABLE (
    stock_id UUID,
    warehouse_id VARCHAR(64),
    catalogue_id UUID,
    sku VARCHAR(64),
    batch_no VARCHAR(50),
    qty_on_hand NUMERIC(14,3),
    qty_reserved NUMERIC(14,3),
    qty_available NUMERIC(14,3),
    uom VARCHAR(10),
    last_updated_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT s.stock_id, s.warehouse_id, s.catalogue_id, s.sku, s.batch_no,
           s.qty_on_hand, s.qty_reserved, s.qty_available, s.uom, s.last_updated_at
    FROM scm_stock s
    WHERE s.merchant_id = p_merchant_id
      AND (p_warehouse_id IS NULL OR s.warehouse_id = p_warehouse_id)
      AND (p_sku IS NULL OR s.sku = p_sku)
    ORDER BY s.sku, s.batch_no;
END;
$$;

-- Function to get stock ledger history
CREATE OR REPLACE FUNCTION get_stock_ledger(
    p_merchant_id VARCHAR(64),
    p_sku VARCHAR(64) DEFAULT NULL,
    p_batch_no VARCHAR(50) DEFAULT NULL,
    p_limit INTEGER DEFAULT 100
) RETURNS TABLE (
    ledger_id UUID,
    warehouse_id VARCHAR(64),
    catalogue_id UUID,
    sku VARCHAR(64),
    batch_no VARCHAR(50),
    txn_type VARCHAR(30),
    qty NUMERIC(14,3),
    uom VARCHAR(10),
    ref_type VARCHAR(30),
    ref_id UUID,
    ref_no VARCHAR(50),
    remarks TEXT,
    created_by VARCHAR(64),
    created_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT l.ledger_id, l.warehouse_id, l.catalogue_id, l.sku, l.batch_no,
           l.txn_type, l.qty, l.uom, l.ref_type, l.ref_id, l.ref_no,
           l.remarks, l.created_by, l.created_at
    FROM scm_stock_ledger l
    WHERE l.merchant_id = p_merchant_id
      AND (p_sku IS NULL OR l.sku = p_sku)
      AND (p_batch_no IS NULL OR l.batch_no = p_batch_no)
    ORDER BY l.created_at DESC
    LIMIT p_limit;
END;
$$;