use socle_3;
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 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
('essential_1', 'essential'),
('on_the_menu_1', 'on_the_menu'),
('advanced_1', 'advanced');

CREATE TABLE solution_report_criterias (
id INT AUTO_INCREMENT UNIQUE,
report_code 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, report_criterias) VALUES
-- Essential
('essential_1', 'essential_1-grade'),
('essential_1', 'essential_1-collaborative_path'),
('essential_1', 'essential_1-function_code-subfunction_code-collaborative_path'),
('essential_1', 'essential_1-function_code-subfunction_code-collaborative_path_grade'),

-- On The Menu
('on_the_menu_1', 'on_the_menu_1-function_code-subfunction_code'),   
('on_the_menu_1', 'on_the_menu_1-function_code-subfunction_code-collaborative_path'),
('on_the_menu_1', 'on_the_menu_1-collaborative_path'),
('on_the_menu_1', 'on_the_menu_1-function_code-subfunction_code-collaborative_path-grade'),

-- Advanced
('advanced_1', 'advanced_1-function_code-subfunction_code'),   
('advanced_1', 'advanced_1-function_code-subfunction_code-collaborative_path'),
('advanced_1', 'advanced_1-collaborative_path'),
('advanced_1', '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,   
report_chart 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, report_chart) VALUES
('essential_1', 'gap_general'),
('essential_1', 'general_internal_gap_fixed'),

('on_the_menu_1', 'gap_general'),
('on_the_menu_1', 'general_internal_gap_fixed'),

('advanced_1', 'gap_general'),
('advanced_1', 'general_internal_gap_fixed');

CREATE TABLE solution_report_criterias_charts (
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_charts (report_criterias, criteria) VALUES
-- On The Menu
('on_the_menu_1-function_code-subfunction_code', 'repartition_gap'),
('on_the_menu_1-function_code-subfunction_code', 'repartition_data_bf'),
('on_the_menu_1-function_code-subfunction_code', 'repartition_data_bt'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'repartition_gap'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'),
('on_the_menu_1-collaborative_path', 'repartition_gap'),
('on_the_menu_1-collaborative_path', 'repartition_data_bf'),
('on_the_menu_1-collaborative_path', 'repartition_data_bt'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'),
('on_the_menu_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt'),

-- Advanced
('advanced_1-function_code-subfunction_code', 'repartition_gap'),
('advanced_1-function_code-subfunction_code', 'repartition_data_bf'),
('advanced_1-function_code-subfunction_code', 'repartition_data_bt'),
('advanced_1-function_code-subfunction_code-collaborative_path', 'repartition_gap'),
('advanced_1-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'),
('advanced_1-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'),
('advanced_1-collaborative_path', 'repartition_gap'),
('advanced_1-collaborative_path', 'repartition_data_bf'),
('advanced_1-collaborative_path', 'repartition_data_bt'),
('advanced_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'),
('advanced_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'),
('advanced_1-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt');