create type public.app_role as enum ('admin', 'user');

create table public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  role public.app_role not null,
  unique (user_id, role)
);
grant select on public.user_roles to authenticated;
grant all on public.user_roles to service_role;
alter table public.user_roles enable row level security;
create policy "Users can read their own roles"
  on public.user_roles for select to authenticated
  using (user_id = auth.uid());

create or replace function public.has_role(_user_id uuid, _role public.app_role)
returns boolean
language sql stable security definer set search_path = public
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = _user_id and role = _role
  )
$$;

create table public.chat_sessions (
  id uuid primary key default gen_random_uuid(),
  secret text not null default encode(gen_random_bytes(24), 'hex'),
  visitor_name text not null,
  visitor_phone text,
  status text not null default 'open',
  created_at timestamptz not null default now(),
  last_message_at timestamptz not null default now()
);
grant all on public.chat_sessions to service_role;
alter table public.chat_sessions enable row level security;

create table public.chat_messages (
  id uuid primary key default gen_random_uuid(),
  session_id uuid not null references public.chat_sessions(id) on delete cascade,
  sender text not null check (sender in ('visitor', 'owner')),
  body text not null,
  created_at timestamptz not null default now()
);
grant all on public.chat_messages to service_role;
alter table public.chat_messages enable row level security;

create index on public.chat_messages (session_id, created_at);
create index on public.chat_sessions (last_message_at desc);