-- ============================================================================
-- Phase 6 Task 5: TaxCore Workflows (Compatible with Existing Schema)
-- ============================================================================
-- 
-- This migration creates workflow definitions for TaxCore automation
-- Compatible with workflows table schema: id, tenant_id, department_id, 
-- name, trigger_event, definition (JSON), is_active, isdeleted, updatedat
-- 
-- ============================================================================

-- Workflow 1: Auto-submit Invoice to TaxCore
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'Auto-submit Invoice to TaxCore',
    'invoice:status:finalized',
    '{
        "description": "Automatically submit invoices to TaxCore when finalized",
        "priority": 5,
        "max_retries": 3,
        "retry_delay": 300,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "name": "validate_org_config",
                "query": "SELECT config_id FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true",
                "required": true
            },
            {
                "step": 2,
                "action": "query",
                "name": "check_idempotency",
                "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''invoices'' AND reference_id = ${record.invoice_id}",
                "skip_if_exists": true
            },
            {
                "step": 3,
                "action": "transform",
                "name": "transform_invoice",
                "mapper": "invoice_to_taxcore",
                "input": "${record}"
            },
            {
                "step": 4,
                "action": "http_post",
                "name": "submit_to_taxcore",
                "url": "http://taxcore-service:3001/invoice",
                "body": "${step3.output}",
                "timeout": 30
            },
            {
                "step": 5,
                "action": "update",
                "name": "update_invoice",
                "table": "invoices",
                "where": {"invoice_id": "${record.invoice_id}"},
                "data": {
                    "taxcore_submission_id": "${step4.submission_id}",
                    "taxcore_status": "submitted"
                }
            },
            {
                "step": 6,
                "action": "notify",
                "name": "notify_success",
                "channel": "websocket",
                "event": "taxcore:submission:success",
                "data": "${step4}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- Workflow 2: Auto-submit Sales Receipt to TaxCore
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'Auto-submit Sales Receipt to TaxCore',
    'sales_receipt:created',
    '{
        "description": "Automatically submit sales receipts to TaxCore (POS)",
        "priority": 10,
        "max_retries": 3,
        "retry_delay": 180,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "name": "validate_org_config",
                "query": "SELECT config_id FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true",
                "required": true
            },
            {
                "step": 2,
                "action": "query",
                "name": "check_idempotency",
                "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''sales_receipts'' AND reference_id = ${record.receipt_id}",
                "skip_if_exists": true
            },
            {
                "step": 3,
                "action": "transform",
                "name": "transform_receipt",
                "mapper": "receipt_to_taxcore",
                "input": "${record}"
            },
            {
                "step": 4,
                "action": "http_post",
                "name": "submit_to_taxcore",
                "url": "http://taxcore-service:3001/sale",
                "body": "${step3.output}",
                "timeout": 30
            },
            {
                "step": 5,
                "action": "update",
                "name": "update_receipt",
                "table": "sales_receipts",
                "where": {"receipt_id": "${record.receipt_id}"},
                "data": {
                    "taxcore_submission_id": "${step4.submission_id}",
                    "taxcore_status": "submitted"
                }
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- Workflow 3: TaxCore Submission Failed - Notify Admin
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'TaxCore Submission Failed - Notify Admin',
    'taxcore:submission:failed',
    '{
        "description": "Notify administrators when TaxCore submission fails after max attempts",
        "priority": 8,
        "condition": "status = ''failed'' AND attempts >= 3",
        "steps": [
            {
                "step": 1,
                "action": "query",
                "name": "get_source_document",
                "query": "SELECT * FROM ${record.reference_table} WHERE ${record.reference_table}_id = ${record.reference_id}"
            },
            {
                "step": 2,
                "action": "query",
                "name": "get_org_info",
                "query": "SELECT * FROM orgs WHERE org_id = ${record.org_id}"
            },
            {
                "step": 3,
                "action": "email",
                "name": "send_alert_email",
                "to": "${step2.admin_email}",
                "subject": "TaxCore Submission Failed: ${record.reference_table} ${record.reference_id}",
                "template": "taxcore_failure_alert",
                "data": {
                    "submission": "${record}",
                    "document": "${step1}",
                    "org": "${step2}"
                }
            },
            {
                "step": 4,
                "action": "insert",
                "name": "create_notification",
                "table": "notifications",
                "data": {
                    "org_id": "${record.org_id}",
                    "type": "taxcore_submission_failed",
                    "severity": "error",
                    "title": "TaxCore Submission Failed",
                    "message": "Submission ${record.submission_id} failed after ${record.attempts} attempts",
                    "link": "/taxcore/submissions/${record.submission_id}"
                }
            },
            {
                "step": 5,
                "action": "notify",
                "name": "websocket_alert",
                "channel": "websocket",
                "event": "taxcore:alert:failure",
                "data": "${record}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- Workflow 4: TaxCore Retry Exceeded - Escalate
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'TaxCore Retry Exceeded - Escalate',
    'taxcore:retry:exceeded',
    '{
        "description": "Escalate to support when retry count exceeds maximum",
        "priority": 10,
        "condition": "retry_count >= max_retries",
        "steps": [
            {
                "step": 1,
                "action": "query",
                "name": "get_submission",
                "query": "SELECT * FROM taxcore_submissions WHERE submission_id = ${record.submission_id}"
            },
            {
                "step": 2,
                "action": "update",
                "name": "remove_from_queue",
                "table": "taxcore_queue",
                "where": {"queue_id": "${record.queue_id}"},
                "data": {"status": "exceeded"}
            },
            {
                "step": 3,
                "action": "update",
                "name": "mark_failed",
                "table": "taxcore_submissions",
                "where": {"submission_id": "${record.submission_id}"},
                "data": {
                    "status": "failed",
                    "error_message": "Max retries exceeded"
                }
            },
            {
                "step": 4,
                "action": "insert",
                "name": "create_support_ticket",
                "table": "support_tickets",
                "data": {
                    "org_id": "${step1.org_id}",
                    "type": "taxcore_retry_exceeded",
                    "priority": "high",
                    "subject": "TaxCore Submission Failed: ${step1.reference_table} ${step1.reference_id}",
                    "description": "Submission ${step1.submission_id} failed after ${record.retry_count} retry attempts"
                }
            },
            {
                "step": 5,
                "action": "email",
                "name": "email_support",
                "to": "support@example.com",
                "subject": "URGENT: TaxCore Retry Exceeded",
                "template": "taxcore_escalation",
                "data": {
                    "submission": "${step1}",
                    "queue": "${record}"
                }
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- Workflow 5: TaxCore Submission Success - Update Source
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'TaxCore Submission Success - Update Source',
    'taxcore:submission:success',
    '{
        "description": "Update source document when TaxCore submission succeeds",
        "priority": 7,
        "condition": "status = ''success'' AND taxcore_invoice_number IS NOT NULL",
        "steps": [
            {
                "step": 1,
                "action": "update",
                "name": "update_source_document",
                "table": "${record.reference_table}",
                "where": {"${record.reference_table}_id": "${record.reference_id}"},
                "data": {
                    "taxcore_invoice_number": "${record.taxcore_invoice_number}",
                    "taxcore_url": "${record.taxcore_url}",
                    "taxcore_submitted_at": "${record.submitted_at}"
                }
            },
            {
                "step": 2,
                "action": "trigger_event",
                "name": "generate_pdf",
                "event": "document:generate_pdf",
                "data": {
                    "table": "${record.reference_table}",
                    "id": "${record.reference_id}"
                }
            },
            {
                "step": 3,
                "action": "notify",
                "name": "notify_user",
                "channel": "websocket",
                "event": "taxcore:success",
                "data": "${record}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- Workflow 6: Process TaxCore Retry Queue (Scheduled)
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'Process TaxCore Retry Queue',
    'schedule:cron',
    '{
        "description": "Process pending TaxCore retry queue items every 5 minutes",
        "schedule": "*/5 * * * *",
        "priority": 5,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "name": "get_pending_retries",
                "query": "SELECT * FROM taxcore_queue WHERE status = ''queued'' AND retry_count < max_retries AND next_retry_at <= NOW() ORDER BY priority DESC, next_retry_at ASC LIMIT 50"
            },
            {
                "step": 2,
                "action": "foreach",
                "name": "process_each_item",
                "collection": "${step1}",
                "steps": [
                    {
                        "step": "2.1",
                        "action": "http_post",
                        "name": "resubmit",
                        "url": "http://taxcore-service:3001/${item.submission_type}",
                        "body": "${item.payload}",
                        "timeout": 30
                    },
                    {
                        "step": "2.2",
                        "action": "update",
                        "name": "update_retry_count",
                        "table": "taxcore_queue",
                        "where": {"queue_id": "${item.queue_id}"},
                        "data": {
                            "retry_count": "${item.retry_count + 1}",
                            "last_attempt_at": "NOW()",
                            "next_retry_at": "NOW() + INTERVAL ''${item.base_delay_minutes * POW(item.backoff_multiplier, item.retry_count + 1)} minutes''"
                        }
                    },
                    {
                        "step": "2.3",
                        "action": "conditional",
                        "name": "check_max_retries",
                        "condition": "${item.retry_count + 1 >= item.max_retries}",
                        "then": {
                            "action": "trigger_event",
                            "event": "taxcore:retry:exceeded",
                            "data": "${item}"
                        }
                    }
                ]
            },
            {
                "step": 3,
                "action": "insert",
                "name": "log_execution",
                "table": "workflow_execution_logs",
                "data": {
                    "workflow_name": "Process TaxCore Retry Queue",
                    "items_processed": "${step1.length}",
                    "execution_time": "${execution_time}"
                }
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE SET
    trigger_event = EXCLUDED.trigger_event,
    definition = EXCLUDED.definition,
    is_active = EXCLUDED.is_active,
    updatedat = NOW();

-- ============================================================================
-- Verification
-- ============================================================================

-- Check created workflows
SELECT 
    id,
    name,
    trigger_event,
    is_active
FROM workflows 
WHERE name LIKE '%TaxCore%'
ORDER BY name;

COMMENT ON TABLE workflows IS 'Automated workflow definitions for TaxCore integration (Phase 6)';
