-- ============================================================================
-- MIGRATION: Inventory Transfer System with Auto-Transaction Triggers
-- ============================================================================
-- Created: 2025-10-22
-- Description: 
--   1. Creates inventory_transfers table for tracking stock movements between branches
--   2. Adds database trigger that automatically creates inventory_txn records
--      (decrease at source, increase at destination) when transfer is completed
--   3. Ensures data consistency and maintains complete audit trail
-- ============================================================================

-- ============================================================================
-- STEP 1: Create inventory_transfers table
-- ============================================================================

CREATE TABLE IF NOT EXISTS inventory_transfers (
    inventory_transfer_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    from_branch_id uuid REFERENCES branches(branch_id) ON DELETE RESTRICT,
    to_branch_id uuid REFERENCES branches(branch_id) ON DELETE RESTRICT,
    transfer_date date NOT NULL DEFAULT CURRENT_DATE,
    reference_number text,
    items jsonb NOT NULL DEFAULT '[]'::jsonb,
    notes text,
    status varchar(50) NOT NULL DEFAULT 'pending',
    org_id uuid REFERENCES orgs(org_id) ON DELETE CASCADE,
    user_id uuid REFERENCES users(user_id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    isdeleted boolean NOT NULL DEFAULT false,
    updatedat timestamptz NOT NULL DEFAULT now(),
    
    -- Constraints
    CONSTRAINT chk_different_branches CHECK (from_branch_id != to_branch_id),
    CONSTRAINT chk_valid_status CHECK (status IN ('pending', 'in_transit', 'completed', 'cancelled'))
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_inventory_transfers_from_branch ON inventory_transfers(from_branch_id) WHERE isdeleted = false;
CREATE INDEX IF NOT EXISTS idx_inventory_transfers_to_branch ON inventory_transfers(to_branch_id) WHERE isdeleted = false;
CREATE INDEX IF NOT EXISTS idx_inventory_transfers_status ON inventory_transfers(status) WHERE isdeleted = false;
CREATE INDEX IF NOT EXISTS idx_inventory_transfers_org ON inventory_transfers(org_id) WHERE isdeleted = false;
CREATE INDEX IF NOT EXISTS idx_inventory_transfers_date ON inventory_transfers(transfer_date) WHERE isdeleted = false;

-- Add comments
COMMENT ON TABLE inventory_transfers IS 'Tracks inventory transfers between branches/warehouses with automatic transaction generation';
COMMENT ON COLUMN inventory_transfers.items IS 'JSONB array of items: [{"product_id": "uuid", "quantity": 10, "unit_type": "pieces", "batch_number": "B001", "notes": "..."}]';
COMMENT ON COLUMN inventory_transfers.status IS 'Transfer status: pending (draft), in_transit (dispatched), completed (received), cancelled';
COMMENT ON COLUMN inventory_transfers.reference_number IS 'External reference number (e.g., dispatch note number, tracking number)';

-- ============================================================================
-- STEP 2: Create trigger function to auto-generate inventory transactions
-- ============================================================================

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;
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;
            
            -- Validate quantity
            IF (v_item->>'quantity')::numeric <= 0 THEN
                RAISE EXCEPTION 'Invalid quantity % for product % in transfer %', 
                    v_item->>'quantity', v_product_uuid, NEW.inventory_transfer_id;
            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_item->>'quantity')::numeric),  -- Always negative for decrease
                'transfer_out',
                COALESCE(v_item->>'unit_type', 'pieces'),
                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_item->>'quantity')::numeric),  -- Always positive for increase
                'transfer_in',
                COALESCE(v_item->>'unit_type', 'pieces'),
                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
            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_item->>'quantity';
            
        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;

-- ============================================================================
-- 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();

-- Add trigger comments
COMMENT ON FUNCTION process_inventory_transfer() IS 'Auto-creates paired inventory_txn records (decrease at source, increase at destination) when transfer status becomes completed. Ensures atomic stock movement and complete audit trail.';
COMMENT ON TRIGGER trg_process_inventory_transfer ON inventory_transfers IS 'Processes completed inventory transfers by creating matching debit/credit inventory_txn records';

-- ============================================================================
-- STEP 4: Add updatedAt trigger (standard for all tables)
-- ============================================================================

CREATE TRIGGER trg_set_updatedat
    BEFORE UPDATE ON inventory_transfers
    FOR EACH ROW
    EXECUTE FUNCTION set_updatedat();

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Verify table structure
-- SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'inventory_transfers' ORDER BY ordinal_position;

-- Verify indexes
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'inventory_transfers';

-- ============================================================================
-- EXAMPLE USAGE
-- ============================================================================

/*
-- Example 1: Create a pending transfer (no transactions generated yet)
INSERT INTO inventory_transfers (
    from_branch_id, 
    to_branch_id, 
    transfer_date, 
    reference_number, 
    items, 
    status,
    notes,
    org_id, 
    user_id
) VALUES (
    (SELECT branch_id FROM branches WHERE name = 'Main Warehouse' LIMIT 1),
    (SELECT branch_id FROM branches WHERE name = 'Retail Store' LIMIT 1),
    CURRENT_DATE,
    'TRF-2025-001',
    '[
        {
            "product_id": "prod-uuid-here",
            "quantity": 50,
            "unit_type": "pieces",
            "batch_number": "BATCH-001",
            "notes": "Urgent restocking"
        },
        {
            "product_id": "prod-uuid-2",
            "quantity": 10,
            "unit_type": "boxes",
            "batch_number": "BATCH-002",
            "notes": "Regular stock"
        }
    ]'::jsonb,
    'pending',  -- Status is pending, so no transactions created yet
    'Weekly stock transfer',
    (SELECT org_id FROM orgs LIMIT 1),
    (SELECT user_id FROM users LIMIT 1)
);

-- Example 2: Complete the transfer (triggers automatic transaction creation)
UPDATE inventory_transfers 
SET status = 'completed' 
WHERE reference_number = 'TRF-2025-001';

-- Example 3: Verify transactions were created
SELECT 
    it.inventory_txn_id,
    it.reason,
    it.change,
    b.name as branch_name,
    p.name as product_name,
    it.batch_number,
    it.processed_at
FROM inventory_txn it
JOIN branches b ON it.branch_id = b.branch_id
JOIN products p ON it.product_id = p.product_id
WHERE it.reference_table = 'inventory_transfers'
  AND it.reference_id = (SELECT inventory_transfer_id FROM inventory_transfers WHERE reference_number = 'TRF-2025-001')
ORDER BY it.processed_at;

-- Example 4: View transfer summary with transaction count
SELECT 
    t.inventory_transfer_id,
    t.reference_number,
    fb.name as from_branch,
    tb.name as to_branch,
    t.transfer_date,
    t.status,
    jsonb_array_length(t.items) as item_count,
    COUNT(txn.inventory_txn_id) as transaction_count
FROM inventory_transfers t
LEFT JOIN branches fb ON t.from_branch_id = fb.branch_id
LEFT JOIN branches tb ON t.to_branch_id = tb.branch_id
LEFT JOIN inventory_txn txn ON txn.reference_id = t.inventory_transfer_id
WHERE t.reference_number = 'TRF-2025-001'
GROUP BY t.inventory_transfer_id, t.reference_number, fb.name, tb.name, t.transfer_date, t.status, t.items;
*/
