-- ============================================================================
-- Inventory Transfer Workflow - Pure Database Implementation
-- ============================================================================
-- Migration: inventory_transfer_workflows.sql
-- Date: 2025-10-24
-- Description: Implements branch-aware inventory transfers using workflow system
-- 
-- Features:
-- - Automatic transfers for related branches (same parent, parent-child)
-- - Manual workflow for unrelated branches (pre_transit → in_transit → completed)
-- - Branch relationship detection via SQL conditional
-- - Inventory updates through inventory_txn table
-- - No custom PHP controllers needed
-- - No custom database functions needed
-- 
-- Uses existing workflow step types:
-- - conditional: Branch checks, status validation
-- - foreach: Item iteration
-- - database: INSERT/UPDATE operations
-- ============================================================================

-- ============================================================================
-- Workflow 1: Automatic Transfer (Related Branches)
-- ============================================================================
INSERT INTO workflows (workflow_id, name, description, trigger_type, trigger_config, is_active)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    'Automatic Inventory Transfer',
    'Automatically process transfers between related branches (same parent, parent-child)',
    'database',
    jsonb_build_object(
        'table', 'inventory_transfers',
        'event', 'AFTER INSERT',
        'condition', 'NEW.status = ''pending'''
    ),
    true
) ON CONFLICT (workflow_id) DO UPDATE 
SET description = EXCLUDED.description,
    trigger_config = EXCLUDED.trigger_config,
    is_active = EXCLUDED.is_active;

-- Step 1: Check if branches are related
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_on_success, next_step_on_failure)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000011'::uuid,
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    1,
    'Check Branch Relationship',
    'conditional',
    jsonb_build_object(
        'condition_type', 'sql_query',
        'query', '
            WITH branch_check AS (
                SELECT 
                    from_b.parent_branch_id AS from_parent,
                    to_b.parent_branch_id AS to_parent,
                    from_b.branch_id AS from_id,
                    to_b.branch_id AS to_id
                FROM branches from_b, branches to_b
                WHERE from_b.branch_id = {{record.from_branch_id}}
                  AND to_b.branch_id = {{record.to_branch_id}}
                  AND from_b.isDeleted = false
                  AND to_b.isDeleted = false
            )
            SELECT 
                CASE 
                    -- Same parent (siblings)
                    WHEN from_parent IS NOT NULL AND from_parent = to_parent THEN true
                    -- Parent to child
                    WHEN from_id = to_parent THEN true
                    -- Child to parent
                    WHEN from_parent = to_id THEN true
                    ELSE false
                END AS are_related
            FROM branch_check
        ',
        'result_field', 'are_related',
        'expected_value', true
    ),
    '0194a8b2-aaaa-7001-8001-000000000012'::uuid, -- next if related
    '0194a8b2-aaaa-7001-8001-000000000020'::uuid  -- next if not related
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_on_success = EXCLUDED.next_step_on_success,
    next_step_on_failure = EXCLUDED.next_step_on_failure;

-- Step 2a: (Related) Process each transfer item - Remove from source
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000012'::uuid,
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    2,
    'Deduct from Source Branch',
    'foreach',
    jsonb_build_object(
        'array_source', '{{record.items}}',
        'item_variable', 'item',
        'steps', jsonb_build_array(
            jsonb_build_object(
                'action_type', 'database',
                'action_config', jsonb_build_object(
                    'operation', 'INSERT',
                    'table', 'inventory_txn',
                    'values', jsonb_build_object(
                        'product_id', '{{item.product_id}}',
                        'change', '-{{item.quantity}}',
                        'unit_type', '{{item.unit_type}}',
                        'branch_id', '{{record.from_branch_id}}',
                        'batch_number', '{{item.batch_number}}',
                        'user_id', '{{record.created_by}}',
                        'org_id', '{{record.org_id}}',
                        'reference_id', '{{record.transfer_id}}',
                        'reference_table', 'inventory_transfers',
                        'notes', 'Auto-transfer to branch {{record.to_branch_id}}'
                    )
                )
            )
        )
    ),
    '0194a8b2-aaaa-7001-8001-000000000013'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 2b: (Related) Process each transfer item - Add to destination
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000013'::uuid,
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    3,
    'Add to Destination Branch',
    'foreach',
    jsonb_build_object(
        'array_source', '{{record.items}}',
        'item_variable', 'item',
        'steps', jsonb_build_array(
            jsonb_build_object(
                'action_type', 'database',
                'action_config', jsonb_build_object(
                    'operation', 'INSERT',
                    'table', 'inventory_txn',
                    'values', jsonb_build_object(
                        'product_id', '{{item.product_id}}',
                        'change', '{{item.quantity}}',
                        'unit_type', '{{item.unit_type}}',
                        'branch_id', '{{record.to_branch_id}}',
                        'batch_number', '{{item.batch_number}}',
                        'user_id', '{{record.created_by}}',
                        'org_id', '{{record.org_id}}',
                        'reference_id', '{{record.transfer_id}}',
                        'reference_table', 'inventory_transfers',
                        'notes', 'Auto-transfer from branch {{record.from_branch_id}}'
                    )
                )
            )
        )
    ),
    '0194a8b2-aaaa-7001-8001-000000000014'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 3: (Related) Update transfer status to completed
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000014'::uuid,
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    4,
    'Mark Transfer Completed',
    'database',
    jsonb_build_object(
        'operation', 'UPDATE',
        'table', 'inventory_transfers',
        'where', jsonb_build_object('transfer_id', '{{record.transfer_id}}'),
        'values', jsonb_build_object(
            'status', 'completed',
            'updatedAt', 'now()'
        )
    )
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config;

