use socle;

-- Insert into solution_quiz for Quiz 1
INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime)
VALUES 
  (1, 'quiz_1_The_Policies_and_Practices_of_Remuneration',
   'Questionnaire 1 -Les Politiques et pratiques de Rémunération.jpg',
   15);

-- Insert into solution_quiz_translate for Quiz 1 (French and English)
INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId)
VALUES 
  ('quiz_1_The_Policies_and_Practices_of_Remuneration', 
   'Questionnaire 1 -Les Politiques et pratiques de Rémunération',
   'Exploration des politiques et pratiques de rémunération au sein de l\'organisation',
   'Exploration des politiques et pratiques de rémunération au sein de l\'organisation',
    2),
  ('quiz_1_The_Policies_and_Practices_of_Remuneration', 
   'Quiz 1 - Remuneration Policies and Practices',
   'Exploration of remuneration policies and practices within the organization',
   'Exploration of remuneration policies and practices within the organization',
    1);

--  Insert into solution_quiz_section for Quiz 1
INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)
VALUES
  ('Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 1),
  ('Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 2),
  ('Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 3),
  ('Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 4);

-- 4. Insert translations for quiz sections (English then French)
INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
VALUES
  ('Remuneration_Policies', 'Remuneration Policies', 1),
  ('Hiring_Salaries', 'Hiring Salaries', 1),
  ('Variable_Remuneration', 'Variable Remuneration', 1),
  ('Seniority_Retribution', 'Seniority Retribution', 1);

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
VALUES
  ('Remuneration_Policies', 'Politiques de rémunération', 2),
  ('Hiring_Salaries', 'Salaires d''embauche', 2),
  ('Variable_Remuneration', 'Rémunération variable', 2),
  ('Seniority_Retribution', 'Rétribution de l''ancienneté', 2);

-- Insert into solution_quiz_section_questions for Quiz 1
-- Section "Remuneration Policies" (Questions 1–3)
INSERT INTO solution_quiz_section_questions 
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
VALUES
  ('Q1-RP-1', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL,      'CU', 1,  'Pie'),
  ('Q1-RP-2', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-RP-1','CU', 2,  'Bar'),
  ('Q1-RP-3', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-RP-1','CM', 3,  'Bar');

-- Section "Hiring Salaries" (Questions 4–6)
INSERT INTO solution_quiz_section_questions 
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
VALUES
  ('Q1-HS-4', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CM', 4, 'Bar'),
  ('Q1-HS-5', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CU', 5, 'Pie'),
  ('Q1-HS-6', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-HS-5','CU', 6, 'Table');

-- Section "Variable Remuneration" (Questions 7–11)
INSERT INTO solution_quiz_section_questions 
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
VALUES
  ('Q1-VR-7',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL,      'CU', 7,  'Pie'),
  ('Q1-VR-8',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM', 8,  'Table'),
  ('Q1-VR-9',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM', 9,  'Bar'),
  ('Q1-VR-10', 'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM', 10, 'Table'),
  ('Q1-VR-11', 'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM', 11, 'Bar');

-- Section "Seniority Retribution" (Questions 12–15)
INSERT INTO solution_quiz_section_questions 
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
VALUES
  ('Q1-SR-12', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CU', 12, 'Pie'),
  ('Q1-SR-13', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CU', 13, 'Bar'),
  ('Q1-SR-14', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CM', 14, 'Bar'),
  ('Q1-SR-15', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CU', 15, 'Bar');

--  Insert translations for questions in Quiz 1
-- French translations
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
VALUES
  ('Q1-RP-1', 'Quelle est la politique générale de rémunération de votre entreprise ?', 2),
  ('Q1-RP-2', 'À quelle fréquence cette politique est-elle révisée ?', 2),
  ('Q1-RP-3', 'Quels sont les objectifs principaux de votre politique de rémunération ?', 2),
  ('Q1-HS-4', 'Quels critères utilisez-vous pour déterminer les salaires d''embauche ?', 2),
  ('Q1-HS-5', 'Proposez-vous des salaires d''embauche différenciés selon les métiers et disciplines ?', 2),
  ('Q1-HS-6', 'Veuillez noter les salaires d''entrées par fonction', 2),
  ('Q1-VR-7', 'Votre entreprise propose-t-elle une rémunération variable (primes, bonus) ?', 2),
  ('Q1-VR-8', 'Quelles sont les catégories de collaborateurs concernées par cette rémunération variable ?', 2),
  ('Q1-VR-9', 'Quels sont les types de rémunérations variables proposés ?', 2),
  ('Q1-VR-10', 'En cas de note, quel pourcentage accordez-vous en termes de poids aux critères suivants ?', 2),
  ('Q1-VR-11', 'Comment votre entreprise communique-t-elle les critères et les objectifs de la rémunération variable aux employés ?', 2),
  ('Q1-SR-12', 'Votre entreprise tient-elle compte de l''ancienneté dans sa politique de rémunération ?', 2),
  ('Q1-SR-13', 'À partir de combien d''années, le collaborateur est-il éligible à la rétribution à l''ancienneté ?', 2),
  ('Q1-SR-14', 'Qu''offrez-vous comme rétribution à l''ancienneté ?', 2),
  ('Q1-SR-15', 'En cas de rétribution monétaire relative à l''ancienneté, quelles modalités pratiquez-vous ?', 2);

-- English translations
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
VALUES
  ('Q1-RP-1', 'What is your company''s general remuneration policy?', 1),
  ('Q1-RP-2', 'How frequently is this policy reviewed?', 1),
  ('Q1-RP-3', 'What are the main objectives of your remuneration policy?', 1),
  ('Q1-HS-4', 'What criteria do you use to determine hiring salaries?', 1),
  ('Q1-HS-5', 'Do you offer differentiated hiring salaries by job and discipline?', 1),
  ('Q1-HS-6', 'Please specify the entry-level salaries by function', 1),
  ('Q1-VR-7', 'Does your company offer variable remuneration (bonuses, incentives)?', 1),
  ('Q1-VR-8', 'Which categories of employees are covered by this variable remuneration?', 1),
  ('Q1-VR-9', 'What types of variable remunerations are offered?', 1),
  ('Q1-VR-10', 'If applicable, what percentage weight do you assign to the following criteria?', 1),
  ('Q1-VR-11', 'How does your company communicate the criteria and objectives of variable remuneration to employees?', 1),
  ('Q1-SR-12', 'Does your company consider seniority in its remuneration policy?', 1),
  ('Q1-SR-13', 'After how many years is an employee eligible for seniority retribution?', 1),
  ('Q1-SR-14', 'What form of seniority retribution do you offer?', 1),
  ('Q1-SR-15', 'For monetary retribution related to seniority, what modalities do you apply?', 1);

--  Insert into solution_reponses_items 
INSERT INTO solution_reponses_items (itemCode, itemName)
VALUES
  ('formal-policy','Formal remuneration policy'),
  ('informal-policy', 'Informal remuneration policy'),
  ('every-two-years','Every two years'),
  ('every-three-years','Every three years'),
  ('every-five-years','Every five years'),
  ('talent-attraction','Talent attraction and retention'),
  ('practice-management', 'Better management and consistency of practices'),
  ('regulatory-compliance', 'Compliance with regulations'),
  ('experience-skills', 'Candidate experience and skills'),
  ('market-comparison', 'Market comparison'),
  ('company-budget',  'Company budget'),
  ('responsibility-level',  'Job responsibility level'),
  ('executives',  'Executives'),
  ('managers', 'Managers'),
  ('sales', 'Sales'),
  ('professionals','Professionals'),
  ('technical-staff', 'Technical staff'),
  ('administrative','Administrative staff'),
  ('labor',  'Labor force'),
  ('annual-bonus', 'Annual bonus'),
  ('project-bonus', 'Project performance bonus'),
  ('stock-options',  'Stock options'),
  ('written-documents','Written documents'),
  ('individual-interviews', 'Individual interviews'),
  ('not-communicated', 'Not communicated'),
  ('3-years',  '3 years'),
  ('5-years',  '5 years'),
  ('7-years',  '7 years'),
  ('10-years', '10 years'),
  ('automatic-increase',   'Automatic salary increase'),
  ('seniority-bonus', 'Seniority bonus'),
  ('extra-vacation',  'Extra vacation days'),
  ('fixed-amount','Fixed amount'),
  ('percentage-of-salary','Percentage of annual salary'),
  ('other-policy',  'Other remuneration policy'),
  ('other-objectives',  'Other objectives'),
  ('other-hiring',  'Other hiring criteria'),
  ('yes', 'Yes'),
  ('no', 'No'),
  ('other-variable', 'Other variable remuneration'),
  ('individual-performance',  'Individual performance'),
  ('team-performance',  'Team performance'),
  ('company-performance', 'Company performance'),
  ('other-criteria', 'Other performance criteria'),
  ('other-communication', 'Other communication method'),
  ('other-seniority', 'Other seniority period'),
  ('other-retribution', 'Other retribution type'),
  ('other-monetary',   'Other monetary retribution'),
  ('annually', 'Annually');


--  Link items to questions via solution_quiz_section_questions_items
INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)
VALUES
  -- Q1-RP-1
  ('formal-policy', 'Q1-RP-1', NULL, 0),
  ('informal-policy', 'Q1-RP-1', NULL, 0),

  -- Q1-RP-2
  ('annually', 'Q1-RP-2', 'formal-policy', 0),
  ('every-two-years', 'Q1-RP-2', 'formal-policy', 0),
  ('every-three-years', 'Q1-RP-2', 'formal-policy', 0),
  ('every-five-years', 'Q1-RP-2', 'formal-policy', 0),
  ('other-policy', 'Q1-RP-2', 'formal-policy', 1),
 
  -- Q1-RP-3
  ('talent-attraction', 'Q1-RP-3','formal-policy', 0),
  ('practice-management', 'Q1-RP-3', 'formal-policy', 0),
  ('regulatory-compliance', 'Q1-RP-3', 'formal-policy', 0),
  ('other-objectives', 'Q1-RP-3','formal-policy', 1),

  -- Q1-HS-4
  ('experience-skills', 'Q1-HS-4', NULL, 0),
  ('market-comparison', 'Q1-HS-4', NULL, 0),
  ('company-budget', 'Q1-HS-4', NULL, 0),
  ('responsibility-level', 'Q1-HS-4', NULL, 0),
  ('other-hiring', 'Q1-HS-4', NULL, 1),

  -- Q1-HS-5
  ('yes', 'Q1-HS-5', NULL, 0),
  ('no', 'Q1-HS-5', NULL, 0),

  -- Q1-HS-6
 ('executives', 'Q1-HS-6', 'yes', 0),
  ('managers', 'Q1-HS-6', 'yes', 0),
  ('sales', 'Q1-HS-6', 'yes', 0),
  ('professionals', 'Q1-HS-6', 'yes', 0),
  ('technical-staff', 'Q1-HS-6', 'yes', 0),
  ('administrative', 'Q1-HS-6','yes', 0),
  ('labor', 'Q1-HS-6', 'yes', 0),
  -- Q1-VR-7
  ('yes', 'Q1-VR-7', NULL, 0),
  ('no', 'Q1-VR-7', NULL, 0),

  -- Q1-VR-8
  ('executives', 'Q1-VR-8', 'yes', 0),
  ('managers', 'Q1-VR-8', 'yes', 0),
  ('sales', 'Q1-VR-8', 'yes', 0),
  ('professionals', 'Q1-VR-8', 'yes', 0),
  ('technical-staff', 'Q1-VR-8', 'yes', 0),
  ('administrative', 'Q1-VR-8','yes', 0),
  ('labor', 'Q1-VR-8', 'yes', 0),

  -- Q1-VR-9
  ('annual-bonus', 'Q1-VR-9', 'yes', 0),
  ('project-bonus', 'Q1-VR-9', 'yes', 0),
  ('stock-options', 'Q1-VR-9', 'yes', 0),
  ('other-variable', 'Q1-VR-9','yes', 1),

  -- Q1-VR-10
  ('individual-performance', 'Q1-VR-10', 'yes', 0),
  ('team-performance', 'Q1-VR-10', 'yes', 0),
  ('company-performance', 'Q1-VR-10', 'yes', 0),
  ('other-criteria', 'Q1-VR-10', 'yes', 1),

  -- Q1-VR-11
  ('written-documents', 'Q1-VR-11', NULL, 0),
  ('individual-interviews', 'Q1-VR-11', NULL, 0),
  ('not-communicated', 'Q1-VR-11', NULL, 0),
  ('other-communication', 'Q1-VR-11', NULL, 1),

  -- Q1-SR-12
  ('yes', 'Q1-SR-12', NULL, 0),
  ('no', 'Q1-SR-12', NULL, 0),

  -- Q1-SR-13
  ('3-years', 'Q1-SR-13', 'yes', 0),
  ('5-years', 'Q1-SR-13', 'yes', 0),
  ('7-years', 'Q1-SR-13', 'yes', 0),
  ('10-years', 'Q1-SR-13', 'yes', 0),
  ('other-seniority', 'Q1-SR-13','yes', 1),

  -- Q1-SR-14
  ('automatic-increase', 'Q1-SR-14', 'yes', 0),
  ('seniority-bonus', 'Q1-SR-14', 'yes', 0),
  ('extra-vacation', 'Q1-SR-14', 'yes', 0),
  ('other-retribution', 'Q1-SR-14', 'yes', 1),

  -- Q1-SR-15
  ('fixed-amount', 'Q1-SR-15','yes', 0),
  ('percentage-of-salary', 'Q1-SR-15', 'yes', 0),
  ('other-monetary', 'Q1-SR-15', 'yes', 1);

-- Insert into solution_quiz_question_items_translate for item value translations
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
VALUES
  ('formal-policy','Formal remuneration policy',1),
  ('informal-policy', 'Informal remuneration policy',1),
  ('every-two-years','Every two years', 1),
  ('every-three-years',  'Every three years', 1),
  ('every-five-years','Every five years', 1),
  ('talent-attraction', 'Talent attraction and retention', 1),
  ('practice-management','Better management and consistency of practices', 1),
  ('regulatory-compliance','Compliance with regulations', 1),
  ('experience-skills','Candidate experience and skills', 1),
  ('market-comparison','Market comparison', 1),
  ('company-budget', 'Company budget', 1),
  ('responsibility-level','Job responsibility level',1),
  ('executives', 'Executives',1),
  ('managers', 'Managers', 1),
  ('sales', 'Sales',  1),
  ('professionals','Professionals',1),
  ('technical-staff','Technical staff',1),
  ('administrative','Administrative staff', 1),
  ('labor','Labor force',1),
  ('annual-bonus','Annual bonus', 1),
  ('project-bonus','Project performance bonus',1),
  ('stock-options','Stock options', 1),
  ('written-documents','Written documents',1),
  ('individual-interviews','Individual interviews',1),
  ('not-communicated','Not communicated',1),
  ('3-years','3 years',1),
  ('5-years','5 years',1),
  ('7-years','7 years', 1),
  ('10-years','10 years',1),
  ('automatic-increase','Automatic salary increase',1),
  ('seniority-bonus','Seniority bonus', 1),
  ('extra-vacation','Extra vacation days',1),
  ('fixed-amount','Fixed amount', 1),
  ('percentage-of-salary','Percentage of annual salary', 1),
  ('other-policy',  'Other remuneration policy',     1),
  ('other-objectives','Other objectives',   1),
  ('other-hiring',  'Other hiring criteria', 1),
  ('yes', 'Yes', 1),
  ('no', 'No', 1),
  ('other-variable', 'Other variable remuneration',  1),
  ('individual-performance',  'Individual performance',   1),
  ('team-performance',   'Team performance', 1),
  ('company-performance', 'Company performance',  1),
  ('other-criteria',   'Other performance criteria', 1),
  ('other-communication', 'Other communication method',  1),
  ('other-seniority', 'Other seniority period',  1),
  ('other-retribution',  'Other retribution type',   1),
  ('other-monetary', 'Other monetary retribution',  1),
  ('annually', 'Annually',  1);
  

-- insert with French language
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
VALUES
  ('formal-policy',            'Politique de rémunération formelle',            2),
  ('informal-policy',          'Politique de rémunération informelle',          2),
  ('every-two-years',          'Tous les deux ans',                             2),
  ('every-three-years',        'Tous les trois ans',                            2),
  ('every-five-years',         'Tous les cinq ans',                             2),
  ('talent-attraction',        'Attraction et rétention des talents',           2),
  ('practice-management',      'Meilleure gestion et cohérence des pratiques',  2),
  ('regulatory-compliance',    'Conformité avec les réglementations',           2),
  ('experience-skills',        'Expérience et compétences du candidat',         2),
  ('market-comparison',        'Comparaison avec le marché',                    2),
  ('company-budget',           'Budget de l''entreprise',                       2),
  ('responsibility-level',     'Niveau de responsabilité du poste',             2),
  ('executives',               'Dirigeants',                                    2),
  ('managers',                 'Managers',                                      2),
  ('sales',                    'Commercial',                                    2),
  ('professionals',            'Professionnels',                                2),
  ('technical-staff',          'Personnel technique',                           2),
  ('administrative',           'Administratif',                                 2),
  ('labor',                    'Main d''œuvre',                                 2),
  ('annual-bonus',             'Bonus annuel',                                  2),
  ('project-bonus',            'Bonus de performance par projet',               2),
  ('stock-options',            'Actions ou stock-options',                      2),
  ('written-documents',        'Documents écrits',                              2),
  ('individual-interviews',    'Entretiens individuels',                        2),
  ('not-communicated',         'Non communiqués',                               2),
  ('3-years',                  '3 ans',                                         2),
  ('5-years',                  '5 ans',                                         2),
  ('7-years',                  '7 ans',                                         2),
  ('10-years',                 '10 ans',                                        2),
  ('automatic-increase',       'Augmentation salariale automatique',            2),
  ('seniority-bonus',          'Prime d''ancienneté',                           2),
  ('extra-vacation',           'Jours de congés supplémentaires',               2),
  ('fixed-amount',             'Montant fixe',                                  2),
  ('percentage-of-salary',     'Pourcentage du salaire annuel',                 2),
  ('other-policy',             'Autre politique de rémunération',               2),
  ('other-objectives',         'Autres objectifs',                              2),
  ('other-hiring',             'Autres critères de recrutement',                2),
  ('yes',                      'Oui',                                           2),
  ('no',                       'Non',                                           2),
  ('other-variable',           'Autre rémunération variable',                   2),
  ('individual-performance',   'Performance individuelle',                      2),
  ('team-performance',         'Performance d’équipe',                          2),
  ('company-performance',      'Performance de l’entreprise',                   2),
  ('other-criteria',           'Autres critères de performance',                2),
  ('other-communication',      'Autre méthode de communication',                2),
  ('other-seniority',          'Autre période d’ancienneté',                    2),
  ('other-retribution',        'Autre type de rétribution',                     2),
  ('other-monetary',           'Autre rétribution monétaire',                   2),
  ('annually',                 'Annuellement',                                  2);

