-- ============================================================================
-- INVENTORY MULTI-UNIT SYSTEM SETUP
-- ============================================================================
-- This file consolidates all multi-unit product support and bulk breaking
-- functionality for inventory management. Run this after TAFDB.pgsql.
--
-- Components:
--   1. Multi-unit conversion functions (5 functions)
--   2. Enhanced inventory transfer trigger with-- ============================================================================
-- PART 3: Inventory Quantity Sync Trigger
-- ============================================================================
-- Automatically updates inventory and product_quantity tables when
-- inventory_txn records are created

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 INVENTORY record (specific batch at branch)
    -- First try to update existing record
    UPDATE inventory
    SET 
        current_qty = current_qty + NEW.change,
        updatedAt = now(),
        reference_id = COALESCE(NEW.reference_id, reference_id),
        reference_table = COALESCE(NEW.reference_table, reference_table)
    WHERE product_batch_number_id = v_batch_id
      AND branch_id = NEW.branch_id
      AND COALESCE(org_id, NEW.org_id) = NEW.org_id
      AND isDeleted = false;
    
    -- If no rows were updated, insert a new record
    IF NOT FOUND THEN
        INSERT INTO inventory (
            product_batch_number_id,
            branch_id,
            current_qty,
            unit_type,
            org_id,
            reference_id,
            reference_table,
            isDeleted,
            updatedAt
        ) VALUES (
            v_batch_id,
            NEW.branch_id,
            NEW.change,
            v_unit_type,
            NEW.org_id,
            NEW.reference_id,
            NEW.reference_table,
            false,
            now()
        );
        
        RAISE NOTICE 'Created inventory record: product_id=%, batch_id=%, branch_id=%, qty=%', 
            NEW.product_id, v_batch_id, NEW.branch_id, NEW.change;
    ELSE
        RAISE NOTICE 'Updated inventory: product_id=%, batch_id=%, branch_id=%, change=%', 
            NEW.product_id, v_batch_id, NEW.branch_id, NEW.change;
    END IF;
    
    -- Update/Insert PRODUCT_QUANTITY record (aggregate by product/branch)
    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 + NEW.change,
        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;

COMMENT ON FUNCTION update_inventory_from_txn() IS 'Automatically updates inventory and product_quantity tables when inventory_txn records are created. Maintains batch-level inventory and product-level aggregates.';

-- Drop trigger if it exists
DROP TRIGGER IF EXISTS trg_update_inventory_from_txn ON inventory_txn;

-- Create trigger that fires AFTER INSERT on inventory_txn
CREATE TRIGGER trg_update_inventory_from_txn
    AFTER INSERT ON inventory_txn
    FOR EACH ROW
    EXECUTE FUNCTION update_inventory_from_txn();

COMMENT ON TRIGGER trg_update_inventory_from_txn ON inventory_txn IS 'Automatically updates inventory and product_quantity tables when new transactions are created';


-- ============================================================================
-- PART 4: Indexes for Performance
-- ============================================================================

-- Indexes for batch tracking
CREATE INDEX IF NOT EXISTS idx_inventory_txn_batch 
ON inventory_txn(batch_number) 
WHERE batch_number IS NOT NULL AND isdeleted = false;

-- Indexes for notes search (full-text)
CREATE INDEX IF NOT EXISTS idx_inventory_txn_notes_search 
ON inventory_txn USING gin(to_tsvector('english', notes)) 
WHERE notes IS NOT NULL AND isdeleted = false;


-- ============================================================================
-- DEPLOYMENT SUCCESS MESSAGE
-- ============================================================================  3. Indexes for batch tracking and notes search
--
-- Created: 2025-10-23
-- Dependencies: TAFDB.pgsql (products, inventory_txn, inventory_transfers tables)
-- ============================================================================

-- ============================================================================
-- PART 1: Multi-Unit Product Support Functions
-- ============================================================================

