-- Revisar y adaptar a la versión confirmada de MySQL/MariaDB.
-- Este archivo no se ejecuta automáticamente.
-- Antes de usarlo, seleccionar explícitamente una base dedicada y vacía.

SET NAMES utf8mb4;

CREATE TABLE roles (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(40) NOT NULL,
    descripcion VARCHAR(160) NULL,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_roles_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (nombre, descripcion, creado_en, actualizado_en) VALUES
    ('administrador', 'Administración global de la plataforma', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
    ('operador', 'Operación autorizada por sucursal', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
    ('consulta', 'Consulta autorizada por sucursal', UTC_TIMESTAMP(), UTC_TIMESTAMP());

CREATE TABLE usuarios (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre_usuario VARCHAR(80) NOT NULL,
    nombre_completo VARCHAR(160) NOT NULL,
    correo VARCHAR(190) NULL,
    password_hash VARCHAR(255) NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_usuarios_nombre_usuario (nombre_usuario),
    UNIQUE KEY uq_usuarios_correo (correo),
    KEY ix_usuarios_activo (activo)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE usuario_roles (
    usuario_id BIGINT UNSIGNED NOT NULL,
    rol_id TINYINT UNSIGNED NOT NULL,
    creado_en DATETIME NOT NULL,
    PRIMARY KEY (usuario_id, rol_id),
    KEY ix_usuario_roles_rol (rol_id),
    CONSTRAINT fk_usuario_roles_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id) ON DELETE CASCADE,
    CONSTRAINT fk_usuario_roles_rol FOREIGN KEY (rol_id) REFERENCES roles (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sucursales (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(140) NOT NULL,
    codigo VARCHAR(40) NOT NULL,
    direccion VARCHAR(255) NULL,
    ciudad VARCHAR(100) NULL,
    activa TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sucursales_codigo (codigo),
    KEY ix_sucursales_activa_nombre (activa, nombre)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dispositivos (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sucursal_id BIGINT UNSIGNED NULL,
    tipo ENUM('camara_ip', 'dvr', 'nvr', 'otro') NOT NULL,
    marca VARCHAR(100) NOT NULL,
    modelo VARCHAR(120) NULL,
    serial VARCHAR(160) NULL,
    direccion_ip VARCHAR(45) NULL,
    puerto_gestion SMALLINT UNSIGNED NULL,
    protocolo_preferido VARCHAR(40) NULL,
    estado_inventario VARCHAR(40) NOT NULL DEFAULT 'pendiente_verificacion',
    notas TEXT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_dispositivos_serial (serial),
    KEY ix_dispositivos_sucursal_activo (sucursal_id, activo),
    KEY ix_dispositivos_tipo (tipo),
    CONSTRAINT fk_dispositivos_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE canales_dispositivo (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    numero_canal SMALLINT UNSIGNED NOT NULL,
    nombre VARCHAR(120) NULL,
    tipo VARCHAR(40) NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_canales_dispositivo_numero (dispositivo_id, numero_canal),
    CONSTRAINT fk_canales_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE credenciales_dispositivo (
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    algoritmo VARCHAR(32) NOT NULL DEFAULT 'aes-256-gcm',
    version_clave SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    usuario_cifrado VARBINARY(1024) NOT NULL,
    usuario_nonce BINARY(12) NOT NULL,
    usuario_tag BINARY(16) NOT NULL,
    secreto_cifrado VARBINARY(2048) NOT NULL,
    secreto_nonce BINARY(12) NOT NULL,
    secreto_tag BINARY(16) NOT NULL,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (dispositivo_id),
    CONSTRAINT fk_credenciales_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permisos_sucursal (
    usuario_id BIGINT UNSIGNED NOT NULL,
    sucursal_id BIGINT UNSIGNED NOT NULL,
    puede_ver TINYINT(1) NOT NULL DEFAULT 1,
    puede_administrar TINYINT(1) NOT NULL DEFAULT 0,
    creado_en DATETIME NOT NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (usuario_id, sucursal_id),
    KEY ix_permisos_sucursal_sucursal (sucursal_id),
    CONSTRAINT fk_permisos_sucursal_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id) ON DELETE CASCADE,
    CONSTRAINT fk_permisos_sucursal_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auditoria (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    usuario_id BIGINT UNSIGNED NULL,
    accion VARCHAR(80) NOT NULL,
    tipo_entidad VARCHAR(60) NOT NULL,
    entidad_id BIGINT UNSIGNED NULL,
    creado_en DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY ix_auditoria_usuario_fecha (usuario_id, creado_en),
    KEY ix_auditoria_entidad_fecha (tipo_entidad, entidad_id, creado_en),
    CONSTRAINT fk_auditoria_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE intentos_acceso (
    clave_hash CHAR(64) NOT NULL,
    ventana_inicio DATETIME NOT NULL,
    intentos SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    bloqueado_hasta DATETIME NULL,
    actualizado_en DATETIME NOT NULL,
    PRIMARY KEY (clave_hash),
    KEY ix_intentos_acceso_actualizado (actualizado_en)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;