USE telc_certificates;

ALTER TABLE certificates
  ADD COLUMN points_leseverstehen INT NOT NULL DEFAULT 0 AFTER place_of_birth,
  ADD COLUMN points_sprachbausteine INT NOT NULL DEFAULT 0 AFTER points_leseverstehen,
  ADD COLUMN points_hoerverstehen INT NOT NULL DEFAULT 0 AFTER points_sprachbausteine,
  ADD COLUMN points_schriftlicher_ausdruck INT NOT NULL DEFAULT 0 AFTER points_hoerverstehen,
  ADD COLUMN points_kontaktaufnahme INT NOT NULL DEFAULT 0 AFTER points_schriftlicher_ausdruck,
  ADD COLUMN points_gespraech_thema INT NOT NULL DEFAULT 0 AFTER points_kontaktaufnahme,
  ADD COLUMN points_gemeinsame_aufgabe INT NOT NULL DEFAULT 0 AFTER points_gespraech_thema;

UPDATE certificates
SET
  points_leseverstehen = COALESCE(reading_points, 0),
  points_sprachbausteine = 0,
  points_hoerverstehen = COALESCE(listening_points, 0),
  points_schriftlicher_ausdruck = COALESCE(writing_points, 0),
  points_kontaktaufnahme = FLOOR(COALESCE(speaking_points, 0) / 3),
  points_gespraech_thema = FLOOR(COALESCE(speaking_points, 0) / 3),
  points_gemeinsame_aufgabe = COALESCE(speaking_points, 0) - 2 * FLOOR(COALESCE(speaking_points, 0) / 3);

UPDATE certificates
SET total_points =
  points_leseverstehen
  + points_sprachbausteine
  + points_hoerverstehen
  + points_schriftlicher_ausdruck
  + points_kontaktaufnahme
  + points_gespraech_thema
  + points_gemeinsame_aufgabe;

ALTER TABLE certificates
  DROP COLUMN reading_points,
  DROP COLUMN listening_points,
  DROP COLUMN writing_points,
  DROP COLUMN speaking_points;