-- Module 11 enterprise hardening (15 improvements)
-- Safe after 025_communications_notifications.sql

-- 1) Notification batches/campaigns
CREATE TABLE IF NOT EXISTS notification_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    notification_type VARCHAR(60) NOT NULL DEFAULT 'campaign',
    channel VARCHAR(40) NOT NULL DEFAULT 'in_app',
    total_recipients INT UNSIGNED NOT NULL DEFAULT 0,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    failed_count INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('draft', 'queued', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'draft',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_notification_batches_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_notification_batches_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_batches_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 batch_id BIGINT UNSIGNED NULL AFTER channel;
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE notifications ADD CONSTRAINT fk_notifications_batch FOREIGN KEY (batch_id) REFERENCES notification_batches(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2) Notification dedupe
ALTER TABLE notifications
    ADD COLUMN IF NOT EXISTS dedupe_key VARCHAR(120) NULL AFTER notification_type,
    ADD COLUMN IF NOT EXISTS dedupe_window_minutes INT UNSIGNED NOT NULL DEFAULT 60 AFTER dedupe_key;
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE notifications ADD INDEX idx_notifications_dedupe (tenant_id, user_id, dedupe_key, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3) Chat attachments
CREATE TABLE IF NOT EXISTS chat_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(190) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT UNSIGNED NULL,
    uploaded_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_attachments_tenant_message (tenant_id, message_id, created_at),
    CONSTRAINT fk_chat_attachments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_attachments_message FOREIGN KEY (message_id) REFERENCES chat_messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_attachments_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) Presence and typing
CREATE TABLE IF NOT EXISTS chat_presence (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('online', 'away', 'offline') NOT NULL DEFAULT 'offline',
    last_seen_at DATETIME NULL,
    heartbeat_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_presence_user (tenant_id, user_id),
    CONSTRAINT fk_chat_presence_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_presence_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 chat_typing_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    started_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_typing_events_tenant (tenant_id, channel_id, expires_at),
    CONSTRAINT fk_chat_typing_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_typing_events_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_typing_events_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Threads
ALTER TABLE chat_messages
    ADD COLUMN IF NOT EXISTS thread_root_id BIGINT UNSIGNED NULL AFTER reply_to_id;
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE chat_messages ADD CONSTRAINT fk_chat_messages_thread_root FOREIGN KEY (thread_root_id) REFERENCES chat_messages(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE chat_messages ADD INDEX idx_chat_messages_thread (tenant_id, channel_id, thread_root_id, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6) Moderation and retention
CREATE TABLE IF NOT EXISTS chat_moderation_actions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    target_user_id BIGINT UNSIGNED NULL,
    action_type ENUM('mute', 'unmute', 'ban', 'unban', 'delete_message') NOT NULL,
    reason VARCHAR(255) NULL,
    expires_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_moderation_actions_tenant (tenant_id, action_type, created_at),
    CONSTRAINT fk_chat_moderation_actions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_moderation_actions_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE SET NULL,
    CONSTRAINT fk_chat_moderation_actions_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_chat_moderation_actions_target FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_retention_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NULL,
    retention_days INT UNSIGNED NOT NULL DEFAULT 365,
    delete_attachments 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_chat_retention_policy (tenant_id, channel_id),
    CONSTRAINT fk_chat_retention_policies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_retention_policies_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7) Email variables enforcement
CREATE TABLE IF NOT EXISTS email_template_constraints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    required_variables_json JSON NULL,
    blocked_variables_json JSON NULL,
    strict_mode 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_template_constraint (tenant_id, template_id),
    CONSTRAINT fk_email_template_constraints_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_template_constraints_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8) Email preview snapshots
CREATE TABLE IF NOT EXISTS email_template_previews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    variables_json JSON NULL,
    rendered_subject VARCHAR(190) NOT NULL,
    rendered_html MEDIUMTEXT NULL,
    rendered_text MEDIUMTEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_template_previews_tenant (tenant_id, template_id, created_at),
    CONSTRAINT fk_email_template_previews_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_template_previews_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_template_previews_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9) Outbound email retry + DLQ
