-- Dashboard phase 2: report scheduling, KPI targets and configurable insight rules
-- Safe after 033_user_data_scopes_audit_report.sql

CREATE TABLE IF NOT EXISTS dashboard_report_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    schedule_name VARCHAR(140) NOT NULL,
    report_key VARCHAR(120) NOT NULL DEFAULT 'dashboard.executive',
    export_type ENUM('csv', 'excel', 'pdf') NOT NULL DEFAULT 'pdf',
    frequency_minutes INT UNSIGNED NOT NULL DEFAULT 1440,
    timezone VARCHAR(80) NOT NULL DEFAULT 'UTC',
    recipient_emails_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    next_run_at DATETIME NOT NULL,
    last_run_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_dashboard_report_schedule (tenant_id, schedule_name),
    INDEX idx_dashboard_report_schedules_due (tenant_id, is_active, next_run_at),
    CONSTRAINT fk_dashboard_report_schedules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_report_schedules_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 dashboard_kpi_targets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    scope_type ENUM('tenant', 'team', 'user') NOT NULL DEFAULT 'tenant',
    scope_id BIGINT UNSIGNED NULL,
    kpi_key VARCHAR(120) NOT NULL,
    target_value DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    window_label VARCHAR(40) NOT NULL DEFAULT '30d',
    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_dashboard_kpi_target (tenant_id, scope_type, scope_id, kpi_key),
    INDEX idx_dashboard_kpi_targets_lookup (tenant_id, kpi_key, scope_type, scope_id, is_active),
    CONSTRAINT fk_dashboard_kpi_targets_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_kpi_targets_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_insight_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_key VARCHAR(120) NOT NULL,
    metric_key VARCHAR(120) NOT NULL,
    comparator ENUM('gt', 'gte', 'lt', 'lte', 'eq') NOT NULL DEFAULT 'gte',
    threshold_value DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    message_template VARCHAR(255) NOT 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_dashboard_insight_rule (tenant_id, rule_key),
    INDEX idx_dashboard_insight_rules_metric (tenant_id, metric_key, is_active),
    CONSTRAINT fk_dashboard_insight_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_insight_rules_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO dashboard_kpi_targets (tenant_id, scope_type, scope_id, kpi_key, target_value, window_label, is_active)
SELECT t.id, 'tenant', NULL, 'sales.won_amount', 25000.00, '30d', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'tenant', NULL, 'sales.won_opportunities', 20.00, '30d', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'tenant', NULL, 'activities.done_range', 50.00, '30d', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'tenant', NULL, 'ops.critical_alerts_open', 0.00, 'realtime', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE target_value = VALUES(target_value), window_label = VALUES(window_label), is_active = VALUES(is_active);

INSERT INTO dashboard_insight_rules (tenant_id, rule_key, metric_key, comparator, threshold_value, severity, message_template, is_active)
SELECT t.id, 'rule.overdue.high', 'overdue_activities', 'gte', 10.00, 'high', 'Actividades vencidas altas: {value}. Recomendado: priorizar seguimiento hoy.', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'rule.alerts.critical', 'critical_alerts_open', 'gte', 1.00, 'critical', 'Alertas criticas abiertas: {value}. Recomendado: activar protocolo de incidentes.', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'rule.notifications.backlog', 'notifications_unread', 'gte', 40.00, 'medium', 'Notificaciones sin leer: {value}. Ajustar prioridades y limpieza.', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE
    metric_key = VALUES(metric_key),
    comparator = VALUES(comparator),
    threshold_value = VALUES(threshold_value),
    severity = VALUES(severity),
    message_template = VALUES(message_template),
    is_active = VALUES(is_active);

INSERT INTO dashboard_report_schedules (tenant_id, schedule_name, report_key, export_type, frequency_minutes, timezone, recipient_emails_json, is_active, next_run_at, created_by)
SELECT t.id, 'Resumen diario direccion', 'dashboard.executive', 'pdf', 1440, 'UTC', JSON_ARRAY('direccion@demo.local'), 0, NOW(), NULL
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE
    report_key = VALUES(report_key),
    export_type = VALUES(export_type),
    frequency_minutes = VALUES(frequency_minutes),
    timezone = VALUES(timezone),
    recipient_emails_json = VALUES(recipient_emails_json);
