-- ============================================================================
-- Phase 6: TaxCore Enhancement - Workflow Definitions
-- ============================================================================
-- Created: October 17, 2025
-- Purpose: Automated workflows for TaxCore submission and monitoring
-- Dependencies: workflows table, taxcore_submissions table, ActionRegistry
-- ============================================================================

-- Ensure workflows table exists (should be created in Phase 4)
-- If not, this will fail gracefully and indicate dependency issue

-- ============================================================================
-- Workflow 1: Auto-submit Invoice to TaxCore
-- ============================================================================
-- Trigger: When invoice status changes to 'finalized'
-- Purpose: Automatically submit finalized invoices to TaxCore for fiscal receipt
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'Auto-submit Invoice to TaxCore',
    'Automatically submit finalized invoices to TaxCore fiscal service for compliance',
    'invoice:status:finalized',
    'invoices',
    'status = ''finalized'' AND taxcore_submission_id_fk IS NULL',
    '[
        {
            "step": 1,
            "name": "validate_org_config",
            "action": "query",
            "description": "Check if organization has TaxCore configuration",
            "query": "SELECT config_id FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true LIMIT 1",
            "on_empty": "skip",
            "on_error": "fail"
        },
        {
            "step": 2,
            "name": "check_idempotency",
            "action": "query",
            "description": "Check if invoice already submitted to TaxCore",
            "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''invoices'' AND reference_id = ${record.invoice_id} AND status IN (''success'', ''pending'') LIMIT 1",
            "on_found": "skip",
            "on_error": "continue"
        },
        {
            "step": 3,
            "name": "transform_invoice_data",
            "action": "transform",
            "description": "Transform invoice to TaxCore format",
            "mapper": "invoice_to_taxcore",
            "input": "${record}",
            "output": "taxcore_payload"
        },
        {
            "step": 4,
            "name": "submit_to_taxcore",
            "action": "http_post",
            "description": "Submit invoice to TaxCore service",
            "url": "http://taxcore-service:3001/invoice",
            "headers": {
                "Content-Type": "application/json"
            },
            "body": "${taxcore_payload}",
            "timeout": 30,
            "on_success": "continue",
            "on_error": "retry"
        },
        {
            "step": 5,
            "name": "update_invoice",
            "action": "update",
            "description": "Update invoice with TaxCore submission ID",
            "table": "invoices",
            "where": {
                "invoice_id": "${record.invoice_id}"
            },
            "data": {
                "taxcore_submitted_at": "${now}",
                "updatedAt": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 6,
            "name": "notify_success",
            "action": "notify",
            "description": "Notify user of successful submission",
            "channel": "websocket",
            "event": "taxcore:invoice:submitted",
            "data": {
                "invoice_id": "${record.invoice_id}",
                "invoice_number": "${record.invoice_number}",
                "submission_id": "${response.submission_id}"
            }
        }
    ]'::jsonb,
    true,
    5,
    3,
    300,
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    updated_at = NOW();

-- ============================================================================
-- Workflow 2: Auto-submit Sales Receipt to TaxCore
-- ============================================================================
-- Trigger: When sales_receipt is created
-- Purpose: Real-time submission of POS sales to TaxCore
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'Auto-submit Sales Receipt to TaxCore',
    'Automatically submit new sales receipts to TaxCore in real-time',
    'sales_receipt:created',
    'sales_receipts',
    'taxcore_submission_id IS NULL',
    '[
        {
            "step": 1,
            "name": "validate_org_config",
            "action": "query",
            "description": "Check if organization has TaxCore configuration",
            "query": "SELECT config_id FROM taxcore_configurations WHERE org_id = ${record.org_id} AND is_active = true LIMIT 1",
            "on_empty": "skip",
            "on_error": "fail"
        },
        {
            "step": 2,
            "name": "check_idempotency",
            "action": "query",
            "description": "Check if receipt already submitted",
            "query": "SELECT submission_id FROM taxcore_submissions WHERE reference_table = ''sales_receipts'' AND reference_id = ${record.sales_receipt_id} LIMIT 1",
            "on_found": "skip",
            "on_error": "continue"
        },
        {
            "step": 3,
            "name": "transform_receipt_data",
            "action": "transform",
            "description": "Transform sales receipt to TaxCore format",
            "mapper": "sales_receipt_to_taxcore",
            "input": "${record}",
            "output": "taxcore_payload"
        },
        {
            "step": 4,
            "name": "submit_to_taxcore",
            "action": "http_post",
            "description": "Submit receipt to TaxCore service",
            "url": "http://taxcore-service:3001/sale",
            "headers": {
                "Content-Type": "application/json"
            },
            "body": "${taxcore_payload}",
            "timeout": 30,
            "on_success": "continue",
            "on_error": "retry"
        },
        {
            "step": 5,
            "name": "update_receipt",
            "action": "update",
            "description": "Update receipt with TaxCore data",
            "table": "sales_receipts",
            "where": {
                "sales_receipt_id": "${record.sales_receipt_id}"
            },
            "data": {
                "taxcore_submitted_at": "${now}",
                "updatedAt": "${now}"
            },
            "on_error": "log"
        }
    ]'::jsonb,
    true,
    10,
    3,
    300,
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    updated_at = NOW();

