-- ============================================================================
-- Phase 4: Workflow Definitions SQL
-- ============================================================================
-- This file defines automated workflows for quotations and invoices using
-- the WorkflowEngine. Workflows trigger on events and execute actions via
-- ActionRegistry.
--
-- Features:
-- - Quotation lifecycle automation
-- - Invoice submission workflows
-- - Payment confirmation automation
-- - Status transition notifications
-- - Auto-conversion workflows
-- ============================================================================

-- ============================================================================
-- Workflow 1: Quotation Send Automation
-- ============================================================================
-- Trigger: When quotation status changes to 'sent'
-- Actions: Send email, update timestamps, emit event
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Quotation Send Automation',
    'quotation:status:sent',
    '{
        "description": "Automatically send quotation email and set timestamps",
        "trigger": {
            "event": "quotation:status:sent",
            "table": "quotations",
            "condition": "status == \"sent\" && sent_at == null"
        },
        "steps": [
            {
                "order": 1,
                "type": "action",
                "action": "quotations:send",
                "description": "Send quotation email to customer",
                "errorHandling": "continue"
            },
            {
                "order": 2,
                "type": "update",
                "table": "quotations",
                "fields": {
                    "sent_at": "NOW()",
                    "expires_at": "NOW() + INTERVAL '30 days'"
                },
                "description": "Update sent and expiry timestamps"
            },
            {
                "order": 3,
                "type": "notification",
                "recipients": ["created_by"],
                "template": "quotation_sent",
                "data": {
                    "quotation_number": "{quotation_number}",
                    "customer_name": "{customer.name}",
                    "amount": "{total_amount}"
                },
                "description": "Notify creator of successful send"
            },
            {
                "order": 4,
                "type": "emit",
                "event": "quotation:sent:complete",
                "data": {
                    "quotation_id": "{quotation_id}",
                    "sent_at": "{sent_at}"
                },
                "description": "Emit event for other systems"
            }
        ],
        "onError": {
            "action": "rollback",
            "notification": {
                "recipients": ["created_by"],
                "message": "Failed to send quotation {quotation_number}"
            }
        }
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 2: Quotation Auto-Convert
-- ============================================================================
-- Trigger: When quotation is accepted
-- Actions: Create invoice, update quotation status
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Quotation Auto-Convert to Invoice',
    'quotation:status:accepted',
    '{
        "description": "Automatically convert accepted quotation to invoice",
        "trigger": {
            "event": "quotation:status:accepted",
            "table": "quotations",
            "condition": "status == \"accepted\" && converted_invoice_id == null"
        },
        "steps": [
            {
                "order": 1,
                "type": "action",
                "action": "quotations:convert",
                "description": "Convert quotation to invoice",
                "params": {
                    "auto_send": false
                }
            },
            {
                "order": 2,
                "type": "update",
                "table": "quotations",
                "fields": {
                    "status": "converted",
                    "converted_invoice_id": "{created_invoice_id}",
                    "accepted_at": "NOW()"
                },
                "description": "Update quotation with conversion info"
            },
            {
                "order": 3,
                "type": "notification",
                "recipients": ["created_by"],
                "template": "quotation_converted",
                "data": {
                    "quotation_number": "{quotation_number}",
                    "invoice_number": "{created_invoice.invoice_number}",
                    "amount": "{total_amount}"
                },
                "description": "Notify about successful conversion"
            },
            {
                "order": 4,
                "type": "emit",
                "event": "invoice:created:from:quotation",
                "data": {
                    "quotation_id": "{quotation_id}",
                    "invoice_id": "{created_invoice_id}"
                }
            }
        ],
        "onSuccess": {
            "notification": {
                "recipients": ["created_by"],
                "message": "Quotation {quotation_number} converted to invoice"
            }
        }
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 3: Invoice TaxCore Submission
-- ============================================================================
-- Trigger: When invoice status changes to 'pending'
-- Actions: Validate, submit to TaxCore, update status
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Invoice TaxCore Submission',
    'invoice:status:pending',
    '{
        "description": "Submit invoice to tax authority via TaxCore",
        "trigger": {
            "event": "invoice:status:pending",
            "table": "invoices",
            "condition": "status == \"pending\" && taxcore_submission_id == null"
        },
        "steps": [
            {
                "order": 1,
                "type": "validation",
                "rules": [
                    {"field": "invoice_number", "required": true},
                    {"field": "amount", "min": 0},
                    {"field": "line_items", "minLength": 1}
                ],
                "description": "Validate invoice data before submission"
            },
            {
                "order": 2,
                "type": "action",
                "action": "invoices:submit-taxcore",
                "description": "Submit invoice to TaxCore API",
                "retry": {
                    "maxAttempts": 3,
                    "backoff": "exponential",
                    "initialDelay": 5000
                }
            },
            {
                "order": 3,
                "type": "update",
                "table": "invoices",
                "fields": {
                    "status": "submitted",
                    "taxcore_submission_id": "{taxcore_response.submission_id}",
                    "taxcore_submitted_at": "NOW()",
                    "taxcore_verification_url": "{taxcore_response.verification_url}",
                    "taxcore_qr_code": "{taxcore_response.qr_code}",
                    "taxcore_receipt_data": "{taxcore_response}"
                },
                "description": "Update invoice with TaxCore response"
            },
            {
                "order": 4,
                "type": "notification",
                "recipients": ["created_by", "accounting_team"],
                "template": "invoice_submitted_taxcore",
                "data": {
                    "invoice_number": "{invoice_number}",
                    "submission_id": "{taxcore_submission_id}",
                    "verification_url": "{taxcore_verification_url}"
                },
                "channels": ["email", "push"]
            },
            {
                "order": 5,
                "type": "emit",
                "event": "invoice:taxcore:submitted",
                "data": {
                    "invoice_id": "{invoice_id}",
                    "submission_id": "{taxcore_submission_id}"
                }
            }
        ],
        "onError": {
            "action": "rollback",
            "update": {
                "table": "invoices",
                "fields": {
                    "status": "draft"
                }
            },
            "notification": {
                "recipients": ["created_by", "accounting_team"],
                "template": "taxcore_submission_failed",
                "data": {
                    "invoice_number": "{invoice_number}",
                    "error": "{error_message}"
                }
            }
        }
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 4: Payment Confirmation
-- ============================================================================
-- Trigger: When payment is recorded
-- Actions: Update invoice, check if fully paid, send receipt
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Payment Confirmation Workflow',
    'payment:recorded',
    '{
        "description": "Process payment confirmation and update invoice status",
        "trigger": {
            "event": "payment:recorded",
            "table": "payments",
            "condition": "status == \"completed\""
        },
        "steps": [
            {
                "order": 1,
                "type": "query",
                "sql": "SELECT SUM(amount) as total_paid FROM payments WHERE reference_table = ''invoices'' AND reference_id = {invoice_id} AND status = ''completed''",
                "outputVar": "payment_total",
                "description": "Calculate total payments for invoice"
            },
            {
                "order": 2,
                "type": "update",
                "table": "invoices",
                "fields": {
                    "amount_paid": "{payment_total.total_paid}",
                    "status": "{payment_total.total_paid} >= amount ? \"paid\" : \"pending\""
                },
                "where": {
                    "invoice_id": "{invoice_id}"
                },
                "description": "Update invoice payment status"
            },
            {
                "order": 3,
                "type": "conditional",
                "condition": "{payment_total.total_paid} >= {invoice.amount}",
                "ifTrue": [
                    {
                        "type": "action",
                        "action": "invoices:send-receipt",
                        "description": "Send payment receipt to customer"
                    },
                    {
                        "type": "notification",
                        "recipients": ["created_by", "accounting_team"],
                        "template": "invoice_fully_paid",
                        "data": {
                            "invoice_number": "{invoice.invoice_number}",
                            "amount": "{invoice.amount}",
                            "paid_at": "NOW()"
                        }
                    },
                    {
                        "type": "emit",
                        "event": "invoice:fully:paid",
                        "data": {
                            "invoice_id": "{invoice_id}",
                            "amount": "{invoice.amount}"
                        }
                    }
                ],
                "ifFalse": [
                    {
                        "type": "notification",
                        "recipients": ["created_by"],
                        "template": "partial_payment_received",
                        "data": {
                            "invoice_number": "{invoice.invoice_number}",
                            "amount_paid": "{payment_total.total_paid}",
                            "amount_remaining": "{invoice.amount} - {payment_total.total_paid}"
                        }
                    }
                ]
            },
            {
                "order": 4,
                "type": "audit",
                "action": "payment_processed",
                "data": {
                    "invoice_id": "{invoice_id}",
                    "payment_id": "{payment_id}",
                    "amount": "{payment.amount}",
                    "new_total": "{payment_total.total_paid}"
                }
            }
        ]
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 5: Quotation Expiry Check
-- ============================================================================
-- Trigger: Daily scheduled task
-- Actions: Check for expired quotations, update status, notify
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Quotation Expiry Check',
    'schedule:daily',
    '{
        "description": "Check for expired quotations and update status",
        "trigger": {
            "event": "schedule:daily",
            "schedule": "0 2 * * *",
            "description": "Run at 2 AM daily"
        },
        "steps": [
            {
                "order": 1,
                "type": "query",
                "sql": "SELECT quotation_id, quotation_number, customer_id, created_by FROM quotations WHERE status = ''sent'' AND expires_at < NOW() AND NOT isDeleted",
                "outputVar": "expired_quotations",
                "description": "Find all expired quotations"
            },
            {
                "order": 2,
                "type": "foreach",
                "collection": "{expired_quotations}",
                "steps": [
                    {
                        "type": "update",
                        "table": "quotations",
                        "fields": {
                            "status": "expired"
                        },
                        "where": {
                            "quotation_id": "{item.quotation_id}"
                        }
                    },
                    {
                        "type": "notification",
                        "recipients": ["{item.created_by}"],
                        "template": "quotation_expired",
                        "data": {
                            "quotation_number": "{item.quotation_number}",
                            "customer_id": "{item.customer_id}"
                        },
                        "channels": ["email"]
                    },
                    {
                        "type": "emit",
                        "event": "quotation:expired",
                        "data": {
                            "quotation_id": "{item.quotation_id}"
                        }
                    }
                ]
            },
            {
                "order": 3,
                "type": "audit",
                "action": "quotations_expired",
                "data": {
                    "count": "{expired_quotations.length}",
                    "quotation_ids": "{expired_quotations.map(q => q.quotation_id)}"
                }
            }
        ]
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 6: Invoice Overdue Check
-- ============================================================================
-- Trigger: Daily scheduled task
-- Actions: Check for overdue invoices, update status, send reminders
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Invoice Overdue Check',
    'schedule:daily',
    '{
        "description": "Check for overdue invoices and send reminders",
        "trigger": {
            "event": "schedule:daily",
            "schedule": "0 3 * * *",
            "description": "Run at 3 AM daily"
        },
        "steps": [
            {
                "order": 1,
                "type": "query",
                "sql": "SELECT invoice_id, invoice_number, name, amount, amount_paid, invoice_date FROM invoices WHERE status IN (''pending'', ''submitted'') AND invoice_date + INTERVAL ''30 days'' < NOW() AND amount > amount_paid AND NOT isDeleted",
                "outputVar": "overdue_invoices",
                "description": "Find all overdue invoices"
            },
            {
                "order": 2,
                "type": "foreach",
                "collection": "{overdue_invoices}",
                "steps": [
                    {
                        "type": "update",
                        "table": "invoices",
                        "fields": {
                            "status": "overdue"
                        },
                        "where": {
                            "invoice_id": "{item.invoice_id}"
                        }
                    },
                    {
                        "type": "notification",
                        "recipients": ["{item.customer_email}", "accounting_team"],
                        "template": "invoice_overdue_reminder",
                        "data": {
                            "invoice_number": "{item.invoice_number}",
                            "customer_name": "{item.name}",
                            "amount": "{item.amount}",
                            "amount_paid": "{item.amount_paid}",
                            "amount_due": "{item.amount} - {item.amount_paid}",
                            "days_overdue": "DATEDIFF(NOW(), {item.invoice_date} + INTERVAL ''30 days'')"
                        },
                        "channels": ["email", "sms"]
                    },
                    {
                        "type": "emit",
                        "event": "invoice:overdue",
                        "data": {
                            "invoice_id": "{item.invoice_id}",
                            "days_overdue": "DATEDIFF(NOW(), {item.invoice_date} + INTERVAL ''30 days'')"
                        }
                    }
                ]
            },
            {
                "order": 3,
                "type": "audit",
                "action": "invoices_overdue_check",
                "data": {
                    "count": "{overdue_invoices.length}",
                    "total_overdue_amount": "{overdue_invoices.reduce((sum, inv) => sum + (inv.amount - inv.amount_paid), 0)}"
                }
            }
        ]
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow 7: Quotation Status Change Notification
-- ============================================================================
-- Trigger: When quotation status changes
-- Actions: Send notification to stakeholders
-- ============================================================================

