-- ===================================================================== -- TecnoTrack - Sistema de rastreo de personal en campo -- Motor: MySQL 5.7+/MariaDB 10.3+ (InnoDB, utf8mb4) -- Acceso desde PHP vía mysqli (sin PDO), como el resto de sistemas -- de TecnoMania / TecnoEngineers. -- -- Diseñado para arrancar como app single-tenant (una sola empresa, -- TecnoMania/TecnoEngineers) pero con las columnas de multi-empresa -- y multi-sucursal ya presentes (empresa_id, sucursal_id) para no -- tener que migrar datos cuando se venda a otras empresas. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- EMPRESAS (multi-tenant, hoy solo existirá 1 fila: TecnoMania) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS empresas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(150) NOT NULL, nit VARCHAR(30) NULL, logo_path VARCHAR(255) NULL, -- logo usado en el QR y en el panel color_primario VARCHAR(7) NULL, -- branding del QR/panel, ej. #1A73E8 direccion VARCHAR(255) NULL, telefono VARCHAR(30) NULL, email_contacto VARCHAR(150) NULL, timezone VARCHAR(60) NOT NULL DEFAULT 'America/Guatemala', activo TINYINT(1) NOT NULL DEFAULT 1, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, actualizado_en DATETIME NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- SUCURSALES (multi-sucursal, hoy 1 fila por defecto por empresa) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sucursales ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, nombre VARCHAR(150) NOT NULL, direccion VARCHAR(255) NULL, lat_centro DECIMAL(10,7) NULL, -- centro del mapa por defecto lng_centro DECIMAL(10,7) NULL, activo TINYINT(1) NOT NULL DEFAULT 1, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_sucursales_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- GRUPOS DE USUARIOS (roles configurables, no hardcodeados) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS grupos_usuarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, nombre VARCHAR(100) NOT NULL, -- ej. "Administrador", "Supervisor", "Solo lectura" descripcion VARCHAR(255) NULL, es_super_admin TINYINT(1) NOT NULL DEFAULT 0, -- bypass total de permisos (dueño del sistema) activo TINYINT(1) NOT NULL DEFAULT 1, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_grupos_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- MÓDULOS del sistema (catálogo fijo en código, reflejado aquí para -- poder armar la matriz de permisos por grupo desde el panel) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS modulos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, codigo VARCHAR(60) NOT NULL UNIQUE, -- ej. 'mapa_vivo', 'empleados', 'historial', 'usuarios' nombre VARCHAR(100) NOT NULL, orden SMALLINT UNSIGNED NOT NULL DEFAULT 0, activo TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- PERMISOS GRANULARES por grupo y módulo (ver / crear / editar / borrar / exportar) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS permisos_grupo ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, grupo_id INT UNSIGNED NOT NULL, modulo_id INT UNSIGNED NOT NULL, puede_ver TINYINT(1) NOT NULL DEFAULT 0, puede_crear TINYINT(1) NOT NULL DEFAULT 0, puede_editar TINYINT(1) NOT NULL DEFAULT 0, puede_eliminar TINYINT(1) NOT NULL DEFAULT 0, puede_exportar TINYINT(1) NOT NULL DEFAULT 0, UNIQUE KEY uq_grupo_modulo (grupo_id, modulo_id), CONSTRAINT fk_permisos_grupo FOREIGN KEY (grupo_id) REFERENCES grupos_usuarios(id) ON DELETE CASCADE, CONSTRAINT fk_permisos_modulo FOREIGN KEY (modulo_id) REFERENCES modulos(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- USUARIOS del panel web (los que hacen login para ver el mapa/reportes) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS usuarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, sucursal_id INT UNSIGNED NULL, -- NULL = acceso a todas las sucursales de su empresa grupo_id INT UNSIGNED NOT NULL, nombre_completo VARCHAR(150) NOT NULL, usuario VARCHAR(60) NOT NULL, -- login email VARCHAR(150) NULL, password_hash VARCHAR(255) NOT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, ultimo_login DATETIME NULL, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, actualizado_en DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_usuario_empresa (empresa_id, usuario), CONSTRAINT fk_usuarios_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_usuarios_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id), CONSTRAINT fk_usuarios_grupo FOREIGN KEY (grupo_id) REFERENCES grupos_usuarios(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- DEPARTAMENTOS / PROYECTOS / SISTEMAS a los que se liga un empleado -- (catálogo libre: "Instalaciones Solares", "Soporte TecnoFoodSuite", -- "Proyecto Hotel X", etc. — así el mapa muestra a qué pertenece cada uno) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS departamentos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, nombre VARCHAR(120) NOT NULL, tipo ENUM('departamento','proyecto','sistema','otro') NOT NULL DEFAULT 'departamento', color VARCHAR(7) NULL, -- color del marcador en el mapa activo TINYINT(1) NOT NULL DEFAULT 1, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_departamentos_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- EMPLEADOS (personal de campo, NO tienen login al panel) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS empleados ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, sucursal_id INT UNSIGNED NULL, departamento_id INT UNSIGNED NULL, -- a qué depto/proyecto/sistema pertenece codigo_empleado VARCHAR(30) NULL, -- código interno opcional nombre_completo VARCHAR(150) NOT NULL, puesto VARCHAR(100) NULL, telefono VARCHAR(30) NULL, foto_path VARCHAR(255) NULL, activo TINYINT(1) NOT NULL DEFAULT 1, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, actualizado_en DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_empleados_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_empleados_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id), CONSTRAINT fk_empleados_departamento FOREIGN KEY (departamento_id) REFERENCES departamentos(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- TOKENS DE EMPAREJAMIENTO QR -- Se genera 1 registro por intento de vinculación. El QR contiene -- solo este token (+ opcionalmente empresa/logo para pintar el QR), -- nunca usuario/contraseña del panel ni el grupo de permisos. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS qr_emparejamiento ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, empleado_id INT UNSIGNED NOT NULL, token CHAR(64) NOT NULL UNIQUE, -- token aleatorio criptográfico generado_por INT UNSIGNED NOT NULL, -- usuarios.id que generó el QR expira_en DATETIME NOT NULL, -- QR válido por tiempo limitado (ej. 15 min) usado TINYINT(1) NOT NULL DEFAULT 0, usado_en DATETIME NULL, dispositivo_id INT UNSIGNED NULL, -- se llena cuando se usa creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_qr_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_qr_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id), CONSTRAINT fk_qr_usuario FOREIGN KEY (generado_por) REFERENCES usuarios(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- DISPOSITIVOS vinculados (1 dispositivo activo por empleado normalmente, -- pero se permite historial de dispositivos reemplazados) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS dispositivos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, empleado_id INT UNSIGNED NOT NULL, nombre_dispositivo VARCHAR(150) NULL, -- modelo, ej. "Samsung Tab A9" identificador VARCHAR(150) NULL, -- Android ID / IMEI si se captura api_token CHAR(64) NOT NULL UNIQUE, -- token que la app usa para autenticar cada request app_version VARCHAR(20) NULL, estado ENUM('activo','pausado','revocado') NOT NULL DEFAULT 'activo', ultima_conexion DATETIME NULL, ultima_lat DECIMAL(10,7) NULL, ultima_lng DECIMAL(10,7) NULL, bateria_pct TINYINT UNSIGNED NULL, vinculado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, revocado_en DATETIME NULL, CONSTRAINT fk_dispositivos_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_dispositivos_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE qr_emparejamiento ADD CONSTRAINT fk_qr_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos(id); -- --------------------------------------------------------------------- -- JORNADAS (turno de trabajo: inicio/fin, para poder mostrar -- "hora de inicio y final" como pediste) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS jornadas ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, empleado_id INT UNSIGNED NOT NULL, dispositivo_id INT UNSIGNED NOT NULL, inicio_en DATETIME NOT NULL, fin_en DATETIME NULL, -- NULL = jornada en curso origen_fin ENUM('app','timeout','admin') NULL, -- cómo terminó (el usuario cerró app, se perdió señal, etc.) creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_jornadas_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_jornadas_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id), CONSTRAINT fk_jornadas_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos(id), INDEX idx_jornadas_empleado_fecha (empleado_id, inicio_en) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- UBICACIONES (histórico crudo de puntos GPS — la tabla más grande, -- pensar en particionar/purgar por fecha a futuro) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS ubicaciones ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, empleado_id INT UNSIGNED NOT NULL, dispositivo_id INT UNSIGNED NOT NULL, jornada_id BIGINT UNSIGNED NULL, lat DECIMAL(10,7) NOT NULL, lng DECIMAL(10,7) NOT NULL, precision_m DECIMAL(6,2) NULL, -- precisión GPS reportada, en metros velocidad_kmh DECIMAL(6,2) NULL, bateria_pct TINYINT UNSIGNED NULL, capturado_en DATETIME(3) NOT NULL, -- momento real en el dispositivo (ms) recibido_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- cuándo llegó al servidor CONSTRAINT fk_ubicaciones_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_ubicaciones_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id), CONSTRAINT fk_ubicaciones_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos(id), CONSTRAINT fk_ubicaciones_jornada FOREIGN KEY (jornada_id) REFERENCES jornadas(id), INDEX idx_ubicaciones_empleado_fecha (empleado_id, capturado_en), INDEX idx_ubicaciones_dispositivo_fecha (dispositivo_id, capturado_en) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- PARADAS DETECTADAS (calculadas por un job/rutina a partir de -- `ubicaciones`: cuando el empleado permanece en un radio pequeño -- por más de N minutos). Se guardan ya resueltas para que el -- historial/auditoría cargue rápido sin recalcular cada vez. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS paradas ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, empleado_id INT UNSIGNED NOT NULL, jornada_id BIGINT UNSIGNED NULL, lat_centro DECIMAL(10,7) NOT NULL, lng_centro DECIMAL(10,7) NOT NULL, inicio_en DATETIME NOT NULL, fin_en DATETIME NOT NULL, duracion_seg INT UNSIGNED NOT NULL, direccion_aprox VARCHAR(255) NULL, -- reverse geocoding opcional, futuro creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_paradas_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_paradas_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id), CONSTRAINT fk_paradas_jornada FOREIGN KEY (jornada_id) REFERENCES jornadas(id), INDEX idx_paradas_empleado_fecha (empleado_id, inicio_en) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- EVENTOS DEL DISPOSITIVO (para auditoría: pairing, revocación, -- cambios de estado de permisos de ubicación reportados por la app, etc.) -- Nota: NO incluye eventos de "desinstalación oculta" — la app es -- transparente para el empleado (ver docs/README.md). -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS eventos_dispositivo ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, dispositivo_id INT UNSIGNED NOT NULL, tipo VARCHAR(60) NOT NULL, -- 'vinculado','revocado','permiso_ubicacion_denegado','app_actualizada', etc. detalle VARCHAR(255) NULL, creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_eventos_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id), CONSTRAINT fk_eventos_dispositivo FOREIGN KEY (dispositivo_id) REFERENCES dispositivos(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- DATOS INICIALES (seed) -- ===================================================================== INSERT INTO empresas (nombre, timezone) VALUES ('TecnoMania', 'America/Guatemala'); SET @empresa_id = LAST_INSERT_ID(); INSERT INTO sucursales (empresa_id, nombre) VALUES (@empresa_id, 'Sede Principal'); INSERT INTO modulos (codigo, nombre, orden) VALUES ('mapa_vivo', 'Mapa en vivo', 1), ('historial', 'Historial y auditoría', 2), ('empleados', 'Empleados', 3), ('dispositivos', 'Dispositivos', 4), ('departamentos', 'Departamentos / Proyectos', 5), ('usuarios', 'Usuarios del panel', 6), ('grupos', 'Grupos y permisos', 7), ('configuracion', 'Configuración de empresa', 8); INSERT INTO grupos_usuarios (empresa_id, nombre, descripcion, es_super_admin) VALUES (@empresa_id, 'Administrador', 'Acceso total al sistema', 1), (@empresa_id, 'Supervisor', 'Ve mapa e historial, no administra usuarios', 0); -- El grupo Administrador es super_admin y no necesita filas en permisos_grupo -- (se resuelve en código: es_super_admin = 1 => todo permitido). -- Permisos iniciales del grupo "Supervisor": solo ver mapa e historial INSERT INTO permisos_grupo (grupo_id, modulo_id, puede_ver, puede_crear, puede_editar, puede_eliminar, puede_exportar) SELECT g.id, m.id, 1, 0, 0, 0, 1 FROM grupos_usuarios g, modulos m WHERE g.nombre = 'Supervisor' AND g.empresa_id = @empresa_id AND m.codigo IN ('mapa_vivo','historial'); -- Usuario admin inicial (password por defecto "cambiar123" -- CAMBIAR EN PRODUCCIÓN) -- Hash generado con password_hash('cambiar123', PASSWORD_BCRYPT) INSERT INTO usuarios (empresa_id, grupo_id, nombre_completo, usuario, email, password_hash) SELECT @empresa_id, g.id, 'Administrador', 'admin', 'admin@tecnomaniagt.com', '$2y$10$8K1p/a0dURXAMgPpGL8pAeR2eq.ZfHYALJn2FS0O5gJmB0XkFtq2G' FROM grupos_usuarios g WHERE g.nombre = 'Administrador' AND g.empresa_id = @empresa_id;