-- Add laufende_nummer (sequential number per year) to bestellungen
ALTER TABLE bestellungen ADD COLUMN laufende_nummer INTEGER;

-- Backfill existing rows with sequential numbers per year
WITH numbered AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY EXTRACT(YEAR FROM erstellt_am) ORDER BY erstellt_am, id
  ) AS nr
  FROM bestellungen
)
UPDATE bestellungen b SET laufende_nummer = n.nr FROM numbered n WHERE b.id = n.id;

-- Make NOT NULL after backfill
ALTER TABLE bestellungen ALTER COLUMN laufende_nummer SET NOT NULL;