-- Function 1: Get Product Unit Hierarchy
-- Returns the complete unit configuration for a product
CREATE OR REPLACE FUNCTION get_product_unit_hierarchy(p_product_id UUID)
RETURNS TABLE (
    base_unit VARCHAR(50),
    master_unit VARCHAR(50),
    units_per_master INTEGER,
    master_unit_2 VARCHAR(50),
    units_per_master_2 INTEGER,
    hierarchy_depth INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.base_unit,
        p.master_unit,
        p.units_per_master,
        p.master_unit_2,
        p.units_per_master_2,
        CASE 
            WHEN p.master_unit_2 IS NOT NULL THEN 3
            WHEN p.master_unit IS NOT NULL THEN 2
            ELSE 1
        END as hierarchy_depth
    FROM products p
    WHERE p.product_id = p_product_id;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION get_product_unit_hierarchy(UUID) IS 'Returns complete unit configuration for a product including hierarchy depth';


-- Function 2: Convert Product Quantity Between Units
-- Converts quantity from one unit to another using product configuration
CREATE OR REPLACE FUNCTION convert_product_quantity(
    p_product_id UUID,
    p_quantity NUMERIC,
    p_from_unit VARCHAR(50),
    p_to_unit VARCHAR(50)
) RETURNS NUMERIC AS $$
DECLARE
    v_base_unit VARCHAR(50);
    v_master_unit VARCHAR(50);
    v_units_per_master INTEGER;
    v_master_unit_2 VARCHAR(50);
    v_units_per_master_2 INTEGER;
    v_quantity_in_base_units NUMERIC;
    v_result NUMERIC;
BEGIN
    -- Get product unit configuration
    SELECT base_unit, master_unit, units_per_master, master_unit_2, units_per_master_2
    INTO v_base_unit, v_master_unit, v_units_per_master, v_master_unit_2, v_units_per_master_2
    FROM products
    WHERE product_id = p_product_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % not found', p_product_id;
    END IF;
    
    -- If from and to units are the same, return original quantity
    IF p_from_unit = p_to_unit THEN
        RETURN p_quantity;
    END IF;
    
    -- Step 1: Convert from_unit to base units
    IF p_from_unit = v_base_unit THEN
        v_quantity_in_base_units := p_quantity;
    ELSIF p_from_unit = v_master_unit AND v_master_unit IS NOT NULL THEN
        v_quantity_in_base_units := p_quantity * v_units_per_master;
    ELSIF p_from_unit = v_master_unit_2 AND v_master_unit_2 IS NOT NULL THEN
        -- master_unit_2 to base: multiply by both conversion factors
        v_quantity_in_base_units := p_quantity * v_units_per_master_2 * v_units_per_master;
    ELSE
        RAISE EXCEPTION 'Invalid from_unit "%" for product %. Valid units are: %, %, %',
            p_from_unit, p_product_id, v_base_unit, v_master_unit, v_master_unit_2;
    END IF;
    
    -- Step 2: Convert from base units to to_unit
    IF p_to_unit = v_base_unit THEN
        v_result := v_quantity_in_base_units;
    ELSIF p_to_unit = v_master_unit AND v_master_unit IS NOT NULL THEN
        v_result := v_quantity_in_base_units / v_units_per_master;
    ELSIF p_to_unit = v_master_unit_2 AND v_master_unit_2 IS NOT NULL THEN
        -- base to master_unit_2: divide by both conversion factors
        v_result := v_quantity_in_base_units / (v_units_per_master_2 * v_units_per_master);
    ELSE
        RAISE EXCEPTION 'Invalid to_unit "%" for product %. Valid units are: %, %, %',
            p_to_unit, p_product_id, v_base_unit, v_master_unit, v_master_unit_2;
    END IF;
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION convert_product_quantity(UUID, NUMERIC, VARCHAR, VARCHAR) IS 'Converts quantity between any configured units for a product. Handles 3-level hierarchies (base → master → master_2).';


-- Function 3: Validate Unit for Product
-- Checks if a given unit is valid for a product
CREATE OR REPLACE FUNCTION is_valid_unit_for_product(
    p_product_id UUID,
    p_unit VARCHAR(50)
) RETURNS BOOLEAN AS $$
DECLARE
    v_base_unit VARCHAR(50);
    v_master_unit VARCHAR(50);
    v_master_unit_2 VARCHAR(50);
BEGIN
    SELECT base_unit, master_unit, master_unit_2
    INTO v_base_unit, v_master_unit, v_master_unit_2
    FROM products
    WHERE product_id = p_product_id;
    
    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;
    
    RETURN p_unit IN (v_base_unit, v_master_unit, v_master_unit_2);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION is_valid_unit_for_product(UUID, VARCHAR) IS 'Validates if a unit is configured for a product';


-- Function 4: Get Quantity in Base Units
-- Normalizes any quantity to base units for reporting
CREATE OR REPLACE FUNCTION get_quantity_in_base_units(
    p_product_id UUID,
    p_quantity NUMERIC,
    p_unit VARCHAR(50)
) RETURNS NUMERIC AS $$
DECLARE
    v_base_unit VARCHAR(50);
BEGIN
    SELECT base_unit INTO v_base_unit FROM products WHERE product_id = p_product_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % not found', p_product_id;
    END IF;
    
    RETURN convert_product_quantity(p_product_id, p_quantity, p_unit, v_base_unit);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION get_quantity_in_base_units(UUID, NUMERIC, VARCHAR) IS 'Normalizes quantity to base units for standardized reporting';


-- Function 5: Get Available Units for Product (for UI dropdowns)
-- Returns JSONB array of available units
CREATE OR REPLACE FUNCTION get_product_available_units(p_product_id UUID)
RETURNS JSONB AS $$
DECLARE
    v_base_unit VARCHAR(50);
    v_master_unit VARCHAR(50);
    v_master_unit_2 VARCHAR(50);
    v_units JSONB := '[]'::jsonb;
BEGIN
    SELECT base_unit, master_unit, master_unit_2
    INTO v_base_unit, v_master_unit, v_master_unit_2
    FROM products
    WHERE product_id = p_product_id;
    
    IF NOT FOUND THEN
        RETURN '[]'::jsonb;
    END IF;
    
    -- Add base unit
    v_units := v_units || jsonb_build_object('label', v_base_unit, 'value', v_base_unit);
    
    -- Add master unit if configured
    IF v_master_unit IS NOT NULL THEN
        v_units := v_units || jsonb_build_object('label', v_master_unit, 'value', v_master_unit);
    END IF;
    
    -- Add master_unit_2 if configured
    IF v_master_unit_2 IS NOT NULL THEN
        v_units := v_units || jsonb_build_object('label', v_master_unit_2, 'value', v_master_unit_2);
    END IF;
    
    RETURN v_units;
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION get_product_available_units(UUID) IS 'Returns JSONB array of available units for product (for UI dropdowns)';


-- ============================================================================
-- PART 2: Enhanced Inventory Transfer Trigger with Bulk Breaking
-- ============================================================================

CREATE OR REPLACE FUNCTION process_inventory_transfer()
RETURNS TRIGGER AS $$
DECLARE
    v_item jsonb;
    v_product_uuid uuid;
    v_from_branch_name text;
    v_to_branch_name text;
    v_txn_notes text;
    v_debit_txn_id uuid;
    v_credit_txn_id uuid;
    v_item_count integer := 0;
    
    -- Bulk breaking variables
    v_break_bulk boolean;
    v_send_unit varchar(50);
    v_receive_unit varchar(50);
    v_send_quantity numeric;
    v_receive_quantity numeric;
    v_conversion_valid boolean;
    v_conversion_error text;
BEGIN
    -- Only process when status changes to 'completed'
    IF NEW.status = 'completed' AND (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND COALESCE(OLD.status, '') != 'completed')) THEN
        
        -- Get branch names for better audit trail
        SELECT name INTO v_from_branch_name FROM branches WHERE branch_id = NEW.from_branch_id;
        SELECT name INTO v_to_branch_name FROM branches WHERE branch_id = NEW.to_branch_id;
        
        -- Build base transaction notes
        v_txn_notes := format('Transfer %s: %s → %s', 
            COALESCE(NEW.reference_number, NEW.inventory_transfer_id::text),
            COALESCE(v_from_branch_name, 'Unknown'),
            COALESCE(v_to_branch_name, 'Unknown')
        );
        
        -- Validate items array is not empty
        IF jsonb_array_length(NEW.items) = 0 THEN
            RAISE EXCEPTION 'Cannot complete transfer % - no items specified', NEW.inventory_transfer_id;
        END IF;
        
        -- Loop through each item in the items JSONB array
        FOR v_item IN SELECT * FROM jsonb_array_elements(NEW.items)
        LOOP
            -- Extract and validate product_id
            BEGIN
                v_product_uuid := (v_item->>'product_id')::uuid;
            EXCEPTION WHEN OTHERS THEN
                RAISE EXCEPTION 'Invalid product_id in transfer item: %', v_item->>'product_id';
            END;
            
            -- Extract bulk breaking configuration
            v_break_bulk := COALESCE((v_item->>'break_bulk')::boolean, false);
            v_send_unit := COALESCE(v_item->>'unit_type', 'PIECE');
            v_send_quantity := (v_item->>'quantity')::numeric;
            
            -- Validate quantity
            IF v_send_quantity <= 0 THEN
                RAISE EXCEPTION 'Invalid quantity % for product % in transfer %', 
                    v_send_quantity, v_product_uuid, NEW.inventory_transfer_id;
            END IF;
            
            -- ================================================================
            -- HANDLE BULK BREAKING (if enabled)
            -- ================================================================
            IF v_break_bulk THEN
                -- Get the receive unit from item configuration
                v_receive_unit := v_item->>'break_to_unit';
                
                -- Validate break_to_unit is specified
                IF v_receive_unit IS NULL OR v_receive_unit = '' THEN
                    RAISE EXCEPTION 'Bulk breaking enabled but break_to_unit not specified for product % in transfer %',
                        v_product_uuid, NEW.inventory_transfer_id;
                END IF;
                
                -- Validate units are different (no point breaking to same unit)
                IF v_send_unit = v_receive_unit THEN
                    RAISE WARNING 'Bulk breaking enabled but send and receive units are identical (%) for product % - treating as regular transfer',
                        v_send_unit, v_product_uuid;
                    v_break_bulk := false;
                    v_receive_unit := v_send_unit;
                    v_receive_quantity := v_send_quantity;
                ELSE
                    -- Validate the receive unit is valid for this product
                    SELECT is_valid_unit_for_product(v_product_uuid, v_receive_unit) INTO v_conversion_valid;
                    
                    IF NOT v_conversion_valid THEN
                        RAISE EXCEPTION 'Invalid break_to_unit "%" for product % in transfer %. Unit not configured for this product.',
                            v_receive_unit, v_product_uuid, NEW.inventory_transfer_id;
                    END IF;
                    
                    -- Validate the send unit is valid for this product
                    SELECT is_valid_unit_for_product(v_product_uuid, v_send_unit) INTO v_conversion_valid;
                    
                    IF NOT v_conversion_valid THEN
                        RAISE EXCEPTION 'Invalid unit_type "%" for product % in transfer %. Unit not configured for this product.',
                            v_send_unit, v_product_uuid, NEW.inventory_transfer_id;
                    END IF;
                    
                    -- Convert quantity from send unit to receive unit
                    BEGIN
                        SELECT convert_product_quantity(
                            v_product_uuid,
                            v_send_quantity,
                            v_send_unit,
                            v_receive_unit
                        ) INTO v_receive_quantity;
                        
                        -- Log the conversion
                        RAISE NOTICE 'Bulk breaking: % % → % % (product: %)',
                            v_send_quantity, v_send_unit,
                            v_receive_quantity, v_receive_unit,
                            v_product_uuid;
                            
                    EXCEPTION WHEN OTHERS THEN
                        GET STACKED DIAGNOSTICS v_conversion_error = MESSAGE_TEXT;
                        RAISE EXCEPTION 'Failed to convert % % to % for product % in transfer %: %',
                            v_send_quantity, v_send_unit, v_receive_unit,
                            v_product_uuid, NEW.inventory_transfer_id, v_conversion_error;
                    END;
                    
                    -- Update transaction notes to include bulk breaking info
                    v_txn_notes := v_txn_notes || format(
                        E'\nBulk Breaking: %s %s → %s %s',
                        v_send_quantity, v_send_unit,
                        v_receive_quantity, v_receive_unit
                    );
                END IF;
            ELSE
                -- Regular transfer (no bulk breaking)
                v_receive_unit := v_send_unit;
                v_receive_quantity := v_send_quantity;
            END IF;
            
            -- ================================================================
            -- Create DECREASE transaction at source branch (stock out)
            -- ================================================================
            INSERT INTO inventory_txn (
                inventory_txn_id,
                product_id,
                branch_id,
                change,
                reason,
                unit_type,
                reference_id,
                reference_table,
                batch_number,
                notes,
                org_id,
                user_id,
                processed_at,
                isdeleted,
                updatedat
            ) VALUES (
                uuid_generate_v7(),
                v_product_uuid,
                NEW.from_branch_id,
                -ABS(v_send_quantity),  -- Always negative for decrease, using SEND unit quantity
                'transfer_out',
                v_send_unit,  -- Record transaction in the SEND unit
                NEW.inventory_transfer_id,
                'inventory_transfers',
                v_item->>'batch_number',
                COALESCE(v_item->>'notes' || E'\n' || v_txn_notes, v_txn_notes),
                NEW.org_id,
                NEW.user_id,
                NEW.transfer_date,
                false,
                now()
            ) RETURNING inventory_txn_id INTO v_debit_txn_id;
            
            -- ================================================================
            -- Create INCREASE transaction at destination branch (stock in)
            -- ================================================================
            INSERT INTO inventory_txn (
                inventory_txn_id,
                product_id,
                branch_id,
                change,
                reason,
                unit_type,
                reference_id,
                reference_table,
                batch_number,
                notes,
                org_id,
                user_id,
                processed_at,
                isdeleted,
                updatedat
            ) VALUES (
                uuid_generate_v7(),
                v_product_uuid,
                NEW.to_branch_id,
                ABS(v_receive_quantity),  -- Always positive for increase, using RECEIVE unit quantity
                'transfer_in',
                v_receive_unit,  -- Record transaction in the RECEIVE unit (may differ if bulk breaking)
                NEW.inventory_transfer_id,
                'inventory_transfers',
                v_item->>'batch_number',
                COALESCE(v_item->>'notes' || E'\n' || v_txn_notes, v_txn_notes),
                NEW.org_id,
                NEW.user_id,
                NEW.transfer_date,
                false,
                now()
            ) RETURNING inventory_txn_id INTO v_credit_txn_id;
            
            v_item_count := v_item_count + 1;
            
            -- Log successful transaction pair creation
            IF v_break_bulk THEN
                RAISE NOTICE 'Transfer % (BULK BREAK): Created txn pair [OUT: % (%), IN: % (%)] for product %',
                    NEW.inventory_transfer_id, 
                    v_debit_txn_id, format('%s %s', v_send_quantity, v_send_unit),
                    v_credit_txn_id, format('%s %s', v_receive_quantity, v_receive_unit),
                    v_product_uuid;
            ELSE
                RAISE NOTICE 'Transfer %: Created txn pair [OUT: %, IN: %] for product % (qty: % %)',
                    NEW.inventory_transfer_id, v_debit_txn_id, v_credit_txn_id, 
                    v_product_uuid, v_send_quantity, v_send_unit;
            END IF;
            
        END LOOP;
        
        -- Log completion
        RAISE NOTICE 'Transfer % completed: % items processed, % transactions created',
            NEW.inventory_transfer_id, v_item_count, v_item_count * 2;
        
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION process_inventory_transfer() IS 'Auto-creates paired inventory_txn records (decrease at source, increase at destination) when transfer status becomes completed. Supports bulk breaking: sends in one unit, receives in another (e.g., send 2 CARTON, receive 80 PIECE). Uses convert_product_quantity() for accurate conversions.';


