Salary Market

use salarymarket;

-- Insert into solution_quiz for Quiz 2

INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime) 

VALUES 

(2,'quiz_2_The_Practices_of_Review_Salary',

 'Questionnaire 2 -Les Pratiques de Révision Salariale.jpg',

  12);

-- Insert into solution_quiz_translate for Quiz 2

INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId) 

VALUES 

('quiz_2_The_Practices_of_Review_Salary', 

 'Questionnaire 2 -Les Pratiques de Révision Salariale',

 'Ce questionnaire examine comment les différentes pratiques d’examen des 

 salaires influent sur la motivation, la satisfaction et le maintien 

 en poste des employés. Il couvre des sujets clés comme l’équité, la transparence,

 la rémunération fondée sur le rendement et

 l’incidence globale des décisions en matière de rémunération sur le moral et l’engagement au travail.', 

 'Analyse des pratiques de révision salariale et leur impact sur les employés', 2),

 ('quiz_2_The_Practices_of_Review_Salary', 

 'Quiz 2 - Salary Review Salary',

 'This quiz examines how different salary review practices affect employee motivation, satisfaction,

 and retention. It covers key topics like fairness, transparency,

 performance-based pay, and the overall impact of compensation decisions on workplace morale and engagement.', 

 'Analysis of salary review practices and their impact on employees', 1);

-- Insert into solution_quiz_section for Quiz 2

INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order) 

VALUES

('Salary_Revision_Policy', 'quiz_2_The_Practices_of_Review_Salary', 1),

('Salary_Revision_Criteria', 'quiz_2_The_Practices_of_Review_Salary', 2),

('Salary_adjustments_and_increases', 'quiz_2_The_Practices_of_Review_Salary', 3);

-- en translate & fr 

-- Insert English translations

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId) VALUES

('Salary_Revision_Policy', 'Salary Revision Policy', 1),

('Salary_Revision_Criteria', 'Salary Revision Criteria', 1),

('Salary_adjustments_and_increases', 'Salary Adjustments and Increases', 1);

-- Insert French translations 

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId) VALUES

('Salary_Revision_Policy', 'Politique de révision des salaires', 2),

('Salary_Revision_Criteria', 'Critères de révision des salaires', 2),

('Salary_adjustments_and_increases', 'Ajustements et augmentations de salaire', 2);

-- Section "Politique de Révision Salariale" (questions 1–4)

INSERT INTO solution_quiz_section_questions 

(questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)

VALUES

