-- ============================================================
-- CRUCE DE CUENTAS GLM · HABILITAR REPÚBLICA DOMINICANA (RD)
-- GT + TT + PA + SV + HN + PR + RD
-- ============================================================
-- Ejecutar una sola vez en el SQL Editor del Supabase empresarial.
-- Es idempotente respecto a RD: conserva datos existentes y puede
-- volver a ejecutarse si se interrumpe antes del COMMIT.

begin;

-- 1) Validar tablas requeridas y ampliar constraints de país.
do $$
declare r record;
begin
  if to_regclass('public.cruce_cuentas_bamboo_aliases') is null then raise exception 'No existe public.cruce_cuentas_bamboo_aliases.'; end if;
  if to_regclass('public.cruce_cuentas_bamboo_correcciones') is null then raise exception 'No existe public.cruce_cuentas_bamboo_correcciones.'; end if;
  if to_regclass('public.cruces_cuentas_gt_reportes') is null then raise exception 'No existe public.cruces_cuentas_gt_reportes.'; end if;
  if to_regclass('public.cruce_cuentas_usuarios_autorizados') is null then raise exception 'No existe public.cruce_cuentas_usuarios_autorizados.'; end if;

  -- Alias BambooHR.
  for r in
    select conname
    from pg_constraint
    where conrelid='public.cruce_cuentas_bamboo_aliases'::regclass
      and contype='c'
      and pg_get_constraintdef(oid) ilike '%pais%'
      and pg_get_constraintdef(oid) ilike '%GT%'
      and pg_get_constraintdef(oid) not ilike '%RD%'
  loop
    execute format('alter table public.cruce_cuentas_bamboo_aliases drop constraint %I',r.conname);
  end loop;
  alter table public.cruce_cuentas_bamboo_aliases drop constraint if exists ck_cruce_cuentas_bamboo_aliases_pais;
  alter table public.cruce_cuentas_bamboo_aliases
    add constraint ck_cruce_cuentas_bamboo_aliases_pais
    check (upper(trim(pais)) in ('GT','TT','PA','SV','HN','PR','RD'));

  -- Solicitudes/correcciones BambooHR.
  for r in
    select conname
    from pg_constraint
    where conrelid='public.cruce_cuentas_bamboo_correcciones'::regclass
      and contype='c'
      and pg_get_constraintdef(oid) ilike '%pais%'
      and pg_get_constraintdef(oid) ilike '%GT%'
      and pg_get_constraintdef(oid) not ilike '%RD%'
  loop
    execute format('alter table public.cruce_cuentas_bamboo_correcciones drop constraint %I',r.conname);
  end loop;
  alter table public.cruce_cuentas_bamboo_correcciones drop constraint if exists ck_cruce_cuentas_bamboo_correcciones_pais;
  alter table public.cruce_cuentas_bamboo_correcciones
    add constraint ck_cruce_cuentas_bamboo_correcciones_pais
    check (upper(trim(pais)) in ('GT','TT','PA','SV','HN','PR','RD'));

  -- Históricos multi-país (el nombre de la tabla es legado).
  for r in
    select conname
    from pg_constraint
    where conrelid='public.cruces_cuentas_gt_reportes'::regclass
      and contype='c'
      and pg_get_constraintdef(oid) ilike '%country%'
      and pg_get_constraintdef(oid) ilike '%GT%'
      and pg_get_constraintdef(oid) not ilike '%RD%'
  loop
    execute format('alter table public.cruces_cuentas_gt_reportes drop constraint %I',r.conname);
  end loop;
  alter table public.cruces_cuentas_gt_reportes drop constraint if exists ck_cruces_cuentas_reportes_country;
  alter table public.cruces_cuentas_gt_reportes
    add constraint ck_cruces_cuentas_reportes_country
    check (country is null or upper(trim(country)) in ('GT','TT','PA','SV','HN','PR','RD'));

  -- Accesos: text[] de países habilitados.
  alter table public.cruce_cuentas_usuarios_autorizados drop constraint if exists cruce_cuentas_paises_validos_check;
  alter table public.cruce_cuentas_usuarios_autorizados
    add constraint cruce_cuentas_paises_validos_check
    check (paises is null or paises <@ array['GT','TT','PA','SV','HN','PR','RD']::text[]);
end;
$$;

