use salarymarket;
-- Compensation
-- Create company_compensation table
CREATE TABLE company_compensation (
	id INT AUTO_INCREMENT PRIMARY KEY,
	city Text NULL,
	site varchar(1) NULL,
	matricule varchar(255) NULL,
	department varchar(255) NULL,
	`function` varchar(255) NULL,
	internal_grade varchar(255) NULL,
	gender varchar(255) NULL,
	birthday Text NULL,
	hiring_date Text NULL,
	function_code varchar(255) NULL, 
	subfunction_code varchar(255) NULL,-- changed instead of discipline_code 
	collaborative_path varchar(255) NULL,-- changed instead of level_career_path  
	grade varchar(255) NULL, -- changed instead of job_grade
	number_employees_supervised  INT NULL ,
	devise VARCHAR(10) NULL,
	gross_annual_salary  float NULL,
	number_monthly_salary INT(5) NULL ,
	type_overtime_pay  varchar(255) NULL,
	meal_allowance float NULL,
	location_allowance float NULL,
	transportation_allowance INT NULL ,
	other_bonuses_awarded_last_year INT NULL, -- changed instead of other_bonuses_awarded
	fixed_allowances float NULL ,
	eligibility_performance_bonus float NULL,
	target_performance_bonus varchar(255) NULL ,
	total_amount_performance_bonus_awarded INT NULL,
	eligibility_commissions varchar(255) NULL,
	target_variable_sales_compensation varchar(255) NULL ,
	total_amount_sales_bonus_awarded  INT NULL,
	total_amount_sales_commissions_awarded INT NULL,
	car_benefit_eligibility varchar(255) NULL ,
	car_depreciation  varchar(255) Null,
	annual_cost_car_depreciation INT NULL,
	schooling_allowance float NULL, -- new
	seniority_bonus float NULL, -- new
	companyId INT NULL,
	FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE
);

---- Report
-- Create company_reports_files table
CREATE TABLE company_reports (
	id INT AUTO_INCREMENT PRIMARY KEY,
	label VARCHAR(255) NOT NULL,
	date DATETIME NULL,
	description TEXT,
	file VARCHAR(255) NULL,
	companyId INT NOT NULL,
	createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- Create company_reports_files table
CREATE TABLE IF NOT EXISTS company_reports_files (
	report_id INT NOT NULL,
    files_id INT NOT NULL,
    PRIMARY KEY (report_id, files_id),
    FOREIGN KEY (report_id) REFERENCES company_reports(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (files_id) REFERENCES company_files(id) ON DELETE CASCADE ON UPDATE CASCADE
);
---- Questionnaire
-- Create solution_questionnaire table
CREATE TABLE solution_questionnaire (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description Text null, 
    countryId INT NOT NULL,                              
    `order` INT NOT NULL,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (countryId) REFERENCES solution_setting_item(id) ON DELETE CASCADE
);
-- Create solution_questionnaire_section table
CREATE TABLE solution_questionnaire_section (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    questionnaireId INT NOT NULL,
	`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,
    `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
);