-- Public site, opt-in bios, and donor desk
-- Database contract v1 — Capital Forge League, Inc.

alter table member_profiles add column if not exists public_bio boolean not null default false;
alter table member_profiles add column if not exists public_slug text;
alter table member_profiles add column if not exists public_headline text;

create unique index if not exists member_profiles_public_slug_idx
  on member_profiles (public_slug)
  where public_slug is not null;

alter table teams add column if not exists public_visible boolean not null default true;
alter table teams add column if not exists donor_featured boolean not null default false;
alter table teams add column if not exists donor_brief text;
alter table teams add column if not exists stage_label text;
alter table teams add column if not exists public_impact text;

alter table game_days add column if not exists audience text not null default 'members';
alter table game_days add column if not exists donor_invite boolean not null default false;

create table if not exists donor_profiles (
  user_id text primary key references league_users (id) on delete cascade,
  organization text,
  giving_tier text not null default 'sideline',
  since_year integer,
  restricted_to text,
  notes text
);

create table if not exists donor_project_access (
  id text primary key,
  donor_user_id text not null references league_users (id) on delete cascade,
  team_id text not null references teams (id) on delete cascade,
  access_level text not null default 'featured',
  invited_at timestamptz not null default now(),
  unique (donor_user_id, team_id)
);

create table if not exists game_day_invites (
  id text primary key,
  game_day_id text not null references game_days (id) on delete cascade,
  donor_user_id text not null references league_users (id) on delete cascade,
  status text not null default 'invited',
  note text,
  unique (game_day_id, donor_user_id)
);

create table if not exists team_updates (
  id text primary key,
  team_id text not null references teams (id) on delete cascade,
  audience text not null default 'donors',
  title text not null,
  body text not null,
  posted_at timestamptz not null default now()
);

create index if not exists team_updates_team_idx on team_updates (team_id, posted_at desc);
create index if not exists donor_project_access_donor_idx on donor_project_access (donor_user_id);
create index if not exists game_day_invites_donor_idx on game_day_invites (donor_user_id);