-- Modules 12-16 completion baseline
-- Safe after 028_communications_top7_execution.sql

-- 12) Dashboard y reportes
CREATE TABLE IF NOT EXISTS dashboard_report_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    report_key VARCHAR(100) NOT NULL,
    period_key VARCHAR(30) NOT NULL DEFAULT 'daily',
    kpis_json JSON NULL,
    generated_by BIGINT UNSIGNED NULL,
    generated_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_dashboard_report_snapshots_tenant (tenant_id, report_key, generated_at),
    CONSTRAINT fk_dashboard_report_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_report_snapshots_user FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_export_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    requested_by BIGINT UNSIGNED NULL,
    export_type ENUM('excel', 'pdf', 'csv') NOT NULL DEFAULT 'excel',
    report_key VARCHAR(100) NOT NULL,
    status ENUM('queued', 'running', 'done', 'failed') NOT NULL DEFAULT 'queued',
    file_path VARCHAR(255) NULL,
    message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME NULL,
    INDEX idx_dashboard_export_jobs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_dashboard_export_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_export_jobs_user FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13) Configuracion y personalizacion
CREATE TABLE IF NOT EXISTS crm_saved_views (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    view_name VARCHAR(120) NOT NULL,
    filters_json JSON NULL,
    columns_json JSON NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    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_crm_saved_views_tenant_name (tenant_id, module_key, view_name),
    INDEX idx_crm_saved_views_tenant_module (tenant_id, module_key, created_at),
    CONSTRAINT fk_crm_saved_views_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_crm_saved_views_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 tenant_branding_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    brand_name VARCHAR(140) NULL,
    primary_color VARCHAR(20) NULL,
    secondary_color VARCHAR(20) NULL,
    logo_url VARCHAR(255) NULL,
    favicon_url VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    updated_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_tenant_branding_settings_tenant (tenant_id),
    CONSTRAINT fk_tenant_branding_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_tenant_branding_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14) API e integraciones
CREATE TABLE IF NOT EXISTS integration_connections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    provider ENUM('email', 'whatsapp', 'payments', 'custom_webhook') NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    config_json JSON NULL,
    status ENUM('active', 'inactive', 'error') NOT NULL DEFAULT 'active',
    last_sync_at DATETIME NULL,
    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_integration_connections_tenant_name (tenant_id, provider, display_name),
    INDEX idx_integration_connections_tenant (tenant_id, provider, status),
    CONSTRAINT fk_integration_connections_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_connections_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 integration_sync_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    connection_id BIGINT UNSIGNED NOT NULL,
    status ENUM('success', 'failed', 'partial') NOT NULL DEFAULT 'success',
    items_processed 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_integration_sync_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_integration_sync_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_integration_sync_runs_connection FOREIGN KEY (connection_id) REFERENCES integration_connections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15) Soporte SaaS y operacion
CREATE TABLE IF NOT EXISTS internal_support_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    requester_user_id BIGINT UNSIGNED NULL,
    assigned_user_id BIGINT UNSIGNED NULL,
    category VARCHAR(80) NOT NULL DEFAULT 'general',
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    subject VARCHAR(180) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    INDEX idx_internal_support_tickets_tenant (tenant_id, status, priority, created_at),
    CONSTRAINT fk_internal_support_tickets_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_internal_support_tickets_requester FOREIGN KEY (requester_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_internal_support_tickets_assigned FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS internal_support_ticket_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    ticket_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    message TEXT NOT NULL,
    is_internal_note TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_internal_support_ticket_messages_tenant (tenant_id, ticket_id, created_at),
    CONSTRAINT fk_internal_support_ticket_messages_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_internal_support_ticket_messages_ticket FOREIGN KEY (ticket_id) REFERENCES internal_support_tickets(id) ON DELETE CASCADE,
    CONSTRAINT fk_internal_support_ticket_messages_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_health_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    check_key VARCHAR(100) NOT NULL,
    status ENUM('ok', 'warning', 'critical') NOT NULL DEFAULT 'ok',
    value_text VARCHAR(255) NULL,
    measured_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_system_health_snapshots_tenant (tenant_id, status, measured_at),
    CONSTRAINT fk_system_health_snapshots_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 system_backup_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    backup_type ENUM('full', 'incremental') NOT NULL DEFAULT 'full',
    status ENUM('success', 'failed', 'running') NOT NULL DEFAULT 'running',
    storage_path VARCHAR(255) NULL,
    size_bytes BIGINT UNSIGNED NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_system_backup_runs_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_system_backup_runs_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 technical_log_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    level ENUM('info', 'warning', 'error') NOT NULL DEFAULT 'info',
    component VARCHAR(100) NOT NULL,
    message VARCHAR(255) NOT NULL,
    context_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_technical_log_entries_tenant (tenant_id, level, created_at),
    CONSTRAINT fk_technical_log_entries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16) Onboarding y portal cliente
