-- Phase 6 Task 5.2: TaxCore Modules (Simplified - No ON CONFLICT)
-- Compatible with actual modules table schema (uses config JSONB column)
-- Generated: 2025-10-17

-- Delete existing TaxCore modules first (if any)
DELETE FROM modules WHERE name IN (
    'TaxCoreSubmissions',
    'TaxCoreDashboard',
    'TaxCoreConfiguration',
    'TaxCoreFailedAlert'
);

-- Module 1: TaxCoreSubmissions (List Module)
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'TaxCoreSubmissions',
    'fa-file-invoice',
    'TaxCore submission history and monitoring',
    '{
        "type": "list",
        "dataSource": "/api/taxcore/submission-history/{org_id}",
        "refreshInterval": 30000,
        "pagination": true,
        "pageSize": 50,
        "exportable": true,
        "filters": [
            {
                "key": "status",
                "label": "Status",
                "type": "select",
                "options": [
                    {"value": "success", "label": "Success"},
                    {"value": "failed", "label": "Failed"},
                    {"value": "pending", "label": "Pending"},
                    {"value": "retrying", "label": "Retrying"}
                ]
            },
            {
                "key": "submission_type",
                "label": "Type",
                "type": "select",
                "options": [
                    {"value": "invoice", "label": "Invoice"},
                    {"value": "sales_receipt", "label": "Sales Receipt"}
                ]
            },
            {
                "key": "date_from",
                "label": "From Date",
                "type": "date"
            },
            {
                "key": "date_to",
                "label": "To Date",
                "type": "date"
            }
        ],
        "columns": [
            {
                "key": "submission_id",
                "label": "Submission ID",
                "type": "uuid",
                "sortable": true,
                "copyable": true
            },
            {
                "key": "status",
                "label": "Status",
                "type": "badge",
                "sortable": true,
                "badges": {
                    "success": {"class": "badge-success", "icon": "fa-check-circle"},
                    "failed": {"class": "badge-error", "icon": "fa-times-circle"},
                    "pending": {"class": "badge-warning", "icon": "fa-clock"},
                    "retrying": {"class": "badge-info", "icon": "fa-sync-alt"}
                }
            },
            {
                "key": "submission_type",
                "label": "Type",
                "type": "text",
                "sortable": true
            },
            {
                "key": "source_id",
                "label": "Source ID",
                "type": "uuid",
                "copyable": true
            },
            {
                "key": "taxcore_invoice_number",
                "label": "TaxCore Invoice #",
                "type": "text",
                "sortable": true,
                "copyable": true
            },
            {
                "key": "total_amount",
                "label": "Amount",
                "type": "currency",
                "sortable": true
            },
            {
                "key": "attempts",
                "label": "Attempts",
                "type": "number",
                "sortable": true
            },
            {
                "key": "created_at",
                "label": "Created",
                "type": "datetime",
                "sortable": true,
                "defaultSort": "desc"
            }
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "taxcore:view",
                "openModal": true
            },
            {
                "label": "Retry Submission",
                "icon": "fa-redo",
                "action": "taxcore:retry",
                "condition": "status == \"failed\" || status == \"retrying\"",
                "confirm": "Retry this submission?"
            },
            {
                "label": "View PDF",
                "icon": "fa-file-pdf",
                "action": "taxcore:pdf",
                "condition": "status == \"success\"",
                "target": "_blank"
            },
            {
                "label": "Copy TaxCore URL",
                "icon": "fa-link",
                "action": "taxcore:copy-url",
                "condition": "status == \"success\""
            }
        ],
        "bulkActions": [
            {
                "label": "Retry Selected",
                "icon": "fa-redo",
                "action": "taxcore:bulk-retry",
                "confirm": "Retry {count} selected submission(s)?"
            },
            {
                "label": "Export to CSV",
                "icon": "fa-file-csv",
                "action": "export:csv"
            }
        ]
    }'::jsonb
);

