-- Base de datos para Sistema de Selección de Personal Sanmarino
CREATE DATABASE IF NOT EXISTS sanmarino_seleccion;
USE sanmarino_seleccion;

-- Tabla de requisiciones (solicitudes de vacantes)
CREATE TABLE requisiciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50) UNIQUE NOT NULL,
    fecha_solicitud DATE NOT NULL,
    empresa VARCHAR(100) DEFAULT 'AGROAVICOLA SANMARINO',
    regional VARCHAR(100),
    area VARCHAR(100),
    ciudad_ubicacion VARCHAR(100),
    centro_costo VARCHAR(50),
    nombre_cargo VARCHAR(150),
    adjunto_descripcion TEXT,
    numero_vacantes INT DEFAULT 1,
    tipo_contrato VARCHAR(50),
    
    -- Información Salarial
    salario_fijo DECIMAL(12,2),
    proporcion_flexible DECIMAL(5,2),
    salario_variable TEXT,
    tiene_comisiones ENUM('Si','No') DEFAULT 'No',
    tiene_rodamiento ENUM('Si','No') DEFAULT 'No',
    rango_rodamiento VARCHAR(50),
    tabla_comisiones TEXT,
    
    -- Condiciones especiales
    requiere_vehiculo ENUM('Si','No') DEFAULT 'No',
    flexibilidad_ubicacion ENUM('Si','No') DEFAULT 'No',
    clausula_transversalidad ENUM('Si','No') DEFAULT 'No',
    observaciones_perfil TEXT,
    otras_condiciones TEXT,
    
    -- Aprobación
    justificacion_requisicion VARCHAR(100),
    tipo_busqueda ENUM('Interna','Externa') DEFAULT 'Externa',
    firma_jefe VARCHAR(100),
    firma_aprobacion VARCHAR(100),
    estado ENUM('Pendiente','Aprobada','En Proceso','Cerrada') DEFAULT 'Pendiente',
    
    -- Metadata
    usuario_solicita_id INT,
    email_solicita VARCHAR(100),
    fecha_notificacion DATETIME,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_solicita_id) REFERENCES usuarios(id),
    INDEX idx_codigo (codigo),
    INDEX idx_estado (estado),
    INDEX idx_fecha_solicitud (fecha_solicitud)
);

-- Tabla de candidatos (Fase 1)
CREATE TABLE candidatos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requisicion_id INT NOT NULL,
    cedula VARCHAR(20) UNIQUE NOT NULL,
    nombres_apellidos VARCHAR(150) NOT NULL,
    antecedentes ENUM('Aprobado','Rechazado','Pendiente') DEFAULT 'Pendiente',
    celular VARCHAR(20),
    
    -- Contador de veces en proceso
    veces_proceso INT DEFAULT 1,
    
    fase_actual ENUM('fase1','fase2','fase3','contratado','descartado') DEFAULT 'fase1',
    estado ENUM('Activo','Inactivo','Contratado','Descartado') DEFAULT 'Activo',
    
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (requisicion_id) REFERENCES requisiciones(id) ON DELETE CASCADE,
    INDEX idx_cedula (cedula),
    INDEX idx_fase (fase_actual),
    INDEX idx_requisicion (requisicion_id)
);

-- Tabla de entrevistas (Fase 2)
CREATE TABLE entrevistas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidato_id INT NOT NULL,
    
    -- Quien refiere
    referidor_nombre VARCHAR(150),
    referidor_cedula VARCHAR(20),
    referidor_area VARCHAR(100),
    
    -- Datos entrevista
    centro_costos VARCHAR(50),
    area VARCHAR(100),
    cargo VARCHAR(100),
    ciudad_vacante VARCHAR(100),
    entidad_reclutadora VARCHAR(100),
    fecha_entrevista DATE,
    supervisor_entrevista VARCHAR(150),
    concepto_entrevista TEXT,
    resultado_entrevista ENUM('Aprobado','Rechazado','Pendiente') DEFAULT 'Pendiente',
    
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (candidato_id) REFERENCES candidatos(id) ON DELETE CASCADE,
    INDEX idx_candidato (candidato_id),
    INDEX idx_fecha_entrevista (fecha_entrevista)
);

