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
);