-- ============================================================
--  Qullqi Wasi — Base de datos
--  Sistema de Gestión de Colecciones de Estampas
--  MySQL 8.0+ | utf8mb4_unicode_ci
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `qullqi_wasi_db`
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `qullqi_wasi_db`;

-- ── roles ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `roles` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre`      VARCHAR(50)  NOT NULL,
    `descripcion` VARCHAR(200) DEFAULT NULL,
    `creado_en`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_roles_nombre` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── usuarios ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `usuarios` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `rol_id`           INT UNSIGNED  NOT NULL DEFAULT 3,
    `nombre`           VARCHAR(100)  NOT NULL,
    `nombre_usuario`   VARCHAR(50)   NOT NULL,
    `correo`           VARCHAR(150)  NOT NULL,
    `contrasena_hash`  VARCHAR(255)  NOT NULL,
    `estado`           ENUM('activo','inactivo','bloqueado') NOT NULL DEFAULT 'activo',
    `intentos_login`   TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `bloqueado_hasta`  TIMESTAMP NULL DEFAULT NULL,
    `ultimo_acceso`    TIMESTAMP NULL DEFAULT NULL,
    `creado_en`        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_usuarios_correo` (`correo`),
    UNIQUE KEY `uk_usuarios_nombre_usuario` (`nombre_usuario`),
    KEY `fk_usuarios_rol_idx` (`rol_id`),
    CONSTRAINT `fk_usuarios_rol`
        FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── colecciones ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `colecciones` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `codigo`         VARCHAR(20)   NOT NULL,
    `nombre`         VARCHAR(150)  NOT NULL,
    `descripcion`    TEXT          DEFAULT NULL,
    `fecha_inicio`   DATE          DEFAULT NULL,
    `fecha_fin`      DATE          DEFAULT NULL,
    `imagen_portada` VARCHAR(255)  DEFAULT NULL,
    `estado`         ENUM('borrador','publicada','archivada') NOT NULL DEFAULT 'borrador',
    `total_estampas` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `creado_por`     INT UNSIGNED  NOT NULL,
    `creado_en`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_colecciones_codigo` (`codigo`),
    KEY `fk_colecciones_usuario_idx` (`creado_por`),
    CONSTRAINT `fk_colecciones_usuario`
        FOREIGN KEY (`creado_por`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── secciones ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `secciones` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `coleccion_id` INT UNSIGNED  NOT NULL,
    `nombre`       VARCHAR(100)  NOT NULL,
    `descripcion`  VARCHAR(300)  DEFAULT NULL,
    `orden`        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `creado_en`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_secciones_coleccion_idx` (`coleccion_id`),
    CONSTRAINT `fk_secciones_coleccion`
        FOREIGN KEY (`coleccion_id`) REFERENCES `colecciones` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── estampas ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `estampas` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `coleccion_id`  INT UNSIGNED NOT NULL,
    `seccion_id`    INT UNSIGNED DEFAULT NULL,
    `numero`        VARCHAR(20)  NOT NULL,
    `codigo`        VARCHAR(30)  NOT NULL,
    `nombre`        VARCHAR(200) NOT NULL,
    `descripcion`   TEXT         DEFAULT NULL,
    `rareza`        ENUM('comun','poco_comun','rara','epica','legendaria') NOT NULL DEFAULT 'comun',
    `imagen`        VARCHAR(255) DEFAULT NULL,
    `estado`        ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
    `creado_en`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en`TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_estampas_coleccion_codigo` (`coleccion_id`, `codigo`),
    KEY `fk_estampas_seccion_idx` (`seccion_id`),
    CONSTRAINT `fk_estampas_coleccion`
        FOREIGN KEY (`coleccion_id`) REFERENCES `colecciones` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_estampas_seccion`
        FOREIGN KEY (`seccion_id`) REFERENCES `secciones` (`id`)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── album_usuario ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `album_usuario` (
    `id`               INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `usuario_id`       INT UNSIGNED   NOT NULL,
    `coleccion_id`     INT UNSIGNED   NOT NULL,
    `porcentaje`       DECIMAL(5,2)   NOT NULL DEFAULT 0.00,
    `total_obtenidas`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `total_faltantes`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `total_duplicados` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `completado`       TINYINT(1)     NOT NULL DEFAULT 0,
    `creado_en`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en`   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_album_usuario_coleccion` (`usuario_id`, `coleccion_id`),
    KEY `fk_album_coleccion_idx` (`coleccion_id`),
    CONSTRAINT `fk_album_usuario`
        FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_album_coleccion`
        FOREIGN KEY (`coleccion_id`) REFERENCES `colecciones` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── inventario_estampas ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `inventario_estampas` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `album_id`      INT UNSIGNED NOT NULL,
    `estampa_id`    INT UNSIGNED NOT NULL,
    `cantidad`      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `duplicados`    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `creado_en`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en`TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_inventario_album_estampa` (`album_id`, `estampa_id`),
    KEY `fk_inventario_estampa_idx` (`estampa_id`),
    CONSTRAINT `fk_inventario_album`
        FOREIGN KEY (`album_id`) REFERENCES `album_usuario` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_inventario_estampa`
        FOREIGN KEY (`estampa_id`) REFERENCES `estampas` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── intercambios ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `intercambios` (
    `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `usuario_origen_id`   INT UNSIGNED NOT NULL,
    `usuario_destino_id`  INT UNSIGNED DEFAULT NULL,
    `estampa_ofrecida_id` INT UNSIGNED NOT NULL,
    `estampa_buscada_id`  INT UNSIGNED DEFAULT NULL,
    `estado`              ENUM('publicada','en_negociacion','completada','cancelada') NOT NULL DEFAULT 'publicada',
    `notas`               TEXT         DEFAULT NULL,
    `creado_en`           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `actualizado_en`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_intercambio_origen_idx`   (`usuario_origen_id`),
    KEY `fk_intercambio_destino_idx`  (`usuario_destino_id`),
    KEY `fk_intercambio_ofrecida_idx` (`estampa_ofrecida_id`),
    KEY `fk_intercambio_buscada_idx`  (`estampa_buscada_id`),
    CONSTRAINT `fk_intercambio_origen`
        FOREIGN KEY (`usuario_origen_id`)   REFERENCES `usuarios` (`id`),
    CONSTRAINT `fk_intercambio_destino`
        FOREIGN KEY (`usuario_destino_id`)  REFERENCES `usuarios` (`id`),
    CONSTRAINT `fk_intercambio_ofrecida`
        FOREIGN KEY (`estampa_ofrecida_id`) REFERENCES `estampas` (`id`),
    CONSTRAINT `fk_intercambio_buscada`
        FOREIGN KEY (`estampa_buscada_id`)  REFERENCES `estampas` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── actividad_diaria ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `actividad_diaria` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `usuario_id`  INT UNSIGNED NOT NULL,
    `fecha`       DATE         NOT NULL,
    `cantidad`    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_actividad_usuario_fecha` (`usuario_id`, `fecha`),
    KEY `fk_actividad_usuario_idx` (`usuario_id`),
    CONSTRAINT `fk_actividad_usuario`
        FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── auditoria ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `auditoria` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `usuario_id`       INT UNSIGNED    DEFAULT NULL,
    `accion`           ENUM('crear','modificar','eliminar','acceder','login','logout') NOT NULL,
    `tabla`            VARCHAR(60)     DEFAULT NULL,
    `registro_id`      INT UNSIGNED    DEFAULT NULL,
    `datos_anteriores` JSON            DEFAULT NULL,
    `datos_nuevos`     JSON            DEFAULT NULL,
    `ip`               VARCHAR(45)     DEFAULT NULL,
    `agente`           VARCHAR(300)    DEFAULT NULL,
    `creado_en`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_auditoria_usuario` (`usuario_id`),
    KEY `idx_auditoria_fecha`   (`creado_en`),
    KEY `idx_auditoria_tabla`   (`tabla`, `registro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  DATOS DE PRUEBA
-- ============================================================

-- Roles (IDs fijos)
INSERT INTO roles (id, nombre, descripcion) VALUES
(1, 'administrador', 'Acceso total al sistema'),
(2, 'editor',        'Gestiona colecciones, álbumes y estampas'),
(3, 'coleccionista', 'Gestiona su colección personal')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- Usuarios de prueba (hashes bcrypt cost=10 para velocidad en pruebas)
-- admin      -> Admin2026*
-- coleccionista.demo -> Demo2026*
INSERT INTO usuarios (id, rol_id, nombre, nombre_usuario, correo, contrasena_hash, estado) VALUES
(1, 1, 'Administrador', 'admin', 'admin@qullqiwasi.local',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'activo'),
(2, 3, 'Demo Coleccionista', 'coleccionista.demo', 'demo@qullqiwasi.local',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'activo')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- NOTA: Los hashes anteriores corresponden a 'password' de Laravel (para testing rápido).
-- Para regenerar con las contraseñas reales, ejecutar generate_hashes.php incluido en scripts/

-- Colección de prueba
INSERT INTO colecciones (id, codigo, nombre, descripcion, fecha_inicio, fecha_fin, estado, total_estampas, creado_por) VALUES
(1, 'FIFA2026', 'Mundial FIFA 2026',
 'Colección oficial del Mundial de Fútbol FIFA 2026. Reúne las figuras de todos los equipos participantes.',
 '2026-01-01', '2026-12-31', 'publicada', 7, 1)
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- Secciones
INSERT INTO secciones (id, coleccion_id, nombre, descripcion, orden) VALUES
(1, 1, 'Grupo A', 'Equipos del Grupo A', 1),
(2, 1, 'Grupo B', 'Equipos del Grupo B', 2),
(3, 1, 'Grupo C', 'Equipos del Grupo C', 3),
(4, 1, 'Estadios', 'Estadios sede del torneo', 4),
(5, 1, 'Mascotas', 'Mascotas y personajes oficiales', 5)
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- Estampas
INSERT INTO estampas (id, coleccion_id, seccion_id, numero, codigo, nombre, rareza, estado) VALUES
(1, 1, 1, '1', 'A-001', 'México',          'comun',       'activa'),
(2, 1, 1, '2', 'A-002', 'Corea del Sur',   'comun',       'activa'),
(3, 1, 1, '3', 'A-003', 'Sudáfrica',       'comun',       'activa'),
(4, 1, 2, '4', 'B-001', 'Canadá',          'comun',       'activa'),
(5, 1, 2, '5', 'B-002', 'Qatar',           'comun',       'activa'),
(6, 1, 4, '6', 'EST-001','Estadio Azteca', 'poco_comun',  'activa'),
(7, 1, 5, '7', 'MSC-001','Mascota Oficial','rara',        'activa')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);