create_database.sql

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS vdart_hr;

-- Use the database
USE vdart_hr;

-- Create Exit Interviews Table
CREATE TABLE IF NOT EXISTS exit_interviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    employee_id VARCHAR(20) NOT NULL,
    designation VARCHAR(100) NOT NULL,
    team VARCHAR(100) NOT NULL,
    cal_dal VARCHAR(100) NOT NULL,
    bu_head VARCHAR(100) NOT NULL,
    doj VARCHAR(20) NOT NULL,
    dor VARCHAR(20) NOT NULL,
    resign_decision VARCHAR(100) NOT NULL,
    explored_options ENUM('Yes', 'No') NOT NULL,
    explored_options_desc TEXT,
    specific_event ENUM('Yes', 'No') NOT NULL,
    specific_event_desc TEXT,
    recommend_company ENUM('Yes', 'No') NOT NULL,
    recommend_company_desc TEXT,
    open_to_reapply ENUM('Yes', 'No') NOT NULL,
    open_to_reapply_desc TEXT,
    encourage_to_stay TEXT,
    factors_new_role TEXT,
    areas_to_improve TEXT,
    members_to_thank TEXT,
    rating_pay ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_training ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_supervisor ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_relationship ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_benefits ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_promotion ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_performance ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    rating_discipline ENUM('Excellent', 'Good', 'Fair', 'Poor') NOT NULL,
    hrbp_note TEXT,
    submission_date DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Exit Interview Reasons Table
CREATE TABLE IF NOT EXISTS exit_interview_reasons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    interview_id INT NOT NULL,
    reason VARCHAR(100) NOT NULL,
    FOREIGN KEY (interview_id) REFERENCES exit_interviews(id) ON DELETE CASCADE
);