-- 2) Dar RD a todos los usuarios actualmente autorizados, preservando permisos.
update public.cruce_cuentas_usuarios_autorizados
set paises = case
  when paises is null then array['GT','TT','PA','SV','HN','PR','RD']::text[]
  when not ('RD'=any(paises)) then array_append(paises,'RD')
  else paises
end,
updated_at = now();

-- 3) Alias BambooHR activos: habilitar RD en la función compartida.
create or replace function public.cruce_cuentas_bamboo_aliases_activos(p_pais text)
returns jsonb
language sql
stable
security definer
set search_path=public
as $$
  select jsonb_build_object(
    'ok',true,
    'pais',upper(trim(coalesce(p_pais,''))),
    'aliases',coalesce(
      jsonb_agg(
        jsonb_build_object(
          'id',a.id,
          'pais',a.pais,
          'nombre_origen',a.nombre_origen,
          'nombre_origen_normalizado',a.nombre_origen_normalizado,
          'cuenta_bancaria',a.cuenta_bancaria,
          'bamboo_employee_number',a.bamboo_employee_number,
          'bamboo_employee_id',a.bamboo_employee_id,
          'bamboo_nombre',a.bamboo_nombre,
          'motivo',a.motivo,
          'activo',a.activo
        ) order by a.updated_at desc
      ) filter (where a.id is not null),
      '[]'::jsonb
    )
  )
  from public.cruce_cuentas_bamboo_aliases a
  where a.activo=true
    and a.pais=upper(trim(coalesce(p_pais,'')))
    and upper(trim(coalesce(p_pais,''))) in ('GT','TT','PA','SV','HN','PR','RD');
$$;
revoke all on function public.cruce_cuentas_bamboo_aliases_activos(text) from public,anon,authenticated;
grant execute on function public.cruce_cuentas_bamboo_aliases_activos(text) to service_role;

-- 4) Históricos paginados multi-país, incluyendo RD.
create index if not exists idx_cruces_cuentas_reportes_country_created
  on public.cruces_cuentas_gt_reportes(country,created_at desc);
create index if not exists idx_cruces_cuentas_reportes_country_status_created
  on public.cruces_cuentas_gt_reportes(country,estado,created_at desc);

create or replace function public.cruce_cuentas_get_historicos(
  p_country text,
  p_page integer default 1,
  p_page_size integer default 10,
  p_search text default null,
  p_status text default null
)
returns jsonb
language plpgsql
security definer
set search_path=public
as $$
declare
  v_country text := upper(trim(coalesce(p_country,'')));
  v_page integer := greatest(coalesce(p_page,1),1);
  v_page_size integer := least(greatest(coalesce(p_page_size,10),1),100);
  v_offset integer;
  v_search text := nullif(trim(coalesce(p_search,'')),'');
  v_status text := nullif(trim(coalesce(p_status,'')),'');
  v_total bigint;
  v_filtered_total bigint;
  v_reports jsonb;
begin
  if v_country not in ('GT','TT','PA','SV','HN','PR','RD') then
    raise exception 'País no permitido: %',v_country;
  end if;

  v_offset := (v_page-1)*v_page_size;

  select count(*) into v_total
  from public.cruces_cuentas_gt_reportes r
  where upper(coalesce(r.country,'GT'))=v_country;

  select count(*) into v_filtered_total
  from public.cruces_cuentas_gt_reportes r
  where upper(coalesce(r.country,'GT'))=v_country
    and (v_status is null or r.estado=v_status)
    and (
      v_search is null
      or coalesce(r.period_label,'') ilike '%'||v_search||'%'
      or coalesce(r.payroll_file_name,'') ilike '%'||v_search||'%'
      or coalesce(r.ejecutado_por_nombre,'') ilike '%'||v_search||'%'
      or coalesce(r.resuelto_por_nombre,'') ilike '%'||v_search||'%'
      or r.id::text ilike '%'||v_search||'%'
    );

  select coalesce(
    jsonb_agg((to_jsonb(q)-'detalle_banco_sin_bamboo') order by q.created_at desc),
    '[]'::jsonb
  ) into v_reports
  from (
    select r.*
    from public.cruces_cuentas_gt_reportes r
    where upper(coalesce(r.country,'GT'))=v_country
      and (v_status is null or r.estado=v_status)
      and (
        v_search is null
        or coalesce(r.period_label,'') ilike '%'||v_search||'%'
        or coalesce(r.payroll_file_name,'') ilike '%'||v_search||'%'
        or coalesce(r.ejecutado_por_nombre,'') ilike '%'||v_search||'%'
        or coalesce(r.resuelto_por_nombre,'') ilike '%'||v_search||'%'
        or r.id::text ilike '%'||v_search||'%'
      )
    order by r.created_at desc
    limit v_page_size offset v_offset
  ) q;

  return jsonb_build_object(
    'ok',true,
    'country',v_country,
    'page',v_page,
    'page_size',v_page_size,
    'total',v_total,
    'filtered_total',v_filtered_total,
    'total_pages',greatest(ceil(v_filtered_total::numeric/v_page_size)::integer,1),
    'reports',v_reports
  );
