-- Module 11 closure hardening (15 improvements)
-- Safe after 026_communications_enterprise_hardening.sql

-- 1) Global email suppression list (unsubscribe, bounce, complaint)
CREATE TABLE IF NOT EXISTS email_suppression_list (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    email VARCHAR(190) NOT NULL,
    reason ENUM('unsubscribe', 'bounce', 'complaint', 'manual') NOT NULL DEFAULT 'manual',
    source VARCHAR(80) NULL,
    notes VARCHAR(255) 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_email_suppression (tenant_id, email),
    INDEX idx_email_suppression_reason (tenant_id, reason, is_active),
    CONSTRAINT fk_email_suppression_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Notification templates + versions
CREATE TABLE IF NOT EXISTS notification_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(140) NOT NULL,
    channel VARCHAR(40) NOT NULL DEFAULT 'in_app',
    title_template VARCHAR(190) NULL,
    body_template TEXT NOT NULL,
    variables_json JSON NULL,
    locale VARCHAR(10) NOT NULL DEFAULT 'es',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_notification_template_code (tenant_id, code, locale, channel),
    CONSTRAINT fk_notification_templates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_template_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    template_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_notification_template_version (tenant_id, template_id, version),
    CONSTRAINT fk_notification_template_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_template_versions_template FOREIGN KEY (template_id) REFERENCES notification_templates(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_template_versions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE notifications
    ADD COLUMN IF NOT EXISTS template_id BIGINT UNSIGNED NULL AFTER batch_id;
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE notifications ADD CONSTRAINT fk_notifications_template FOREIGN KEY (template_id) REFERENCES notification_templates(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3) Delivery attempts traceability
CREATE TABLE IF NOT EXISTS notification_delivery_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    queue_id BIGINT UNSIGNED NULL,
    notification_id BIGINT UNSIGNED NOT NULL,
    channel VARCHAR(40) NOT NULL,
    attempt TINYINT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('sent', 'failed', 'skipped') NOT NULL,
    duration_ms INT UNSIGNED NULL,
    provider_code VARCHAR(80) NULL,
    provider_message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notification_delivery_attempts_tenant (tenant_id, notification_id, created_at),
    CONSTRAINT fk_notification_delivery_attempts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_delivery_attempts_queue FOREIGN KEY (queue_id) REFERENCES notification_delivery_queue(id) ON DELETE SET NULL,
    CONSTRAINT fk_notification_delivery_attempts_notification FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) Notification channel rate limits
CREATE TABLE IF NOT EXISTS notification_rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel VARCHAR(40) NOT NULL,
    scope_type ENUM('tenant', 'user', 'notification_type') NOT NULL DEFAULT 'tenant',
    scope_key VARCHAR(120) NULL,
    window_seconds INT UNSIGNED NOT NULL DEFAULT 60,
    max_events INT UNSIGNED NOT NULL DEFAULT 120,
    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_notification_rate_limit (tenant_id, channel, scope_type, scope_key, window_seconds),
    CONSTRAINT fk_notification_rate_limits_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Chat anti-flood controls
CREATE TABLE IF NOT EXISTS chat_rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NULL,
    role VARCHAR(50) NULL,
    window_seconds INT UNSIGNED NOT NULL DEFAULT 30,
    max_messages INT UNSIGNED NOT NULL DEFAULT 20,
    max_mentions INT UNSIGNED NOT NULL DEFAULT 10,
    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_chat_rate_limit (tenant_id, channel_id, role, window_seconds),
    CONSTRAINT fk_chat_rate_limits_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_rate_limits_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_spam_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    message_id BIGINT UNSIGNED NULL,
    event_type ENUM('rate_limit', 'duplicate', 'blocked_word') NOT NULL,
    detail VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_spam_events_tenant (tenant_id, event_type, created_at),
    CONSTRAINT fk_chat_spam_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_spam_events_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE SET NULL,
    CONSTRAINT fk_chat_spam_events_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_chat_spam_events_message FOREIGN KEY (message_id) REFERENCES chat_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Channel settings + pinned messages
CREATE TABLE IF NOT EXISTS chat_channel_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    only_admin_can_post TINYINT(1) NOT NULL DEFAULT 0,
    slow_mode_seconds INT UNSIGNED NOT NULL DEFAULT 0,
    max_message_length INT UNSIGNED NOT NULL DEFAULT 4000,
    allow_attachments TINYINT(1) NOT NULL DEFAULT 1,
    allow_mentions TINYINT(1) NOT NULL DEFAULT 1,
    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_chat_channel_settings (tenant_id, channel_id),
    CONSTRAINT fk_chat_channel_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_channel_settings_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_pinned_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NOT NULL,
    pinned_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_pinned_message (tenant_id, channel_id, message_id),
    INDEX idx_chat_pinned_messages_tenant (tenant_id, channel_id, created_at),
    CONSTRAINT fk_chat_pinned_messages_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_pinned_messages_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_pinned_messages_message FOREIGN KEY (message_id) REFERENCES chat_messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_pinned_messages_user FOREIGN KEY (pinned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_messages ADD FULLTEXT INDEX ft_chat_messages_content (content)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 8) Soft-delete + purge policies
ALTER TABLE notifications
    ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL AFTER expires_at;

ALTER TABLE outbound_emails
    ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL AFTER last_error;

CREATE TABLE IF NOT EXISTS communication_retention_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    entity_type ENUM('notification', 'chat_message', 'email', 'alert', 'realtime_event') NOT NULL,
    retention_days INT UNSIGNED NOT NULL DEFAULT 365,
    hard_delete_enabled TINYINT(1) NOT NULL DEFAULT 0,
    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_communication_retention_policy (tenant_id, entity_type),
    CONSTRAINT fk_communication_retention_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 communication_purge_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    entity_type ENUM('notification', 'chat_message', 'email', 'alert', 'realtime_event') NOT NULL,
    status ENUM('success', 'failed', 'skipped') NOT NULL DEFAULT 'success',
    affected_rows INT UNSIGNED NOT NULL DEFAULT 0,
    message VARCHAR(255) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_communication_purge_runs_tenant (tenant_id, entity_type, created_at),
    CONSTRAINT fk_communication_purge_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE email_webhook_endpoints
    ADD COLUMN IF NOT EXISTS active_secret_version INT UNSIGNED NULL AFTER secret_key;

-- 10) Realtime subscriptions and delivery acknowledgements
CREATE TABLE IF NOT EXISTS realtime_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    stream_key VARCHAR(80) NOT NULL,
    client_id VARCHAR(120) NOT NULL,
    status ENUM('connected', 'disconnected') NOT NULL DEFAULT 'connected',
    subscribed_at DATETIME NOT NULL,
    last_seen_at DATETIME NULL,
    disconnected_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_realtime_subscriptions_tenant (tenant_id, stream_key, status, last_seen_at),
    CONSTRAINT fk_realtime_subscriptions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_realtime_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS realtime_delivery_acks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    event_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    client_id VARCHAR(120) NULL,
    ack_status ENUM('delivered', 'seen') NOT NULL DEFAULT 'delivered',
    ack_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_realtime_delivery_ack (tenant_id, event_id, user_id, client_id, ack_status),
    INDEX idx_realtime_delivery_acks_tenant (tenant_id, ack_status, ack_at),
    CONSTRAINT fk_realtime_delivery_acks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_realtime_delivery_acks_event FOREIGN KEY (event_id) REFERENCES realtime_events(id) ON DELETE CASCADE,
    CONSTRAINT fk_realtime_delivery_acks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11) Scheduler for communication jobs
