Salary Market

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