use socle ; -- DROP TABLES DROP TABLE IF EXISTS solution_report_criterias_charts; DROP TABLE IF EXISTS solution_report_criterias_config; DROP TABLE IF EXISTS solution_report_charts; DROP TABLE IF EXISTS solution_report_criterias; DROP TABLE IF EXISTS company_reports_details; DROP TABLE IF EXISTS company_reports; DROP TABLE IF EXISTS solution_reports_config_gaps; -- =============================================== -- 1️ solution_reports_config_gaps -- =============================================== CREATE TABLE solution_reports_config_gaps ( subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, gap BOOLEAN NOT NULL, PRIMARY KEY (subscription_code, category), FOREIGN KEY (subscription_code) REFERENCES solution_subscription(subscription_code) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO solution_reports_config_gaps (subscription_code, category, gap) VALUES ('essential', 'market', false), ('advanced', 'market', false), ('advanced', 'personalized', true), ('advanced', 'matricule', true), ('on_the_menu', 'market', false), ('on_the_menu', 'personalized', true), ('on_the_menu', 'matricule', true); -- =============================================== -- 2️ :solution_report_criterias -- =============================================== CREATE TABLE solution_report_criterias ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, report_criterias VARCHAR(255) NOT NULL, createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (subscription_code, category) REFERENCES solution_reports_config_gaps(subscription_code, category) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY unique_report_per_subscription (subscription_code, category, report_criterias) ); INSERT INTO solution_report_criterias (subscription_code, category, report_criterias) VALUES -- Essential / market ('essential', 'market', 'essential-grade'), ('essential', 'market', 'essential-collaborative_path'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade'), -- On The Menu / market ('on_the_menu', 'market', 'on_the_menu-grade'), ('on_the_menu', 'market', 'on_the_menu-collaborative_path'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade'), -- On The Menu / matricule ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade'), -- On The Menu / personalized ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path'), ('on_the_menu', 'personalized', 'on_the_menu-collaborative_path'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade'), -- Advanced / market ('advanced', 'market', 'advanced-grade'), ('advanced', 'market', 'advanced-collaborative_path'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path-grade'), -- Advanced / matricule ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade'), -- Advanced / personalized ('advanced', 'personalized', 'advanced-function_code-subfunction_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path'), ('advanced', 'personalized', 'advanced-collaborative_path'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade'); -- =============================================== -- 3️: solution_report_criterias_config -- =============================================== CREATE TABLE solution_report_criterias_config ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, 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 (subscription_code, category, report_criterias) REFERENCES solution_report_criterias(subscription_code, category, report_criterias) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO solution_report_criterias_config (subscription_code, category, report_criterias, criteria) VALUES -- Essential / market ('essential', 'market', 'essential-grade', 'grade'), ('essential', 'market', 'essential-collaborative_path', 'collaborative_path'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path', 'function_code'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path', 'subfunction_code'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path', 'collaborative_path'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('essential', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- On The Menu / market ('on_the_menu', 'market', 'on_the_menu-grade', 'grade'), ('on_the_menu', 'market', 'on_the_menu-collaborative_path', 'collaborative_path'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'function_code'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'subfunction_code'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'collaborative_path'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('on_the_menu', 'market', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- On The Menu / matricule ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- On The Menu / personalized ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code', 'function_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code', 'subfunction_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'function_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'subfunction_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'collaborative_path'), ('on_the_menu', 'personalized', 'on_the_menu-collaborative_path', 'collaborative_path'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- Advanced / market ('advanced', 'market', 'advanced-grade', 'grade'), ('advanced', 'market', 'advanced-collaborative_path', 'collaborative_path'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path', 'function_code'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path', 'subfunction_code'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path', 'collaborative_path'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('advanced', 'market', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- Advanced / matricule ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'grade'), -- Advanced / personalized ('advanced', 'personalized', 'advanced-function_code-subfunction_code', 'function_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code', 'subfunction_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'function_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'subfunction_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'collaborative_path'), ('advanced', 'personalized', 'advanced-collaborative_path', 'collaborative_path'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'function_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'grade'); -- =============================================== -- 4 : solution_report_charts -- =============================================== CREATE TABLE solution_report_charts ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, report_chart VARCHAR(255) NOT NULL, createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (subscription_code, category) REFERENCES solution_reports_config_gaps(subscription_code, category) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO solution_report_charts (subscription_code, category, report_chart) VALUES -- Essential / market -- no graphs -- On The Menu -- no graphs for market category ('on_the_menu', 'matricule', 'gap_general'), ('on_the_menu', 'matricule', 'general_internal_gap_fixed'), ('on_the_menu', 'personalized', 'gap_general'), ('on_the_menu', 'personalized', 'general_internal_gap_fixed'), -- Advanced -- no graphs for market category ('advanced', 'matricule', 'gap_general'), ('advanced', 'matricule', 'general_internal_gap_fixed'), ('advanced', 'personalized', 'gap_general'), ('advanced', 'personalized', 'general_internal_gap_fixed'); -- =============================================== -- 5️: solution_report_criterias_charts -- =============================================== CREATE TABLE solution_report_criterias_charts ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, 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 (subscription_code, category, report_criterias) REFERENCES solution_report_criterias(subscription_code, category, report_criterias) ON DELETE CASCADE ON UPDATE CASCADE ); -- done INSERT INTO solution_report_criterias_charts (subscription_code, category, report_criterias, criteria) VALUES -- On The Menu / personalized ('on_the_menu', 'personalized', 'on_the_menu-collaborative_path', 'repartition_gap'), ('on_the_menu', 'personalized', 'on_the_menu-collaborative_path', 'repartition_data_bf'), ('on_the_menu', 'personalized', 'on_the_menu-collaborative_path', 'repartition_data_bt'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code', 'repartition_gap'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code', 'repartition_data_bf'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code', 'repartition_data_bt'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_gap'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'), ('on_the_menu', 'personalized', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt'), -- On The Menu / matricule ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'), ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'), ('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt'), -- Advanced / matricule ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','repartition_gap'), ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','repartition_data_bf'), ('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','repartition_data_bt'), -- Advanced / personalized ('advanced', 'personalized', 'advanced-collaborative_path', 'repartition_gap'), ('advanced', 'personalized', 'advanced-collaborative_path', 'repartition_data_bf'), ('advanced', 'personalized', 'advanced-collaborative_path', 'repartition_data_bt'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code', 'repartition_gap'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code', 'repartition_data_bf'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code', 'repartition_data_bt'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'repartition_gap'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'), ('advanced', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt'); -- =============================================== -- 6️: solution_report_calculation_columns + solution_report_display_columns -- =============================================== CREATE TABLE solution_report_calculation_columns ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, column_name VARCHAR(255) NOT NULL, createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (subscription_code, category) REFERENCES solution_reports_config_gaps(subscription_code, category) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY unique_calc_col (subscription_code, category, column_name) ); CREATE TABLE solution_report_display_columns ( id INT AUTO_INCREMENT PRIMARY KEY, subscription_code VARCHAR(255) NOT NULL, category ENUM('market', 'personalized', 'matricule') NOT NULL, column_name VARCHAR(255) NOT NULL, createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (subscription_code, category) REFERENCES solution_reports_config_gaps(subscription_code, category) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY unique_display_col (subscription_code, category, column_name) ); -- On The Menu / matricule INSERT INTO solution_report_calculation_columns (subscription_code, category, column_name) VALUES ('on_the_menu', 'matricule', 'matricule'); INSERT INTO solution_report_display_columns (subscription_code, category, column_name) VALUES ('on_the_menu', 'matricule', 'matricule'), ('on_the_menu', 'matricule', 'department'), ('on_the_menu', 'matricule', 'function'); -- Advanced / matricule INSERT INTO solution_report_calculation_columns (subscription_code, category, column_name) VALUES ('advanced', 'matricule', 'matricule'); INSERT INTO solution_report_display_columns (subscription_code, category, column_name) VALUES ('advanced', 'matricule', 'matricule'), ('advanced', 'matricule', 'department'), ('advanced', 'matricule', 'function'); -- =============================================== -- 7️: company_reports + company_reports_details -- =============================================== CREATE TABLE company_reports ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description TEXT, report_type VARCHAR(20) NULL, includeCompany BOOLEAN DEFAULT FALSE, category ENUM('market', 'personalized', 'matricule') NOT NULL, subscription_code VARCHAR(255) NOT 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, FOREIGN KEY (subscription_code, category) REFERENCES solution_reports_config_gaps(subscription_code, category) 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 );