-- Tabla de exámenes (Fase 3)
CREATE TABLE examenes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidato_id INT NOT NULL,
    
    examenes_medicos ENUM('Apto','No Apto','Pendiente') DEFAULT 'Pendiente',
    poligrafo ENUM('Aprobado','Rechazado','Pendiente','No Aplica') DEFAULT 'Pendiente',
    
    contratado ENUM('Si','No') DEFAULT 'No',
    fecha_contratacion DATE,
    observaciones TEXT,
    
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (candidato_id) REFERENCES candidatos(id) ON DELETE CASCADE,
    INDEX idx_candidato (candidato_id)
);

-- Tabla de historial de alertas
CREATE TABLE alertas_candidato (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidato_id INT NOT NULL,
    requisicion_id INT NOT NULL,
    cedula VARCHAR(20) NOT NULL,
    fecha_alerta TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    descripcion TEXT,
    
    FOREIGN KEY (candidato_id) REFERENCES candidatos(id) ON DELETE CASCADE,
    FOREIGN KEY (requisicion_id) REFERENCES requisiciones(id) ON DELETE CASCADE,
    INDEX idx_cedula (cedula)
);

-- Tabla de notificaciones por email
CREATE TABLE notificaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requisicion_id INT,
    destinatario_email VARCHAR(100) NOT NULL,
    asunto VARCHAR(200),
    mensaje TEXT,
    tipo ENUM('vacante_encontrada','estado_candidato','aprobacion') DEFAULT 'vacante_encontrada',
    enviado TINYINT(1) DEFAULT 0,
    fecha_envio DATETIME,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (requisicion_id) REFERENCES requisiciones(id) ON DELETE SET NULL,
    INDEX idx_enviado (enviado)
);
-- Password por defecto: password123

-- Vista para reportes de candidatos por requisición
CREATE VIEW vista_candidatos_completa AS
SELECT 
    c.id as candidato_id,
    c.cedula,
    c.nombres_apellidos,
    c.celular,
    c.antecedentes,
    c.fase_actual,
    c.estado,
    c.veces_proceso,
    r.codigo as requisicion_codigo,
    r.nombre_cargo,
    r.area,
    r.ciudad_ubicacion,
    e.fecha_entrevista,
    e.concepto_entrevista,
    e.resultado_entrevista,
    ex.examenes_medicos,
    ex.poligrafo,
    ex.contratado,
    ex.fecha_contratacion
FROM candidatos c
LEFT JOIN requisiciones r ON c.requisicion_id = r.id
LEFT JOIN entrevistas e ON c.id = e.candidato_id
LEFT JOIN examenes ex ON c.id = ex.candidato_id;

-- Trigger para generar código único de requisición
DELIMITER //
CREATE TRIGGER generar_codigo_requisicion 
BEFORE INSERT ON requisiciones
FOR EACH ROW
BEGIN
    DECLARE nuevo_codigo VARCHAR(50);
    SET nuevo_codigo = CONCAT('REQ-', YEAR(NEW.fecha_solicitud), '-', LPAD(FLOOR(RAND() * 9999), 4, '0'));
    WHILE EXISTS(SELECT 1 FROM requisiciones WHERE codigo = nuevo_codigo) DO
        SET nuevo_codigo = CONCAT('REQ-', YEAR(NEW.fecha_solicitud), '-', LPAD(FLOOR(RAND() * 9999), 4, '0'));
    END WHILE;
    SET NEW.codigo = nuevo_codigo;
END//
DELIMITER ;

-- Trigger para incrementar contador de veces en proceso
DELIMITER //
CREATE TRIGGER incrementar_veces_proceso
BEFORE INSERT ON candidatos
FOR EACH ROW
BEGIN
    DECLARE veces INT;
    SELECT COUNT(*) INTO veces FROM candidatos WHERE cedula = NEW.cedula;
    IF veces > 0 THEN
        SET NEW.veces_proceso = veces + 1;
    END IF;
END//
DELIMITER ;