end;
$$;
revoke all on function public.cruce_cuentas_get_historicos(text,integer,integer,text,text) from public,anon;
grant execute on function public.cruce_cuentas_get_historicos(text,integer,integer,text,text) to authenticated;

-- 5) Detalle Banco sin BambooHR de históricos, incluyendo RD.
create or replace function public.cruce_cuentas_get_banco_sin_bamboo_reporte(
  p_report_id text,
  p_country text
)
returns jsonb
language plpgsql
stable
security definer
set search_path=public
as $$
declare
  v_country text := upper(trim(coalesce(p_country,'')));
  v_report public.cruces_cuentas_gt_reportes%rowtype;
  v_corrections jsonb := '[]'::jsonb;
begin
  if auth.uid() is null then raise exception 'No autenticado.'; end if;
  if v_country not in ('GT','TT','PA','SV','HN','PR','RD') then raise exception 'País no permitido: %',v_country; end if;

  select r.* into v_report
  from public.cruces_cuentas_gt_reportes r
  where r.id::text=trim(coalesce(p_report_id,''))
    and upper(coalesce(r.country,'GT'))=v_country
  limit 1;

  if not found then
    return jsonb_build_object('ok',false,'message','No se encontró el reporte solicitado.');
  end if;

  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'id',c.id,
        'pais',c.pais,
        'nombre_origen_principal',c.nombre_origen_principal,
        'nombres_origen',c.nombres_origen,
        'cuenta_bancaria',c.cuenta_bancaria,
        'bamboo_employee_number',c.bamboo_employee_number,
        'bamboo_nombre',c.bamboo_nombre,
        'estado',c.estado,
        'correo_enviado',c.correo_enviado,
        'correo_enviado_at',c.correo_enviado_at,
        'created_at',c.created_at
      ) order by c.created_at desc
    ),
    '[]'::jsonb
  ) into v_corrections
  from public.cruce_cuentas_bamboo_correcciones c
  where c.pais=v_country
    and (
      c.execution_id=v_report.id::text
      or (
        nullif(trim(coalesce(c.execution_id,'')),'') is null
        and nullif(trim(coalesce(c.report_url,'')),'') is not null
        and c.report_url=v_report.report_url
      )
    );

  return jsonb_build_object(
    'ok',true,
    'report',jsonb_build_object(
      'id',v_report.id,
      'country',v_report.country,
      'period_label',v_report.period_label,
      'period_start',v_report.period_start,
      'period_end',v_report.period_end,
      'report_url',v_report.report_url,
      'banco_sin_bamboo',v_report.banco_sin_bamboo
    ),
    'cases',coalesce(v_report.detalle_banco_sin_bamboo,'[]'::jsonb),
    'corrections',v_corrections
  );
end;
$$;
revoke all on function public.cruce_cuentas_get_banco_sin_bamboo_reporte(text,text) from public,anon;
grant execute on function public.cruce_cuentas_get_banco_sin_bamboo_reporte(text,text) to authenticated;

commit;

-- ============================================================
-- VALIDACIÓN (solo lectura)
-- ============================================================
select email,nombre,paises
from public.cruce_cuentas_usuarios_autorizados
order by email;

select conname, pg_get_constraintdef(oid) as definicion
from pg_constraint
where conrelid in (
  'public.cruce_cuentas_bamboo_aliases'::regclass,
  'public.cruce_cuentas_bamboo_correcciones'::regclass,
  'public.cruces_cuentas_gt_reportes'::regclass,
  'public.cruce_cuentas_usuarios_autorizados'::regclass
)
  and contype='c'
  and (pg_get_constraintdef(oid) ilike '%pais%' or pg_get_constraintdef(oid) ilike '%country%')
order by conname;

select public.cruce_cuentas_get_historicos('RD',1,10,null,null) as historicos_rd;