-- Phase 7: Frontend Modules for Purchase Orders and Enhanced Inventory
-- Declarative module definitions for ModuleLoader (Phase 4 pattern)
-- Author: TAF ERP Development Team
-- Date: 2025-10-21

-- ============================================================================
-- MODULE: Purchase Orders Management
-- ============================================================================

INSERT INTO modules (module_id, name, label, icon, category, status, config, created_at, updated_at)
VALUES (
    uuid_generate_v7(),
    'purchase_orders',
    'Purchase Orders',
    'fa-shopping-cart',
    'procurement',
    'active',
    '{
        "description": "Manage purchase orders with multi-unit tracking",
        "version": "1.0.0",
        "dataSources": {
            "purchase_orders": {
                "endpoint": "/api/purchase_orders",
                "primaryKey": "purchase_order_id",
                "cache": true,
                "refreshInterval": 30000
            },
            "vendors": {
                "endpoint": "/api/vendors",
                "primaryKey": "vendor_id",
                "cache": true
            }
        },
        "columns": [
            {"field": "po_number", "label": "PO #", "sortable": true, "searchable": true},
            {"field": "vendor_name", "label": "Vendor", "sortable": true, "searchable": true},
            {"field": "status", "label": "Status", "sortable": true, "type": "badge", 
             "badges": {
                 "pending": "badge-info",
                 "partially_received": "badge-warning", 
                 "fully_received": "badge-success",
                 "closed": "badge-neutral"
             }},
            {"field": "expected_delivery_date", "label": "Expected Delivery", "type": "date", "sortable": true},
            {"field": "total_amount", "label": "Total", "type": "currency", "sortable": true},
            {"field": "completion_percentage", "label": "Progress", "type": "progress"},
            {"field": "created_at", "label": "Created", "type": "date", "sortable": true}
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "po:view",
                "class": "btn-sm btn-ghost"
            },
            {
                "label": "Receive Goods",
                "icon": "fa-truck",
                "action": "po:receive",
                "class": "btn-sm btn-primary",
                "condition": "status != \"closed\" && status != \"fully_received\"",
                "confirm": false
            },
            {
                "label": "Close PO",
                "icon": "fa-check",
                "action": "po:close",
                "class": "btn-sm btn-warning",
                "condition": "status != \"closed\"",
                "confirm": "Close this Purchase Order? This requires closure notes."
            },
            {
                "label": "Edit",
                "icon": "fa-edit",
                "action": "po:edit",
                "class": "btn-sm btn-ghost",
                "condition": "status == \"pending\""
            },
            {
                "label": "Delete",
                "icon": "fa-trash",
                "action": "po:delete",
                "class": "btn-sm btn-error",
                "condition": "status == \"pending\"",
                "confirm": "Delete this Purchase Order? This action cannot be undone."
            }
        ],
        "headerActions": [
            {
                "label": "Create Purchase Order",
                "icon": "fa-plus",
                "action": "po:create",
                "class": "btn-primary"
            }
        ],
        "filters": [
            {
                "name": "status",
                "label": "Status",
                "type": "select",
                "options": [
                    {"value": "", "label": "All Status"},
                    {"value": "pending", "label": "Pending"},
                    {"value": "partially_received", "label": "Partially Received"},
                    {"value": "fully_received", "label": "Fully Received"},
                    {"value": "closed", "label": "Closed"}
                ]
            },
            {
                "name": "vendor_id",
                "label": "Vendor",
                "type": "select",
                "dataSource": "vendors"
            },
            {
                "name": "date_from",
                "label": "From Date",
                "type": "date"
            },
            {
                "name": "date_to",
                "label": "To Date",
                "type": "date"
            }
        ],
        "defaultSort": {"field": "created_at", "direction": "desc"},
        "pageSize": 25,
        "exportable": true
    }',
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    label = EXCLUDED.label,
    icon = EXCLUDED.icon,
    category = EXCLUDED.category,
    status = EXCLUDED.status,
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE: Enhanced Inventory Management (Phase 7)
-- ============================================================================

