-- Tablas de configuración del dashboard (BD canal13 o la que use la conexión externa)

CREATE TABLE IF NOT EXISTS dashboard_menu (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(120) NOT NULL,
    parent_id INT UNSIGNED DEFAULT NULL,
    icono VARCHAR(60) DEFAULT 'chart-bar',
    slug VARCHAR(80) NOT NULL UNIQUE,
    orden INT UNSIGNED DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES dashboard_menu(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id INT UNSIGNED NOT NULL,
    titulo VARCHAR(120) NOT NULL,
    slug VARCHAR(80) NOT NULL,
    layout VARCHAR(20) DEFAULT '1col',
    icono VARCHAR(60) DEFAULT 'chart-line',
    date_filter_enabled TINYINT(1) DEFAULT 0,
    date_filter_from DATE DEFAULT NULL,
    date_filter_to DATE DEFAULT NULL,
    date_filter_quick VARCHAR(20) DEFAULT '',
    orden INT UNSIGNED DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_page_slug (menu_id, slug),
    FOREIGN KEY (menu_id) REFERENCES dashboard_menu(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_charts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(180) NOT NULL,
    sql_query TEXT NOT NULL,
    x_field VARCHAR(80) NOT NULL,
    y_field VARCHAR(80) NOT NULL,
    series_field VARCHAR(80) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_page_charts (
    page_id INT UNSIGNED NOT NULL,
    chart_id INT UNSIGNED NOT NULL,
    titulo VARCHAR(180) NOT NULL,
    tipo VARCHAR(30) DEFAULT 'line',
    chart_lib VARCHAR(20) DEFAULT 'highcharts',
    series_colors TEXT,
    color VARCHAR(30) DEFAULT '#3b82f6',
    value_prefix VARCHAR(20) DEFAULT '',
    target_field VARCHAR(80) DEFAULT '',
    compare_field VARCHAR(80) DEFAULT '',
    fill_color VARCHAR(30) DEFAULT '',
    line_width INT UNSIGNED DEFAULT 2,
    line_dash VARCHAR(40) DEFAULT '',
    is_stacked TINYINT(1) DEFAULT 0,
    y_min DOUBLE DEFAULT NULL,
    y_max DOUBLE DEFAULT NULL,
    show_legend TINYINT(1) DEFAULT 1,
    show_title TINYINT(1) DEFAULT 0,
    title_size INT UNSIGNED DEFAULT 14,
    chart_height SMALLINT UNSIGNED DEFAULT 360,
    table_row_limit SMALLINT UNSIGNED DEFAULT NULL,
    table_columns_map TEXT,
    table_show_total TINYINT(1) DEFAULT 0,
    style_config_json LONGTEXT NULL,
    chart_span TINYINT UNSIGNED DEFAULT 3,
    orden INT UNSIGNED DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (page_id, chart_id),
    FOREIGN KEY (page_id) REFERENCES dashboard_pages(id) ON DELETE CASCADE,
    FOREIGN KEY (chart_id) REFERENCES dashboard_charts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_tablas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    db_name VARCHAR(120) NOT NULL,
    object_name VARCHAR(160) NOT NULL,
    object_type VARCHAR(20) DEFAULT 'view',
    description VARCHAR(255) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_page_blocks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id INT UNSIGNED NOT NULL,
    block_type VARCHAR(30) NOT NULL,
    title VARCHAR(200) DEFAULT '',
    text TEXT,
    image_url VARCHAR(500) DEFAULT '',
    link_url VARCHAR(500) DEFAULT '',
    block_span TINYINT UNSIGNED DEFAULT 3,
    orden INT UNSIGNED DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (page_id) REFERENCES dashboard_pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_page_items (
    page_id INT UNSIGNED NOT NULL,
    item_type VARCHAR(20) NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    orden INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (page_id, item_type, item_id),
    FOREIGN KEY (page_id) REFERENCES dashboard_pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_page_resources (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id INT UNSIGNED NOT NULL,
    resource_type VARCHAR(30) NOT NULL,
    title VARCHAR(200) DEFAULT '',
    body LONGTEXT NULL,
    media_url VARCHAR(500) DEFAULT '',
    link_url VARCHAR(500) DEFAULT '',
    col_span TINYINT UNSIGNED DEFAULT 12,
    sort_order INT UNSIGNED DEFAULT 0,
    active TINYINT(1) DEFAULT 1,
    chart_type VARCHAR(30) DEFAULT NULL,
    chart_library VARCHAR(20) DEFAULT NULL,
    sql_query LONGTEXT NULL,
    x_field VARCHAR(80) DEFAULT '',
    y_field VARCHAR(80) DEFAULT '',
    series_field VARCHAR(80) DEFAULT '',
    value_prefix VARCHAR(20) DEFAULT '',
    target_field VARCHAR(80) DEFAULT '',
    compare_field VARCHAR(80) DEFAULT '',
    chart_height SMALLINT UNSIGNED DEFAULT 360,
    data_config_json LONGTEXT NULL,
    style_config_json LONGTEXT NULL,
    legacy_item_type VARCHAR(20) DEFAULT NULL,
    legacy_item_id INT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_page_resource_legacy (page_id, legacy_item_type, legacy_item_id),
    KEY idx_page_resources_page_sort (page_id, active, sort_order, id),
    FOREIGN KEY (page_id) REFERENCES dashboard_pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Índices para listados
CREATE INDEX idx_menu_activo_orden ON dashboard_menu(activo, orden);
CREATE INDEX idx_menu_parent ON dashboard_menu(parent_id);
CREATE INDEX idx_pages_menu ON dashboard_pages(menu_id);
CREATE INDEX idx_page_charts_page ON dashboard_page_charts(page_id, orden);
CREATE INDEX idx_page_charts_chart ON dashboard_page_charts(chart_id);
CREATE INDEX idx_dashboard_tablas_name ON dashboard_tablas(name);
CREATE INDEX idx_page_blocks_page ON dashboard_page_blocks(page_id, activo, orden);
CREATE INDEX idx_page_items_page ON dashboard_page_items(page_id, orden);
CREATE INDEX idx_page_resources_page ON dashboard_page_resources(page_id, active, sort_order);

CREATE TABLE IF NOT EXISTS dashboard_alerting_settings (
    key_name VARCHAR(120) PRIMARY KEY,
    value_text LONGTEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_alerting_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    description VARCHAR(500) DEFAULT '',
    metric_key VARCHAR(120) DEFAULT '',
    sql_query LONGTEXT NOT NULL,
    value_field VARCHAR(80) DEFAULT 'value',
    schedule_type VARCHAR(20) DEFAULT 'interval',
    schedule_cron VARCHAR(120) DEFAULT '',
    schedule_timezone VARCHAR(80) DEFAULT 'America/Santiago',
    comparison_op VARCHAR(10) DEFAULT 'gte',
    threshold_value DECIMAL(20,4) NOT NULL DEFAULT 0,
    cooldown_minutes INT UNSIGNED DEFAULT 60,
    slack_channel VARCHAR(120) DEFAULT '',
    notify_slack TINYINT(1) DEFAULT 1,
    notify_pushover TINYINT(1) DEFAULT 0,
    pushover_user_key VARCHAR(120) DEFAULT '',
    pushover_device VARCHAR(120) DEFAULT '',
    pushover_priority TINYINT DEFAULT NULL,
    message_template LONGTEXT NULL,
    message_template_pushover LONGTEXT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_metric_value LONGTEXT NULL,
    last_evaluated_at DATETIME NULL,
    last_triggered_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_alert_rules_active (is_active, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_alerting_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rule_id INT UNSIGNED NOT NULL,
    event_key VARCHAR(190) NOT NULL,
    metric_value DECIMAL(20,4) NULL,
    threshold_value DECIMAL(20,4) NULL,
    delivery_status VARCHAR(20) DEFAULT 'pending',
    payload_json LONGTEXT NULL,
    response_json LONGTEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_alert_event (rule_id, event_key),
    KEY idx_alert_events_rule_created (rule_id, created_at),
    CONSTRAINT fk_alert_events_rule FOREIGN KEY (rule_id) REFERENCES dashboard_alerting_rules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_alerting_bot_intents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intent_key VARCHAR(120) NOT NULL,
    label VARCHAR(180) NOT NULL,
    description VARCHAR(500) DEFAULT '',
    question_examples TEXT NULL,
    sql_query LONGTEXT NULL,
    response_template LONGTEXT NULL,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_alerting_intent_key (intent_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_user_groups (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    description VARCHAR(500) DEFAULT '',
    access_json LONGTEXT NULL,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_dashboard_user_groups_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dashboard_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id INT UNSIGNED NULL,
    full_name VARCHAR(180) NOT NULL,
    username VARCHAR(120) NOT NULL,
    email VARCHAR(180) DEFAULT '',
    password_hash VARCHAR(255) DEFAULT '',
    access_json LONGTEXT NULL,
    dashboard_visibility_json LONGTEXT NULL,
    is_super_user TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT UNSIGNED DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_dashboard_users_username (username),
    UNIQUE KEY uk_dashboard_users_full_name (full_name),
    KEY idx_dashboard_users_group (group_id, is_active, sort_order, id),
    CONSTRAINT fk_dashboard_users_group FOREIGN KEY (group_id) REFERENCES dashboard_user_groups(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
