-- Dashboard production hardening
-- Safe after 029_crm_completion_modules_12_16.sql

-- 1) KPI contract definitions
CREATE TABLE IF NOT EXISTS dashboard_kpi_definitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    kpi_key VARCHAR(100) NOT NULL,
    display_name VARCHAR(160) NOT NULL,
    formula_text VARCHAR(500) NOT NULL,
    owner_module VARCHAR(60) NOT NULL DEFAULT 'dashboard',
    default_window VARCHAR(40) NOT NULL DEFAULT '30d',
    unit VARCHAR(40) NOT NULL DEFAULT 'count',
    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_dashboard_kpi_definitions (tenant_id, kpi_key),
    INDEX idx_dashboard_kpi_definitions_tenant (tenant_id, owner_module, is_active),
    CONSTRAINT fk_dashboard_kpi_definitions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Refresh lock to avoid stampede recalculation
CREATE TABLE IF NOT EXISTS dashboard_refresh_locks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    lock_key VARCHAR(120) NOT NULL,
    owner_key VARCHAR(120) NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dashboard_refresh_locks (tenant_id, lock_key),
    INDEX idx_dashboard_refresh_locks_expiry (tenant_id, expires_at),
    CONSTRAINT fk_dashboard_refresh_locks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) Widget preferences
CREATE TABLE IF NOT EXISTS dashboard_widget_preferences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    widget_key VARCHAR(100) NOT NULL,
    is_visible TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    settings_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dashboard_widget_preference (tenant_id, user_id, widget_key),
    INDEX idx_dashboard_widget_preferences_tenant (tenant_id, user_id, sort_order),
    CONSTRAINT fk_dashboard_widget_preferences_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_widget_preferences_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) Dashboard telemetry
CREATE TABLE IF NOT EXISTS dashboard_telemetry_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    event_type ENUM('dashboard_load', 'dashboard_refresh', 'dashboard_export', 'dashboard_drilldown') NOT NULL,
    duration_ms INT UNSIGNED NULL,
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_dashboard_telemetry_tenant (tenant_id, event_type, created_at),
    CONSTRAINT fk_dashboard_telemetry_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_dashboard_telemetry_events_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Data quality checks
CREATE TABLE IF NOT EXISTS dashboard_data_quality_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    check_key VARCHAR(100) NOT NULL,
    status ENUM('passed', 'failed', 'warning') NOT NULL DEFAULT 'passed',
    expected_value DECIMAL(14,4) NULL,
    observed_value DECIMAL(14,4) NULL,
    detail VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_dashboard_data_quality_tenant (tenant_id, check_key, status, created_at),
    CONSTRAINT fk_dashboard_data_quality_checks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Optional artifact metadata for export jobs
SET @tbl_exists := (
    SELECT COUNT(*)
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name = 'dashboard_export_jobs'
);
SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'dashboard_export_jobs'
      AND column_name = 'requested_params_json'
);
SET @alter_sql := IF(
    @tbl_exists = 1 AND @col_exists = 0,
    'ALTER TABLE dashboard_export_jobs ADD COLUMN requested_params_json JSON NULL AFTER report_key',
    'SELECT 1'
);
PREPARE stmt FROM @alter_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7) Operational indexes for dashboard hot queries
SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'opportunities'
      AND index_name = 'idx_opportunities_tenant_status_updated'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE opportunities ADD INDEX idx_opportunities_tenant_status_updated (tenant_id, status, updated_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 = 'sales_activities'
      AND index_name = 'idx_sales_activities_tenant_status_completed'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE sales_activities ADD INDEX idx_sales_activities_tenant_status_completed (tenant_id, status, completed_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 = 'notifications'
      AND index_name = 'idx_notifications_tenant_read_created'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE notifications ADD INDEX idx_notifications_tenant_read_created (tenant_id, read_at, created_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Demo seeds
INSERT INTO dashboard_kpi_definitions (tenant_id, kpi_key, display_name, formula_text, owner_module, default_window, unit, is_active)
SELECT t.id, 'sales.won_amount', 'Monto ganado', 'SUM(opportunities.amount WHERE status=won AND updated_at in range)', 'pipeline', '30d', 'currency', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'sales.open_amount', 'Pipeline abierto', 'SUM(opportunities.amount WHERE status=open)', 'pipeline', 'realtime', 'currency', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'activities.overdue', 'Actividades vencidas', 'COUNT(sales_activities WHERE status in planned,in_progress AND due_at < NOW())', 'activities', 'realtime', 'count', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'ops.critical_alerts', 'Alertas criticas abiertas', 'COUNT(communication_operational_alerts WHERE severity=critical AND status=open)', 'communications', 'realtime', 'count', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE display_name = VALUES(display_name), formula_text = VALUES(formula_text), is_active = VALUES(is_active);

INSERT INTO dashboard_widget_preferences (tenant_id, user_id, widget_key, is_visible, sort_order)
SELECT u.tenant_id, u.id, 'sales_kpis', 1, 1 FROM users u INNER JOIN tenants t ON t.id = u.tenant_id WHERE t.slug = 'demo'
UNION ALL
SELECT u.tenant_id, u.id, 'funnel_chart', 1, 2 FROM users u INNER JOIN tenants t ON t.id = u.tenant_id WHERE t.slug = 'demo'
UNION ALL
SELECT u.tenant_id, u.id, 'trend_chart', 1, 3 FROM users u INNER JOIN tenants t ON t.id = u.tenant_id WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_visible = VALUES(is_visible), sort_order = VALUES(sort_order);
