-- ============================================================================
-- PHASE 7 TASK 3: CREDIT MANAGEMENT DECLARATIVE MODULES
-- ============================================================================
-- Description: Declarative module definitions for Credit Management UI
-- Created: 2025-10-18
-- Dependencies: 
--   - migrations/phase7_credit_management_schema.sql
--   - api/controllers/CreditManagementController.php
--   - api/services/CreditManagementService.php
-- ============================================================================

-- ============================================================================
-- MODULE 1: CREDIT UTILIZATION DASHBOARD
-- ============================================================================
-- Purpose: Real-time credit health monitoring dashboard
-- Type: dashboard
-- Features:
--   - Summary widgets showing credit health distribution
--   - Charts for utilization trends and at-risk customers
--   - Unacknowledged alerts panel
--   - Auto-refresh every 30 seconds
-- ============================================================================

INSERT INTO modules (
    module_id,
    module_name,
    module_type,
    title,
    description,
    icon,
    config,
    is_active,
    created_at
) VALUES (
    'credit-utilization-dashboard',
    'CreditUtilizationDashboard',
    'dashboard',
    'Credit Utilization Dashboard',
    'Real-time credit health monitoring and risk management dashboard',
    'fa-credit-card',
    '{
        "dataSource": {
            "summaries": "/api/credit/utilization-summary/:orgId",
            "alerts": "/api/credit/alerts/:orgId"
        },
        "autoRefresh": 30000,
        "layout": {
            "type": "grid",
            "columns": 12
        },
        "widgets": [
            {
                "id": "healthy_count",
                "type": "stat",
                "title": "Healthy",
                "icon": "fa-check-circle",
                "color": "success",
                "position": { "row": 1, "col": 1, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.filter(c => c.health_status === ''healthy'').length}}",
                "description": "Customers with good credit standing"
            },
            {
                "id": "caution_count",
                "type": "stat",
                "title": "Caution",
                "icon": "fa-exclamation-triangle",
                "color": "info",
                "position": { "row": 1, "col": 3, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.filter(c => c.health_status === ''caution'').length}}",
                "description": "60-79% credit utilization"
            },
            {
                "id": "warning_count",
                "type": "stat",
                "title": "Warning",
                "icon": "fa-exclamation",
                "color": "warning",
                "position": { "row": 1, "col": 5, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.filter(c => c.health_status === ''warning'').length}}",
                "description": "80-99% credit utilization"
            },
            {
                "id": "critical_count",
                "type": "stat",
                "title": "Critical",
                "icon": "fa-ban",
                "color": "error",
                "position": { "row": 1, "col": 7, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.filter(c => c.health_status === ''critical'').length}}",
                "description": "100%+ credit utilization"
            },
            {
                "id": "on_hold_count",
                "type": "stat",
                "title": "On Hold",
                "icon": "fa-pause-circle",
                "color": "error",
                "position": { "row": 1, "col": 9, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.filter(c => c.on_hold).length}}",
                "description": "Accounts with credit hold"
            },
            {
                "id": "total_exposure",
                "type": "currency",
                "title": "Total Credit Exposure",
                "icon": "fa-dollar-sign",
                "color": "primary",
                "position": { "row": 1, "col": 11, "width": 2 },
                "dataSource": "summaries",
                "value": "{{data.reduce((sum, c) => sum + parseFloat(c.total_utilized || 0), 0)}}",
                "description": "Total outstanding across all customers",
                "format": "currency"
            },
            {
                "id": "utilization_chart",
                "type": "chart",
                "title": "Credit Utilization Distribution",
                "chartType": "doughnut",
                "position": { "row": 2, "col": 1, "width": 6 },
                "dataSource": "summaries",
                "config": {
                    "labels": ["Healthy (0-59%)", "Caution (60-79%)", "Warning (80-99%)", "Critical (100%+)", "On Hold"],
                    "datasets": [{
                        "label": "Customer Count",
                        "data": [
                            "{{data.filter(c => c.health_status === ''healthy'').length}}",
                            "{{data.filter(c => c.health_status === ''caution'').length}}",
                            "{{data.filter(c => c.health_status === ''warning'').length}}",
                            "{{data.filter(c => c.health_status === ''critical'').length}}",
                            "{{data.filter(c => c.on_hold).length}}"
                        ],
                        "backgroundColor": [
                            "#10b981",
                            "#3b82f6",
                            "#f59e0b",
                            "#ef4444",
                            "#6b7280"
                        ]
                    }]
                }
            },
            {
                "id": "top_at_risk",
                "type": "chart",
                "title": "Top 10 At-Risk Customers",
                "chartType": "horizontalBar",
                "position": { "row": 2, "col": 7, "width": 6 },
                "dataSource": "summaries",
                "config": {
                    "labels": "{{data.filter(c => c.utilization_percentage >= 80).sort((a,b) => b.utilization_percentage - a.utilization_percentage).slice(0,10).map(c => c.customer_name)}}",
                    "datasets": [{
                        "label": "Utilization %",
                        "data": "{{data.filter(c => c.utilization_percentage >= 80).sort((a,b) => b.utilization_percentage - a.utilization_percentage).slice(0,10).map(c => c.utilization_percentage)}}",
                        "backgroundColor": "#ef4444"
                    }]
                }
            },
            {
                "id": "alerts_panel",
                "type": "list",
                "title": "Unacknowledged Alerts",
                "icon": "fa-bell",
                "position": { "row": 3, "col": 1, "width": 12 },
                "dataSource": "alerts",
                "emptyMessage": "No unacknowledged alerts",
                "itemTemplate": "<div class=\"alert alert-{{item.severity}} mb-2\"><div class=\"flex items-center justify-between\"><div><strong>{{item.customer_name}}</strong> - {{item.alert_type}}</div><div><span class=\"badge badge-{{item.severity}}\">{{item.severity}}</span><button class=\"btn btn-sm btn-ghost ml-2\" data-action=\"credit:acknowledge-alert\" data-alert-id=\"{{item.alert_id}}\">Acknowledge</button></div></div><div class=\"text-sm mt-1\">{{item.message}}</div><div class=\"text-xs text-gray-500 mt-1\">{{item.created_at | formatDateTime}}</div></div>",
                "maxItems": 10
            },
            {
                "id": "recent_holds",
                "type": "table",
                "title": "Recent Credit Holds",
                "icon": "fa-pause-circle",
                "position": { "row": 4, "col": 1, "width": 12 },
                "dataSource": "summaries",
                "filter": "{{data.filter(c => c.on_hold)}}",
                "columns": [
                    {
                        "field": "customer_name",
                        "label": "Customer",
                        "sortable": true
                    },
                    {
                        "field": "credit_limit",
                        "label": "Credit Limit",
                        "type": "currency",
                        "sortable": true
                    },
                    {
                        "field": "total_utilized",
                        "label": "Utilized",
                        "type": "currency",
                        "sortable": true
                    },
                    {
                        "field": "utilization_percentage",
                        "label": "Utilization %",
                        "type": "percentage",
                        "sortable": true
                    },
                    {
                        "field": "hold_reason",
                        "label": "Hold Reason",
                        "sortable": false
                    },
                    {
                        "field": "hold_applied_at",
                        "label": "Hold Applied",
                        "type": "datetime",
                        "sortable": true
                    }
                ],
                "rowActions": [
                    {
                        "label": "Remove Hold",
                        "icon": "fa-play-circle",
                        "action": "credit:remove-hold",
                        "style": "success",
                        "confirm": "Remove credit hold for this customer?"
                    },
                    {
                        "label": "View History",
                        "icon": "fa-history",
                        "action": "credit:view-history",
                        "style": "ghost"
                    }
                ],
                "maxRows": 10
            }
        ]
    }'::jsonb,
    true,
    NOW()
);