CREATE TABLE IF NOT EXISTS onboarding_journeys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    owner_user_id BIGINT UNSIGNED NULL,
    status ENUM('not_started', 'in_progress', 'completed') NOT NULL DEFAULT 'not_started',
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_onboarding_journeys_tenant (tenant_id),
    CONSTRAINT fk_onboarding_journeys_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_onboarding_journeys_user FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS onboarding_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    journey_id BIGINT UNSIGNED NOT NULL,
    task_key VARCHAR(100) NOT NULL,
    title VARCHAR(190) NOT NULL,
    status ENUM('pending', 'done') NOT NULL DEFAULT 'pending',
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    completed_at DATETIME NULL,
    completed_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_onboarding_tasks_task (tenant_id, journey_id, task_key),
    INDEX idx_onboarding_tasks_tenant (tenant_id, status, sort_order),
    CONSTRAINT fk_onboarding_tasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_onboarding_tasks_journey FOREIGN KEY (journey_id) REFERENCES onboarding_journeys(id) ON DELETE CASCADE,
    CONSTRAINT fk_onboarding_tasks_user FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS client_portal_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL,
    access_token_hash CHAR(64) NOT NULL,
    status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_client_portal_users_tenant_email (tenant_id, email),
    UNIQUE KEY uq_client_portal_users_token_hash (access_token_hash),
    INDEX idx_client_portal_users_tenant (tenant_id, status),
    CONSTRAINT fk_client_portal_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_portal_users_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS client_portal_activity (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    portal_user_id BIGINT UNSIGNED NOT NULL,
    event_key VARCHAR(100) NOT NULL,
    details_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client_portal_activity_tenant (tenant_id, event_key, created_at),
    CONSTRAINT fk_client_portal_activity_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_portal_activity_user FOREIGN KEY (portal_user_id) REFERENCES client_portal_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo seeds
INSERT INTO tenant_branding_settings (tenant_id, brand_name, primary_color, secondary_color, is_active)
SELECT t.id, 'CRM1 Demo', '#0d6efd', '#212529', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE brand_name = VALUES(brand_name), primary_color = VALUES(primary_color), secondary_color = VALUES(secondary_color);

INSERT INTO crm_saved_views (tenant_id, module_key, view_name, filters_json, columns_json, is_default)
SELECT t.id, 'pipeline', 'Pipeline abierto', JSON_OBJECT('status', 'open'), JSON_ARRAY('title', 'amount', 'stage_name', 'owner_name'), 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_default = VALUES(is_default), filters_json = VALUES(filters_json), columns_json = VALUES(columns_json);

INSERT INTO integration_connections (tenant_id, provider, display_name, config_json, status)
SELECT t.id, 'email', 'SMTP Principal', JSON_OBJECT('provider', 'smtp'), 'active' FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'whatsapp', 'WhatsApp Cloud', JSON_OBJECT('provider', 'meta'), 'inactive' FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'payments', 'Pasarela de pagos', JSON_OBJECT('provider', 'stripe'), 'active' FROM tenants t WHERE t.slug = 'demo';
ON DUPLICATE KEY UPDATE config_json = VALUES(config_json), status = VALUES(status);

INSERT INTO onboarding_journeys (tenant_id, owner_user_id, status, started_at)
SELECT t.id, u.id, 'in_progress', NOW()
FROM tenants t
LEFT JOIN users u ON u.tenant_id = t.id AND u.role IN ('owner', 'admin')
WHERE t.slug = 'demo'
LIMIT 1
ON DUPLICATE KEY UPDATE status = VALUES(status), started_at = VALUES(started_at);

INSERT INTO onboarding_tasks (tenant_id, journey_id, task_key, title, status, sort_order)
SELECT j.tenant_id, j.id, 'company_profile', 'Completar perfil de empresa', 'done', 1
FROM onboarding_journeys j
INNER JOIN tenants t ON t.id = j.tenant_id
WHERE t.slug = 'demo'
UNION ALL
SELECT j.tenant_id, j.id, 'import_contacts', 'Importar contactos iniciales', 'pending', 2
FROM onboarding_journeys j
INNER JOIN tenants t ON t.id = j.tenant_id
WHERE t.slug = 'demo'
UNION ALL
SELECT j.tenant_id, j.id, 'create_pipeline', 'Configurar pipeline comercial', 'pending', 3
FROM onboarding_journeys j
INNER JOIN tenants t ON t.id = j.tenant_id
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE title = VALUES(title), sort_order = VALUES(sort_order);

INSERT INTO communication_permissions (tenant_id, role, action_key, is_allowed)
SELECT t.id, 'manager', 'reports.view', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 'integrations.manage', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 'operations.support', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed);
