-- ============================================================================
-- GLM HUB · VERSIÓN FINAL
-- LED DE DISPONIBILIDAD + REGISTRO CENTRAL DE ACCESOS + JEFE INMEDIATO
-- GomezLee Marketing
-- Fecha: 2026-08-14
--
-- OBJETIVO
-- 1) Mantener a Isaac + José Leopoldo + Máximo como aprobadores base.
-- 2) Agregar al jefe inmediato desde empleados_glm.supervisor_email.
-- 3) Crear UNA tabla central que el Hub usa únicamente para saber si debe mostrar
--    "Disponible" o "Sin acceso".
-- 4) NO modifica el login ni los permisos reales de ninguna aplicación.
-- 5) NO modifica claves, credenciales ni configuración de EasyPanel/n8n.
--
-- TABLA CENTRAL
-- public.glm_hub_app_access
--   app_name = nombre visible de la aplicación en el Hub
--   email    = correo autorizado
--   email='*' significa que TODOS los usuarios @gomezleemarketing.com tienen acceso
--   is_active=false permite desactivar el registro sin borrarlo
--
-- OPERACIÓN FUTURA
-- - App abierta a todo GLM: agregar una fila con app_name + email='*'.
-- - App restringida: agregar una fila por usuario con app_name + su correo.
-- - Revocar: borrar la fila o cambiar is_active a false.
-- ============================================================================

begin;

-- --------------------------------------------------------------------------
-- 1. APROBADORES DINÁMICOS: 3 BASE + JEFE INMEDIATO
-- --------------------------------------------------------------------------

alter table public.glm_hub_access_request_approvers
  drop constraint if exists glm_hub_access_request_approvers_email_check;

alter table public.glm_hub_access_request_approvers
  drop constraint if exists glm_hub_access_request_approvers_email_normalized_check;

alter table public.glm_hub_access_request_approvers
  add constraint glm_hub_access_request_approvers_email_normalized_check
  check (
    approver_email = lower(btrim(approver_email))
    and approver_email ~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9.-]+\.[a-z]{2,}$'
  );

create or replace function public.glm_hub_issue_access_request_tokens(p_request_id uuid)
returns jsonb
language plpgsql
volatile
security definer
set search_path = ''
as $$
declare
  v_request public.glm_hub_access_requests%rowtype;
begin
  select request.*
    into v_request
  from public.glm_hub_access_requests as request
  where request.id = p_request_id
    and request.status = 'pending'
  for update;

  if not found then
    return jsonb_build_object('ok', false, 'error', 'La solicitud no existe o ya fue atendida.');
  end if;

  with supervisor_candidate as (
    select
      coalesce(
        nullif(btrim(employee.supervisor), ''),
        nullif(btrim(supervisor_employee.name), ''),
        split_part(lower(btrim(employee.supervisor_email)), '@', 1)
      ) as approver_name,
      lower(btrim(employee.supervisor_email)) as approver_email,
      40 as priority
    from public.empleados_glm as employee
    left join public.empleados_glm as supervisor_employee
      on lower(btrim(supervisor_employee.work_email)) = lower(btrim(employee.supervisor_email))
    where lower(btrim(employee.work_email)) = v_request.requester_email
      and nullif(btrim(employee.supervisor_email), '') is not null
    order by
      case when employee.status::text = 'Active' then 0 else 1 end,
      employee.id
    limit 1
  ),
  candidate_approvers as (
    select 'Isaac Aracena'::text as approver_name,
           'iaracena@gomezleemarketing.com'::text as approver_email,
           10 as priority
    union all
    select 'José Leopoldo Gómez', 'jgomez@gomezleemarketing.com', 20
    union all
    select 'Máximo Gómez', 'mgomez@gomezleemarketing.com', 30
    union all
    select approver_name, approver_email, priority
    from supervisor_candidate
  ),
  deduplicated as (
    select distinct on (approver_email)
      approver_name,
      approver_email,
      priority
    from candidate_approvers
    where approver_email is not null
      and approver_email <> ''
      and approver_email <> v_request.requester_email
      and approver_email ~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9.-]+\.[a-z]{2,}$'
    order by approver_email, priority
  ),
  tokenized as (
    select
      approver_name,
      approver_email,
      priority,
      encode(extensions.gen_random_bytes(32), 'hex') as decision_token
    from deduplicated
  )
  insert into public.glm_hub_access_request_approvers (
    request_id,
    approver_name,
    approver_email,
    decision_token,
    decision_token_hash
  )
  select
    p_request_id,
    tokenized.approver_name,
    tokenized.approver_email,
    tokenized.decision_token,
    encode(extensions.digest(tokenized.decision_token, 'sha256'), 'hex')
  from tokenized
  on conflict (request_id, approver_email) do nothing;

  return jsonb_build_object(
    'ok', true,
    'requestId', v_request.id,
    'requesterName', v_request.requester_name,
    'requesterEmail', v_request.requester_email,
    'appName', v_request.app_name,
    'appCategory', v_request.app_category,
    'approvers', (
      select coalesce(
        jsonb_agg(
          jsonb_build_object(
            'name', approver.approver_name,
            'email', approver.approver_email,
            'token', approver.decision_token
          )
          order by
            case approver.approver_email
              when 'iaracena@gomezleemarketing.com' then 1
              when 'jgomez@gomezleemarketing.com' then 2
              when 'mgomez@gomezleemarketing.com' then 3
              else 4
            end,
            approver.approver_email
        ),
        '[]'::jsonb
      )
      from public.glm_hub_access_request_approvers as approver
      where approver.request_id = p_request_id
    )
  );
