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

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

ALTER TABLE meeting_agenda_item
  ADD COLUMN IF NOT EXISTS follow_up_type VARCHAR(30);

ALTER TABLE meeting_agenda_item
  ADD COLUMN IF NOT EXISTS follow_up_note TEXT;

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

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

ALTER TABLE meeting_agenda_item
  ADD COLUMN IF NOT EXISTS follow_up_created_at TIMESTAMPTZ;

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

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
      FROM pg_constraint
     WHERE conname = 'chk_meeting_agenda_item_follow_up_type'
       AND conrelid = 'meeting_agenda_item'::regclass
  ) THEN
    ALTER TABLE meeting_agenda_item
      ADD CONSTRAINT chk_meeting_agenda_item_follow_up_type
      CHECK (follow_up_type IS NULL OR follow_up_type IN ('rueckfrage', 'klaerung', 'bemerkung'));
  END IF;
END $$;

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

CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_parent ON meeting_agenda_item(parent_agenda_item_id);
CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_root ON meeting_agenda_item(root_agenda_item_id);
CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_follow_up_source ON meeting_agenda_item(follow_up_created_from_meeting_id, follow_up_created_from_agenda_item_id);
CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_return_log_follow_up ON meeting_agenda_item_return_log(follow_up_agenda_item_id);