-- CFL Academy + league directory (student / instructor / staff)
-- Database contract v1 — Capital Forge League, Inc.  Registrant: Mark Jones

create table if not exists league_users (
  id text primary key,
  email text not null unique,
  full_name text not null,
  role text not null,
  title text,
  password_hint text not null,
  sport text,
  service_branch text,
  location text,
  avatar_initials text not null,
  status text not null default 'active',
  last_seen_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists league_users_role_idx on league_users (role);

create table if not exists members (
  id text primary key,
  user_id text not null references league_users (id) on delete cascade,
  member_no text not null unique,
  track text not null,
  year_in_program integer not null default 1,
  yard_points integer not null default 0,
  team_id text,
  eligibility text not null default 'active',
  joined_at timestamptz not null default now()
);

create table if not exists member_profiles (
  member_id text primary key references members (id) on delete cascade,
  school text,
  sport text,
  position text,
  bio text,
  emergency_contact text,
  nil_complete boolean not null default false,
  created_at timestamptz not null default now()
);

create table if not exists teams (
  id text primary key,
  name text not null,
  slug text not null unique,
  venture_summary text not null,
  category text not null,
  coach_user_id text references league_users (id),
  status text not null default 'open',
  yard_points integer not null default 0,
  created_at timestamptz not null default now()
);

create table if not exists courses (
  id text primary key,
  code text not null unique,
  title text not null,
  quarter text not null,
  summary text not null,
  weeks integer not null default 8,
  instructor_user_id text references league_users (id),
  published boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists course_modules (
  id text primary key,
  course_id text not null references courses (id) on delete cascade,
  week integer not null,
  title text not null,
  summary text not null,
  minutes integer not null default 45
);

create table if not exists enrollments (
  id text primary key,
  course_id text not null references courses (id) on delete cascade,
  member_id text not null references members (id) on delete cascade,
  progress_pct integer not null default 0,
  status text not null default 'in_progress',
  enrolled_at timestamptz not null default now(),
  unique (course_id, member_id)
);

create table if not exists assignments (
  id text primary key,
  course_id text not null references courses (id) on delete cascade,
  title text not null,
  prompt text not null,
  due_at date,
  points integer not null default 100
);

create table if not exists submissions (
  id text primary key,
  assignment_id text not null references assignments (id) on delete cascade,
  member_id text not null references members (id) on delete cascade,
  status text not null default 'submitted',
  score integer,
  feedback text,
  submitted_at timestamptz not null default now()
);

create table if not exists game_days (
  id text primary key,
  title text not null,
  starts_at timestamptz not null,
  location text not null,
  notes text
);

create table if not exists org_directory (
  id text primary key,
  registrant_name text not null,
  registrant_email text not null,
  address_line text not null,
  city_line text not null,
  ein text not null,
  legal_name text not null,
  updated_at timestamptz not null default now()
);