INSERT INTO workflows (
    name,
    trigger_event,
    definition,
    is_active
) VALUES (
    'Quotation Status Change Notification',
    'quotation:status:changed',
    '{
        "description": "Notify stakeholders when quotation status changes",
        "trigger": {
            "event": "quotation:status:changed",
            "table": "quotations"
        },
        "steps": [
            {
                "order": 1,
                "type": "conditional",
                "condition": "{new_status} != {old_status}",
                "ifTrue": [
                    {
                        "type": "notification",
                        "recipients": ["created_by", "sales_team"],
                        "template": "quotation_status_changed",
                        "data": {
                            "quotation_number": "{quotation_number}",
                            "old_status": "{old_status}",
                            "new_status": "{new_status}",
                            "customer_name": "{customer.name}",
                            "amount": "{total_amount}"
                        },
                        "channels": ["email", "push"]
                    },
                    {
                        "type": "emit",
                        "event": "quotation:status:{new_status}",
                        "data": {
                            "quotation_id": "{quotation_id}",
                            "status": "{new_status}",
                            "previous_status": "{old_status}"
                        }
                    }
                ]
            }
        ]
    }'::json,
    true
) ON CONFLICT (name) DO UPDATE SET
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedAt = now();

-- ============================================================================
-- Workflow Step Types (Reference)
-- ============================================================================

INSERT INTO workflow_step_types (type, description, metadata) VALUES
('action', 'Execute an ActionRegistry action', '{"supports_retry": true, "async": true}'::json),
('update', 'Update database record', '{"supports_rollback": true}'::json),
('query', 'Execute SQL query', '{"output_required": true}'::json),
('notification', 'Send notification to users', '{"channels": ["email", "sms", "push"]}'::json),
('emit', 'Emit event to event bus', '{"async": true}'::json),
('validation', 'Validate data', '{"supports_custom_rules": true}'::json),
('conditional', 'Conditional branching', '{"supports_nested_steps": true}'::json),
('foreach', 'Iterate over collection', '{"supports_nested_steps": true}'::json),
('audit', 'Create audit log entry', '{"automatic": false}'::json)
ON CONFLICT (type) DO UPDATE SET
    description = EXCLUDED.description,
    metadata = EXCLUDED.metadata,
    updatedAt = now();

-- ============================================================================
-- Success!
-- ============================================================================
COMMENT ON TABLE workflows IS 'Phase 4: Workflow definitions automate quotations, invoices, and payment processing';
