
-- 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) UNIQUE 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_criterias (subscription_code, category, report_criterias) VALUES
-- Essential
('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
('on_the_menu', 'market', 'essential-grade'),
('on_the_menu', 'market', 'essential-collaborative_path'),
('on_the_menu', 'market', 'essential-function_code-subfunction_code-collaborative_path'),
('on_the_menu', 'market', 'essential-function_code-subfunction_code-collaborative_path-grade'),


('on_the_menu', 'matricule', 'advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function'),

('on_the_menu', 'personalized', 'advanced-function_code-subfunction_code'),
('on_the_menu', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path'),
('on_the_menu', 'personalized', 'advanced-collaborative_path'),
('on_the_menu', 'personalized', 'advanced-function_code-subfunction_code-collaborative_path-grade'),

-- Advanced
('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-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function'),

('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,
  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 (report_criterias) REFERENCES solution_report_criterias(report_criterias)
    ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO solution_report_criterias_config (report_criterias, criteria) VALUES
-- Essential / market
('essential-grade', 'grade'),

('essential-collaborative_path', 'collaborative_path'),

('essential-function_code-subfunction_code-collaborative_path', 'function_code'),
('essential-function_code-subfunction_code-collaborative_path', 'subfunction_code'),
('essential-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('essential-function_code-subfunction_code-collaborative_path-grade', 'function_code'),
('essential-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'),
('essential-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'),
('essential-function_code-subfunction_code-collaborative_path-grade', 'grade'),

-- On The Menu / market
('on_the_menu-grade', 'grade'),

('on_the_menu-collaborative_path', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'function_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'function_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'grade'),

-- On The Menu / matricule

('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'function_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'grade'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'matricule'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'department'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'function'),

-- On The Menu / personalized
('on_the_menu-function_code-subfunction_code', 'function_code'),
('on_the_menu-function_code-subfunction_code', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'function_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('on_the_menu-collaborative_path', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'function_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'grade'),

-- Advanced / market
('advanced-essential-grade', 'grade'),
('advanced-collaborative_path', 'collaborative_path'),
('advanced-function_code-subfunction_code-collaborative_path', 'function_code'),
('advanced-function_code-subfunction_code-collaborative_path', 'subfunction_code'),
('advanced-function_code-subfunction_code-collaborative_path', 'collaborative_path'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'function_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'grade'),

-- Advanced / matricule
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'function_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'subfunction_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'collaborative_path'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'grade'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'matricule'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'department'),
('advanced-function_code-subfunction_code-collaborative_path-grade-matricule-departement-function', 'function'),

-- Advanced / personalized
('advanced-function_code-subfunction_code', 'function_code'),
('advanced-function_code-subfunction_code', 'subfunction_code'),

('advanced-function_code-subfunction_code-collaborative_path', 'function_code'),
('advanced-function_code-subfunction_code-collaborative_path', 'subfunction_code'),
('advanced-function_code-subfunction_code-collaborative_path', 'collaborative_path'),

('advanced-collaborative_path', 'collaborative_path'),

('advanced-function_code-subfunction_code-collaborative_path-grade', 'function_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'subfunction_code'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'collaborative_path'),
('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
('essential', 'market', 'gap_general'),
('essential', 'market', 'general_internal_gap_fixed'),

-- On The Menu
('on_the_menu', 'market', 'gap_general'),
('on_the_menu', 'market', 'general_internal_gap_fixed'),

('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
('advanced', 'market', 'gap_general'),
('advanced', 'market', 'general_internal_gap_fixed'),

('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,
  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 (report_criterias) REFERENCES solution_report_criterias(report_criterias)
    ON DELETE CASCADE ON UPDATE CASCADE
);
 -- in progres ( j'ai pas encore  fini !!!!!!)
INSERT INTO solution_report_criterias_charts (report_criterias, criteria) VALUES
-- On The Menu
('on_the_menu-function_code-subfunction_code', 'repartition_gap'),
('on_the_menu-function_code-subfunction_code', 'repartition_data_bf'),
('on_the_menu-function_code-subfunction_code', 'repartition_data_bt'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_gap'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'),
('on_the_menu-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'),
('on_the_menu-collaborative_path', 'repartition_gap'),
('on_the_menu-collaborative_path', 'repartition_data_bf'),
('on_the_menu-collaborative_path', 'repartition_data_bt'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'),
('on_the_menu-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt'),
-- Advanced
('advanced-function_code-subfunction_code', 'repartition_gap'),
('advanced-function_code-subfunction_code', 'repartition_data_bf'),
('advanced-function_code-subfunction_code', 'repartition_data_bt'),
('advanced-function_code-subfunction_code-collaborative_path', 'repartition_gap'),
('advanced-function_code-subfunction_code-collaborative_path', 'repartition_data_bf'),
('advanced-function_code-subfunction_code-collaborative_path', 'repartition_data_bt'),
('advanced-collaborative_path', 'repartition_gap'),
('advanced-collaborative_path', 'repartition_data_bf'),
('advanced-collaborative_path', 'repartition_data_bt'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_gap'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bf'),
('advanced-function_code-subfunction_code-collaborative_path-grade', 'repartition_data_bt');

-- ===============================================
-- 6️: 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
);
