-- Module 11 top-7 execution hardening
-- Safe after 027_communications_closure_hardening.sql

-- 1) Unified communication events catalog + event bus table
CREATE TABLE IF NOT EXISTS communication_event_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    event_key VARCHAR(100) NOT NULL,
    entity_type VARCHAR(60) NULL,
    default_severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    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_event_type (tenant_id, event_key),
    CONSTRAINT fk_communication_event_types_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_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    event_type_id BIGINT UNSIGNED NULL,
    event_key VARCHAR(100) NOT NULL,
    entity_type VARCHAR(60) NULL,
    entity_id BIGINT UNSIGNED NULL,
    payload_json JSON NULL,
    source_module VARCHAR(60) NULL,
    status ENUM('pending', 'processed', 'failed') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    INDEX idx_communication_events_tenant (tenant_id, event_key, status, created_at),
    CONSTRAINT fk_communication_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_events_type FOREIGN KEY (event_type_id) REFERENCES communication_event_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Business validations for key operational tables
SET @chk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'notification_rate_limits'
      AND constraint_type = 'CHECK'
      AND constraint_name = 'chk_notification_rate_limits_scope_key'
);
SET @chk_sql := IF(
    @chk_exists = 0,
    'ALTER TABLE notification_rate_limits ADD CONSTRAINT chk_notification_rate_limits_scope_key CHECK ((scope_type = ''tenant'' AND scope_key IS NULL) OR (scope_type IN (''user'', ''notification_type'') AND scope_key IS NOT NULL))',
    'SELECT 1'
);
PREPARE stmt FROM @chk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @chk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'notification_rate_limits'
      AND constraint_type = 'CHECK'
      AND constraint_name = 'chk_notification_rate_limits_positive'
);
SET @chk_sql := IF(
    @chk_exists = 0,
    'ALTER TABLE notification_rate_limits ADD CONSTRAINT chk_notification_rate_limits_positive CHECK (window_seconds > 0 AND max_events > 0)',
    'SELECT 1'
);
PREPARE stmt FROM @chk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @chk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'notification_delivery_attempts'
      AND constraint_type = 'CHECK'
      AND constraint_name = 'chk_notification_delivery_attempts_attempt'
);
SET @chk_sql := IF(
    @chk_exists = 0,
    'ALTER TABLE notification_delivery_attempts ADD CONSTRAINT chk_notification_delivery_attempts_attempt CHECK (attempt >= 1)',
    'SELECT 1'
);
PREPARE stmt FROM @chk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3) Mandatory operational jobs
INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.delivery.dispatch', '* * * * *', 'UTC', 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);

INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.email.retry', '*/5 * * * *', 'UTC', 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);

INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.chat.typing.cleanup', '*/2 * * * *', 'UTC', 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);

INSERT INTO communication_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'communications.alerts.escalation', '*/10 * * * *', 'UTC', 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);

-- 4) Observability metrics and derived operational alerts
CREATE TABLE IF NOT EXISTS communication_live_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    metric_key VARCHAR(100) NOT NULL,
    metric_value DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    metric_count INT UNSIGNED NOT NULL DEFAULT 0,
    window_start DATETIME NOT NULL,
    window_end DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_communication_live_metric (tenant_id, metric_key, window_start),
    INDEX idx_communication_live_metrics_tenant (tenant_id, metric_key, window_end),
    CONSTRAINT fk_communication_live_metrics_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_operational_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    metric_key VARCHAR(100) NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    threshold_value DECIMAL(14,4) NOT NULL,
    observed_value DECIMAL(14,4) NOT NULL,
    status ENUM('open', 'acknowledged', 'resolved') NOT NULL DEFAULT 'open',
    message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    INDEX idx_communication_operational_alerts_tenant (tenant_id, status, severity, created_at),
    CONSTRAINT fk_communication_operational_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Strong webhook security (HMAC + replay guard)
ALTER TABLE email_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,
    ADD COLUMN IF NOT EXISTS require_https TINYINT(1) NOT NULL DEFAULT 1 AFTER replay_ttl_seconds;

ALTER TABLE email_webhook_deliveries
    ADD COLUMN IF NOT EXISTS signature_hash CHAR(64) NULL AFTER event_type,
    ADD COLUMN IF NOT EXISTS request_id VARCHAR(120) NULL AFTER signature_hash;

