ALTER TABLE meeting_agenda_item
  ADD COLUMN IF NOT EXISTS case_id BIGINT REFERENCES br_case(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_case_id ON meeting_agenda_item(case_id);

CREATE TABLE IF NOT EXISTS meeting_agenda_counter (
  meeting_id BIGINT PRIMARY KEY REFERENCES meeting(id) ON DELETE CASCADE,
  last_value INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION next_agenda_number(p_meeting_id BIGINT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_seq INTEGER;
BEGIN
  IF p_meeting_id IS NULL THEN
    RAISE EXCEPTION 'meeting_id is required for next_agenda_number';
  END IF;

  LOOP
    UPDATE meeting_agenda_counter
       SET last_value = last_value + 1,
           updated_at = NOW()
     WHERE meeting_id = p_meeting_id
     RETURNING last_value INTO v_seq;

    IF FOUND THEN
      EXIT;
    END IF;

    BEGIN
      INSERT INTO meeting_agenda_counter(meeting_id, last_value)
      VALUES (p_meeting_id, 1);
      v_seq := 1;
      EXIT;
    EXCEPTION
      WHEN unique_violation THEN
        NULL;
    END;
  END LOOP;

  RETURN v_seq::TEXT;
END;
$$;

INSERT INTO meeting_agenda_item(meeting_id, agenda_number, title, description, duration_minutes, case_id)
SELECT NULL,
       NULL,
       c.case_number || ' – ' || c.title,
       COALESCE(c.summary, 'Automatisch aus Vorgang ' || c.case_number || ' erzeugt'),
       NULL,
       c.id
  FROM br_case c
 WHERE c.deleted_at IS NULL
   AND NOT EXISTS (
     SELECT 1
       FROM meeting_agenda_item ai
      WHERE ai.case_id = c.id
   );