-- ============================================================================
-- PHASE 7 TASK 5: FINANCIAL REPORTS DECLARATIVE MODULES
-- ============================================================================
-- Description: Declarative UI modules for financial reporting (Phase 4 pattern)
-- Dependencies: 
--   - phase7_financial_reports_schema.sql (database views)
--   - api/controllers/ReportsController.php (API endpoints)
--   - FrontEnd/js/core/ModuleLoader.js
--   - FrontEnd/js/core/ActionRegistry.js
-- Created: October 18, 2025
-- ============================================================================

-- ============================================================================
-- MODULE 1: FINANCIAL REPORTS DASHBOARD
-- ============================================================================
-- Purpose: Executive dashboard with key financial metrics and trends
-- Type: dashboard
-- Features:
--   - 8 summary widgets (stats, gauges, metrics)
--   - 4 charts (revenue trends, top customers, top products, payment methods)
--   - 3 quick link lists (recent invoices, outstanding AR, tax summary)
--   - Auto-refresh every 60 seconds
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    title,
    icon,
    type,
    category,
    description,
    config,
    is_active,
    created_at
) VALUES (
    'financial-reports-dashboard',
    'FinancialReportsDashboard',
    'Financial Reports Dashboard',
    'fa-chart-line',
    'dashboard',
    'Finance',
    'Executive financial dashboard with revenue, collections, and efficiency metrics',
    '{
        "layout": "grid",
        "columns": 3,
        "autoRefresh": 60000,
        "widgets": [
            {
                "id": "total-revenue-widget",
                "type": "stat",
                "title": "Total Revenue",
                "icon": "fa-dollar-sign",
                "color": "success",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.summary.total_revenue",
                "format": "currency",
                "trend": {
                    "dataPath": "data.summary.revenue_growth",
                    "format": "percentage"
                },
                "size": "large",
                "position": {"row": 1, "col": 1}
            },
            {
                "id": "outstanding-ar-widget",
                "type": "stat",
                "title": "Outstanding AR",
                "icon": "fa-file-invoice-dollar",
                "color": "warning",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.summary.total_outstanding",
                "format": "currency",
                "subtitle": {
                    "dataPath": "data.summary.overdue_count",
                    "format": "count",
                    "suffix": " overdue"
                },
                "size": "large",
                "position": {"row": 1, "col": 2}
            },
            {
                "id": "collection-rate-widget",
                "type": "gauge",
                "title": "Collection Rate",
                "icon": "fa-percent",
                "dataSource": "/api/reports/collection-efficiency/:orgId",
                "dataPath": "data.collection_rate",
                "format": "percentage",
                "ranges": [
                    {"min": 0, "max": 50, "color": "error", "label": "Poor"},
                    {"min": 50, "max": 75, "color": "warning", "label": "Fair"},
                    {"min": 75, "max": 90, "color": "info", "label": "Good"},
                    {"min": 90, "max": 100, "color": "success", "label": "Excellent"}
                ],
                "size": "large",
                "position": {"row": 1, "col": 3}
            },
            {
                "id": "dso-widget",
                "type": "metric",
                "title": "Days Sales Outstanding",
                "icon": "fa-calendar-days",
                "color": "info",
                "dataSource": "/api/reports/collection-efficiency/:orgId",
                "dataPath": "data.days_sales_outstanding",
                "format": "number",
                "suffix": " days",
                "size": "medium",
                "position": {"row": 2, "col": 1}
            },
            {
                "id": "avg-collection-time-widget",
                "type": "metric",
                "title": "Avg Collection Time",
                "icon": "fa-clock",
                "color": "info",
                "dataSource": "/api/reports/collection-efficiency/:orgId",
                "dataPath": "data.avg_days_to_collect",
                "format": "number",
                "suffix": " days",
                "size": "medium",
                "position": {"row": 2, "col": 2}
            },
            {
                "id": "tax-collected-widget",
                "type": "stat",
                "title": "Tax Collected (YTD)",
                "icon": "fa-receipt",
                "color": "primary",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.summary.tax_collected",
                "format": "currency",
                "size": "medium",
                "position": {"row": 2, "col": 3}
            },
            {
                "id": "monthly-revenue-widget",
                "type": "stat",
                "title": "Revenue (This Month)",
                "icon": "fa-calendar-check",
                "color": "accent",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.summary.current_month_revenue",
                "format": "currency",
                "trend": {
                    "dataPath": "data.summary.month_over_month_growth",
                    "format": "percentage"
                },
                "size": "medium",
                "position": {"row": 3, "col": 1}
            },
            {
                "id": "invoice-count-widget",
                "type": "stat",
                "title": "Invoices (This Month)",
                "icon": "fa-file-invoice",
                "color": "secondary",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.summary.current_month_invoices",
                "format": "count",
                "size": "medium",
                "position": {"row": 3, "col": 2}
            },
            {
                "id": "revenue-trends-chart",
                "type": "chart",
                "title": "Revenue Trends (6 Months)",
                "chartType": "line",
                "dataSource": "/api/reports/revenue-trends/:orgId?months=6",
                "xAxis": {
                    "dataPath": "data.month_name",
                    "label": "Month"
                },
                "yAxis": [
                    {
                        "dataPath": "data.total_revenue",
                        "label": "Total Revenue",
                        "color": "#10b981",
                        "format": "currency"
                    },
                    {
                        "dataPath": "data.paid_revenue",
                        "label": "Paid Revenue",
                        "color": "#3b82f6",
                        "format": "currency"
                    }
                ],
                "size": "xlarge",
                "position": {"row": 4, "col": 1, "colspan": 2}
            },
            {
                "id": "top-customers-chart",
                "type": "chart",
                "title": "Top 10 Customers",
                "chartType": "bar",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.top_customers",
                "xAxis": {
                    "dataPath": "customer_name",
                    "label": "Customer"
                },
                "yAxis": [
                    {
                        "dataPath": "total_revenue",
                        "label": "Revenue",
                        "color": "#8b5cf6",
                        "format": "currency"
                    }
                ],
                "size": "xlarge",
                "position": {"row": 4, "col": 3, "colspan": 1, "rowspan": 2}
            },
            {
                "id": "payment-methods-chart",
                "type": "chart",
                "title": "Collections by Payment Method",
                "chartType": "pie",
                "dataSource": "/api/reports/payment-collection/:orgId",
                "dataPath": "data",
                "labelField": "payment_method",
                "valueField": "total_collected",
                "format": "currency",
                "colors": ["#3b82f6", "#10b981", "#f59e0b", "#ef4444", "#8b5cf6"],
                "size": "large",
                "position": {"row": 5, "col": 1}
            },
            {
                "id": "top-products-chart",
                "type": "chart",
                "title": "Top 10 Products",
                "chartType": "bar",
                "dataSource": "/api/reports/dashboard-summary/:orgId",
                "dataPath": "data.top_products",
                "xAxis": {
                    "dataPath": "product_name",
                    "label": "Product"
                },
                "yAxis": [
                    {
                        "dataPath": "total_revenue",
                        "label": "Revenue",
                        "color": "#f59e0b",
                        "format": "currency"
                    }
                ],
                "size": "large",
                "position": {"row": 5, "col": 2}
            },
            {
                "id": "outstanding-invoices-list",
                "type": "list",
                "title": "Outstanding Invoices (Top 10)",
                "icon": "fa-exclamation-triangle",
                "dataSource": "/api/reports/outstanding-invoices/:orgId?limit=10",
                "dataPath": "data",
                "columns": [
                    {
                        "field": "customer_name",
                        "label": "Customer",
                        "width": "40%"
                    },
                    {
                        "field": "total_outstanding",
                        "label": "Outstanding",
                        "format": "currency",
                        "align": "right",
                        "width": "30%"
                    },
                    {
                        "field": "overdue_0_30",
                        "label": "0-30 Days",
                        "format": "currency",
                        "align": "right",
                        "width": "30%"
                    }
                ],
                "actions": [
                    {
                        "label": "View All",
                        "action": "reports:view-outstanding-invoices",
                        "icon": "fa-arrow-right"
                    }
                ],
                "size": "large",
                "position": {"row": 6, "col": 1}
            },
            {
                "id": "tax-summary-list",
                "type": "list",
                "title": "Sales Tax Summary",
                "icon": "fa-receipt",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "data",
                "columns": [
                    {
                        "field": "tax_rate",
                        "label": "Tax Rate",
                        "format": "percentage",
                        "width": "30%"
                    },
                    {
                        "field": "tax_collected_paid",
                        "label": "Collected",
                        "format": "currency",
                        "align": "right",
                        "width": "35%"
                    },
                    {
                        "field": "tax_collected_pending",
                        "label": "Pending",
                        "format": "currency",
                        "align": "right",
                        "width": "35%"
                    }
                ],
                "actions": [
                    {
                        "label": "View Details",
                        "action": "reports:view-tax-summary",
                        "icon": "fa-arrow-right"
                    }
                ],
                "size": "large",
                "position": {"row": 6, "col": 2}
            },
            {
                "id": "collection-efficiency-list",
                "type": "table",
                "title": "Collection Efficiency Metrics",
                "icon": "fa-chart-line",
                "dataSource": "/api/reports/collection-efficiency/:orgId",
                "dataPath": "data",
                "columns": [
                    {
                        "field": "metric_name",
                        "label": "Metric",
                        "width": "50%"
                    },
                    {
                        "field": "metric_value",
                        "label": "Value",
                        "align": "right",
                        "width": "50%"
                    }
                ],
                "transform": "efficiency_to_rows",
                "size": "large",
                "position": {"row": 6, "col": 3}
            }
        ],
        "quickActions": [
            {
                "label": "Export Dashboard",
                "action": "reports:export-dashboard-pdf",
                "icon": "fa-file-pdf",
                "color": "error"
            },
            {
                "label": "Email Report",
                "action": "reports:email-dashboard",
                "icon": "fa-envelope",
                "color": "primary"
            },
            {
                "label": "Schedule Report",
                "action": "reports:schedule-dashboard",
                "icon": "fa-calendar-plus",
                "color": "info"
            }
        ]
    }',
    true,
    NOW()
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE 2: CUSTOMER REVENUE REPORT
-- ============================================================================
-- Purpose: Detailed customer revenue analysis with drill-down
-- Type: list
-- Features:
--   - 10 columns showing revenue metrics by customer
--   - 6 filters (customer status, date range, min revenue)
--   - 5 row actions (view details, drill-down, send statement, export)
--   - 3 bulk actions (export selected, email reports, analyze trends)
--   - 4 summary widgets (total revenue, customer count, avg revenue, top customer)
--   - Auto-refresh every 120 seconds
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    title,
    icon,
    type,
    category,
    description,
    config,
    is_active,
    created_at
) VALUES (
    'customer-revenue-report',
    'CustomerRevenueReport',
    'Customer Revenue Report',
    'fa-users-rectangle',
    'list',
    'Finance',
    'Detailed revenue analysis by customer with trends and status classification',
    '{
        "dataSource": "/api/reports/revenue-by-customer/:orgId",
        "autoRefresh": 120000,
        "pagination": {
            "enabled": true,
            "pageSize": 25,
            "pageSizes": [10, 25, 50, 100]
        },
        "columns": [
            {
                "field": "customer_name",
                "label": "Customer",
                "sortable": true,
                "searchable": true,
                "width": "20%",
                "render": "link",
                "linkAction": "reports:view-customer-details"
            },
            {
                "field": "customer_status",
                "label": "Status",
                "sortable": true,
                "width": "10%",
                "render": "badge",
                "badgeColors": {
                    "active": "success",
                    "recent": "info",
                    "inactive": "warning",
                    "dormant": "error"
                }
            },
            {
                "field": "total_revenue",
                "label": "Total Revenue",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "revenue_ytd",
                "label": "Revenue YTD",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "revenue_90_days",
                "label": "Last 90 Days",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "revenue_30_days",
                "label": "Last 30 Days",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "invoice_count",
                "label": "Invoices",
                "sortable": true,
                "width": "8%",
                "format": "count",
                "align": "center"
            },
            {
                "field": "avg_invoice_amount",
                "label": "Avg Invoice",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "last_invoice_date",
                "label": "Last Invoice",
                "sortable": true,
                "width": "10%",
                "format": "date"
            }
        ],
        "filters": [
            {
                "field": "customer_status",
                "label": "Customer Status",
                "type": "select",
                "options": [
                    {"value": "", "label": "All Statuses"},
                    {"value": "active", "label": "Active"},
                    {"value": "recent", "label": "Recent"},
                    {"value": "inactive", "label": "Inactive"},
                    {"value": "dormant", "label": "Dormant"}
                ],
                "default": ""
            },
            {
                "field": "min_revenue",
                "label": "Min Revenue",
                "type": "number",
                "placeholder": "0.00"
            },
            {
                "field": "date_from",
                "label": "Date From",
                "type": "date"
            },
            {
                "field": "date_to",
                "label": "Date To",
                "type": "date"
            },
            {
                "field": "search",
                "label": "Search",
                "type": "text",
                "placeholder": "Search customer name..."
            }
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "reports:view-customer-details",
                "color": "info"
            },
            {
                "label": "Invoice Drill-Down",
                "icon": "fa-list",
                "action": "reports:drill-down-invoices",
                "color": "primary"
            },
            {
                "label": "Send Statement",
                "icon": "fa-envelope",
                "action": "reports:send-customer-statement",
                "color": "accent",
                "confirm": "Send statement to this customer?"
            },
            {
                "label": "Export Customer Report",
                "icon": "fa-file-export",
                "action": "reports:export-customer-csv",
                "color": "secondary"
            }
        ],
        "bulkActions": [
            {
                "label": "Export Selected",
                "icon": "fa-file-csv",
                "action": "reports:export-selected-csv",
                "color": "secondary"
            },
            {
                "label": "Send Statements",
                "icon": "fa-paper-plane",
                "action": "reports:send-statements-bulk",
                "color": "primary",
                "confirm": "Send statements to {count} customers?"
            },
            {
                "label": "Analyze Trends",
                "icon": "fa-chart-line",
                "action": "reports:analyze-customer-trends",
                "color": "info"
            }
        ],
        "summaryWidgets": [
            {
                "id": "total-customer-revenue",
                "type": "stat",
                "title": "Total Revenue",
                "icon": "fa-dollar-sign",
                "color": "success",
                "dataPath": "totals.total_revenue",
                "format": "currency"
            },
            {
                "id": "customer-count",
                "type": "stat",
                "title": "Total Customers",
                "icon": "fa-users",
                "color": "info",
                "dataPath": "totals.customer_count",
                "format": "count"
            },
            {
                "id": "avg-customer-revenue",
                "type": "stat",
                "title": "Avg Revenue per Customer",
                "icon": "fa-chart-bar",
                "color": "primary",
                "dataPath": "totals.avg_revenue",
                "format": "currency"
            },
            {
                "id": "top-customer",
                "type": "stat",
                "title": "Top Customer",
                "icon": "fa-trophy",
                "color": "warning",
                "dataPath": "totals.top_customer_name",
                "subtitle": {
                    "dataPath": "totals.top_customer_revenue",
                    "format": "currency"
                }
            }
        ],
        "exportOptions": [
            {
                "label": "Export to CSV",
                "action": "reports:export-csv",
                "icon": "fa-file-csv",
                "params": {"reportType": "revenue-by-customer"}
            },
            {
                "label": "Export to PDF",
                "action": "reports:export-pdf",
                "icon": "fa-file-pdf",
                "params": {"reportType": "revenue-by-customer"}
            }
        ]
    }',
    true,
    NOW()
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE 3: PRODUCT REVENUE REPORT
-- ============================================================================
-- Purpose: Product/service performance analysis
-- Type: list
-- Features:
--   - 10 columns showing product metrics
--   - 5 filters (category, active status, min revenue)
--   - 4 row actions (view details, drill-down, analyze)
--   - 3 bulk actions (export, compare, categorize)
--   - 4 summary widgets
--   - Auto-refresh every 120 seconds
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    title,
    icon,
    type,
    category,
    description,
    config,
    is_active,
    created_at
) VALUES (
    'product-revenue-report',
    'ProductRevenueReport',
    'Product Revenue Report',
    'fa-box',
    'list',
    'Finance',
    'Product and service revenue analysis with sales metrics and trends',
    '{
        "dataSource": "/api/reports/revenue-by-product/:orgId",
        "autoRefresh": 120000,
        "pagination": {
            "enabled": true,
            "pageSize": 25,
            "pageSizes": [10, 25, 50, 100]
        },
        "columns": [
            {
                "field": "product_name",
                "label": "Product/Service",
                "sortable": true,
                "searchable": true,
                "width": "20%",
                "render": "link",
                "linkAction": "reports:view-product-details"
            },
            {
                "field": "category",
                "label": "Category",
                "sortable": true,
                "width": "12%",
                "render": "badge",
                "badgeColor": "info"
            },
            {
                "field": "total_revenue",
                "label": "Total Revenue",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "revenue_ytd",
                "label": "Revenue YTD",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "quantity_sold",
                "label": "Qty Sold",
                "sortable": true,
                "width": "8%",
                "format": "number",
                "align": "center"
            },
            {
                "field": "times_sold",
                "label": "Times Sold",
                "sortable": true,
                "width": "8%",
                "format": "count",
                "align": "center"
            },
            {
                "field": "avg_selling_price",
                "label": "Avg Price",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "unique_customers",
                "label": "Customers",
                "sortable": true,
                "width": "8%",
                "format": "count",
                "align": "center"
            },
            {
                "field": "last_sale_date",
                "label": "Last Sale",
                "sortable": true,
                "width": "10%",
                "format": "date"
            }
        ],
        "filters": [
            {
                "field": "category",
                "label": "Category",
                "type": "select",
                "options": [
                    {"value": "", "label": "All Categories"},
                    {"value": "Product", "label": "Products"},
                    {"value": "Service", "label": "Services"}
                ],
                "default": ""
            },
            {
                "field": "is_active",
                "label": "Status",
                "type": "select",
                "options": [
                    {"value": "", "label": "All"},
                    {"value": "true", "label": "Active"},
                    {"value": "false", "label": "Inactive"}
                ],
                "default": ""
            },
            {
                "field": "min_revenue",
                "label": "Min Revenue",
                "type": "number",
                "placeholder": "0.00"
            },
            {
                "field": "search",
                "label": "Search",
                "type": "text",
                "placeholder": "Search product/service..."
            }
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "reports:view-product-details",
                "color": "info"
            },
            {
                "label": "Sales History",
                "icon": "fa-history",
                "action": "reports:drill-down-product-sales",
                "color": "primary"
            },
            {
                "label": "Customer Analysis",
                "icon": "fa-users",
                "action": "reports:analyze-product-customers",
                "color": "accent"
            },
            {
                "label": "Export Product Report",
                "icon": "fa-file-export",
                "action": "reports:export-product-csv",
                "color": "secondary"
            }
        ],
        "bulkActions": [
            {
                "label": "Export Selected",
                "icon": "fa-file-csv",
                "action": "reports:export-selected-csv",
                "color": "secondary"
            },
            {
                "label": "Compare Products",
                "icon": "fa-code-compare",
                "action": "reports:compare-products",
                "color": "info"
            },
            {
                "label": "Bulk Categorize",
                "icon": "fa-tags",
                "action": "reports:bulk-categorize",
                "color": "warning"
            }
        ],
        "summaryWidgets": [
            {
                "id": "total-product-revenue",
                "type": "stat",
                "title": "Total Revenue",
                "icon": "fa-dollar-sign",
                "color": "success",
                "dataPath": "totals.total_revenue",
                "format": "currency"
            },
            {
                "id": "product-count",
                "type": "stat",
                "title": "Products/Services",
                "icon": "fa-box",
                "color": "info",
                "dataPath": "totals.product_count",
                "format": "count"
            },
            {
                "id": "total-qty-sold",
                "type": "stat",
                "title": "Total Quantity Sold",
                "icon": "fa-shopping-cart",
                "color": "primary",
                "dataPath": "totals.total_quantity",
                "format": "number"
            },
            {
                "id": "best-seller",
                "type": "stat",
                "title": "Best Seller",
                "icon": "fa-star",
                "color": "warning",
                "dataPath": "totals.best_seller_name",
                "subtitle": {
                    "dataPath": "totals.best_seller_revenue",
                    "format": "currency"
                }
            }
        ],
        "exportOptions": [
            {
                "label": "Export to CSV",
                "action": "reports:export-csv",
                "icon": "fa-file-csv",
                "params": {"reportType": "revenue-by-product"}
            },
            {
                "label": "Export to PDF",
                "action": "reports:export-pdf",
                "icon": "fa-file-pdf",
                "params": {"reportType": "revenue-by-product"}
            }
        ]
    }',
    true,
    NOW()
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE 4: TAX REPORTS DASHBOARD
-- ============================================================================
-- Purpose: Sales tax collection and remittance tracking
-- Type: dashboard
-- Features:
--   - 5 summary widgets (tax stats)
--   - 2 tables (tax by rate, remittance schedule)
--   - 2 charts (tax breakdown, collection trends)
--   - Export for tax filing
--   - Auto-refresh every 300 seconds (5 minutes)
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    title,
    icon,
    type,
    category,
    description,
    config,
    is_active,
    created_at
) VALUES (
    'tax-reports-dashboard',
    'TaxReportsDashboard',
    'Sales Tax Reports',
    'fa-receipt',
    'dashboard',
    'Finance',
    'Sales tax collection summary and remittance tracking',
    '{
        "layout": "grid",
        "columns": 3,
        "autoRefresh": 300000,
        "widgets": [
            {
                "id": "total-tax-collected-widget",
                "type": "stat",
                "title": "Total Tax Collected",
                "icon": "fa-dollar-sign",
                "color": "success",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.total_tax_collected",
                "format": "currency",
                "size": "large",
                "position": {"row": 1, "col": 1}
            },
            {
                "id": "tax-paid-widget",
                "type": "stat",
                "title": "Tax Collected (Paid Invoices)",
                "icon": "fa-check-circle",
                "color": "primary",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.tax_collected_paid",
                "format": "currency",
                "size": "large",
                "position": {"row": 1, "col": 2}
            },
            {
                "id": "tax-pending-widget",
                "type": "stat",
                "title": "Tax Pending (Unpaid Invoices)",
                "icon": "fa-clock",
                "color": "warning",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.tax_collected_pending",
                "format": "currency",
                "size": "large",
                "position": {"row": 1, "col": 3}
            },
            {
                "id": "current-month-tax-widget",
                "type": "stat",
                "title": "Current Month Tax",
                "icon": "fa-calendar-day",
                "color": "info",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.current_month_tax",
                "format": "currency",
                "size": "medium",
                "position": {"row": 2, "col": 1}
            },
            {
                "id": "current-quarter-tax-widget",
                "type": "stat",
                "title": "Current Quarter Tax",
                "icon": "fa-calendar",
                "color": "accent",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.current_quarter_tax",
                "format": "currency",
                "size": "medium",
                "position": {"row": 2, "col": 2}
            },
            {
                "id": "ytd-tax-widget",
                "type": "stat",
                "title": "Year to Date Tax",
                "icon": "fa-calendar-check",
                "color": "secondary",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "totals.ytd_tax",
                "format": "currency",
                "size": "medium",
                "position": {"row": 2, "col": 3}
            },
            {
                "id": "tax-by-rate-table",
                "type": "table",
                "title": "Tax Collection by Rate",
                "icon": "fa-percent",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "data",
                "columns": [
                    {
                        "field": "tax_rate",
                        "label": "Tax Rate",
                        "format": "percentage",
                        "width": "15%"
                    },
                    {
                        "field": "taxable_amount",
                        "label": "Taxable Amount",
                        "format": "currency",
                        "align": "right",
                        "width": "20%"
                    },
                    {
                        "field": "tax_collected_paid",
                        "label": "Collected (Paid)",
                        "format": "currency",
                        "align": "right",
                        "width": "20%"
                    },
                    {
                        "field": "tax_collected_pending",
                        "label": "Pending",
                        "format": "currency",
                        "align": "right",
                        "width": "15%"
                    },
                    {
                        "field": "invoice_count",
                        "label": "Invoices",
                        "format": "count",
                        "align": "center",
                        "width": "10%"
                    },
                    {
                        "field": "total_with_tax",
                        "label": "Total with Tax",
                        "format": "currency",
                        "align": "right",
                        "width": "20%"
                    }
                ],
                "size": "xlarge",
                "position": {"row": 3, "col": 1, "colspan": 2}
            },
            {
                "id": "tax-breakdown-chart",
                "type": "chart",
                "title": "Tax Breakdown by Rate",
                "chartType": "doughnut",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "data",
                "labelField": "tax_rate_label",
                "valueField": "tax_collected_paid",
                "format": "currency",
                "colors": ["#3b82f6", "#10b981", "#f59e0b", "#ef4444", "#8b5cf6", "#ec4899"],
                "size": "large",
                "position": {"row": 3, "col": 3}
            },
            {
                "id": "remittance-schedule-table",
                "type": "table",
                "title": "Remittance Schedule (Upcoming)",
                "icon": "fa-calendar-alt",
                "dataSource": "/api/reports/sales-tax-summary/:orgId",
                "dataPath": "remittance_schedule",
                "columns": [
                    {
                        "field": "due_date",
                        "label": "Due Date",
                        "format": "date",
                        "width": "25%"
                    },
                    {
                        "field": "period",
                        "label": "Period",
                        "width": "25%"
                    },
                    {
                        "field": "amount_due",
                        "label": "Amount Due",
                        "format": "currency",
                        "align": "right",
                        "width": "25%"
                    },
                    {
                        "field": "status",
                        "label": "Status",
                        "render": "badge",
                        "badgeColors": {
                            "pending": "warning",
                            "paid": "success",
                            "overdue": "error"
                        },
                        "width": "25%"
                    }
                ],
                "size": "xlarge",
                "position": {"row": 4, "col": 1, "colspan": 3}
            }
        ],
        "quickActions": [
            {
                "label": "Export for Filing",
                "action": "reports:export-tax-filing-csv",
                "icon": "fa-file-export",
                "color": "primary"
            },
            {
                "label": "Generate Tax Report",
                "action": "reports:generate-tax-report-pdf",
                "icon": "fa-file-pdf",
                "color": "error"
            },
            {
                "label": "Email Tax Summary",
                "action": "reports:email-tax-summary",
                "icon": "fa-envelope",
                "color": "info"
            },
            {
                "label": "Schedule Remittance",
                "action": "reports:schedule-tax-remittance",
                "icon": "fa-calendar-plus",
                "color": "accent"
            }
        ]
    }',
    true,
    NOW()
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- MODULE 5: OUTSTANDING INVOICES REPORT
-- ============================================================================
-- Purpose: AR aging and outstanding invoices tracking
-- Type: list
-- Features:
--   - 8 columns showing AR details
--   - 4 filters (date range, aging bucket)
--   - 4 row actions (view, payment, reminder)
--   - 3 bulk actions
--   - 6 summary widgets (aging buckets)
-- ============================================================================