CREATE TABLE IF NOT EXISTS email_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,
    request_id VARCHAR(120) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uq_email_webhook_replay_guard (tenant_id, endpoint_id, signature_hash),
    INDEX idx_email_webhook_replay_guard_expiry (tenant_id, expires_at),
    CONSTRAINT fk_email_webhook_replay_guards_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_webhook_replay_guards_endpoint FOREIGN KEY (endpoint_id) REFERENCES email_webhook_endpoints(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Messenger-like UX support (reactions + inbox counters)
CREATE TABLE IF NOT EXISTS chat_message_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction VARCHAR(24) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_message_reaction (tenant_id, message_id, user_id, reaction),
    INDEX idx_chat_message_reactions_tenant (tenant_id, message_id, created_at),
    CONSTRAINT fk_chat_message_reactions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_message_reactions_message FOREIGN KEY (message_id) REFERENCES chat_messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_message_reactions_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_user_inbox_stats (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    unread_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_message_id BIGINT UNSIGNED NULL,
    last_message_at DATETIME NULL,
    is_archived TINYINT(1) NOT NULL DEFAULT 0,
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_user_inbox_stats (tenant_id, user_id, channel_id),
    INDEX idx_chat_user_inbox_stats_tenant (tenant_id, user_id, is_pinned, updated_at),
    CONSTRAINT fk_chat_user_inbox_stats_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_user_inbox_stats_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_user_inbox_stats_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_user_inbox_stats_message FOREIGN KEY (last_message_id) REFERENCES chat_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7) CI quality policies + release checks
CREATE TABLE IF NOT EXISTS communication_gate_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    gate_id BIGINT UNSIGNED NOT NULL,
    on_fail_action ENUM('warn', 'block_release') NOT NULL DEFAULT 'block_release',
    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_gate_policy (tenant_id, gate_id),
    CONSTRAINT fk_communication_gate_policies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_communication_gate_policies_gate FOREIGN KEY (gate_id) REFERENCES communication_quality_gates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_release_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    release_ref VARCHAR(120) NOT NULL,
    status ENUM('passed', 'failed') NOT NULL DEFAULT 'passed',
    total_gates INT UNSIGNED NOT NULL DEFAULT 0,
    failed_gates INT UNSIGNED NOT NULL DEFAULT 0,
    summary VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_communication_release_check (tenant_id, release_ref),
    INDEX idx_communication_release_checks_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_communication_release_checks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo seeds
INSERT INTO communication_event_types (tenant_id, event_key, entity_type, default_severity, is_active)
SELECT t.id, 'chat.message.created', 'chat_message', 'low', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'notification.sent', 'notification', 'medium', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'email.failed', 'outbound_email', 'high', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE default_severity = VALUES(default_severity), is_active = VALUES(is_active);

INSERT INTO communication_quality_gates (tenant_id, gate_key, threshold_value, comparator, window_minutes, is_active)
SELECT t.id, 'notification.queue.backlog', 500.00, 'lte', 30, 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE threshold_value = VALUES(threshold_value), is_active = VALUES(is_active);

INSERT INTO communication_gate_policies (tenant_id, gate_id, on_fail_action, is_active)
SELECT g.tenant_id, g.id, 'block_release', 1
FROM communication_quality_gates g
INNER JOIN tenants t ON t.id = g.tenant_id
WHERE t.slug = 'demo' AND g.gate_key IN ('email.failed_ratio_pct', 'notification.queue.backlog')
ON DUPLICATE KEY UPDATE on_fail_action = VALUES(on_fail_action), is_active = VALUES(is_active);

-- Stability patch: idempotency + operational indexing
-- A) Normalize nullable scope_key for true uniqueness in notification_rate_limits
ALTER TABLE notification_rate_limits
    ADD COLUMN IF NOT EXISTS scope_key_norm VARCHAR(120)
        GENERATED ALWAYS AS (IFNULL(scope_key, '__NULL__')) STORED;

-- Remove potential duplicates produced by NULL semantics before enforcing normalized unique key
DELETE n1 FROM notification_rate_limits n1
INNER JOIN notification_rate_limits n2
    ON n1.tenant_id = n2.tenant_id
   AND n1.channel = n2.channel
   AND n1.scope_type = n2.scope_type
   AND IFNULL(n1.scope_key, '__NULL__') = IFNULL(n2.scope_key, '__NULL__')
   AND n1.window_seconds = n2.window_seconds
   AND n1.id > n2.id;

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

-- B) Scheduler and realtime hot-path indexes
SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'communication_job_schedules'
      AND index_name = 'idx_communication_job_schedules_due'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE communication_job_schedules ADD INDEX idx_communication_job_schedules_due (tenant_id, is_active, next_run_at)',
    '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 = 'realtime_subscriptions'
      AND index_name = 'idx_realtime_subscriptions_user_status'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE realtime_subscriptions ADD INDEX idx_realtime_subscriptions_user_status (tenant_id, user_id, status, last_seen_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
