-- Pick Contradiction Guard
-- Tracks directional picks per game to prevent recommending both sides of the same event

ALTER TABLE sc_twitter_tweets ADD COLUMN IF NOT EXISTS game_key TEXT;
ALTER TABLE sc_twitter_tweets ADD COLUMN IF NOT EXISTS pick_direction TEXT;

-- Index for fast lookups: "have we already picked a side on this game?"
CREATE INDEX IF NOT EXISTS idx_twitter_tweets_game_pick
  ON sc_twitter_tweets (game_key, pick_direction, status, created_at)
  WHERE game_key IS NOT NULL;

COMMENT ON COLUMN sc_twitter_tweets.game_key IS 'Normalized event identifier, e.g. nba:celtics@lakers or prop:nba:lebron:points';
COMMENT ON COLUMN sc_twitter_tweets.pick_direction IS 'Side recommended: home, away, over, under';