-- ============================================================================
-- STEP 3: Attach trigger to inventory_transfers table
-- ============================================================================

-- Drop existing trigger if it exists
DROP TRIGGER IF EXISTS trg_process_inventory_transfer ON inventory_transfers;

-- Create trigger that fires AFTER INSERT OR UPDATE
CREATE TRIGGER trg_process_inventory_transfer
    AFTER INSERT OR UPDATE OF status, items
    ON inventory_transfers
    FOR EACH ROW
    EXECUTE FUNCTION process_inventory_transfer();

COMMENT ON TRIGGER trg_process_inventory_transfer ON inventory_transfers IS 'Processes completed inventory transfers by creating matching debit/credit inventory_txn records with automatic bulk breaking support';


-- ============================================================================
-- PART 3: Indexes for Performance
-- ============================================================================

-- Indexes for batch tracking
CREATE INDEX IF NOT EXISTS idx_inventory_txn_batch 
ON inventory_txn(batch_number) 
WHERE batch_number IS NOT NULL AND isdeleted = false;

-- Indexes for notes search (full-text)
CREATE INDEX IF NOT EXISTS idx_inventory_txn_notes_search 
ON inventory_txn USING gin(to_tsvector('english', notes)) 
WHERE notes IS NOT NULL AND isdeleted = false;


