-- Module 11: Comunicacion y Notificaciones
-- Notificaciones internas, chat interno, email templates y centro de alertas en tiempo real.

ALTER TABLE notifications
    ADD COLUMN IF NOT EXISTS notification_type VARCHAR(60) NOT NULL DEFAULT 'system' AFTER user_id,
    ADD COLUMN IF NOT EXISTS severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium' AFTER notification_type,
    ADD COLUMN IF NOT EXISTS entity_type VARCHAR(60) NULL AFTER severity,
    ADD COLUMN IF NOT EXISTS entity_id BIGINT UNSIGNED NULL AFTER entity_type,
    ADD COLUMN IF NOT EXISTS channel VARCHAR(40) NOT NULL DEFAULT 'in_app' AFTER entity_id,
    ADD COLUMN IF NOT EXISTS delivered_at DATETIME NULL AFTER read_at,
    ADD COLUMN IF NOT EXISTS expires_at DATETIME NULL AFTER delivered_at;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'notifications'
      AND index_name = 'idx_notifications_center'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE notifications ADD INDEX idx_notifications_center (tenant_id, user_id, read_at, severity, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS notification_preferences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    notification_type VARCHAR(60) NOT NULL,
    in_app_enabled TINYINT(1) NOT NULL DEFAULT 1,
    email_enabled TINYINT(1) NOT NULL DEFAULT 0,
    push_enabled TINYINT(1) NOT NULL DEFAULT 0,
    quiet_hours_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_notification_preference (tenant_id, user_id, notification_type),
    CONSTRAINT fk_notification_preferences_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_preferences_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_channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    channel_type ENUM('direct', 'group', 'announcement') NOT NULL DEFAULT 'group',
    created_by BIGINT UNSIGNED 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_chat_channels_name (tenant_id, name),
    INDEX idx_chat_channels_tenant (tenant_id, is_active, created_at),
    CONSTRAINT fk_chat_channels_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_channels_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 chat_channel_members (
    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,
    role ENUM('member', 'moderator', 'owner') NOT NULL DEFAULT 'member',
    is_muted TINYINT(1) NOT NULL DEFAULT 0,
    joined_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_channel_member (tenant_id, channel_id, user_id),
    INDEX idx_chat_channel_members_user (tenant_id, user_id),
    CONSTRAINT fk_chat_channel_members_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_channel_members_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_channel_members_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_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    sender_user_id BIGINT UNSIGNED NULL,
    message_type ENUM('text', 'system') NOT NULL DEFAULT 'text',
    content TEXT NOT NULL,
    mentions_json JSON NULL,
    reply_to_id BIGINT UNSIGNED NULL,
    edited_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_messages_channel (tenant_id, channel_id, created_at),
    CONSTRAINT fk_chat_messages_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_messages_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_chat_messages_reply FOREIGN KEY (reply_to_id) REFERENCES chat_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_message_reads (
    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,
    last_read_message_id BIGINT UNSIGNED NULL,
    last_read_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_message_read (tenant_id, channel_id, user_id),
    CONSTRAINT fk_chat_message_reads_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_message_reads_channel FOREIGN KEY (channel_id) REFERENCES chat_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_message_reads_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_message_reads_message FOREIGN KEY (last_read_message_id) REFERENCES chat_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(140) NOT NULL,
    subject_template VARCHAR(190) NOT NULL,
    body_html MEDIUMTEXT NULL,
    body_text MEDIUMTEXT 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_email_template_code (tenant_id, code, locale),
    CONSTRAINT fk_email_templates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_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 email_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_email_template_version (tenant_id, template_id, version),
    CONSTRAINT fk_email_template_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_template_versions_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_template_versions_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 outbound_emails (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NULL,
    recipient_email VARCHAR(190) NOT NULL,
    recipient_name VARCHAR(140) NULL,
    subject VARCHAR(190) NOT NULL,
    body_html MEDIUMTEXT NULL,
    body_text MEDIUMTEXT NULL,
    variables_json JSON NULL,
    status ENUM('queued', 'sent', 'failed', 'cancelled') NOT NULL DEFAULT 'queued',
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    last_error VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_outbound_emails_tenant_status (tenant_id, status, created_at),
    CONSTRAINT fk_outbound_emails_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_outbound_emails_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_outbound_emails_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 outbound_email_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    outbound_email_id BIGINT UNSIGNED NOT NULL,
    event_type ENUM('queued', 'sent', 'failed', 'opened', 'clicked', 'bounced', 'complained') NOT NULL,
    detail VARCHAR(255) NULL,
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_outbound_email_events_tenant (tenant_id, outbound_email_id, created_at),
    CONSTRAINT fk_outbound_email_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_outbound_email_events_email FOREIGN KEY (outbound_email_id) REFERENCES outbound_emails(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS alert_center_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    alert_type VARCHAR(80) NOT NULL,
    severity ENUM('info', 'warning', 'critical') NOT NULL DEFAULT 'info',
    title VARCHAR(190) NOT NULL,
    message VARCHAR(255) NULL,
    source_module VARCHAR(60) NULL,
    source_ref_id BIGINT UNSIGNED NULL,
    status ENUM('open', 'acknowledged', 'resolved') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at DATETIME NULL,
    resolved_at DATETIME NULL,
    INDEX idx_alert_center_items_tenant (tenant_id, status, severity, created_at),
    CONSTRAINT fk_alert_center_items_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 realtime_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stream_key VARCHAR(80) NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    payload_json JSON NULL,
    delivered TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    delivered_at DATETIME NULL,
    INDEX idx_realtime_events_tenant (tenant_id, stream_key, delivered, created_at),
    CONSTRAINT fk_realtime_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO chat_channels (tenant_id, name, channel_type, is_active, created_by)
SELECT t.id, 'General', 'group', 1, NULL
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);

INSERT INTO email_templates (tenant_id, code, name, subject_template, body_html, body_text, locale, is_active)
SELECT t.id, 'welcome', 'Bienvenida', 'Bienvenido {{name}}',
       '<p>Hola {{name}}, bienvenido a CRM1.</p>',
       'Hola {{name}}, bienvenido a CRM1.',
       'es', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active), subject_template = VALUES(subject_template);

INSERT INTO alert_center_items (tenant_id, alert_type, severity, title, message, source_module, status)
SELECT t.id, 'system.heartbeat', 'info', 'Centro de alertas activo', 'El modulo 11 esta inicializado', 'communications', 'open'
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE message = VALUES(message);