('Q2-PRS-1', 'Salary_Revision_Policy', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CU', 1, 'Pie'), -- L'entreprise dispose-t-elle d'une politique formelle de révision salariale ?

('Q2-PRS-2', 'Salary_Revision_Policy', 'quiz_2_The_Practices_of_Review_Salary', 'Q2-PRS-1', 'CU', 2, 'Pie'), -- Si oui, depuis combien de temps cette politique est-elle en place ?

('Q2-PRS-3', 'Salary_Revision_Policy', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CM', 3, 'Bar'), -- À quelle fréquence les révisions salariales sont-elles effectuées ?

('Q2-PRS-4', 'Salary_Revision_Policy', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CM', 4, 'Bar'); -- Quels sont les principaux objectifs de votre politique de révision salariale ?

-- Section "Critères de Révision Salariale" (question 5)

INSERT INTO solution_quiz_section_questions 

(questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)

VALUES

('Q2-SRS-1', 'Salary_Revision_Criteria', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CM', 5, 'Bar'); -- Quels sont les principaux critères utilisés pour les révisions salariales ?

-- Section "Ajustements et Augmentations Salariales" (questions 6–9)

INSERT INTO solution_quiz_section_questions 

(questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)

VALUES

('Q2-AAS-1', 'Salary_adjustments_and_increases', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CM', 6, 'Bar'), -- Quels types d'ajustements salariaux sont pratiqués dans votre entreprise ?

('Q2-AAS-2', 'Salary_adjustments_and_increases', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CU', 7, 'Pie'), -- Quel est le pourcentage moyen d'augmentation salariale pour la dernière année ?

('Q2-AAS-3', 'Salary_adjustments_and_increases', 'quiz_2_The_Practices_of_Review_Salary', NULL, 'CU', 8, 'Pie'), -- Quel est le pourcentage moyen d'augmentation salariale sur trois ans ?

('Q2-AAS-4', 'Salary_adjustments_and_increases', 'quiz_2_The_Practices_of_Review_Salary',NULL, 'CU', 9, 'Pie'); -- Quelle part du budget annuel est dédiée aux augmentations salariales ?

-- Translations for Section "Politique de Révision Salariale" (French)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-PRS-1', 'L\'entreprise dispose-t-elle d\'une politique formelle de révision salariale ?', 2),

('Q2-PRS-2', 'Si oui, depuis combien de temps cette politique est-elle en place ?', 2),

('Q2-PRS-3', 'À quelle fréquence les révisions salariales sont-elles effectuées ?', 2),

('Q2-PRS-4', 'Quels sont les principaux objectifs de votre politique de révision salariale ?', 2);

-- Translations for Section "Critères de Révision Salariale" (French)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-SRS-1', 'Quels sont les principaux critères utilisés pour les révisions salariales ?', 2);

-- Translations for Section "Ajustements et Augmentations Salariales" (French)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-AAS-1', 'Quels types d\'ajustements salariaux sont pratiqués dans votre entreprise ?', 2),

('Q2-AAS-2', 'Quel est le pourcentage moyen d\'augmentation salariale accordé par votre entreprise pour la dernière année ?', 2),

('Q2-AAS-3', 'Quel est le pourcentage moyen d\'augmentation salariale accordé par votre entreprise au cours des trois dernières années ?', 2),

('Q2-AAS-4', 'Quelle part du budget annuel est dédiée aux augmentations salariales ?', 2);

-- Translations for Section "Salary Revision Policy" (English)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-PRS-1', 'Does the company have a formal salary revision policy?', 1),

('Q2-PRS-2', 'If yes, how long has this policy been in place?', 1),

('Q2-PRS-3', 'How frequently are salary revisions conducted?', 1),

('Q2-PRS-4', 'What are the main objectives of your salary revision policy?', 1);

-- Translations for Section "Salary Revision Criteria" (English)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-SRS-1', 'What are the main criteria used for salary revisions?', 1);

-- Translations for Section "Salary Adjustments and Increases" (English)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

('Q2-AAS-1', 'What types of salary adjustments are practiced in your company?', 1),

('Q2-AAS-2', 'What is the average percentage of salary increase granted by your company in the last year?', 1),

('Q2-AAS-3', 'What is the average percentage of salary increase granted by your company over the past three years?', 1),

('Q2-AAS-4', 'What portion of the annual budget is dedicated to salary increases?', 1);

-- Insert items for the second quiz

INSERT INTO solution_reponses_items (itemCode, itemName)

VALUES

  ('less-than-1-year', 'Less than 1 year'),

  ('1-3-years', '1-3 years'),

  ('3-5-years', '3-5 years'),

  ('more-than-5-years', 'More than 5 years'),

  ('semi-annually', 'semi-annually'),

  ('quarterly', 'quarterly'),

  ('employee-motivation-retention', 'Employee motivation and retention'),

  ('alignment-of-wages', 'Alignment of wages to the market'),

  ('reward-for-performance', 'Reward for performance'),

  ('internal-equity', 'internal equity'),

  ('seniority', 'Seniority'),

  ('specific-skills', 'Specific skills'),

  ('market-salary-comparison', 'Comparison with market salaries'),

  ('achieving-personal-goals', 'Achieving personal goals'),

  ('merit-increases', 'Merit increases'),

  ('cost-of-living-adjustments', 'Cost of living adjustments'),

  ('promotion-increases', 'Increases for promotions'),

  ('one-time-bonuses', 'One-time bonuses'),

  ('market-comparisons', 'Adjustments based on market comparisons'),

  ('less-than-3-percent', 'Less than 3%'),

  ('between-3-and-5-percent', 'Between 3% and 5%'),

  ('between-5-and-7-percent', 'Between 5% and 7%'),

  ('more-than-7-percent', 'More than 7%'),

  ('less-than-1-percent', 'Less than 1%'),

  ('between-1-and-3-percent', 'Between 1% and 3%'),

  ('more-than-5-percent', 'More than 5%');

-- linking 

INSERT  INTO solution_quiz_section_questions_items

  (itemCode, questionCode, itemParentId, isOther)

VALUES

  -- Question 1 (Q2-PRS-1)

  ('yes', 'Q2-PRS-1', NULL, 0),

  ('no',  'Q2-PRS-1', NULL, 0),

  -- Question 2 (Q2-PRS-2)

  ('less-than-1-year',   'Q2-PRS-2', 'yes', 0),

  ('1-3-years',   'Q2-PRS-2', 'yes', 0),

  ('3-5-years',  'Q2-PRS-2', 'yes', 0),

  ('more-than-5-years',  'Q2-PRS-2', 'yes', 0),

  -- Question 3 (Q2-PRS-3)

  ('annually',   'Q2-PRS-3', NULL, 0),

  ('semi-annually','Q2-PRS-3', NULL, 0),

  ('quarterly', 'Q2-PRS-3', NULL, 0),

  ('other-please-specify', 'Q2-PRS-3', NULL, 1),

  -- Question 4 (Q2-PRS-4)

  ('employee-motivation-retention','Q2-PRS-4', NULL, 0),

  ('alignment-of-wages',            'Q2-PRS-4', NULL, 0),

  ('reward-for-performance',        'Q2-PRS-4', NULL, 0),

  ('internal-equity',               'Q2-PRS-4', NULL, 0),

  ('other-please-specify',          'Q2-PRS-4', NULL, 1),

  -- Question 5 (Q2-SRS-1)

  ('individual-performance','Q2-SRS-1', NULL, 0),

  ('team-performance',      'Q2-SRS-1', NULL, 0),

  ('company-performance',   'Q2-SRS-1', NULL, 0),

  ('seniority',             'Q2-SRS-1', NULL, 0),

  ('specific-skills',       'Q2-SRS-1', NULL, 0),

  ('market-salary-comparison','Q2-SRS-1',NULL, 0),

  ('achieving-personal-goals','Q2-SRS-1',NULL, 0),

  ('other-please-specify',  'Q2-SRS-1', NULL, 1),

  -- Question 6 (Q2-AAS-1)

  ('merit-increases',             'Q2-AAS-1', NULL, 0),

  ('cost-of-living-adjustments',  'Q2-AAS-1', NULL, 0),

  ('promotion-increases',         'Q2-AAS-1', NULL, 0),

  ('one-time-bonuses',            'Q2-AAS-1', NULL, 0),

  ('market-comparisons',          'Q2-AAS-1', NULL, 0),

  ('other-please-specify',        'Q2-AAS-1', NULL, 1),

  -- Question 7 (Q2-AAS-2)

  ('less-than-3-percent',      'Q2-AAS-2', NULL, 0),

  ('between-3-and-5-percent',  'Q2-AAS-2', NULL, 0),

  ('between-5-and-7-percent',  'Q2-AAS-2', NULL, 0),

  ('more-than-7-percent',      'Q2-AAS-2', NULL, 0),

  -- Question 8 (Q2-AAS-3)

  ('less-than-3-percent',      'Q2-AAS-3', NULL, 0),

  ('between-3-and-5-percent',  'Q2-AAS-3', NULL, 0),

  ('between-5-and-7-percent',  'Q2-AAS-3', NULL, 0),

  ('more-than-7-percent',      'Q2-AAS-3', NULL, 0),

  -- Question 9 (Q2-AAS-4)

  ('less-than-1-percent',      'Q2-AAS-4', NULL, 0),

  ('between-1-and-3-percent',  'Q2-AAS-4', NULL, 0),

  ('between-3-and-5-percent',  'Q2-AAS-4', NULL, 0),

  ('more-than-5-percent',      'Q2-AAS-4', NULL, 0);

-- French translations (languageId = 2)

INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)

VALUES

  ('less-than-1-year',  'Moins d''un an',  2),

  ('1-3-years',  '1-3 ans',   2),

  ('3-5-years', '3-5 ans',  2),

  ('more-than-5-years','Plus de 5 ans', 2),

  ('semi-annually', 'Semestriellement',  2),

  ('quarterly','Trimestriellement',    2),

  ('employee-motivation-retention','Motivation et rétention des employés',2),

  ('alignment-of-wages',  'Alignement des salaires sur le marché', 2),

  ('reward-for-performance','Récompense pour la performance', 2),

  ('internal-equity', 'Équité interne',  2),

  ('seniority',   'Ancienneté',  2),

  ('specific-skills', 'Compétences spécifiques',   2),

  ('market-salary-comparison',  'Comparaison avec les salaires du marché',2),

  ('achieving-personal-goals',  'Atteinte des objectifs personnels', 2),

  ('merit-increases', 'Augmentations au mérite',  2),

  ('cost-of-living-adjustments','Ajustements du coût de la vie', 2),

  ('promotion-increases',  'Augmentations pour promotion',2),

  ('one-time-bonuses', 'Bonus ponctuels', 2),

  ('market-comparisons', 'Ajustements basés sur des comparaisons de marché',2),

  ('less-than-3-percent', 'Moins de 3%', 2),

  ('between-3-and-5-percent',   'Entre 3% et 5%',  2),

  ('between-5-and-7-percent',   'Entre 5% et 7%', 2),

  ('more-than-7-percent',  'Plus de 7%',   2),

  ('less-than-1-percent',  'Moins de 1%',   2),

  ('between-1-and-3-percent', 'Entre 1% et 3%',     2),

  ('more-than-5-percent',   'Plus de 5%',     2);

-- English translations (languageId = 1)

INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)

VALUES

  ('less-than-1-year',  'Less than 1 year',   1),

  ('1-3-years',  '1-3 years',  1),

  ('3-5-years', '3-5 years',  1),

  ('more-than-5-years',   'More than 5 years',  1),

  ('semi-annually','Semi-annually',   1),

  ('quarterly',  'Quarterly',   1),

  ('employee-motivation-retention','Employee motivation and retention',1),

  ('alignment-of-wages', 'Alignment of wages to the market',    1),

  ('reward-for-performance', 'Reward for performance', 1),

  ('internal-equity', 'Internal equity',  1),

  ('seniority',  'Seniority', 1),

  ('specific-skills','Specific skills', 1),

  ('market-salary-comparison', 'Comparison with market salaries',  1),

  ('achieving-personal-goals',  'Achieving personal goals', 1),

  ('merit-increases','Merit increases',  1),

  ('cost-of-living-adjustments','Cost of living adjustments',  1),

  ('promotion-increases', 'Increases for promotions',    1),

  ('one-time-bonuses', 'One-time bonuses', 1),

  ('market-comparisons','Adjustments based on market comparisons',1),

  ('less-than-3-percent',  'Less than 3%',   1),

  ('between-3-and-5-percent', 'Between 3% and 5%', 1),

  ('between-5-and-7-percent', 'Between 5% and 7%', 1),

  ('more-than-7-percent', 'More than 7%', 1),

  ('less-than-1-percent', 'Less than 1%', 1),

  ('between-1-and-3-percent','Between 1% and 3%', 1),

  ('more-than-5-percent',  'More than 5%',1);