-- ============================================================
-- HeavyHaul Agent — saved contacts (2026-09-09 meeting, Tasks 98 & 99).
--
-- Nash: "Maybe this page under My Fleet, as the carrier dispatch, maybe this
-- page is better for me to invite drivers? To save, like, my contacts, kind
-- of? … And I can just add my email, phone number, and name of the driver,
-- and it kind of triggers an invite email to him to create an account and to
-- join." And the reason: "when I go to create a trip, I have to invite
-- drivers, and I have to do it every time — put the name and the email and
-- the phone number and the extension. Can I have an ability to choose any of
-- my existing drivers?"
--
-- Until now a person existed ONLY as a per-trip `trip_participants` row, so
-- nothing connected the same driver across two trips and every invite was
-- retyped. A contact belongs to the dispatcher who saved it, not to any one
-- trip — which is exactly why the retyping happened.
--
-- Drivers and brokers are kept distinguishable: Nash, "We do have to keep
-- them distinguished — separate drivers and brokers — when I go to choose."
--
-- RLS enabled with no policies, like every table since 0002: the service role
-- is the only path in and authorization lives in app code.
-- ============================================================

create table if not exists public.carrier_contacts (
  id uuid primary key default gen_random_uuid(),
  -- Whose address book this is. Private to them.
  owner_id uuid not null references public.profiles (id) on delete cascade,
  name text not null,
  email text not null,
  -- Same shape the trip invite already requires (Task 33): phone required in
  -- the UI, extension optional.
  phone text,
  phone_ext text,
  role text not null default 'driver' check (role in ('driver', 'broker')),
  -- Set once the person has an account, so the picker can show who has joined.
  user_id uuid references public.profiles (id) on delete set null,
  -- When an account invitation was recorded for them. Email delivery is the
  -- email backend (later) — the record is real now.
  invited_at timestamptz,
  -- Last time they were added to a trip, for ordering the picker.
  last_used_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- One entry per person per role in an owner's book; email is matched
-- case-insensitively because that is how invitations already match people.
create unique index if not exists carrier_contacts_owner_email_role_key
  on public.carrier_contacts (owner_id, lower(email), role);

create index if not exists carrier_contacts_owner_idx
  on public.carrier_contacts (owner_id, role, name);

comment on table public.carrier_contacts is
  'A dispatcher''s or broker''s saved people (Tasks 98/99). Private to owner_id; drivers and brokers stay separate.';

alter table public.carrier_contacts enable row level security;