begin;

-- Esquema privado para funciones y triggers internos.
create schema if not exists privado;

-- Perfiles vinculados a las cuentas de Supabase Auth.
create table if not exists public.perfiles (
  id uuid primary key references auth.users(id) on delete cascade,
  nombre text,
  email text,
  ip_ultima inet,
  ip_actualizada_en timestamptz,
  creado_en timestamptz not null default now(),
  actualizado_en timestamptz not null default now()
);

-- Cuentas de Stripe Connect.
create table if not exists public.cuentas_stripe (
  id uuid primary key default gen_random_uuid(),
  usuario_id uuid not null unique
    references public.perfiles(id) on delete cascade,
  stripe_account_id text not null unique,
  habilitada boolean not null default false,
  puede_cobrar boolean not null default false,
  puede_recibir_transferencias boolean not null default false,
  detalles_completos boolean not null default false,
  actualizado_en timestamptz not null default now()
);

-- Comisión de la plataforma: 1850 puntos básicos equivalen al 18,5 %.
create table if not exists public.configuracion_comisiones (
  id smallint primary key default 1 check (id = 1),
  tasa_puntos_basicos integer not null default 1850
    check (tasa_puntos_basicos between 0 and 10000),
  actualizado_en timestamptz not null default now()
);

insert into public.configuracion_comisiones (id, tasa_puntos_basicos)
values (1, 1850)
on conflict (id) do update
set tasa_puntos_basicos = excluded.tasa_puntos_basicos,
    actualizado_en = now();

-- Pagos. Los importes se guardan en céntimos, por ejemplo.
create table if not exists public.pagos (
  id uuid primary key default gen_random_uuid(),
  usuario_id uuid not null references public.perfiles(id),
  stripe_payment_intent_id text unique,
  stripe_charge_id text unique,
  importe_bruto bigint not null check (importe_bruto > 0),
  importe_comision bigint not null default 0
    check (importe_comision >= 0),
  moneda text not null default 'eur'
    check (char_length(moneda) = 3),
  estado text not null default 'pendiente'
    check (
      estado in (
        'pendiente',
        'procesando',
        'pagado',
        'fallido',
        'reembolsado'
      )
    ),
  creado_en timestamptz not null default now(),
  actualizado_en timestamptz not null default now()
);

-- Eventos recibidos de Stripe. El backend debe verificar la firma
-- del webhook antes de guardar un evento.
create table if not exists public.eventos_stripe (
  stripe_event_id text primary key,
  tipo_evento text not null,
  procesado boolean not null default false,
  recibido_en timestamptz not null default now(),
  procesado_en timestamptz,
  error text
);

-- Alertas que se muestran en la aplicación.
create table if not exists public.alertas_servicio (
  id uuid primary key default gen_random_uuid(),
  usuario_id uuid references public.perfiles(id) on delete cascade,
  categoria text not null
    check (categoria in ('servicio', 'pago', 'seguridad', 'sistema')),
  titulo text not null,
  mensaje text not null,
  leida boolean not null default false,
  creada_en timestamptz not null default now()
);

-- Automatizaciones administradas desde el backend.
create table if not exists public.automatizaciones (
  id uuid primary key default gen_random_uuid(),
  nombre text not null unique,
  activa boolean not null default true,
  configuracion jsonb not null default '{}'::jsonb,
  creada_en timestamptz not null default now(),
  actualizada_en timestamptz not null default now()
);

-- Función común para actualizar la fecha de modificación.
create or replace function privado.actualizar_marca_temporal()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.actualizado_en := now();
  return new;
end;
$$;

-- Crea automáticamente un perfil cuando se registra un usuario.
create or replace function privado.crear_perfil_nuevo_usuario()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.perfiles (id, nombre, email)
  values (
    new.id,
    new.raw_user_meta_data ->> 'nombre',
    new.email
  )
  on conflict (id) do nothing;

  return new;
end;
$$;

-- Triggers de perfiles.
drop trigger if exists al_crear_usuario_auth on auth.users;
create trigger al_crear_usuario_auth
after insert on auth.users
for each row
execute function privado.crear_perfil_nuevo_usuario();

drop trigger if exists perfiles_actualizar_marca on public.perfiles;
create trigger perfiles_actualizar_marca
before update on public.perfiles
for each row
execute function privado.actualizar_marca_temporal();

-- Triggers de pagos y cuentas Stripe.
drop trigger if exists pagos_actualizar_marca on public.pagos;
create trigger pagos_actualizar_marca
before update on public.pagos
for each row
execute function privado.actualizar_marca_temporal();

drop trigger if exists cuentas_stripe_actualizar_marca
on public.cuentas_stripe;
create trigger cuentas_stripe_actualizar_marca
before update on public.cuentas_stripe
for each row
execute function privado.actualizar_marca_temporal();

