use salarymarket;
CREATE TABLE solution_questionnaire (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description Text null, 
    countryCode VARCHAR(255) NOT NULL, 
    quiz_image  VARCHAR(255) NULL,
    questionnaire_order INT NOT NULL,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE
);
-- Create solution_questionnaire_section table
CREATE TABLE solution_questionnaire_section (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    questionnaireId INT NOT NULL,
	section_order INT NOT NULL,
    FOREIGN KEY (questionnaireId) REFERENCES solution_questionnaire(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create solution_questionnaire_questions table
CREATE TABLE solution_questionnaire_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question VARCHAR(255) NOT NULL,
    sectionId INT NOT NULL,
    questionnaireId 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_questionnaire_section(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (questionnaireId) REFERENCES solution_questionnaire(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create solution_questionnaire_question_items table
CREATE TABLE solution_questionnaire_question_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(255) NOT NULL,
    questionId INT NOT NULL,
    itemParentId INT NULL,
    isOther BOOLEAN DEFAULT 0,
    FOREIGN KEY (questionId) REFERENCES solution_questionnaire_questions(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_questionnaire_response table
CREATE TABLE company_questionnaire_response (
	id INT AUTO_INCREMENT PRIMARY KEY,
    questionnaireId 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 (questionnaireId) REFERENCES solution_questionnaire(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (sectionId) REFERENCES solution_questionnaire_section(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (itemId) REFERENCES solution_questionnaire_question_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (questionId) REFERENCES solution_questionnaire_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
);