-- ============================================================================
-- MODULE 2: CREDIT APPROVAL REQUESTS
-- ============================================================================
-- Purpose: Manage credit approval requests for over-limit transactions
-- Type: list
-- Features:
--   - Pending approval requests with priority sorting
--   - Filters by priority, request type, customer
--   - Row actions: approve, reject, view details
--   - Bulk actions: approve selected, reject selected
--   - Auto-refresh every 30 seconds
-- ============================================================================

INSERT INTO modules (
    module_id,
    module_name,
    module_type,
    title,
    description,
    icon,
    config,
    is_active,
    created_at
) VALUES (
    'credit-approval-requests',
    'CreditApprovalRequests',
    'list',
    'Credit Approval Requests',
    'Manage pending credit approval requests for over-limit transactions',
    'fa-check-square',
    '{
        "dataSource": "/api/credit/approval/pending/:orgId",
        "autoRefresh": 30000,
        "pagination": {
            "enabled": true,
            "pageSize": 50,
            "pageSizeOptions": [25, 50, 100]
        },
        "columns": [
            {
                "field": "request_id",
                "label": "Request ID",
                "type": "text",
                "width": "120px",
                "sortable": true,
                "template": "<code class=\"text-xs\">{{value.substring(0,8)}}</code>"
            },
            {
                "field": "priority",
                "label": "Priority",
                "type": "badge",
                "width": "100px",
                "sortable": true,
                "template": "<span class=\"badge badge-{{value === ''high'' ? ''error'' : value === ''medium'' ? ''warning'' : ''info''}}\">{{value}}</span>"
            },
            {
                "field": "customer_name",
                "label": "Customer",
                "type": "text",
                "width": "200px",
                "sortable": true
            },
            {
                "field": "request_type",
                "label": "Request Type",
                "type": "text",
                "width": "150px",
                "sortable": true,
                "template": "{{value.replace(''_'', '' '').toUpperCase()}}"
            },
            {
                "field": "current_limit",
                "label": "Current Limit",
                "type": "currency",
                "width": "120px",
                "sortable": true
            },
            {
                "field": "requested_amount",
                "label": "Requested",
                "type": "currency",
                "width": "120px",
                "sortable": true
            },
            {
                "field": "over_limit_amount",
                "label": "Over Limit By",
                "type": "currency",
                "width": "120px",
                "sortable": true,
                "template": "<span class=\"text-error font-semibold\">{{value | currency}}</span>"
            },
            {
                "field": "requested_by_name",
                "label": "Requested By",
                "type": "text",
                "width": "150px",
                "sortable": true
            },
            {
                "field": "expires_at",
                "label": "Expires",
                "type": "datetime",
                "width": "150px",
                "sortable": true,
                "template": "{{value | relativeTime}}"
            },
            {
                "field": "created_at",
                "label": "Created",
                "type": "datetime",
                "width": "150px",
                "sortable": true
            }
        ],
        "filters": [
            {
                "field": "priority",
                "label": "Priority",
                "type": "select",
                "options": [
                    { "value": "", "label": "All Priorities" },
                    { "value": "high", "label": "High" },
                    { "value": "medium", "label": "Medium" },
                    { "value": "low", "label": "Low" }
                ]
            },
            {
                "field": "request_type",
                "label": "Request Type",
                "type": "select",
                "options": [
                    { "value": "", "label": "All Types" },
                    { "value": "over_limit_purchase", "label": "Over Limit Purchase" },
                    { "value": "limit_increase", "label": "Limit Increase" },
                    { "value": "temporary_increase", "label": "Temporary Increase" },
                    { "value": "hold_removal", "label": "Hold Removal" }
                ]
            },
            {
                "field": "customer_id",
                "label": "Customer",
                "type": "select",
                "dataSource": "/api/customers/list/:orgId",
                "valueField": "customer_id",
                "labelField": "customer_name"
            },
            {
                "field": "date_from",
                "label": "From Date",
                "type": "date"
            },
            {
                "field": "date_to",
                "label": "To Date",
                "type": "date"
            }
        ],
        "rowActions": [
            {
                "label": "Approve",
                "icon": "fa-check",
                "action": "credit:approve-request",
                "style": "success",
                "confirm": "Approve this credit request?",
                "condition": "status === ''pending''"
            },
            {
                "label": "Reject",
                "icon": "fa-times",
                "action": "credit:reject-request",
                "style": "error",
                "confirm": "Reject this credit request?",
                "condition": "status === ''pending''"
            },
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "credit:view-request-details",
                "style": "ghost"
            },
            {
                "label": "View Customer Credit",
                "icon": "fa-user",
                "action": "credit:view-customer-credit",
                "style": "ghost"
            },
            {
                "label": "View History",
                "icon": "fa-history",
                "action": "credit:view-history",
                "style": "ghost"
            }
        ],
        "bulkActions": [
            {
                "label": "Approve Selected",
                "icon": "fa-check-double",
                "action": "credit:approve-bulk",
                "style": "success",
                "confirm": "Approve {{count}} selected requests?"
            },
            {
                "label": "Reject Selected",
                "icon": "fa-times-circle",
                "action": "credit:reject-bulk",
                "style": "error",
                "confirm": "Reject {{count}} selected requests?"
            },
            {
                "label": "Export CSV",
                "icon": "fa-file-csv",
                "action": "credit:export-csv",
                "style": "ghost"
            }
        ],
        "summaryWidgets": [
            {
                "id": "total_pending",
                "type": "stat",
                "title": "Total Pending",
                "icon": "fa-clock",
                "color": "info",
                "value": "{{data.length}}",
                "description": "Approval requests awaiting review"
            },
            {
                "id": "high_priority",
                "type": "stat",
                "title": "High Priority",
                "icon": "fa-exclamation-triangle",
                "color": "error",
                "value": "{{data.filter(r => r.priority === ''high'').length}}",
                "description": "Urgent requests requiring attention"
            },
            {
                "id": "total_requested",
                "type": "currency",
                "title": "Total Requested",
                "icon": "fa-dollar-sign",
                "color": "warning",
                "value": "{{data.reduce((sum, r) => sum + parseFloat(r.requested_amount || 0), 0)}}",
                "description": "Total amount in pending requests"
            },
            {
                "id": "expiring_soon",
                "type": "stat",
                "title": "Expiring Soon",
                "icon": "fa-hourglass-end",
                "color": "warning",
                "value": "{{data.filter(r => new Date(r.expires_at) - new Date() < 24*60*60*1000).length}}",
                "description": "Requests expiring within 24 hours"
            },
            {
                "id": "avg_over_limit",
                "type": "currency",
                "title": "Avg Over Limit",
                "icon": "fa-chart-line",
                "color": "primary",
                "value": "{{data.length > 0 ? data.reduce((sum, r) => sum + parseFloat(r.over_limit_amount || 0), 0) / data.length : 0}}",
                "description": "Average over-limit amount"
            }
        ],
        "defaultSort": {
            "field": "priority",
            "order": "desc",
            "thenBy": [
                { "field": "expires_at", "order": "asc" },
                { "field": "created_at", "order": "asc" }
            ]
        },
        "emptyStateMessage": "No pending credit approval requests",
        "emptyStateIcon": "fa-check-circle",
        "emptyStateAction": {
            "label": "View All Requests",
            "action": "credit:view-all-requests"
        }
    }'::jsonb,
    true,
    NOW()
);

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================
-- Run these queries to verify the modules were created successfully

-- Check module creation
SELECT 
    module_id,
    module_name,
    module_type,
    title,
    is_active,
    created_at
FROM modules
WHERE module_id IN ('credit-utilization-dashboard', 'credit-approval-requests')
ORDER BY module_id;

-- Verify JSON structure
SELECT 
    module_id,
    jsonb_pretty(config) as module_config
FROM modules
WHERE module_id = 'credit-utilization-dashboard';

SELECT 
    module_id,
    jsonb_pretty(config) as module_config
FROM modules
WHERE module_id = 'credit-approval-requests';

-- ============================================================================
-- ROLLBACK (if needed)
-- ============================================================================
-- To remove these modules, uncomment and run:
/*
DELETE FROM modules 
WHERE module_id IN ('credit-utilization-dashboard', 'credit-approval-requests');
*/