-- Module 2: TaxCoreDashboard (Dashboard Module)
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'TaxCoreDashboard',
    'fa-chart-line',
    'TaxCore real-time monitoring dashboard',
    '{
        "type": "dashboard",
        "refreshInterval": 30000,
        "layout": "grid",
        "widgets": [
            {
                "id": "stats_overview",
                "type": "stats",
                "title": "Submission Overview",
                "col": 1,
                "row": 1,
                "width": 4,
                "height": 1,
                "dataSource": "/api/taxcore/stats/{org_id}",
                "stats": [
                    {
                        "key": "total",
                        "label": "Total Submissions",
                        "icon": "fa-file-invoice",
                        "color": "primary"
                    },
                    {
                        "key": "successful",
                        "label": "Successful",
                        "icon": "fa-check-circle",
                        "color": "success"
                    },
                    {
                        "key": "failed",
                        "label": "Failed",
                        "icon": "fa-times-circle",
                        "color": "error"
                    },
                    {
                        "key": "pending",
                        "label": "Pending",
                        "icon": "fa-clock",
                        "color": "warning"
                    }
                ]
            },
            {
                "id": "success_rate",
                "type": "gauge",
                "title": "Success Rate",
                "col": 1,
                "row": 2,
                "width": 2,
                "height": 1,
                "dataSource": "/api/taxcore/stats/{org_id}",
                "valueKey": "success_rate",
                "min": 0,
                "max": 100,
                "unit": "%",
                "ranges": [
                    {"min": 0, "max": 70, "color": "error", "label": "Critical"},
                    {"min": 70, "max": 90, "color": "warning", "label": "Warning"},
                    {"min": 90, "max": 100, "color": "success", "label": "Healthy"}
                ]
            },
            {
                "id": "total_amount",
                "type": "metric",
                "title": "Total Amount Submitted",
                "col": 3,
                "row": 2,
                "width": 2,
                "height": 1,
                "dataSource": "/api/taxcore/stats/{org_id}",
                "valueKey": "total_amount",
                "format": "currency",
                "trend": true,
                "trendKey": "amount_trend"
            },
            {
                "id": "retry_queue",
                "type": "alert",
                "title": "Retry Queue",
                "col": 1,
                "row": 3,
                "width": 4,
                "height": 1,
                "dataSource": "/api/taxcore/retry-queue/{org_id}",
                "alerts": [
                    {
                        "key": "queued_for_retry",
                        "label": "{count} submission(s) queued for retry",
                        "severity": "warning",
                        "icon": "fa-clock"
                    },
                    {
                        "key": "max_retries_exceeded",
                        "label": "{count} submission(s) exceeded max retries",
                        "severity": "error",
                        "icon": "fa-exclamation-triangle"
                    }
                ]
            },
            {
                "id": "recent_submissions",
                "type": "list",
                "title": "Recent Submissions",
                "col": 1,
                "row": 4,
                "width": 4,
                "height": 2,
                "dataSource": "/api/taxcore/submission-history/{org_id}",
                "limit": 10,
                "columns": [
                    {"key": "status", "label": "Status", "type": "badge"},
                    {"key": "submission_type", "label": "Type"},
                    {"key": "taxcore_invoice_number", "label": "Invoice #"},
                    {"key": "total_amount", "label": "Amount", "type": "currency"},
                    {"key": "created_at", "label": "Time", "type": "datetime"}
                ],
                "actions": [
                    {"label": "View All", "link": "/taxcore/submissions"}
                ]
            },
            {
                "id": "submission_trend",
                "type": "chart",
                "title": "Submission Trend (Last 7 Days)",
                "col": 1,
                "row": 6,
                "width": 4,
                "height": 2,
                "dataSource": "/api/taxcore/submission-trend/{org_id}",
                "chartType": "line",
                "xAxis": "date",
                "yAxis": ["successful", "failed"],
                "colors": {
                    "successful": "#10b981",
                    "failed": "#ef4444"
                }
            }
        ]
    }'::jsonb
);

