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')