-- ============================================================
-- 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');

-- ------------------------------------------------------------
-- 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)
);
