-- Migration: Online attendance — "join with others" (group attendance)
-- Description: Lets one primary joiner credit co-located companions who watched
--              through the same device. Each companion gets their own attendance
--              row flagged join_with_others = true and linked back to the primary's
--              registration via joined_with_registration_id. FK uses ON DELETE SET
--              NULL (never CASCADE) so removing the primary's registration only
--              clears the link, not the companion's attendance.
-- Idempotent — safe to re-run.

ALTER TABLE program_user_attendance
  ADD COLUMN IF NOT EXISTS joined_with_registration_id BIGINT,
  ADD COLUMN IF NOT EXISTS join_with_others BOOLEAN NOT NULL DEFAULT false;

COMMENT ON COLUMN program_user_attendance.joined_with_registration_id IS 'Registration id of the primary joiner this companion attended through (NULL for normal/self joins)';
COMMENT ON COLUMN program_user_attendance.join_with_others IS 'True when this attendance was credited via another person''s device (the companion)';

-- Guarded FK add so re-running does not error on the existing constraint.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'fk_attendance_joined_with_registration'
  ) THEN
    ALTER TABLE program_user_attendance
      ADD CONSTRAINT fk_attendance_joined_with_registration
      FOREIGN KEY (joined_with_registration_id)
      REFERENCES hdb_program_registration (id)
      ON DELETE SET NULL;
  END IF;
END $$;
