-- ============================================================
-- HeavyHaul Agent — 2026-09-07 meeting, 11:56–12:21 (Tasks 69, 71, 75, 76).
-- Independent of 0010: every statement is "add column if not exists", so the
-- two files can be applied in either order.
--
-- 1. Kingpin to rear axle (Task 76). Nash: "very important part is we have a
--    kingpin to rear axle spacing. This is the last one… An abbreviation for
--    kingpin to rear axle is called KPTRA." Its own column, not one more entry
--    in axle_spacings_in — the kingpin is not an axle, and the spacing list's
--    length is tied to the axle count.
--
-- 2. Chat languages on the profile (Task 71). Nash: "one language, English,
--    first, and then a button to add… It should stick to your profile… maybe
--    you want to set one of them to be primary language. The default language."
--
-- 3. How a route was paid for (Task 69). Nash: "If I have the active balance…
--    it should show me there that I have 15 available. When I buy one, it's
--    gonna go drop to 14." The remaining count is derived from credit-paid
--    route requests, so it is a real number that decrements.
--
-- 4. Re-ordering an expired permit (Task 75). Nash: "the person that's making
--    that request should be the one that's set as the client… if it's a broker
--    does it, the broker has the power to choose who is going to pay."
-- ============================================================

-- 1. KPTRA -------------------------------------------------------------------

alter table public.trip_units
  add column if not exists kingpin_to_rear_axle_in integer
    check (kingpin_to_rear_axle_in is null or kingpin_to_rear_axle_in >= 0);

comment on column public.trip_units.kingpin_to_rear_axle_in is
  'Kingpin to rear axle (KPTRA), in inches — the last spacing, required by permit offices.';

-- 2. Chat languages ------------------------------------------------------------

alter table public.profiles
  add column if not exists chat_languages text[] not null default '{en}',
  add column if not exists primary_language text not null default 'en';

comment on column public.profiles.chat_languages is
  'Languages this user enabled for the agent chat (subset of en/es/ru/ro). New users: English only.';
comment on column public.profiles.primary_language is
  'The language every chat opens in for this user. Must be one of chat_languages.';

-- 3. Route purchase payment source --------------------------------------------

alter table public.service_requests
  add column if not exists paid_with text
    check (paid_with is null or paid_with in ('credit', 'card'));

comment on column public.service_requests.paid_with is
  'How a route request was paid: credit = a prepaid Express Route credit, card = cart checkout. Null = permit requests / legacy rows.';

-- 4. Permit re-order ------------------------------------------------------------

alter table public.service_requests
  add column if not exists payer text
    check (payer is null or payer in ('requester', 'broker', 'carrier')),
  add column if not exists replaces_permit_id uuid references public.permits (id) on delete set null;

comment on column public.service_requests.payer is
  'Who is liable for a permit order sent to Synchron Permits. requester = the person who clicked; broker/carrier = chosen by a broker.';
comment on column public.service_requests.replaces_permit_id is
  'The expired permit this permit request re-orders, so the card can say "New permit requested" and the delivered permit can be matched.';

create index if not exists service_requests_replaces_permit_idx
  on public.service_requests (replaces_permit_id);