-- ============================================================================
-- Phase 6: TaxCore Workflows (Schema-Compatible Version)
-- ============================================================================
-- Compatible with existing workflows table structure (uses definition JSON column)
-- ============================================================================

-- 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',
    '{
        "priority": 5,
        "max_retries": 3,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "Get TaxCore configuration for org",
                "query": "SELECT config_id, api_base_url, org_tin, is_active FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true LIMIT 1"
            },
            {
                "step": 2,
                "action": "query",
                "description": "Check if already submitted (idempotency)",
                "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''invoices'' AND reference_id = ${record.invoice_id} LIMIT 1"
            },
            {
                "step": 3,
                "action": "conditional",
                "description": "Skip if already submitted",
                "condition": "${step2.result.length} > 0",
                "then": "exit",
                "else": "continue"
            },
            {
                "step": 4,
                "action": "http_post",
                "description": "Submit to Node.js TaxCore service",
                "url": "http://taxcore-service:3001/invoice",
                "payload": {
                    "invoice_id": "${record.invoice_id}",
                    "org_id": "${record.org_id}",
                    "invoice_number": "${record.invoice_number}",
                    "customer_id": "${record.customer_id}",
                    "total_amount": "${record.total_amount}",
                    "tax_amount": "${record.tax_amount}"
                }
            },
            {
                "step": 5,
                "action": "trigger_event",
                "description": "Trigger success or failure event",
                "event": "${step4.success ? ''taxcore:submission:success'' : ''taxcore:submission:failed''}",
                "data": "${step4.response}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- 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',
    '{
        "priority": 10,
        "max_retries": 3,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "Get TaxCore configuration",
                "query": "SELECT config_id, api_base_url, org_tin, is_active FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true LIMIT 1"
            },
            {
                "step": 2,
                "action": "query",
                "description": "Check if already submitted",
                "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''sales_receipts'' AND reference_id = ${record.receipt_id} LIMIT 1"
            },
            {
                "step": 3,
                "action": "conditional",
                "condition": "${step2.result.length} > 0",
                "then": "exit",
                "else": "continue"
            },
            {
                "step": 4,
                "action": "http_post",
                "description": "Submit to TaxCore service",
                "url": "http://taxcore-service:3001/sale",
                "payload": {
                    "receipt_id": "${record.receipt_id}",
                    "org_id": "${record.org_id}",
                    "receipt_number": "${record.receipt_number}",
                    "total_amount": "${record.total_amount}",
                    "tax_amount": "${record.tax_amount}"
                }
            },
            {
                "step": 5,
                "action": "trigger_event",
                "event": "${step4.success ? ''taxcore:submission:success'' : ''taxcore:submission:failed''}",
                "data": "${step4.response}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- 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',
    '{
        "priority": 8,
        "max_retries": 1,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "Get submission details",
                "query": "SELECT * FROM taxcore_submissions WHERE submission_id = ${event.submission_id}"
            },
            {
                "step": 2,
                "action": "query",
                "description": "Get org details",
                "query": "SELECT name, email FROM orgs WHERE org_id = ${step1.org_id}"
            },
            {
                "step": 3,
                "action": "email",
                "description": "Send email to admin",
                "to": "admin@taf.com",
                "subject": "TaxCore Submission Failed: ${step1.reference_table} ${step1.reference_id}",
                "body": "TaxCore submission failed after ${step1.attempts} attempts. Error: ${step1.error_message}"
            },
            {
                "step": 4,
                "action": "notify",
                "description": "Send websocket notification",
                "channel": "taxcore:failures",
                "message": "Submission failed: ${step1.submission_id}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- Workflow 4: TaxCore Retry Exceeded - Escalate
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'TaxCore Retry Exceeded - Escalate',
    'taxcore:retry:exceeded',
    '{
        "priority": 10,
        "max_retries": 1,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "Get submission details",
                "query": "SELECT * FROM taxcore_submissions WHERE submission_id = ${event.submission_id}"
            },
            {
                "step": 2,
                "action": "update",
                "description": "Mark as permanently failed",
                "table": "taxcore_submissions",
                "where": "submission_id = ${event.submission_id}",
                "set": {
                    "status": "failed",
                    "error_message": "Max retries exceeded"
                }
            },
            {
                "step": 3,
                "action": "email",
                "to": "support@taf.com",
                "subject": "URGENT: TaxCore Submission Failed Permanently",
                "body": "Submission ${step1.submission_id} has exceeded max retries and requires manual intervention."
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- 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',
    '{
        "priority": 7,
        "max_retries": 3,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "Get submission details",
                "query": "SELECT * FROM taxcore_submissions WHERE submission_id = ${event.submission_id}"
            },
            {
                "step": 2,
                "action": "update",
                "description": "Update source document with TaxCore invoice number",
                "table": "${step1.reference_table}",
                "where": "${step1.reference_table === ''invoices'' ? ''invoice_id'' : ''receipt_id''} = ${step1.reference_id}",
                "set": {
                    "taxcore_invoice_number": "${step1.taxcore_invoice_number}",
                    "taxcore_url": "${step1.taxcore_url}"
                }
            },
            {
                "step": 3,
                "action": "notify",
                "channel": "taxcore:success",
                "message": "TaxCore submission successful: ${step1.taxcore_invoice_number}"
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- Workflow 6: Process TaxCore Retry Queue (Scheduled)
-- Note: This is handled by the PHP cron job instead of a workflow
-- But we include it for completeness
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
    'Process TaxCore Retry Queue',
    'schedule:cron',
    '{
        "schedule": "*/5 * * * *",
        "priority": 5,
        "max_retries": 1,
        "steps": [
            {
                "step": 1,
                "action": "query",
                "description": "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",
                "description": "Process each retry item",
                "items": "${step1.results}",
                "steps": [
                    {
                        "step": "2a",
                        "action": "http_post",
                        "url": "http://taxcore-service:3001/${item.reference_table === ''invoices'' ? ''invoice'' : ''sale''}",
                        "payload": "${item.payload}"
                    },
                    {
                        "step": "2b",
                        "action": "conditional",
                        "condition": "${step2a.success}",
                        "then": {
                            "action": "update",
                            "table": "taxcore_queue",
                            "where": "queue_id = ${item.queue_id}",
                            "set": {"status": "completed"}
                        },
                        "else": {
                            "action": "update",
                            "table": "taxcore_queue",
                            "where": "queue_id = ${item.queue_id}",
                            "set": {
                                "retry_count": "${item.retry_count + 1}",
                                "last_error": "${step2a.error}"
                            }
                        }
                    }
                ]
            }
        ]
    }'::json,
    true
)
ON CONFLICT (name) DO UPDATE
SET definition = EXCLUDED.definition,
    trigger_event = EXCLUDED.trigger_event,
    is_active = EXCLUDED.is_active;

-- Add comments
COMMENT ON TABLE workflows IS 'Workflow automation definitions for Phase 6 TaxCore integration';
