CREATE TABLE IF NOT EXISTS meeting_number_counter (
  period_ym CHAR(7) PRIMARY KEY,
  last_value INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION next_meeting_number(p_date DATE DEFAULT CURRENT_DATE)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_period TEXT;
  v_seq INTEGER;
BEGIN
  v_period := TO_CHAR(p_date, 'YYYY_MM');

  LOOP
    UPDATE meeting_number_counter
       SET last_value = last_value + 1,
           updated_at = NOW()
     WHERE period_ym = v_period
     RETURNING last_value INTO v_seq;

    IF FOUND THEN
      EXIT;
    END IF;

    BEGIN
      INSERT INTO meeting_number_counter(period_ym, last_value)
      VALUES (v_period, 1);
      v_seq := 1;
      EXIT;
    EXCEPTION
      WHEN unique_violation THEN
        -- paralleler Insert, erneut versuchen
    END;
  END LOOP;

  RETURN 'SITZ-' || v_period || '_' || LPAD(v_seq::TEXT, 4, '0');
END;
$$;

UPDATE meeting
SET meeting_number = next_meeting_number(COALESCE(meeting_date, CURRENT_DATE))
WHERE meeting_number IS NULL
   OR TRIM(meeting_number) = '';

ALTER TABLE meeting
  ALTER COLUMN meeting_number SET DEFAULT next_meeting_number();

ALTER TABLE meeting
  ALTER COLUMN meeting_number SET NOT NULL;