-- Database schema for Bolsa de Trabajo system

CREATE DATABASE IF NOT EXISTS bolsa_trabajo;
USE bolsa_trabajo;

-- Careers table
CREATE TABLE careers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Roles table
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Insert default careers
INSERT INTO careers (name) VALUES
('Ingeniería de Sistemas'),
('Ingeniería Civil'),
('Medicina'),
('Derecho'),
('Administración'),
('Contabilidad'),
('Psicología'),
('Arquitectura'),
('Enfermería'),
('Educación');

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    photo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- Companies table (for empresa role)
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    company_name VARCHAR(100) NOT NULL,
    description TEXT,
    website VARCHAR(255),
    address VARCHAR(255),
    phone VARCHAR(20),
    logo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Students table (for alumno role)
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    career_id INT,
    dni VARCHAR(20),
    birth_date DATE,
    address TEXT,
    phone VARCHAR(20),
    graduation_year YEAR,
    resume TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (career_id) REFERENCES careers(id)
);

-- Teachers table (for profesor role)
CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    career_id INT,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (career_id) REFERENCES careers(id)
);

-- Jobs table
CREATE TABLE jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    career_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    requirements TEXT,
    salary DECIMAL(10,2),
    location VARCHAR(100),
    job_type ENUM('full-time', 'part-time', 'contract', 'internship') DEFAULT 'full-time',
    status ENUM('active', 'inactive', 'filled') DEFAULT 'active',
    posted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiration_date DATE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (career_id) REFERENCES careers(id)
);

-- Applications table
CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    student_id INT NOT NULL,
    status ENUM('pending', 'reviewed', 'accepted', 'rejected') DEFAULT 'pending',
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    UNIQUE KEY unique_application (job_id, student_id)
);


-- Insert default roles
INSERT INTO roles (name) VALUES ('administrador'), ('profesor'), ('alumno'), ('empresa');

-- Insert default admin user (username: admin, password: password)
INSERT INTO users (username, password, email, role_id, first_name, last_name) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@bolsa.com', 1, 'Admin', 'System');

-- Insert sample company users
INSERT INTO users (username, password, email, role_id, first_name, last_name) VALUES
('empresa1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'empresa1@bolsa.com', 4, 'Juan', 'Perez'),
('empresa2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'empresa2@bolsa.com', 4, 'Maria', 'Garcia'),
('empresa3', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'empresa3@bolsa.com', 4, 'Carlos', 'Lopez');

-- Insert sample companies
INSERT INTO companies (user_id, company_name, description, website, address, phone) VALUES
(2, 'Tech Solutions S.A.', 'Empresa líder en desarrollo de software y soluciones tecnológicas', 'https://techsolutions.com', 'Av. Principal 123, Lima', '+51 123 456 789'),
(3, 'Constructora Moderna', 'Construcción de infraestructuras modernas y sostenibles', 'https://constructora.com', 'Calle Industrial 456, Lima', '+51 987 654 321'),
(4, 'Consultora Educativa', 'Servicios de consultoría en educación y formación profesional', 'https://educacion.com', 'Jr. Educación 789, Lima', '+51 555 123 456');

-- Insert sample student users
INSERT INTO users (username, password, email, role_id, first_name, last_name) VALUES
('alumno1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'alumno1@bolsa.com', 3, 'Ana', 'Martinez'),
('alumno2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'alumno2@bolsa.com', 3, 'Pedro', 'Rodriguez');

-- Insert sample students
INSERT INTO students (user_id, career_id) VALUES
(5, 1), -- Ana Martinez - Ingeniería de Sistemas
(6, 2); -- Pedro Rodriguez - Ingeniería Civil

-- Student Languages table (for multiple languages per student)
CREATE TABLE student_languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    language_name VARCHAR(100) NOT NULL,
    proficiency_level VARCHAR(50),
    reading_level VARCHAR(20),
    writing_level VARCHAR(20),
    speaking_level VARCHAR(20),
    certificate_file VARCHAR(255),
    obtained_date DATE,
    expiry_date DATE,
    issuing_organization VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    INDEX idx_student_language (student_id)
);

-- Student Trainings/Courses table (for multiple trainings per student)
CREATE TABLE student_trainings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    training_name VARCHAR(255) NOT NULL,
    institution VARCHAR(255),
    training_type ENUM('course', 'workshop', 'seminar', 'certification', 'diploma', 'other') DEFAULT 'course',
    description TEXT,
    start_date DATE,
    end_date DATE,
    duration_hours INT,
    certificate_file VARCHAR(255),
    obtained_date DATE,
    expiry_date DATE,
    issuing_organization VARCHAR(255),
    skills_acquired TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    INDEX idx_student_training (student_id)
);

-- Student Work Experience table (for multiple work experiences per student)
CREATE TABLE student_work_experience (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    job_title VARCHAR(255) NOT NULL,
    employment_type ENUM('full-time', 'part-time', 'contract', 'freelance', 'internship', 'volunteer') DEFAULT 'full-time',
    industry VARCHAR(100),
    location VARCHAR(255),
    start_date DATE NOT NULL,
    end_date DATE,
    current_job BOOLEAN DEFAULT FALSE,
    description TEXT,
    achievements TEXT,
    skills_used TEXT,
    reference_contact VARCHAR(255),
    reference_phone VARCHAR(50),
    salary_range VARCHAR(100),
    reason_for_leaving TEXT,
    certificate_file VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    INDEX idx_student_work_exp (student_id)
);

-- Insert sample jobs
INSERT INTO jobs (company_id, career_id, title, description, requirements, salary, location, job_type) VALUES
(1, 1, 'Desarrollador Full Stack', 'Buscamos desarrollador con experiencia en PHP, JavaScript y bases de datos', '2 años de experiencia, conocimientos en Laravel y React', 2500.00, 'Lima', 'full-time'),
(1, 1, 'Analista de Sistemas', 'Posición para analista con conocimientos en análisis de requerimientos', '1 año de experiencia, UML, SQL', 2000.00, 'Lima', 'full-time'),
(2, 2, 'Ingeniero Civil Junior', 'Oportunidad para ingeniero civil recién graduado', 'Título universitario, conocimientos básicos de AutoCAD', 1800.00, 'Lima', 'full-time'),
(3, 3, 'Profesor de Matemáticas', 'Docente para institución educativa', 'Título en Educación Matemática, 3 años de experiencia', 1500.00, 'Lima', 'part-time');


-- Notifications table
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('info', 'success', 'warning', 'danger') DEFAULT 'info',
    title VARCHAR(255),
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user (user_id),
    INDEX idx_notifications_read (is_read),
    INDEX idx_notifications_created (created_at)
);

-- Indexes for better performance
CREATE INDEX idx_users_role ON users(role_id);
CREATE INDEX idx_jobs_company ON jobs(company_id);
CREATE INDEX idx_jobs_status ON jobs(status);
CREATE INDEX idx_applications_job ON applications(job_id);
CREATE INDEX idx_applications_student ON applications(student_id);