use salarymarket;

ALTER TABLE salarymarket.company_reports
ADD COLUMN includeCompany boolean;
ALTER TABLE salarymarket.company_reports
ADD COLUMN category ENUM('market', 'personalized', 'matricule') NOT NULL;

SET SQL_SAFE_UPDATES = 0;

DELETE FROM salarymarket.solution_compensation_informations
WHERE column_code IN (
  'amortissement_voiture',
  'frais_annuel_amortissement_avantage_voiture',
  'car_depreciation',
  'annual_cost_car_depreciation'
);

SET SQL_SAFE_UPDATES = 1;
-- fix BS04 translate 
UPDATE salarymarket.solution_setting_business_sector_translate
SET languageId = 2
WHERE label = 'Marché industriel' 
AND businessSectorCode = 'BS04';

use salarymarket ;
-- 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;

-- 1️ solution_reports_config_gaps
CREATE TABLE solution_reports_config_gaps (
  subscription_code VARCHAR(255) NOT NULL,
  category ENUM('market', 'personalized') 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),
('on_the_menu', 'market', false),
('on_the_menu', 'personalized', 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') 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 / 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 / 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') 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 / 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 / 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') 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
-- On The Menu / personalized
('on_the_menu', 'personalized', 'gap_general'),
('on_the_menu', 'personalized', 'general_internal_gap_fixed'),

-- Advanced / personalized
('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') 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_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'),

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