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 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_group (

id INT AUTO_INCREMENT UNIQUE,

report_code VARCHAR(255) NOT NULL,

subscription VARCHAR(255) NOT NULL,   

report_groupe 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_group (report_code, subscription, report_groupe) 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_group_config (

id INT AUTO_INCREMENT PRIMARY KEY,

report_groupe 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_groupe) REFERENCES solution_report_group(report_groupe) ON DELETE CASCADE ON UPDATE CASCADE

);

INSERT INTO solution_report_group_config (report_groupe, 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')