-- Migration 050: Add missing columns to ausruestung_anfragen
-- These columns are referenced by the service code but were never created

ALTER TABLE ausruestung_anfragen ADD COLUMN IF NOT EXISTS bearbeitet_am TIMESTAMPTZ;
ALTER TABLE ausruestung_anfragen ADD COLUMN IF NOT EXISTS bestell_nummer INT;
ALTER TABLE ausruestung_anfragen ADD COLUMN IF NOT EXISTS bestell_jahr INT;

-- Backfill bestell_nummer for existing rows
DO $$
DECLARE
  yr INT;
  rec RECORD;
  nr INT;
BEGIN
  FOR yr IN SELECT DISTINCT EXTRACT(YEAR FROM erstellt_am)::int FROM ausruestung_anfragen LOOP
    nr := 0;
    FOR rec IN SELECT id FROM ausruestung_anfragen WHERE EXTRACT(YEAR FROM erstellt_am)::int = yr AND bestell_nummer IS NULL ORDER BY erstellt_am LOOP
      nr := nr + 1;
      UPDATE ausruestung_anfragen SET bestell_nummer = nr, bestell_jahr = yr WHERE id = rec.id;
    END LOOP;
  END LOOP;
END $$;