-- ============================================================================
-- Workflow 3: TaxCore Submission Failed - Notify Admin
-- ============================================================================
-- Trigger: When TaxCore submission fails
-- Purpose: Alert administrators of submission failures requiring attention
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'TaxCore Submission Failed - Notify Admin',
    'Send notification when TaxCore submission fails',
    'taxcore:submission:failed',
    'taxcore_submissions',
    'status = ''failed'' AND attempts >= 3',
    '[
        {
            "step": 1,
            "name": "get_source_record",
            "action": "query",
            "description": "Retrieve original invoice/receipt details",
            "query": "SELECT * FROM ${record.reference_table} WHERE ${record.reference_table}.${record.reference_table}_id = ${record.reference_id} LIMIT 1",
            "output": "source_record",
            "on_error": "continue"
        },
        {
            "step": 2,
            "name": "get_org_details",
            "action": "query",
            "description": "Get organization name",
            "query": "SELECT name FROM orgs WHERE org_id = ${record.org_id} LIMIT 1",
            "output": "org",
            "on_error": "continue"
        },
        {
            "step": 3,
            "name": "send_email_notification",
            "action": "email",
            "description": "Email admin about failed submission",
            "to": "admin@example.com",
            "subject": "TaxCore Submission Failed: ${source_record.invoice_number}",
            "template": "taxcore_failure",
            "data": {
                "submission_id": "${record.submission_id}",
                "org_name": "${org.name}",
                "reference_type": "${record.reference_table}",
                "reference_number": "${source_record.invoice_number}",
                "error_message": "${record.error_message}",
                "attempts": "${record.attempts}",
                "last_attempt": "${record.last_attempt_at}"
            },
            "on_error": "log"
        },
        {
            "step": 4,
            "name": "create_system_notification",
            "action": "insert",
            "description": "Create in-app notification",
            "table": "notifications",
            "data": {
                "notification_id": "${uuid}",
                "user_id": null,
                "org_id": "${record.org_id}",
                "type": "error",
                "title": "TaxCore Submission Failed",
                "message": "Failed to submit ${record.reference_table} after ${record.attempts} attempts. Error: ${record.error_message}",
                "link": "/taxcore/submissions/${record.submission_id}",
                "is_read": false,
                "created_at": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 5,
            "name": "websocket_alert",
            "action": "notify",
            "description": "Send real-time websocket alert",
            "channel": "websocket",
            "event": "taxcore:submission:failed",
            "data": {
                "submission_id": "${record.submission_id}",
                "org_id": "${record.org_id}",
                "reference_table": "${record.reference_table}",
                "error_message": "${record.error_message}"
            }
        }
    ]'::jsonb,
    true,
    8,
    1,
    0,
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    updated_at = NOW();

