labre-web/supabase/migrations/0001_init.sql

-- 0001_init.sql
-- Sistema de Prestamos - Laboratorio de Sistemas Computacionales UABC
-- Schema aislado 'prestamos'. Coexiste con otras apps que usan 'public'.

begin;

create schema if not exists prestamos;
grant usage on schema prestamos to anon, authenticated, service_role;

-- ============================================================
-- Perfiles
-- ============================================================
create table prestamos.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text unique not null,
  nombre text,
  matricula text,
  rol text not null default 'alumno' check (rol in ('alumno','admin')),
  created_at timestamptz not null default now()
);

-- ============================================================
-- Categorias e inventario
-- ============================================================
create table prestamos.categorias (
  id serial primary key,
  nombre text unique not null
);

create table prestamos.materiales (
  id serial primary key,
  nombre text not null,
  categoria_id int references prestamos.categorias(id) on delete restrict,
  descripcion text,
  cantidad_total int not null check (cantidad_total >= 0),
  cantidad_disponible int not null check (cantidad_disponible >= 0),
  numero_inventario text unique,
  estado text not null default 'disponible' check (estado in ('disponible','mantenimiento','baja')),
  created_at timestamptz not null default now(),
  constraint disponibilidad_no_excede check (cantidad_disponible <= cantidad_total)
);

-- ============================================================
-- Prestamos
-- ============================================================
create table prestamos.prestamos (
  id bigserial primary key,
  alumno_id uuid not null references prestamos.profiles(id),
  material_id int not null references prestamos.materiales(id),
  cantidad int not null check (cantidad > 0),
  estado text not null default 'pendiente'
    check (estado in ('pendiente','aprobado','rechazado','activo','devuelto','vencido')),
  fecha_solicitud timestamptz not null default now(),
  fecha_aprobacion timestamptz,
  fecha_devolucion_estimada date,
  fecha_devolucion_real timestamptz,
  aprobado_por uuid references prestamos.profiles(id),
  notas text
);

create index prestamos_alumno_idx on prestamos.prestamos(alumno_id);
create index prestamos_estado_idx on prestamos.prestamos(estado);
create index prestamos_material_idx on prestamos.prestamos(material_id);

-- ============================================================
-- Audit log
-- ============================================================
create table prestamos.audit_log (
  id bigserial primary key,
  prestamo_id bigint references prestamos.prestamos(id) on delete cascade,
  actor_id uuid references prestamos.profiles(id),
  accion text not null,
  estado_anterior text,
  estado_nuevo text,
  at timestamptz not null default now(),
  payload jsonb
);

create index audit_prestamo_idx on prestamos.audit_log(prestamo_id);
create index audit_at_idx on prestamos.audit_log(at desc);

-- ============================================================
-- Helpers
-- ============================================================
create or replace function prestamos.is_admin()
returns boolean
language sql
stable
security definer
set search_path = prestamos, pg_temp
as $$
  select coalesce((select rol = 'admin' from prestamos.profiles where id = auth.uid()), false);
$$;

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

-- ============================================================
-- Trigger: crear perfil al registrar usuario en auth.users
-- Rechaza emails no @uabc.edu.mx (segunda linea de defensa)
-- ============================================================
create or replace function prestamos.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = prestamos, pg_temp
as $$
begin
  if new.email is null or new.email !~* '@uabc\.edu\.mx$' then
    raise exception 'dominio_no_permitido: solo se permite @uabc.edu.mx';
  end if;

  insert into prestamos.profiles (id, email, nombre)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'name')
  )
  on conflict (id) do nothing;

  return new;
end;
$$;

-- ponytail: trigger nombrado con prefijo 'prestamos_' para no colisionar con
-- posibles triggers de otras apps sobre auth.users
drop trigger if exists prestamos_on_auth_user_created on auth.users;
create trigger prestamos_on_auth_user_created
after insert on auth.users
for each row execute function prestamos.handle_new_user();