INSERT INTO modules (module_id, name, label, icon, category, status, config, created_at, updated_at)
VALUES (
    uuid_generate_v7(),
    'inventory_enhanced',
    'Inventory Control',
    'fa-warehouse',
    'inventory',
    'active',
    '{
        "description": "Enhanced inventory management with multi-unit tracking",
        "version": "2.0.0",
        "tabs": [
            {
                "id": "stocks",
                "label": "Stock Levels",
                "icon": "fa-boxes",
                "dataSources": {
                    "inventory": {
                        "endpoint": "/api/inventory",
                        "primaryKey": "inventory_id",
                        "cache": true,
                        "refreshInterval": 10000
                    }
                },
                "columns": [
                    {"field": "product_name", "label": "Product", "sortable": true, "searchable": true},
                    {"field": "branch_name", "label": "Location", "sortable": true},
                    {"field": "quantity", "label": "Quantity", "sortable": true, "type": "number"},
                    {"field": "unit_type", "label": "Unit", "sortable": true},
                    {"field": "batch", "label": "Batch #"},
                    {"field": "expiry", "label": "Expiry", "type": "date"},
                    {"field": "reorder_level", "label": "Reorder Level", "type": "number"},
                    {"field": "last_updated", "label": "Last Updated", "type": "datetime"}
                ],
                "headerActions": [
                    {
                        "label": "Break Bulk",
                        "icon": "fa-cut",
                        "action": "inventory:break",
                        "class": "btn-secondary"
                    },
                    {
                        "label": "Dispatch",
                        "icon": "fa-truck",
                        "action": "inventory:dispatch",
                        "class": "btn-secondary"
                    },
                    {
                        "label": "Process Transactions",
                        "icon": "fa-sync",
                        "action": "inventory:process",
                        "class": "btn-secondary"
                    }
                ],
                "filters": [
                    {
                        "name": "branch_id",
                        "label": "Location",
                        "type": "select",
                        "dataSource": "branches"
                    },
                    {
                        "name": "low_stock",
                        "label": "Show Low Stock Only",
                        "type": "checkbox"
                    }
                ]
            },
            {
                "id": "receive",
                "label": "Receive Goods",
                "icon": "fa-inbox",
                "component": "custom",
                "customHandler": "InventoryManagement.openReceivingForm"
            },
            {
                "id": "movement",
                "label": "Movement History",
                "icon": "fa-history",
                "dataSources": {
                    "movement_history": {
                        "endpoint": "/api/inventory/movement_history",
                        "cache": false
                    }
                },
                "columns": [
                    {"field": "transaction_date", "label": "Date", "type": "datetime", "sortable": true},
                    {"field": "product_name", "label": "Product", "searchable": true},
                    {"field": "from_location", "label": "From"},
                    {"field": "to_location", "label": "To"},
                    {"field": "quantity", "label": "Qty", "type": "number"},
                    {"field": "unit_type", "label": "Unit"},
                    {"field": "movement_type", "label": "Type", "type": "badge"},
                    {"field": "po_number", "label": "PO #", "searchable": true},
                    {"field": "delivery_note_number", "label": "Delivery #", "searchable": true},
                    {"field": "invoice_number", "label": "Invoice #", "searchable": true},
                    {"field": "batch", "label": "Batch #"}
                ],
                "headerActions": [
                    {
                        "label": "Refresh View",
                        "icon": "fa-sync",
                        "action": "inventory:history:refresh",
                        "class": "btn-secondary"
                    },
                    {
                        "label": "Export",
                        "icon": "fa-download",
                        "action": "inventory:history:export",
                        "class": "btn-secondary"
                    }
                ],
                "filters": [
                    {
                        "name": "start_date",
                        "label": "From Date",
                        "type": "date"
                    },
                    {
                        "name": "end_date",
                        "label": "To Date",
                        "type": "date"
                    },
                    {
                        "name": "product_id",
                        "label": "Product",
                        "type": "product-search"
                    },
                    {
                        "name": "branch_id",
                        "label": "Location",
                        "type": "select",
                        "dataSource": "branches"
                    },
                    {
                        "name": "movement_type",
                        "label": "Type",
                        "type": "select",
                        "options": [
                            {"value": "", "label": "All Types"},
                            {"value": "RECEIVE", "label": "Receive"},
                            {"value": "TRANSFER", "label": "Transfer"},
                            {"value": "BREAK", "label": "Break"},
                            {"value": "SALE", "label": "Sale"},
                            {"value": "ADJUSTMENT", "label": "Adjustment"}
                        ]
                    }
                ],
                "pageSize": 50
            }
        ]
    }',
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    label = EXCLUDED.label,
    icon = EXCLUDED.icon,
    category = EXCLUDED.category,
    status = EXCLUDED.status,
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE: Product Unit Configuration
-- ============================================================================

