-- SportsClaw Twitter Agent Tables
-- Migration 002

-- Every tweet posted by the Twitter agent
CREATE TABLE IF NOT EXISTS sc_twitter_tweets (
  id BIGSERIAL PRIMARY KEY,
  tweet_id VARCHAR(30),
  content_type VARCHAR(50) NOT NULL,
  tweet_text TEXT NOT NULL,
  league VARCHAR(50),
  media_type VARCHAR(20),
  media_task_id VARCHAR(100),
  media_url TEXT,
  twitter_media_id VARCHAR(30),
  thread_parent_id BIGINT REFERENCES sc_twitter_tweets(id),
  in_reply_to VARCHAR(30),
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  error TEXT,
  impressions INTEGER DEFAULT 0,
  likes INTEGER DEFAULT 0,
  retweets INTEGER DEFAULT 0,
  replies INTEGER DEFAULT 0,
  blog_post_id BIGINT REFERENCES sc_blog_posts(id),
  posted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS sc_twitter_tweets_status_idx ON sc_twitter_tweets(status);
CREATE INDEX IF NOT EXISTS sc_twitter_tweets_content_type_idx ON sc_twitter_tweets(content_type);
CREATE INDEX IF NOT EXISTS sc_twitter_tweets_posted_at_idx ON sc_twitter_tweets(posted_at);
CREATE INDEX IF NOT EXISTS sc_twitter_tweets_league_idx ON sc_twitter_tweets(league);
CREATE INDEX IF NOT EXISTS sc_twitter_tweets_tweet_id_idx ON sc_twitter_tweets(tweet_id);

-- Search/like/reply engagement actions
CREATE TABLE IF NOT EXISTS sc_twitter_engagement (
  id BIGSERIAL PRIMARY KEY,
  action_type VARCHAR(30) NOT NULL,
  target_tweet_id VARCHAR(30),
  target_user VARCHAR(100),
  search_query TEXT,
  reply_text TEXT,
  our_tweet_id BIGINT REFERENCES sc_twitter_tweets(id),
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS sc_twitter_engagement_action_type_idx ON sc_twitter_engagement(action_type);
CREATE INDEX IF NOT EXISTS sc_twitter_engagement_target_tweet_idx ON sc_twitter_engagement(target_tweet_id);

-- One-free-question funnel tracking
CREATE TABLE IF NOT EXISTS sc_twitter_free_questions (
  id BIGSERIAL PRIMARY KEY,
  twitter_user_id VARCHAR(30) NOT NULL,
  twitter_username VARCHAR(100),
  question_tweet_id VARCHAR(30),
  question_text TEXT,
  answer_tweet_id VARCHAR(30),
  answer_text TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS sc_twitter_free_questions_user_idx ON sc_twitter_free_questions(twitter_user_id);

-- Worker run idempotency (prevents double posts on restarts)
CREATE TABLE IF NOT EXISTS sc_twitter_worker_runs (
  id BIGSERIAL PRIMARY KEY,
  cycle_time TIMESTAMPTZ NOT NULL,
  slot_name VARCHAR(50) NOT NULL,
  executed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status VARCHAR(20) NOT NULL DEFAULT 'completed',
  tweets_posted INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS sc_twitter_worker_runs_slot_idx ON sc_twitter_worker_runs(slot_name, executed_at);

-- Engagement spam protection (per-user reply cooldown)
CREATE TABLE IF NOT EXISTS sc_twitter_user_cooldowns (
  id BIGSERIAL PRIMARY KEY,
  twitter_user_id VARCHAR(30) NOT NULL UNIQUE,
  last_replied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS sc_twitter_user_cooldowns_user_idx ON sc_twitter_user_cooldowns(twitter_user_id);