-- ============================================================================
-- PART 4: Inventory Quantity Sync Triggers
-- ============================================================================
-- Purpose: Automatically update inventory and product_quantity tables when
--          inventory_txn records are created
--
-- Tables Updated:
--   - inventory: Updates current_qty for specific product batch at a branch
--   - product_quantity: Updates total quantity for a product at a branch (across all batches)
-- ============================================================================

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 or generate batch number from batch_number column or data JSONB
    IF NEW.batch_number IS NOT NULL THEN
        v_batch_number := NEW.batch_number;
    ELSIF NEW.data IS NOT NULL AND NEW.data ? 'batch_number' THEN
        v_batch_number := NEW.data->>'batch_number';
    ELSE
        v_batch_number := 'DEFAULT';
    END IF;
    
    -- 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;
    END IF;
    
    -- Update/Insert INVENTORY record (specific batch at branch)
    UPDATE inventory
    SET 
        current_qty = current_qty + NEW.change,
        updatedat = now(),
        reference_id = COALESCE(NEW.reference_id, reference_id),
        reference_table = COALESCE(NEW.reference_table, reference_table)
    WHERE product_batch_number_id = v_batch_id
      AND branch_id = NEW.branch_id
      AND org_id = NEW.org_id
      AND isdeleted = false;
    
    -- If no rows were updated, insert a new record
    IF NOT FOUND THEN
        INSERT INTO inventory (
            product_batch_number_id,
            branch_id,
            current_qty,
            unit_type,
            org_id,
            reference_id,
            reference_table,
            isdeleted,
            updatedat
        ) VALUES (
            v_batch_id,
            NEW.branch_id,
            NEW.change,
            v_unit_type,
            NEW.org_id,
            NEW.reference_id,
            NEW.reference_table,
            false,
            now()
        );
    END IF;
    
    -- Update/Insert PRODUCT_QUANTITY record (aggregate by product/branch)
    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 + NEW.change,
        updatedat = now();
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION update_inventory_from_txn() IS 'Automatically updates inventory and product_quantity tables when inventory_txn records are created. Maintains batch-level inventory and product-level aggregates.';

