-- ============================================================
-- HeavyHaul Agent — initial schema, RLS, storage
-- Run via: supabase db push  (or paste into SQL editor)
-- ============================================================

create extension if not exists "pgcrypto";

-- ---------- enums ----------
create type public.user_role as enum ('broker', 'dispatcher', 'driver', 'admin');
create type public.trip_role as enum ('broker', 'dispatcher', 'driver', 'pilot', 'shipper', 'admin');
create type public.trip_status as enum ('draft', 'waiting_for_permits', 'active', 'completed', 'cancelled');
create type public.permit_source as enum ('upload', 'synchron');
create type public.document_kind as enum ('rate_confirmation', 'permit', 'provision', 'route', 'other');
create type public.extraction_status as enum ('pending', 'processed', 'failed', 'skipped');
create type public.warning_kind as enum ('dimension_mismatch', 'expired', 'expiring', 'curfew', 'escort', 'missing_permit', 'other');
create type public.warning_severity as enum ('info', 'warning', 'danger');
create type public.participant_status as enum ('invited', 'active', 'removed');
create type public.request_type as enum ('permit_request', 'route_request');
create type public.request_status as enum ('requested', 'in_progress', 'fulfilled', 'cancelled');

-- ---------- profiles ----------
create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text not null,
  full_name text not null default '',
  phone text,
  company_name text,
  default_role public.user_role not null default 'broker',
  created_at timestamptz not null default now()
);

-- auto-create profile on signup (role/name passed via auth metadata)
create or replace function public.handle_new_user()
returns trigger
language plpgsql security definer set search_path = public
as $$
begin
  insert into public.profiles (id, email, full_name, phone, company_name, default_role)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data ->> 'full_name', ''),
    new.raw_user_meta_data ->> 'phone',
    new.raw_user_meta_data ->> 'company_name',
    coalesce((new.raw_user_meta_data ->> 'default_role')::public.user_role, 'broker')
  );
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ---------- broker intake pages ----------
create table public.broker_pages (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles (id) on delete cascade,
  slug text not null unique check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
  display_name text not null,
  intro_message text,
  enabled boolean not null default true,
  created_at timestamptz not null default now()
);
create index broker_pages_owner_idx on public.broker_pages (owner_id);

