-- Migration script to add new columns and tables

USE bolsa_trabajo;

-- Add new columns to students table
ALTER TABLE students
ADD COLUMN dni VARCHAR(20) AFTER career_id,
ADD COLUMN birth_date DATE AFTER dni,
ADD COLUMN address TEXT AFTER birth_date,
ADD COLUMN phone VARCHAR(20) AFTER address;

-- Remove old columns from students table (now handled by separate tables)
ALTER TABLE students
DROP COLUMN work_experience,
DROP COLUMN languages,
DROP COLUMN knowledge,
DROP COLUMN certificates,
DROP COLUMN language_certificates;

-- Create student_languages table for multiple languages
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)
);

-- Create student_trainings table for multiple trainings/courses
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)
);

-- Create student_work_experience table for multiple work experiences
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)
);

-- Create graduates table (for egresado role)
CREATE TABLE graduates (
    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,
    skills TEXT,
    work_experience TEXT,
    current_position VARCHAR(255),
    company VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (career_id) REFERENCES careers(id)
);

-- Create graduate_languages table for multiple languages per graduate
CREATE TABLE graduate_languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    graduate_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 (graduate_id) REFERENCES graduates(id) ON DELETE CASCADE,
    INDEX idx_graduate_language (graduate_id)
);

-- Create graduate_trainings table for multiple trainings/courses per graduate
CREATE TABLE graduate_trainings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    graduate_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 (graduate_id) REFERENCES graduates(id) ON DELETE CASCADE,
    INDEX idx_graduate_training (graduate_id)
);

-- Create graduate_work_experience table for multiple work experiences per graduate
CREATE TABLE graduate_work_experience (
    id INT AUTO_INCREMENT PRIMARY KEY,
    graduate_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 (graduate_id) REFERENCES graduates(id) ON DELETE CASCADE,
    INDEX idx_graduate_work_exp (graduate_id)
);

-- Verify the changes
DESCRIBE students;
DESCRIBE student_languages;