-- Drop trigger if it exists
DROP TRIGGER IF EXISTS trg_update_inventory_from_txn ON inventory_txn;

-- Create trigger that fires AFTER INSERT on inventory_txn
CREATE TRIGGER trg_update_inventory_from_txn
    AFTER INSERT ON inventory_txn
    FOR EACH ROW
    EXECUTE FUNCTION update_inventory_from_txn();

COMMENT ON TRIGGER trg_update_inventory_from_txn ON inventory_txn IS 'Automatically updates inventory and product_quantity tables when new transactions are created';


-- ============================================================================
-- DEPLOYMENT SUCCESS MESSAGE
-- ============================================================================

DO $$
BEGIN
    RAISE NOTICE '';
    RAISE NOTICE '==========================================================================';
    RAISE NOTICE 'COMPLETE INVENTORY SYSTEM DEPLOYED SUCCESSFULLY';
    RAISE NOTICE '==========================================================================';
    RAISE NOTICE 'Components installed:';
    RAISE NOTICE '  ✓ 5 multi-unit conversion functions';
    RAISE NOTICE '  ✓ Enhanced inventory transfer trigger with bulk breaking';
    RAISE NOTICE '  ✓ Inventory quantity sync trigger (updates inventory tables)';
    RAISE NOTICE '  ✓ Performance indexes for batch tracking and notes search';
    RAISE NOTICE '';
    RAISE NOTICE 'Features:';
    RAISE NOTICE '  • 3-level unit hierarchy support (Base → Master → Master_2)';
    RAISE NOTICE '  • Automatic unit conversion during transfers';
    RAISE NOTICE '  • Bulk breaking: send in one unit, receive in another';
    RAISE NOTICE '  • Auto-update inventory and product_quantity on every transaction';
    RAISE NOTICE '  • Complete audit trail with batch tracking';
    RAISE NOTICE '  • Full validation and error handling';
    RAISE NOTICE '';
    RAISE NOTICE 'Example: Transfer 2 CARTON, automatically receive as 80 PIECE';
    RAISE NOTICE '         Inventory levels update automatically!';
    RAISE NOTICE '==========================================================================';
    RAISE NOTICE '';
END $$;