end;
$$;

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

-- --------------------------------------------------------------------------
-- 2. TABLA CENTRAL DE ACCESOS DEL HUB
-- --------------------------------------------------------------------------
-- Esta tabla es INFORMATIVA para el Hub. No sustituye el login/seguridad real
-- de cada aplicación.
--
-- app_id es rellenado automáticamente a partir de app_name, de forma que desde
-- el Table Editor basta con escribir el nombre de la app y el correo.
-- --------------------------------------------------------------------------

create table if not exists public.glm_hub_app_access (
  id uuid primary key default extensions.gen_random_uuid(),
  app_name text not null,
  email text not null,
  is_active boolean not null default true,
  app_id uuid references public.glm_hub_apps(id) on delete cascade,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint glm_hub_app_access_email_check
    check (
      email = '*'
      or email ~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9.-]+\.[a-z]{2,}$'
    )
);

comment on table public.glm_hub_app_access is
  'Registro central informativo de accesos mostrado por GLM Hub. No reemplaza los permisos reales de cada aplicación.';
comment on column public.glm_hub_app_access.app_name is
  'Nombre de la aplicación tal como aparece en glm_hub_apps.';
comment on column public.glm_hub_app_access.email is
  'Correo autorizado. El valor * significa todos los usuarios GLM autenticados.';
comment on column public.glm_hub_app_access.is_active is
  'TRUE = acceso mostrado como Disponible; FALSE = registro desactivado.';
comment on column public.glm_hub_app_access.app_id is
  'Se completa automáticamente usando app_name.';

create unique index if not exists glm_hub_app_access_app_email_uidx
  on public.glm_hub_app_access (app_id, email);

create index if not exists glm_hub_app_access_email_idx
  on public.glm_hub_app_access (email)
  where is_active = true;

-- Normaliza correos y permite trabajar por nombre de aplicación desde Table Editor.
create or replace function public.glm_hub_app_access_prepare_row()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_app_id uuid;
  v_app_name text;
begin
  new.app_name := btrim(coalesce(new.app_name, ''));
  new.email := lower(btrim(coalesce(new.email, '')));

  if new.app_name = '' then
    raise exception 'Debes indicar app_name.';
  end if;

  if new.email = '' then
    raise exception 'Debes indicar email. Usa * si la aplicación está disponible para todo GLM.';
  end if;

  -- Si el usuario cambió app_name manualmente, ese nombre manda y se resuelve
  -- nuevamente el UUID. En INSERT también permite dejar app_id vacío.
  if tg_op = 'INSERT' then
    select app.id, app.name
      into v_app_id, v_app_name
    from public.glm_hub_apps as app
    where lower(btrim(app.name)) = lower(new.app_name)
    order by app.created_at
    limit 1;

    if v_app_id is null then
      raise exception 'No existe una aplicación en GLM Hub llamada "%".', new.app_name;
    end if;

    new.app_id := v_app_id;
    new.app_name := v_app_name;
  elsif new.app_id is null or new.app_name is distinct from old.app_name then
    select app.id, app.name
      into v_app_id, v_app_name
    from public.glm_hub_apps as app
    where lower(btrim(app.name)) = lower(new.app_name)
    order by app.created_at
    limit 1;

    if v_app_id is null then
      raise exception 'No existe una aplicación en GLM Hub llamada "%".', new.app_name;
    end if;

    new.app_id := v_app_id;
    new.app_name := v_app_name;
  else
    select app.name
      into v_app_name
    from public.glm_hub_apps as app
    where app.id = new.app_id;

    if v_app_name is null then
      raise exception 'El app_id indicado no existe en glm_hub_apps.';
    end if;

    new.app_name := v_app_name;
  end if;

  new.updated_at := now();
  return new;
end;
$$;

drop trigger if exists glm_hub_app_access_prepare_row_trigger
  on public.glm_hub_app_access;

create trigger glm_hub_app_access_prepare_row_trigger
before insert or update on public.glm_hub_app_access
for each row
execute function public.glm_hub_app_access_prepare_row();

-- Si un administrador cambia el nombre de una app en el Hub, el nombre visible
-- de los registros centrales se mantiene sincronizado por app_id.
create or replace function public.glm_hub_sync_access_app_name()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if new.name is distinct from old.name then
    update public.glm_hub_app_access
       set app_name = new.name,
           updated_at = now()
     where app_id = new.id;
  end if;
  return new;
end;
$$;

drop trigger if exists glm_hub_sync_access_app_name_trigger
  on public.glm_hub_apps;

create trigger glm_hub_sync_access_app_name_trigger
after update of name on public.glm_hub_apps
for each row
execute function public.glm_hub_sync_access_app_name();