-- ---------- trips ----------
create table public.trips (
  id uuid primary key default gen_random_uuid(),
  ref_code text not null unique,
  created_by uuid references public.profiles (id) on delete set null,
  broker_page_id uuid references public.broker_pages (id) on delete set null,
  status public.trip_status not null default 'draft',
  permit_source public.permit_source not null default 'upload',
  carrier_name text not null default '',
  unit_number text,
  origin text not null default '',
  destination text not null default '',
  commodity text not null default '',
  load_length_in integer,
  load_width_in integer,
  load_height_in integer,
  load_weight_lbs integer,
  pickup_date date,
  delivery_date date,
  notes text,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index trips_status_idx on public.trips (status);
create index trips_broker_page_idx on public.trips (broker_page_id);

create or replace function public.touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;
create trigger trips_touch before update on public.trips
  for each row execute function public.touch_updated_at();

-- ---------- participants ----------
create table public.trip_participants (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  user_id uuid references public.profiles (id) on delete set null,
  email text not null,
  name text not null default '',
  phone text,
  role public.trip_role not null,
  status public.participant_status not null default 'invited',
  invited_by uuid references public.profiles (id) on delete set null,
  created_at timestamptz not null default now(),
  unique (trip_id, email, role)
);
create index trip_participants_trip_idx on public.trip_participants (trip_id);
create index trip_participants_user_idx on public.trip_participants (user_id);
create index trip_participants_email_idx on public.trip_participants (lower(email));

-- ---------- invitations ----------
create table public.trip_invitations (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  participant_id uuid references public.trip_participants (id) on delete cascade,
  email text not null,
  role public.trip_role not null,
  token text not null unique default encode(gen_random_bytes(24), 'hex'),
  invited_by uuid references public.profiles (id) on delete set null,
  expires_at timestamptz not null default now() + interval '14 days',
  accepted_at timestamptz,
  accepted_by uuid references public.profiles (id) on delete set null,
  created_at timestamptz not null default now()
);
create index trip_invitations_trip_idx on public.trip_invitations (trip_id);

-- ---------- documents ----------
create table public.documents (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  kind public.document_kind not null default 'other',
  storage_path text not null,
  file_name text not null,
  mime_type text,
  size_bytes bigint,
  uploaded_by uuid references public.profiles (id) on delete set null,
  uploader_label text not null default '',
  created_at timestamptz not null default now()
);
create index documents_trip_idx on public.documents (trip_id);

-- ---------- permits ----------
create table public.permits (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  document_id uuid references public.documents (id) on delete set null,
  state_code text not null default '',
  permit_number text,
  effective_date date,
  expiration_date date,
  permit_length_in integer,
  permit_width_in integer,
  permit_height_in integer,
  permit_weight_lbs integer,
  extraction jsonb,
  extraction_status public.extraction_status not null default 'pending',
  created_at timestamptz not null default now()
);
create index permits_trip_idx on public.permits (trip_id);

-- ---------- warnings ----------
create table public.warnings (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  permit_id uuid references public.permits (id) on delete cascade,
  kind public.warning_kind not null,
  severity public.warning_severity not null default 'warning',
  message text not null,
  resolved boolean not null default false,
  created_at timestamptz not null default now()
);
create index warnings_trip_idx on public.warnings (trip_id);

-- ---------- manual service requests (no payments in MVP) ----------
create table public.service_requests (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  type public.request_type not null,
  state_code text,
  notes text,
  status public.request_status not null default 'requested',
  requested_by uuid references public.profiles (id) on delete set null,
  requester_label text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index service_requests_trip_idx on public.service_requests (trip_id);
create trigger service_requests_touch before update on public.service_requests
  for each row execute function public.touch_updated_at();

-- ---------- shared chat ----------
create table public.chat_messages (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  user_id uuid references public.profiles (id) on delete set null,
  author_label text not null,
  author_role text not null default '',
  is_ai boolean not null default false,
  content text not null,
  state_code text,
  confidence text,
  sources jsonb,
  feedback smallint check (feedback in (-1, 1)),
  created_at timestamptz not null default now()
);
create index chat_messages_trip_idx on public.chat_messages (trip_id, created_at);

-- ---------- audit log ----------
create table public.trip_events (
  id uuid primary key default gen_random_uuid(),
  trip_id uuid not null references public.trips (id) on delete cascade,
  actor_id uuid references public.profiles (id) on delete set null,
  actor_label text not null default 'system',
  action text not null,
  detail jsonb,
  created_at timestamptz not null default now()
);
create index trip_events_trip_idx on public.trip_events (trip_id, created_at);

-- ---------- public intake submissions ----------
create table public.intake_submissions (
  id uuid primary key default gen_random_uuid(),
  broker_page_id uuid not null references public.broker_pages (id) on delete cascade,
  trip_id uuid references public.trips (id) on delete set null,
  carrier_company text not null,
  contact_name text not null,
  contact_email text not null,
  contact_phone text not null,
  permit_source public.permit_source not null,
  notes text,
  source_ip text,
  created_at timestamptz not null default now()
);
create index intake_submissions_page_idx on public.intake_submissions (broker_page_id);

-- ============================================================
-- Row Level Security
-- ============================================================

alter table public.profiles enable row level security;
alter table public.broker_pages enable row level security;
alter table public.trips enable row level security;
alter table public.trip_participants enable row level security;
alter table public.trip_invitations enable row level security;
alter table public.documents enable row level security;
alter table public.permits enable row level security;
alter table public.warnings enable row level security;
alter table public.service_requests enable row level security;
alter table public.chat_messages enable row level security;
alter table public.trip_events enable row level security;
alter table public.intake_submissions enable row level security;

-- helper: is the current user an active participant of the trip?
create or replace function public.is_trip_participant(p_trip_id uuid)
returns boolean
language sql security definer stable set search_path = public
as $$
  select exists (
    select 1 from public.trip_participants tp
    where tp.trip_id = p_trip_id
      and tp.status <> 'removed'
      and (
        tp.user_id = auth.uid()
        or lower(tp.email) = lower(coalesce(auth.jwt() ->> 'email', ''))
      )
  );
$$;

-- helper: does the current user hold one of the given roles on the trip?
create or replace function public.has_trip_role(p_trip_id uuid, p_roles public.trip_role[])
returns boolean
language sql security definer stable set search_path = public
as $$
  select exists (
    select 1 from public.trip_participants tp
    where tp.trip_id = p_trip_id
      and tp.status <> 'removed'
      and tp.role = any (p_roles)
      and (
        tp.user_id = auth.uid()
        or lower(tp.email) = lower(coalesce(auth.jwt() ->> 'email', ''))
      )
  );
$$;

-- profiles: self read/update; participants of shared trips can read basic profile
create policy "profiles self read" on public.profiles
  for select using (id = auth.uid());
create policy "profiles self update" on public.profiles
  for update using (id = auth.uid());

-- broker pages: owner full control; anyone can read enabled pages (public intake)
create policy "broker pages public read" on public.broker_pages
  for select using (enabled = true or owner_id = auth.uid());
create policy "broker pages owner insert" on public.broker_pages
  for insert with check (owner_id = auth.uid());
create policy "broker pages owner update" on public.broker_pages
  for update using (owner_id = auth.uid());

-- trips: participants read; broker page owner reads intake-created trips;
-- creator + broker/dispatcher/admin participants update
create policy "trips participant read" on public.trips
  for select using (
    public.is_trip_participant(id)
    or created_by = auth.uid()
    or exists (
      select 1 from public.broker_pages bp
      where bp.id = trips.broker_page_id and bp.owner_id = auth.uid()
    )
  );
create policy "trips authenticated insert" on public.trips
  for insert with check (auth.uid() is not null and created_by = auth.uid());
create policy "trips manager update" on public.trips
  for update using (
    created_by = auth.uid()
    or public.has_trip_role(id, array['broker','dispatcher','admin']::public.trip_role[])
  );

-- participants: visible to trip participants; managed by broker/dispatcher/admin or creator
create policy "participants read" on public.trip_participants
  for select using (public.is_trip_participant(trip_id) or exists (
    select 1 from public.trips t where t.id = trip_id and t.created_by = auth.uid()
  ));
create policy "participants manage insert" on public.trip_participants
  for insert with check (
    public.has_trip_role(trip_id, array['broker','dispatcher','admin']::public.trip_role[])
    or exists (select 1 from public.trips t where t.id = trip_id and t.created_by = auth.uid())
  );
create policy "participants manage update" on public.trip_participants
  for update using (
    public.has_trip_role(trip_id, array['broker','dispatcher','admin']::public.trip_role[])
    or user_id = auth.uid()
  );

-- invitations: managed server-side (service role); participants can read theirs
create policy "invitations participant read" on public.trip_invitations
  for select using (public.is_trip_participant(trip_id));

-- documents / permits / warnings / requests / chat / events: participant access
create policy "documents read" on public.documents
  for select using (public.is_trip_participant(trip_id));
create policy "documents insert" on public.documents
  for insert with check (public.is_trip_participant(trip_id));

create policy "permits read" on public.permits
  for select using (public.is_trip_participant(trip_id));
create policy "permits insert" on public.permits
  for insert with check (public.is_trip_participant(trip_id));

create policy "warnings read" on public.warnings
  for select using (public.is_trip_participant(trip_id));
create policy "warnings update" on public.warnings
  for update using (public.is_trip_participant(trip_id));

create policy "service requests read" on public.service_requests
  for select using (public.is_trip_participant(trip_id));
create policy "service requests insert" on public.service_requests
  for insert with check (public.is_trip_participant(trip_id));

create policy "chat read" on public.chat_messages
  for select using (public.is_trip_participant(trip_id));
create policy "chat insert" on public.chat_messages
  for insert with check (public.is_trip_participant(trip_id) and (user_id = auth.uid() or user_id is null));
create policy "chat feedback update" on public.chat_messages
  for update using (public.is_trip_participant(trip_id));

create policy "events read" on public.trip_events
  for select using (public.is_trip_participant(trip_id));

-- intake submissions: broker page owner reads their own
create policy "intake owner read" on public.intake_submissions
  for select using (exists (
    select 1 from public.broker_pages bp
    where bp.id = intake_submissions.broker_page_id and bp.owner_id = auth.uid()
  ));
-- inserts happen through the server API using the service role (bypasses RLS)

-- ============================================================
-- Storage: private bucket for trip documents
-- ============================================================
insert into storage.buckets (id, name, public)
values ('trip-documents', 'trip-documents', false)
on conflict (id) do nothing;

-- path convention: trip-documents/<trip_id>/<uuid>-<filename>
create policy "trip docs read" on storage.objects
  for select using (
    bucket_id = 'trip-documents'
    and public.is_trip_participant(((string_to_array(name, '/'))[1])::uuid)
  );
create policy "trip docs insert" on storage.objects
  for insert with check (
    bucket_id = 'trip-documents'
    and public.is_trip_participant(((string_to_array(name, '/'))[1])::uuid)
  );