-- ============================================================
-- Sistema de Pronósticos de Loterías - Schema MySQL/MariaDB
-- Base de datos: hackruzc_analisis
-- ============================================================

CREATE DATABASE IF NOT EXISTS hackruzc_analisis
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE hackruzc_analisis;

-- ─── loterias ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS loterias (
    id                       TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre                   VARCHAR(20) NOT NULL UNIQUE,
    num_bolillas_principales TINYINT UNSIGNED NOT NULL,
    max_numero               TINYINT UNSIGNED NOT NULL,
    tiene_yapa               BOOLEAN NOT NULL DEFAULT FALSE,
    tiene_adicionales        BOOLEAN NOT NULL DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO loterias
    (nombre, num_bolillas_principales, max_numero, tiene_yapa, tiene_adicionales)
VALUES
    ('tinka',  6, 55, TRUE,  TRUE),
    ('kabala', 6, 42, FALSE, FALSE),
    ('chau',   6, 42, FALSE, FALSE),
    ('diario', 5, 35, FALSE, FALSE);

-- ─── sorteos ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sorteos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loteria_id      TINYINT UNSIGNED NOT NULL,
    fecha           DATE NOT NULL,
    numero_sorteo   SMALLINT UNSIGNED NOT NULL,
    extra_info      VARCHAR(255) NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sorteo (loteria_id, numero_sorteo),
    KEY idx_fecha (fecha),
    FOREIGN KEY (loteria_id) REFERENCES loterias(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── bolillas_principales ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS bolillas_principales (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sorteo_id   INT UNSIGNED NOT NULL,
    orden       TINYINT UNSIGNED NOT NULL,
    valor       TINYINT UNSIGNED NOT NULL,
    UNIQUE KEY uq_bolilla (sorteo_id, orden),
    KEY idx_valor (valor),
    FOREIGN KEY (sorteo_id) REFERENCES sorteos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── bolillas_yapa (solo Tinka) ───────────────────────────────
CREATE TABLE IF NOT EXISTS bolillas_yapa (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sorteo_id   INT UNSIGNED NOT NULL UNIQUE,
    valor       TINYINT UNSIGNED NOT NULL,
    KEY idx_valor (valor),
    FOREIGN KEY (sorteo_id) REFERENCES sorteos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── bolillas_adicionales (solo Tinka) ───────────────────────
CREATE TABLE IF NOT EXISTS bolillas_adicionales (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sorteo_id   INT UNSIGNED NOT NULL,
    orden       TINYINT UNSIGNED NOT NULL,
    valor       TINYINT UNSIGNED NOT NULL,
    UNIQUE KEY uq_adicional (sorteo_id, orden),
    KEY idx_valor (valor),
    FOREIGN KEY (sorteo_id) REFERENCES sorteos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── pronosticos ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pronosticos (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loteria_id       TINYINT UNSIGNED NOT NULL,
    fecha_generado   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metodo           VARCHAR(50) NOT NULL DEFAULT 'combinado',
    numeros_json     LONGTEXT NOT NULL,
    yapa_json        LONGTEXT NULL,
    adicionales_json LONGTEXT NULL,
    hit_rate         DECIMAL(5,4) NULL,
    FOREIGN KEY (loteria_id) REFERENCES loterias(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── metricas ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS metricas (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loteria_id     TINYINT UNSIGNED NOT NULL,
    metodo         VARCHAR(50) NOT NULL,
    sorteos_eval   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    hit_rate       DECIMAL(5,4) NOT NULL DEFAULT 0,
    peso_actual    DECIMAL(5,4) NOT NULL DEFAULT 0.2000,
    actualizado_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_metrica (loteria_id, metodo),
    FOREIGN KEY (loteria_id) REFERENCES loterias(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pesos iniciales (20 % cada método)
INSERT IGNORE INTO metricas (loteria_id, metodo, sorteos_eval, hit_rate, peso_actual)
SELECT l.id, m.metodo, 0, 0, 0.2000
FROM loterias l
CROSS JOIN (
    SELECT 'hot_cold'   AS metodo UNION ALL
    SELECT 'bayesian'            UNION ALL
    SELECT 'neural'              UNION ALL
    SELECT 'montecarlo'          UNION ALL
    SELECT 'quantum'
) m;