-- Nadie necesita leer esta tabla directamente desde el navegador. El frontend
-- consulta únicamente el RPC seguro glm_hub_get_my_app_access().
alter table public.glm_hub_app_access enable row level security;
revoke all on table public.glm_hub_app_access from anon, authenticated;
grant all on table public.glm_hub_app_access to service_role;

-- --------------------------------------------------------------------------
-- 3. CARGA INICIAL DE LOS ACCESOS ACTUALES
-- --------------------------------------------------------------------------
-- A) Apps abiertas para cualquier usuario GLM -> una sola fila con email='*'.
-- --------------------------------------------------------------------------

insert into public.glm_hub_app_access (app_id, app_name, email, is_active)
select app.id, app.name, '*', true
from public.glm_hub_apps as app
where lower(btrim(app.name)) in (
  'bamboohr',
  'cdc brief',
  'cruce de seguridad social - honduras',
  'cruce de seguridad social - guatemala',
  'cruce de seguridad social - costa rica',
  'cruce de seguridad social - república dominicana',
  'cruce de seguridad social - el salvador',
  'validación de ir - nicaragua',
  'glm id card generator'
)
on conflict (app_id, email)
do update set
  app_name = excluded.app_name,
  is_active = true,
  updated_at = now();

-- B) CDC Project Management -> snapshot inicial desde tablero_cdc_allowed_users.
insert into public.glm_hub_app_access (app_id, app_name, email, is_active)
select
  app.id,
  app.name,
  lower(btrim(allowed_user.email)),
  true
from public.glm_hub_apps as app
cross join public.tablero_cdc_allowed_users as allowed_user
where lower(btrim(app.name)) = 'cdc project management'
  and nullif(btrim(allowed_user.email), '') is not null
  and coalesce(allowed_user.is_active, false) = true
on conflict (app_id, email)
do update set
  app_name = excluded.app_name,
  is_active = true,
  updated_at = now();

-- C) Portal de Verificación de Nóminas -> snapshot inicial desde la tabla actual.
insert into public.glm_hub_app_access (app_id, app_name, email, is_active)
select
  app.id,
  app.name,
  lower(btrim(allowed_user.email)),
  true
from public.glm_hub_apps as app
cross join public.cruce_cuentas_usuarios_autorizados as allowed_user
where lower(btrim(app.name)) = 'portal de verificación de nóminas'
  and nullif(btrim(allowed_user.email), '') is not null
on conflict (app_id, email)
do update set
  app_name = excluded.app_name,
  is_active = true,
  updated_at = now();

-- D) Seguimiento de Impuestos GLM -> snapshot inicial desde tax_calendar_access.
insert into public.glm_hub_app_access (app_id, app_name, email, is_active)
select
  app.id,
  app.name,
  lower(btrim(allowed_user.email)),
  true
from public.glm_hub_apps as app
cross join public.tax_calendar_access as allowed_user
where lower(btrim(app.name)) = 'seguimiento de impuestos glm'
  and nullif(btrim(allowed_user.email), '') is not null
  and coalesce(allowed_user.active, false) = true
on conflict (app_id, email)
do update set
  app_name = excluded.app_name,
  is_active = true,
  updated_at = now();

-- --------------------------------------------------------------------------
-- 4. RPC FINAL PARA EL LED DEL HUB
-- --------------------------------------------------------------------------
-- El Hub ya NO consulta las tablas particulares de cada aplicación.
-- Solo lee este registro central.
-- --------------------------------------------------------------------------

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

  if auth.uid() is null or v_email = '' then
    return;
  end if;

  -- El GLM Hub continúa reservado al dominio corporativo principal.
  if v_email !~ '^[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@gomezleemarketing\.com$' then
    return;
  end if;

  return query
  select
    app.id,
    app.name,
    exists (
      select 1
      from public.glm_hub_app_access as access_row
      where access_row.app_id = app.id
        and access_row.is_active = true
        and (
          access_row.email = '*'
          or access_row.email = v_email
        )
    ) as has_access
  from public.glm_hub_apps as app
  where app.visibility = 'published'
  order by app.name;
end;
$$;

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

commit;

-- ============================================================================
-- VERIFICACIONES OPCIONALES DESPUÉS DE EJECUTAR
-- ============================================================================

-- Ver toda la matriz central:
-- select app_name, email, is_active
-- from public.glm_hub_app_access
-- order by app_name, email;

-- Agregar acceso individual a una futura app restringida DESDE SQL:
-- insert into public.glm_hub_app_access (app_name, email)
-- values ('Nombre exacto de la aplicación', 'usuario@gomezleemarketing.com');
--
-- En el Table Editor puedes hacer exactamente lo mismo dejando app_id vacío.

-- Marcar una futura app como abierta para todo GLM:
-- insert into public.glm_hub_app_access (app_name, email)
-- values ('Nombre exacto de la aplicación', '*');

-- Revocar sin borrar historial:
-- update public.glm_hub_app_access
-- set is_active = false
-- where lower(app_name) = lower('Nombre exacto de la aplicación')
--   and email = 'usuario@gomezleemarketing.com';