-- 0053 — Permanent register of HHA trip and order numbers (2026-10-06).
--
-- Agreed with the Synchron CTO: HHA issues 9-digit numbers that become
-- Synchron order tokens. A number is never reused, even after a trip is
-- deleted, and each environment draws from its own range:
--   production 200000000–999999999 · staging 100000000–100999999
-- A trip's first carrier uses the trip number as its Synchron order number;
-- a new carrier on the same trip gets a new order number from this register.
-- Additive only. Paste in the Supabase SQL editor.

create table if not exists public.issued_trip_numbers (
  number text primary key check (number ~ '^[1-9][0-9]{8}$'),
  kind text not null check (kind in ('trip', 'order')),
  environment text not null check (environment in ('production', 'staging')),
  trip_id uuid references public.trips (id) on delete set null,
  issued_at timestamptz not null default now()
);
create index if not exists issued_trip_numbers_trip_idx on public.issued_trip_numbers (trip_id);
alter table public.issued_trip_numbers enable row level security;

-- Existing 9-digit Workspace trip numbers are already issued.
insert into public.issued_trip_numbers (number, kind, environment, trip_id)
select substring(ref_code from 4), 'trip', 'production', id
from public.trips
where ref_code ~ '^HH-[1-9][0-9]{8}$'
on conflict (number) do nothing;

-- One Synchron order number per trip and carrier.
create table if not exists public.trip_order_numbers (
  order_number text primary key references public.issued_trip_numbers (number),
  trip_id uuid not null references public.trips (id) on delete cascade,
  carrier_company_id uuid not null references public.companies (id) on delete restrict,
  created_at timestamptz not null default now(),
  unique (trip_id, carrier_company_id)
);
create index if not exists trip_order_numbers_trip_idx on public.trip_order_numbers (trip_id);
alter table public.trip_order_numbers enable row level security;
