-- League OS hub: member categories, applications, capstone, content.
-- Ported from the CFL Admin Portal. No contractor names.

alter table league_users add column if not exists category text;
alter table league_users add column if not exists org_name text;

update league_users set category = role where category is null;

create table if not exists applications (
  id text primary key,
  kind text not null,
  full_name text not null,
  email text not null,
  summary text not null,
  status text not null default 'pending',
  score numeric,
  submitted_at timestamptz not null default now()
);

create index if not exists applications_kind_idx on applications (kind);
create index if not exists applications_status_idx on applications (status);

create table if not exists application_events (
  id text primary key,
  application_id text not null references applications (id) on delete cascade,
  from_status text,
  to_status text not null,
  note text,
  at timestamptz not null default now()
);

create table if not exists capstone_projects (
  id text primary key,
  slug text not null unique,
  title text not null,
  subtitle text,
  location text,
  company text,
  category text not null,
  summary text not null,
  bullets jsonb not null default '[]'::jsonb,
  status text not null default 'assembling',
  packet jsonb not null default '{}'::jsonb,
  public_card boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists capstone_seats (
  id text primary key,
  project_id text not null references capstone_projects (id) on delete cascade,
  seat text not null,
  person_name text not null default 'Open',
  filled boolean not null default false
);

create table if not exists media_assets (
  id text primary key,
  title text not null,
  kind text not null,
  src text not null,
  caption text,
  project_id text,
  posted_at timestamptz not null default now()
);

create table if not exists social_posts (
  id text primary key,
  platform text not null,
  body text not null,
  campaign text,
  scheduled_at timestamptz not null,
  status text not null default 'scheduled'
);