CREATE TABLE IF NOT EXISTS feature_discovery_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  event_type TEXT NOT NULL CHECK (event_type IN ('suggested', 'accepted', 'dismissed')),
  feature_type TEXT NOT NULL CHECK (feature_type IN ('journey', 'reading_plan', 'reminder')),
  feature_key TEXT NOT NULL,
  score REAL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_user_created
  ON feature_discovery_events(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_feature_created
  ON feature_discovery_events(feature_key, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_event_created
  ON feature_discovery_events(event_type, created_at DESC);
