Spreadsheets to a live CRM

-- =====================================================================
-- UniSole Dispatch CRM — Phase B, migration 8: row-level security (RLS)
-- =====================================================================
-- This is the access wall, enforced INSIDE the database (PRD §3, "never
-- by hiding things in the UI"). Once RLS is on, a table returns only the
-- rows the logged-in user is allowed to see — even if the app has a bug.
--
-- Access matrix:
--   sales_agent — own carriers ONLY while not yet Active; own follow-ups
--   dispatcher  — carriers assigned to them; their loads & dispatch notes
--   admin       — everything; the only role that can reassign a dispatcher
-- =====================================================================


-- ---------------------------------------------------------------------
-- Identity helpers. SECURITY DEFINER so they can read `profiles` without
-- being blocked by profiles' own RLS (prevents recursion).
-- ---------------------------------------------------------------------
create or replace function app_is_admin()
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from profiles where id = auth.uid() and role in ('admin', 'system'));
$$;

create or replace function app_agent_id()
returns bigint language sql stable security definer set search_path = public as $$
  select linked_agent_id from profiles where id = auth.uid();
$$;

create or replace function app_dispatcher_id()
returns bigint language sql stable security definer set search_path = public as $$
  select linked_dispatcher_id from profiles where id = auth.uid();
$$;

-- Can the current user see this carrier? Used by the child tables so their
-- policies don't have to re-implement (or recurse into) carrier RLS.
create or replace function app_can_see_carrier(p_carrier_id bigint)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from carriers c
    where c.id = p_carrier_id and (
      app_is_admin()
      or (c.sales_agent_id = app_agent_id() and c.first_load_delivered_at is null)
      or c.dispatcher_id = app_dispatcher_id()
    )
  );
$$;


-- ---------------------------------------------------------------------
-- Turn RLS on for every table. With RLS on and no matching policy, access
-- is denied by default — so we add exactly the policies we want below.
-- ---------------------------------------------------------------------
alter table profiles                   enable row level security;
alter table sales_agents               enable row level security;
alter table dispatchers                enable row level security;
alter table truck_types                enable row level security;
alter table carriers                   enable row level security;
alter table carrier_status_history     enable row level security;
alter table carrier_dispatcher_history enable row level security;
alter table follow_ups                 enable row level security;
alter table carrier_dispatch_notes     enable row level security;
alter table loads                      enable row level security;


-- ---------------------------------------------------------------------
-- profiles — read your own; admins manage all
-- ---------------------------------------------------------------------
create policy profiles_select on profiles for select to authenticated
  using (id = auth.uid() or app_is_admin());
create policy profiles_admin_write on profiles for all to authenticated
  using (app_is_admin()) with check (app_is_admin());


-- ---------------------------------------------------------------------
-- Reference tables — everyone signed in can read (needed for labels /
-- dropdowns); only admins can change them.
-- ---------------------------------------------------------------------
create policy truck_types_select on truck_types for select to authenticated using (true);
create policy truck_types_admin  on truck_types for all    to authenticated
  using (app_is_admin()) with check (app_is_admin());

// =====================================================================
// UniSole CRM — guided simulation
// =====================================================================
// Run this to WATCH the CRM's business rules fire against your real
// Supabase database. It creates a demo sales agent, dispatcher, and
// carrier, then walks that carrier through its whole life — narrating
// what the DATABASE does automatically at each step.
//
//   node simulation.mjs          run the walkthrough (safe to re-run)
//   node simulation.mjs --clean  just delete the demo rows and exit
//
// Everything it creates is tagged "[SIM]" so it's easy to spot in the
// app and easy to clean up. It talks to the DB with the service_role
// key, which BYPASSES row-level security — that's why it can set data
// up freely. The access wall (who-sees-what) is demonstrated by
// explanation here; you can feel it for real by logging into the app as
// an agent vs. a dispatcher vs. an admin.
// =====================================================================

import { readFileSync } from "node:fs";
import { createClient } from "@supabase/supabase-js";

// --- tiny .env.local reader (no dependency needed) --------------------
function loadEnv(path = ".env.local") {
  const env = {};
  for (const line of readFileSync(path, "utf8").split("\n")) {
    const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
    if (m) env[m[1]] = m[2];
  }
  return env;
}
const env = loadEnv();
const URL = env.NEXT_PUBLIC_SUPABASE_URL;
const SERVICE_KEY = env.SUPABASE_SERVICE_ROLE_KEY;
if (!URL || !SERVICE_KEY) {
  console.error("Missing Supabase URL or service_role key in .env.local");
  process.exit(1);
}

// service_role client — bypasses RLS. NEVER ship this key to a browser.
const db = createClient(URL, SERVICE_KEY, {
  auth: { autoRefreshToken: false, persistSession: false },
});

// --- pretty printing --------------------------------------------------
const line = "─".repeat(70);
const h = (t) => console.log(`\n${line}\n  ${t}\n${line}`);
const say = (t) => console.log(`   ${t}`);
const ok = (t) => console.log(`   ✅ ${t}`);
const no = (t) => console.log(`   🛑 ${t}`);
const rule = (t) => console.log(`   📏 RULE — ${t}`);
const pause = () => new Promise((r) => setTimeout(r, 400));

// Fail loudly if a step that should succeed doesn't.
function must({ error }, what) {
  if (error) {
    console.error(`\n   ✖ Unexpected failure during "${what}":\n     ${error.message}\n`);
    process.exit(1);
  }
}

// =====================================================================
// Cleanup — remove any rows from a previous run (children → parents)
// =====================================================================
async function clean() {
  const { data: sims } = await db.from("carriers").select("id").like("company_name", "[SIM]%");
  const ids = (sims ?? []).map((r) => r.id);
  if (ids.length) {
    await db.from("loads").delete().in("carrier_id", ids);
    await db.from("carrier_status_history").delete().in("carrier_id", ids);