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