-- TutuPlay Asistencia - esquema de ejemplo (MySQL / MariaDB)
--
-- Uso:
--   mysql -u root -p < schema.sql
--
-- Nota: el API de ejemplo tambien trae `sqlite.sql` para probar sin MySQL.
-- Este archivo es el que corresponde a produccion.

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

USE tutuplay_asistencia;

-- ---------------------------------------------------------------------------
-- Sucursales
--
-- `code` es el codigo de 5 caracteres (A-Z0-9) que la aplicacion genera en su
-- primer arranque. Al registrar esa fila aqui, la aplicacion queda vinculada.
-- `totp_secret` es el valor que el administrador teclea en la app para
-- autorizar la captura de una huella nueva (newfingerprint.php unicamente).
-- Marcar entrada/salida (switchstatus.php) NO pide TOTP: lo autoriza la
-- huella verificada en el equipo.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sucursal (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    code            CHAR(5)         NOT NULL,
    nombre          VARCHAR(150)    NOT NULL,
    totp_secret     VARCHAR(64)     NOT NULL,
    activo          TINYINT(1)      NOT NULL DEFAULT 1,
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sucursal_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Usuarios (empleados)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuario (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    sucursal_id     INT UNSIGNED    NOT NULL,
    nombre          VARCHAR(150)    NOT NULL,
    activo          TINYINT(1)      NOT NULL DEFAULT 1,
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_usuario_sucursal (sucursal_id),
    CONSTRAINT fk_usuario_sucursal FOREIGN KEY (sucursal_id)
        REFERENCES sucursal (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Asistencia
--
-- `in` = 0 ausente, 1 presente. Se conserva ese nombre porque es el campo que
-- la aplicacion envia en switchstatus.php y espera en getusers.php.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asistencia (
    usuario_id      INT UNSIGNED    NOT NULL,
    `in`            TINYINT(1)      NOT NULL DEFAULT 0,
    actualizado_en  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (usuario_id),
    CONSTRAINT fk_asistencia_usuario FOREIGN KEY (usuario_id)
        REFERENCES usuario (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Huellas
--
-- `huella` guarda la plantilla del SDK (Fmd) como XML codificado en base64:
-- texto ASCII sin saltos de linea.
--
-- La plantilla NO es un hash estable: cada captura del mismo dedo produce
-- bytes distintos, y la comparacion la hace la aplicacion con el SDK, nunca
-- SQL. Por eso se permiten varias filas por usuario (plantillas alternativas
-- del mismo dedo) y basta con que una coincida.
--
-- MEDIUMTEXT (16 MB) sobra: una plantilla ronda 1-3 KB en base64.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS huella (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    usuario_id      INT UNSIGNED    NOT NULL,
    huella          MEDIUMTEXT      NOT NULL,
    creado_en       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_huella_usuario (usuario_id),
    CONSTRAINT fk_huella_usuario FOREIGN KEY (usuario_id)
        REFERENCES usuario (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Datos de ejemplo
--   codigo de sucursal: DEMO1
--   TOTP de ejemplo:    123456
-- ---------------------------------------------------------------------------
INSERT INTO sucursal (code, nombre, totp_secret)
VALUES ('DEMO1', 'Sucursal de pruebas', '123456')
ON DUPLICATE KEY UPDATE
    nombre = VALUES(nombre),
    totp_secret = VALUES(totp_secret);

INSERT INTO usuario (id, sucursal_id, nombre)
SELECT 1, s.id, 'Ana Lopez' FROM sucursal s WHERE s.code = 'DEMO1'
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

INSERT INTO usuario (id, sucursal_id, nombre)
SELECT 2, s.id, 'Bruno Diaz' FROM sucursal s WHERE s.code = 'DEMO1'
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

INSERT INTO asistencia (usuario_id, `in`) VALUES (1, 0)
ON DUPLICATE KEY UPDATE `in` = VALUES(`in`);

INSERT INTO asistencia (usuario_id, `in`) VALUES (2, 0)
ON DUPLICATE KEY UPDATE `in` = VALUES(`in`);