-- ============================================================================
-- Workflow 4: TaxCore Retry Exceeded - Escalate
-- ============================================================================
-- Trigger: When submission exceeds maximum retry attempts
-- Purpose: Escalate to support team for manual intervention
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'TaxCore Retry Exceeded - Escalate',
    'Escalate submissions that have exceeded maximum retry attempts',
    'taxcore:retry:exceeded',
    'taxcore_queue',
    'retry_count >= max_retries AND status = ''queued''',
    '[
        {
            "step": 1,
            "name": "get_submission",
            "action": "query",
            "description": "Get full submission details",
            "query": "SELECT * FROM taxcore_submissions WHERE submission_id = ${record.submission_id} LIMIT 1",
            "output": "submission",
            "on_error": "fail"
        },
        {
            "step": 2,
            "name": "update_queue_status",
            "action": "update",
            "description": "Mark queue item as failed",
            "table": "taxcore_queue",
            "where": {
                "queue_id": "${record.queue_id}"
            },
            "data": {
                "status": "failed",
                "updated_at": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 3,
            "name": "update_submission_status",
            "action": "update",
            "description": "Mark submission as permanently failed",
            "table": "taxcore_submissions",
            "where": {
                "submission_id": "${record.submission_id}"
            },
            "data": {
                "status": "failed",
                "error_message": "Maximum retry attempts exceeded",
                "updatedAt": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 4,
            "name": "create_support_ticket",
            "action": "insert",
            "description": "Create support ticket for manual intervention",
            "table": "support_tickets",
            "data": {
                "ticket_id": "${uuid}",
                "org_id": "${submission.org_id}",
                "type": "taxcore_failure",
                "priority": "high",
                "title": "TaxCore Submission Failed - Max Retries Exceeded",
                "description": "Submission ${record.submission_id} failed after ${record.retry_count} retry attempts. Manual intervention required.",
                "status": "open",
                "metadata": {
                    "submission_id": "${record.submission_id}",
                    "reference_table": "${submission.reference_table}",
                    "reference_id": "${submission.reference_id}",
                    "error_message": "${submission.error_message}"
                },
                "created_at": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 5,
            "name": "email_support_team",
            "action": "email",
            "description": "Email support team",
            "to": "support@example.com",
            "subject": "URGENT: TaxCore Submission Requires Manual Intervention",
            "template": "taxcore_escalation",
            "data": {
                "submission_id": "${record.submission_id}",
                "retry_count": "${record.retry_count}",
                "error_message": "${submission.error_message}",
                "reference_table": "${submission.reference_table}",
                "reference_id": "${submission.reference_id}"
            },
            "on_error": "log"
        }
    ]'::jsonb,
    true,
    10,
    1,
    0,
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    updated_at = NOW();

-- ============================================================================
-- Workflow 5: TaxCore Submission Success - Update Source
-- ============================================================================
-- Trigger: When TaxCore submission succeeds
-- Purpose: Update original invoice/receipt with TaxCore receipt data
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'TaxCore Submission Success - Update Source',
    'Update source invoice/receipt when TaxCore submission succeeds',
    'taxcore:submission:success',
    'taxcore_submissions',
    'status = ''success'' AND taxcore_invoice_number IS NOT NULL',
    '[
        {
            "step": 1,
            "name": "update_source_record",
            "action": "update_polymorphic",
            "description": "Update original record with TaxCore receipt data",
            "table": "${record.reference_table}",
            "id_column": "${record.reference_table}_id",
            "where": {
                "${record.reference_table}_id": "${record.reference_id}"
            },
            "data": {
                "taxcore_submission_id": "${record.submission_id}",
                "invoiceNumber": "${record.taxcore_invoice_number}",
                "verificationUrl": "${record.taxcore_verification_url}",
                "verificationQRCode": "${record.taxcore_qr_code}",
                "journal": "${record.taxcore_journal}",
                "updatedAt": "${now}"
            },
            "on_error": "log"
        },
        {
            "step": 2,
            "name": "generate_pdf_receipt",
            "action": "generate_pdf",
            "description": "Generate PDF with fiscal receipt",
            "template": "fiscal_receipt",
            "data": "${record}",
            "output_path": "storage/receipts/${record.submission_id}.pdf",
            "on_error": "log"
        },
        {
            "step": 3,
            "name": "notify_user",
            "action": "notify",
            "description": "Notify user of successful submission",
            "channel": "websocket",
            "event": "taxcore:submission:success",
            "data": {
                "submission_id": "${record.submission_id}",
                "taxcore_invoice_number": "${record.taxcore_invoice_number}",
                "verification_url": "${record.taxcore_verification_url}"
            }
        }
    ]'::jsonb,
    true,
    7,
    2,
    60,
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    updated_at = NOW();

-- ============================================================================
-- Scheduled Workflow: Process TaxCore Retry Queue
-- ============================================================================
-- Trigger: Cron schedule (every 5 minutes)
-- Purpose: Process retry queue and re-submit failed submissions
-- ============================================================================

INSERT INTO workflows (
    workflow_id,
    name,
    description,
    trigger_event,
    trigger_table,
    trigger_condition,
    steps,
    is_active,
    priority,
    max_retries,
    retry_delay_seconds,
    schedule_cron,
    created_at,
    updated_at
) VALUES (
    uuid_generate_v7(),
    'Process TaxCore Retry Queue',
    'Scheduled job to process retry queue every 5 minutes',
    'schedule:cron',
    null,
    null,
    '[
        {
            "step": 1,
            "name": "get_pending_retries",
            "action": "query",
            "description": "Get submissions ready for retry",
            "query": "SELECT q.*, s.* FROM taxcore_queue q JOIN taxcore_submissions s ON q.submission_id = s.submission_id WHERE q.status = ''queued'' AND q.retry_count < q.max_retries AND q.next_retry_at <= NOW() ORDER BY q.priority DESC, q.next_retry_at ASC LIMIT 50",
            "output": "retry_items",
            "on_empty": "skip",
            "on_error": "fail"
        },
        {
            "step": 2,
            "name": "process_each_retry",
            "action": "foreach",
            "description": "Process each retry item",
            "items": "${retry_items}",
            "steps": [
                {
                    "step": "2a",
                    "name": "submit_to_taxcore",
                    "action": "http_post",
                    "description": "Re-submit to TaxCore service",
                    "url": "http://taxcore-service:3001/sale",
                    "headers": {
                        "Content-Type": "application/json"
                    },
                    "body": "${item.request_payload}",
                    "timeout": 30,
                    "on_success": "continue",
                    "on_error": "continue"
                },
                {
                    "step": "2b",
                    "name": "update_retry_count",
                    "action": "update",
                    "description": "Increment retry count",
                    "table": "taxcore_queue",
                    "where": {
                        "queue_id": "${item.queue_id}"
                    },
                    "data": {
                        "retry_count": "${item.retry_count + 1}",
                        "next_retry_at": "${now + (item.base_delay_minutes * 60 * Math.pow(item.backoff_multiplier, item.retry_count))}",
                        "updated_at": "${now}"
                    }
                },
                {
                    "step": "2c",
                    "name": "check_max_retries",
                    "action": "conditional",
                    "description": "Check if max retries exceeded",
                    "condition": "${item.retry_count + 1 >= item.max_retries}",
                    "on_true": {
                        "action": "trigger_event",
                        "event": "taxcore:retry:exceeded",
                        "data": "${item}"
                    }
                }
            ]
        },
        {
            "step": 3,
            "name": "log_completion",
            "action": "log",
            "description": "Log queue processing completion",
            "level": "info",
            "message": "Processed ${retry_items.length} TaxCore retry items"
        }
    ]'::jsonb,
    true,
    5,
    1,
    0,
    '*/5 * * * *',
    NOW(),
    NOW()
) ON CONFLICT (name) DO UPDATE SET
    description = EXCLUDED.description,
    steps = EXCLUDED.steps,
    schedule_cron = EXCLUDED.schedule_cron,
    updated_at = NOW();

