-- Module 10: Automatizaciones y Workflows
-- Reglas "si X entonces Y", triggers por eventos, acciones automáticas y alertas.

ALTER TABLE automation_rules
    ADD COLUMN IF NOT EXISTS condition_json JSON NULL AFTER trigger_event,
    ADD COLUMN IF NOT EXISTS action_payload_json JSON NULL AFTER config_json,
    ADD COLUMN IF NOT EXISTS priority TINYINT UNSIGNED NOT NULL DEFAULT 100 AFTER action_payload_json,
    ADD COLUMN IF NOT EXISTS cooldown_seconds INT UNSIGNED NOT NULL DEFAULT 0 AFTER priority,
    ADD COLUMN IF NOT EXISTS max_retries TINYINT UNSIGNED NOT NULL DEFAULT 3 AFTER cooldown_seconds,
    ADD COLUMN IF NOT EXISTS failure_policy ENUM('continue', 'stop', 'alert') NOT NULL DEFAULT 'alert' AFTER max_retries,
    ADD COLUMN IF NOT EXISTS run_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER last_run_at,
    ADD COLUMN IF NOT EXISTS last_error VARCHAR(255) NULL AFTER run_count,
    ADD COLUMN IF NOT EXISTS created_by BIGINT UNSIGNED NULL AFTER created_at,
    ADD COLUMN IF NOT EXISTS updated_by BIGINT UNSIGNED NULL AFTER created_by,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER updated_by;
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE automation_rules ADD INDEX idx_automation_rules_tenant_active_trigger (tenant_id, is_active, trigger_event, priority)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE automation_rules ADD CONSTRAINT fk_automation_rules_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE automation_rules ADD CONSTRAINT fk_automation_rules_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS automation_event_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    payload_json JSON NULL,
    source VARCHAR(80) NULL,
    triggered_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_automation_event_log_tenant_event (tenant_id, event_name, created_at),
    CONSTRAINT fk_automation_event_log_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_event_log_user FOREIGN KEY (triggered_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_rule_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    event_id BIGINT UNSIGNED NULL,
    status ENUM('success', 'failed', 'skipped') NOT NULL DEFAULT 'success',
    attempt TINYINT UNSIGNED NOT NULL DEFAULT 1,
    duration_ms INT UNSIGNED NULL,
    message VARCHAR(255) NULL,
    context_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_automation_rule_runs_tenant (tenant_id, status, created_at),
    INDEX idx_automation_rule_runs_rule (tenant_id, rule_id, created_at),
    CONSTRAINT fk_automation_rule_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_rule_runs_rule FOREIGN KEY (rule_id) REFERENCES automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_automation_rule_runs_event FOREIGN KEY (event_id) REFERENCES automation_event_log(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    run_id BIGINT UNSIGNED NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('open', 'acknowledged', 'resolved') NOT NULL DEFAULT 'open',
    title VARCHAR(190) NOT NULL,
    detail VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at DATETIME NULL,
    resolved_at DATETIME NULL,
    INDEX idx_automation_alerts_tenant (tenant_id, status, severity, created_at),
    CONSTRAINT fk_automation_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_alerts_rule FOREIGN KEY (rule_id) REFERENCES automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_automation_alerts_run FOREIGN KEY (run_id) REFERENCES automation_rule_runs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_webhook_deliveries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    endpoint_id BIGINT UNSIGNED NOT NULL,
    event_id BIGINT UNSIGNED NULL,
    payload_json JSON NULL,
    status ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    http_status SMALLINT NULL,
    response_body TEXT NULL,
    next_attempt_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_automation_webhook_deliveries_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_automation_webhook_deliveries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_webhook_deliveries_endpoint FOREIGN KEY (endpoint_id) REFERENCES automation_webhook_endpoints(id) ON DELETE CASCADE,
    CONSTRAINT fk_automation_webhook_deliveries_event FOREIGN KEY (event_id) REFERENCES automation_event_log(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    cron_expr VARCHAR(80) NOT NULL,
    timezone VARCHAR(60) NOT NULL DEFAULT 'UTC',
    trigger_event VARCHAR(100) NOT NULL,
    payload_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_automation_schedules_tenant_name (tenant_id, name),
    INDEX idx_automation_schedules_tenant_active (tenant_id, is_active),
    CONSTRAINT fk_automation_schedules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO automation_rules (tenant_id, name, trigger_event, action_type, condition_json, action_payload_json, is_active, priority)
SELECT t.id, 'Lead alto valor -> notificar equipo', 'lead.qualified', 'create_notification',
       JSON_OBJECT('payload_contains', JSON_OBJECT('qualification_stage', 'sql')),
       JSON_OBJECT('title', 'Lead SQL detectado', 'body', 'Revisar lead calificado automaticamente'),
       1, 50
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active), priority = VALUES(priority);

INSERT INTO automation_schedules (tenant_id, name, cron_expr, timezone, trigger_event, payload_json, is_active)
SELECT t.id, 'Heartbeat workflow', '*/30 * * * *', 'UTC', 'workflow.heartbeat', JSON_OBJECT('source', 'scheduler'), 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), timezone = VALUES(timezone), is_active = VALUES(is_active);