CREATE TABLE IF NOT EXISTS communication_job_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_key VARCHAR(80) NOT NULL,
    cron_expr VARCHAR(80) NOT NULL,
    timezone VARCHAR(60) NOT NULL DEFAULT 'UTC',
    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_communication_job_schedule (tenant_id, job_key),
    CONSTRAINT fk_communication_job_schedules_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 communication_job_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    schedule_id BIGINT UNSIGNED NULL,
    job_key VARCHAR(80) NOT NULL,
    status ENUM('success', 'failed', 'skipped') NOT NULL DEFAULT 'success',
    message VARCHAR(255) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_communication_job_runs_tenant (tenant_id, job_key, created_at),
    CONSTRAINT fk_communication_job_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_job_runs_schedule FOREIGN KEY (schedule_id) REFERENCES communication_job_schedules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12) Operational SLA metrics snapshots
CREATE TABLE IF NOT EXISTS communication_sla_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    snapshot_date DATE NOT NULL,
    pending_notifications INT UNSIGNED NOT NULL DEFAULT 0,
    failed_notifications INT UNSIGNED NOT NULL DEFAULT 0,
    pending_emails INT UNSIGNED NOT NULL DEFAULT 0,
    failed_emails INT UNSIGNED NOT NULL DEFAULT 0,
    open_alerts INT UNSIGNED NOT NULL DEFAULT 0,
    critical_open_alerts INT UNSIGNED NOT NULL DEFAULT 0,
    avg_notification_latency_ms DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    avg_email_latency_ms DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_communication_sla_snapshot (tenant_id, snapshot_date),
    CONSTRAINT fk_communication_sla_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13) Fine-grained communication permissions
