-- Pink Pulse referral analytics (ExposedGays local guide)
-- Run on SUPABASE_01 in exposedgays public schema

CREATE TABLE IF NOT EXISTS public.pulse_referral_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL CHECK (event_type IN ('impression', 'click', 'expand')),
  placement TEXT NOT NULL,
  link_key TEXT,
  target_url TEXT,
  page_path TEXT,
  session_id TEXT,
  user_agent TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_pulse_events_created
  ON public.pulse_referral_events (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_events_placement
  ON public.pulse_referral_events (placement, event_type);

ALTER TABLE public.pulse_referral_events ENABLE ROW LEVEL SECURITY;