-- Migration: Fix inventory trigger to only update product_quantity
-- Date: 2025-11-23
-- Description: Removes inventory table updates from trigger since inventory_txn is just a log
--              and product_quantity holds the aggregate quantities

CREATE OR REPLACE FUNCTION update_inventory_from_txn()
RETURNS TRIGGER AS $$
DECLARE
    v_batch_id uuid;
    v_batch_number text;
    v_unit_type text;
BEGIN
    -- Only process if transaction is not deleted
    IF NEW.isDeleted THEN
        RETURN NEW;
    END IF;
    
    -- Extract unit_type (default to 'PIECE' if not specified)
    v_unit_type := COALESCE(NEW.unit_type, 'PIECE');
    
    -- Extract batch number from the new batch_number column or data JSONB
    v_batch_number := COALESCE(
        NEW.batch_number,
        CASE WHEN NEW.data IS NOT NULL AND NEW.data ? 'batch_number' 
             THEN NEW.data->>'batch_number' 
             ELSE 'DEFAULT' 
        END
    );
    
    -- Find or create product_batch_number record
    SELECT product_batch_number_id INTO v_batch_id
    FROM product_batch_number
    WHERE product_id = NEW.product_id
      AND batch_number = v_batch_number
      AND isDeleted = false;
    
    -- If batch doesn't exist, create it
    IF v_batch_id IS NULL THEN
        INSERT INTO product_batch_number (
            product_id,
            batch_number,
            expiry_date,
            isDeleted,
            updatedAt
        ) VALUES (
            NEW.product_id,
            v_batch_number,
            CASE 
                WHEN NEW.data IS NOT NULL AND NEW.data ? 'expiry_date' 
                THEN (NEW.data->>'expiry_date')::date
                ELSE NULL
            END,
            false,
            now()
        ) RETURNING product_batch_number_id INTO v_batch_id;
        
        RAISE NOTICE 'Created new batch %: product_id=%, batch_number=%', 
            v_batch_id, NEW.product_id, v_batch_number;
    END IF;
    
    -- Update/Insert PRODUCT_QUANTITY record (aggregate by product/branch)
    -- inventory_txn is just a log, product_quantity holds the actual quantities
    INSERT INTO product_quantity (
        product_id,
        branch_id,
        org_id,
        quantity,
        isDeleted,
        updatedAt
    ) VALUES (
        NEW.product_id,
        NEW.branch_id,
        NEW.org_id,
        NEW.change,
        false,
        now()
    )
    ON CONFLICT ON CONSTRAINT uq_product_quantity_product_branch_org
    DO UPDATE SET
        quantity = product_quantity.quantity + EXCLUDED.quantity,
        updatedAt = now();
    
    RAISE NOTICE 'Updated product_quantity: product_id=%, branch_id=%, change=%', 
        NEW.product_id, NEW.branch_id, NEW.change;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
