-- ============================================================
-- User profiles (roles) — keyed to auth.users.id
-- ============================================================
-- Used by api/src/middleware/auth.ts and api/src/routes/users.ts
--
-- Note: We do NOT add REFERENCES auth.users(id) here. Scripts in
-- docker-entrypoint-initdb.d run on first Postgres init *before* GoTrue
-- creates auth.users, so an FK would fail on fresh volumes. After the
-- stack has started once, you may optionally enforce integrity:
--   ALTER TABLE public.profiles
--     ADD CONSTRAINT profiles_id_fkey
--     FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS profiles (
  id         uuid PRIMARY KEY,
  email      text NOT NULL DEFAULT '',
  role       text NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'user')),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_profiles_role ON profiles(role);

GRANT SELECT, INSERT, UPDATE, DELETE ON profiles TO anon, authenticated, service_role;
