-- 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,
    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;

-- Í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);
