CREATE TABLE job_openings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    field VARCHAR(100),
    location VARCHAR(150),
    description TEXT,
    status ENUM('open','closed','paused') DEFAULT 'open',
    deadline DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);




CREATE TABLE user_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    postal VARCHAR(20),
    country VARCHAR(100),

    resume VARCHAR(255),
    cover_letter TEXT,

    ssn VARCHAR(50), -- store encrypted if possible

    school VARCHAR(150),
    qualification VARCHAR(100),
    field_of_study VARCHAR(150),
    overall_gpa VARCHAR(10),
    language VARCHAR(100),

    sponsorship ENUM('yes','no') DEFAULT 'no',
    restriction ENUM('yes','no') DEFAULT 'no',
    our_staff ENUM('yes','no') DEFAULT 'no',
    secondary_employment ENUM('yes','no') DEFAULT 'no',

    how_did_u_hear_about_us VARCHAR(150),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    job_id INT NOT NULL,

    status ENUM(
        'applied',
        'under_review',
        'shortlisted',
        'interview',
        'offered',
        'rejected'
    ) DEFAULT 'applied',

    date_applied TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,

    notes TEXT,

    FOREIGN KEY (user_id) REFERENCES user_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (job_id) REFERENCES job_openings(id) ON DELETE CASCADE
);



CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,

    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,

    subject VARCHAR(150),
    message TEXT NOT NULL,

    sender_type ENUM('admin','user') NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (sender_id) REFERENCES user_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES user_accounts(id) ON DELETE CASCADE
);



CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(150),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
