-- ============================================================================
-- PHASE 7 TASK 2: PAYMENT REMINDERS DECLARATIVE MODULES
-- ============================================================================
-- Description: Module definitions for Payment Reminders management
-- Dependencies: migrations/phase7_payment_reminders_schema.sql
--               migrations/phase7_email_templates_schema.sql
-- Author: AI Assistant
-- Date: October 18, 2025
-- ============================================================================

-- ============================================================================
-- MODULE 1: PAYMENT REMINDERS LIST
-- ============================================================================
-- Purpose: List view for payment reminders with filters, stats, and actions
-- Type: list
-- ============================================================================

INSERT INTO modules (
    module_id,
    module_code,
    module_name,
    module_type,
    category,
    description,
    icon,
    config,
    is_active,
    priority,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'payment_reminders_list',
    'Payment Reminders',
    'list',
    'finance',
    'View and manage payment reminders with delivery tracking and statistics',
    'fa-bell',
    '{
        "dataSource": {
            "endpoint": "/api/payment-reminders/list",
            "method": "GET",
            "params": {
                "limit": 50,
                "offset": 0
            }
        },
        "refreshInterval": 30000,
        "columns": [
            {
                "key": "reminder_id",
                "label": "Reminder ID",
                "type": "text",
                "width": "120px",
                "sortable": true,
                "formatter": "truncate",
                "formatterOptions": {
                    "length": 8
                }
            },
            {
                "key": "invoice_number",
                "label": "Invoice",
                "type": "link",
                "width": "120px",
                "sortable": true,
                "linkTemplate": "/invoices/{{invoice_id}}"
            },
            {
                "key": "customer_name",
                "label": "Customer",
                "type": "text",
                "width": "200px",
                "sortable": true
            },
            {
                "key": "amount_due",
                "label": "Amount Due",
                "type": "currency",
                "width": "120px",
                "sortable": true,
                "align": "right"
            },
            {
                "key": "reminder_stage",
                "label": "Stage",
                "type": "badge",
                "width": "140px",
                "sortable": true,
                "badgeConfig": {
                    "7_days_before": {"color": "success", "label": "7 Days Before"},
                    "due_date": {"color": "warning", "label": "Due Date"},
                    "3_days_after": {"color": "error", "label": "3 Days Overdue"},
                    "7_days_after": {"color": "error", "label": "7 Days Overdue"},
                    "30_days_after": {"color": "error", "label": "30+ Days Overdue"}
                }
            },
            {
                "key": "delivery_method",
                "label": "Method",
                "type": "badge",
                "width": "100px",
                "sortable": true,
                "badgeConfig": {
                    "email": {"color": "info", "icon": "fa-envelope"},
                    "sms": {"color": "primary", "icon": "fa-mobile"},
                    "portal": {"color": "secondary", "icon": "fa-desktop"}
                }
            },
            {
                "key": "delivery_status",
                "label": "Status",
                "type": "badge",
                "width": "120px",
                "sortable": true,
                "badgeConfig": {
                    "pending": {"color": "warning", "label": "Pending"},
                    "sent": {"color": "success", "label": "Sent"},
                    "delivered": {"color": "success", "label": "Delivered"},
                    "opened": {"color": "info", "label": "Opened"},
                    "clicked": {"color": "primary", "label": "Clicked"},
                    "failed": {"color": "error", "label": "Failed"},
                    "bounced": {"color": "error", "label": "Bounced"}
                }
            },
            {
                "key": "sent_at",
                "label": "Sent At",
                "type": "datetime",
                "width": "160px",
                "sortable": true
            },
            {
                "key": "opened_at",
                "label": "Opened",
                "type": "datetime",
                "width": "160px",
                "sortable": true
            },
            {
                "key": "attempts",
                "label": "Attempts",
                "type": "number",
                "width": "80px",
                "sortable": true,
                "align": "center"
            }
        ],
        "filters": [
            {
                "key": "reminder_stage",
                "label": "Reminder Stage",
                "type": "select",
                "options": [
                    {"value": "7_days_before", "label": "7 Days Before"},
                    {"value": "due_date", "label": "Due Date"},
                    {"value": "3_days_after", "label": "3 Days Overdue"},
                    {"value": "7_days_after", "label": "7 Days Overdue"},
                    {"value": "30_days_after", "label": "30+ Days Overdue"}
                ],
                "multiple": true
            },
            {
                "key": "delivery_status",
                "label": "Delivery Status",
                "type": "select",
                "options": [
                    {"value": "pending", "label": "Pending"},
                    {"value": "sent", "label": "Sent"},
                    {"value": "delivered", "label": "Delivered"},
                    {"value": "opened", "label": "Opened"},
                    {"value": "clicked", "label": "Clicked"},
                    {"value": "failed", "label": "Failed"},
                    {"value": "bounced", "label": "Bounced"}
                ],
                "multiple": true
            },
            {
                "key": "delivery_method",
                "label": "Delivery Method",
                "type": "select",
                "options": [
                    {"value": "email", "label": "Email"},
                    {"value": "sms", "label": "SMS"},
                    {"value": "portal", "label": "Portal Notification"}
                ],
                "multiple": true
            },
            {
                "key": "customer_id",
                "label": "Customer",
                "type": "select",
                "dataSource": "/api/customers/list-simple",
                "valueKey": "customer_id",
                "labelKey": "customer_name"
            },
            {
                "key": "date_from",
                "label": "From Date",
                "type": "date"
            },
            {
                "key": "date_to",
                "label": "To Date",
                "type": "date"
            }
        ],
        "rowActions": [
            {
                "label": "View Details",
                "icon": "fa-eye",
                "action": "payment-reminders:view-details",
                "color": "info"
            },
            {
                "label": "View Invoice",
                "icon": "fa-file-invoice",
                "action": "invoices:view",
                "color": "primary"
            },
            {
                "label": "Resend",
                "icon": "fa-paper-plane",
                "action": "payment-reminders:resend",
                "condition": "delivery_status == \"failed\" || delivery_status == \"bounced\"",
                "color": "warning",
                "confirm": "Resend this payment reminder?"
            },
            {
                "label": "Mark as Sent",
                "icon": "fa-check",
                "action": "payment-reminders:mark-sent",
                "condition": "delivery_status == \"pending\"",
                "color": "success",
                "confirm": "Mark this reminder as sent?"
            }
        ],
        "bulkActions": [
            {
                "label": "Resend Selected",
                "icon": "fa-paper-plane",
                "action": "payment-reminders:resend-bulk",
                "color": "warning",
                "confirm": "Resend {{count}} payment reminders?"
            },
            {
                "label": "Export CSV",
                "icon": "fa-download",
                "action": "payment-reminders:export-csv",
                "color": "secondary"
            },
            {
                "label": "Export PDF",
                "icon": "fa-file-pdf",
                "action": "payment-reminders:export-pdf",
                "color": "error"
            }
        ],
        "summaryWidgets": [
            {
                "type": "stat",
                "label": "Total Reminders",
                "key": "total_reminders",
                "icon": "fa-bell",
                "color": "primary",
                "format": "number"
            },
            {
                "type": "stat",
                "label": "Sent Today",
                "key": "sent_today",
                "icon": "fa-paper-plane",
                "color": "info",
                "format": "number"
            },
            {
                "type": "stat",
                "label": "Opened",
                "key": "opened_count",
                "icon": "fa-envelope-open",
                "color": "success",
                "format": "number"
            },
            {
                "type": "stat",
                "label": "Open Rate",
                "key": "open_rate",
                "icon": "fa-percentage",
                "color": "success",
                "format": "percentage"
            },
            {
                "type": "stat",
                "label": "Failed",
                "key": "failed_count",
                "icon": "fa-exclamation-triangle",
                "color": "error",
                "format": "number"
            },
            {
                "type": "stat",
                "label": "Pending",
                "key": "pending_count",
                "icon": "fa-clock",
                "color": "warning",
                "format": "number"
            }
        ],
        "charts": [
            {
                "type": "pie",
                "title": "Reminders by Stage",
                "dataKey": "reminders_by_stage",
                "labelKey": "stage_label",
                "valueKey": "count",
                "height": 300
            },
            {
                "type": "bar",
                "title": "Delivery Status",
                "dataKey": "reminders_by_status",
                "labelKey": "status_label",
                "valueKey": "count",
                "height": 300
            },
            {
                "type": "line",
                "title": "Reminders Sent (Last 7 Days)",
                "dataKey": "reminders_trend",
                "xKey": "date",
                "yKey": "count",
                "height": 300
            }
        ],
        "defaultSort": {
            "column": "sent_at",
            "direction": "desc"
        },
        "pagination": {
            "enabled": true,
            "pageSize": 50,
            "pageSizeOptions": [25, 50, 100, 200]
        },
        "search": {
            "enabled": true,
            "placeholder": "Search by invoice, customer, or reminder ID...",
            "fields": ["invoice_number", "customer_name", "reminder_id"]
        }
    }'::jsonb,
    true,
    200,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
) ON CONFLICT (module_code) DO UPDATE SET
    module_name = EXCLUDED.module_name,
    config = EXCLUDED.config,
    updated_at = CURRENT_TIMESTAMP;

