UPDATE appointment_units
SET requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM appointments a
    JOIN students s ON s.id = a.student_id
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE a.id = appointment_units.appointment_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND (
          (appointment_units.requirement_key = 'intro' AND lt.name = 'Einweisung')
          OR (appointment_units.requirement_key = 'practice' AND lt.name IN ('Uebungsstunde', 'Übungsstunde', 'Uebungsstunden', 'Übungsstunden'))
          OR (appointment_units.requirement_key = 'rural' AND lt.name IN ('Ueberlandfahrt', 'Überlandfahrt', 'Bundes- / Landstrasse'))
          OR (appointment_units.requirement_key = 'highway' AND lt.name IN ('Autobahn', 'Autobahnfahrt'))
          OR (appointment_units.requirement_key = 'night' AND lt.name IN ('Nachtfahrt', 'Nachtfahrten', 'Daemmerung / Dunkelheit'))
      )
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), requirement_key)
WHERE requirement_key IN ('intro', 'practice', 'rural', 'highway', 'night');

UPDATE student_requirement_snapshots AS target
SET prior_completed_units = prior_completed_units + COALESCE((
    SELECT SUM(src.prior_completed_units)
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'intro'
), 0)
WHERE target.requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM students s
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE s.id = target.student_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND lt.name = 'Einweisung'
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), target.requirement_key)
  AND EXISTS (
    SELECT 1
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'intro'
  );

UPDATE student_requirement_snapshots AS target
SET prior_completed_units = prior_completed_units + COALESCE((
    SELECT SUM(src.prior_completed_units)
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'practice'
), 0)
WHERE target.requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM students s
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE s.id = target.student_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND lt.name IN ('Uebungsstunde', 'Übungsstunde', 'Uebungsstunden', 'Übungsstunden')
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), target.requirement_key)
  AND EXISTS (
    SELECT 1
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'practice'
  );

UPDATE student_requirement_snapshots AS target
SET prior_completed_units = prior_completed_units + COALESCE((
    SELECT SUM(src.prior_completed_units)
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'rural'
), 0)
WHERE target.requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM students s
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE s.id = target.student_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND lt.name IN ('Ueberlandfahrt', 'Überlandfahrt', 'Bundes- / Landstrasse')
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), target.requirement_key)
  AND EXISTS (
    SELECT 1
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'rural'
  );

UPDATE student_requirement_snapshots AS target
SET prior_completed_units = prior_completed_units + COALESCE((
    SELECT SUM(src.prior_completed_units)
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'highway'
), 0)
WHERE target.requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM students s
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE s.id = target.student_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND lt.name IN ('Autobahn', 'Autobahnfahrt')
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), target.requirement_key)
  AND EXISTS (
    SELECT 1
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'highway'
  );

UPDATE student_requirement_snapshots AS target
SET prior_completed_units = prior_completed_units + COALESCE((
    SELECT SUM(src.prior_completed_units)
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'night'
), 0)
WHERE target.requirement_key = COALESCE((
    SELECT 'lesson_type_' || lt.id
    FROM students s
    JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
    JOIN lesson_types lt ON lt.id = vis.lesson_type_id
    WHERE s.id = target.student_id
      AND lt.category = 'student'
      AND lt.is_counted = 1
      AND lt.name IN ('Nachtfahrt', 'Nachtfahrten', 'Daemmerung / Dunkelheit')
    ORDER BY lt.sort_order, lt.id
    LIMIT 1
), target.requirement_key)
  AND EXISTS (
    SELECT 1
    FROM student_requirement_snapshots src
    WHERE src.student_id = target.student_id
      AND src.requirement_key = 'night'
  );

DELETE FROM student_requirement_snapshots
WHERE requirement_key IN ('intro', 'practice', 'rural', 'highway', 'night')
  AND EXISTS (
      SELECT 1
      FROM students s
      JOIN lesson_type_template_visibility vis ON vis.template_id = s.template_id
      JOIN lesson_types lt ON lt.id = vis.lesson_type_id
      WHERE s.id = student_requirement_snapshots.student_id
        AND lt.category = 'student'
        AND lt.is_counted = 1
        AND (
            (student_requirement_snapshots.requirement_key = 'intro' AND lt.name = 'Einweisung')
            OR (student_requirement_snapshots.requirement_key = 'practice' AND lt.name IN ('Uebungsstunde', 'Übungsstunde', 'Uebungsstunden', 'Übungsstunden'))
            OR (student_requirement_snapshots.requirement_key = 'rural' AND lt.name IN ('Ueberlandfahrt', 'Überlandfahrt', 'Bundes- / Landstrasse'))
            OR (student_requirement_snapshots.requirement_key = 'highway' AND lt.name IN ('Autobahn', 'Autobahnfahrt'))
            OR (student_requirement_snapshots.requirement_key = 'night' AND lt.name IN ('Nachtfahrt', 'Nachtfahrten', 'Daemmerung / Dunkelheit'))
        )
  );