-- Module 3: TaxCoreConfiguration (Form Module)
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'TaxCoreConfiguration',
    'fa-cog',
    'TaxCore API configuration and settings',
    '{
        "type": "form",
        "dataSource": "/api/taxcore/config/{org_id}",
        "submitEndpoint": "/api/taxcore/config/{org_id}",
        "method": "POST",
        "sections": [
            {
                "title": "API Configuration",
                "fields": [
                    {
                        "name": "api_base_url",
                        "label": "API Base URL",
                        "type": "url",
                        "required": true,
                        "placeholder": "https://api.taxcore.gov",
                        "help": "TaxCore API endpoint URL"
                    },
                    {
                        "name": "org_tin",
                        "label": "Organization TIN",
                        "type": "text",
                        "required": true,
                        "pattern": "^[0-9]{9}$",
                        "placeholder": "123456789",
                        "help": "9-digit Tax Identification Number"
                    },
                    {
                        "name": "api_version",
                        "label": "API Version",
                        "type": "select",
                        "required": true,
                        "options": [
                            {"value": "v1", "label": "Version 1.0"},
                            {"value": "v2", "label": "Version 2.0 (Recommended)"}
                        ],
                        "default": "v2"
                    }
                ]
            },
            {
                "title": "Certificate Configuration",
                "fields": [
                    {
                        "name": "certificate_file",
                        "label": "Certificate File (.p12)",
                        "type": "file",
                        "required": true,
                        "accept": ".p12,.pfx",
                        "help": "Upload your TaxCore digital certificate"
                    },
                    {
                        "name": "certificate_password",
                        "label": "Certificate Password",
                        "type": "password",
                        "required": true,
                        "help": "Password for the certificate file"
                    },
                    {
                        "name": "certificate_expiry",
                        "label": "Certificate Expiry Date",
                        "type": "date",
                        "readonly": true,
                        "help": "Automatically detected from certificate"
                    }
                ]
            },
            {
                "title": "Submission Settings",
                "fields": [
                    {
                        "name": "auto_submit_invoices",
                        "label": "Auto-submit Invoices",
                        "type": "checkbox",
                        "default": true,
                        "help": "Automatically submit invoices when finalized"
                    },
                    {
                        "name": "auto_submit_receipts",
                        "label": "Auto-submit Sales Receipts",
                        "type": "checkbox",
                        "default": true,
                        "help": "Automatically submit POS receipts"
                    },
                    {
                        "name": "retry_max_attempts",
                        "label": "Max Retry Attempts",
                        "type": "number",
                        "required": true,
                        "min": 1,
                        "max": 10,
                        "default": 3,
                        "help": "Maximum number of retry attempts for failed submissions"
                    },
                    {
                        "name": "retry_base_delay",
                        "label": "Base Retry Delay (seconds)",
                        "type": "number",
                        "required": true,
                        "min": 60,
                        "max": 3600,
                        "default": 300,
                        "help": "Initial delay before retrying (increased exponentially)"
                    },
                    {
                        "name": "retry_backoff_multiplier",
                        "label": "Retry Backoff Multiplier",
                        "type": "number",
                        "required": true,
                        "min": 1,
                        "max": 5,
                        "step": 0.1,
                        "default": 2,
                        "help": "Multiplier for exponential backoff (e.g., 2 = double delay each retry)"
                    }
                ]
            },
            {
                "title": "Environment",
                "fields": [
                    {
                        "name": "is_sandbox",
                        "label": "Sandbox Mode",
                        "type": "checkbox",
                        "default": false,
                        "help": "Use sandbox environment for testing"
                    },
                    {
                        "name": "is_active",
                        "label": "TaxCore Integration Active",
                        "type": "checkbox",
                        "default": true,
                        "help": "Enable or disable TaxCore integration"
                    }
                ]
            }
        ],
        "actions": [
            {
                "label": "Test Connection",
                "action": "taxcore:test-connection",
                "icon": "fa-plug",
                "type": "button",
                "class": "btn-secondary"
            },
            {
                "label": "Save Configuration",
                "type": "submit",
                "icon": "fa-save",
                "class": "btn-primary"
            }
        ],
        "validation": {
            "showErrors": "onSubmit",
            "scrollToError": true
        }
    }'::jsonb
);

-- Module 4: TaxCoreFailedAlert (Alert Module)
INSERT INTO modules (
    module_id,
    name,
    icon,
    description,
    config
) VALUES (
    uuid_generate_v7(),
    'TaxCoreFailedAlert',
    'fa-exclamation-triangle',
    'Alert for failed TaxCore submissions',
    '{
        "type": "alert",
        "severity": "error",
        "dataSource": "/api/taxcore/failed-count/{org_id}",
        "refreshInterval": 10000,
        "showWhen": "count > 0",
        "template": "{count} TaxCore submission(s) failed and require attention",
        "actions": [
            {
                "label": "View Failed Submissions",
                "link": "/taxcore/submissions?status=failed",
                "icon": "fa-eye"
            },
            {
                "label": "Retry All Failed",
                "action": "taxcore:retry-all-failed",
                "icon": "fa-redo",
                "confirm": "Retry all failed submissions?"
            }
        ],
        "sound": {
            "enabled": true,
            "playWhen": "count > previous_count",
            "soundFile": "/sounds/alert.mp3"
        },
        "dismissible": true,
        "persistent": true
    }'::jsonb
);

-- Verification query
SELECT module_id, name, icon, description
FROM modules
WHERE name IN (
    'TaxCoreSubmissions',
    'TaxCoreDashboard',
    'TaxCoreConfiguration',
    'TaxCoreFailedAlert'
)
ORDER BY name;

COMMENT ON TABLE modules IS 'Updated with Phase 6 TaxCore modules - 2025-10-17';