CREATE TABLE IF NOT EXISTS communication_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_communication_permission (tenant_id, role, action_key),
    CONSTRAINT fk_communication_permissions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14) Notification digest runs
CREATE TABLE IF NOT EXISTS notification_digest_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    digest_mode ENUM('hourly', 'daily') NOT NULL,
    status ENUM('queued', 'sent', 'failed', 'skipped') NOT NULL DEFAULT 'queued',
    items_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    message VARCHAR(255) NULL,
    INDEX idx_notification_digest_runs_tenant (tenant_id, digest_mode, status, created_at),
    CONSTRAINT fk_notification_digest_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_digest_runs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15) Communication quality gates (CI/ops)
CREATE TABLE IF NOT EXISTS communication_quality_gates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    gate_key VARCHAR(100) NOT NULL,
    threshold_value DECIMAL(10,2) NOT NULL,
    comparator ENUM('lte', 'gte', 'eq') NOT NULL DEFAULT 'lte',
    window_minutes INT UNSIGNED NOT NULL DEFAULT 60,
    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_communication_quality_gate (tenant_id, gate_key),
    CONSTRAINT fk_communication_quality_gates_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 communication_quality_gate_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    gate_id BIGINT UNSIGNED NOT NULL,
    measured_value DECIMAL(10,2) NOT NULL,
    status ENUM('passed', 'failed') NOT NULL DEFAULT 'passed',
    detail VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_communication_quality_gate_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_communication_quality_gate_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_quality_gate_runs_gate FOREIGN KEY (gate_id) REFERENCES communication_quality_gates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo seeds
INSERT INTO notification_templates (tenant_id, code, name, channel, title_template, body_template, locale, is_active)
SELECT t.id, 'ops.alert', 'Alerta operativa', 'in_app', 'Alerta {{severity}}', 'Se detecto un evento: {{event_name}}', 'es', 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active), body_template = VALUES(body_template);

INSERT INTO notification_rate_limits (tenant_id, channel, scope_type, scope_key, window_seconds, max_events, is_active)
SELECT t.id, 'in_app', 'tenant', NULL, 60, 300, 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE max_events = VALUES(max_events), is_active = VALUES(is_active);

INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.digest.dispatch', '*/15 * * * *', 'UTC', 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), is_active = VALUES(is_active);

INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.purge.run', '20 1 * * *', 'UTC', 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), is_active = VALUES(is_active);

INSERT INTO communication_permissions (tenant_id, role, action_key, is_allowed)
SELECT t.id, 'manager', 'communications.manage', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 'chat.moderate', 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 communication_quality_gates (tenant_id, gate_key, threshold_value, comparator, window_minutes, is_active)
SELECT t.id, 'email.failed_ratio_pct', 5.00, 'lte', 60, 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE threshold_value = VALUES(threshold_value), is_active = VALUES(is_active);