CREATE TABLE IF NOT EXISTS outbound_email_retry_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED 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_outbound_email_retry_policy (tenant_id),
    CONSTRAINT fk_outbound_email_retry_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 outbound_email_dead_letters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    outbound_email_id BIGINT UNSIGNED NULL,
    recipient_email VARCHAR(190) NULL,
    subject VARCHAR(190) NULL,
    payload_json JSON NULL,
    error_message VARCHAR(255) NULL,
    status ENUM('open', 'replayed', 'ignored') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    replayed_at DATETIME NULL,
    INDEX idx_outbound_email_dead_letters_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_outbound_email_dead_letters_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_outbound_email_dead_letters_email FOREIGN KEY (outbound_email_id) REFERENCES outbound_emails(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_webhook_deliveries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    endpoint_id BIGINT UNSIGNED NOT NULL,
    outbound_email_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(60) NOT 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_email_webhook_deliveries_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_email_webhook_deliveries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_webhook_deliveries_endpoint FOREIGN KEY (endpoint_id) REFERENCES email_webhook_endpoints(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_webhook_deliveries_email FOREIGN KEY (outbound_email_id) REFERENCES outbound_emails(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11) Alert correlation and dedupe
ALTER TABLE alert_center_items
    ADD COLUMN IF NOT EXISTS correlation_key VARCHAR(120) NULL AFTER alert_type,
    ADD COLUMN IF NOT EXISTS occurrence_count INT UNSIGNED NOT NULL DEFAULT 1 AFTER correlation_key;
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE alert_center_items ADD INDEX idx_alert_center_correlation (tenant_id, correlation_key, status, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS alert_correlation_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    alert_type VARCHAR(80) NOT NULL,
    dedupe_window_minutes INT UNSIGNED NOT NULL DEFAULT 15,
    max_occurrences_before_escalation INT UNSIGNED NOT NULL DEFAULT 5,
    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_alert_correlation_rule (tenant_id, alert_type),
    CONSTRAINT fk_alert_correlation_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12) Alert SLA escalation
CREATE TABLE IF NOT EXISTS alert_sla_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    severity ENUM('info', 'warning', 'critical') NOT NULL,
    first_escalation_minutes INT UNSIGNED NOT NULL DEFAULT 30,
    second_escalation_minutes 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_alert_sla_policy (tenant_id, severity),
    CONSTRAINT fk_alert_sla_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 alert_escalation_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    alert_id BIGINT UNSIGNED NOT NULL,
    escalation_level TINYINT UNSIGNED NOT NULL DEFAULT 1,
    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_alert_escalation_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_alert_escalation_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_alert_escalation_runs_alert FOREIGN KEY (alert_id) REFERENCES alert_center_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13) Quiet hours enforcement and delivery queue
ALTER TABLE notification_preferences
    ADD COLUMN IF NOT EXISTS timezone VARCHAR(60) NOT NULL DEFAULT 'UTC' AFTER quiet_hours_json,
    ADD COLUMN IF NOT EXISTS digest_mode ENUM('off', 'hourly', 'daily') NOT NULL DEFAULT 'off' AFTER timezone;

CREATE TABLE IF NOT EXISTS notification_delivery_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    notification_id BIGINT UNSIGNED NOT NULL,
    channel VARCHAR(40) NOT NULL DEFAULT 'in_app',
    status ENUM('pending', 'sent', 'failed', 'skipped') NOT NULL DEFAULT 'pending',
    scheduled_for DATETIME NULL,
    sent_at DATETIME NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_error VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notification_delivery_queue_tenant (tenant_id, status, scheduled_for),
    CONSTRAINT fk_notification_delivery_queue_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_delivery_queue_notification FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14) Communication audit log
CREATE TABLE IF NOT EXISTS communication_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    entity_type VARCHAR(60) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    old_value JSON NULL,
    new_value JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_communication_audit_logs_tenant (tenant_id, entity_type, created_at),
    CONSTRAINT fk_communication_audit_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_audit_logs_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15) E2E tests registry
CREATE TABLE IF NOT EXISTS communication_test_cases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    scenario_type ENUM('notification', 'chat', 'email', 'alert', 'realtime') NOT NULL,
    input_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_communication_test_case (tenant_id, name),
    CONSTRAINT fk_communication_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 communication_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_communication_test_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_communication_test_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_test_runs_case FOREIGN KEY (test_case_id) REFERENCES communication_test_cases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo seeds
INSERT INTO alert_correlation_rules (tenant_id, alert_type, dedupe_window_minutes, max_occurrences_before_escalation, is_active)
SELECT t.id, 'system.heartbeat', 15, 5, 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE dedupe_window_minutes = VALUES(dedupe_window_minutes), is_active = VALUES(is_active);

INSERT INTO alert_sla_policies (tenant_id, severity, first_escalation_minutes, second_escalation_minutes, is_active)
SELECT t.id, 'critical', 10, 30, 1
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE first_escalation_minutes = VALUES(first_escalation_minutes), is_active = VALUES(is_active);

INSERT INTO outbound_email_retry_policies (tenant_id, base_delay_seconds, max_delay_seconds, max_attempts, jitter_ratio, is_active)
SELECT t.id, 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);
