-- 0007_baneos.sql
-- Lista negra de cuentas: tabla prestamos.baneos con historial
-- (soporta baneos permanentes y temporales via expires_at, aunque la UI
-- de esta ronda solo expone permanentes). Helper is_banned() para middleware.

begin;

create table if not exists prestamos.baneos (
  id           bigserial primary key,
  profile_id   uuid not null references prestamos.profiles(id) on delete cascade,
  razon        text not null,
  banned_at    timestamptz not null default now(),
  banned_by    uuid references prestamos.profiles(id),
  expires_at   timestamptz,
  unbanned_at  timestamptz,
  unbanned_by  uuid references prestamos.profiles(id)
);

-- Un solo baneo activo por profile (unbanned_at IS NULL) — partial unique index
create unique index if not exists baneos_profile_activo_uniq
  on prestamos.baneos (profile_id)
  where unbanned_at is null;

create index if not exists baneos_banned_at_idx
  on prestamos.baneos (banned_at desc);

alter table prestamos.baneos enable row level security;

-- Admin lee/escribe todo
drop policy if exists baneos_admin_all on prestamos.baneos;
create policy baneos_admin_all on prestamos.baneos
  for all to authenticated
  using (prestamos.is_admin())
  with check (prestamos.is_admin());

-- User autenticado lee los suyos (para /banned mostrar la razón)
drop policy if exists baneos_read_self on prestamos.baneos;
create policy baneos_read_self on prestamos.baneos
  for select to authenticated
  using (profile_id = auth.uid());

grant select, insert, update, delete on prestamos.baneos to authenticated, service_role;
grant usage, select on prestamos.baneos_id_seq to authenticated, service_role;

-- Helper: baneo activo (no desbaneado y no expirado)
create or replace function prestamos.is_banned(p_uid uuid)
returns boolean
language sql
stable
security definer
set search_path = prestamos, pg_temp
as $$
  select exists (
    select 1 from prestamos.baneos
    where profile_id = p_uid
      and unbanned_at is null
      and (expires_at is null or expires_at > now())
  );
$$;

grant execute on function prestamos.is_banned(uuid) to anon, authenticated;

commit;