-- Step 2c: (Unrelated) Update status to pre_transit
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config)
VALUES (
    '0194a8b2-aaaa-7001-8001-000000000020'::uuid,
    '0194a8b2-aaaa-7001-8001-000000000001'::uuid,
    2,
    'Set Pre-Transit Status',
    'database',
    jsonb_build_object(
        'operation', 'UPDATE',
        'table', 'inventory_transfers',
        'where', jsonb_build_object('transfer_id', '{{record.transfer_id}}'),
        'values', jsonb_build_object(
            'status', 'pre_transit',
            'updatedAt', 'now()'
        )
    )
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config;

-- ============================================================================
-- Action Registry: Assign Driver (Manual Transfer)
-- ============================================================================
INSERT INTO action_registry (action_name, description, handler_type, handler_config)
VALUES (
    'inventory_transfer:assign_driver',
    'Assign driver and vehicle to inventory transfer',
    'workflow',
    jsonb_build_object(
        'workflow_id', '0194a8b2-aaaa-7001-8002-000000000001',
        'input_mapping', jsonb_build_object(
            'transfer_id', '{{record.transfer_id}}',
            'driver_id', '{{input.driver_id}}',
            'vehicle_id', '{{input.vehicle_id}}',
            'user_id', '{{user.user_id}}'
        ),
        'confirm', 'Assign driver to this transfer?',
        'form', jsonb_build_object(
            'fields', jsonb_build_array(
                jsonb_build_object(
                    'name', 'driver_id',
                    'type', 'select',
                    'label', 'Driver',
                    'dataSource', 'users',
                    'filter', jsonb_build_object('role', 'driver'),
                    'required', true
                ),
                jsonb_build_object(
                    'name', 'vehicle_id',
                    'type', 'select',
                    'label', 'Vehicle',
                    'dataSource', 'vehicles',
                    'required', true
                )
            )
        )
    )
) ON CONFLICT (action_name) DO UPDATE
SET handler_config = EXCLUDED.handler_config,
    description = EXCLUDED.description;

-- ============================================================================
-- Workflow 2: Assign Driver and Vehicle (Manual Transfer)
-- ============================================================================
INSERT INTO workflows (workflow_id, name, description, trigger_type, is_active)
VALUES (
    '0194a8b2-aaaa-7001-8002-000000000001'::uuid,
    'Assign Driver to Transfer',
    'Update transfer status and assign driver/vehicle for manual transfers',
    'manual',
    true
) ON CONFLICT (workflow_id) DO UPDATE 
SET description = EXCLUDED.description,
    is_active = EXCLUDED.is_active;

-- Step 1: Validate transfer is in pre_transit
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_on_success, next_step_on_failure)
VALUES (
    '0194a8b2-aaaa-7001-8002-000000000011'::uuid,
    '0194a8b2-aaaa-7001-8002-000000000001'::uuid,
    1,
    'Validate Pre-Transit Status',
    'conditional',
    jsonb_build_object(
        'condition_type', 'field_equals',
        'field', '{{record.status}}',
        'expected_value', 'pre_transit'
    ),
    '0194a8b2-aaaa-7001-8002-000000000012'::uuid,
    null
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_on_success = EXCLUDED.next_step_on_success;

-- Step 2: Update status to in_transit
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8002-000000000012'::uuid,
    '0194a8b2-aaaa-7001-8002-000000000001'::uuid,
    2,
    'Update Status to In-Transit',
    'database',
    jsonb_build_object(
        'operation', 'UPDATE',
        'table', 'inventory_transfers',
        'where', jsonb_build_object('transfer_id', '{{input.transfer_id}}'),
        'values', jsonb_build_object(
            'status', 'in_transit',
            'updatedAt', 'now()'
        )
    ),
    '0194a8b2-aaaa-7001-8002-000000000013'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 3: Log driver assignment in notes
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config)
VALUES (
    '0194a8b2-aaaa-7001-8002-000000000013'::uuid,
    '0194a8b2-aaaa-7001-8002-000000000001'::uuid,
    3,
    'Log Driver Assignment',
    'database',
    jsonb_build_object(
        'operation', 'RAW_SQL',
        'query', 'UPDATE inventory_transfers SET notes = COALESCE(notes, '''') || E''\nDriver assigned: '' || $1 || '', Vehicle: '' || $2 || '' by user: '' || $3 || '' at '' || now()::text WHERE transfer_id = $4',
        'params', jsonb_build_array(
            '{{input.driver_id}}',
            '{{input.vehicle_id}}',
            '{{input.user_id}}',
            '{{input.transfer_id}}'
        )
    )
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config;

-- ============================================================================
-- Action Registry: Complete Transfer (Manual Transfer)
-- ============================================================================
INSERT INTO action_registry (action_name, description, handler_type, handler_config)
VALUES (
    'inventory_transfer:complete',
    'Complete transfer with receipt verification',
    'workflow',
    jsonb_build_object(
        'workflow_id', '0194a8b2-aaaa-7001-8003-000000000001',
        'input_mapping', jsonb_build_object(
            'transfer_id', '{{record.transfer_id}}',
            'received_items', '{{input.received_items}}',
            'user_id', '{{user.user_id}}'
        ),
        'confirm', 'Complete this transfer?',
        'form', jsonb_build_object(
            'fields', jsonb_build_array(
                jsonb_build_object(
                    'name', 'received_items',
                    'type', 'json_array',
                    'label', 'Received Items',
                    'source', '{{record.items}}',
                    'columns', jsonb_build_array(
                        jsonb_build_object('field', 'product_id', 'label', 'Product', 'readonly', true, 'type', 'select', 'dataSource', 'products'),
                        jsonb_build_object('field', 'quantity', 'label', 'Requested', 'readonly', true),
                        jsonb_build_object('field', 'quantity_received', 'label', 'Received', 'type', 'number', 'required', true),
                        jsonb_build_object('field', 'quantity_damaged', 'label', 'Damaged', 'type', 'number', 'default', 0),
                        jsonb_build_object('field', 'notes', 'label', 'Notes', 'type', 'text')
                    )
                )
            )
        )
    )
) ON CONFLICT (action_name) DO UPDATE
SET handler_config = EXCLUDED.handler_config,
    description = EXCLUDED.description;

-- ============================================================================
-- Workflow 3: Complete Transfer with Verification
-- ============================================================================
INSERT INTO workflows (workflow_id, name, description, trigger_type, is_active)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    'Complete Transfer Receipt',
    'Process received items, update inventory, and complete transfer',
    'manual',
    true
) ON CONFLICT (workflow_id) DO UPDATE 
SET description = EXCLUDED.description,
    is_active = EXCLUDED.is_active;

-- Step 1: Validate transfer is in_transit
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_on_success)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000011'::uuid,
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    1,
    'Validate In-Transit Status',
    'conditional',
    jsonb_build_object(
        'condition_type', 'field_equals',
        'field', '{{record.status}}',
        'expected_value', 'in_transit'
    ),
    '0194a8b2-aaaa-7001-8003-000000000012'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_on_success = EXCLUDED.next_step_on_success;

-- Step 2: Process received items - Remove from source
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000012'::uuid,
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    2,
    'Deduct Requested Quantity from Source',
    'foreach',
    jsonb_build_object(
        'array_source', '{{record.items}}',
        'item_variable', 'orig_item',
        'steps', jsonb_build_array(
            jsonb_build_object(
                'action_type', 'database',
                'action_config', jsonb_build_object(
                    'operation', 'INSERT',
                    'table', 'inventory_txn',
                    'values', jsonb_build_object(
                        'product_id', '{{orig_item.product_id}}',
                        'change', '-{{orig_item.quantity}}',
                        'unit_type', '{{orig_item.unit_type}}',
                        'branch_id', '{{record.from_branch_id}}',
                        'batch_number', '{{orig_item.batch_number}}',
                        'user_id', '{{input.user_id}}',
                        'org_id', '{{record.org_id}}',
                        'reference_id', '{{input.transfer_id}}',
                        'reference_table', 'inventory_transfers',
                        'notes', 'Transfer to branch {{record.to_branch_id}}'
                    )
                )
            )
        )
    ),
    '0194a8b2-aaaa-7001-8003-000000000013'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 3: Add received quantities to destination
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000013'::uuid,
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    3,
    'Add Received Quantity to Destination',
    'foreach',
    jsonb_build_object(
        'array_source', '{{input.received_items}}',
        'item_variable', 'recv_item',
        'steps', jsonb_build_array(
            jsonb_build_object(
                'action_type', 'conditional',
                'action_config', jsonb_build_object(
                    'condition_type', 'field_greater_than',
                    'field', '{{recv_item.quantity_received}}',
                    'expected_value', 0
                ),
                'next_step_on_success', jsonb_build_object(
                    'action_type', 'database',
                    'action_config', jsonb_build_object(
                        'operation', 'INSERT',
                        'table', 'inventory_txn',
                        'values', jsonb_build_object(
                            'product_id', '{{recv_item.product_id}}',
                            'change', '{{recv_item.quantity_received}}',
                            'unit_type', '{{recv_item.unit_type}}',
                            'branch_id', '{{record.to_branch_id}}',
                            'user_id', '{{input.user_id}}',
                            'org_id', '{{record.org_id}}',
                            'reference_id', '{{input.transfer_id}}',
                            'reference_table', 'inventory_transfers',
                            'notes', 'Received from branch {{record.from_branch_id}}: {{recv_item.notes}}'
                        )
                    )
                )
            )
        )
    ),
    '0194a8b2-aaaa-7001-8003-000000000014'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 4: Log damaged items
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config, next_step_id)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000014'::uuid,
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    4,
    'Log Damaged Items',
    'foreach',
    jsonb_build_object(
        'array_source', '{{input.received_items}}',
        'item_variable', 'recv_item',
        'steps', jsonb_build_array(
            jsonb_build_object(
                'action_type', 'conditional',
                'action_config', jsonb_build_object(
                    'condition_type', 'field_greater_than',
                    'field', '{{recv_item.quantity_damaged}}',
                    'expected_value', 0
                ),
                'next_step_on_success', jsonb_build_object(
                    'action_type', 'database',
                    'action_config', jsonb_build_object(
                        'operation', 'INSERT',
                        'table', 'inventory_txn',
                        'values', jsonb_build_object(
                            'product_id', '{{recv_item.product_id}}',
                            'change', '-{{recv_item.quantity_damaged}}',
                            'unit_type', '{{recv_item.unit_type}}',
                            'branch_id', '{{record.to_branch_id}}',
                            'user_id', '{{input.user_id}}',
                            'org_id', '{{record.org_id}}',
                            'reference_id', '{{input.transfer_id}}',
                            'reference_table', 'inventory_transfers',
                            'notes', 'Damaged during transfer: {{recv_item.notes}}'
                        )
                    )
                )
            )
        )
    ),
    '0194a8b2-aaaa-7001-8003-000000000015'::uuid
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config,
    next_step_id = EXCLUDED.next_step_id;

-- Step 5: Update transfer status to completed
INSERT INTO workflow_steps (step_id, workflow_id, step_order, name, action_type, action_config)
VALUES (
    '0194a8b2-aaaa-7001-8003-000000000015'::uuid,
    '0194a8b2-aaaa-7001-8003-000000000001'::uuid,
    5,
    'Mark Transfer Completed',
    'database',
    jsonb_build_object(
        'operation', 'UPDATE',
        'table', 'inventory_transfers',
        'where', jsonb_build_object('transfer_id', '{{input.transfer_id}}'),
        'values', jsonb_build_object(
            'status', 'completed',
            'updatedAt', 'now()'
        )
    )
) ON CONFLICT (step_id) DO UPDATE 
SET action_config = EXCLUDED.action_config;

-- ============================================================================
-- Summary & Usage
-- ============================================================================
-- This migration creates a complete database-driven inventory transfer system.
--
-- AUTOMATIC TRANSFERS (Related Branches):
--   1. User creates transfer via /api/sync (inventory_transfers table)
--   2. Workflow automatically detects branch relationship
--   3. If related: Inventory updated immediately, status → completed
--   4. If unrelated: Status → pre_transit (manual workflow begins)
--
-- MANUAL TRANSFERS (Unrelated Branches):
--   1. Status: pre_transit (waiting for driver)
--   2. User clicks "Assign Driver" action → status: in_transit
--   3. User clicks "Complete" action → verifies quantities → status: completed
--
-- ACTIONS AVAILABLE:
--   - inventory_transfer:assign_driver (pre_transit → in_transit)
--   - inventory_transfer:complete (in_transit → completed)
--
-- NO PHP CONTROLLERS NEEDED - Everything runs through:
--   - WorkflowEngine (processes workflow steps)
--   - ActionRegistry (handles user actions)
--   - /api/sync (generic CRUD endpoint)
--
-- NEXT STEPS:
--   1. Create module definition for UI
--   2. Register in tenant_tables for sync
--   3. Add to IndexedDB schema
-- ============================================================================
