-- ============================================================
-- CRUCE DE CUENTAS GLM · HABILITAR EL SALVADOR (SV)
-- ============================================================
-- Ejecutar UNA VEZ en el SQL Editor del Supabase empresarial.
-- Es idempotente y conserva los datos existentes de Guatemala, Trinidad y Tobago y Panamá.
-- La tabla de reportes conserva su nombre legado cruces_cuentas_gt_reportes,
-- pero funciona como tabla multi-país mediante la columna country.

begin;

-- ------------------------------------------------------------
-- 1. Checks de país en aliases/correcciones BambooHR
-- ------------------------------------------------------------
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. Ejecuta primero el SQL base de aliases BambooHR.';
  end if;

  if to_regclass('public.cruce_cuentas_bamboo_correcciones') is null then
    raise exception 'No existe public.cruce_cuentas_bamboo_correcciones. Ejecuta primero el SQL base de correcciones BambooHR.';
  end if;

  for r in
    select c.conname
    from pg_constraint c
    where c.conrelid = 'public.cruce_cuentas_bamboo_aliases'::regclass
      and c.contype = 'c'
      and pg_get_constraintdef(c.oid) ilike '%pais%'
      and pg_get_constraintdef(c.oid) ilike '%GT%'
      and pg_get_constraintdef(c.oid) ilike '%TT%'
      and pg_get_constraintdef(c.oid) not ilike '%SV%'
  loop
    execute format('alter table public.cruce_cuentas_bamboo_aliases drop constraint %I', r.conname);
  end loop;

  if not exists (
    select 1 from pg_constraint
    where conrelid = 'public.cruce_cuentas_bamboo_aliases'::regclass
      and conname = 'ck_cruce_cuentas_bamboo_aliases_pais'
  ) then
    alter table public.cruce_cuentas_bamboo_aliases
      add constraint ck_cruce_cuentas_bamboo_aliases_pais
      check (upper(trim(pais)) in ('GT', 'TT', 'PA', 'SV'));
  end if;

  for r in
    select c.conname
    from pg_constraint c
    where c.conrelid = 'public.cruce_cuentas_bamboo_correcciones'::regclass
      and c.contype = 'c'
      and pg_get_constraintdef(c.oid) ilike '%pais%'
      and pg_get_constraintdef(c.oid) ilike '%GT%'
      and pg_get_constraintdef(c.oid) ilike '%TT%'
      and pg_get_constraintdef(c.oid) not ilike '%SV%'
  loop
    execute format('alter table public.cruce_cuentas_bamboo_correcciones drop constraint %I', r.conname);
  end loop;

  if not exists (
    select 1 from pg_constraint
    where conrelid = 'public.cruce_cuentas_bamboo_correcciones'::regclass
      and conname = 'ck_cruce_cuentas_bamboo_correcciones_pais'
  ) then
    alter table public.cruce_cuentas_bamboo_correcciones
      add constraint ck_cruce_cuentas_bamboo_correcciones_pais
      check (upper(trim(pais)) in ('GT', 'TT', 'PA', 'SV'));
  end if;
end;
$$;

-- Si la tabla histórica tiene un CHECK antiguo limitado a GT/TT, se amplía.
do $$
declare
  r record;
begin
  if to_regclass('public.cruces_cuentas_gt_reportes') is null then
    raise exception 'No existe public.cruces_cuentas_gt_reportes.';
  end if;

  for r in
    select c.conname
    from pg_constraint c
    where c.conrelid = 'public.cruces_cuentas_gt_reportes'::regclass
      and c.contype = 'c'
      and pg_get_constraintdef(c.oid) ilike '%country%'
      and pg_get_constraintdef(c.oid) ilike '%GT%'
      and pg_get_constraintdef(c.oid) ilike '%TT%'
      and pg_get_constraintdef(c.oid) not ilike '%SV%'
  loop
    execute format('alter table public.cruces_cuentas_gt_reportes drop constraint %I', r.conname);
  end loop;

  if not exists (
    select 1 from pg_constraint
    where conrelid = 'public.cruces_cuentas_gt_reportes'::regclass
      and conname = 'ck_cruces_cuentas_reportes_country'
  ) then
    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'));
  end if;
end;
$$;

-- ------------------------------------------------------------
-- 2. Alias BambooHR activos: GT + TT + PA + SV
-- ------------------------------------------------------------
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');
$$;

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;

-- ------------------------------------------------------------
-- 3. Históricos paginados: GT + TT + PA + SV
-- ------------------------------------------------------------
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') 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;

-- ------------------------------------------------------------
-- 4. Detalle Banco sin Bamboo desde Históricos: GT + TT + PA + SV
-- ------------------------------------------------------------
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') 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;

-- ============================================================
-- VALIDACIONES OPCIONALES (solo lectura)
-- ============================================================
-- Debe devolver ok=true y aliases (aunque sea []).
select public.cruce_cuentas_bamboo_aliases_activos('SV');

-- Debe devolver country=SV, aunque todavía no existan reportes.
select public.cruce_cuentas_get_historicos('SV', 1, 10, null, null);

-- Verificar constraints finales.
select conrelid::regclass as tabla, 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
)
and contype = 'c'
order by 1, 2;