Salary Market

use socle;

CREATE TABLE company_reports (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

description TEXT,

report_type VARCHAR(20) 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 TABLE company_reports_details (

id INT AUTO_INCREMENT PRIMARY KEY,

reportId INT NOT NULL,

request_table JSON NULL, 

request_chart JSON NULL,     

response_table JSON NULL, 

response_chart JSON NULL,   

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (reportId) REFERENCES company_reports(id) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_report_subscription (

id INT AUTO_INCREMENT UNIQUE,

report_code VARCHAR(255) PRIMARY KEY,

subscription_code VARCHAR(255),   

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (subscription_code) REFERENCES solution_subscription(subscription_code) ON DELETE CASCADE ON UPDATE CASCADE

);

INSERT INTO solution_report_subscription (report_code, subscription_code) VALUES

-- Compensation

('report_essential_1', 'essential'),

('report_on_the_menu_1', 'on_the_menu'),

('report_advanced_1', 'advanced');

CREATE TABLE solution_report_criterias (

id INT AUTO_INCREMENT UNIQUE,

report_code VARCHAR(255) NOT NULL,

subscription VARCHAR(255) NOT NULL,   

report_criterias VARCHAR(255) PRIMARY KEY,   

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (report_code) REFERENCES solution_report_subscription(report_code) ON DELETE CASCADE ON UPDATE CASCADE

);

INSERT INTO solution_report_criterias (report_code, subscription, report_criterias) VALUES

-- Essential

('report_essential_1', 'essential', 'essential_1-grade'),

('report_essential_1', 'essential', 'essential_1-collaborative_path'),

('report_essential_1', 'essential', 'essential_1-function_code-subfunction_code-collaborative_path'),

('report_essential_1', 'essential', 'essential_1-function_code-subfunction_code-collaborative_path_grade'),

-- On The Menu

('report_on_the_menu_1', 'on_the_menu', 'on_the_menu_1-function_code-subfunction_code'),   

('report_on_the_menu_1', 'on_the_menu', 'on_the_menu_1-function_code-subfunction_code-collaborative_path'),

('report_on_the_menu_1', 'on_the_menu', 'on_the_menu_1-collaborative_path'),

('report_on_the_menu_1' , 'on_the_menu',   'on_the_menu_1-function_code-subfunction_code-collaborative_path-grade'),

-- Advanced

('report_advanced_1', 'advanced', 'advanced_1-function_code-subfunction_code'),   

('report_advanced_1', 'advanced', 'advanced_1-function_code-subfunction_code-collaborative_path'),

('report_advanced_1', 'advanced', 'advanced_1-collaborative_path'),

('report_advanced_1' , 'advanced',   'advanced_1-function_code-subfunction_code-collaborative_path-grade');

CREATE TABLE solution_report_criterias_config (

id INT AUTO_INCREMENT PRIMARY KEY,

report_criterias VARCHAR(255) NOT NULL,   

criteria VARCHAR(255) NOT NULL,   

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (report_criterias) REFERENCES solution_report_criterias(report_criterias) ON DELETE CASCADE ON UPDATE CASCADE

);

INSERT INTO solution_report_criterias_config (report_criterias, criteria) VALUES

-- Essential

('essential_1-grade', 'grade'),

('essential_1-collaborative_path', 'collaborative_path'),

('essential_1-function_code-subfunction_code-collaborative_path', 'function_code'),

('essential_1-function_code-subfunction_code-collaborative_path', 'subfunction_code'),

('essential_1-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('essential_1-function_code-subfunction_code-collaborative_path_grade', 'function_code'),

('essential_1-function_code-subfunction_code-collaborative_path_grade', 'subfunction_code'),

('essential_1-function_code-subfunction_code-collaborative_path_grade', 'collaborative_path'),

('essential_1-function_code-subfunction_code-collaborative_path_grade', 'grade'),

-- On The Menu

('on_the_menu_1-function_code-subfunction_code', 'function_code'),   

('on_the_menu_1-function_code-subfunction_code', 'subfunction_code'),   

('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'function_code'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'subfunction_code'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('on_the_menu_1-collaborative_path', 'collaborative_path'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade' , 'function_code'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade' , 'subfunction_code'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade' , 'collaborative_path'),

('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade' , 'grade'),

-- Advanced

('advanced_1-function_code-subfunction_code', 'function_code'),   

('advanced_1-function_code-subfunction_code', 'subfunction_code'),   

('advanced_1-function_code-subfunction_code-collaborative_path', 'function_code'),

('advanced_1-function_code-subfunction_code-collaborative_path', 'subfunction_code'),

('advanced_1-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('advanced_1-collaborative_path', 'collaborative_path'),

('advanced_1-function_code-subfunction_code-collaborative_path-grade' , 'function_code'),

('advanced_1-function_code-subfunction_code-collaborative_path-grade' , 'subfunction_code'),

('advanced_1-function_code-subfunction_code-collaborative_path-grade' , 'collaborative_path'),

('advanced_1-function_code-subfunction_code-collaborative_path-grade' , 'grade');

CREATE TABLE  solution_report_charts (

id INT AUTO_INCREMENT PRIMARY KEY,

report_code VARCHAR(255) NOT NULL,   

chart_code VARCHAR(255) NOT NULL,  

subscription_code VARCHAR(255) NOT NULL,  

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (report_code) REFERENCES solution_report_subscription(report_code) ON DELETE CASCADE ON UPDATE CASCADE

);

INSERT INTO solution_report_charts (report_code, chart_code, subscription_code) VALUES

('report_essential_1', 'gap_general', 'essential'),

('report_on_the_menu_1', 'gap_general', 'on_the_menu'),

('report_on_the_menu_1', 'repartition_gap', 'on_the_menu'),

('report_on_the_menu_1', 'repartition_data_bf', 'on_the_menu'),

('report_on_the_menu_1', 'repartition_data_bt', 'on_the_menu'),

('report_on_the_menu_1', 'general_internal_gap_fixed', 'on_the_menu'),

('report_advanced_1', 'gap_general', 'advanced'),

('report_advanced_1', 'repartition_gap', 'advanced'),

('report_advanced_1', 'repartition_data_bf', 'advanced'),

('report_advanced_1', 'repartition_data_bt', 'advanced'),

('report_advanced_1', 'general_internal_gap_fixed', 'advanced');