-- ============================================================================
-- Phase 7 Task 1: Aging Report Modules
-- ============================================================================
-- Creates declarative modules for invoice aging reports
--
-- Modules:
--   1. AgingReportDashboard - Main aging dashboard with charts
--   2. AgingReportDetail - Detailed list view with filters
--   3. AgingReportByCustomer - Customer-focused aging view
--
-- Created: October 17, 2025
-- Status: Production Ready
-- ============================================================================

-- Prerequisites check
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_tables WHERE tablename = 'modules') THEN
        RAISE EXCEPTION 'modules table does not exist.';
    END IF;
    
    IF NOT EXISTS (SELECT FROM pg_views WHERE viewname = 'invoice_aging') THEN
        RAISE EXCEPTION 'invoice_aging view does not exist. Run phase7_task1_aging_reports.sql first.';
    END IF;
END $$;

-- Delete existing aging modules (for idempotency)
DELETE FROM modules WHERE name IN (
    'AgingReportDashboard',
    'AgingReportDetail',
    'AgingReportByCustomer'
);

-- ============================================================================
-- Module 1: AgingReportDashboard
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'AgingReportDashboard',
    'fa-chart-pie',
    'Invoice aging analysis dashboard with visualizations',
    '{
        "type": "dashboard",
        "refreshInterval": 60000,
        "layout": "grid",
        "widgets": [
            {
                "id": "aging_summary_stats",
                "type": "stats",
                "title": "Aging Overview",
                "col": 1,
                "row": 1,
                "width": 4,
                "height": 1,
                "dataSource": "/api/aging-report/summary/{org_id}",
                "stats": [
                    {
                        "key": "totals.total_invoices",
                        "label": "Outstanding Invoices",
                        "icon": "fa-file-invoice",
                        "color": "primary"
                    },
                    {
                        "key": "totals.total_balance",
                        "label": "Total Outstanding",
                        "icon": "fa-dollar-sign",
                        "color": "warning",
                        "format": "currency"
                    },
                    {
                        "key": "totals.avg_days_overdue",
                        "label": "Avg Days Overdue",
                        "icon": "fa-calendar-alt",
                        "color": "error",
                        "format": "number",
                        "decimals": 1
                    }
                ]
            },
            {
                "id": "aging_buckets_chart",
                "type": "chart",
                "title": "Aging by Bucket",
                "col": 1,
                "row": 2,
                "width": 2,
                "height": 2,
                "dataSource": "/api/aging-report/summary/{org_id}",
                "chartType": "pie",
                "dataKey": "summary",
                "labels": "age_bucket",
                "values": "total_balance",
                "colors": ["#10b981", "#f59e0b", "#f97316", "#dc2626", "#7c2d12"],
                "legend": true,
                "tooltips": true
            },
            {
                "id": "aging_buckets_bar",
                "type": "chart",
                "title": "Invoice Count by Age",
                "col": 3,
                "row": 2,
                "width": 2,
                "height": 2,
                "dataSource": "/api/aging-report/summary/{org_id}",
                "chartType": "bar",
                "dataKey": "summary",
                "xAxis": "age_bucket",
                "yAxis": ["invoice_count"],
                "colors": {
                    "invoice_count": "#3b82f6"
                }
            },
            {
                "id": "top_overdue_customers",
                "type": "list",
                "title": "Top Overdue Customers",
                "col": 1,
                "row": 4,
                "width": 4,
                "height": 2,
                "dataSource": "/api/aging-report/top-overdue/{org_id}?limit=10",
                "columns": [
                    {
                        "key": "customer_name",
                        "label": "Customer",
                        "type": "text"
                    },
                    {
                        "key": "overdue_count",
                        "label": "Overdue Invoices",
                        "type": "number"
                    },
                    {
                        "key": "total_overdue",
                        "label": "Total Overdue",
                        "type": "currency"
                    },
                    {
                        "key": "max_days_overdue",
                        "label": "Max Days",
                        "type": "number"
                    },
                    {
                        "key": "severely_overdue_amount",
                        "label": "90+ Days",
                        "type": "currency",
                        "badge": true,
                        "badgeColor": "error"
                    }
                ],
                "actions": [
                    {
                        "label": "View All",
                        "link": "/aging-report/detail"
                    }
                ]
            }
        ]
    }'::jsonb
);

