-- ============================================================================
-- Phase 7: Finance Integration - Module Definitions
-- ============================================================================
-- This file defines declarative modules for Phase 7 finance features
--
-- Modules:
-- 1. AgingReportModule - Accounts receivable aging analysis
-- 2. PaymentRemindersModule - Payment reminder configuration (Task 2)
-- 3. CreditManagementModule - Customer credit limits (Task 3)
-- 4. AccountingSyncModule - QuickBooks/Xero integration (Task 4)
-- 5. FinancialReportsModule - Financial reporting dashboard (Task 5)
--
-- Author: TAF Development Team
-- Date: October 17, 2025
-- Phase: 7
-- Task: 1 (Aging Reports)
-- ============================================================================

-- Prerequisites check
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'modules') THEN
        RAISE EXCEPTION 'modules table does not exist - run schema.php first';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.views WHERE table_name = 'invoice_aging') THEN
        RAISE EXCEPTION 'invoice_aging view does not exist - run phase7_aging_reports.sql first';
    END IF;
    
    RAISE NOTICE 'Prerequisites check passed';
END $$;

-- Delete existing Phase 7 modules (idempotency)
DELETE FROM modules WHERE name IN (
    'AgingReportModule',
    'PaymentRemindersModule',
    'CreditManagementModule',
    'AccountingSyncModule',
    'FinancialReportsModule'
);

-- ============================================================================
-- Module 1: Aging Report Module
-- ============================================================================
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    gen_random_uuid(),
    'AgingReportModule',
    'fa-chart-bar',
    'Accounts receivable aging analysis with bucket classification',
    '{
        "type": "list",
        "title": "Aging Report",
        "dataSource": "/api/finance/aging-report/{org_id}",
        "refreshInterval": 60000,
        "permissions": {
            "view": ["admin", "accountant", "finance_manager"],
            "export": ["admin", "accountant", "finance_manager"],
            "email": ["admin", "finance_manager"]
        },
        "filters": [
            {
                "key": "aging_bucket",
                "label": "Aging Bucket",
                "type": "select",
                "options": [
                    {"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": null
            },
            {
                "key": "payment_status",
                "label": "Payment Status",
                "type": "select",
                "options": [
                    {"value": "current", "label": "Current"},
                    {"value": "overdue", "label": "Overdue"},
                    {"value": "partial", "label": "Partially Paid"},
                    {"value": "paid", "label": "Paid"}
                ],
                "default": null
            },
            {
                "key": "customer_id",
                "label": "Customer",
                "type": "select",
                "dataSource": "/api/customers/list/{org_id}",
                "valueKey": "customer_id",
                "labelKey": "name",
                "searchable": true,
                "default": null
            },
            {
                "key": "from_date",
                "label": "From Date",
                "type": "date",
                "default": null
            },
            {
                "key": "to_date",
                "label": "To Date",
                "type": "date",
                "default": null
            }
        ],
        "columns": [
            {
                "key": "invoice_number",
                "label": "Invoice #",
                "sortable": true,
                "width": "120px",
                "link": "/invoices/view/{invoice_id}"
            },
            {
                "key": "customer_name",
                "label": "Customer",
                "sortable": true,
                "width": "200px",
                "link": "/customers/view/{customer_id}"
            },
            {
                "key": "invoice_date",
                "label": "Invoice Date",
                "type": "date",
                "sortable": true,
                "width": "120px"
            },
            {
                "key": "due_date",
                "label": "Due Date",
                "type": "date",
                "sortable": true,
                "width": "120px"
            },
            {
                "key": "total_amount",
                "label": "Total",
                "type": "currency",
                "sortable": true,
                "width": "120px",
                "align": "right"
            },
            {
                "key": "paid_amount",
                "label": "Paid",
                "type": "currency",
                "sortable": true,
                "width": "120px",
                "align": "right"
            },
            {
                "key": "outstanding_amount",
                "label": "Outstanding",
                "type": "currency",
                "sortable": true,
                "width": "130px",
                "align": "right",
                "highlight": true
            },
            {
                "key": "days_overdue",
                "label": "Days Overdue",
                "type": "number",
                "sortable": true,
                "width": "120px",
                "align": "center"
            },
            {
                "key": "aging_bucket",
                "label": "Aging",
                "type": "badge",
                "width": "100px",
                "align": "center",
                "badges": {
                    "current": {"label": "Current", "class": "badge-success"},
                    "0-30": {"label": "0-30 Days", "class": "badge-warning"},
                    "30-60": {"label": "30-60 Days", "class": "badge-warning"},
                    "60-90": {"label": "60-90 Days", "class": "badge-error"},
                    "90+": {"label": "90+ Days", "class": "badge-error"}
                }
            },
            {
                "key": "payment_status",
                "label": "Status",
                "type": "badge",
                "width": "100px",
                "align": "center",
                "badges": {
                    "current": {"label": "Current", "class": "badge-success"},
                    "overdue": {"label": "Overdue", "class": "badge-error"},
                    "partial": {"label": "Partial", "class": "badge-warning"},
                    "paid": {"label": "Paid", "class": "badge-success"}
                }
            }
        ],
        "rowActions": [
            {
                "label": "View Invoice",
                "icon": "fa-eye",
                "action": "invoice:view",
                "primary": true
            },
            {
                "label": "Record Payment",
                "icon": "fa-dollar-sign",
                "action": "payment:record",
                "condition": "payment_status != \"paid\"",
                "confirm": false
            },
            {
                "label": "Send Reminder",
                "icon": "fa-envelope",
                "action": "reminder:send",
                "condition": "payment_status == \"overdue\"",
                "confirm": "Send payment reminder to customer?"
            },
            {
                "label": "View Customer",
                "icon": "fa-user",
                "action": "customer:view"
            }
        ],
        "bulkActions": [
            {
                "label": "Export CSV",
                "icon": "fa-file-csv",
                "action": "export:csv",
                "endpoint": "/api/finance/aging-report/export",
                "method": "POST",
                "params": {"format": "csv"}
            },
            {
                "label": "Export PDF",
                "icon": "fa-file-pdf",
                "action": "export:pdf",
                "endpoint": "/api/finance/aging-report/export",
                "method": "POST",
                "params": {"format": "pdf"}
            },
            {
                "label": "Email Report",
                "icon": "fa-paper-plane",
                "action": "email:report",
                "requiresInput": true,
                "inputFields": [
                    {
                        "key": "recipients",
                        "label": "Email Recipients",
                        "type": "text",
                        "placeholder": "email1@example.com, email2@example.com",
                        "required": true
                    }
                ]
            },
            {
                "label": "Send Reminders",
                "icon": "fa-bell",
                "action": "reminder:sendBulk",
                "condition": "aging_bucket != \"current\"",
                "confirm": "Send payment reminders to selected customers?"
            }
        ],
        "summary": {
            "dataSource": "/api/finance/aging-summary/{org_id}",
            "widgets": [
                {
                    "type": "stat",
                    "title": "Total Outstanding",
                    "key": "total_outstanding",
                    "format": "currency",
                    "icon": "fa-dollar-sign",
                    "color": "primary"
                },
                {
                    "type": "stat",
                    "title": "Current",
                    "key": "current_amount",
                    "format": "currency",
                    "icon": "fa-check-circle",
                    "color": "success"
                },
                {
                    "type": "stat",
                    "title": "0-30 Days",
                    "key": "overdue_0_30",
                    "format": "currency",
                    "icon": "fa-clock",
                    "color": "warning"
                },
                {
                    "type": "stat",
                    "title": "30-60 Days",
                    "key": "overdue_30_60",
                    "format": "currency",
                    "icon": "fa-exclamation-triangle",
                    "color": "warning"
                },
                {
                    "type": "stat",
                    "title": "60-90 Days",
                    "key": "overdue_60_90",
                    "format": "currency",
                    "icon": "fa-exclamation-circle",
                    "color": "error"
                },
                {
                    "type": "stat",
                    "title": "90+ Days",
                    "key": "overdue_90_plus",
                    "format": "currency",
                    "icon": "fa-ban",
                    "color": "error"
                }
            ]
        },
        "charts": [
            {
                "type": "pie",
                "title": "Aging Distribution",
                "dataSource": "summary.buckets",
                "xKey": "aging_bucket",
                "yKey": "total_outstanding",
                "colors": {
                    "current": "#10b981",
                    "0-30": "#f59e0b",
                    "30-60": "#f59e0b",
                    "60-90": "#ef4444",
                    "90+": "#991b1b"
                },
                "height": "300px"
            },
            {
                "type": "bar",
                "title": "Top 10 Overdue Customers",
                "dataSource": "/api/finance/aging-by-customer/{org_id}?limit=10&sort_by=total_outstanding&sort_order=DESC",
                "xKey": "customer_name",
                "yKey": "total_outstanding",
                "color": "#ef4444",
                "height": "400px"
            }
        ],
        "defaultSort": {
            "key": "days_overdue",
            "order": "DESC"
        },
        "pagination": {
            "enabled": true,
            "pageSize": 50,
            "pageSizes": [25, 50, 100, 200]
        },
        "export": {
            "enabled": true,
            "formats": ["csv", "pdf"],
            "filename": "aging_report"
        }
    }'::jsonb
);