-- ============================================================================
-- MODULE 2: PAYMENT REMINDERS CONFIGURATION
-- ============================================================================
-- Purpose: Configure payment reminder settings per organization
-- Type: form
-- ============================================================================

INSERT INTO modules (
    module_id,
    module_code,
    module_name,
    module_type,
    category,
    description,
    icon,
    config,
    is_active,
    priority,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'payment_reminders_config',
    'Payment Reminder Settings',
    'form',
    'finance',
    'Configure payment reminder schedules, templates, and delivery settings',
    'fa-cog',
    '{
        "dataSource": {
            "endpoint": "/api/payment-reminders/config",
            "method": "GET"
        },
        "saveEndpoint": "/api/payment-reminders/config",
        "saveMethod": "POST",
        "sections": [
            {
                "title": "General Settings",
                "icon": "fa-sliders-h",
                "fields": [
                    {
                        "key": "enabled",
                        "label": "Enable Payment Reminders",
                        "type": "toggle",
                        "default": true,
                        "help": "Master switch for payment reminder system"
                    },
                    {
                        "key": "from_name",
                        "label": "From Name",
                        "type": "text",
                        "required": true,
                        "placeholder": "Your Company Name",
                        "help": "Name that appears in reminder emails"
                    },
                    {
                        "key": "from_email",
                        "label": "From Email",
                        "type": "email",
                        "required": true,
                        "placeholder": "reminders@yourcompany.com",
                        "help": "Email address for sending reminders"
                    },
                    {
                        "key": "reply_to_email",
                        "label": "Reply-To Email",
                        "type": "email",
                        "placeholder": "support@yourcompany.com",
                        "help": "Email address for customer replies"
                    },
                    {
                        "key": "cc_emails",
                        "label": "CC Recipients",
                        "type": "tags",
                        "placeholder": "accounting@yourcompany.com",
                        "help": "Additional recipients (comma-separated)"
                    },
                    {
                        "key": "bcc_emails",
                        "label": "BCC Recipients",
                        "type": "tags",
                        "placeholder": "admin@yourcompany.com",
                        "help": "Hidden copy recipients (comma-separated)"
                    }
                ]
            },
            {
                "title": "Reminder Schedule",
                "icon": "fa-calendar-alt",
                "fields": [
                    {
                        "key": "send_7_days_before",
                        "label": "Send 7 Days Before Due Date",
                        "type": "toggle",
                        "default": true,
                        "help": "Friendly advance notice"
                    },
                    {
                        "key": "send_on_due_date",
                        "label": "Send On Due Date",
                        "type": "toggle",
                        "default": true,
                        "help": "Payment due today reminder"
                    },
                    {
                        "key": "send_3_days_after",
                        "label": "Send 3 Days After Due Date",
                        "type": "toggle",
                        "default": true,
                        "help": "First overdue notice"
                    },
                    {
                        "key": "send_7_days_after",
                        "label": "Send 7 Days After Due Date",
                        "type": "toggle",
                        "default": true,
                        "help": "Second overdue notice with late fees"
                    },
                    {
                        "key": "send_30_days_after",
                        "label": "Send 30 Days After Due Date",
                        "type": "toggle",
                        "default": true,
                        "help": "Final notice before collections"
                    },
                    {
                        "key": "reminder_time",
                        "label": "Send Time (24-hour)",
                        "type": "time",
                        "default": "09:00",
                        "help": "Time of day to send reminders"
                    }
                ]
            },
            {
                "title": "Delivery Methods",
                "icon": "fa-paper-plane",
                "fields": [
                    {
                        "key": "email_enabled",
                        "label": "Email Delivery",
                        "type": "toggle",
                        "default": true,
                        "help": "Send reminders via email"
                    },
                    {
                        "key": "sms_enabled",
                        "label": "SMS Delivery",
                        "type": "toggle",
                        "default": false,
                        "help": "Send reminders via SMS (requires Twilio)"
                    },
                    {
                        "key": "portal_notification_enabled",
                        "label": "Portal Notifications",
                        "type": "toggle",
                        "default": true,
                        "help": "Show reminders in customer portal"
                    }
                ]
            },
            {
                "title": "Twilio Configuration (SMS)",
                "icon": "fa-mobile",
                "condition": "sms_enabled == true",
                "fields": [
                    {
                        "key": "twilio_account_sid",
                        "label": "Twilio Account SID",
                        "type": "text",
                        "placeholder": "ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
                        "help": "Your Twilio Account SID"
                    },
                    {
                        "key": "twilio_auth_token",
                        "label": "Twilio Auth Token",
                        "type": "password",
                        "placeholder": "Enter your Twilio auth token",
                        "help": "Your Twilio authentication token"
                    },
                    {
                        "key": "twilio_phone_number",
                        "label": "Twilio Phone Number",
                        "type": "tel",
                        "placeholder": "+1234567890",
                        "help": "Your Twilio phone number (E.164 format)"
                    },
                    {
                        "key": "sms_template",
                        "label": "SMS Template",
                        "type": "textarea",
                        "rows": 4,
                        "default": "Payment reminder: Invoice {{invoice_number}} for ${{amount_due}} is due on {{due_date}}. Pay now: {{payment_link}}",
                        "help": "SMS message template (max 160 characters). Use {{variables}} for dynamic content."
                    }
                ]
            },
            {
                "title": "Late Fees & Penalties",
                "icon": "fa-dollar-sign",
                "fields": [
                    {
                        "key": "late_fee_enabled",
                        "label": "Enable Late Fees",
                        "type": "toggle",
                        "default": false,
                        "help": "Automatically apply late fees to overdue invoices"
                    },
                    {
                        "key": "late_fee_type",
                        "label": "Late Fee Type",
                        "type": "select",
                        "options": [
                            {"value": "fixed", "label": "Fixed Amount"},
                            {"value": "percentage", "label": "Percentage"}
                        ],
                        "default": "fixed",
                        "condition": "late_fee_enabled == true"
                    },
                    {
                        "key": "late_fee_amount",
                        "label": "Late Fee Amount",
                        "type": "number",
                        "min": 0,
                        "step": 0.01,
                        "default": 25,
                        "condition": "late_fee_enabled == true && late_fee_type == \"fixed\"",
                        "help": "Fixed late fee amount"
                    },
                    {
                        "key": "late_fee_percentage",
                        "label": "Late Fee Percentage",
                        "type": "number",
                        "min": 0,
                        "max": 100,
                        "step": 0.1,
                        "default": 5,
                        "condition": "late_fee_enabled == true && late_fee_type == \"percentage\"",
                        "help": "Late fee as percentage of invoice amount"
                    },
                    {
                        "key": "late_fee_grace_period",
                        "label": "Grace Period (days)",
                        "type": "number",
                        "min": 0,
                        "default": 7,
                        "condition": "late_fee_enabled == true",
                        "help": "Days after due date before applying late fees"
                    }
                ]
            },
            {
                "title": "Tracking & Analytics",
                "icon": "fa-chart-line",
                "fields": [
                    {
                        "key": "track_email_opens",
                        "label": "Track Email Opens",
                        "type": "toggle",
                        "default": true,
                        "help": "Insert tracking pixel to detect email opens"
                    },
                    {
                        "key": "track_link_clicks",
                        "label": "Track Link Clicks",
                        "type": "toggle",
                        "default": true,
                        "help": "Track when customers click payment links"
                    },
                    {
                        "key": "max_attempts",
                        "label": "Maximum Retry Attempts",
                        "type": "number",
                        "min": 1,
                        "max": 10,
                        "default": 3,
                        "help": "Maximum retry attempts for failed deliveries"
                    }
                ]
            },
            {
                "title": "Customer Preferences",
                "icon": "fa-user-cog",
                "fields": [
                    {
                        "key": "allow_opt_out",
                        "label": "Allow Customer Opt-Out",
                        "type": "toggle",
                        "default": true,
                        "help": "Allow customers to opt out of reminders"
                    },
                    {
                        "key": "opt_out_text",
                        "label": "Opt-Out Link Text",
                        "type": "text",
                        "default": "Unsubscribe from payment reminders",
                        "condition": "allow_opt_out == true",
                        "help": "Text for opt-out link in emails"
                    },
                    {
                        "key": "respect_customer_timezone",
                        "label": "Respect Customer Timezone",
                        "type": "toggle",
                        "default": false,
                        "help": "Send reminders based on customer timezone"
                    }
                ]
            }
        ],
        "actions": [
            {
                "label": "Save Configuration",
                "action": "save",
                "color": "primary",
                "icon": "fa-save"
            },
            {
                "label": "Test Email Delivery",
                "action": "payment-reminders:test-email",
                "color": "info",
                "icon": "fa-envelope",
                "confirm": "Send a test payment reminder email to {{from_email}}?"
            },
            {
                "label": "Test SMS Delivery",
                "action": "payment-reminders:test-sms",
                "color": "warning",
                "icon": "fa-mobile",
                "condition": "sms_enabled == true",
                "confirm": "Send a test SMS to {{twilio_phone_number}}?"
            },
            {
                "label": "View Email Templates",
                "action": "navigation:goto",
                "params": {"path": "/email-templates"},
                "color": "secondary",
                "icon": "fa-file-alt"
            },
            {
                "label": "Reset to Defaults",
                "action": "payment-reminders:reset-config",
                "color": "error",
                "icon": "fa-undo",
                "confirm": "Reset all settings to default values? This cannot be undone."
            }
        ],
        "validation": {
            "rules": [
                {
                    "field": "from_email",
                    "rule": "email",
                    "message": "Please enter a valid email address"
                },
                {
                    "field": "reply_to_email",
                    "rule": "email",
                    "message": "Please enter a valid reply-to email address"
                },
                {
                    "field": "twilio_phone_number",
                    "rule": "phone",
                    "message": "Please enter a valid phone number in E.164 format",
                    "condition": "sms_enabled == true"
                }
            ]
        }
    }'::jsonb,
    true,
    190,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
) ON CONFLICT (module_code) DO UPDATE SET
    module_name = EXCLUDED.module_name,
    config = EXCLUDED.config,
    updated_at = CURRENT_TIMESTAMP;

