-- ============================================================================
-- Inventory Management Module - Phase 4 (Declarative) - SCHEMA CORRECTED
-- ============================================================================
-- This migration creates the Inventory Management module using the Phase 4
-- declarative pattern with forms, modules, and tenant_tables configuration.
-- All UI is defined in the database - zero custom UI code needed.
--
-- Updated to match actual database schema:
-- - forms table uses 'id' (not 'form_id'), no 'title' column
-- - modules table uses 'module_id' (UUID), 'config' (jsonb), no 'title'
-- - ui_actions table does not exist (actions handled in ActionRegistry.js)
-- ============================================================================

BEGIN;

-- ============================================================================
-- RESET SEQUENCES TO AVOID CONFLICTS
-- ============================================================================
-- Update forms sequence to max ID + 1
SELECT setval('forms_id_seq', COALESCE((SELECT MAX(id) FROM forms), 0) + 1, false);

-- ============================================================================
-- 1. INVENTORY RECEIVE FORM
-- ============================================================================
DELETE FROM forms WHERE name = 'inventory_receive_form';

INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_receive_form',
    'Receive inventory into the system from purchase orders or direct receipts',
    'inventory_receive',
    2,
    'grid',
    '[
        {
            "name": "product_id",
            "label": "Product",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/products/search",
            "onChange": "populateProductDetails"
        },
        {
            "name": "quantity",
            "label": "Quantity",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "unit_cost",
            "label": "Unit Cost",
            "type": "number",
            "required": true,
            "step": 0.01
        },
        {
            "name": "batch_number",
            "label": "Batch Number",
            "type": "text",
            "required": false
        },
        {
            "name": "expiry_date",
            "label": "Expiry Date",
            "type": "date",
            "required": false
        },
        {
            "name": "warehouse_id",
            "label": "Warehouse",
            "type": "select",
            "required": true,
            "dataSource": "/api/inventory/warehouses"
        },
        {
            "name": "supplier_id",
            "label": "Supplier",
            "type": "select-search",
            "required": false,
            "dataSource": "/api/vendors/search"
        },
        {
            "name": "reference_number",
            "label": "PO/Reference Number",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- 2. INVENTORY TRANSFER FORM
-- ============================================================================
DELETE FROM forms WHERE name = 'inventory_transfer_form';

INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_transfer_form',
    'Transfer inventory between warehouses or locations',
    'inventory_txn',
    2,
    'grid',
    '[
        {
            "name": "product_id",
            "label": "Product",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/products/search"
        },
        {
            "name": "quantity",
            "label": "Quantity to Transfer",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "from_warehouse_id",
            "label": "From Warehouse",
            "type": "select",
            "required": true,
            "dataSource": "/api/inventory/warehouses"
        },
        {
            "name": "to_warehouse_id",
            "label": "To Warehouse",
            "type": "select",
            "required": true,
            "dataSource": "/api/inventory/warehouses"
        },
        {
            "name": "reference_number",
            "label": "Transfer Reference",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Transfer Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- 3. INVENTORY BREAK BULK FORM
-- ============================================================================
DELETE FROM forms WHERE name = 'inventory_break_bulk_form';

INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_break_bulk_form',
    'Break down bulk items into smaller units',
    'inventory_breaks',
    2,
    'grid',
    '[
        {
            "name": "source_product_id",
            "label": "Source Product (Bulk)",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/products/search"
        },
        {
            "name": "source_quantity",
            "label": "Quantity to Break",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "target_product_id",
            "label": "Target Product (Unit)",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/products/search"
        },
        {
            "name": "target_quantity",
            "label": "Units Produced",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "warehouse_id",
            "label": "Warehouse",
            "type": "select",
            "required": true,
            "dataSource": "/api/inventory/warehouses"
        },
        {
            "name": "batch_number",
            "label": "New Batch Number",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- 4. INVENTORY ADJUSTMENT FORM
-- ============================================================================
DELETE FROM forms WHERE name = 'inventory_adjustment_form';

INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_adjustment_form',
    'Adjust inventory quantities for stock takes, damage, theft, etc.',
    'inventory_txn',
    2,
    'grid',
    '[
        {
            "name": "product_id",
            "label": "Product",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/products/search"
        },
        {
            "name": "adjustment_type",
            "label": "Adjustment Type",
            "type": "select",
            "required": true,
            "options": [
                {"value": "increase", "label": "Increase (Add Stock)"},
                {"value": "decrease", "label": "Decrease (Remove Stock)"}
            ]
        },
        {
            "name": "quantity",
            "label": "Quantity",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "warehouse_id",
            "label": "Warehouse",
            "type": "select",
            "required": true,
            "dataSource": "/api/inventory/warehouses"
        },
        {
            "name": "reason",
            "label": "Reason",
            "type": "select",
            "required": true,
            "options": [
                {"value": "stock_take", "label": "Stock Take Correction"},
                {"value": "damage", "label": "Damaged Goods"},
                {"value": "theft", "label": "Theft/Loss"},
                {"value": "return", "label": "Customer Return"},
                {"value": "expired", "label": "Expired Product"},
                {"value": "other", "label": "Other"}
            ]
        },
        {
            "name": "reference_number",
            "label": "Reference Number",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Notes/Details",
            "type": "textarea",
            "required": true,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- 5. WAREHOUSE FORM
-- ============================================================================
DELETE FROM forms WHERE name = 'warehouse_form';

INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'warehouse_form',
    'Create and manage warehouse locations',
    'locations',
    2,
    'grid',
    '[
        {
            "name": "name",
            "label": "Warehouse Name",
            "type": "text",
            "required": true
        },
        {
            "name": "code",
            "label": "Warehouse Code",
            "type": "text",
            "required": true
        },
        {
            "name": "type",
            "label": "Location Type",
            "type": "select",
            "required": true,
            "options": [
                {"value": "warehouse", "label": "Warehouse"},
                {"value": "store", "label": "Store/Outlet"},
                {"value": "transit", "label": "In Transit"}
            ]
        },
        {
            "name": "address",
            "label": "Address",
            "type": "text",
            "required": false
        },
        {
            "name": "capacity",
            "label": "Capacity (units)",
            "type": "number",
            "required": false
        },
        {
            "name": "manager",
            "label": "Warehouse Manager",
            "type": "text",
            "required": false
        },
        {
            "name": "is_active",
            "label": "Active",
            "type": "checkbox",
            "default": true
        },
        {
            "name": "notes",
            "label": "Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- 6. INVENTORY MANAGEMENT MODULE
-- ============================================================================
DELETE FROM modules WHERE name = 'inventory_management';

INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'inventory_management',
    'fa-boxes',
    'Complete inventory management with receiving, transfers, break bulk operations, and warehouse management',
    '{
        "title": "Inventory Management",
        "layout": "tabs",
        "dataSource": "/api/inventory/dashboard",
        "tabs": [
            {
                "id": "receive",
                "label": "Receive",
                "icon": "fa-arrow-down",
                "actions": [
                    {
                        "label": "Receive Inventory",
                        "icon": "fa-plus",
                        "action": "inventory:receive",
                        "style": "btn-primary"
                    },
                    {
                        "label": "Transfer",
                        "icon": "fa-exchange-alt",
                        "action": "inventory:transfer",
                        "style": "btn-secondary"
                    },
                    {
                        "label": "Break Bulk",
                        "icon": "fa-cut",
                        "action": "inventory:break",
                        "style": "btn-secondary"
                    },
                    {
                        "label": "Process Transactions",
                        "icon": "fa-cog",
                        "action": "inventory:process",
                        "style": "btn-success"
                    }
                ],
                "component": "DataLoader",
                "config": {
                    "dataSource": "/api/inventory/pending-transactions",
                    "columns": [
                        {"key": "txn_type", "label": "Type"},
                        {"key": "product_name", "label": "Product"},
                        {"key": "quantity", "label": "Quantity"},
                        {"key": "warehouse_name", "label": "Warehouse"},
                        {"key": "status", "label": "Status"},
                        {"key": "created_at", "label": "Date", "type": "date"}
                    ],
                    "rowActions": [
                        {
                            "label": "View",
                            "icon": "fa-eye",
                            "action": "inventory:view"
                        },
                        {
                            "label": "Cancel",
                            "icon": "fa-times",
                            "action": "inventory:cancel",
                            "condition": "status == \"pending\"",
                            "confirm": "Cancel this transaction?"
                        }
                    ]
                }
            },
            {
                "id": "stocks",
                "label": "Stocks",
                "icon": "fa-warehouse",
                "actions": [
                    {
                        "label": "Adjust Stock",
                        "icon": "fa-edit",
                        "action": "inventory:adjust",
                        "style": "btn-warning"
                    },
                    {
                        "label": "Export",
                        "icon": "fa-download",
                        "action": "inventory:export",
                        "style": "btn-secondary"
                    }
                ],
                "component": "DataLoader",
                "config": {
                    "dataSource": "/api/inventory/stocks",
                    "columns": [
                        {"key": "product_name", "label": "Product"},
                        {"key": "sku", "label": "SKU"},
                        {"key": "warehouse_name", "label": "Warehouse"},
                        {"key": "quantity_on_hand", "label": "On Hand"},
                        {"key": "quantity_available", "label": "Available"},
                        {"key": "unit_cost", "label": "Unit Cost", "type": "currency"},
                        {"key": "total_value", "label": "Total Value", "type": "currency"}
                    ],
                    "rowActions": [
                        {
                            "label": "Adjust",
                            "icon": "fa-edit",
                            "action": "inventory:adjust"
                        },
                        {
                            "label": "History",
                            "icon": "fa-history",
                            "action": "inventory:history"
                        }
                    ],
                    "filters": [
                        {
                            "key": "warehouse_id",
                            "label": "Warehouse",
                            "type": "select",
                            "dataSource": "/api/inventory/warehouses"
                        },
                        {
                            "key": "low_stock",
                            "label": "Low Stock Only",
                            "type": "checkbox"
                        }
                    ]
                }
            },
            {
                "id": "warehouses",
                "label": "Warehouses",
                "icon": "fa-building",
                "actions": [
                    {
                        "label": "Add Warehouse",
                        "icon": "fa-plus",
                        "action": "inventory:addWarehouse",
                        "style": "btn-primary"
                    }
                ],
                "component": "DataLoader",
                "config": {
                    "dataSource": "/api/inventory/warehouses",
                    "columns": [
                        {"key": "name", "label": "Warehouse Name"},
                        {"key": "code", "label": "Code"},
                        {"key": "type", "label": "Type"},
                        {"key": "address", "label": "Address"},
                        {"key": "capacity", "label": "Capacity"},
                        {"key": "is_active", "label": "Active", "type": "badge"}
                    ],
                    "rowActions": [
                        {
                            "label": "Edit",
                            "icon": "fa-edit",
                            "action": "inventory:editWarehouse"
                        },
                        {
                            "label": "View Stock",
                            "icon": "fa-boxes",
                            "action": "inventory:warehouseStock"
                        },
                        {
                            "label": "Deactivate",
                            "icon": "fa-ban",
                            "action": "inventory:deactivateWarehouse",
                            "condition": "is_active == true",
                            "confirm": "Deactivate this warehouse?"
                        }
                    ]
                }
            }
        ]
    }'::jsonb
);

-- ============================================================================
-- 7. TENANT TABLES SYNC CONFIGURATION
-- ============================================================================
-- Ensure inventory tables are registered for sync
-- Note: tenant_tables uses a 'definition' jsonb column for configuration
-- Delete existing entries first to avoid duplicates
DELETE FROM tenant_tables WHERE table_name IN (
    'inventory', 'inventory_receive', 'inventory_txn', 
    'inventory_breaks', 'products', 'locations'
);

INSERT INTO tenant_tables (table_name, definition)
VALUES 
    ('inventory', '{"sync_enabled": true, "cache_duration_minutes": 15}'::jsonb),
    ('inventory_receive', '{"sync_enabled": true, "cache_duration_minutes": 15}'::jsonb),
    ('inventory_txn', '{"sync_enabled": true, "cache_duration_minutes": 15}'::jsonb),
    ('inventory_breaks', '{"sync_enabled": true, "cache_duration_minutes": 30}'::jsonb),
    ('products', '{"sync_enabled": true, "cache_duration_minutes": 60}'::jsonb),
    ('locations', '{"sync_enabled": true, "cache_duration_minutes": 60}'::jsonb);

COMMIT;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================
-- Run these after migration to verify:
--
-- SELECT * FROM forms WHERE name LIKE 'inventory%';
-- SELECT * FROM modules WHERE name = 'inventory_management';
-- SELECT * FROM tenant_tables WHERE table_name IN ('inventory', 'inventory_receive', 'inventory_txn', 'inventory_breaks');
-- ============================================================================
