use salarymarket;

CREATE TABLE solution_setting_country (
id INT AUTO_INCREMENT,
code TEXT NOT NULL PRIMARY KEY,
active boolean,
);

CREATE TABLE solution_setting_country_translate (
id  TEXT NOT NULL PRIMARY KEY,
code  TEXT, 
Libele TEXT,
languageCode INT NOT NULL,
FOREIGN KEY (code) REFERENCES solution_setting_country(code) ON DELETE CASCADE
);

--solution_setting_country 
(1, 'TN', NULL, 1),
(1, 'FR', NULL, 1),
--solution_setting_country_translate
('TN1' ,'TN', 'TUNISIE', 1),
('TN2' ,'TN', 'TUNISIA', 2),
('FR1' ,'FR', 'FRANCE', 1),
('FR2' ,'FR', 'FRANCE', 2),


SELECT p.code, t.Libele
FROM solution_setting_country p
JOIN solution_setting_country_translate t ON p.code = t.code
WHERE t.LanguageCode = 'fr';



CREATE TABLE solution_setting_town(
id INT AUTO_INCREMENT,
code TEXT NOT NULL PRIMARY KEY,
countryCode TEXT NOT NULL,
FOREIGN KEY (code) REFERENCES solution_setting_country(countryCode) ON DELETE CASCADE
);

CREATE TABLE solution_setting_town_translate (
id  TEXT NOT NULL PRIMARY KEY,
code  TEXT, 
Libele TEXT,
languageCode INT NOT NULL,
FOREIGN KEY (code) REFERENCES solution_setting_country(code) ON DELETE CASCADE
);


--solution_setting_country_town
(2, 'TUNIS', 'TN', 1),
(2, 'PARIS', 'FR', 1),

--solution_setting_country_town_translate
('TUNIS1' ,'TUNIS', 'TUNIS', 1),
('TUNIS2' ,'TUNIS', 'TUNIS', 2),
('PARIS1' ,'PARIS', 'PARIS', 1),
('PARIS2' ,'PARIS', 'PARIS', 2),

SELECT p.code, t.Libele
FROM solution_setting_town p
JOIN solution_setting_town_translate t ON p.code = t.code
WHERE t.LanguageCode = 'fr'
AND p.countrycode = 'TN';