-- ============================================================
-- Trigger: mantener cantidad_disponible al cambiar estado del prestamo
-- ============================================================
create or replace function prestamos.sync_stock()
returns trigger
language plpgsql
security definer
set search_path = prestamos, pg_temp
as $$
begin
  -- transiciones que sacan stock
  if new.estado in ('aprobado','activo') and old.estado not in ('aprobado','activo') then
    update prestamos.materiales
    set cantidad_disponible = cantidad_disponible - new.cantidad
    where id = new.material_id;
  end if;

  -- transiciones que devuelven stock
  if new.estado in ('devuelto','rechazado') and old.estado in ('aprobado','activo') then
    update prestamos.materiales
    set cantidad_disponible = cantidad_disponible + new.cantidad
    where id = new.material_id;
  end if;

  return new;
end;
$$;

drop trigger if exists prestamos_sync_stock on prestamos.prestamos;
create trigger prestamos_sync_stock
after update of estado on prestamos.prestamos
for each row execute function prestamos.sync_stock();

-- ============================================================
-- Trigger: audit log cuando cambia estado
-- ============================================================
create or replace function prestamos.log_estado()
returns trigger
language plpgsql
security definer
set search_path = prestamos, pg_temp
as $$
begin
  if new.estado is distinct from old.estado then
    insert into prestamos.audit_log (prestamo_id, actor_id, accion, estado_anterior, estado_nuevo)
    values (new.id, auth.uid(), 'cambio_estado', old.estado, new.estado);
  end if;
  return new;
end;
$$;

drop trigger if exists prestamos_log_estado on prestamos.prestamos;
create trigger prestamos_log_estado
after update of estado on prestamos.prestamos
for each row execute function prestamos.log_estado();

-- ============================================================
-- RLS
-- ============================================================
alter table prestamos.profiles enable row level security;
alter table prestamos.categorias enable row level security;
alter table prestamos.materiales enable row level security;
alter table prestamos.prestamos enable row level security;
alter table prestamos.audit_log enable row level security;

-- profiles
create policy profiles_select_self_or_admin on prestamos.profiles
  for select to authenticated
  using (id = auth.uid() or prestamos.is_admin());

create policy profiles_update_self on prestamos.profiles
  for update to authenticated
  using (id = auth.uid())
  with check (id = auth.uid() and rol = (select rol from prestamos.profiles where id = auth.uid()));
  -- el usuario NO puede cambiar su propio rol; admin lo hace via service_role o via Studio

create policy profiles_admin_all on prestamos.profiles
  for all to authenticated
  using (prestamos.is_admin())
  with check (prestamos.is_admin());

-- categorias
create policy categorias_read_all on prestamos.categorias
  for select to authenticated using (true);

create policy categorias_admin_write on prestamos.categorias
  for all to authenticated
  using (prestamos.is_admin())
  with check (prestamos.is_admin());

-- materiales
create policy materiales_read_all on prestamos.materiales
  for select to authenticated using (true);

create policy materiales_admin_write on prestamos.materiales
  for all to authenticated
  using (prestamos.is_admin())
  with check (prestamos.is_admin());

-- prestamos
create policy prestamos_select_own_or_admin on prestamos.prestamos
  for select to authenticated
  using (alumno_id = auth.uid() or prestamos.is_admin());

create policy prestamos_insert_own on prestamos.prestamos
  for insert to authenticated
  with check (alumno_id = auth.uid() and estado = 'pendiente');

create policy prestamos_update_admin on prestamos.prestamos
  for update to authenticated
  using (prestamos.is_admin())
  with check (prestamos.is_admin());

-- audit_log
create policy audit_admin_read on prestamos.audit_log
  for select to authenticated using (prestamos.is_admin());
-- INSERT solo via triggers security definer -> no policy publica

-- ============================================================
-- Grants (RLS ya filtra; se otorgan a authenticated para operar)
-- ============================================================
grant select, insert, update, delete on all tables in schema prestamos to authenticated, service_role;
grant usage, select on all sequences in schema prestamos to authenticated, service_role;
alter default privileges in schema prestamos
  grant select, insert, update, delete on tables to authenticated, service_role;
grant select on prestamos.audit_log to authenticated;  -- redundante pero explicito

-- ============================================================
-- Seed: categorias iniciales
-- ============================================================
insert into prestamos.categorias (nombre) values
  ('Cables'),
  ('Herramientas'),
  ('Componentes electronicos'),
  ('Equipo de medicion'),
  ('Kits de desarrollo'),
  ('Otros')
on conflict (nombre) do nothing;

commit;