-- ============================================================================
-- Module 2: AgingReportDetail
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'AgingReportDetail',
    'fa-list-alt',
    'Detailed invoice aging report with filters and export',
    '{
        "type": "list",
        "dataSource": "/api/aging-report/detail/{org_id}",
        "refreshInterval": 60000,
        "pagination": true,
        "pageSize": 50,
        "exportable": true,
        "filters": [
            {
                "key": "age_bucket",
                "label": "Age Bucket",
                "type": "select",
                "options": [
                    {"value": "Current", "label": "Current"},
                    {"value": "1-30 days", "label": "1-30 Days"},
                    {"value": "31-60 days", "label": "31-60 Days"},
                    {"value": "61-90 days", "label": "61-90 Days"},
                    {"value": "90+ days", "label": "90+ Days"}
                ]
            },
            {
                "key": "customer_id",
                "label": "Customer",
                "type": "select",
                "dataSource": "/api/customers/list/{org_id}",
                "valueKey": "customer_id",
                "labelKey": "customer_name"
            },
            {
                "key": "status",
                "label": "Status",
                "type": "select",
                "options": [
                    {"value": "finalized", "label": "Finalized"},
                    {"value": "overdue", "label": "Overdue"},
                    {"value": "paid", "label": "Paid"}
                ]
            },
            {
                "key": "min_balance",
                "label": "Min Balance",
                "type": "number",
                "min": 0,
                "step": 100
            }
        ],
        "columns": [
            {
                "key": "invoice_number",
                "label": "Invoice #",
                "type": "text",
                "sortable": true,
                "link": "/invoices/{invoice_id}"
            },
            {
                "key": "customer_name",
                "label": "Customer",
                "type": "text",
                "sortable": true
            },
            {
                "key": "invoice_date",
                "label": "Invoice Date",
                "type": "date",
                "sortable": true
            },
            {
                "key": "due_date",
                "label": "Due Date",
                "type": "date",
                "sortable": true
            },
            {
                "key": "total_amount",
                "label": "Total",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "amount_paid",
                "label": "Paid",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "balance_due",
                "label": "Balance",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "days_overdue",
                "label": "Days Overdue",
                "type": "number",
                "sortable": true,
                "badge": true,
                "badgeConditions": [
                    {"condition": "value == 0", "class": "badge-success"},
                    {"condition": "value <= 30", "class": "badge-warning"},
                    {"condition": "value <= 60", "class": "badge-warning"},
                    {"condition": "value <= 90", "class": "badge-error"},
                    {"condition": "value > 90", "class": "badge-error"}
                ]
            },
            {
                "key": "age_bucket",
                "label": "Age Bucket",
                "type": "badge",
                "sortable": true,
                "badges": {
                    "Current": {"class": "badge-success"},
                    "1-30 days": {"class": "badge-info"},
                    "31-60 days": {"class": "badge-warning"},
                    "61-90 days": {"class": "badge-warning"},
                    "90+ days": {"class": "badge-error"}
                }
            }
        ],
        "rowActions": [
            {
                "label": "View Invoice",
                "icon": "fa-eye",
                "action": "invoice:view",
                "link": "/invoices/{invoice_id}"
            },
            {
                "label": "Send Reminder",
                "icon": "fa-envelope",
                "action": "invoice:send-reminder",
                "condition": "is_overdue == true",
                "confirm": "Send payment reminder to {customer_name}?"
            },
            {
                "label": "Record Payment",
                "icon": "fa-dollar-sign",
                "action": "invoice:record-payment",
                "openModal": true
            }
        ],
        "bulkActions": [
            {
                "label": "Send Reminders",
                "icon": "fa-envelope",
                "action": "invoice:send-bulk-reminders",
                "confirm": "Send payment reminders for {count} selected invoice(s)?"
            },
            {
                "label": "Export to CSV",
                "icon": "fa-file-csv",
                "action": "export:csv",
                "url": "/api/aging-report/export/csv/{org_id}"
            }
        ]
    }'::jsonb
);

-- ============================================================================
-- Module 3: AgingReportByCustomer
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'AgingReportByCustomer',
    'fa-users',
    'Invoice aging grouped by customer',
    '{
        "type": "list",
        "dataSource": "/api/aging-report/by-customer/{org_id}",
        "refreshInterval": 60000,
        "pagination": true,
        "pageSize": 50,
        "exportable": true,
        "columns": [
            {
                "key": "customer_name",
                "label": "Customer",
                "type": "text",
                "sortable": true
            },
            {
                "key": "invoice_count",
                "label": "Invoices",
                "type": "number",
                "sortable": true
            },
            {
                "key": "total_balance",
                "label": "Total Outstanding",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "current_balance",
                "label": "Current",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "days_1_30",
                "label": "1-30 Days",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "days_31_60",
                "label": "31-60 Days",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "days_61_90",
                "label": "61-90 Days",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "days_90_plus",
                "label": "90+ Days",
                "type": "currency",
                "sortable": true,
                "badge": true,
                "badgeConditions": [
                    {"condition": "value == 0", "class": "badge-success"},
                    {"condition": "value > 0", "class": "badge-error"}
                ]
            },
            {
                "key": "max_days_overdue",
                "label": "Max Days",
                "type": "number",
                "sortable": true
            }
        ],
        "rowActions": [
            {
                "label": "View Customer",
                "icon": "fa-user",
                "action": "customer:view",
                "link": "/customers/{customer_id}"
            },
            {
                "label": "View Invoices",
                "icon": "fa-file-invoice",
                "action": "customer:view-invoices",
                "link": "/aging-report/detail?customer_id={customer_id}"
            },
            {
                "label": "Send Statement",
                "icon": "fa-envelope",
                "action": "customer:send-statement",
                "confirm": "Send account statement to {customer_name}?"
            }
        ],
        "bulkActions": [
            {
                "label": "Send Statements",
                "icon": "fa-envelope",
                "action": "customer:send-bulk-statements",
                "confirm": "Send account statements to {count} selected customer(s)?"
            }
        ]
    }'::jsonb
);

-- ============================================================================
-- VERIFICATION
-- ============================================================================

-- Verify modules
DO $$
DECLARE
    module_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO module_count FROM modules WHERE name LIKE 'AgingReport%';
    IF module_count <> 3 THEN
        RAISE WARNING 'Expected 3 Aging Report modules, found %', module_count;
    ELSE
        RAISE NOTICE '✅ Successfully inserted 3 Aging Report modules';
    END IF;
END $$;

-- Display results
SELECT 
    LEFT(module_id::text, 8) as id,
    name,
    icon,
    description
FROM modules 
WHERE name LIKE 'AgingReport%'
ORDER BY name;

-- ============================================================================
-- END OF PHASE 7 TASK 1 - AGING REPORT MODULES
-- ============================================================================
