-- ============================================================ -- BackendAliat - DDL Schema -- App: demoaliat - Control Operativo -- ============================================================ CREATE DATABASE IF NOT EXISTS aliat_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE aliat_db; -- ------------------------------------------------------------ -- Roles -- ------------------------------------------------------------ CREATE TABLE cat_roles ( id_rol INT AUTO_INCREMENT PRIMARY KEY, desc_rol VARCHAR(60) NOT NULL, status TINYINT(1) NOT NULL DEFAULT 1 ); INSERT INTO cat_roles (desc_rol) VALUES ('Administrador'), ('Operador'); -- ------------------------------------------------------------ -- Usuarios -- ------------------------------------------------------------ CREATE TABLE cat_usuarios ( id_usuario INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(80) NOT NULL, a_paterno VARCHAR(60) NOT NULL, a_materno VARCHAR(60) DEFAULT NULL, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, email VARCHAR(120) DEFAULT NULL, id_rol INT NOT NULL DEFAULT 2, status TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_usr_rol FOREIGN KEY (id_rol) REFERENCES cat_roles(id_rol) ); -- Usuario demo: admin / admin123 INSERT INTO cat_usuarios (nombre, a_paterno, username, password_hash, email, id_rol) VALUES ('Administrador', 'Sistema', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- admin123 (bcrypt) 'admin@aliat.edu.mx', 1); -- ------------------------------------------------------------ -- Sucursales (24 campus) -- ------------------------------------------------------------ CREATE TABLE cat_sucursales ( id_sucursal INT AUTO_INCREMENT PRIMARY KEY, clave VARCHAR(20) NOT NULL UNIQUE, nombre VARCHAR(100) NOT NULL, ciudad VARCHAR(80) DEFAULT NULL, status TINYINT(1) NOT NULL DEFAULT 1 ); INSERT INTO cat_sucursales (clave, nombre) VALUES ('FORUM', 'FÓRUM'), ('HUASTECA', 'HUASTECA'), ('ANAHUAC', 'ANÁHUAC'), ('CUMBRES', 'CUMBRES'), ('CONTRY', 'CONTRY'), ('CONSTITUCION','CONSTITUCIÓN'), ('LINCOLN', 'LINCOLN'), ('MITRAS', 'MITRAS'), ('MORONES', 'MORONES PRIETO'), ('OBISPADO', 'OBISPADO'), ('REVOLUCION', 'REVOLUCIÓN'), ('ROMA', 'ROMA'), ('SABINITAS', 'SABINITAS'), ('SANNICOLAS', 'SAN NICOLÁS'), ('SENDERO', 'SENDERO'), ('SERTOMA', 'SERTOMA'), ('SKYNA', 'SKY-NA'), ('STAFE', 'SANTA FE'), ('TECNOLOGICO', 'TECNOLÓGICO'), ('UNIVERSIDAD', 'UNIVERSIDAD'), ('VALLES', 'VALLES'), ('VICTORIA', 'VICTORIA'), ('ZARAGOZA', 'ZARAGOZA'), ('ZUAZUA', 'ZUAZUA'); -- ------------------------------------------------------------ -- Catálogo de actividades / estatus -- Prefijos: IT=Sucursal, CO=Comer, PE=Personal, DE=Descansar, ACT=Intermedio -- ------------------------------------------------------------ CREATE TABLE cat_actividades ( id_actividad INT AUTO_INCREMENT PRIMARY KEY, codigo_estatus VARCHAR(10) NOT NULL UNIQUE, nombre VARCHAR(100) NOT NULL, prefijo ENUM('IT','CO','PE','DE','ACT') NOT NULL, status TINYINT(1) NOT NULL DEFAULT 1 ); INSERT INTO cat_actividades (codigo_estatus, nombre, prefijo) VALUES ('IT01', 'Llegué a sucursal', 'IT'), ('IT02', 'Salí de sucursal', 'IT'), ('CO01', 'Salí a comer', 'CO'), ('CO02', 'Regresé de comer', 'CO'), ('PE01', 'Asunto personal', 'PE'), ('PE02', 'Regresé de personal', 'PE'), ('DE01', 'Inicio descanso', 'DE'), ('DE02', 'Fin descanso', 'DE'), ('ACT01','Inicio actividad', 'ACT'), ('ACT02','Fin actividad', 'ACT'); -- ------------------------------------------------------------ -- Log de actividades del operador -- ------------------------------------------------------------ CREATE TABLE log_actividades ( id_log INT AUTO_INCREMENT PRIMARY KEY, id_usuario INT NOT NULL, id_sucursal INT DEFAULT NULL, id_actividad INT NOT NULL, comentarios TEXT DEFAULT NULL, fecha_hora DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, latitud DECIMAL(10,7) DEFAULT NULL, longitud DECIMAL(10,7) DEFAULT NULL, -- referencia al log anterior para calcular duración id_log_anterior INT DEFAULT NULL, CONSTRAINT fk_log_usr FOREIGN KEY (id_usuario) REFERENCES cat_usuarios(id_usuario), CONSTRAINT fk_log_suc FOREIGN KEY (id_sucursal) REFERENCES cat_sucursales(id_sucursal), CONSTRAINT fk_log_act FOREIGN KEY (id_actividad) REFERENCES cat_actividades(id_actividad) ); -- ------------------------------------------------------------ -- Aspirantes -- ------------------------------------------------------------ CREATE TABLE aspirantes ( id_aspirante INT AUTO_INCREMENT PRIMARY KEY, id_usuario_captura INT NOT NULL, id_sucursal INT DEFAULT NULL, -- Datos personales nombre VARCHAR(80) NOT NULL, a_paterno VARCHAR(60) NOT NULL, a_materno VARCHAR(60) DEFAULT NULL, procedencia VARCHAR(100) DEFAULT NULL, telefono_cel VARCHAR(20) DEFAULT NULL, telefono_casa VARCHAR(20) DEFAULT NULL, correo VARCHAR(120) DEFAULT NULL, grado_estudios VARCHAR(60) DEFAULT NULL, programa_interes VARCHAR(100) DEFAULT NULL, -- Captación medio_contacto ENUM('prensa','redes','whatsapp','referido','visita','otro') DEFAULT NULL, nivel_interes ENUM('Alto','Medio','Bajo') DEFAULT NULL, horario_contacto VARCHAR(60) DEFAULT NULL, comentarios TEXT DEFAULT NULL, fecha_registro DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asp_usr FOREIGN KEY (id_usuario_captura) REFERENCES cat_usuarios(id_usuario), CONSTRAINT fk_asp_suc FOREIGN KEY (id_sucursal) REFERENCES cat_sucursales(id_sucursal) ); -- ------------------------------------------------------------ -- Órdenes de Servicio -- ------------------------------------------------------------ CREATE TABLE ordenes_servicio ( id_orden INT AUTO_INCREMENT PRIMARY KEY, id_usuario INT NOT NULL, id_sucursal INT DEFAULT NULL, numero_orden VARCHAR(30) DEFAULT NULL, campus VARCHAR(100) DEFAULT NULL, empresa_escuela VARCHAR(150) DEFAULT NULL, meta_bdd VARCHAR(100) DEFAULT NULL, programa VARCHAR(100) DEFAULT NULL, responsable VARCHAR(100) DEFAULT NULL, accion TEXT DEFAULT NULL, fecha_orden DATE DEFAULT NULL, status TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_ord_usr FOREIGN KEY (id_usuario) REFERENCES cat_usuarios(id_usuario), CONSTRAINT fk_ord_suc FOREIGN KEY (id_sucursal) REFERENCES cat_sucursales(id_sucursal) ); -- ------------------------------------------------------------ -- PDI - Puntos de Interés (empresas) -- ------------------------------------------------------------ CREATE TABLE pdi ( id_pdi INT AUTO_INCREMENT PRIMARY KEY, id_sucursal INT DEFAULT NULL, nombre_empresa VARCHAR(150) NOT NULL, domicilio TEXT DEFAULT NULL, distancia_km DECIMAL(6,2) DEFAULT NULL, estatus_visita ENUM('CF','CV','pendiente') NOT NULL DEFAULT 'pendiente', comentarios TEXT DEFAULT NULL, status TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_pdi_suc FOREIGN KEY (id_sucursal) REFERENCES cat_sucursales(id_sucursal) ); -- ------------------------------------------------------------ -- Evidencias (fotos vinculadas a log_actividades o aspirantes) -- ------------------------------------------------------------ CREATE TABLE evidencias ( id_evidencia INT AUTO_INCREMENT PRIMARY KEY, id_usuario INT NOT NULL, entidad_tipo ENUM('log','aspirante','orden') NOT NULL, entidad_id INT NOT NULL, ruta_archivo VARCHAR(255) NOT NULL, nombre_original VARCHAR(255) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ev_usr FOREIGN KEY (id_usuario) REFERENCES cat_usuarios(id_usuario) );