use salarymarket;
-- 1) Quiz definition
INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime)
VALUES
  (9, 'quiz_9_Parcours_et_Performances', 'Questionnaire 9.png', 15);
-- 2) Quiz translations
INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId)
VALUES
  ('quiz_9_Parcours_et_Performances',
   'Questionnaire 9 – Parcours et Performances',
   'Questionnaire sur le système d’évaluation, l’attribution des grades et le chemin de carrière',
   'Questionnaire sur le système d’évaluation, l’attribution des grades et le chemin de carrière',
   2),
  ('quiz_9_Parcours_et_Performances',
   'Quiz 9 – Performance, Grades & Career Pathways',
   'Survey on performance evaluation systems, grade assignment, and career paths',
   'Survey on performance evaluation systems, grade assignment, and career paths',
   1);
-- 3) Sections
INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)
VALUES
  ('Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', 1),
  ('Grades_and_Promotions', 'quiz_9_Parcours_et_Performances', 2),
  ('Career_Management', 'quiz_9_Parcours_et_Performances', 3);
-- 4) Section translations
INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
VALUES
  ('Performance_Evaluation_System', 'Performance Evaluation System', 1),
  ('Performance_Evaluation_System', 'Système d’évaluation de la performance', 2),
  ('Grades_and_Promotions', 'Grades and Promotions', 1),
  ('Grades_and_Promotions', 'Attribution des grades et promotions', 2),
  ('Career_Management', 'Career Management', 1),
  ('Career_Management', 'Gestion des parcours professionnels', 2);