-- Reseñas entre usuarios registrados.
create table if not exists public.resenas_usuarios (
  id bigint generated always as identity primary key,
  autor_id uuid not null references public.perfiles(id) on delete cascade,
  usuario_resenado_id uuid not null
    references public.perfiles(id) on delete cascade,
  estrellas smallint not null check (estrellas between 1 and 5),
  comentario text
    check (comentario is null or char_length(comentario) <= 2000),
  creada_en timestamptz not null default now(),
  actualizada_en timestamptz not null default now(),

  constraint resena_no_propia
    check (autor_id <> usuario_resenado_id),

  constraint una_resena_por_pareja
    unique (autor_id, usuario_resenado_id)
);

create index if not exists idx_resenas_usuario_resenado
  on public.resenas_usuarios (usuario_resenado_id);

create index if not exists idx_resenas_autor
  on public.resenas_usuarios (autor_id);

-- Actualiza la fecha si se modifica una reseña.
create or replace function privado.actualizar_fecha_resena()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.actualizada_en := now();
  return new;
end;
$$;

drop trigger if exists trg_actualizar_fecha_resena
on public.resenas_usuarios;

create trigger trg_actualizar_fecha_resena
before update on public.resenas_usuarios
for each row
execute function privado.actualizar_fecha_resena();

-- Resumen de la puntuación y cantidad de reseñas por perfil.
create or replace view public.resumen_resenas_usuarios as
select
  usuario_resenado_id as usuario_id,
  round(avg(estrellas)::numeric, 2) as media_estrellas,
  count(*) as total_resenas
from public.resenas_usuarios
group by usuario_resenado_id;

-- El usuario solo puede consultar y modificar su propio perfil.
alter table public.perfiles enable row level security;

revoke all on public.perfiles from anon, authenticated;
grant select, insert, update on public.perfiles to authenticated;

drop policy if exists "Ver perfil propio" on public.perfiles;
create policy "Ver perfil propio"
on public.perfiles
for select
to authenticated
using ((select auth.uid()) = id);

drop policy if exists "Crear perfil propio" on public.perfiles;
create policy "Crear perfil propio"
on public.perfiles
for insert
to authenticated
with check ((select auth.uid()) = id);

drop policy if exists "Actualizar perfil propio" on public.perfiles;
create policy "Actualizar perfil propio"
on public.perfiles
for update
to authenticated
using ((select auth.uid()) = id)
with check ((select auth.uid()) = id);

-- Los usuarios pueden consultar su propia cuenta Stripe.
alter table public.cuentas_stripe enable row level security;

revoke all on public.cuentas_stripe from anon, authenticated;
grant select on public.cuentas_stripe to authenticated;

drop policy if exists "Ver cuenta Stripe propia"
on public.cuentas_stripe;

create policy "Ver cuenta Stripe propia"
on public.cuentas_stripe
for select
to authenticated
using ((select auth.uid()) = usuario_id);

-- Los usuarios solo pueden consultar sus propios pagos.
alter table public.pagos enable row level security;

revoke all on public.pagos from anon, authenticated;
grant select on public.pagos to authenticated;

drop policy if exists "Ver pagos propios" on public.pagos;
create policy "Ver pagos propios"
on public.pagos
for select
to authenticated
using ((select auth.uid()) = usuario_id);

-- Cada usuario ve las alertas generales y las dirigidas a él.
alter table public.alertas_servicio enable row level security;

revoke all on public.alertas_servicio from anon, authenticated;
grant select, update on public.alertas_servicio to authenticated;

drop policy if exists "Ver alertas propias" on public.alertas_servicio;
create policy "Ver alertas propias"
on public.alertas_servicio
for select
to authenticated
using (
  usuario_id is null
  or (select auth.uid()) = usuario_id
);

drop policy if exists "Marcar alerta propia como leida"
on public.alertas_servicio;

create policy "Marcar alerta propia como leida"
on public.alertas_servicio
for update
to authenticated
using (
  usuario_id is not null
  and (select auth.uid()) = usuario_id
)
with check (
  usuario_id is not null
  and (select auth.uid()) = usuario_id
);

-- Un usuario conectado puede leer reseñas y publicar las suyas.
alter table public.resenas_usuarios enable row level security;

revoke all on public.resenas_usuarios from anon, authenticated;
grant select, insert on public.resenas_usuarios to authenticated;

drop policy if exists "Leer resenas" on public.resenas_usuarios;
create policy "Leer resenas"
on public.resenas_usuarios
for select
to authenticated
using (true);

drop policy if exists "Crear resena propia" on public.resenas_usuarios;
create policy "Crear resena propia"
on public.resenas_usuarios
for insert
to authenticated
with check (
  (select auth.uid()) = autor_id
  and autor_id <> usuario_resenado_id
);

grant select on public.resumen_resenas_usuarios to authenticated;

-- Estas tablas solo se usan desde el backend o administración.
alter table public.configuracion_comisiones enable row level security;
alter table public.eventos_stripe enable row level security;
alter table public.automatizaciones enable row level security;

revoke all on public.configuracion_comisiones from anon, authenticated;
revoke all on public.eventos_stripe from anon, authenticated;
revoke all on public.automatizaciones from anon, authenticated;

commit;