-- ============================================================================
-- LIMPIOO — Esquema de base de datos de tenant (aplicación)
-- ============================================================================
-- Generado: 2026-03-01
--
-- Este archivo contiene el esquema de la BD por tenant (limpioo_userdb_N).
-- Cada lavadero tiene su propia BD con esta estructura.
--
-- Uso:
--   mysql -u root -p limpioo_userdb_N < database.sql
-- ============================================================================


-- ----------------------------------------------------------------------------
-- Tabla: users
-- Usuarios finales (clientes del lavadero). Registrados vía login.php.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`        INT(11)      NOT NULL AUTO_INCREMENT,
    `email`     TEXT         NOT NULL,
    `uuid`      VARCHAR(36)  NOT NULL,
    `confirmed` INT(11)      NOT NULL DEFAULT 0,
    `password`  TEXT         NOT NULL,
    `WHO`       TEXT         NOT NULL,
    `WHERE`     TEXT         NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `idx_uuid` (`uuid`),
    UNIQUE KEY `email` (`email`(100)) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: codes
-- Códigos de verificación de email (4 dígitos).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `codes` (
    `UUID` VARCHAR(255) NOT NULL,
    `CODE` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`UUID`, `CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: globals
-- Variables globales de configuración del tenant (key-value store).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `globals` (
    `var`       TINYTEXT NOT NULL,
    `val`       TEXT     NOT NULL,
    `timestamp` INT(11)  NOT NULL DEFAULT 0,
    UNIQUE KEY `var` (`var`(100)) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: log
-- Registro de transacciones de pago y uso de servicios.
-- Cada fila = 1 operación (pago, uso de suscripción, etc.).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `log` (
    `Id`           INT(11)    NOT NULL AUTO_INCREMENT,
    `UUID`         TEXT       NOT NULL,
    `P_DATE`       TEXT       NOT NULL,
    `P_ORDER`      TINYTEXT   NOT NULL,
    `P_RESPONSE`   TINYTEXT   NOT NULL,
    `P_AMOUNT`     INT(11)    NOT NULL DEFAULT 0,
    `P_DEBIT`      INT(11)    DEFAULT 0,
    `P_DATA`       TEXT       NOT NULL,
    `P_URL`        TEXT       NOT NULL,
    `USED`         INT(11)    DEFAULT 0,
    `U_DATE`       TEXT       DEFAULT NULL,
    `R_DATA`       TEXT       DEFAULT NULL,
    `R_DATE`       TEXT       DEFAULT NULL,
    `HUB`          TEXT       NOT NULL,
    `WHO`          TEXT       NOT NULL,
    `WHERE`        TEXT       NOT NULL,
    `THING`        TEXT       NOT NULL,
    `NAME`         TEXT       NOT NULL,
    `DEVICE`       TEXT       NOT NULL,
    `G_CURRENCY`   TEXT       DEFAULT NULL,
    `G_POINTS`     TEXT       DEFAULT NULL,
    `G_AMOUNT`     TEXT       DEFAULT NULL,
    `SUBSCRIPT_ID` TEXT       DEFAULT NULL,
    `SUBSCRIPTION` TINYINT(1) NOT NULL DEFAULT 0,
    `SUBSCRI_WASH` TINYINT(1) NOT NULL DEFAULT 0,
    `LICENSE_PLATE` VARCHAR(20) DEFAULT NULL COMMENT 'Matrícula del vehículo que realizó el lavado (LPR)',
    `QUANTITY`     INT(11)    NOT NULL DEFAULT 1 COMMENT 'Cantidad de unidades de la operación legacy',
    PRIMARY KEY (`Id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: rewards
-- Saldo de recompensas del usuario por moneda (LavaCoin, puntos, etc.).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `rewards` (
    `UUID`     TEXT  NOT NULL,
    `CURRENCY` TEXT  NOT NULL,
    `TOTAL`    FLOAT NOT NULL DEFAULT 0,
    UNIQUE KEY `INDEX` (`UUID`(100), `CURRENCY`(100)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: tokenization
-- Tokens de pago recurrente (Redsys COF — Credential On File).
-- Un token + COF_ID por usuario/comercio para cobros MIT.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tokenization` (
    `ID`       INT(11) NOT NULL AUTO_INCREMENT,
    `UUID`     TEXT    NOT NULL,
    `WHO`      TEXT    NOT NULL,
    `WHERE`    TEXT    NOT NULL,
    `WIU`      TEXT    NOT NULL COMMENT 'MD5(UUID.HUB.WHERE) — clave de unicidad',
    `PROVIDER` TEXT    NOT NULL,
    `TOKEN`    TEXT    NOT NULL,
    `COFID`    TEXT    DEFAULT NULL COMMENT 'Usado junto a TOKEN para cobros recursivos',
    PRIMARY KEY (`ID`),
    UNIQUE KEY `WIU` (`WIU`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: subscriptions
-- Suscripciones activas de usuarios a productos/servicios.
-- Una fila por suscripción de negocio. Cuando existe subscription_id,
-- es la identidad estable usada por tickets, cancelación y cobros.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id`                 INT(11)      NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la suscripción',
    `uuid`               VARCHAR(64)  NOT NULL COMMENT 'UUID del usuario propietario',
    `item_id`            INT(11)      NOT NULL COMMENT 'ID del producto/servicio (campo id en items)',
    `item_title`         VARCHAR(128) NOT NULL COMMENT 'Nombre del producto/servicio',
    `subscription_id`    VARCHAR(64)  DEFAULT NULL COMMENT 'Identificador de negocio estable de la suscripción',
    `subscription_name`  VARCHAR(64)  NOT NULL COMMENT 'Nombre de la suscripción',
    `subscription_text`  VARCHAR(255) NOT NULL COMMENT 'Descripción de la suscripción',
    `subscription_price` INT(11)      NOT NULL COMMENT 'Precio base en céntimos',
    `extra_member_price` INT(11)      NOT NULL DEFAULT 0 COMMENT 'Precio por miembro familiar extra en céntimos',
    `max_members`        INT(11)      NOT NULL DEFAULT 0 COMMENT 'Número máximo de miembros familiares permitidos',
    `license_plate`      VARCHAR(20)  DEFAULT NULL COMMENT 'Matrícula del vehículo del propietario',
    `start_date`         DATETIME     NOT NULL COMMENT 'Fecha de inicio de la suscripción',
    `end_date`           DATETIME     DEFAULT NULL COMMENT 'Fecha de fin (si aplica)',
    `status`             VARCHAR(20)  NOT NULL DEFAULT 'active' COMMENT 'Estado: new, active, cancelled, expired',
    `last_charge`        DATETIME     DEFAULT NULL COMMENT 'Fecha del último cobro exitoso',
    `HUB`                TEXT         DEFAULT NULL,
    `WHO`                TEXT         DEFAULT NULL,
    `WHERE`              TEXT         DEFAULT NULL,
    `THING`              TEXT         DEFAULT NULL,
    `device_key`         VARCHAR(64)  DEFAULT NULL,
    `payment_gateway`    TEXT         DEFAULT NULL COMMENT 'URL base de la pasarela de pago (de JSONFILE.payment.gateway)',
    `failed_attempts`    INT(11)      DEFAULT 0 COMMENT 'Intentos de cobro fallidos consecutivos',
    `cancelled_at`       DATETIME     DEFAULT NULL COMMENT 'Fecha de cancelación',
    `lpr_opted_out`      TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'El titular rechazó registrar matrícula LPR: lavado gratis sin cámara hasta fin de período, sin renovación',
    `created_at`         DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `updated_at`         DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_uuid_subscription_id` (`uuid`, `subscription_id`),
        KEY `idx_uuid_item_plate_thing` (`uuid`, `item_id`, `license_plate`, `THING`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  COMMENT='Suscripciones activas de los usuarios a productos/servicios y vehículos';

-- ----------------------------------------------------------------------------
-- Tabla: subscription_members
-- Miembros familiares de una suscripción (multi-matrícula).
-- Flujo: invitación → aceptación → activo → gracia → eliminado.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `subscription_members` (
    `id`              INT(11)      NOT NULL AUTO_INCREMENT,
    `subscription_id` INT(11)      NOT NULL COMMENT 'FK a subscriptions.id',
    `email`           VARCHAR(255) NOT NULL COMMENT 'Email del miembro invitado',
    `license_plate`   VARCHAR(20)  NOT NULL COMMENT 'Matrícula del vehículo del miembro',
    `member_uuid`     VARCHAR(64)  DEFAULT NULL COMMENT 'UUID del miembro (vinculado tras primer login)',
    `status`          ENUM('pending','active','grace','removed') NOT NULL DEFAULT 'pending'
                      COMMENT 'pending=invitado, active=aceptado, grace=en período de gracia, removed=eliminado',
    `invited_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de envío de invitación',
    `joined_at`       DATETIME     DEFAULT NULL COMMENT 'Fecha de aceptación de la invitación',
    `removed_at`      DATETIME     DEFAULT NULL COMMENT 'Fecha de eliminación/abandono',
    `grace_until`     DATETIME     DEFAULT NULL COMMENT 'Fin del período de gracia (1 mes tras eliminación)',
    `created_at`      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_sub_plate` (`subscription_id`, `license_plate`),
    KEY `idx_plate` (`license_plate`),
    KEY `idx_status` (`status`),
    KEY `idx_member_uuid` (`member_uuid`),
    CONSTRAINT `fk_sub_member` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: qr
-- Códigos QR generados para pagos en efectivo o promociones.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `qr` (
    `id`             INT(11)      NOT NULL AUTO_INCREMENT,
    `code`           VARCHAR(255) NOT NULL,
    `uuid`           VARCHAR(255) DEFAULT NULL,
    `customer_email` VARCHAR(255) DEFAULT NULL COMMENT 'Email del cliente',
    `location`       VARCHAR(255) DEFAULT NULL COMMENT 'Ubicación donde se adquirió/generó el QR',
    `device_name`    VARCHAR(255) NOT NULL COMMENT 'Nombre del dispositivo',
    `device_token`   VARCHAR(255) NOT NULL COMMENT 'Token del dispositivo',
    `payment_method` VARCHAR(50)  NOT NULL DEFAULT 'cash' COMMENT 'Método de pago',
    `total_net`      INT(11)      NOT NULL DEFAULT 0 COMMENT 'Total neto sin impuestos en céntimos',
    `total_taxes`    INT(11)      NOT NULL DEFAULT 0 COMMENT 'Total de impuestos en céntimos',
    `total_amount`   INT(11)      NOT NULL DEFAULT 0 COMMENT 'Total final en céntimos',
    `print_option`   TINYINT(1)   DEFAULT 0 COMMENT 'Opción de imprimir',
    `email_option`   TINYINT(1)   DEFAULT 0 COMMENT 'Opción de enviar email',
    `status`         VARCHAR(20)  DEFAULT 'pending' COMMENT 'Estado del QR',
    `valid_from`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha desde cuando es válido',
    `valid_until`    DATETIME     DEFAULT NULL COMMENT 'Fecha hasta cuando es válido (NULL = sin expiración)',
    `used_at`        DATETIME     DEFAULT NULL COMMENT 'Fecha cuando se usó',
    `created_at`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `device_name` (`device_name`),
    KEY `location` (`location`),
    KEY `status` (`status`),
    KEY `valid_period` (`valid_from`, `valid_until`),
    KEY `used_at` (`used_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: qr_items
-- Ítems individuales dentro de un código QR (1 QR → N ítems).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `qr_items` (
    `id`                 INT(11)      NOT NULL AUTO_INCREMENT,
    `qr_id`              INT(11)      NOT NULL,
    `unique_id`          VARCHAR(100) NOT NULL COMMENT 'ID único del item',
    `product_name`       VARCHAR(255) NOT NULL COMMENT 'Nombre del producto',
    `program_id`         INT(11)      NOT NULL COMMENT 'ID del programa',
    `quantity`           INT(11)      NOT NULL DEFAULT 1 COMMENT 'Cantidad total comprada',
    `quantity_used`      INT(11)      NOT NULL DEFAULT 0 COMMENT 'Cantidad ya utilizada',
    `quantity_refunded`  INT(11)      NOT NULL DEFAULT 0 COMMENT 'Cantidad devuelta',
    `quantity_remaining` INT(11)      GENERATED ALWAYS AS (`quantity` - `quantity_used` - `quantity_refunded`) STORED
                         COMMENT 'Cantidad disponible (calculada)',
    `unit_price`         INT(11)      NOT NULL COMMENT 'Precio unitario en céntimos',
    `total`              INT(11)      NOT NULL COMMENT 'Total del item en céntimos',
    `has_rewards`        TINYINT(1)   DEFAULT 0 COMMENT 'Tiene recompensas',
    `first_used_at`      DATETIME     DEFAULT NULL COMMENT 'Fecha del primer uso',
    `last_used_at`       DATETIME     DEFAULT NULL COMMENT 'Fecha del último uso',
    `device_name`        VARCHAR(255) NOT NULL COMMENT 'Nombre del dispositivo',
    `device_token`       VARCHAR(255) NOT NULL COMMENT 'Token del dispositivo',
    PRIMARY KEY (`id`),
    KEY `qr_id` (`qr_id`),
    KEY `unique_id` (`unique_id`),
    KEY `program_id` (`program_id`),
    CONSTRAINT `fk_qr_items_qr` FOREIGN KEY (`qr_id`) REFERENCES `qr` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: qr_usage_log
-- Registro de cada uso individual de un QR item.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `qr_usage_log` (
    `id`              INT(11)      NOT NULL AUTO_INCREMENT,
    `qr_item_id`      INT(11)      NOT NULL COMMENT 'ID del item usado',
    `qr_id`           INT(11)      NOT NULL COMMENT 'ID del QR (para consultas rápidas)',
    `qr_code`         VARCHAR(255) NOT NULL COMMENT 'Código QR usado',
    `quantity_used`    INT(11)      NOT NULL DEFAULT 1 COMMENT 'Cantidad usada en esta ocasión',
    `used_at`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha y hora del uso',
    `used_by_device`   VARCHAR(255) DEFAULT NULL COMMENT 'Dispositivo que registró el uso',
    `location`         VARCHAR(255) DEFAULT NULL COMMENT 'Ubicación donde se usó',
    `notes`            TEXT         DEFAULT NULL COMMENT 'Notas adicionales del uso',
    `user_uuid`        VARCHAR(255) DEFAULT NULL COMMENT 'Usuario que lo usó',
    PRIMARY KEY (`id`),
    KEY `qr_item_id` (`qr_item_id`),
    KEY `qr_id` (`qr_id`),
    KEY `qr_code` (`qr_code`),
    KEY `used_at` (`used_at`),
    KEY `user_uuid` (`user_uuid`),
    CONSTRAINT `fk_usage_log_qr` FOREIGN KEY (`qr_id`) REFERENCES `qr` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_usage_log_qr_item` FOREIGN KEY (`qr_item_id`) REFERENCES `qr_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: uses
-- Contador de usos de la API por dispositivo y fecha.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `uses` (
    `ID`     INT(11) NOT NULL AUTO_INCREMENT,
    `APIKEY` TEXT    NOT NULL,
    `NAME`   TEXT    NOT NULL,
    `DATE`   TEXT    NOT NULL,
    `SCANED` INT(11) NOT NULL DEFAULT 0,
    UNIQUE KEY `ID` (`ID`) USING BTREE,
    UNIQUE KEY `INDEX` (`NAME`(100), `DATE`(20)) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------------------------------------------------------
-- Tabla: fcm_tokens
-- Almacena el token FCM (Firebase Cloud Messaging) por dispositivo (UUID).
-- Usado por includes/api/fcm-token.php para notificaciones push.
-- ----------------------------------------------------------------------------
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_general_ci;