-- 5) Questions & chart types
INSERT INTO solution_quiz_section_questions
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
VALUES
  -- Section 1: Performance Evaluation System
  ('Q9-PES-1', 'Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 1, 'Pie'),
  ('Q9-PES-2', 'Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', 'Q9-PES-1', 'CU', 2, 'Pie'),
  ('Q9-PES-3', 'Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', 'Q9-PES-1', 'CM', 3, 'Bar'),
  ('Q9-PES-4', 'Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', 'Q9-PES-1', 'CU', 4, 'Pie'),
  ('Q9-PES-5', 'Performance_Evaluation_System', 'quiz_9_Parcours_et_Performances', 'Q9-PES-1', 'CM', 5, 'Bar'),
  -- Section 2: Grades and Promotions
  ('Q9-GP-1', 'Grades_and_Promotions', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 6, 'Pie'),
  ('Q9-GP-2', 'Grades_and_Promotions', 'quiz_9_Parcours_et_Performances', 'Q9-GP-1', 'CM', 7, 'Bar'),
  ('Q9-GP-3', 'Grades_and_Promotions', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 8, 'Pie'),
  ('Q9-GP-4', 'Grades_and_Promotions', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 9, 'Pie'),
  -- Section 3: Career Management
  ('Q9-CM-1', 'Career_Management', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 10, 'Pie'),
  ('Q9-CM-2', 'Career_Management', 'quiz_9_Parcours_et_Performances', NULL, 'CM', 11, 'Bar'),
  ('Q9-CM-3', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),
  ('Q9-CM-4', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),
  ('Q9-CM-5', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),

  ('Q9-CM-6', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),

  ('Q9-CM-7', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),

  ('Q9-CM-8', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),

  ('Q9-CM-9', 'Career_Management', 'quiz_9_Parcours_et_Performances', 'Q9-CM-2', 'CM', 12, 'Bar'),

  ('Q9-CM-10', 'Career_Management', 'quiz_9_Parcours_et_Performances', NULL, 'CU', 13, 'Pie');
-- 6) Question translations (French & English)
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
VALUES
  -- Section 1: Performance Evaluation System
  ('Q9-PES-1', 'Votre entreprise dispose-t-elle d’un système formalisé d’évaluation de la performance ?', 2),
  ('Q9-PES-1', 'Does your company have a formalized performance evaluation system?', 1),
  ('Q9-PES-2', 'A quelle fréquence les évaluations sont-elles réalisées ?', 2),
  ('Q9-PES-2', 'How often are performance evaluations conducted?', 1),
  ('Q9-PES-3', 'Quels outils ou méthodes utilisez-vous pour l’évaluation ?', 2),
  ('Q9-PES-3', 'What tools or methods do you use for evaluation?', 1),
  ('Q9-PES-4', 'Les critères d’évaluation sont-ils homogènes pour l’ensemble des métiers ?', 2),
  ('Q9-PES-4', 'Are evaluation criteria homogeneous for all job types?', 1),
  ('Q9-PES-5', 'Les résultats de l’évaluation influencent-ils directement:', 2),
  ('Q9-PES-5', 'Do evaluation results directly influence:', 1),
  -- Section 2: Grades and Promotions
  ('Q9-GP-1', 'Disposez-vous d’une grille de grades formalisée ?', 2),
  ('Q9-GP-1', 'Do you have a formalized grade grid?', 1),
  ('Q9-GP-2', 'Quels sont les principaux critères pour accéder à un grade supérieur ?', 2),
  ('Q9-GP-2', 'What are the main criteria for accessing a higher grade?', 1),
  ('Q9-GP-3', 'Les décisions de promotion sont-elles prises selon un processus standardisé ?', 2),
  ('Q9-GP-3', 'Are promotion decisions made according to a standardized process?', 1),
  ('Q9-GP-4', 'Disposez vous d\'une politique de communication autour des promotions', 2),
  ('Q9-GP-4', 'Do you have a communication policy around promotions?', 1),
  -- Section 3: Career Management
  ('Q9-CM-1', 'Disposez vous d\'une politique formelle de gestion des parcours professionnels ?', 2),
  ('Q9-CM-1', 'Do you have a formal career management policy?', 1),
  ('Q9-CM-2', 'Quelles dispositifs mettez-vous en place pour favoriser l’évolution de carrière ?', 2),
  ('Q9-CM-2', 'What measures do you implement to support career development?', 1),
  ('Q9-CM-3', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-3', 'Are these measures accessible to:', 1),
    ('Q9-CM-4', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-4', 'Are these measures accessible to:', 1),
  ('Q9-CM-5', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-5', 'Are these measures accessible to:', 1),
  ('Q9-CM-6', 'Ces dispositifs sont-ils accessibles à :', 2),
   ('Q9-CM-6', 'Are these measures accessible to:', 1),
  ('Q9-CM-7', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-7', 'Are these measures accessible to:', 1),
  ('Q9-CM-8', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-8', 'Are these measures accessible to:', 1),
  ('Q9-CM-9', 'Ces dispositifs sont-ils accessibles à :', 2),
  ('Q9-CM-9', 'Are these measures accessible to:', 1),
  
  ('Q9-CM-10', 'Existe-t-il des parcours différenciés selon les profils ou les métiers ?', 2),
  ('Q9-CM-10', 'Are there differentiated career paths according to profiles or job types?', 1);
-- 7) Only insert new items (with English names, standardized codes)
INSERT INTO solution_reponses_items (itemCode, itemName)
VALUES
  ('semiannual',                  'Semiannual'),
  ('annual_interview',            'Annual interview'),
  ('evaluation_360',              '360° evaluation'),
  ('self_evaluation',             'Self‑evaluation'),
  ('digital_platform',            'Digital evaluation platform'),
  ('salary_increase',             'Salary increases'),
  ('training_needs',              'Training needs'),
  ('in_progress',                 'In progress'),
  ('organizational_needs',        'Organizational needs'),
  ('hierarchical_recommendation', 'Hierarchical recommendation'),
  ('standardized_process',        'Standardized process'),
  ('informal_practices',          'Informal practices'),
  ('communication_policy',        'Communication policy'),
  ('career_plan',                 'Career plan'),
  ('internal_mobility_device',    'Internal mobility'),
  ('individual_development_plan', 'Individual development plan'),
  ('skills_assessment',           'Skills assessment'),
  ('managerial_support',          'Managerial support'),
  ('career_interview',            'Career interview'),
  ('all_employees',               'All employees'),
  ('technical_experts',           'Technical experts'),
  ('identified_talents',          'Identified talents'),
  ('promotion',                   'Promotion'),
  ('internal_mobility',           'Internal mobility'),
  ('evaluation_results',          'Evaluation results'),
  ('skills_certifications','Skills certifications'),
   ('yes_for_all','yes for all professions'),
   ('yes_career_plans_coaching','Yes (career plans, coaching...)'),
  ('under_development',           'Under development');
-- 8) Translations for new items (French and English)
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
VALUES
  ('semiannual', 'Semestrielle', 2), ('semiannual', 'Semiannual', 1),
  ('annual_interview', 'Entretiens annuels', 2), 
  ('annual_interview', 'Annual interview', 1),
  ('evaluation_360', 'Évaluation 360°', 2), 
  ('evaluation_360', '360° evaluation', 1),
  ('self_evaluation', 'Auto-évaluation', 2), 
  ('self_evaluation', 'Self-evaluation', 1),
  ('digital_platform', 'Plateformes numériques d’évaluation', 2),
   ('digital_platform', 'Digital evaluation platform', 1),
  ('salary_increase', 'Les augmentations salariales', 2), 
  ('salary_increase', 'Salary increases', 1),
  ('training_needs', 'Les besoins en formation', 2), 
  ('training_needs', 'Training needs', 1),
  ('in_progress', 'En cours de structuration', 2), 
  ('in_progress', 'In progress', 1),
  ('organizational_needs', 'Besoins organisationnels', 2),
   ('organizational_needs', 'Organizational needs', 1),
  ('hierarchical_recommendation', 'Recommandation hiérarchique', 2), 
  ('hierarchical_recommendation', 'Hierarchical recommendation', 1),
  ('standardized_process', 'Oui (processus écrit, comités, validation RH)', 2), 
  ('standardized_process', 'Yes (written process, committees, HR validation', 1),
  ('informal_practices', 'Partiellement (pratiques informelles encadrées)', 2), 
  ('informal_practices', 'Partially (structured informal practices)', 1),
  ('communication_policy', 'Politique de communication', 2), 
  ('communication_policy', 'Communication policy', 1),
  ('career_plan', 'Plans de carrière', 2), ('career_plan', 'Career plan', 1),
  ('internal_mobility_device', 'Mobilité interne', 2), 
  ('internal_mobility_device', 'Internal mobility', 1),
  ('individual_development_plan', 'Plan de développement individuel', 2),
   ('individual_development_plan', 'Individual development plan', 1),
  ('skills_assessment', 'Bilan de compétences', 2), ('skills_assessment', 'Skills assessment', 1),
  ('managerial_support', 'Accompagnement managérial', 2), ('managerial_support', 'Managerial support', 1),
  ('career_interview', 'Entretien de carrière', 2), ('career_interview', 'Career interview', 1),
  ('all_employees', 'Tous les collaborateurs', 2), ('all_employees', 'All employees', 1),
  ('technical_experts', 'Experts Techniques', 2), ('technical_experts', 'Technical experts', 1),
  ('identified_talents', 'Les Talents identifiés', 2), ('identified_talents', 'Identified talents', 1),
  ('under_development', 'En cours d''élaboration', 2), ('under_development', 'Under development', 1),
  ('skills_certifications', 'Compétences / certifications', 2), -- French
  ('skills_certifications', 'Skills certifications', 1),        -- English
  ('promotion', 'Les promotions', 2),  -- French
  ('promotion', 'Promotion', 1),  -- English
  ('internal_mobility', 'La mobilité Interne', 2),  -- French
  ('internal_mobility', 'Internal mobility', 1),    -- English
  ('yes_for_all', 'Oui pour tous les métiers', 2),  -- French
  ('yes_for_all', 'Yes for all professions', 1),    -- English 
  ('yes_career_plans_coaching',"Oui (plans de carrière, coaching...)", 2),
  ('yes_career_plans_coaching',"Yes (career plans, coaching...)", 1),
  ('evaluation_results', 'Résultats de l’évaluation de performance', 2),  -- French
  ('evaluation_results', 'Evaluation results', 1);                        -- English
-- 9) Link items to Quiz 9 questions (use standardized codes)
INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)
VALUES
  -- Q9-PES-1 (Performance evaluation system)
  ('yes', 'Q9-PES-1', NULL, 0),
  ('no', 'Q9-PES-1', NULL, 0),
  -- Q9-PES-2 (Frequency)
  ('annually', 'Q9-PES-2', 'yes', 0),
  ('semiannual', 'Q9-PES-2', 'yes', 0),
  ('quarterly', 'Q9-PES-2', 'yes', 0),
  ('other-please-specify', 'Q9-PES-2', 'yes', 1),
  -- Q9-PES-3 (Methods)
  ('annual_interview', 'Q9-PES-3', 'yes', 0),
  ('evaluation_360', 'Q9-PES-3', 'yes', 0),
  ('self_evaluation', 'Q9-PES-3', 'yes', 0),
  ('digital_platform', 'Q9-PES-3', 'yes', 0),
  ('other-please-specify', 'Q9-PES-3', 'yes', 1),
  -- Q9-PES-4 (Homogeneity)
  ('yes', 'Q9-PES-4', 'yes', 0),
  ('partially', 'Q9-PES-4','yes', 0),
  ('no', 'Q9-PES-4', 'yes', 0),
  -- Q9-PES-5 (Influence)
  ('salary_increase', 'Q9-PES-5', 'yes', 0),
  ('promotion', 'Q9-PES-5', 'yes', 0),
  ('training_needs', 'Q9-PES-5', 'yes', 0),
  ('internal_mobility', 'Q9-PES-5', 'yes', 0),
  ('no', 'Q9-PES-5', 'yes', 0),
  ('other-please-specify', 'Q9-PES-5', 'yes', 1),
  -- Q9-GP-1 (Grade grid)
  ('yes_for_all', 'Q9-GP-1', NULL, 0),
  ('partially', 'Q9-GP-1', NULL, 0),
  ('no', 'Q9-GP-1', NULL, 0),
  ('in_progress', 'Q9-GP-1', NULL, 0),
  -- Q9-GP-2 (Criteria for grade)
  ('seniority', 'Q9-GP-2', 'yes_for_all', 0),
  ('evaluation_results', 'Q9-GP-2', 'yes_for_all', 0),
  ('skills_certifications', 'Q9-GP-2', 'yes_for_all', 0),
  ('organizational_needs', 'Q9-GP-2', 'yes_for_all', 0),
  ('hierarchical_recommendation', 'Q9-GP-2', 'yes_for_all', 0),
  ('other-please-specify', 'Q9-GP-2', 'yes_for_all', 1),
-- for partially
  ('seniority', 'Q9-GP-2', 'partially', 0),
  ('evaluation_results', 'Q9-GP-2', 'partially', 0),
  ('skills_certifications', 'Q9-GP-2', 'partially', 0),
  ('organizational_needs', 'Q9-GP-2', 'partially', 0),
  ('hierarchical_recommendation', 'Q9-GP-2', 'partially', 0),
  ('other-please-specify', 'Q9-GP-2', 'partially', 1),
  -- Q9-GP-3 (Promotion process)
  ('standardized_process', 'Q9-GP-3', NULL, 0),
  ('informal_practices', 'Q9-GP-3', NULL, 0),
  ('no', 'Q9-GP-3', NULL, 0),
  -- Q9-GP-4 (Promotion communication)
  ('yes', 'Q9-GP-4', NULL, 0),
  ('no', 'Q9-GP-4', NULL, 0),
  -- Q9-CM-1 (Career management policy)
  ('yes_career_plans_coaching', 'Q9-CM-1', NULL, 0),
  ('no', 'Q9-CM-1', NULL, 0),
  ('in_progress', 'Q9-CM-1', NULL, 0),
  -- Q9-CM-2 (Career development measures)
  ('internal_mobility_device', 'Q9-CM-2', NULL, 0),
  ('training_needs', 'Q9-CM-2', NULL, 0),
  ('individual_development_plan', 'Q9-CM-2', NULL, 0),
  ('skills_assessment', 'Q9-CM-2', NULL, 0),
  ('managerial_support', 'Q9-CM-2', NULL, 0),
  ('career_interview', 'Q9-CM-2', NULL, 0),
  ('other-please-specify', 'Q9-CM-2', NULL, 1),
  -- Q9-CM-3 (Beneficiaries)
  ('all_employees', 'Q9-CM-3', 'internal_mobility_device', 0),
  ('managers', 'Q9-CM-3', 'internal_mobility_device', 0),
  ('technical_experts', 'Q9-CM-3', 'internal_mobility_device', 0),
  ('identified_talents', 'Q9-CM-3', 'internal_mobility_device', 0),
  ('other-please-specify', 'Q9-CM-3', 'internal_mobility_device', 1),
   -- Q9-CM-3 (Beneficiaries)
  ('all_employees', 'Q9-CM-4', 'training_needs', 0),
  ('managers', 'Q9-CM-4', 'training_needs', 0),
  ('technical_experts', 'Q9-CM-4', 'training_needs', 0),
  ('identified_talents', 'Q9-CM-4', 'training_needs', 0),
  ('other-please-specify', 'Q9-CM-4', 'training_needs', 1),
  
  ('all_employees', 'Q9-CM-5', 'individual_development_plan', 0),
  ('managers', 'Q9-CM-5', 'individual_development_plan', 0),
  ('technical_experts', 'Q9-CM-5', 'individual_development_plan', 0),
  ('identified_talents', 'Q9-CM-5', 'individual_development_plan', 0),
  ('other-please-specify', 'Q9-CM-5', 'individual_development_plan', 1),
  
  ('all_employees', 'Q9-CM-6', 'skills_assessment', 0),
  ('managers', 'Q9-CM-6', 'skills_assessment', 0),
  ('technical_experts', 'Q9-CM-6', 'skills_assessment', 0),
  ('identified_talents', 'Q9-CM-6', 'skills_assessment', 0),
  ('other-please-specify', 'Q9-CM-6', 'skills_assessment', 1),
  
  ('all_employees', 'Q9-CM-7', 'managerial_support', 0),
  ('managers', 'Q9-CM-7', 'managerial_support', 0),
  ('technical_experts', 'Q9-CM-7', 'managerial_support', 0),
  ('identified_talents', 'Q9-CM-7', 'managerial_support', 0),
  ('other-please-specify', 'Q9-CM-7', 'managerial_support', 1),
  
  ('all_employees', 'Q9-CM-8', 'career_interview', 0),
  ('managers', 'Q9-CM-8', 'career_interview', 0),
  ('technical_experts', 'Q9-CM-8', 'career_interview', 0),
  ('identified_talents', 'Q9-CM-8', 'career_interview', 0),
  ('other-please-specify', 'Q9-CM-8', 'career_interview', 1),
  
  ('all_employees', 'Q9-CM-9', 'other-please-specify', 0),
  ('managers', 'Q9-CM-9', 'other-please-specify', 0),
  ('technical_experts', 'Q9-CM-9', 'other-please-specify', 0),
  ('identified_talents', 'Q9-CM-9', 'other-please-specify', 0),
  ('other-please-specify', 'Q9-CM-9', 'other-please-specify', 1),
  
  
  -- Q9-CM-4 (Differentiated paths)
  ('yes', 'Q9-CM-10', NULL, 0),
  ('no', 'Q9-CM-10', NULL, 0),
  ('under_development', 'Q9-CM-10', NULL, 0);