alter table course_product_details
  add column if not exists capacity integer not null default 0,
  add column if not exists checkin_required boolean not null default false;

update course_product_details
set checkin_required = true
where course_format = '线下培训';

create table if not exists course_registrations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  product_id text not null references products(id) on delete cascade,
  order_id uuid references orders(id) on delete cascade,
  entitlement_id uuid references entitlements(id) on delete set null,
  status text not null default 'registered',
  checkin_code text not null unique,
  checked_in_at timestamptz,
  checked_in_by_staff_id uuid references staff_accounts(id) on delete set null,
  checkin_source text not null default '',
  checkin_note text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists idx_course_registrations_user_product_active
  on course_registrations(user_id, product_id)
  where status <> 'cancelled';

create index if not exists idx_course_registrations_product_status
  on course_registrations(product_id, status, created_at desc);

create index if not exists idx_course_registrations_checkin_staff
  on course_registrations(checked_in_by_staff_id, checked_in_at desc);
