-- Base de datos para Sistema de Vacantes de Empleo - Turpial Airlines, C.A.
-- Fecha: 2025-10-08

CREATE DATABASE IF NOT EXISTS turpial_jobs CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE turpial_jobs;

-- Tabla de roles de usuarios
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de usuarios administrativos
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- Tabla de divisiones de la empresa
CREATE TABLE divisions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    manager VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de categorías de empleo
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    division_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE CASCADE
);

-- Tabla de vacantes de empleo
CREATE TABLE job_vacancies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    requirements TEXT,
    responsibilities TEXT,
    salary_range VARCHAR(100),
    employment_type ENUM('Tiempo Completo', 'Medio Tiempo', 'Contrato', 'Pasantía') DEFAULT 'Tiempo Completo',
    location VARCHAR(100),
    experience_required VARCHAR(50),
    education_required VARCHAR(100),
    deadline DATE,
    max_applicants INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Tabla de postulaciones
CREATE TABLE applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    job_vacancy_id INT NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    country VARCHAR(50) DEFAULT 'Venezuela',
    education_level VARCHAR(50),
    years_experience INT DEFAULT 0,
    current_position VARCHAR(100),
    cover_letter TEXT,
    cv_filename VARCHAR(255),
    cv_path VARCHAR(500),
    application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('Pendiente', 'En Revisión', 'Entrevista', 'Seleccionado', 'Rechazado') DEFAULT 'Pendiente',
    notes TEXT,
    reviewed_by INT,
    reviewed_at TIMESTAMP NULL,
    FOREIGN KEY (job_vacancy_id) REFERENCES job_vacancies(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id)
);

-- Tabla de configuración del sistema
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Índices para optimización
CREATE INDEX idx_applications_job_vacancy ON applications(job_vacancy_id);
CREATE INDEX idx_applications_status ON applications(status);
CREATE INDEX idx_applications_date ON applications(application_date);
CREATE INDEX idx_job_vacancies_category ON job_vacancies(category_id);
CREATE INDEX idx_job_vacancies_active ON job_vacancies(is_active);
CREATE INDEX idx_categories_division ON categories(division_id);

-- Datos iniciales
INSERT INTO roles (name, description) VALUES 
('Super Admin', 'Acceso completo al sistema'),
('Admin', 'Administrador con acceso limitado'),
('HR Manager', 'Gestor de recursos humanos'),
('Viewer', 'Solo visualización');

INSERT INTO users (username, email, password, full_name, role_id) VALUES 
('admin', 'admin@turpialairlines.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrador Principal', 1),
('hr_manager', 'rrhh@turpialairlines.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Gerente de RRHH', 3);
-- Password para ambos usuarios: 'password'

INSERT INTO divisions (name, description, manager) VALUES 
('División de Sistemas', 'Tecnología e informática de la aerolínea', 'Carlos Rodríguez'),
('División de Operaciones', 'Operaciones de vuelo y mantenimiento', 'Ana Martínez'),
('División Comercial', 'Ventas, marketing y atención al cliente', 'Luis González'),
('División Administrativa', 'Recursos humanos, finanzas y contabilidad', 'María Fernández'),
('División de Seguridad', 'Seguridad aeroportuaria y corporativa', 'Roberto Silva');

INSERT INTO categories (division_id, name, description) VALUES 
(1, 'IT y Desarrollo', 'Programación, desarrollo web y aplicaciones'),
(1, 'Infraestructura', 'Redes, servidores y telecomunicaciones'),
(1, 'Soporte Técnico', 'Mesa de ayuda y soporte a usuarios'),
(2, 'Pilotos', 'Personal de vuelo - Comandantes y copilotos'),
(2, 'Tripulación de Cabina', 'Auxiliares de vuelo y sobrecargos'),
(2, 'Mantenimiento', 'Técnicos de mantenimiento aeronáutico'),
(3, 'Ventas', 'Agentes de ventas y reservas'),
(3, 'Marketing', 'Mercadeo y publicidad'),
(3, 'Atención al Cliente', 'Servicio al cliente y call center'),
(4, 'Recursos Humanos', 'Personal de RRHH y reclutamiento'),
(4, 'Finanzas', 'Contabilidad, finanzas y auditoría'),
(5, 'Seguridad Aeroportuaria', 'Personal de seguridad en aeropuertos'),
(5, 'Seguridad Corporativa', 'Seguridad de instalaciones y personal');

INSERT INTO settings (setting_key, setting_value, description) VALUES 
('company_name', 'Turpial Airlines, C.A.', 'Nombre de la empresa'),
('company_email', 'rrhh@turpialairlines.com', 'Email principal de la empresa'),
('company_phone', '+58-212-555-0123', 'Teléfono principal'),
('company_address', 'Av. Principal de Maiquetía, Aeropuerto Internacional Simón Bolívar', 'Dirección de la empresa'),
('max_cv_size', '5242880', 'Tamaño máximo de CV en bytes (5MB)'),
('allowed_cv_formats', 'pdf,doc,docx', 'Formatos de CV permitidos');