-- Module 10 enterprise hardening (15 improvements)
-- Safe after 023_automations_workflows.sql

-- 1) Rule versioning
ALTER TABLE automation_rules
    ADD COLUMN IF NOT EXISTS version INT UNSIGNED NOT NULL DEFAULT 1 AFTER name,
    ADD COLUMN IF NOT EXISTS parent_rule_id BIGINT UNSIGNED NULL AFTER version;

SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'automation_rules'
      AND constraint_name = 'fk_automation_rules_parent'
);
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE automation_rules ADD CONSTRAINT fk_automation_rules_parent FOREIGN KEY (parent_rule_id) REFERENCES automation_rules(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS automation_rule_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NOT NULL,
    version INT UNSIGNED NOT NULL,
    snapshot_json JSON NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_rule_version (tenant_id, rule_id, version),
    CONSTRAINT fk_automation_rule_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_rule_versions_rule FOREIGN KEY (rule_id) REFERENCES automation_rules(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_rule_versions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Dry-run/simulation support
CREATE TABLE IF NOT EXISTS automation_simulations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(100) NOT NULL,
    payload_json JSON NULL,
    result_json JSON NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_automation_simulations_tenant (tenant_id, created_at),
    CONSTRAINT fk_automation_simulations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_simulations_rule FOREIGN KEY (rule_id) REFERENCES automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_automation_simulations_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) Condition schema registry
CREATE TABLE IF NOT EXISTS automation_condition_schemas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    trigger_event VARCHAR(100) NOT NULL,
    schema_json JSON NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_condition_schema (tenant_id, trigger_event),
    CONSTRAINT fk_automation_condition_schemas_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) Exponential retry/backoff metadata
ALTER TABLE automation_webhook_deliveries
    ADD COLUMN IF NOT EXISTS retry_backoff_seconds INT UNSIGNED NOT NULL DEFAULT 60 AFTER attempts,
    ADD COLUMN IF NOT EXISTS last_error VARCHAR(255) NULL AFTER response_body;

CREATE TABLE IF NOT EXISTS automation_retry_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(80) NOT NULL,
    base_delay_seconds INT UNSIGNED NOT NULL DEFAULT 60,
    max_delay_seconds INT UNSIGNED NOT NULL DEFAULT 3600,
    max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 5,
    jitter_ratio DECIMAL(4,3) NOT NULL DEFAULT 0.100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_retry_policy (tenant_id, action_type),
    CONSTRAINT fk_automation_retry_policies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Dead-letter queue
CREATE TABLE IF NOT EXISTS automation_dead_letters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    source_type ENUM('event', 'run', 'webhook_delivery') NOT NULL,
    source_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(100) NULL,
    payload_json JSON NULL,
    error_message VARCHAR(255) NULL,
    retries_exhausted TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('open', 'replayed', 'ignored') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    replayed_at DATETIME NULL,
    INDEX idx_automation_dead_letters_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_automation_dead_letters_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Event idempotency keys + payload hash
ALTER TABLE automation_event_log
    ADD COLUMN IF NOT EXISTS event_hash CHAR(64) NULL AFTER event_name,
    ADD COLUMN IF NOT EXISTS idempotency_key VARCHAR(120) NULL AFTER event_hash;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'automation_event_log'
      AND index_name = 'uq_automation_event_idempotency'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE automation_event_log ADD UNIQUE KEY uq_automation_event_idempotency (tenant_id, idempotency_key)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'automation_event_log'
      AND index_name = 'idx_automation_event_hash'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE automation_event_log ADD INDEX idx_automation_event_hash (tenant_id, event_name, event_hash)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7) Rate limits by rule/event
CREATE TABLE IF NOT EXISTS automation_rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(100) NULL,
    window_seconds INT UNSIGNED NOT NULL DEFAULT 60,
    max_executions INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_rate_limit (tenant_id, rule_id, event_name, window_seconds),
    CONSTRAINT fk_automation_rate_limits_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_rate_limits_rule FOREIGN KEY (rule_id) REFERENCES automation_rules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8) Loop detection / execution traces
ALTER TABLE automation_rule_runs
    ADD COLUMN IF NOT EXISTS trace_id CHAR(36) NULL AFTER event_id,
    ADD COLUMN IF NOT EXISTS chain_depth TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER trace_id;

CREATE TABLE IF NOT EXISTS automation_execution_traces (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    trace_id CHAR(36) NOT NULL,
    root_event_id BIGINT UNSIGNED NULL,
    max_depth_reached TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('open', 'closed', 'loop_detected') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    UNIQUE KEY uq_automation_execution_trace (tenant_id, trace_id),
    CONSTRAINT fk_automation_execution_traces_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_execution_traces_event FOREIGN KEY (root_event_id) REFERENCES automation_event_log(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9) Fine-grained automation permissions
CREATE TABLE IF NOT EXISTS automation_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(50) NOT NULL,
    action_key VARCHAR(80) NOT NULL,
    is_allowed TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_permission (tenant_id, role, action_key),
    CONSTRAINT fk_automation_permissions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10) Action templates catalog