INSERT INTO modules (
    module_id,
    name,
    title,
    icon,
    type,
    category,
    description,
    config,
    is_active,
    created_at
) VALUES (
    'outstanding-invoices-report',
    'OutstandingInvoicesReport',
    'Outstanding Invoices Report',
    'fa-file-invoice-dollar',
    'list',
    'Finance',
    'Accounts receivable aging report with collection tracking',
    '{
        "dataSource": "/api/reports/outstanding-invoices/:orgId",
        "autoRefresh": 120000,
        "pagination": {
            "enabled": true,
            "pageSize": 25,
            "pageSizes": [10, 25, 50, 100]
        },
        "columns": [
            {
                "field": "customer_name",
                "label": "Customer",
                "sortable": true,
                "searchable": true,
                "width": "20%",
                "render": "link",
                "linkAction": "reports:view-customer-ar"
            },
            {
                "field": "total_outstanding",
                "label": "Total Outstanding",
                "sortable": true,
                "width": "12%",
                "format": "currency",
                "align": "right",
                "highlight": true
            },
            {
                "field": "current",
                "label": "Current",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right"
            },
            {
                "field": "overdue_0_30",
                "label": "0-30 Days",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right",
                "colorClass": "text-warning"
            },
            {
                "field": "overdue_30_60",
                "label": "30-60 Days",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right",
                "colorClass": "text-warning"
            },
            {
                "field": "overdue_60_90",
                "label": "60-90 Days",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right",
                "colorClass": "text-error"
            },
            {
                "field": "overdue_90_plus",
                "label": "90+ Days",
                "sortable": true,
                "width": "10%",
                "format": "currency",
                "align": "right",
                "colorClass": "text-error",
                "bold": true
            },
            {
                "field": "invoice_count",
                "label": "Invoices",
                "sortable": true,
                "width": "8%",
                "format": "count",
                "align": "center"
            }
        ],
        "filters": [
            {
                "field": "aging_bucket",
                "label": "Aging Bucket",
                "type": "select",
                "options": [
                    {"value": "", "label": "All Buckets"},
                    {"value": "current", "label": "Current"},
                    {"value": "0-30", "label": "0-30 Days"},
                    {"value": "30-60", "label": "30-60 Days"},
                    {"value": "60-90", "label": "60-90 Days"},
                    {"value": "90+", "label": "90+ Days"}
                ],
                "default": ""
            },
            {
                "field": "min_outstanding",
                "label": "Min Outstanding",
                "type": "number",
                "placeholder": "0.00"
            },
            {
                "field": "search",
                "label": "Search",
                "type": "text",
                "placeholder": "Search customer..."
            }
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "reports:view-ar-details",
                "color": "info"
            },
            {
                "label": "Invoice List",
                "icon": "fa-list",
                "action": "reports:view-customer-invoices",
                "color": "primary"
            },
            {
                "label": "Record Payment",
                "icon": "fa-money-bill",
                "action": "invoices:record-payment",
                "color": "success"
            },
            {
                "label": "Send Reminder",
                "icon": "fa-bell",
                "action": "invoices:send-reminder",
                "color": "warning",
                "confirm": "Send payment reminder to this customer?"
            }
        ],
        "bulkActions": [
            {
                "label": "Send Reminders",
                "icon": "fa-paper-plane",
                "action": "reports:send-reminders-bulk",
                "color": "warning",
                "confirm": "Send payment reminders to {count} customers?"
            },
            {
                "label": "Export Selected",
                "icon": "fa-file-csv",
                "action": "reports:export-selected-csv",
                "color": "secondary"
            },
            {
                "label": "Generate Statements",
                "icon": "fa-file-invoice",
                "action": "reports:generate-statements-bulk",
                "color": "primary"
            }
        ],
        "summaryWidgets": [
            {
                "id": "total-outstanding",
                "type": "stat",
                "title": "Total Outstanding",
                "icon": "fa-dollar-sign",
                "color": "error",
                "dataPath": "totals.total_outstanding",
                "format": "currency"
            },
            {
                "id": "current-ar",
                "type": "stat",
                "title": "Current",
                "icon": "fa-check-circle",
                "color": "success",
                "dataPath": "totals.current",
                "format": "currency"
            },
            {
                "id": "overdue-0-30",
                "type": "stat",
                "title": "0-30 Days",
                "icon": "fa-clock",
                "color": "warning",
                "dataPath": "totals.overdue_0_30",
                "format": "currency"
            },
            {
                "id": "overdue-30-60",
                "type": "stat",
                "title": "30-60 Days",
                "icon": "fa-exclamation-circle",
                "color": "warning",
                "dataPath": "totals.overdue_30_60",
                "format": "currency"
            },
            {
                "id": "overdue-60-90",
                "type": "stat",
                "title": "60-90 Days",
                "icon": "fa-exclamation-triangle",
                "color": "error",
                "dataPath": "totals.overdue_60_90",
                "format": "currency"
            },
            {
                "id": "overdue-90-plus",
                "type": "stat",
                "title": "90+ Days",
                "icon": "fa-ban",
                "color": "error",
                "dataPath": "totals.overdue_90_plus",
                "format": "currency"
            }
        ],
        "exportOptions": [
            {
                "label": "Export to CSV",
                "action": "reports:export-csv",
                "icon": "fa-file-csv",
                "params": {"reportType": "outstanding-invoices"}
            },
            {
                "label": "Export to PDF",
                "action": "reports:export-pdf",
                "icon": "fa-file-pdf",
                "params": {"reportType": "outstanding-invoices"}
            }
        ]
    }',
    true,
    NOW()
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updated_at = NOW();

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Check all modules were created successfully
SELECT 
    module_id,
    name,
    title,
    type,
    category,
    is_active,
    created_at
FROM modules
WHERE module_id IN (
    'financial-reports-dashboard',
    'customer-revenue-report',
    'product-revenue-report',
    'tax-reports-dashboard',
    'outstanding-invoices-report'
)
ORDER BY module_id;

-- Count widgets per module
SELECT 
    module_id,
    jsonb_array_length(config->'widgets') as widget_count,
    jsonb_array_length(config->'columns') as column_count,
    jsonb_array_length(config->'filters') as filter_count,
    jsonb_array_length(config->'rowActions') as row_action_count
FROM modules
WHERE module_id IN (
    'financial-reports-dashboard',
    'customer-revenue-report',
    'product-revenue-report',
    'tax-reports-dashboard',
    'outstanding-invoices-report'
);

-- ============================================================================
-- ROLLBACK SCRIPT (if needed)
-- ============================================================================
/*
DELETE FROM modules WHERE module_id IN (
    'financial-reports-dashboard',
    'customer-revenue-report',
    'product-revenue-report',
    'tax-reports-dashboard',
    'outstanding-invoices-report'
);
*/

-- ============================================================================
-- END OF MIGRATION
-- ============================================================================
