-- ============================================================================
-- Add Missing Legacy Modules to Module Registry
-- ============================================================================
-- Description: Register recently created legacy JavaScript modules in modules table
-- Date: 2025-11-22
-- Modules: HRMS Recruitment, Finance Management, Fleet Management, Packaging, QA
-- Note: These are legacy modules that use bespoke JavaScript files
-- ============================================================================

-- 1. HRMS Recruitment Module (Legacy)
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    parent_module_id,
    aria_label,
    updatedat,
    isdeleted
) VALUES (
    '019c0001-0001-7000-8000-000000000001',
    'HRMSRecruitment',
    'fa-users-gear',
    'Complete recruitment workflow from job posting to employee onboarding',
    '{
        "type": "legacy",
        "jsFile": "FrontEnd/js/modules/HRMSRecruitment.js",
        "controller": "HRMSController",
        "category": "Human Resources",
        "route": "/hrms-recruitment",
        "permissions": ["hrms.view", "hrms.manage"],
        "dataSources": {
            "job_postings": {
                "table": "job_postings",
                "columns": ["id", "job_code", "job_title", "department", "status", "application_deadline", "posted_at"],
                "filters": ["status", "department"],
                "searchable": ["job_title", "job_code", "department"]
            },
            "job_applications": {
                "table": "job_applications",
                "columns": ["id", "application_number", "applicant_id", "job_posting_id", "status", "stage", "application_date"],
                "filters": ["status", "stage"],
                "searchable": ["application_number"]
            },
            "applicants": {
                "table": "applicants",
                "columns": ["id", "first_name", "last_name", "email", "phone", "current_position"],
                "filters": ["is_blacklisted"],
                "searchable": ["first_name", "last_name", "email"]
            }
        },
        "views": [
            {
                "name": "job_postings",
                "label": "Job Postings",
                "type": "table",
                "dataSource": "job_postings",
                "rowActions": [
                    {"label": "View Applications", "icon": "fa-folder-open", "action": "hrms:view-applications"},
                    {"label": "Edit", "icon": "fa-edit", "action": "edit"},
                    {"label": "Close Posting", "icon": "fa-lock", "action": "hrms:close-posting", "condition": "status == \"published\""}
                ]
            },
            {
                "name": "applications",
                "label": "Applications",
                "type": "table",
                "dataSource": "job_applications",
                "rowActions": [
                    {"label": "Review", "icon": "fa-eye", "action": "hrms:review-application"},
                    {"label": "Schedule Interview", "icon": "fa-calendar", "action": "hrms:schedule-interview", "condition": "status == \"shortlisted\""},
                    {"label": "Send Offer", "icon": "fa-envelope", "action": "hrms:send-offer", "condition": "status == \"interviewed\""}
                ]
            },
            {
                "name": "candidates",
                "label": "Candidate Pool",
                "type": "table",
                "dataSource": "applicants"
            }
        ]
    }'::jsonb,
    NULL,
    NULL,
    'HRMS Recruitment Module',
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- 2. Finance Management Module
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    parent_module_id,
    aria_label,
    updatedat,
    isdeleted
) VALUES (
    '019c0001-0002-7000-8000-000000000002',
    'Finance Management',
    'fa-chart-line',
    'Comprehensive accounting with bank reconciliation, budgets, and financial reporting',
    '{
        "category": "finance",
        "route": "/finance-management",
        "permissions": ["finance.view", "finance.manage"],
        "dataSources": {
            "fiscal_periods": {
                "table": "fiscal_periods",
                "columns": ["id", "period_name", "fiscal_year", "start_date", "end_date", "status"],
                "filters": ["status", "fiscal_year"],
                "searchable": ["period_name"]
            },
            "bank_accounts": {
                "table": "bank_accounts",
                "columns": ["id", "account_name", "account_number", "bank_name", "current_balance", "is_active"],
                "filters": ["is_active", "account_type"],
                "searchable": ["account_name", "account_number", "bank_name"]
            },
            "bank_reconciliations": {
                "table": "bank_reconciliations",
                "columns": ["id", "bank_account_id", "reconciliation_date", "statement_balance", "book_balance", "status"],
                "filters": ["status"],
                "searchable": []
            },
            "financial_budgets": {
                "table": "financial_budgets",
                "columns": ["id", "budget_name", "fiscal_period_id", "start_date", "end_date", "total_amount", "status"],
                "filters": ["status", "budget_type"],
                "searchable": ["budget_name"]
            }
        },
        "views": [
            {
                "name": "fiscal_periods",
                "label": "Fiscal Periods",
                "type": "table",
                "dataSource": "fiscal_periods",
                "rowActions": [
                    {"label": "Close Period", "icon": "fa-lock", "action": "finance:close-period", "condition": "status == \"open\"", "confirm": "Close this fiscal period?"},
                    {"label": "View Transactions", "icon": "fa-list", "action": "finance:view-transactions"}
                ]
            },
            {
                "name": "bank_accounts",
                "label": "Bank Accounts",
                "type": "table",
                "dataSource": "bank_accounts",
                "rowActions": [
                    {"label": "Reconcile", "icon": "fa-balance-scale", "action": "finance:reconcile-account"},
                    {"label": "View Transactions", "icon": "fa-list", "action": "finance:view-bank-transactions"}
                ]
            },
            {
                "name": "reconciliations",
                "label": "Reconciliations",
                "type": "table",
                "dataSource": "bank_reconciliations"
            },
            {
                "name": "budgets",
                "label": "Budgets",
                "type": "table",
                "dataSource": "financial_budgets",
                "rowActions": [
                    {"label": "View Details", "icon": "fa-eye", "action": "view"},
                    {"label": "Budget vs Actual", "icon": "fa-chart-bar", "action": "finance:budget-analysis"}
                ]
            }
        ]
    }'::jsonb,
    NULL,
    NULL,
    'Finance Management Module',
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- 3. Fleet Management Module
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    parent_module_id,
    aria_label,
    updatedat,
    isdeleted
) VALUES (
    '019c0001-0003-7000-8000-000000000003',
    'Fleet Management',
    'fa-truck-fast',
    'Vehicle fleet management with GPS tracking, maintenance, and fuel monitoring',
    '{
        "category": "operations",
        "route": "/fleet-management",
        "permissions": ["fleet.view", "fleet.manage"],
        "dataSources": {
            "fleet_vehicles": {
                "table": "fleet_vehicles",
                "columns": ["id", "vehicle_number", "registration_number", "make", "model", "status", "current_mileage", "assigned_driver_id"],
                "filters": ["status", "vehicle_type"],
                "searchable": ["vehicle_number", "registration_number", "make", "model"]
            },
            "fleet_drivers": {
                "table": "fleet_drivers",
                "columns": ["id", "driver_code", "first_name", "last_name", "phone", "license_number", "status"],
                "filters": ["status", "employment_status"],
                "searchable": ["driver_code", "first_name", "last_name", "phone"]
            },
            "fleet_maintenance": {
                "table": "fleet_maintenance",
                "columns": ["id", "maintenance_number", "vehicle_id", "maintenance_type", "scheduled_date", "status", "total_cost"],
                "filters": ["status", "maintenance_type"],
                "searchable": ["maintenance_number"]
            },
            "fleet_trips": {
                "table": "fleet_trips",
                "columns": ["id", "trip_number", "vehicle_id", "driver_id", "start_location", "end_location", "status", "distance_km"],
                "filters": ["status", "trip_type"],
                "searchable": ["trip_number"]
            },
            "fleet_fuel_logs": {
                "table": "fleet_fuel_logs",
                "columns": ["id", "vehicle_id", "log_date", "quantity_liters", "total_cost", "fuel_efficiency"],
                "filters": [],
                "searchable": []
            }
        },
        "views": [
            {
                "name": "vehicles",
                "label": "Vehicles",
                "type": "table",
                "dataSource": "fleet_vehicles",
                "rowActions": [
                    {"label": "Track Location", "icon": "fa-map-marker-alt", "action": "fleet:track-vehicle"},
                    {"label": "Schedule Maintenance", "icon": "fa-wrench", "action": "fleet:schedule-maintenance"},
                    {"label": "View History", "icon": "fa-history", "action": "fleet:vehicle-history"}
                ]
            },
            {
                "name": "drivers",
                "label": "Drivers",
                "type": "table",
                "dataSource": "fleet_drivers",
                "rowActions": [
                    {"label": "Assign Vehicle", "icon": "fa-truck", "action": "fleet:assign-vehicle"},
                    {"label": "View Performance", "icon": "fa-chart-line", "action": "fleet:driver-performance"}
                ]
            },
            {
                "name": "maintenance",
                "label": "Maintenance",
                "type": "table",
                "dataSource": "fleet_maintenance",
                "rowActions": [
                    {"label": "Complete", "icon": "fa-check", "action": "fleet:complete-maintenance", "condition": "status == \"in-progress\""},
                    {"label": "View Details", "icon": "fa-eye", "action": "view"}
                ]
            },
            {
                "name": "trips",
                "label": "Trips",
                "type": "table",
                "dataSource": "fleet_trips",
                "rowActions": [
                    {"label": "View Route", "icon": "fa-route", "action": "fleet:view-route"},
                    {"label": "Trip Report", "icon": "fa-file-alt", "action": "fleet:trip-report"}
                ]
            },
            {
                "name": "fuel",
                "label": "Fuel Logs",
                "type": "table",
                "dataSource": "fleet_fuel_logs"
            }
        ]
    }'::jsonb,
    NULL,
    NULL,
    'Fleet Management Module',
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- 4. Packaging Module
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    parent_module_id,
    aria_label,
    updatedat,
    isdeleted
) VALUES (
    '019c0001-0004-7000-8000-000000000004',
    'Packaging Management',
    'fa-box',
    'Packaging operations, templates, materials, and batch management',
    '{
        "category": "operations",
        "route": "/packaging-management",
        "permissions": ["packaging.view", "packaging.manage"],
        "dataSources": {
            "packaging_materials": {
                "table": "packaging_materials",
                "columns": ["id", "material_code", "name", "material_type", "current_stock", "status"],
                "filters": ["status", "material_type"],
                "searchable": ["material_code", "name"]
            },
            "packaging_templates": {
                "table": "packaging_templates",
                "columns": ["id", "template_code", "name", "package_type", "target_weight", "status"],
                "filters": ["status", "package_type"],
                "searchable": ["template_code", "name"]
            },
            "packaging_orders": {
                "table": "packaging_orders",
                "columns": ["id", "order_number", "product_id", "quantity_to_pack", "quantity_packed", "status", "assigned_to"],
                "filters": ["status", "priority"],
                "searchable": ["order_number"]
            },
            "packaging_batches": {
                "table": "packaging_batches",
                "columns": ["id", "batch_number", "product_id", "packed_date", "total_packages", "status"],
                "filters": ["status"],
                "searchable": ["batch_number", "lot_number"]
            }
        },
        "views": [
            {
                "name": "materials",
                "label": "Materials",
                "type": "table",
                "dataSource": "packaging_materials",
                "rowActions": [
                    {"label": "Reorder", "icon": "fa-shopping-cart", "action": "packaging:reorder-material", "condition": "current_stock < minimum_stock"},
                    {"label": "View History", "icon": "fa-history", "action": "packaging:material-history"}
                ]
            },
            {
                "name": "templates",
                "label": "Templates",
                "type": "table",
                "dataSource": "packaging_templates",
                "rowActions": [
                    {"label": "Create Order", "icon": "fa-plus", "action": "packaging:create-order"},
                    {"label": "Duplicate", "icon": "fa-copy", "action": "duplicate"}
                ]
            },
            {
                "name": "orders",
                "label": "Packaging Orders",
                "type": "table",
                "dataSource": "packaging_orders",
                "rowActions": [
                    {"label": "Start Packing", "icon": "fa-play", "action": "packaging:start-order", "condition": "status == \"pending\""},
                    {"label": "Complete", "icon": "fa-check", "action": "packaging:complete-order", "condition": "status == \"in-progress\""},
                    {"label": "View Details", "icon": "fa-eye", "action": "view"}
                ]
            },
            {
                "name": "batches",
                "label": "Batches",
                "type": "table",
                "dataSource": "packaging_batches",
                "rowActions": [
                    {"label": "Print Labels", "icon": "fa-print", "action": "packaging:print-labels"},
                    {"label": "Quality Check", "icon": "fa-clipboard-check", "action": "packaging:quality-check", "condition": "!quality_checked"}
                ]
            }
        ]
    }'::jsonb,
    NULL,
    NULL,
    'Packaging Management Module',
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- 5. Quality Assurance Module
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    parent_module_id,
    aria_label,
    updatedat,
    isdeleted
) VALUES (
    '019c0001-0005-7000-8000-000000000005',
    'Quality Assurance',
    'fa-clipboard-check',
    'Quality inspections, tests, standards, and non-conformance management',
    '{
        "category": "operations",
        "route": "/quality-assurance",
        "permissions": ["qa.view", "qa.manage"],
        "dataSources": {
            "qa_standards": {
                "table": "qa_standards",
                "columns": ["id", "standard_code", "name", "category", "status"],
                "filters": ["status", "category"],
                "searchable": ["standard_code", "name"]
            },
            "qa_inspections": {
                "table": "qa_inspections",
                "columns": ["id", "inspection_number", "inspection_type", "inspection_date", "product_id", "overall_result", "status"],
                "filters": ["status", "inspection_type", "overall_result"],
                "searchable": ["inspection_number", "batch_number"]
            },
            "qa_tests": {
                "table": "qa_tests",
                "columns": ["id", "inspection_id", "test_name", "parameter", "result", "tested_at"],
                "filters": ["result"],
                "searchable": ["test_name"]
            },
            "qa_non_conformances": {
                "table": "qa_non_conformances",
                "columns": ["id", "nc_number", "detected_date", "category", "severity", "status"],
                "filters": ["status", "severity", "category"],
                "searchable": ["nc_number"]
            }
        },
        "views": [
            {
                "name": "standards",
                "label": "QA Standards",
                "type": "table",
                "dataSource": "qa_standards",
                "rowActions": [
                    {"label": "Edit", "icon": "fa-edit", "action": "edit"},
                    {"label": "View Criteria", "icon": "fa-eye", "action": "qa:view-criteria"}
                ]
            },
            {
                "name": "inspections",
                "label": "Inspections",
                "type": "table",
                "dataSource": "qa_inspections",
                "rowActions": [
                    {"label": "Conduct Inspection", "icon": "fa-clipboard", "action": "qa:conduct-inspection", "condition": "status == \"pending\""},
                    {"label": "View Tests", "icon": "fa-flask", "action": "qa:view-tests"},
                    {"label": "Generate Report", "icon": "fa-file-pdf", "action": "qa:inspection-report", "condition": "status == \"completed\""}
                ]
            },
            {
                "name": "non_conformances",
                "label": "Non-Conformances",
                "type": "table",
                "dataSource": "qa_non_conformances",
                "rowActions": [
                    {"label": "Assign Action", "icon": "fa-user-plus", "action": "qa:assign-corrective-action", "condition": "status == \"open\""},
                    {"label": "Verify", "icon": "fa-check-circle", "action": "qa:verify-action", "condition": "status == \"action-taken\""},
                    {"label": "Close", "icon": "fa-times-circle", "action": "qa:close-nc", "condition": "status == \"verified\""}
                ]
            }
        ]
    }'::jsonb,
    NULL,
    NULL,
    'Quality Assurance Module',
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- Complete
SELECT 'Successfully added 5 modules to module registry' AS status;
