use salarymarket;

CREATE TABLE solution_quiz(
    id INT PRIMARY KEY AUTO_INCREMENT,
	quiz_code VARCHAR(255) NOT NULL,
	logo VARCHAR(255) NULL,	
    countryCode VARCHAR(255) NOT NULL,                              
    quiz_order INT NOT NULL,
	Averagetime	VARCHAR(3) NOT NULL, --NEW
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);
CREATE TABLE solution_quiz_translate(
    id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL,
	short_description Text null, --NEW
    description Text null,
	FOREIGN KEY (quizCode) REFERENCES solution_quiz(quiz_code) ON DELETE CASCADE ON UPDATE CASCADE
);	

-- Create solution_quiz_section table
CREATE TABLE solution_quiz_section (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sectionCode VARCHAR(255) NOT NULL,
    quizCode INT NOT NULL,
	section_order INT NOT NULL,
    FOREIGN KEY (quizCode) REFERENCES solution_quiz(quiz_code) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create solution_quiz_section table
CREATE TABLE solution_quiz_section_translate(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sectionCode VARCHAR(255) NOT NULL,
	title VARCHAR(255) NOT NULL,
    FOREIGN KEY (quizCode) REFERENCES solution_quiz_section(sectionCode) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_quiz_questions(
    id INT PRIMARY KEY AUTO_INCREMENT,
    questionCode INT NOT NULL,
    idparent INT DEFAULT 0,
    type VARCHAR(2) NOT NULL,
    question_order INT NOT NULL,
    echartType varchar(255),
    FOREIGN KEY (sectionId) REFERENCES solution_quiz_section(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_quiz_questions_translate(
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_Code VARCHAR(255) NOT NULL,
	title VARCHAR(255) NOT NULL,
    FOREIGN KEY (quizCode) REFERENCES solution_quiz_section(questionCode) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create solution_quiz_question_items table
CREATE TABLE solution_quiz_question_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    itemCode VARCHAR(255) NOT NULL,
    questionCode INT NOT NULL,
    itemParentId INT NULL,
    isOther BOOLEAN DEFAULT 0,
    FOREIGN KEY (questionCode) REFERENCES solution_quiz_questions(questionCode) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_quiz_question_items_translate (
    id INT PRIMARY KEY AUTO_INCREMENT,
    itemCode VARCHAR(255) NOT NULL,
    item VARCHAR(255) NOT NULL,
    FOREIGN KEY (questionId) REFERENCES solution_quiz_questions(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE company_quiz(
	id INT AUTO_INCREMENT PRIMARY KEY,
	compagnyid
	quizid
	Status
 FOREIGN KEY (questionId) REFERENCES solution_quiz_questions(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create company_quiz_response table
CREATE TABLE company_quiz_response (
	id INT AUTO_INCREMENT PRIMARY KEY,
    quizId INT NOT NULL,
    questionId INT NOT NULL,
	sectionId INT NOT NULL,
    itemId INT NOT NULL,
    response VARCHAR(255),
    type VARCHAR(50),
    companyId INT NOT NULL,
	FOREIGN KEY (quizId) REFERENCES solution_quiz(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (sectionId) REFERENCES solution_quiz_section(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (itemId) REFERENCES solution_quiz_question_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (questionId) REFERENCES solution_quiz_questions(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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(quiz_code) ON DELETE CASCADE		
);