-- ============================================================================
-- Verification
-- ============================================================================
DO $$
DECLARE
    module_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO module_count
    FROM modules
    WHERE name = 'AgingReportModule';
    
    IF module_count = 0 THEN
        RAISE EXCEPTION 'AgingReportModule was not created';
    END IF;
    
    RAISE NOTICE 'Phase 7 Finance Module created successfully';
    RAISE NOTICE 'Module count: %', module_count;
END $$;

-- Display created module
SELECT 
    module_id,
    name,
    icon,
    description
FROM modules
WHERE name = 'AgingReportModule';

-- ============================================================================
-- Usage Instructions
-- ============================================================================
-- 1. Deploy database views:
--    psql -U taf -d devdb -f migrations/phase7_aging_reports.sql
--
-- 2. Deploy modules:
--    psql -U taf -d devdb -f sql/phase7_finance_modules.sql
--
-- 3. Restart web server if needed
--
-- 4. Access module:
--    Navigate to Dashboard → Finance → Aging Report
--
-- 5. API Endpoints:
--    GET  /api/finance/aging-report/{org_id}
--    GET  /api/finance/aging-by-customer/{org_id}
--    GET  /api/finance/aging-customer-detail/{org_id}/{customer_id}
--    GET  /api/finance/aging-summary/{org_id}
--    POST /api/finance/aging-report/export
--
-- 6. Testing:
--    - Verify filters work (aging bucket, payment status, customer, dates)
--    - Test sorting (by days overdue, outstanding amount)
--    - Test pagination
--    - Test CSV export
--    - Verify charts render (pie chart, bar chart)
--    - Test row actions (view invoice, record payment, send reminder)
--    - Test bulk actions (export, email report, send reminders)
-- ============================================================================
