glm-hub/SUPABASE-GLM-HUB-ACCESO-POR-DOMINIO.sql

-- ============================================================================
-- GLM HUB — MIGRACIÓN: ACCESO AUTOMÁTICO POR DOMINIO
-- ============================================================================
-- Ejecutar una sola vez sobre la base existente.
-- Resultado:
--   * Todo correo @gomezleemarketing.com entra automáticamente como Usuario.
--   * glm_hub_authorized_users conserva solo administradores y bloqueos.
--   * Los permisos internos de cada aplicación continúan fuera del Hub.
-- ============================================================================

begin;

comment on table public.glm_hub_authorized_users is
  'Excepciones de acceso del GLM Hub: administradores activos y usuarios bloqueados. Los usuarios corporativos normales no requieren registro.';

-- Permite que cada persona consulte únicamente su propia excepción, incluyendo
-- un posible bloqueo con is_active = false.
drop policy if exists "GLM Hub users can read only their active access"
  on public.glm_hub_authorized_users;
drop policy if exists "GLM Hub users can read their own control record"
  on public.glm_hub_authorized_users;

create policy "GLM Hub users can read their own control record"
on public.glm_hub_authorized_users
for select
to authenticated
using (
  email = lower(coalesce((select auth.jwt() ->> 'email'), ''))
);

-- Garantiza los cinco administradores definidos.
insert into public.glm_hub_authorized_users (email, full_name, role, is_active)
values
  ('jgomez@gomezleemarketing.com', 'José Leopoldo Gómez', 'admin', true),
  ('iaracena@gomezleemarketing.com', 'Isaac Aracena', 'admin', true),
  ('ethen@gomezleemarketing.com', 'Eidan Then', 'admin', true),
  ('mgomez@gomezleemarketing.com', 'Máximo Gomez', 'admin', true),
  ('lmatos@gomezleemarketing.com', 'Luis Matos', 'admin', true)
on conflict (email) do update
set
  full_name = excluded.full_name,
  role = excluded.role,
  is_active = true,
  updated_at = now();

-- Los usuarios normales ya no requieren una fila. Se conservan administradores
-- y bloqueos (member con is_active = false).
delete from public.glm_hub_authorized_users
where role = 'member'
  and is_active = true;

create or replace function public.glm_hub_is_active_user()
returns boolean
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  v_email text;
begin
  v_email := lower(coalesce(auth.jwt() ->> 'email', ''));

  return
    auth.uid() is not null
    and v_email ~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@gomezleemarketing\.com$'
    and not exists (
      select 1
      from public.glm_hub_authorized_users as access
      where access.email = v_email
        and access.is_active = false
    );
end;
$$;

create or replace function public.glm_hub_is_admin()
returns boolean
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  v_email text;
begin
  v_email := lower(coalesce(auth.jwt() ->> 'email', ''));

  return
    auth.uid() is not null
    and exists (
      select 1
      from public.glm_hub_authorized_users as access
      where access.email = v_email
        and access.role = 'admin'
        and access.is_active = true
    );
end;
$$;

alter function public.glm_hub_is_active_user() owner to postgres;
alter function public.glm_hub_is_admin() owner to postgres;

revoke all on function public.glm_hub_is_active_user() from public, anon;
revoke all on function public.glm_hub_is_admin() from public, anon;
grant execute on function public.glm_hub_is_active_user() to authenticated;
grant execute on function public.glm_hub_is_admin() to authenticated;
grant execute on function public.glm_hub_is_active_user() to service_role;
grant execute on function public.glm_hub_is_admin() to service_role;

-- Actualiza la solicitud de acceso para que un usuario corporativo no necesite
-- existir previamente en glm_hub_authorized_users.
create or replace function public.glm_hub_prepare_access_request(p_app_id uuid)
returns jsonb
language plpgsql
volatile
security definer
set search_path = ''
as $$
declare
  v_user_id uuid;
  v_email text;
  v_name text;
  v_control_name text;
  v_role text;
  v_is_active boolean;
  v_app_name text;
  v_app_category text;
  v_request_id uuid;
