use salarymarket;
CREATE TABLE solution_questionnaire (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description Text null, 
    countryCode VARCHAR(255) NOT NULL,                              
    `order` INT NOT NULL,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (countryCode) REFERENCES solution_setting_country(countryCode) ON DELETE CASCADE
);
INSERT INTO solution_questionnaire (id, name, description, countryCode, `order`)
VALUES 
    (1, 'Questionnaire 1 -Les Politiques et pratiques de Rémunération','Exploration des politiques et pratiques de rémunération au sein de l\'organisation' , 'TN', 1),
    (2, 'Questionnaire 2 -Led Pratiques de Révision Salariale','Analyse des pratiques de révision salariale et leur impact sur les employés', 'TN', 2),
    (3, 'Questionnaire 3 -Attraction, Rétention et fin de contrat','Évaluation des stratégies d\'attraction et de rétention des talents, ainsi que des processus de fin de contrat', 'TN', 3),
    (4, 'Questionnaire 4 -Les Véhicules de Société','Examen des politiques concernant l\'utilisation des véhicules de société', 'TN', 4);

CREATE TABLE solution_setting_country (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
active boolean,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE solution_setting_country_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
countryCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_business_sector (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE solution_setting_business_sector_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
businessSectorCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_sales_turnover (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 
CREATE TABLE solution_setting_sales_turnover_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
salesTurnoverCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_company_size (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE solution_setting_company_size_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
companySizeCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_geographic_extent (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE solution_setting_geographic_extent_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
geographicExtentCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_value_chain (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE solution_setting_value_chain_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
valueChainCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_town(
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
countryCode VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE 
);
CREATE TABLE solution_setting_town_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
townCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (townCode) REFERENCES solution_setting_town(code) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE solution_setting_sub_sector (
id INT AUTO_INCREMENT UNIQUE,
code VARCHAR(255) NOT NULL PRIMARY KEY,
businessSectorCode VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE solution_setting_sub_sector_translate (
id  INT AUTO_INCREMENT PRIMARY KEY,
Label TEXT,
subSectorCode VARCHAR(255) NOT NULL,
languageId INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (subSectorCode) REFERENCES solution_setting_sub_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE company
ADD COLUMN companySizeCode VARCHAR(255),
ADD COLUMN valueChainCode VARCHAR(255),
ADD COLUMN businessSectorCode VARCHAR(255),
ADD COLUMN geographicExtentCode VARCHAR(255),
ADD COLUMN salesTurnoverCode VARCHAR(255),
ADD COLUMN countryCode VARCHAR(255),
ADD FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO solution_setting_country (id, code, active) VALUES
(1, 'TN', 1),
(2, 'FR', 1);
INSERT INTO solution_setting_country_translate (id, label, countryCode, languageId) VALUES
(1, 'Tunisia', 'TN', 1),   -- Traduction en anglais pour le pays TN
(2, 'Tunisie', 'TN', 2),   -- Traduction en français pour le pays TN
(3, 'France', 'FR', 1),    -- Traduction en anglais pour le pays FR
(4, 'France', 'FR', 2);    -- Traduction en français pour le pays FR

INSERT INTO solution_setting_business_sector (id, code) VALUES
(1, 'BS001'),
(2, 'BS002'),
(3, 'BS003'),
(4, 'BS004');

INSERT INTO solution_setting_business_sector_translate (id, label, businessSectorCode, languageId) VALUES
(1, 'IT', 'BS001', 1),    
(2, 'Informatique', 'BS001', 2),   
(3, 'Automobile', 'BS002', 1),    
(4, 'Automobile', 'BS002', 2), 
(5, 'Pharmaceutical industry', 'BS003', 1),     
(6, 'Industrie pharmaceutique', 'BS003', 2),
(7, 'Agrifood', 'BS004', 1),   
(8, 'Agroalimentaire', 'BS004', 2);  

INSERT INTO solution_setting_sales_turnover (id, code) VALUES
(1, 'CA001'),
(2, 'CA002'),
(3, 'CA003'),
(4, 'CA004'),
(5, 'CA005');

INSERT INTO solution_setting_sales_turnover_translate (id, label, salesTurnoverCode, languageId) VALUES
(1, '1 - 10000', 'CA001', 1),    
(2, '1 - 10000', 'CA001', 2),   
(3, '10000 - 100000', 'CA002', 1),    
(4, '10000 - 100000', 'CA002', 2), 
(5, '100000 - 500000', 'CA003', 1),     
(6, '100000 - 500000', 'CA003', 2),
(7, '500000 - 1000000', 'CA004', 1),   
(8, '500000 - 1000000', 'CA004', 2), 
(9, 'Over 100,000', 'CA005', 1),   
(10, 'Plus de 100000', 'CA005', 2);  

INSERT INTO solution_setting_company_size (id, code) VALUES
(1, 'CS001'),
(2, 'CS002'),
(3, 'CS003'),
(4, 'CS004'),
(5, 'CS005'),
(6, 'CS006'),
(7, 'CS007'),
(8, 'CS008'),
(9, 'CS009'),
(10, 'CS0010');

INSERT INTO solution_setting_company_size_translate (id, label, companySizeCode, languageId) VALUES
(1, 'Less than 10', 'CS001', 1),    
(2, 'Moins de 10', 'CS001', 2),   
(3, 'Between 11 and 20', 'CS002', 1),    
(4, 'Entre 11 et 20', 'CS002', 2), 
(5, 'Between 21 and 30', 'CS003', 1),     
(6, 'Entre 21 et 30', 'CS003', 2),
(7, 'Between 31 and 50', 'CS004', 1),   
(8, 'Entre 31 et 50', 'CS004', 2), 
(9, 'Between 51 and 100', 'CS005', 1),   
(10, 'Entre 51 et 100', 'CS005', 2),  
(11, 'Between 101 and 300', 'CS006', 1),    
(12, 'Entre 101 et 300', 'CS006', 2),   
(13, 'Between 301 and 500', 'CS007', 1),    
(14, 'Entre 301 et 500', 'CS007', 2), 
(15, 'Between 501 and 1000', 'CS008', 1),     
(16, 'Entre 501 et 1000', 'CS008', 2),
(17, 'Between 1001 and 2000', 'CS009', 1),   
(18, 'Entre 1001 et 2000', 'CS009', 2), 
(19, 'Over 2001', 'CS0010', 1),   
(20, 'Plus de 2001', 'CS0010', 2); 

INSERT INTO solution_setting_value_chain (id, code) VALUES
(1, 'VC001'),
(2, 'VC002');

INSERT INTO solution_setting_value_chain_translate (id, label, valueChainCode, languageId) VALUES
(1, 'Full control of the entire value chain', 'VC001', 1),    
(2, 'Contrôle total de toute la chaine de valeur', 'VC001', 2),   
(3, 'Partial control of the value chain', 'VC002', 1),    
(4, 'Contrôle partiel de la chaine de valeur', 'VC002', 2);

INSERT INTO solution_setting_geographic_extent (id, code) VALUES
(1, 'GE001'),
(2, 'GE002'),
(3, 'GE003'),
(4, 'GE004');

INSERT INTO solution_setting_geographic_extent_translate (id, label, geographicExtentCode, languageId) VALUES
(1, 'Present in one country', 'GE001', 1),    
(2, 'Présent sur un seul pays', 'GE001', 2),   
(3, 'Present in several countries of the same region', 'GE002', 1),    
(4, 'Présent sur plusieurs pays d’une même région', 'GE002', 2), 
(5, 'Present in several countries on two different continents', 'GE003', 1),     
(6, 'Présent sur plusieurs pays de deux continents différents', 'GE003', 2),
(7, 'Present on more than two continents', 'GE004', 1),   
(8, 'Présent sur plus de deux continents', 'GE004', 2);