-- ============================================================================
-- 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)
--
-- Trigger Flow:
--   1. inventory_txn INSERT occurs
--   2. update_inventory_from_txn() fires
--   3. Updates/creates inventory record for the specific batch
--   4. Updates/creates product_quantity record for the product totals
-- ============================================================================

-- First, create the product_quantity table if it doesn't exist
CREATE TABLE IF NOT EXISTS product_quantity (
  product_quantity_id       uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  product_id                uuid NOT NULL REFERENCES products(product_id) ON DELETE CASCADE,
  branch_id                 uuid NOT NULL REFERENCES branches(branch_id) ON DELETE CASCADE,
  org_id                    uuid NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
  quantity                  integer DEFAULT 0,
  isDeleted                 BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt                 timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_product_quantity_product_branch_org UNIQUE (product_id, branch_id, org_id)
);

CREATE INDEX IF NOT EXISTS idx_product_quantity_product ON product_quantity(product_id) WHERE isDeleted = false;
CREATE INDEX IF NOT EXISTS idx_product_quantity_branch ON product_quantity(branch_id) WHERE isDeleted = false;
CREATE INDEX IF NOT EXISTS idx_product_quantity_org ON product_quantity(org_id) WHERE isDeleted = false;

COMMENT ON TABLE product_quantity IS 'Aggregate product quantities per branch (sum of all batches)';
COMMENT ON COLUMN product_quantity.quantity IS 'Total quantity of this product at this branch across all batches';

-- ============================================================================
-- Trigger Function: update_inventory_from_txn
-- ============================================================================
-- Updates inventory and product_quantity tables based on inventory_txn records
-- 
-- Logic:
--   1. Extract transaction details from NEW record
--   2. Find or create product_batch_number record
--   3. Update/insert inventory record (specific batch at branch)
--   4. Update/insert product_quantity record (product totals at branch)
--
-- Notes:
--   - Uses UPSERT (INSERT ... ON CONFLICT) for atomic operations
--   - Handles missing batch numbers by creating default batches
--   - Maintains referential integrity with product_batch_number
--   - Only processes non-deleted transactions
-- ============================================================================

CREATE OR REPLACE FUNCTION update_inventory_from_txn()
RETURNS TRIGGER AS $$
DECLARE
    v_batch_id uuid;
    v_batch_number text;
    v_unit_type text;
    v_current_inventory_qty integer;
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
    -- Check if batch_number is in the data JSONB field
    IF NEW.data IS NOT NULL AND NEW.data ? 'batch_number' THEN
        v_batch_number := NEW.data->>'batch_number';
    ELSE
        -- Use a default batch if none specified
        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;
        
        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)
    -- ========================================================================
    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,  -- Initialize with the transaction change
        v_unit_type,
        NEW.org_id,
        NEW.reference_id,
        NEW.reference_table,
        false,
        now()
    )
    ON CONFLICT ON CONSTRAINT inventory_pkey DO NOTHING;
    
    -- If no conflict (no matching primary key), we need to update by finding the 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 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,  -- Initialize with the transaction 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.';

-- ============================================================================
-- Attach Trigger to inventory_txn Table
-- ============================================================================

-- Drop trigger if it exists (for clean re-creation)
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';

-- ============================================================================
-- Example Usage and Verification Queries
-- ============================================================================

/*
-- Test the trigger by inserting a transaction:

INSERT INTO inventory_txn (
    product_id,
    branch_id,
    change,
    reason,
    unit_type,
    org_id,
    reference_table,
    data
) VALUES (
    'product-uuid-here',
    'branch-uuid-here',
    100,
    'stock_received',
    'PIECE',
    'org-uuid-here',
    'purchase_orders',
    '{"batch_number": "BATCH001", "expiry_date": "2025-12-31"}'::jsonb
);

-- Verify inventory was updated:
SELECT 
    i.inventory_id,
    p.name as product_name,
    pbn.batch_number,
    i.current_qty,
    i.unit_type,
    b.name as branch_name
FROM inventory i
JOIN product_batch_number pbn ON i.product_batch_number_id = pbn.product_batch_number_id
JOIN products p ON pbn.product_id = p.product_id
JOIN branches b ON i.branch_id = b.branch_id
WHERE i.isDeleted = false
ORDER BY i.updatedAt DESC
LIMIT 10;

-- Verify product_quantity was updated:
SELECT 
    pq.product_quantity_id,
    p.name as product_name,
    b.name as branch_name,
    pq.quantity as total_quantity,
    pq.updatedAt
FROM product_quantity pq
JOIN products p ON pq.product_id = p.product_id
JOIN branches b ON pq.branch_id = b.branch_id
WHERE pq.isDeleted = false
ORDER BY pq.updatedAt DESC
LIMIT 10;

-- Verify batch-level details:
SELECT 
    p.name as product_name,
    pbn.batch_number,
    b.name as branch_name,
    i.current_qty as batch_qty,
    pq.quantity as total_branch_qty
FROM inventory i
JOIN product_batch_number pbn ON i.product_batch_number_id = pbn.product_batch_number_id
JOIN products p ON pbn.product_id = p.product_id
JOIN branches b ON i.branch_id = b.branch_id
LEFT JOIN product_quantity pq ON pq.product_id = p.product_id 
    AND pq.branch_id = b.branch_id
WHERE i.isDeleted = false
ORDER BY p.name, pbn.batch_number;

-- Test transfer scenario (creates 2 txns via transfer trigger):
-- The transfer trigger will create inventory_txn records
-- which will then trigger this function automatically

-- View transaction history:
SELECT 
    it.inventory_txn_id,
    p.name as product_name,
    b.name as branch_name,
    it.change,
    it.reason,
    it.reference_table,
    it.processed_at
FROM inventory_txn it
JOIN products p ON it.product_id = p.product_id
JOIN branches b ON it.branch_id = b.branch_id
WHERE it.isDeleted = false
ORDER BY it.processed_at DESC
LIMIT 20;
*/

-- ============================================================================
-- Maintenance Notes
-- ============================================================================

/*
IMPORTANT NOTES:

1. Transaction Order:
   - inventory_transfer trigger creates inventory_txn records
   - inventory_txn trigger (this one) updates inventory & product_quantity
   - Both triggers work together automatically

2. Batch Management:
   - If no batch_number in transaction data, uses 'DEFAULT' batch
   - Batches are created automatically as needed
   - Each batch maintains separate inventory records

3. Quantity Tracking:
   - inventory table: Tracks quantity per batch per branch
   - product_quantity table: Tracks total quantity per product per branch
   - product_quantity.quantity = SUM(inventory.current_qty) for all batches

4. Unit Types:
   - Defaults to 'PIECE' if not specified
   - Should match product unit definitions
   - Consider adding validation if needed

5. Performance:
   - Uses UPSERT (ON CONFLICT) for atomic operations
   - Indexed on product_id, branch_id for fast lookups
   - Consider partitioning if tables grow very large

6. Error Handling:
   - RAISE NOTICE provides audit trail in logs
   - Transaction rolls back on any error
   - All operations are atomic

7. Testing:
   - Always test on development database first
   - Verify trigger fires correctly
   - Check that quantities calculate accurately
   - Test with transfer completion workflow
*/
