Salary Market

USE salarymarket;

-- Create solution_quiz table

CREATE TABLE solution_quiz (

    id INT AUTO_INCREMENT UNIQUE,

    quizCode VARCHAR(255) NOT NULL PRIMARY KEY,

    quiz_image VARCHAR(255) NULL,    

    averagetime VARCHAR(3) NOT NULL,  

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

-- Create solution_quiz_country table

CREATE TABLE solution_quiz_country (

    id INT PRIMARY KEY AUTO_INCREMENT,

    quizCode VARCHAR(255) NOT NULL,

    countryCode VARCHAR(255) NOT NULL,

    FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_translate table

CREATE TABLE solution_quiz_translate (

    id INT PRIMARY KEY AUTO_INCREMENT,

    quizCode VARCHAR(255) NOT NULL,

    title VARCHAR(255) NOT NULL,

    short_description TEXT NULL,  

    description TEXT NULL,

    languageId INT NOT NULL,

    FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_section table

CREATE TABLE solution_quiz_section (

    id INT AUTO_INCREMENT UNIQUE,

    sectionCode VARCHAR(255) NOT NULL PRIMARY KEY,

    quizCode VARCHAR(255) NOT NULL,

    section_order INT NOT NULL,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_section_translate table

CREATE TABLE solution_quiz_section_translate (

    id INT PRIMARY KEY AUTO_INCREMENT,

    sectionCode VARCHAR(255) NOT NULL,

    title VARCHAR(255) NOT NULL,

    languageId INT NOT NULL,

    FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (sectionCode) REFERENCES solution_quiz_section(sectionCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_questions table

CREATE TABLE solution_quiz_section_questions (

    id INT AUTO_INCREMENT UNIQUE,

    questionCode VARCHAR(255) NOT NULL  PRIMARY KEY,

    sectionCode VARCHAR(255) NOT NULL,

    quizCode VARCHAR(255) NOT NULL,

    idparent VARCHAR(255) NULL,

    type VARCHAR(2) NOT NULL,

    question_order INT NOT NULL,

    echartType VARCHAR(255) NULL,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE

,

    FOREIGN KEY (sectionCode) REFERENCES solution_quiz_section(sectionCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_questions_translate table

CREATE TABLE solution_quiz_section_questions_translate (

    id INT PRIMARY KEY AUTO_INCREMENT,

    questionCode VARCHAR(255) NOT NULL,

    title VARCHAR(255) NOT NULL,

    languageId INT NOT NULL,

    FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (questionCode) REFERENCES solution_quiz_section_questions(questionCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Table for storing unique quiz items

CREATE TABLE solution_reponses_items (

    itemCode VARCHAR(255)  NOT NULL  PRIMARY KEY,

    itemName  VARCHAR(255)

);

-- Table for linking items to multiple questions

CREATE TABLE solution_quiz_section_questions_items (

    id INT AUTO_INCREMENT PRIMARY KEY,  

    itemCode VARCHAR(255) NOT NULL,  

    questionCode VARCHAR(255) NOT NULL,  

    itemParentId  VARCHAR(255) NULL,

    isOther BOOLEAN DEFAULT 0,

    FOREIGN KEY (itemCode) REFERENCES solution_reponses_items(itemCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (questionCode) REFERENCES solution_quiz_section_questions(questionCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create solution_quiz_question_items_translate table

CREATE TABLE solution_quiz_question_items_translate (

    id INT PRIMARY KEY AUTO_INCREMENT,

    itemCode VARCHAR(255) NOT NULL,

    itemValue VARCHAR(255) NOT NULL,

    languageId INT NOT NULL,

    FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (itemCode) REFERENCES solution_reponses_items(itemCode) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create company_quiz table

CREATE TABLE company_quiz (

    id INT AUTO_INCREMENT PRIMARY KEY,

    companyId INT NOT NULL,

    quizCode VARCHAR(255) NOT NULL,

    quiz_status ENUM('NOT_STARTED','IN_PROGRESS','COMPLETED') DEFAULT 'NOT_STARTED',

    progress_value FLOAT DEFAULT 0,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE

);

-- Create company_quiz_response table

CREATE TABLE company_quiz_response (

    id INT AUTO_INCREMENT PRIMARY KEY,

    quizCode VARCHAR(255) NOT NULL,

    questionCode VARCHAR(255) NOT NULL, 

    sectionCode VARCHAR(255) NOT NULL,

    itemCode VARCHAR(255) NOT NULL,

    response VARCHAR(255),

    type VARCHAR(50),

    companyId INT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quizCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (sectionCode) REFERENCES solution_quiz_section(sectionCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (itemCode) REFERENCES solution_reponses_items(itemCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (questionCode) REFERENCES solution_quiz_section_questions(questionCode) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE

);