-- ============================================================================
-- REVENUE BY CUSTOMER MODULE - PHASE 4 DECLARATIVE IMPLEMENTATION
-- ============================================================================
-- Description: Creates graphs and module definition for Revenue by Customer report
-- Created: 2025-10-24
-- Dependencies: graphs table, modules table, revenue_by_customer view
-- ============================================================================

-- ============================================================================
-- GRAPH DEFINITIONS
-- ============================================================================

-- Graph 1: Top 10 Customers by Total Revenue (Bar Chart)
INSERT INTO graphs (tenant_id, branch_id, name, config, createdat, updatedat, isdeleted)
VALUES (
    NULL,
    NULL,
    'Top 10 Customers by Revenue',
    '{
        "table": "revenue_by_customer",
        "x": "customer_name",
        "y": "total_revenue",
        "type": "bar",
        "groupBy": null,
        "aggregateFunc": null,
        "timeUnit": null
    }'::jsonb,
    NOW(),
    NOW(),
    false
) ON CONFLICT DO NOTHING;

-- Graph 2: Revenue Acquisition Over Time (Line Chart)
INSERT INTO graphs (tenant_id, branch_id, name, config, createdat, updatedat, isdeleted)
VALUES (
    NULL,
    NULL,
    'Revenue Acquisition Over Time',
    '{
        "table": "revenue_by_customer",
        "x": "customer_since",
        "y": "revenue_ytd",
        "type": "line",
        "groupBy": "customer_since",
        "aggregateFunc": "sum",
        "timeUnit": "month"
    }'::jsonb,
    NOW(),
    NOW(),
    false
) ON CONFLICT DO NOTHING;

-- Graph 3: Revenue Distribution by Customer Status (Pie Chart)
INSERT INTO graphs (tenant_id, branch_id, name, config, createdat, updatedat, isdeleted)
VALUES (
    NULL,
    NULL,
    'Revenue by Customer Status',
    '{
        "table": "revenue_by_customer",
        "x": "customer_status",
        "y": "total_revenue",
        "type": "pie",
        "groupBy": "customer_status",
        "aggregateFunc": "sum",
        "timeUnit": null
    }'::jsonb,
    NOW(),
    NOW(),
    false
) ON CONFLICT DO NOTHING;

-- Graph 4: Revenue Trends Comparison - 30 vs 90 Days (Grouped Bar Chart)
INSERT INTO graphs (tenant_id, branch_id, name, config, createdat, updatedat, isdeleted)
VALUES (
    NULL,
    NULL,
    'Revenue Trends: 30 vs 90 Days',
    '{
        "table": "revenue_by_customer",
        "x": "customer_name",
        "y": "revenue_last_30_days,revenue_last_90_days",
        "type": "groupedBar",
        "groupBy": "customer_name",
        "aggregateFunc": null,
        "timeUnit": null
    }'::jsonb,
    NOW(),
    NOW(),
    false
) ON CONFLICT DO NOTHING;

-- ============================================================================
-- MODULE DEFINITION
-- ============================================================================

