-- ============================================================
-- HeavyHaul Agent — Company Verification & Agent Approval (2026-09-08 meeting,
-- Tasks 84, 88–94; Nash's article "Company Verification and Broker Agent
-- Approval Logic" is the design).
--
-- Nash: "when he creates an account as a broker agent that works for a
-- company, somehow we need to qualify if this person is actually an employee
-- of that company… it will trigger an email to the main corporate email
-- asking to allow this employee to represent this company… somewhere in our
-- settings we should have a corporate profile." Same for carriers/dispatchers.
-- Verification is OPTIONAL — nothing in the product is gated on it
-- (Nash, 2026-09-08 answer to Q2).
--
-- Three layers, never a shortcut (article §32):
--   1. user account         → public.profiles (exists)
--   2. company profile      → public.companies
--   3. company membership   → public.company_memberships
-- plus the approval request (company_claims), the public-record evidence
-- (company_verification_records) and the audit log (company_audit_log).
--
-- Also: service_requests.vendor_order_id (Task 84 — Synchron's order number
-- was returned by the adapter and thrown away) and broker_page_slug_history
-- (Task 94 — an old intake URL keeps working after the company renames it).
--
-- RLS enabled with no policies, like every table since 0002: the service
-- role is the only path in and authorization lives in app code.
-- ============================================================

-- Task 84 ----------------------------------------------------------------------

alter table public.service_requests
  add column if not exists vendor_order_id text;

comment on column public.service_requests.vendor_order_id is
  'Synchron Permits order number returned by the order adapter (Task 84). Null until the backend returns one.';

-- Task 88: companies ------------------------------------------------------------

create table if not exists public.companies (
  id uuid primary key default gen_random_uuid(),
  legal_name text not null,
  display_name text not null,
  dba_name text,
  company_type text not null default 'broker' check (company_type in ('broker', 'carrier')),
  mc_number text,
  dot_number text,
  physical_address text,
  mailing_address text,
  corporate_email text,
  corporate_phone text,
  website text,
  logo_url text,
  -- Article §23 verification levels 0–4.
  verification_status text not null default 'unverified' check (verification_status in (
    'unverified', 'public_data_matched', 'corporate_contact_confirmed',
    'company_admin_approved', 'manual_verified'
  )),
  verification_level integer not null default 0 check (verification_level between 0 and 4),
  source_fmcsa_id text,
  source_sos_id text,
  -- Article §29: whether managers see every company trip (off by default).
  agents_see_all_trips boolean not null default false,
  notification_recipients text[] not null default '{}',
  -- Article §19: admin can lock a suspicious profile / merge duplicates.
  locked boolean not null default false,
  merged_into uuid references public.companies (id) on delete set null,
  -- Demo/placeholder companies created from an MC number before the FMCSA
  -- lookup exists (Task 89). Cleared by the admin queue when approved.
  is_placeholder boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists companies_mc_number_key
  on public.companies (mc_number) where mc_number is not null and merged_into is null;

comment on table public.companies is
  'Business entity (article §5/§21). Separate from the person: a user can belong to many companies.';

-- Task 88: memberships ----------------------------------------------------------

create table if not exists public.company_memberships (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles (id) on delete cascade,
  company_id uuid not null references public.companies (id) on delete cascade,
  -- Article §12 role levels. Carrier companies use the same set; the UI reads
  -- broker_manager / broker_agent as "manager" / "agent" for them.
  role text not null default 'broker_agent' check (role in (
    'company_owner', 'company_admin', 'broker_manager', 'broker_agent', 'billing_admin', 'viewer'
  )),
  status text not null default 'pending' check (status in ('pending', 'approved', 'rejected', 'revoked')),
  approved_by text,
  approved_at timestamptz,
  revoked_by text,
  revoked_at timestamptz,
  created_at timestamptz not null default now(),
  unique (user_id, company_id)
);

-- Task 88: claims (the approval request) -------------------------------------------

create table if not exists public.company_claims (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles (id) on delete cascade,
  company_id uuid not null references public.companies (id) on delete cascade,
  requested_role text not null default 'broker_agent',
  -- Article §4 company-relationship statuses.
  claim_status text not null default 'company_claimed' check (claim_status in (
    'company_claimed', 'company_match_found', 'company_verification_pending',
    'pending_corporate_approval', 'approved', 'rejected', 'revoked', 'admin_review_required'
  )),
  -- Article §8 methods.
  verification_method text not null default 'corporate_email_approval' check (verification_method in (
    'corporate_email_domain', 'corporate_email_approval', 'company_admin_approval', 'manual_review'
  )),
  -- The MC number the user typed (Task 89) — kept even when it matched nothing.
  mc_number text,
  approval_sent_to text,
  approval_token text not null unique default encode(gen_random_bytes(24), 'hex'),
  expires_at timestamptz not null default now() + interval '14 days',
  approver_name text,
  approver_email text,
  approver_ip text,
  decided_at timestamptz,
  -- Why the admin queue has this item (article §19 triggers) and its notes.
  review_reason text,
  review_notes text,
  -- Note the company/admin left for the user ("Request more info", §28).
  info_request text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists company_claims_user_idx on public.company_claims (user_id, created_at desc);
create index if not exists company_claims_company_idx on public.company_claims (company_id, claim_status);

-- Task 88: verification evidence ---------------------------------------------------

create table if not exists public.company_verification_records (
  id uuid primary key default gen_random_uuid(),
  company_id uuid not null references public.companies (id) on delete cascade,
  source text not null check (source in ('fmcsa', 'sos', 'corporate_email', 'company_admin', 'manual', 'local')),
  source_data jsonb,
  match_score text not null default 'none' check (match_score in ('high', 'medium', 'low', 'none')),
  verified_fields text[] not null default '{}',
  verified_at timestamptz,
  status text not null default 'recorded',
  created_at timestamptz not null default now()
);

-- Task 88: audit log ---------------------------------------------------------------

create table if not exists public.company_audit_log (
  id uuid primary key default gen_random_uuid(),
  actor_user_id uuid,
  actor_label text not null,
  company_id uuid references public.companies (id) on delete cascade,
  event_type text not null,
  old_value jsonb,
  new_value jsonb,
  ip_address text,
  created_at timestamptz not null default now()
);

create index if not exists company_audit_log_company_idx on public.company_audit_log (company_id, created_at desc);

-- Task 94: intake page ↔ company, slug history ----------------------------------------

alter table public.broker_pages
  add column if not exists company_id uuid references public.companies (id) on delete set null;

create table if not exists public.broker_page_slug_history (
  id uuid primary key default gen_random_uuid(),
  broker_page_id uuid not null references public.broker_pages (id) on delete cascade,
  old_slug text not null,
  created_at timestamptz not null default now()
);

create index if not exists broker_page_slug_history_slug_idx on public.broker_page_slug_history (old_slug);

-- RLS: enabled, no policies (service role only) ---------------------------------------

alter table public.companies enable row level security;
alter table public.company_memberships enable row level security;
alter table public.company_claims enable row level security;
alter table public.company_verification_records enable row level security;
alter table public.company_audit_log enable row level security;
alter table public.broker_page_slug_history enable row level security;

-- Storage: company logos (public — shown on the public intake page) and
-- verification documents (private, admin review only). -----------------------------------

insert into storage.buckets (id, name, public)
values ('company-logos', 'company-logos', true)
on conflict (id) do nothing;

insert into storage.buckets (id, name, public)
values ('company-verification-docs', 'company-verification-docs', false)
on conflict (id) do nothing;