-- ============================================================================
-- REGISTER ACTIONS IN ActionRegistry
-- ============================================================================
-- Note: These actions should be registered in FrontEnd/js/ActionRegistry.js
-- 
-- Actions needed:
-- 1. payment-reminders:view-details - Open reminder details modal
-- 2. payment-reminders:resend - Resend single reminder
-- 3. payment-reminders:resend-bulk - Resend multiple reminders
-- 4. payment-reminders:mark-sent - Manually mark as sent
-- 5. payment-reminders:export-csv - Export to CSV
-- 6. payment-reminders:export-pdf - Export to PDF
-- 7. payment-reminders:test-email - Send test email
-- 8. payment-reminders:test-sms - Send test SMS
-- 9. payment-reminders:reset-config - Reset configuration
-- ============================================================================

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

-- Verify modules were created
SELECT 
    module_code,
    module_name,
    module_type,
    is_active,
    priority
FROM modules
WHERE module_code IN ('payment_reminders_list', 'payment_reminders_config')
ORDER BY priority DESC;

-- Count total modules
SELECT COUNT(*) as total_modules FROM modules WHERE category = 'finance';

-- ============================================================================
-- ROLLBACK (IF NEEDED)
-- ============================================================================
-- To remove these modules, run:
-- DELETE FROM modules WHERE module_code IN ('payment_reminders_list', 'payment_reminders_config');
-- ============================================================================

-- Migration complete
SELECT 'Payment Reminders modules created successfully!' AS status;