INSERT INTO modules (module_id, name, label, icon, category, status, config, created_at, updated_at)
VALUES (
    uuid_generate_v7(),
    'product_units',
    'Product Units Setup',
    'fa-balance-scale',
    'products',
    'active',
    '{
        "description": "Configure multi-unit conversions for products",
        "version": "1.0.0",
        "dataSources": {
            "products": {
                "endpoint": "/api/products",
                "primaryKey": "product_id",
                "cache": true
            }
        },
        "columns": [
            {"field": "name", "label": "Product", "sortable": true, "searchable": true},
            {"field": "base_unit", "label": "Base Unit"},
            {"field": "master_unit", "label": "Master Unit 1"},
            {"field": "units_per_master", "label": "Units/Master 1", "type": "number"},
            {"field": "master_unit_2", "label": "Master Unit 2"},
            {"field": "units_per_master_2", "label": "Units/Master 2", "type": "number"},
            {"field": "conversion_display", "label": "Conversion", "computed": true, 
             "formula": "1 {master_unit_2} = {units_per_master_2} {master_unit}, 1 {master_unit} = {units_per_master} {base_unit}"}
        ],
        "rowActions": [
            {
                "label": "Edit Units",
                "icon": "fa-edit",
                "action": "product:edit-units",
                "class": "btn-sm btn-primary"
            }
        ],
        "headerActions": [
            {
                "label": "Bulk Update",
                "icon": "fa-upload",
                "action": "product:units:bulk-import",
                "class": "btn-secondary"
            }
        ],
        "filters": [
            {
                "name": "has_units",
                "label": "Show Configured Only",
                "type": "checkbox"
            }
        ]
    }',
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    label = EXCLUDED.label,
    icon = EXCLUDED.icon,
    category = EXCLUDED.category,
    status = EXCLUDED.status,
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- Update router.js entries (informational - manual update required)
-- ============================================================================

-- The following entries should be added to FrontEnd/js/router.js:
-- 
-- PurchaseOrders: () => import('modules/purchase-orders/purchase-orders'),
-- InventoryEnhanced: () => import('modules/inventory/inventory-enhanced'),
-- ProductUnits: () => import('modules/products/product-units'),

-- ============================================================================
-- Grant permissions to default roles
-- ============================================================================

-- Grant purchase order permissions to procurement role
INSERT INTO role_permissions (role_permission_id, role_id, resource, action, granted, created_at)
SELECT 
    uuid_generate_v7(),
    r.role_id,
    'purchase_orders',
    action_name,
    true,
    NOW()
FROM roles r
CROSS JOIN (
    VALUES 
        ('create'),
        ('read'),
        ('update'),
        ('delete'),
        ('receive'),
        ('close')
) AS actions(action_name)
WHERE r.role_name IN ('Admin', 'Procurement Manager', 'Warehouse Manager')
ON CONFLICT DO NOTHING;

-- Grant inventory permissions
INSERT INTO role_permissions (role_permission_id, role_id, resource, action, granted, created_at)
SELECT 
    uuid_generate_v7(),
    r.role_id,
    'inventory_enhanced',
    action_name,
    true,
    NOW()
FROM roles r
CROSS JOIN (
    VALUES 
        ('view'),
        ('receive'),
        ('transfer'),
        ('dispatch'),
        ('break'),
        ('history')
) AS actions(action_name)
WHERE r.role_name IN ('Admin', 'Warehouse Manager', 'Store Manager')
ON CONFLICT DO NOTHING;

-- ============================================================================
-- Add IndexedDB stores for offline sync (informational)
-- ============================================================================

-- The following stores should be added to FrontEnd/js/core/schema.js:
--
-- purchase_orders: { keyPath: 'purchase_order_id', indexes: ['po_number', 'vendor_id', 'status'] },
-- purchase_order_lines: { keyPath: 'po_line_id', indexes: ['purchase_order_id', 'product_id'] },
-- goods_receipt_lines: { keyPath: 'receipt_line_id', indexes: ['goods_receipt_id', 'po_line_id'] },
-- dispatch_notes: { keyPath: 'dispatch_id', indexes: ['dispatch_number', 'from_branch_id', 'to_branch_id'] },
-- dispatch_lines: { keyPath: 'dispatch_line_id', indexes: ['dispatch_id', 'product_id'] },
-- inventory_breaks: { keyPath: 'break_id', indexes: ['product_id', 'branch_id', 'break_date'] }

COMMENT ON TABLE modules IS 'Phase 7 frontend modules added: purchase_orders, inventory_enhanced, product_units';
