-- ============================================================
-- HeavyHaul Agent — switch from Supabase Auth to .env-based auth
--
-- Accounts now live in the AUTH_USERS environment variable and every
-- database/storage access goes through the server with the service-role key.
-- Authorization is enforced in the application layer (src/lib/data/trips.ts,
-- src/lib/api-guard.ts). RLS stays ENABLED with no anon/authenticated
-- policies, which means: deny-all for API keys, service-role only.
-- ============================================================

-- 1. profiles no longer mirror auth.users
drop trigger if exists on_auth_user_created on auth.users;
drop function if exists public.handle_new_user();
alter table public.profiles drop constraint if exists profiles_id_fkey;
alter table public.profiles alter column id set default gen_random_uuid();

-- 2. drop auth.uid()-based policies (session comes from the app, not Supabase)
drop policy if exists "profiles self read" on public.profiles;
drop policy if exists "profiles self update" on public.profiles;
drop policy if exists "broker pages public read" on public.broker_pages;
drop policy if exists "broker pages owner insert" on public.broker_pages;
drop policy if exists "broker pages owner update" on public.broker_pages;
drop policy if exists "trips participant read" on public.trips;
drop policy if exists "trips authenticated insert" on public.trips;
drop policy if exists "trips manager update" on public.trips;
drop policy if exists "participants read" on public.trip_participants;
drop policy if exists "participants manage insert" on public.trip_participants;
drop policy if exists "participants manage update" on public.trip_participants;
drop policy if exists "invitations participant read" on public.trip_invitations;
drop policy if exists "documents read" on public.documents;
drop policy if exists "documents insert" on public.documents;
drop policy if exists "permits read" on public.permits;
drop policy if exists "permits insert" on public.permits;
drop policy if exists "warnings read" on public.warnings;
drop policy if exists "warnings update" on public.warnings;
drop policy if exists "service requests read" on public.service_requests;
drop policy if exists "service requests insert" on public.service_requests;
drop policy if exists "chat read" on public.chat_messages;
drop policy if exists "chat insert" on public.chat_messages;
drop policy if exists "chat feedback update" on public.chat_messages;
drop policy if exists "events read" on public.trip_events;
drop policy if exists "intake owner read" on public.intake_submissions;

drop policy if exists "trip docs read" on storage.objects;
drop policy if exists "trip docs insert" on storage.objects;

drop function if exists public.has_trip_role(uuid, public.trip_role[]);
drop function if exists public.is_trip_participant(uuid);

-- RLS remains ENABLED on all tables with zero policies:
-- anon / authenticated keys can read or write NOTHING.
-- The server (service role) is the only path to data, and it enforces
-- participant-based authorization in code.

-- 3. storage bucket stays private (no change needed); files are served only
--    through short-lived signed URLs created server-side after an access check.