-- ============================================================================
-- Indexes for Workflow Performance
-- ============================================================================

-- Index for finding workflows by trigger event
CREATE INDEX IF NOT EXISTS idx_workflows_trigger_event 
ON workflows(trigger_event) WHERE is_active = true;

-- Index for scheduled workflows
CREATE INDEX IF NOT EXISTS idx_workflows_schedule 
ON workflows(schedule_cron) WHERE is_active = true AND schedule_cron IS NOT NULL;

-- Index for workflow priority
CREATE INDEX IF NOT EXISTS idx_workflows_priority 
ON workflows(priority DESC) WHERE is_active = true;

-- ============================================================================
-- Comments and Documentation
-- ============================================================================

COMMENT ON TABLE workflows IS 'Automated workflow definitions for business process automation';
COMMENT ON COLUMN workflows.trigger_event IS 'Event that triggers the workflow (e.g., invoice:status:finalized)';
COMMENT ON COLUMN workflows.trigger_table IS 'Database table to monitor for trigger conditions';
COMMENT ON COLUMN workflows.trigger_condition IS 'SQL condition to evaluate on trigger table';
COMMENT ON COLUMN workflows.steps IS 'JSON array of workflow steps with actions and parameters';
COMMENT ON COLUMN workflows.schedule_cron IS 'Cron expression for scheduled workflows (e.g., */5 * * * *)';

-- ============================================================================
-- Verification Queries
-- ============================================================================

-- Count active workflows
-- SELECT COUNT(*) as active_workflows FROM workflows WHERE is_active = true;

-- List all TaxCore workflows
-- SELECT name, trigger_event, priority, is_active FROM workflows WHERE name LIKE '%TaxCore%' ORDER BY priority DESC;

-- Get workflow details
-- SELECT name, description, jsonb_array_length(steps) as step_count FROM workflows WHERE trigger_event LIKE 'taxcore:%';
