-- ============================================================================
-- LIMPIOO — Esquema de base de datos maestra
-- ============================================================================
-- Generado: 2026-03-01
--
-- Este archivo contiene el esquema de la BD maestra (limpioo).
-- Es compartida por toda la plataforma y gestiona usuarios del panel,
-- mapeo UUID→BD, y sesiones.
--
-- Uso:
--   mysql -u root -p < database.sql
-- ============================================================================

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

USE `limpioo`;

-- ----------------------------------------------------------------------------
-- Tabla: users
-- Usuarios del panel de administración (propietarios de lavaderos).
-- NO confundir con la tabla `users` de tenant (clientes finales).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email`          VARCHAR(255) NOT NULL UNIQUE,
    `password_hash`  VARCHAR(255) NOT NULL,
    `email_verified` TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Tabla: users_databases
-- Relación usuario del panel → BD tenant asignada.
-- FK: users_databases.user_id → users.id (CASCADE)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users_databases` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`          INT UNSIGNED   NOT NULL,
    `db_name`          VARCHAR(100)   NOT NULL UNIQUE,
    `db_username`      VARCHAR(100)   NOT NULL UNIQUE,
    `db_password_enc`  TEXT           NOT NULL,
    `iv`               VARBINARY(32)  NOT NULL,
    `tag`              VARBINARY(16)  NOT NULL,
    `api_key`          VARCHAR(255) DEFAULT 'afaec328c001bf09e9cad29057057ff2',
    `enabled`          TINYINT(1)     NOT NULL DEFAULT 1,
    `created_at`       TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_ud_user`
      FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Tabla: sessions
-- Almacenamiento de sesiones PHP en BD (en lugar de archivos).
-- Ventajas: No depende del garbage collector, persiste tras reinicios.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
    `id`             VARCHAR(128) NOT NULL PRIMARY KEY COMMENT 'ID de sesión PHP',
    `user_id`        INT UNSIGNED DEFAULT NULL COMMENT 'ID del usuario (si está logueado)',
    `data`           TEXT NOT NULL COMMENT 'Datos serializados de la sesión',
    `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
    `last_activity`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actividad',
    `expires_at`     DATETIME NOT NULL COMMENT 'Fecha de expiración',
    `user_agent`     VARCHAR(255) DEFAULT NULL COMMENT 'Navegador/dispositivo',
    `ip_address`     VARCHAR(45) DEFAULT NULL COMMENT 'Dirección IP',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_expires` (`expires_at`),
    INDEX `idx_last_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Sesiones de usuario almacenadas en BD';

-- ----------------------------------------------------------------------------
-- Tabla: fcm_tokens
-- Tokens FCM de clientes finales (cross-tenant).
-- Permite al administrador de la plataforma enviar notificaciones globales
-- (ej: nueva versión, avisos de mantenimiento) a todos los usuarios.
-- El mismo token también se almacena en la BD de tenant (application/v2.0/database.sql)
-- para que cada lavadero pueda enviar notificaciones a sus propios clientes.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fcm_tokens` (
    `id`         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `uuid`       VARCHAR(64)   NOT NULL COMMENT 'UUID del dispositivo (sesión)',
    `email`      VARCHAR(255)  NOT NULL COMMENT 'Email del usuario registrado',
    `token`      VARCHAR(500)  NOT NULL COMMENT 'Token FCM del cliente',
    `updated_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_uuid` (`uuid`),
    KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Tokens FCM cross-tenant para notificaciones globales de plataforma';

-- ----------------------------------------------------------------------------
-- Tabla: user_uuid_databases
-- Mapeo UUID de cliente final → BD tenant donde tiene compras.
-- Permite a un usuario acceder a sus compras de todos los sitios
-- sin necesidad de escanear un QR. Estrategia híbrida:
--   - Consulta rápida O(1) por UUID
--   - Fallback auto-descubrimiento si la tabla está vacía
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_uuid_databases` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `uuid`       VARCHAR(36) NOT NULL,
    `db_name`    VARCHAR(100) NOT NULL,
    `first_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `last_used`  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `idx_uuid_db` (`uuid`, `db_name`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_last_used` (`last_used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