CREATE TABLE IF NOT EXISTS automation_action_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(80) NOT NULL,
    name VARCHAR(140) NOT NULL,
    params_schema_json JSON NULL,
    default_payload_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_action_template (tenant_id, action_type, name),
    CONSTRAINT fk_automation_action_templates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11) Metrics snapshots
CREATE TABLE IF NOT EXISTS automation_daily_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    snapshot_date DATE NOT NULL,
    total_events INT UNSIGNED NOT NULL DEFAULT 0,
    total_runs INT UNSIGNED NOT NULL DEFAULT 0,
    success_runs INT UNSIGNED NOT NULL DEFAULT 0,
    failed_runs INT UNSIGNED NOT NULL DEFAULT 0,
    skipped_runs INT UNSIGNED NOT NULL DEFAULT 0,
    avg_duration_ms DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    p95_duration_ms DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_daily_snapshot (tenant_id, snapshot_date),
    CONSTRAINT fk_automation_daily_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12) Alert escalation policies and runs
CREATE TABLE IF NOT EXISTS automation_alert_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    escalate_after_minutes INT UNSIGNED NOT NULL DEFAULT 30,
    target_channel ENUM('in_app', 'email', 'webhook') NOT NULL DEFAULT 'in_app',
    target_ref VARCHAR(190) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_alert_policy (tenant_id, severity, target_channel),
    CONSTRAINT fk_automation_alert_policies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_alert_escalations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    alert_id BIGINT UNSIGNED NOT NULL,
    policy_id BIGINT UNSIGNED NULL,
    status ENUM('queued', 'sent', 'failed') NOT NULL DEFAULT 'queued',
    message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_automation_alert_escalations_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_automation_alert_escalations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_alert_escalations_alert FOREIGN KEY (alert_id) REFERENCES automation_alerts(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_alert_escalations_policy FOREIGN KEY (policy_id) REFERENCES automation_alert_policies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13) Dispatch queue for scheduled events
CREATE TABLE IF NOT EXISTS automation_schedule_dispatches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    schedule_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending', 'dispatched', 'failed') NOT NULL DEFAULT 'pending',
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    dispatched_at DATETIME NULL,
    message VARCHAR(255) NULL,
    INDEX idx_automation_schedule_dispatches_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_automation_schedule_dispatches_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_schedule_dispatches_schedule FOREIGN KEY (schedule_id) REFERENCES automation_schedules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14) Webhook security hardening (HMAC/replay guard)
ALTER TABLE automation_webhook_endpoints
    ADD COLUMN IF NOT EXISTS signing_algorithm ENUM('sha256', 'sha512') NOT NULL DEFAULT 'sha256' AFTER secret_key,
    ADD COLUMN IF NOT EXISTS replay_ttl_seconds INT UNSIGNED NOT NULL DEFAULT 300 AFTER signing_algorithm;

CREATE TABLE IF NOT EXISTS automation_webhook_replay_guards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    endpoint_id BIGINT UNSIGNED NOT NULL,
    signature_hash CHAR(64) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uq_automation_webhook_replay_guard (tenant_id, endpoint_id, signature_hash),
    INDEX idx_automation_webhook_replay_expires (tenant_id, expires_at),
    CONSTRAINT fk_automation_webhook_replay_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_webhook_replay_endpoint FOREIGN KEY (endpoint_id) REFERENCES automation_webhook_endpoints(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15) Automated quality tests registry for workflows
CREATE TABLE IF NOT EXISTS automation_test_cases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    trigger_event VARCHAR(100) NOT NULL,
    input_payload_json JSON NULL,
    expected_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_test_case (tenant_id, name),
    CONSTRAINT fk_automation_test_cases_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_test_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    test_case_id BIGINT UNSIGNED NOT NULL,
    status ENUM('passed', 'failed') NOT NULL DEFAULT 'passed',
    output_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_automation_test_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_automation_test_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_test_runs_case FOREIGN KEY (test_case_id) REFERENCES automation_test_cases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo seed
INSERT INTO automation_permissions (tenant_id, role, action_key, is_allowed)
SELECT t.id, 'manager', 'rules.manage', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 'events.publish', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 'alerts.resolve', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed);

INSERT INTO automation_action_templates (tenant_id, action_type, name, params_schema_json, default_payload_json, is_active)
SELECT t.id, 'create_notification', 'Notificacion estandar',
       JSON_OBJECT('required', JSON_ARRAY('title', 'body')),
       JSON_OBJECT('title', 'Evento automatizado', 'body', 'Se ejecuto una regla'), 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);

INSERT INTO automation_retry_policies (tenant_id, action_type, base_delay_seconds, max_delay_seconds, max_attempts, jitter_ratio, is_active)
SELECT t.id, 'webhook_enqueue', 30, 1800, 6, 0.120, 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE max_attempts = VALUES(max_attempts), is_active = VALUES(is_active);

INSERT INTO automation_alert_policies (tenant_id, severity, escalate_after_minutes, target_channel, target_ref, is_active)
SELECT t.id, 'high', 20, 'in_app', 'automation.ops', 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE escalate_after_minutes = VALUES(escalate_after_minutes), is_active = VALUES(is_active);
