-- add author
ALTER TABLE salarymarket.company_reports
ADD COLUMN createdBy INT  NULL,
ADD CONSTRAINT fk_createdBy
    FOREIGN KEY (createdBy) REFERENCES salarymarket.company_users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
    
-- createdFrom (espace client or admin)
ALTER TABLE company_reports
ADD COLUMN createdFrom ENUM('admin', 'client') 
DEFAULT 'admin' NOT NULL;

-- 1️ Add 'matricule'
use salarymarket;
ALTER TABLE solution_reports_config_gaps MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
ALTER TABLE solution_report_criterias MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
ALTER TABLE solution_report_criterias_config MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
ALTER TABLE solution_report_charts MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
ALTER TABLE solution_report_criterias_charts MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
ALTER TABLE company_reports MODIFY category ENUM('market', 'personalized', 'matricule') NOT NULL;
-- 2️ INSERTS WITH category = 'matricule'
-- solution_reports_config_gaps
INSERT INTO solution_reports_config_gaps (subscription_code, category, gap) VALUES 
('advanced', 'matricule', true),
('on_the_menu', 'matricule', true);
-- solution_report_criterias
INSERT INTO solution_report_criterias (subscription_code, category, report_criterias) VALUES
-- On The Menu / matricule
('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade'),
-- Advanced / matricule
('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade');
-- solution_report_criterias_config
INSERT INTO solution_report_criterias_config 
  (subscription_code, category, report_criterias, criteria) 
VALUES
-- 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'),
-- 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');
-- solution_report_charts
INSERT INTO solution_report_charts (subscription_code, category, report_chart) VALUES
-- On The Menu / matricule
('on_the_menu', 'matricule', 'gap_general'),
('on_the_menu', 'matricule', 'general_internal_gap_fixed'),
-- Advanced / matricule
('advanced', 'matricule', 'gap_general'),
('advanced', 'matricule', 'general_internal_gap_fixed');
-- solution_report_criterias_charts
INSERT INTO solution_report_criterias_charts 
(subscription_code, category, report_criterias, criteria) VALUES
-- 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');
-- ===============================================
-- 6️: solution_report_colunum_associe_condition + solution_report_display_columns
-- ===============================================
CREATE TABLE solution_report_colunum_associe_condition (
  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,
  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, report_criterias)
    REFERENCES solution_report_criterias_config(subscription_code, category, report_criterias)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
CREATE TABLE solution_report_colunum_other_display (
  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,
  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, report_criterias)
    REFERENCES solution_report_criterias_config(subscription_code, category, report_criterias)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
INSERT into  solution_report_colunum_associe_condition (subscription_code, category,report_criterias,column_name) VALUES
--  on_the_menu/advanced
('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade','matricule'),
('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','matricule');
INSERT INTO solution_report_colunum_other_display (subscription_code, category,report_criterias,column_name) VALUES
-- for department
('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade','department'),
-- for function
('on_the_menu', 'matricule', 'on_the_menu-function_code-subfunction_code-collaborative_path-grade',  'function');
INSERT INTO solution_report_colunum_other_display (subscription_code, category,report_criterias, column_name) VALUES
-- Advanced / matricule
('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','department'),
('advanced', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade','function');
-- add  
ALTER TABLE solution_report_criterias_config
ADD COLUMN best_matching BOOLEAN DEFAULT FALSE;
UPDATE solution_report_criterias_config
SET best_matching = TRUE
WHERE (subscription_code = 'advanced' AND category = 'matricule')
   OR (subscription_code = 'on_the_menu' AND category = 'matricule');