-- Dugout: social baseball card trading platform

create table if not exists profiles (
  user_id     text primary key,
  handle      text not null unique,
  display_name text not null,
  bio         text not null default '',
  avatar_url  text,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);
create index if not exists profiles_handle_idx on profiles (handle);

create table if not exists cards (
  id            text primary key,
  owner_id      text not null,
  player_name   text not null,
  team          text not null,
  position      text not null,
  season_year   int not null,
  rarity        text not null default 'common',
  batting_avg   text,
  home_runs     int,
  rbi           int,
  era           text,
  wins          int,
  strikeouts    int,
  image_data    text,
  description   text not null default '',
  for_trade     boolean not null default true,
  created_at    timestamptz not null default now()
);
create index if not exists cards_owner_id_idx on cards (owner_id);
create index if not exists cards_for_trade_idx on cards (for_trade);
create index if not exists cards_created_at_idx on cards (created_at desc);

create table if not exists posts (
  id          text primary key,
  user_id     text not null,
  content     text not null,
  card_id     text references cards (id) on delete set null,
  created_at  timestamptz not null default now()
);
create index if not exists posts_user_id_idx on posts (user_id);
create index if not exists posts_created_at_idx on posts (created_at desc);

create table if not exists post_likes (
  post_id   text not null references posts (id) on delete cascade,
  user_id   text not null,
  created_at timestamptz not null default now(),
  primary key (post_id, user_id)
);

create table if not exists comments (
  id         text primary key,
  post_id    text not null references posts (id) on delete cascade,
  user_id    text not null,
  content    text not null,
  created_at timestamptz not null default now()
);
create index if not exists comments_post_id_idx on comments (post_id);

create table if not exists follows (
  follower_id  text not null,
  following_id text not null,
  created_at   timestamptz not null default now(),
  primary key (follower_id, following_id)
);
create index if not exists follows_following_id_idx on follows (following_id);

create table if not exists trades (
  id                text primary key,
  from_user_id      text not null,
  to_user_id        text not null,
  offered_card_id   text not null references cards (id) on delete cascade,
  requested_card_id text not null references cards (id) on delete cascade,
  message           text not null default '',
  status            text not null default 'pending',
  created_at        timestamptz not null default now(),
  resolved_at       timestamptz
);
create index if not exists trades_from_user_idx on trades (from_user_id);
create index if not exists trades_to_user_idx on trades (to_user_id);
create index if not exists trades_status_idx on trades (status);

create table if not exists starter_grants (
  user_id    text primary key,
  granted_at timestamptz not null default now()
);