-- Stadium scoring: arenas, matchups, play-by-play, fan alerts, deal-room docs.

create table if not exists stadium_matchups (
  id text primary key,
  arena_id text not null,
  home_team_id text not null references teams (id),
  away_team_id text not null references teams (id),
  round_label text not null default 'Sweet 16',
  status text not null default 'live',
  kickoff_at timestamptz not null default now()
);

create table if not exists stadium_plays (
  id text primary key,
  matchup_id text not null references stadium_matchups (id) on delete cascade,
  team_id text not null references teams (id),
  member_id text references members (id),
  actor_email text,
  event_type text not null,
  points integer not null default 0,
  note text,
  created_at timestamptz not null default now()
);

create index if not exists stadium_plays_matchup_idx on stadium_plays (matchup_id, created_at);
create index if not exists stadium_plays_member_idx on stadium_plays (member_id);

create table if not exists stadium_votes (
  id text primary key,
  matchup_id text not null references stadium_matchups (id) on delete cascade,
  team_id text not null references teams (id),
  voter_email text not null,
  created_at timestamptz not null default now(),
  unique (matchup_id, voter_email)
);

create table if not exists stadium_alerts (
  id text primary key,
  email text not null,
  team_id text not null references teams (id) on delete cascade,
  role text not null default 'public',
  created_at timestamptz not null default now(),
  unique (email, team_id)
);

create table if not exists deal_room_docs (
  id text primary key,
  project_id text not null references capstone_projects (id) on delete cascade,
  kind text not null,
  title text not null,
  body text not null,
  uploaded_by text not null,
  created_at timestamptz not null default now()
);

create table if not exists deal_room_questions (
  id text primary key,
  project_id text not null references capstone_projects (id) on delete cascade,
  from_email text not null,
  from_name text not null,
  from_role text not null,
  body text not null,
  answer text,
  answered_by text,
  answered_at timestamptz,
  created_at timestamptz not null default now()
);

alter table members add column if not exists traction_flag boolean not null default false;