-- Create the module with a specific UUID
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config,
    org_id,
    branch_id,
    updatedat,
    isdeleted
)
VALUES (
    '0194a8b2-9001-7123-8123-123456789abc'::uuid,
    'Revenue by Customer',
    'fa-chart-line',
    'Track customer revenue, trends, and status',
    '{
        "type": "report",
        "slug": "revenue-by-customer",
        "dataSources": [
            {
                "name": "revenue_by_customer",
                "endpoint": "/api/aggregate?table=revenue_by_customer",
                "refresh": 300000
            }
        ],
        "layout": {
            "header": {
                "title": "Revenue by Customer Analysis",
                "subtitle": "Track customer revenue, trends, and status",
                "actions": [
                    {
                        "label": "Export CSV",
                        "action": "export:csv",
                        "icon": "fa-download"
                    },
                    {
                        "label": "Export PDF",
                        "action": "export:pdf",
                        "icon": "fa-file-pdf"
                    },
                    {
                        "label": "Refresh",
                        "action": "module:refresh",
                        "icon": "fa-sync"
                    }
                ]
            },
            "body": [
                {
                    "type": "cards",
                    "columns": 4,
                    "cards": [
                        {
                            "title": "Total Customers",
                            "value": "{{ count(revenue_by_customer) }}",
                            "icon": "fa-users",
                            "color": "info"
                        },
                        {
                            "title": "Active Customers",
                            "value": "{{ count(revenue_by_customer, customer_status=active) }}",
                            "icon": "fa-user-check",
                            "color": "success"
                        },
                        {
                            "title": "Total Revenue",
                            "value": "{{ formatCurrency(sum(revenue_by_customer.total_revenue)) }}",
                            "icon": "fa-dollar-sign",
                            "color": "primary"
                        },
                        {
                            "title": "Avg Revenue/Customer",
                            "value": "{{ formatCurrency(avg(revenue_by_customer.total_revenue)) }}",
                            "icon": "fa-chart-bar",
                            "color": "warning"
                        }
                    ]
                },
                {
                    "type": "tabs",
                    "tabs": [
                        {
                            "label": "Customer List",
                            "icon": "fa-table",
                            "components": [
                                {
                                    "type": "dataTable",
                                    "dataSource": "revenue_by_customer",
                                    "columns": [
                                        {
                                            "field": "customer_name",
                                            "label": "Customer Name",
                                            "sortable": true,
                                            "searchable": true
                                        },
                                        {
                                            "field": "email",
                                            "label": "Email",
                                            "sortable": true,
                                            "searchable": true
                                        },
                                        {
                                            "field": "phone",
                                            "label": "Phone",
                                            "sortable": true
                                        },
                                        {
                                            "field": "total_invoices",
                                            "label": "Total Invoices",
                                            "sortable": true,
                                            "align": "center"
                                        },
                                        {
                                            "field": "paid_invoices",
                                            "label": "Paid Invoices",
                                            "sortable": true,
                                            "align": "center"
                                        },
                                        {
                                            "field": "total_revenue",
                                            "label": "Total Revenue",
                                            "sortable": true,
                                            "align": "right",
                                            "formula": "formatCurrency(total_revenue)"
                                        },
                                        {
                                            "field": "avg_invoice_amount",
                                            "label": "Avg Invoice",
                                            "sortable": true,
                                            "align": "right",
                                            "formula": "formatCurrency(avg_invoice_amount)"
                                        },
                                        {
                                            "field": "revenue_last_30_days",
                                            "label": "Last 30 Days",
                                            "sortable": true,
                                            "align": "right",
                                            "formula": "formatCurrency(revenue_last_30_days)"
                                        },
                                        {
                                            "field": "revenue_ytd",
                                            "label": "YTD Revenue",
                                            "sortable": true,
                                            "align": "right",
                                            "formula": "formatCurrency(revenue_ytd)"
                                        },
                                        {
                                            "field": "customer_status",
                                            "label": "Status",
                                            "sortable": true,
                                            "align": "center",
                                            "formula": "formatBadge(customer_status)"
                                        },
                                        {
                                            "field": "customer_since",
                                            "label": "Customer Since",
                                            "sortable": true,
                                            "formula": "formatDate(customer_since)"
                                        }
                                    ],
                                    "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"}
                                            ]
                                        },
                                        {
                                            "field": "customer_name",
                                            "label": "Search",
                                            "type": "text",
                                            "placeholder": "Search by name or email..."
                                        }
                                    ],
                                    "defaultSort": {
                                        "field": "total_revenue",
                                        "direction": "DESC"
                                    },
                                    "pagination": true,
                                    "perPage": 25,
                                    "rowActions": [
                                        {
                                            "label": "View Details",
                                            "action": "customer:view-details",
                                            "icon": "fa-eye",
                                            "color": "primary"
                                        },
                                        {
                                            "label": "View Invoices",
                                            "action": "customer:view-invoices",
                                            "icon": "fa-file-invoice",
                                            "color": "info"
                                        }
                                    ]
                                }
                            ]
                        },
                        {
                            "label": "Top Customers",
                            "icon": "fa-trophy",
                            "components": [
                                {
                                    "type": "description",
                                    "text": "Top 10 customers ranked by total revenue generated"
                                },
                                {
                                    "type": "graph",
                                    "graphName": "Top 10 Customers by Revenue",
                                    "height": 400,
                                    "config": {
                                        "limit": 10,
                                        "order": "DESC"
                                    }
                                }
                            ]
                        },
                        {
                            "label": "Revenue Trends",
                            "icon": "fa-chart-line",
                            "components": [
                                {
                                    "type": "description",
                                    "text": "Revenue acquisition trends over time by customer acquisition date"
                                },
                                {
                                    "type": "graph",
                                    "graphName": "Revenue Acquisition Over Time",
                                    "height": 400
                                }
                            ]
                        },
                        {
                            "label": "Status Distribution",
                            "icon": "fa-chart-pie",
                            "components": [
                                {
                                    "type": "description",
                                    "text": "Revenue distribution across customer status categories"
                                },
                                {
                                    "type": "graph",
                                    "graphName": "Revenue by Customer Status",
                                    "height": 400
                                }
                            ]
                        },
                        {
                            "label": "Period Comparison",
                            "icon": "fa-balance-scale",
                            "components": [
                                {
                                    "type": "description",
                                    "text": "Compare revenue performance: Last 30 days vs Last 90 days"
                                },
                                {
                                    "type": "graph",
                                    "graphName": "Revenue Trends: 30 vs 90 Days",
                                    "height": 400,
                                    "config": {
                                        "limit": 10,
                                        "order": "DESC"
                                    }
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        "permissions": {
            "view": ["admin", "finance", "manager"],
            "export": ["admin", "finance"]
        }
    }'::jsonb,
    NULL,
    NULL,
    NOW(),
    false
) ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    updatedat = NOW();

-- ============================================================================
-- REGISTER MODULE IN DEV PROGRAM
-- ============================================================================

-- Add Revenue by Customer module to Dev Program
INSERT INTO program_modules (program_id, module_id, config_json)
VALUES (
    '0194a8b2-dddd-7123-8123-123456789acd'::uuid,  -- Dev Program
    '0194a8b2-9001-7123-8123-123456789abc'::uuid,  -- Revenue by Customer module
    NULL
) ON CONFLICT DO NOTHING;

-- ============================================================================
-- COMMENTS
-- ============================================================================

COMMENT ON TABLE modules IS 'Stores declarative module definitions for Phase 4 architecture';

-- ============================================================================
-- SUCCESS MESSAGE
-- ============================================================================

DO $$
BEGIN
    RAISE NOTICE '✅ Revenue by Customer module created successfully!';
    RAISE NOTICE '📊 4 graph definitions created';
    RAISE NOTICE '🎨 Module registered in Dev Program';
    RAISE NOTICE '🎨 Module accessible at: /dashboard#revenue-by-customer';
    RAISE NOTICE '🔌 API endpoint: /api/aggregate?table=revenue_by_customer';
END $$;
