-- Central Workflow System Tables (Payload-based approach)
-- This extends the existing approval system with support for payloads and flexible serial/parallel approvals

-- Workflows table (replaces approval_workflows for new system)
CREATE TABLE IF NOT EXISTS workflows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    module VARCHAR(100) NOT NULL,
    operation VARCHAR(100) NOT NULL,
    description TEXT,
    active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_module_operation (module, operation),
    INDEX idx_active (active)
);

-- Workflow steps (supports serial and parallel)
CREATE TABLE IF NOT EXISTS workflow_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workflow_id INT NOT NULL,
    step_order INT NOT NULL,
    mode ENUM('serial', 'parallel', 'quorum') DEFAULT 'serial',
    quorum INT DEFAULT 1, -- for parallel: number required to complete step
    assignees JSON NOT NULL, -- [{type: 'user|role|group|dynamic', refId|resolver: 'string'}]
    sla_minutes INT, -- optional SLA
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE,
    INDEX idx_workflow_order (workflow_id, step_order)
);

-- Policies table (conditions for requiring approval)
CREATE TABLE IF NOT EXISTS workflow_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(100) NOT NULL,
    operation VARCHAR(100) NOT NULL,
    condition_expr TEXT, -- e.g., "amount > 10000 AND currency = 'NGN'"
    workflow_id INT NOT NULL,
    priority INT DEFAULT 1, -- higher priority wins
    active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE,
    INDEX idx_module_operation_priority (module, operation, priority DESC),
    INDEX idx_active (active)
);

-- Operation registry (microservice operations that can require workflows)
CREATE TABLE IF NOT EXISTS operations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service VARCHAR(100) NOT NULL,
    module VARCHAR(100) NOT NULL,
    operation VARCHAR(100) NOT NULL,
    method VARCHAR(10) NOT NULL,
    path VARCHAR(255) NOT NULL,
    version VARCHAR(20) DEFAULT 'v1',
    payload_schema JSON,
    is_active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_service_operation (service, module, operation, method, path),
    INDEX idx_module_operation (module, operation),
    INDEX idx_active (is_active)
);

-- Operation callback configuration
CREATE TABLE IF NOT EXISTS operation_callbacks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_id INT NOT NULL,
    success_url VARCHAR(500),
    failure_url VARCHAR(500),
    webhook_secret VARCHAR(255) NOT NULL,
    retry_policy JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_operation (operation_id)
);

-- Approval requests (payload-based)
CREATE TABLE IF NOT EXISTS approval_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workflow_id INT NOT NULL,
    module VARCHAR(100) NOT NULL,
    operation VARCHAR(100) NOT NULL,
    status ENUM('pending', 'in_progress', 'approved', 'changes_required', 'cancelled') DEFAULT 'pending',
    requester_id VARCHAR(100) NOT NULL,
    requester_name VARCHAR(255),
    payload JSON NOT NULL, -- the full operation payload
    resource_ref VARCHAR(255), -- client-visible correlation key (e.g., PO-12345)
    correlation_id VARCHAR(255), -- for tracing
    current_step_order INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id),
    INDEX idx_status (status),
    INDEX idx_requester (requester_id),
    INDEX idx_module_operation (module, operation),
    INDEX idx_correlation (correlation_id),
    INDEX idx_resource_ref (resource_ref)
);

-- Operation invocations (must come after approval_requests)
CREATE TABLE IF NOT EXISTS operation_invocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_id INT NOT NULL,
    approval_request_id INT,
    correlation_id VARCHAR(255),
    resource_ref VARCHAR(255),
    payload JSON,
    created_by VARCHAR(100),
    status ENUM('pending', 'queued', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    idempotency_key VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE CASCADE,
    FOREIGN KEY (approval_request_id) REFERENCES approval_requests(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_idempotency (operation_id, idempotency_key),
    INDEX idx_operation_status (operation_id, status),
    INDEX idx_correlation (correlation_id),
    INDEX idx_resource_ref (resource_ref)
);

-- Step progress tracking
CREATE TABLE IF NOT EXISTS approval_step_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    step_id INT NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'failed') DEFAULT 'pending',
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    due_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES approval_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (step_id) REFERENCES workflow_steps(id) ON DELETE CASCADE,
    INDEX idx_request_step (request_id, step_id),
    INDEX idx_status (status)
);

-- Individual approval actions
CREATE TABLE IF NOT EXISTS approval_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    step_id INT NOT NULL,
    actor_id VARCHAR(100) NOT NULL,
    actor_name VARCHAR(255),
    action ENUM('approve', 'reject', 'delegate', 'escalate') NOT NULL,
    comment TEXT,
    metadata JSON, -- additional context
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES approval_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (step_id) REFERENCES workflow_steps(id) ON DELETE CASCADE,
    INDEX idx_request_step (request_id, step_id),
    INDEX idx_actor (actor_id),
    INDEX idx_action (action)
);

-- Webhook delivery log
CREATE TABLE IF NOT EXISTS webhook_deliveries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    url VARCHAR(500) NOT NULL,
    method ENUM('POST', 'PUT', 'PATCH') DEFAULT 'POST',
    payload JSON,
    response_status INT,
    response_body TEXT,
    success BOOLEAN DEFAULT FALSE,
    attempt_count INT DEFAULT 1,
    next_retry_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES approval_requests(id) ON DELETE CASCADE,
    INDEX idx_request (request_id),
    INDEX idx_success (success),
    INDEX idx_next_retry (next_retry_at)
);

-- Delegations (temporary assignment transfers)
CREATE TABLE IF NOT EXISTS delegations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_user_id VARCHAR(100) NOT NULL,
    to_user_id VARCHAR(100) NOT NULL,
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP,
    module VARCHAR(100), -- optional: module-specific delegation
    operation VARCHAR(100), -- optional: operation-specific delegation
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_from_user (from_user_id),
    INDEX idx_to_user (to_user_id),
    INDEX idx_validity (valid_from, valid_to),
    INDEX idx_scope (module, operation)
);

-- Escalations (automatic step advancement rules)
CREATE TABLE IF NOT EXISTS escalations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    step_id INT NOT NULL,
    rule_json JSON NOT NULL, -- e.g., {"delay_minutes": 1440, "target_type": "role", "target_ref": "manager"}
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (step_id) REFERENCES workflow_steps(id) ON DELETE CASCADE,
    INDEX idx_step (step_id),
    INDEX idx_active (active)
);

-- Pending operations queue (for execution engine)
CREATE TABLE IF NOT EXISTS pending_operations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    approval_request_id INT NOT NULL,
    operation_id INT NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    payload JSON NOT NULL,
    correlation_id VARCHAR(255),
    resource_ref VARCHAR(255),
    callback_url VARCHAR(500),
    callback_secret VARCHAR(255),
    retry_count INT DEFAULT 0,
    last_error TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (approval_request_id) REFERENCES approval_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_correlation (correlation_id)
);

-- Audit log (immutable trail)
CREATE TABLE IF NOT EXISTS workflow_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL, -- request|step|action|webhook
    entity_id INT NOT NULL,
    actor_id VARCHAR(100),
    old_value JSON,
    new_value JSON,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_actor (actor_id),
    INDEX idx_event (event_type)
);

