Salary Market

use salarymarket;

-- 1) Quiz definition

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

VALUES

  (8, 'quiz_8_Heures_de_travail', 'Questionnaire 8.png', 15);

-- 2) Quiz translations

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

VALUES

  ('quiz_8_Heures_de_travail',

   'Questionnaire 8 – Heures de travail',

   'Étude des politiques et pratiques d’aménagement des heures de travail',

   'Étude des politiques et pratiques d’aménagement des heures de travail',

   2),

  ('quiz_8_Heures_de_travail',

   'Quiz 8 – Working Hours Policy Survey',

   'Survey of working hours and flexible work arrangements',

   'Survey of working hours and flexible work arrangements',

   1);

-- 3) Section

INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)

VALUES

  ('Working_Hours_Policy', 'quiz_8_Heures_de_travail', 1);

-- 4) Section translations

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)

VALUES

  ('Working_Hours_Policy', 'Working Hours Policy', 1),

  ('Working_Hours_Policy', 'Politique d’aménagement des heures de travail', 2);

-- 5) Questions & chart types

INSERT INTO solution_quiz_section_questions

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

VALUES

  ('Q8-WHP-1', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CM', 1, 'Bar'),

  ('Q8-WHP-2', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 2, 'Pie'),

  ('Q8-WHP-3', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 3, 'Pie'),

  ('Q8-WHP-4', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-3', 'CM', 4, 'Bar'),

  ('Q8-WHP-5', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 5, 'Pie'),

  ('Q8-WHP-6', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-5', 'CM', 6, 'Bar'),

  ('Q8-WHP-7', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-6', 'CU', 7, 'Pie'),

  ('Q8-WHP-8', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-6', 'CM', 8, 'Bar'),

  ('Q8-WHP-9', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-6', 'CM', 9, 'Pie'),

  ('Q8-WHP-10', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-6', 'CU', 10, 'Bar'),

  ('Q8-WHP-11', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 11, 'Pie'),

  ('Q8-WHP-12', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 12, 'Pie'),

  ('Q8-WHP-13', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-12', 'CM', 13, 'Bar'),

  ('Q8-WHP-14', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-12', 'CM', 14, 'Bar'),

  ('Q8-WHP-15', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', NULL, 'CU', 15, 'Pie'),

  ('Q8-WHP-16', 'Working_Hours_Policy', 'quiz_8_Heures_de_travail', 'Q8-WHP-15', 'CU', 16, 'Pie');

-- 6) Question translations (French & English)

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

  ('Q8-WHP-1', 'Quels sont les lieux et endroits de travail autorisés par votre Entreprise ?', 2),

  ('Q8-WHP-1', 'What are the authorized workplaces and locations in your company?', 1),

  ('Q8-WHP-2', 'Quel est le régime horaire hebdomadaire adopté par votre Entreprise ?', 2),

  ('Q8-WHP-2', 'What is the weekly working hours regime adopted by your company?', 1),

  ('Q8-WHP-3', 'Les heures supplémentaires sont-elles autorisées dans votre entreprise ?', 2),

  ('Q8-WHP-3', 'Are overtime hours allowed in your company?', 1),

  ('Q8-WHP-4', 'Quels sont les postes éligibiles aux heures supplémentaires ?', 2),

  ('Q8-WHP-4', 'Which positions are eligible for overtime?', 1),

  ('Q8-WHP-5', 'Existe-t-il une politique formalisée de flexibilité des horaires dans votre Entreprise?', 2),

  ('Q8-WHP-5', 'Is there a formalized flexible hours policy in your company?', 1),

  ('Q8-WHP-6', 'Quels types d\'aménagements flexibles sont proposés ?', 2),

  ('Q8-WHP-6', 'What types of flexible arrangements are offered?', 1),

  ('Q8-WHP-7', 'Le Télétravail est-il applicable à l\'ensemble des collaborateurs de votre Entreprise ?', 2),

  ('Q8-WHP-7', 'Is telework applicable to all employees in your company?', 1),

  ('Q8-WHP-8', 'Quelles sont les catégories professionnelles concernées par le Télétravail ?', 2),

  ('Q8-WHP-8', 'Which professional categories are concerned by telework?', 1),

  ('Q8-WHP-9', 'La prise en charge des frais liés au télétravail est-elle prévue par votre politique interne ?', 2),

  ('Q8-WHP-9', 'Is the coverage of telework-related expenses provided for by your internal policy?', 1),

  ('Q8-WHP-10', 'Nombre de jours télétravaillables par semaine autorisés ?', 2),

  ('Q8-WHP-10', 'Number of teleworkable days per week allowed?', 1),

  ('Q8-WHP-11', 'Des horaires spécifiques sont-ils appliqués durant le mois de Ramadan ?', 2),

  ('Q8-WHP-11', 'Are specific hours applied during Ramadan?', 1),

  ('Q8-WHP-12', 'Est-ce que l\'organisation adopte une séance unique pendant l\'été?', 2),

  ('Q8-WHP-12', 'Does the organization adopt a single session during summer?', 1),

  ('Q8-WHP-13', 'A quelle heure commence la journée de travail en séance unique ?', 2),

  ('Q8-WHP-13', 'At what time does the single-session workday start?', 1),

  ('Q8-WHP-14', 'A quelle heure se termine la journée de travail en séance unique ?', 2),

  ('Q8-WHP-14', 'At what time does the single-session workday end?', 1),

  ('Q8-WHP-15', 'Votre organisation pratique-t-elle le travail alterné (Travail par shift)?', 2),

  ('Q8-WHP-15', 'Does your organization practice alternating work (shift work)?', 1),

  ('Q8-WHP-16', 'Combien de shifts de travail sont proposés au sein de votre organisation ?', 2),

  ('Q8-WHP-16', 'How many work shifts are offered in your organization?', 1);

-- 7) Only insert new items (with English names, standardized codes)

INSERT INTO solution_reponses_items (itemCode, itemName)

VALUES

  ('office', 'Office'),

  ('factory', 'Factory'),

  ('retail_site', 'Retail or Commercial Sites'),

  ('regime_48h', '48h regime'),

  ('regime_40h', '40h regime'),

  ('flexible_hours', 'Flexible hours'),

  ('hybrid_week', 'Hybrid work week'),

  ('part_time', 'Part-time work'),

  ('with_cap', 'Yes, with a defined cap'),

  ('without_cap', 'Yes, without a defined cap'),

  ('under_conditions', 'Under conditions (please specify)'),

  ('one_day', '1 day'),

  ('two_days', '2 days'),

  ('three_days', '3 days'),

  ('seven_h', '7am'),

  ('eight_h', '8am'),

  ('nine_h', '9am'),

  ('fourteen_h', '2pm'),

  ('fifteen_h', '3pm'),

  ('sixteen_h', '4pm'),

  ('two_shifts', '2 shifts'),

  ('three_shifts', '3 shifts'),

  ('telework', 'Telework');

-- 8) Translations for new items (add French for standardized codes if not present)

INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)

VALUES

  ('office', 'Bureau', 2), ('office', 'Office', 1),

  ('factory', 'Usine', 2), ('factory', 'Factory', 1),

  ('retail_site', 'Points de vente ou Sites commerciaux', 2), ('retail_site', 'Retail or Commercial Sites', 1),

  ('regime_48h', 'Régime 48h', 2), ('regime_48h', '48h regime', 1),

  ('regime_40h', 'Régime 40h', 2), ('regime_40h', '40h regime', 1),

  ('flexible_hours', 'Horaire flexible', 2), ('flexible_hours', 'Flexible hours', 1),

  ('hybrid_week', 'Semaine de travail hybride', 2), ('hybrid_week', 'Hybrid work week', 1),

  ('part_time', 'Travail partiel', 2), ('part_time', 'Part-time work', 1),

  ('with_cap', 'Oui, avec un plafond défini', 2), ('with_cap', 'Yes, with a defined cap', 1),

  ('without_cap', 'Oui, sans un plafond défini', 2), 

  ('without_cap', 'Yes, without a defined cap', 1),

  ('under_conditions', 'Sous conditions (veuillez préciser)', 2), 

  ('under_conditions', 'Under conditions (please specify)', 1),

  ('one_day', '1 jour', 2), ('one_day', '1 day', 1),

  ('two_days', '2 jours', 2), ('two_days', '2 days', 1),

  ('three_days', '3 jours', 2), ('three_days', '3 days', 1),

  ('seven_h', '7h', 2), ('seven_h', '7am', 1),

  ('eight_h', '8h', 2), ('eight_h', '8am', 1),

  ('nine_h', '9h', 2), ('nine_h', '9am', 1),

  ('fourteen_h', '14h', 2), ('fourteen_h', '2pm', 1),

  ('fifteen_h', '15h', 2), ('fifteen_h', '3pm', 1),

  ('sixteen_h', '16h', 2), ('sixteen_h', '4pm', 1),

  ('two_shifts', '2 shifts', 2), ('two_shifts', '2 shifts', 1),

  ('three_shifts', '3 shifts', 2), ('three_shifts', '3 shifts', 1),

  ('telework', 'Télétravail', 2),

  ('telework', 'Telework', 1);

  -- Standardized codes (add French if not present)

-- 9) Link items to Quiz 8 questions (use standardized codes)

INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)

VALUES

  -- Q8-WHP-1 (Work locations)

  ('office', 'Q8-WHP-1', NULL, 0),

  ('factory', 'Q8-WHP-1', NULL, 0),

  ('retail_site', 'Q8-WHP-1', NULL, 0),

  ('telework', 'Q8-WHP-1', NULL, 0),

  ('other-please-specify', 'Q8-WHP-1', NULL, 1),

  -- Q8-WHP-2 (Weekly regime)

  ('regime_48h', 'Q8-WHP-2', NULL, 0),

  ('regime_40h', 'Q8-WHP-2', NULL, 0),

  ('other-please-specify', 'Q8-WHP-2', NULL, 1),

  -- Q8-WHP-3 (Overtime allowed)

  ('yes', 'Q8-WHP-3', NULL, 0),

  ('no', 'Q8-WHP-3', NULL, 0),

  -- Q8-WHP-4 (Eligible for overtime)

  ('executives', 'Q8-WHP-4', 'yes', 0),

  ('middle_managers', 'Q8-WHP-4', 'yes', 0),

  ('managers', 'Q8-WHP-4', 'yes', 0),

  ('professionals', 'Q8-WHP-4', 'yes', 0),

  ('support_tech', 'Q8-WHP-4', 'yes', 0),

  ('professional_support', 'Q8-WHP-4', 'yes', 0),

  ('labor', 'Q8-WHP-4', 'yes', 0),

  -- Q8-WHP-5 (Formalized flexible policy)

  ('yes', 'Q8-WHP-5', NULL, 0),

  ('no', 'Q8-WHP-5', NULL, 0),

  -- Q8-WHP-6 (Types of flexible arrangements)

  ('flexible_hours', 'Q8-WHP-6', 'yes', 0),

  ('telework', 'Q8-WHP-6', 'yes', 0),

  ('hybrid_week', 'Q8-WHP-6', 'yes', 0),

  ('part_time', 'Q8-WHP-6', 'yes', 0),

  ('other-please-specify', 'Q8-WHP-6', 'yes', 1),

  -- Q8-WHP-7 (Telework for all)

  ('yes', 'Q8-WHP-7', 'telework', 0),

  ('no', 'Q8-WHP-7', 'telework', 0),

  -- Q8-WHP-8 (Categories for telework)

  ('executives', 'Q8-WHP-8', 'telework', 0),

  ('middle_managers', 'Q8-WHP-8', 'telework', 0),

  ('managers', 'Q8-WHP-8', 'telework', 0),

  ('professionals', 'Q8-WHP-8', 'telework', 0),

  ('support_tech', 'Q8-WHP-8', 'telework', 0),

  ('professional_support', 'Q8-WHP-8', 'telework', 0),

  ('labor', 'Q8-WHP-8', 'telework', 0),

  -- Q8-WHP-9 (Telework expense coverage)

  ('with_cap', 'Q8-WHP-9', 'telework', 0),

  ('without_cap', 'Q8-WHP-9', 'telework', 0),

  ('under_conditions', 'Q8-WHP-9', 'telework', 1),

  ('no', 'Q8-WHP-9', 'telework', 0),

  -- Q8-WHP-10 (Teleworkable days)

  ('one_day', 'Q8-WHP-10', 'telework', 0),

  ('two_days', 'Q8-WHP-10', 'telework', 0),

  ('three_days', 'Q8-WHP-10', 'telework', 0),

  ('other-please-specify', 'Q8-WHP-10', 'telework', 1),

  -- Q8-WHP-11 (Specific Ramadan hours)

  ('yes', 'Q8-WHP-11', NULL, 0),

  ('no', 'Q8-WHP-11', NULL, 0),

  -- Q8-WHP-12 (Single session in summer)

  ('yes', 'Q8-WHP-12', NULL, 0),

  ('no', 'Q8-WHP-12', NULL, 0),

  -- Q8-WHP-13 (Start time single session)

  ('seven_h', 'Q8-WHP-13', 'yes', 0),

  ('eight_h', 'Q8-WHP-13', 'yes', 0),

  ('nine_h', 'Q8-WHP-13', 'yes', 0),

  ('other-please-specify', 'Q8-WHP-13', 'yes', 1),

  -- Q8-WHP-14 (End time single session)

  ('fourteen_h', 'Q8-WHP-14', 'yes', 0),

  ('fifteen_h', 'Q8-WHP-14', 'yes', 0),

  ('sixteen_h', 'Q8-WHP-14', 'yes', 0),

  ('other-please-specify', 'Q8-WHP-14', 'yes', 1),

  -- Q8-WHP-15 (Shift work)

  ('yes', 'Q8-WHP-15', NULL, 0),

  ('no', 'Q8-WHP-15', NULL, 0),

  -- Q8-WHP-16 (Number of shifts)

  ('two_shifts', 'Q8-WHP-16', 'yes', 0),

  ('three_shifts', 'Q8-WHP-16', 'yes', 0),

  ('other-please-specify', 'Q8-WHP-16', 'yes', 1);