begin
  v_user_id := auth.uid();
  v_email := lower(coalesce(auth.jwt() ->> 'email', ''));

  if v_user_id is null or v_email = '' then
    return jsonb_build_object('ok', false, 'error', 'Tu sesión no es válida. Cierra sesión e inicia nuevamente.');
  end if;

  if v_email !~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@gomezleemarketing\.com$' then
    return jsonb_build_object('ok', false, 'error', 'Debes utilizar una cuenta corporativa de GomezLee Marketing.');
  end if;

  select access.full_name, access.role, access.is_active
    into v_control_name, v_role, v_is_active
  from public.glm_hub_authorized_users as access
  where access.email = v_email
  limit 1;

  if found and v_is_active = false then
    return jsonb_build_object('ok', false, 'error', 'Tu acceso al GLM Hub está deshabilitado. Contacta a IT Support.');
  end if;

  if coalesce(v_role, 'member') = 'admin' then
    return jsonb_build_object('ok', false, 'error', 'Los administradores no necesitan solicitar acceso desde el Hub.');
  end if;

  v_name := coalesce(
    nullif(btrim(v_control_name), ''),
    nullif(btrim(auth.jwt() -> 'user_metadata' ->> 'full_name'), ''),
    nullif(btrim(auth.jwt() -> 'user_metadata' ->> 'name'), ''),
    split_part(v_email, '@', 1)
  );

  select app.name, app.category
    into v_app_name, v_app_category
  from public.glm_hub_apps as app
  where app.id = p_app_id
    and app.visibility = 'published'
  limit 1;

  if not found then
    return jsonb_build_object('ok', false, 'error', 'La aplicación seleccionada ya no está publicada.');
  end if;

  if exists (
    select 1
    from public.glm_hub_access_requests as request
    where request.requester_email = v_email
      and request.app_id = p_app_id
      and request.status = 'pending'
  ) then
    return jsonb_build_object(
      'ok', false,
      'code', 'PENDING_EXISTS',
      'error', 'Ya existe una solicitud pendiente para esta aplicación.'
    );
  end if;

  insert into public.glm_hub_access_requests (
    requester_user_id,
    requester_name,
    requester_email,
    app_id,
    app_name,
    app_category
  )
  values (
    v_user_id,
    v_name,
    v_email,
    p_app_id,
    v_app_name,
    v_app_category
  )
  returning id into v_request_id;

  return jsonb_build_object(
    'ok', true,
    'requestId', v_request_id,
    'requesterName', v_name,
    'requesterEmail', v_email,
    'appName', v_app_name,
    'appCategory', v_app_category
  );
end;
$$;

alter function public.glm_hub_prepare_access_request(uuid) owner to postgres;
revoke all on function public.glm_hub_prepare_access_request(uuid) from public, anon;
grant execute on function public.glm_hub_prepare_access_request(uuid) to authenticated;
grant execute on function public.glm_hub_prepare_access_request(uuid) to service_role;

notify pgrst, 'reload schema';

commit;

-- ==========================================================================
-- OPERACIONES FUTURAS
-- ==========================================================================

-- HACER ADMINISTRADOR:
-- insert into public.glm_hub_authorized_users (email, full_name, role, is_active)
-- values ('usuario@gomezleemarketing.com', 'Nombre del usuario', 'admin', true)
-- on conflict (email) do update
-- set full_name = excluded.full_name, role = 'admin', is_active = true, updated_at = now();

-- DEVOLVER A USUARIO NORMAL:
-- delete from public.glm_hub_authorized_users
-- where email = 'usuario@gomezleemarketing.com';

-- BLOQUEAR USUARIO:
-- insert into public.glm_hub_authorized_users (email, full_name, role, is_active)
-- values ('usuario@gomezleemarketing.com', 'Nombre del usuario', 'member', false)
-- on conflict (email) do update
-- set full_name = excluded.full_name, role = 'member', is_active = false, updated_at = now();

-- DESBLOQUEAR USUARIO:
-- delete from public.glm_hub_authorized_users
-- where email = 'usuario@gomezleemarketing.com'
--   and role = 'member';