-- ============================================================
-- HeavyHaul Agent — account overrides for real password reset and role
-- change (2026-09-11).
--
-- Requirement: "Add an admin user management panel with password reset and
-- the ability to change user roles."
--
-- Logins are defined in the AUTH_USERS environment variable, with the
-- password in plain text. Environment variables are read-only at runtime, so
-- until now no screen could reset a password, and a role changed in the admin
-- console was overwritten by the env role at the user's next sign-in.
--
-- This table holds what an admin changes. It takes PRECEDENCE over AUTH_USERS:
--   • password_hash set → sign-in verifies against it (scrypt), not the env.
--   • role set          → the role used at sign-in, not the env role.
-- AUTH_USERS stays the list of who can sign in (the bootstrap); a login with
-- no row here behaves exactly as before.
--
--   • tokens_valid_after — every session issued before this instant is
--     rejected. Set on password reset and role change, so a reset actually
--     locks out an old session, and a demoted admin loses admin immediately
--     instead of keeping it until their 7-day session expires.
--
-- No foreign key to profiles on purpose: a profile row only exists once the
-- person has signed in, and an admin may reset a login that never has.
-- RLS enabled with no policies, like every table since 0002: the service role
-- is the only path in. Passwords are never stored in plain text here.
-- ============================================================

create table if not exists public.auth_accounts (
  -- Deterministic from the username (identifierToUserId), same id as profiles.
  user_id uuid primary key,
  username text not null,
  -- scrypt$N$r$p$salt$hash — null means "use the AUTH_USERS password".
  password_hash text,
  -- null means "use the AUTH_USERS role".
  role text check (role is null or role in ('broker', 'dispatcher', 'driver', 'admin')),
  tokens_valid_after timestamptz,
  password_updated_at timestamptz,
  updated_by text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists auth_accounts_username_key
  on public.auth_accounts (lower(username));

comment on table public.auth_accounts is
  'Admin overrides for AUTH_USERS logins: hashed password, role, session revocation. Takes precedence over the env.';

alter table public.auth_accounts enable row level security;