1381 lines
61 KiB
PL/PgSQL
1381 lines
61 KiB
PL/PgSQL
-- Seguimiento de Impuestos GLM
|
|
-- Esquema completo, seguridad, datos históricos 2026, contactos y funciones para n8n.
|
|
-- Ejecutar una sola vez en el SQL Editor de Supabase.
|
|
|
|
begin;
|
|
|
|
create extension if not exists pgcrypto;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Funciones comunes
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function public.tax_set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
new.updated_at := now();
|
|
if auth.uid() is not null then
|
|
new.updated_by := auth.uid();
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Control de acceso a la app
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_calendar_access (
|
|
email text primary key,
|
|
full_name text,
|
|
active boolean not null default true,
|
|
notes text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint tax_calendar_access_email_not_blank check (length(trim(email)) > 3)
|
|
);
|
|
|
|
create or replace function public.tax_normalize_access_email()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
new.email := lower(trim(new.email));
|
|
new.updated_at := now();
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists tax_normalize_access_email_trigger on public.tax_calendar_access;
|
|
create trigger tax_normalize_access_email_trigger
|
|
before insert or update on public.tax_calendar_access
|
|
for each row execute function public.tax_normalize_access_email();
|
|
|
|
create or replace function public.has_tax_calendar_access()
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
select exists (
|
|
select 1
|
|
from public.tax_calendar_access access
|
|
where access.email = lower(coalesce(auth.jwt() ->> 'email', ''))
|
|
and access.active = true
|
|
);
|
|
$$;
|
|
|
|
revoke all on function public.has_tax_calendar_access() from public, anon;
|
|
grant execute on function public.has_tax_calendar_access() to authenticated;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Catálogo de países
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_countries (
|
|
id uuid primary key default gen_random_uuid(),
|
|
code text not null unique,
|
|
name text not null unique,
|
|
is_regional boolean not null default false,
|
|
sort_order integer not null default 100,
|
|
active boolean not null default true,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
updated_by uuid
|
|
);
|
|
|
|
insert into public.tax_countries (code, name, is_regional, sort_order)
|
|
values
|
|
('REG', 'Regional', true, 0),
|
|
('CO', 'Colombia', false, 10),
|
|
('CR', 'Costa Rica', false, 20),
|
|
('SV', 'El Salvador', false, 30),
|
|
('GT', 'Guatemala', false, 40),
|
|
('HN', 'Honduras', false, 50),
|
|
('JM', 'Jamaica', false, 60),
|
|
('MX', 'México', false, 70),
|
|
('NI', 'Nicaragua', false, 80),
|
|
('PA', 'Panamá', false, 90),
|
|
('PR', 'Puerto Rico', false, 100),
|
|
('DO', 'República Dominicana', false, 110),
|
|
('TT', 'Trinidad y Tobago', false, 120)
|
|
on conflict (code) do update
|
|
set name = excluded.name,
|
|
is_regional = excluded.is_regional,
|
|
sort_order = excluded.sort_order,
|
|
active = true,
|
|
updated_at = now();
|
|
|
|
drop trigger if exists tax_countries_updated_at_trigger on public.tax_countries;
|
|
create trigger tax_countries_updated_at_trigger
|
|
before update on public.tax_countries
|
|
for each row execute function public.tax_set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Obligaciones tributarias
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_obligations (
|
|
id uuid primary key default gen_random_uuid(),
|
|
due_date date not null,
|
|
description text not null,
|
|
category text not null check (category in ('nomina', 'admin')),
|
|
country_id uuid not null references public.tax_countries(id),
|
|
source text not null default 'app',
|
|
is_historical boolean not null default false,
|
|
active boolean not null default true,
|
|
created_by uuid default auth.uid(),
|
|
updated_by uuid default auth.uid(),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint tax_obligations_description_not_blank check (length(trim(description)) > 0)
|
|
);
|
|
|
|
create unique index if not exists tax_obligations_unique_active_event
|
|
on public.tax_obligations (due_date, country_id, lower(description))
|
|
where active = true;
|
|
|
|
drop trigger if exists tax_obligations_updated_at_trigger on public.tax_obligations;
|
|
create trigger tax_obligations_updated_at_trigger
|
|
before update on public.tax_obligations
|
|
for each row execute function public.tax_set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Contactos y canales
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_contacts (
|
|
id uuid primary key default gen_random_uuid(),
|
|
country_id uuid not null references public.tax_countries(id),
|
|
full_name text not null,
|
|
email text,
|
|
whatsapp_number text,
|
|
job_title text,
|
|
area text not null check (area in ('admin', 'nomina', 'regional')),
|
|
email_enabled boolean not null default true,
|
|
whatsapp_enabled boolean not null default true,
|
|
active boolean not null default true,
|
|
created_by uuid default auth.uid(),
|
|
updated_by uuid default auth.uid(),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint tax_contacts_name_not_blank check (length(trim(full_name)) > 0),
|
|
constraint tax_contacts_email_when_enabled check (not email_enabled or length(trim(coalesce(email, ''))) > 3),
|
|
constraint tax_contacts_whatsapp_when_enabled check (not whatsapp_enabled or length(trim(coalesce(whatsapp_number, ''))) >= 8)
|
|
);
|
|
|
|
create or replace function public.tax_normalize_contact()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
new.full_name := trim(new.full_name);
|
|
new.email := nullif(lower(trim(coalesce(new.email, ''))), '');
|
|
new.whatsapp_number := nullif(regexp_replace(coalesce(new.whatsapp_number, ''), '[^0-9]', '', 'g'), '');
|
|
new.job_title := nullif(trim(coalesce(new.job_title, '')), '');
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists tax_contacts_normalize_trigger on public.tax_contacts;
|
|
create trigger tax_contacts_normalize_trigger
|
|
before insert or update on public.tax_contacts
|
|
for each row execute function public.tax_normalize_contact();
|
|
|
|
create unique index if not exists tax_contacts_unique_active_email
|
|
on public.tax_contacts (country_id, lower(email))
|
|
where active = true and email is not null;
|
|
|
|
drop trigger if exists tax_contacts_updated_at_trigger on public.tax_contacts;
|
|
create trigger tax_contacts_updated_at_trigger
|
|
before update on public.tax_contacts
|
|
for each row execute function public.tax_set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Bitácora de envíos: evita recordatorios duplicados
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_notification_log (
|
|
id uuid primary key default gen_random_uuid(),
|
|
obligation_id uuid not null references public.tax_obligations(id),
|
|
contact_id uuid not null references public.tax_contacts(id),
|
|
alert_date date not null,
|
|
channel text not null check (channel in ('email', 'whatsapp')),
|
|
status text not null default 'sent' check (status in ('sent', 'failed')),
|
|
provider_response jsonb,
|
|
sent_at timestamptz not null default now(),
|
|
unique (obligation_id, contact_id, alert_date, channel)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Estado de envío y bloqueo de obligaciones notificadas
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function public.tax_obligation_delivery_status()
|
|
returns table (
|
|
obligation_id uuid,
|
|
has_sent_notification boolean,
|
|
first_sent_at timestamptz,
|
|
sent_notification_count bigint
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
select
|
|
obligation.id as obligation_id,
|
|
count(log.id) filter (where log.status = 'sent') > 0 as has_sent_notification,
|
|
min(log.sent_at) filter (where log.status = 'sent') as first_sent_at,
|
|
count(log.id) filter (where log.status = 'sent') as sent_notification_count
|
|
from public.tax_obligations obligation
|
|
left join public.tax_notification_log log
|
|
on log.obligation_id = obligation.id
|
|
where obligation.active = true
|
|
and public.has_tax_calendar_access()
|
|
group by obligation.id;
|
|
$$;
|
|
|
|
revoke all on function public.tax_obligation_delivery_status()
|
|
from public, anon;
|
|
grant execute on function public.tax_obligation_delivery_status()
|
|
to authenticated;
|
|
|
|
create or replace function public.tax_prevent_sent_obligation_changes()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = old.id
|
|
and log.status = 'sent'
|
|
) and (
|
|
new.due_date is distinct from old.due_date
|
|
or new.description is distinct from old.description
|
|
or new.category is distinct from old.category
|
|
or new.country_id is distinct from old.country_id
|
|
or new.active is distinct from old.active
|
|
) then
|
|
raise exception using
|
|
errcode = 'P0001',
|
|
message = 'Esta obligación ya generó avisos y no puede modificarse ni eliminarse.';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists tax_obligations_lock_after_sent_trigger
|
|
on public.tax_obligations;
|
|
|
|
create trigger tax_obligations_lock_after_sent_trigger
|
|
before update on public.tax_obligations
|
|
for each row execute function public.tax_prevent_sent_obligation_changes();
|
|
|
|
-- Miércoles estrictamente anterior a la fecha límite.
|
|
create or replace function public.tax_previous_wednesday(p_due_date date)
|
|
returns date
|
|
language sql
|
|
immutable
|
|
set search_path = ''
|
|
as $$
|
|
select p_due_date -
|
|
case
|
|
when mod((extract(dow from p_due_date)::integer - 3 + 7), 7) = 0 then 7
|
|
else mod((extract(dow from p_due_date)::integer - 3 + 7), 7)
|
|
end;
|
|
$$;
|
|
|
|
comment on function public.tax_previous_wednesday(date) is
|
|
'Calcula el miércoles estrictamente anterior a una fecha límite.';
|
|
|
|
-- Devuelve una fila por obligación y destinatario pendiente de notificar.
|
|
create or replace function public.tax_due_reminders(p_run_date date default current_date)
|
|
returns table (
|
|
obligation_id uuid,
|
|
contact_id uuid,
|
|
alert_date date,
|
|
alert_type text,
|
|
due_date date,
|
|
description text,
|
|
category text,
|
|
country_name text,
|
|
country_code text,
|
|
contact_name text,
|
|
contact_email text,
|
|
contact_whatsapp text,
|
|
email_enabled boolean,
|
|
whatsapp_enabled boolean
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
with due as (
|
|
select
|
|
obligation.id,
|
|
obligation.due_date,
|
|
obligation.description,
|
|
obligation.category,
|
|
obligation.country_id,
|
|
country.name as country_name,
|
|
country.code as country_code,
|
|
case
|
|
when p_run_date = obligation.due_date then 'same_day'
|
|
else 'previous_wednesday'
|
|
end as alert_type
|
|
from public.tax_obligations obligation
|
|
join public.tax_countries country on country.id = obligation.country_id
|
|
where obligation.active = true
|
|
and (
|
|
p_run_date = obligation.due_date
|
|
or p_run_date = public.tax_previous_wednesday(obligation.due_date)
|
|
)
|
|
)
|
|
select
|
|
due.id as obligation_id,
|
|
contact.id as contact_id,
|
|
p_run_date as alert_date,
|
|
due.alert_type,
|
|
due.due_date,
|
|
due.description,
|
|
due.category,
|
|
due.country_name,
|
|
due.country_code,
|
|
contact.full_name as contact_name,
|
|
contact.email as contact_email,
|
|
contact.whatsapp_number as contact_whatsapp,
|
|
(
|
|
contact.email_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'email'
|
|
and log.status = 'sent'
|
|
)
|
|
) as email_enabled,
|
|
(
|
|
contact.whatsapp_enabled
|
|
and contact.whatsapp_number is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'whatsapp'
|
|
and log.status = 'sent'
|
|
)
|
|
) as whatsapp_enabled
|
|
from due
|
|
join public.tax_contacts contact
|
|
on contact.active = true
|
|
join public.tax_countries contact_country
|
|
on contact_country.id = contact.country_id
|
|
and (
|
|
contact.area = 'regional'
|
|
or (
|
|
contact_country.is_regional = true
|
|
and contact.area = due.category
|
|
)
|
|
or (
|
|
contact.country_id = due.country_id
|
|
and contact.area = due.category
|
|
)
|
|
)
|
|
where
|
|
(
|
|
contact.email_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'email'
|
|
and log.status = 'sent'
|
|
)
|
|
)
|
|
or
|
|
(
|
|
contact.whatsapp_enabled
|
|
and contact.whatsapp_number is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'whatsapp'
|
|
and log.status = 'sent'
|
|
)
|
|
)
|
|
order by due.due_date, due.country_name, contact.full_name;
|
|
$$;
|
|
|
|
revoke all on function public.tax_due_reminders(date) from public, anon, authenticated;
|
|
grant execute on function public.tax_due_reminders(date) to service_role;
|
|
|
|
create or replace function public.tax_record_notification(
|
|
p_obligation_id uuid,
|
|
p_contact_id uuid,
|
|
p_alert_date date,
|
|
p_channel text,
|
|
p_status text default 'sent',
|
|
p_provider_response jsonb default null
|
|
)
|
|
returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
insert into public.tax_notification_log (
|
|
obligation_id,
|
|
contact_id,
|
|
alert_date,
|
|
channel,
|
|
status,
|
|
provider_response,
|
|
sent_at
|
|
)
|
|
values (
|
|
p_obligation_id,
|
|
p_contact_id,
|
|
p_alert_date,
|
|
p_channel,
|
|
p_status,
|
|
p_provider_response,
|
|
now()
|
|
)
|
|
on conflict (obligation_id, contact_id, alert_date, channel)
|
|
do update set
|
|
status = excluded.status,
|
|
provider_response = excluded.provider_response,
|
|
sent_at = now();
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.tax_record_notification(uuid, uuid, date, text, text, jsonb)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.tax_record_notification(uuid, uuid, date, text, text, jsonb)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Seguridad RLS para el frontend
|
|
-- ---------------------------------------------------------------------------
|
|
alter table public.tax_calendar_access enable row level security;
|
|
alter table public.tax_countries enable row level security;
|
|
alter table public.tax_obligations enable row level security;
|
|
alter table public.tax_contacts enable row level security;
|
|
alter table public.tax_notification_log enable row level security;
|
|
|
|
revoke all on table public.tax_calendar_access from anon, authenticated;
|
|
revoke all on table public.tax_notification_log from anon, authenticated;
|
|
|
|
grant select on table public.tax_countries to authenticated;
|
|
grant select, insert, update on table public.tax_obligations to authenticated;
|
|
grant select, insert, update on table public.tax_contacts to authenticated;
|
|
|
|
drop policy if exists tax_countries_authorized_read on public.tax_countries;
|
|
create policy tax_countries_authorized_read
|
|
on public.tax_countries for select
|
|
to authenticated
|
|
using (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_obligations_authorized_read on public.tax_obligations;
|
|
create policy tax_obligations_authorized_read
|
|
on public.tax_obligations for select
|
|
to authenticated
|
|
using (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_obligations_authorized_insert on public.tax_obligations;
|
|
create policy tax_obligations_authorized_insert
|
|
on public.tax_obligations for insert
|
|
to authenticated
|
|
with check (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_obligations_authorized_update on public.tax_obligations;
|
|
create policy tax_obligations_authorized_update
|
|
on public.tax_obligations for update
|
|
to authenticated
|
|
using (public.has_tax_calendar_access())
|
|
with check (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_contacts_authorized_read on public.tax_contacts;
|
|
create policy tax_contacts_authorized_read
|
|
on public.tax_contacts for select
|
|
to authenticated
|
|
using (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_contacts_authorized_insert on public.tax_contacts;
|
|
create policy tax_contacts_authorized_insert
|
|
on public.tax_contacts for insert
|
|
to authenticated
|
|
with check (public.has_tax_calendar_access());
|
|
|
|
drop policy if exists tax_contacts_authorized_update on public.tax_contacts;
|
|
create policy tax_contacts_authorized_update
|
|
on public.tax_contacts for update
|
|
to authenticated
|
|
using (public.has_tax_calendar_access())
|
|
with check (public.has_tax_calendar_access());
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Usuarios autorizados iniciales
|
|
-- ---------------------------------------------------------------------------
|
|
insert into public.tax_calendar_access (email)
|
|
values
|
|
('ethen@gomezleemarketing.com'),
|
|
('iaracena@gomezleemarketing.com'),
|
|
('lmatos@gomezleemarketing.com'),
|
|
('asrodriguez@gomezleemarketing.com')
|
|
on conflict (email) do update
|
|
set active = true,
|
|
updated_at = now();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Contactos iniciales
|
|
-- Los números de WhatsApp quedan en formato internacional, sin el signo +.
|
|
-- ---------------------------------------------------------------------------
|
|
with contact_data(country_name, full_name, email, whatsapp_number, job_title, area) as (
|
|
values
|
|
('Guatemala', 'Narcisa Yessenia Barrera Godoy', 'administrativoguatemala2@gomezleemarketing.com', '50255581600', 'Coordinador Administrativo', 'admin'),
|
|
('República Dominicana', 'Erika Benitez', 'administrativord@gomezleemarketing.com', '18099662004', 'Coordinador Administrativo', 'admin'),
|
|
('Honduras', 'Bessy Calix Pineda', 'administrativohonduras@gomezleemarketing.com', '50495890865', 'Coordinador Administrativo', 'admin'),
|
|
('Colombia', 'Natalia Casas Avella', 'administrativocolombia@gomezleemarketing.com', '573137702391', 'Coordinador Administrativo', 'admin'),
|
|
('Costa Rica', 'Fernanda Céspedes Vargas', 'admincr@gomezleemarketing.com', '50686236444', 'Coordinador Administrativo', 'admin'),
|
|
('Nicaragua', 'Angie Chavez Silva', 'administrativonicaragua@gomezleemarketing.com', '50585263292', 'Coordinador Administrativo', 'admin'),
|
|
('Panamá', 'Jheysana Garaban Quintero', 'administrativopty2@gomezleemarketing.com', '50764548936', 'Coordinador Administrativo', 'admin'),
|
|
('República Dominicana', 'Adrianita Gonzalez Figueroa', 'agonzalez@gomezleemarketing.com', '18094328347', 'Aux. Administrativo', 'admin'),
|
|
('El Salvador', 'Noemi Elizabeth Mena Medina', 'nmena02@gomezleemarketing.com', '50378102952', 'Aux. Administrativo', 'admin'),
|
|
('Nicaragua', 'Estefani Orozco Davila', 'administrativo@gomezleemarketing.com', '50585306316', 'Coordinador Administrativo', 'admin'),
|
|
('Panamá', 'Carlos Paredes Garcia', 'cparedes@gomezleemarketing.com', '5073975475', 'Asistente Administrativo', 'admin'),
|
|
('Colombia', 'Lady Perdomo Suarez', 'administrativocolombia2@gomezleemarketing.com', '573222062462', 'Coordinador Administrativo', 'admin'),
|
|
('Guatemala', 'Carmen Estela Sierra Arias', 'administrativoguatemala@gomezleemarketing.com', '50258469211', 'Coordinador Administrativo', 'admin'),
|
|
('Guatemala', 'Shirley Velásquez González', 'administrativoguatemala3@gomezleemarketing.com', '50237727117', 'Coordinador Administrativo', 'admin'),
|
|
('Nicaragua', 'Seyling Lissamary Zambrana Reyes', 'auxiliaradmonnic@gomezleemarketing.com', '50584241616', 'Coordinador Administrativo', 'admin'),
|
|
('Regional', 'Ada Rodríguez', 'asrodriguez@gomezleemarketing.com', '50233335008', 'Coordinador Regional Administrativo', 'regional'),
|
|
('Regional', 'Zoeya Salomon Imbert', 'zsalomon@gomezleemarketing.com', '18293874206', 'Coordinador Regional Administrativo', 'regional'),
|
|
('Regional', 'Brayan Alexander Licona Oseguera', 'conciliaciones@gomezleemarketing.com', '50432077484', 'Coordinador Administrativo', 'regional'),
|
|
('Regional', 'Viviana Páramo Gonzalez', 'vparamo@gomezleemarketing.com', '573105474049', 'Subgerente Administrativo Regional', 'regional'),
|
|
('Regional', 'Mati Soto Valenzuela', 'msoto@gomezleemarketing.com', '18094037571', 'Nómina Regional', 'nomina'),
|
|
('Regional', 'Iveth Herrera', 'iherrera@gomezleemarketing.com', '50376373732', 'Nómina Regional', 'nomina')
|
|
)
|
|
insert into public.tax_contacts (
|
|
country_id,
|
|
full_name,
|
|
email,
|
|
whatsapp_number,
|
|
job_title,
|
|
area,
|
|
email_enabled,
|
|
whatsapp_enabled
|
|
)
|
|
select
|
|
country.id,
|
|
data.full_name,
|
|
lower(data.email),
|
|
data.whatsapp_number,
|
|
data.job_title,
|
|
data.area,
|
|
true,
|
|
true
|
|
from contact_data data
|
|
join public.tax_countries country on country.name = data.country_name
|
|
on conflict do nothing;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Migración de las 294 obligaciones históricas del calendario 2026
|
|
-- ---------------------------------------------------------------------------
|
|
with obligation_data(due_date, description, category, country_name) as (
|
|
values
|
|
('2026-01-02'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-01-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-01-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-01-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-01-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-01-12'::date, 'Puerto Rico: IVU Impuestos sobre ventas y uso)', 'admin', 'Puerto Rico'),
|
|
('2026-01-14'::date, 'Panamá: IVA / Municipio', 'admin', 'Panamá'),
|
|
('2026-01-14'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-01-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-01-14'::date, 'HN: INFOP', 'nomina', 'Honduras'),
|
|
('2026-01-14'::date, 'NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Nicaragua'),
|
|
('2026-01-15'::date, 'NICARAGUA: INSS e INATEC', 'nomina', 'Nicaragua'),
|
|
('2026-01-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-01-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-01-17'::date, 'Trinidad: Value Aded tax (Vat)', 'admin', 'Trinidad y Tobago'),
|
|
('2026-01-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-01-21'::date, 'COL: Retencion en la fuente', 'admin', 'Colombia'),
|
|
('2026-01-21'::date, 'COL: IVA', 'admin', 'Colombia'),
|
|
('2026-01-21'::date, 'HN: IHSS y RAP', 'nomina', 'Honduras'),
|
|
('2026-01-22'::date, 'Nicaragua Paga Matricula 2024', 'admin', 'Nicaragua'),
|
|
('2026-01-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-01-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-01-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-01-29'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-01-30'::date, 'Guatemala: IVA General; IVA Facturas Especiales; ISO (Anual)', 'admin', 'Guatemala'),
|
|
('2026-01-30'::date, 'Puerto Rico: Formularios Anuales (W-2, 1099, Reconciliaciones patronales)', 'nomina', 'Puerto Rico'),
|
|
('2026-02-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-02-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-02-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-02-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-02-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-02-12'::date, 'Panamá: IVA / NICARAGUA: INSS e INATEC', 'nomina', 'Panamá'),
|
|
('2026-02-12'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-02-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-02-13'::date, '- Guatemala: Impuestos de Retenciones /', 'admin', 'Guatemala'),
|
|
('2026-02-13'::date, '- NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Nicaragua'),
|
|
('2026-02-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-02-19'::date, 'COL: Retencion fuente (Ene)', 'admin', 'Colombia'),
|
|
('2026-02-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-02-20'::date, 'COL: ICA BIM 6', 'admin', 'Colombia'),
|
|
('2026-02-23'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-02-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-02-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-02-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-02-26'::date, 'COL: ICA Anual 2024', 'admin', 'Colombia'),
|
|
('2026-02-26'::date, 'Nicaragua-Pago de IR Anual', 'admin', 'Nicaragua'),
|
|
('2026-02-27'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-02-27'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-03-02'::date, 'Jamaica: Anual Return and beneficial ownership', 'admin', 'Jamaica'),
|
|
('2026-03-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-03-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-03-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-03-07'::date, 'Jamaica: Anual Income Tax (1st Installment)', 'nomina', 'Jamaica'),
|
|
('2026-03-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-03-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-03-12'::date, 'Panamá: IVA / NICARAGUA: INSS e INATEC', 'nomina', 'Panamá'),
|
|
('2026-03-12'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-03-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-03-13'::date, 'Guatemala: Impuestos de Retenciones / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Guatemala'),
|
|
('2026-03-16'::date, 'MX: Liquidaciones al IMSS e INFONAVIT', 'nomina', 'México'),
|
|
('2026-03-16'::date, 'MX: Impuesto Sobre nóminas CDMX', 'nomina', 'México'),
|
|
('2026-03-17'::date, 'Trinidad: Value Aded tax (Vat)', 'admin', 'Trinidad y Tobago'),
|
|
('2026-03-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-03-19'::date, 'COL: Retencion fuente (Feb)', 'admin', 'Colombia'),
|
|
('2026-03-19'::date, 'COL:IVA', 'admin', 'Colombia'),
|
|
('2026-03-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-03-20'::date, 'COL: Rete Ica Bimestre 1 (Ene-Feb)', 'admin', 'Colombia'),
|
|
('2026-03-23'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-03-24'::date, 'MX: Retenciones de ISR e IVA', 'admin', 'México'),
|
|
('2026-03-26'::date, 'Costa Rica: RENTA 2023, INS IITrimestre', 'nomina', 'Costa Rica'),
|
|
('2026-03-27'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-03-29'::date, 'Panamá: Declaracion de renta', 'admin', 'Panamá'),
|
|
('2026-04-03'::date, 'COL: ICA 1(Ene-Feb)', 'admin', 'Colombia'),
|
|
('2026-04-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-04-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-04-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-04-07'::date, 'Puerto Rico: Planilla de Contribucion sobre Ingresos', 'admin', 'Puerto Rico'),
|
|
('2026-04-07'::date, 'Puerto Rico: Quarterly Estimated Tax', 'admin', 'Puerto Rico'),
|
|
('2026-04-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-04-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR / NICARAGUA: INSS e INATEC', 'nomina', 'Honduras'),
|
|
('2026-04-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-04-13'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-04-14'::date, 'Panamá: IVA / Municipio / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Panamá'),
|
|
('2026-04-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-04-16'::date, 'SEMANA SANTA', 'admin', 'Regional'),
|
|
('2026-04-16'::date, 'MX: Liquidaciones al IMSS e INFONAVIT', 'nomina', 'México'),
|
|
('2026-04-16'::date, 'MX: Impuesto Sobre nóminas CDMX', 'nomina', 'México'),
|
|
('2026-04-17'::date, 'SEMANA SANTA', 'admin', 'Regional'),
|
|
('2026-04-18'::date, 'SEMANA SANTA', 'admin', 'Regional'),
|
|
('2026-04-20'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-04-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-04-21'::date, 'COL: Retencion fuente (Marzo) HN: RAP', 'nomina', 'Colombia'),
|
|
('2026-04-23'::date, 'MX: Retenciones de ISR e IVA', 'admin', 'México'),
|
|
('2026-04-29'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-04-29'::date, 'Trinidad: Anual Income Tax Return', 'admin', 'Trinidad y Tobago'),
|
|
('2026-05-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-05-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-05-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-05-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-05-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-05-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-05-13'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-05-14'::date, 'Panamá: IVA / Municipio / NICARAGUA: INSS e INATEC / NICARAGUA: IMPUESTO IMI ALCALDIA', 'nomina', 'Panamá'),
|
|
('2026-05-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-05-16'::date, 'MX: Liquidaciones al IMSS e INFONAVIT', 'nomina', 'México'),
|
|
('2026-05-16'::date, 'MX: Impuesto Sobre nóminas CDMX', 'nomina', 'México'),
|
|
('2026-05-17'::date, 'Trinidad: Value Aded tax (Vat)', 'admin', 'Trinidad y Tobago'),
|
|
('2026-05-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-05-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-05-20'::date, 'COL: Renta Anual (01 cuota)', 'admin', 'Colombia'),
|
|
('2026-05-20'::date, 'COL: Iva (Ene-Abr)', 'admin', 'Colombia'),
|
|
('2026-05-20'::date, 'COL: Retencion fuente (Abril)', 'admin', 'Colombia'),
|
|
('2026-05-21'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-05-22'::date, 'COL: ReteIca Bimestre 2 (Mar-Abr)', 'admin', 'Colombia'),
|
|
('2026-05-22'::date, 'MX: Retenciones de ISR e IVA', 'admin', 'México'),
|
|
('2026-05-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-05-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-05-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-05-29'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-06-01'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-06-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-06-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-06-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-06-07'::date, 'Jamaica: Anual Income Tax (2nd Installment)', 'nomina', 'Jamaica'),
|
|
('2026-06-07'::date, 'Puerto Rico: Qarterly Estimated Tax', 'admin', 'Puerto Rico'),
|
|
('2026-06-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-06-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-06-11'::date, 'Panamá: IVA / Municipio / NICARAGUA: INSS e INATEC', 'nomina', 'Panamá'),
|
|
('2026-06-11'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-06-12'::date, 'Guatemala: Impuestos de Retenciones / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Guatemala'),
|
|
('2026-06-12'::date, 'COL: ICA 2(Mar-Abr)', 'admin', 'Colombia'),
|
|
('2026-06-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-06-12'::date, 'ESV: IVA, Retencion en la fuente, AFP Crecer, AFP Confia', 'nomina', 'El Salvador'),
|
|
('2026-06-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-06-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-06-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-06-19'::date, 'COL: Retencion fuente (May)', 'admin', 'Colombia'),
|
|
('2026-06-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-06-22'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-06-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-06-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-06-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-06-29'::date, 'Panamá: Seguridad social / 1ra pardida de ISR', 'admin', 'Panamá'),
|
|
('2026-06-29'::date, 'CostaRica: 1er anticipo de Renta, INS IIIer trimestre', 'nomina', 'Costa Rica'),
|
|
('2026-06-29'::date, 'TT: Pago Estimada de renta Q1 2025', 'admin', 'Trinidad y Tobago'),
|
|
('2026-06-30'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-07-03'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-07-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-07-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-07-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-07-07'::date, 'Puerto Rico: Patente Municipal', 'admin', 'Puerto Rico'),
|
|
('2026-07-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-07-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-07-13'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-07-14'::date, 'Panamá: IVA / Municipio / Tasa Unica / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Panamá'),
|
|
('2026-07-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-07-14'::date, 'ESV: IVA, Retencion en la fuente, AFP Crecer, AFP Confia', 'nomina', 'El Salvador'),
|
|
('2026-07-16'::date, 'NICARAGUA: INSS e INATECI', 'nomina', 'Nicaragua'),
|
|
('2026-07-16'::date, 'MX: Impuesto Sobre NóminasIMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-07-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-07-17'::date, 'COL: ReteIca Bimestre 3(May-Jun)', 'admin', 'Colombia'),
|
|
('2026-07-17'::date, 'COL: Retencion fuente (Jun)', 'admin', 'Colombia'),
|
|
('2026-07-17'::date, 'COL: Renta Anual (02 cuota)', 'admin', 'Colombia'),
|
|
('2026-07-17'::date, 'Trinidad: Value Added Tax', 'admin', 'Trinidad y Tobago'),
|
|
('2026-07-20'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-07-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-07-21'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-07-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-07-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-07-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-07-29'::date, 'Panamá: Seguridad social / Planilla 03', 'admin', 'Panamá'),
|
|
('2026-07-29'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-08-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-08-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-08-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-08-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-08-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-08-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-08-13'::date, 'Panamá: IVA / Municipio / NICARAGUA: INSS e INATEC', 'nomina', 'Panamá'),
|
|
('2026-08-13'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-08-13'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-08-14'::date, 'COL: ICA 3(May-Jun) / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Colombia'),
|
|
('2026-08-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-08-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-08-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-08-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-08-21'::date, 'COL: Retencion fuente (Jul) HN: RAP', 'nomina', 'Colombia'),
|
|
('2026-08-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-08-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-08-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-08-28'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-08-31'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-09-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-09-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-09-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-09-07'::date, 'Jamaica: Anual Income Tax (3rd Installment)', 'nomina', 'Jamaica'),
|
|
('2026-09-07'::date, 'Puerto Rico: Quarterly Estimated Tax', 'admin', 'Puerto Rico'),
|
|
('2026-09-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-09-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-09-10'::date, 'NICARAGUA: INSS e INATEC', 'nomina', 'Nicaragua'),
|
|
('2026-09-11'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-09-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-09-14'::date, 'Panamá: IVA / Municipio / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Panamá'),
|
|
('2026-09-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-09-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-09-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-09-17'::date, 'COL: Iva (May-Ago)', 'admin', 'Colombia'),
|
|
('2026-09-17'::date, 'COL: Retencion Fuente (Ago)', 'admin', 'Colombia'),
|
|
('2026-09-17'::date, 'Trinidad: Value added Tax', 'admin', 'Trinidad y Tobago'),
|
|
('2026-09-18'::date, 'COL: ReteIca Bimestre 4(Jul-Ago)', 'admin', 'Colombia'),
|
|
('2026-09-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-09-21'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-09-21'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-09-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-09-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-09-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-09-29'::date, 'Panamá: Seguridad social / 2da pardida de ISR', 'admin', 'Panamá'),
|
|
('2026-09-29'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-09-29'::date, 'Costa Rica: II anticipo de Renta IVtrimestre del INS', 'nomina', 'Costa Rica'),
|
|
('2026-09-29'::date, 'Trinidad: Quarterly Installment Payments', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-10-02'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-10-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-10-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-10-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-10-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-10-09'::date, 'COL: ICA 4(Jul-Ago)', 'admin', 'Colombia'),
|
|
('2026-10-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-10-13'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-10-14'::date, 'Panamá: IVA / Municipio / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Panamá'),
|
|
('2026-10-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-10-15'::date, 'NICARAGUA: INSS e INATEC', 'nomina', 'Nicaragua'),
|
|
('2026-10-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-10-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-10-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-10-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-10-20'::date, 'COL: Retencion fuente (Sep)', 'admin', 'Colombia'),
|
|
('2026-10-21'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-10-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-10-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-10-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-10-29'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-10-30'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-11-05'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-11-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-11-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-11-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-11-09'::date, 'HN: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-11-12'::date, 'Panamá: IVA / Municipio / NICARAGUA: INSS e INATEC', 'nomina', 'Panamá'),
|
|
('2026-11-12'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-11-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-11-13'::date, 'Guatemala: Impuestos de Retenciones / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Guatemala'),
|
|
('2026-11-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-11-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-11-17'::date, 'Trinidad: Value Aded tax (Vat)', 'admin', 'Trinidad y Tobago'),
|
|
('2026-11-19'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-11-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-11-20'::date, 'COL: ReteIca Bimestre 5(Sep-Oct)', 'admin', 'Colombia'),
|
|
('2026-11-23'::date, 'COL: Retencion Fuente (Oct)', 'admin', 'Colombia'),
|
|
('2026-11-23'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-11-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-11-23'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-11-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-11-27'::date, 'Panamá: Seguridad social', 'admin', 'Panamá'),
|
|
('2026-11-30'::date, 'COL: Retencion Fuente (Oct)', 'admin', 'Colombia'),
|
|
('2026-11-30'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-11-30'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-11-30'::date, 'MX: Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-11-30'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-11-30'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-12-04'::date, 'Nicaragua-Pago de DGI', 'admin', 'Nicaragua'),
|
|
('2026-12-06'::date, 'Jamaica: GCT Monthly Statutory Deductions', 'nomina', 'Jamaica'),
|
|
('2026-12-07'::date, 'Trinidad: PAYE, NIS, and Health Surcharge', 'nomina', 'Trinidad y Tobago'),
|
|
('2026-12-07'::date, 'Jamaica: Anual Income Tax (4th Installment)', 'nomina', 'Jamaica'),
|
|
('2026-12-07'::date, 'Puerto Rico: Nomina y Retenciones Patronales', 'nomina', 'Puerto Rico'),
|
|
('2026-12-09'::date, 'Honduras: ISV, Ret 12.5%, Ret 1%, ISR', 'admin', 'Honduras'),
|
|
('2026-12-10'::date, 'NICARAGUA: INSS e INATEC', 'nomina', 'Nicaragua'),
|
|
('2026-12-11'::date, 'Guatemala: Impuestos de Retenciones', 'admin', 'Guatemala'),
|
|
('2026-12-11'::date, 'COL: ICA 5(Sep-Oct)', 'admin', 'Colombia'),
|
|
('2026-12-12'::date, 'Puerto Rico: IVU Impuesto sobre ventas y uso', 'admin', 'Puerto Rico'),
|
|
('2026-12-14'::date, 'Panamá: IVA / Municipio / NICARAGUA: IMPUESTO IMI ALCALDIA', 'admin', 'Panamá'),
|
|
('2026-12-14'::date, 'Costa Rica:IVA, Rete fuente y CCSS', 'nomina', 'Costa Rica'),
|
|
('2026-12-16'::date, 'MX: IMSS, SAR, INFONAVIT', 'nomina', 'México'),
|
|
('2026-12-16'::date, 'MX: Impuesto Sobre Nóminas', 'nomina', 'México'),
|
|
('2026-12-18'::date, 'COL: Retencion Fuente (Nov)', 'admin', 'Colombia'),
|
|
('2026-12-20'::date, 'HN: IHSS', 'nomina', 'Honduras'),
|
|
('2026-12-21'::date, 'Guatemala: IGSS', 'nomina', 'Guatemala'),
|
|
('2026-12-21'::date, 'HN: RAP', 'nomina', 'Honduras'),
|
|
('2026-12-23'::date, 'MX: Retenciones de ISR por sueldos', 'admin', 'México'),
|
|
('2026-12-23'::date, 'MX; Retenciones de ISR por honorarios y arrendamiento', 'admin', 'México'),
|
|
('2026-12-23'::date, 'MX: IVA a cargo (a favor)', 'admin', 'México'),
|
|
('2026-12-29'::date, 'Panamá: Seguridad social / 3ra pardida de ISR', 'admin', 'Panamá'),
|
|
('2026-12-29'::date, 'Guatemala: IVA General; IVA Facturas Especiales;', 'admin', 'Guatemala'),
|
|
('2026-12-29'::date, 'Costa Rica: III anticipo de Renta, Ier trimestre 2026 INS', 'nomina', 'Costa Rica'),
|
|
('2026-12-30'::date, 'Trinidad: Quarterly Installment Payment', 'nomina', 'Trinidad y Tobago')
|
|
)
|
|
insert into public.tax_obligations (
|
|
due_date,
|
|
description,
|
|
category,
|
|
country_id,
|
|
source,
|
|
is_historical
|
|
)
|
|
select
|
|
data.due_date,
|
|
data.description,
|
|
data.category,
|
|
country.id,
|
|
'excel_2026',
|
|
true
|
|
from obligation_data data
|
|
join public.tax_countries country on country.name = data.country_name
|
|
on conflict do nothing;
|
|
|
|
commit;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Operaciones administrativas frecuentes
|
|
-- ---------------------------------------------------------------------------
|
|
-- Agregar/reactivar acceso:
|
|
-- insert into public.tax_calendar_access (email, full_name)
|
|
-- values ('nuevo@gomezleemarketing.com', 'Nombre Apellido')
|
|
-- on conflict (email) do update set active = true, full_name = excluded.full_name;
|
|
|
|
-- Quitar acceso sin borrar historial:
|
|
-- update public.tax_calendar_access
|
|
-- set active = false
|
|
-- where email = 'persona@gomezleemarketing.com';
|
|
|
|
-- Prueba de recordatorios de una fecha:
|
|
-- select * from public.tax_due_reminders('2026-07-24'::date);
|
|
|
|
|
|
-- ===========================================================================
|
|
-- ACTUALIZACIÓN JULIO 2026: BambooHR, Google Calendar y fechas futuras
|
|
-- ===========================================================================
|
|
-- Seguimiento de Impuestos GLM
|
|
-- Actualización: contactos BambooHR, edición ampliada, Google Calendar y bloqueo de fechas pasadas.
|
|
-- Ejecutar una sola vez en Supabase SQL Editor antes de desplegar el frontend y activar los workflows.
|
|
|
|
begin;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Contactos: metadatos de BambooHR y tercer canal de notificación
|
|
-- ---------------------------------------------------------------------------
|
|
alter table public.tax_contacts
|
|
add column if not exists department text,
|
|
add column if not exists division text,
|
|
add column if not exists location text,
|
|
add column if not exists source text not null default 'manual',
|
|
add column if not exists bamboo_employee_id text,
|
|
add column if not exists bamboo_employee_number text,
|
|
add column if not exists bamboo_status text,
|
|
add column if not exists bamboo_synced_at timestamptz,
|
|
add column if not exists calendar_enabled boolean not null default true;
|
|
|
|
alter table public.tax_contacts
|
|
drop constraint if exists tax_contacts_area_check;
|
|
|
|
alter table public.tax_contacts
|
|
add constraint tax_contacts_area_check
|
|
check (area in ('admin', 'nomina', 'regional', 'other'));
|
|
|
|
update public.tax_contacts
|
|
set calendar_enabled = false
|
|
where email is null or length(trim(email)) <= 3;
|
|
|
|
alter table public.tax_contacts
|
|
drop constraint if exists tax_contacts_calendar_email_when_enabled;
|
|
|
|
alter table public.tax_contacts
|
|
add constraint tax_contacts_calendar_email_when_enabled
|
|
check (not calendar_enabled or length(trim(coalesce(email, ''))) > 3);
|
|
|
|
create unique index if not exists tax_contacts_unique_active_bamboo_employee
|
|
on public.tax_contacts (bamboo_employee_id)
|
|
where active = true and bamboo_employee_id is not null;
|
|
|
|
create index if not exists tax_contacts_bamboo_employee_number_idx
|
|
on public.tax_contacts (bamboo_employee_number)
|
|
where bamboo_employee_number is not null;
|
|
|
|
-- Amplía la normalización existente para los nuevos campos.
|
|
create or replace function public.tax_normalize_contact()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
new.full_name := trim(new.full_name);
|
|
new.email := nullif(lower(trim(coalesce(new.email, ''))), '');
|
|
new.whatsapp_number := nullif(regexp_replace(coalesce(new.whatsapp_number, ''), '[^0-9]', '', 'g'), '');
|
|
new.job_title := nullif(trim(coalesce(new.job_title, '')), '');
|
|
new.department := nullif(trim(coalesce(new.department, '')), '');
|
|
new.division := nullif(trim(coalesce(new.division, '')), '');
|
|
new.location := nullif(trim(coalesce(new.location, '')), '');
|
|
new.source := coalesce(nullif(lower(trim(coalesce(new.source, ''))), ''), 'manual');
|
|
new.bamboo_employee_id := nullif(trim(coalesce(new.bamboo_employee_id, '')), '');
|
|
new.bamboo_employee_number := nullif(trim(coalesce(new.bamboo_employee_number, '')), '');
|
|
new.bamboo_status := nullif(trim(coalesce(new.bamboo_status, '')), '');
|
|
|
|
if new.source = 'bamboohr' then
|
|
if tg_op = 'INSERT' then
|
|
new.bamboo_synced_at := now();
|
|
elsif new.bamboo_employee_id is distinct from old.bamboo_employee_id
|
|
or new.full_name is distinct from old.full_name
|
|
or new.email is distinct from old.email
|
|
or new.whatsapp_number is distinct from old.whatsapp_number then
|
|
new.bamboo_synced_at := now();
|
|
end if;
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- No permitir obligaciones nuevas o movidas a fechas pasadas
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function public.tax_validate_future_obligation()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
local_today date := (now() at time zone 'America/Santo_Domingo')::date;
|
|
begin
|
|
if new.active = true
|
|
and new.is_historical = false
|
|
and new.due_date < local_today then
|
|
if tg_op = 'INSERT' then
|
|
raise exception 'No se puede crear una obligación en una fecha que ya pasó.'
|
|
using errcode = '22007';
|
|
elsif new.due_date is distinct from old.due_date then
|
|
raise exception 'No se puede mover una obligación a una fecha que ya pasó.'
|
|
using errcode = '22007';
|
|
end if;
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists tax_validate_future_obligation_trigger on public.tax_obligations;
|
|
create trigger tax_validate_future_obligation_trigger
|
|
before insert or update on public.tax_obligations
|
|
for each row execute function public.tax_validate_future_obligation();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Sincronización con Google Calendar
|
|
-- Un evento de Google Calendar por obligación, con todos los destinatarios
|
|
-- aplicables como invitados.
|
|
-- ---------------------------------------------------------------------------
|
|
create table if not exists public.tax_google_calendar_events (
|
|
obligation_id uuid primary key references public.tax_obligations(id) on delete cascade,
|
|
google_event_id text,
|
|
calendar_id text not null default 'primary',
|
|
attendees jsonb not null default '[]'::jsonb,
|
|
status text not null default 'active'
|
|
check (status in ('active', 'deleted', 'failed')),
|
|
last_action text,
|
|
provider_response jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
synced_at timestamptz
|
|
);
|
|
|
|
alter table public.tax_google_calendar_events enable row level security;
|
|
revoke all on table public.tax_google_calendar_events from anon, authenticated;
|
|
grant select, insert, update, delete on table public.tax_google_calendar_events to service_role;
|
|
|
|
create or replace function public.tax_google_calendar_payload(p_obligation_id uuid)
|
|
returns table (
|
|
obligation_id uuid,
|
|
active boolean,
|
|
due_date date,
|
|
description text,
|
|
category text,
|
|
country_id uuid,
|
|
country_name text,
|
|
country_code text,
|
|
attendees jsonb,
|
|
google_event_id text,
|
|
calendar_id text,
|
|
sync_status text
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
select
|
|
obligation.id,
|
|
obligation.active,
|
|
obligation.due_date,
|
|
obligation.description,
|
|
obligation.category,
|
|
obligation.country_id,
|
|
country.name,
|
|
country.code,
|
|
coalesce(
|
|
(
|
|
select jsonb_agg(recipient.email order by recipient.email)
|
|
from (
|
|
select distinct lower(contact.email) as email
|
|
from public.tax_contacts contact
|
|
join public.tax_countries contact_country
|
|
on contact_country.id = contact.country_id
|
|
where contact.active = true
|
|
and contact.calendar_enabled = true
|
|
and contact.email is not null
|
|
and length(trim(contact.email)) > 3
|
|
and (
|
|
contact.area = 'regional'
|
|
or (
|
|
contact_country.is_regional = true
|
|
and contact.area = obligation.category
|
|
)
|
|
or (
|
|
contact.country_id = obligation.country_id
|
|
and contact.area = obligation.category
|
|
)
|
|
)
|
|
) recipient
|
|
),
|
|
'[]'::jsonb
|
|
) as attendees,
|
|
sync.google_event_id,
|
|
coalesce(sync.calendar_id, 'primary') as calendar_id,
|
|
sync.status
|
|
from public.tax_obligations obligation
|
|
join public.tax_countries country on country.id = obligation.country_id
|
|
left join public.tax_google_calendar_events sync
|
|
on sync.obligation_id = obligation.id
|
|
where obligation.id = p_obligation_id;
|
|
$$;
|
|
|
|
revoke all on function public.tax_google_calendar_payload(uuid)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.tax_google_calendar_payload(uuid)
|
|
to service_role;
|
|
|
|
create or replace function public.tax_record_google_calendar_sync(
|
|
p_obligation_id uuid,
|
|
p_google_event_id text,
|
|
p_calendar_id text default 'primary',
|
|
p_attendees jsonb default '[]'::jsonb,
|
|
p_status text default 'active',
|
|
p_last_action text default null,
|
|
p_provider_response jsonb default null
|
|
)
|
|
returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
insert into public.tax_google_calendar_events (
|
|
obligation_id,
|
|
google_event_id,
|
|
calendar_id,
|
|
attendees,
|
|
status,
|
|
last_action,
|
|
provider_response,
|
|
synced_at,
|
|
updated_at
|
|
)
|
|
values (
|
|
p_obligation_id,
|
|
nullif(trim(coalesce(p_google_event_id, '')), ''),
|
|
coalesce(nullif(trim(coalesce(p_calendar_id, '')), ''), 'primary'),
|
|
coalesce(p_attendees, '[]'::jsonb),
|
|
p_status,
|
|
p_last_action,
|
|
p_provider_response,
|
|
now(),
|
|
now()
|
|
)
|
|
on conflict (obligation_id)
|
|
do update set
|
|
google_event_id = coalesce(excluded.google_event_id, public.tax_google_calendar_events.google_event_id),
|
|
calendar_id = excluded.calendar_id,
|
|
attendees = excluded.attendees,
|
|
status = excluded.status,
|
|
last_action = excluded.last_action,
|
|
provider_response = excluded.provider_response,
|
|
synced_at = now(),
|
|
updated_at = now();
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.tax_record_google_calendar_sync(uuid, text, text, jsonb, text, text, jsonb)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.tax_record_google_calendar_sync(uuid, text, text, jsonb, text, text, jsonb)
|
|
to service_role;
|
|
|
|
commit;
|
|
|
|
-- Verificaciones opcionales:
|
|
-- select * from public.tax_google_calendar_payload('UUID_DE_OBLIGACION');
|
|
-- select * from public.tax_google_calendar_events order by updated_at desc;
|
|
|
|
|
|
|
|
-- Seguimiento de Impuestos GLM
|
|
-- Actualización incremental: retirar WhatsApp y habilitar Google Chat.
|
|
-- Ejecutar después de actualizacion_bamboohr_google_calendar.sql.
|
|
|
|
begin;
|
|
|
|
alter table public.tax_contacts
|
|
add column if not exists google_chat_enabled boolean not null default true;
|
|
|
|
alter table public.tax_contacts
|
|
alter column whatsapp_enabled set default false;
|
|
|
|
-- La nueva operación deja de usar WhatsApp. Se conserva la información histórica
|
|
-- para no destruir datos ni bitácoras anteriores.
|
|
update public.tax_contacts
|
|
set
|
|
whatsapp_enabled = false,
|
|
google_chat_enabled = case
|
|
when email_enabled = true and email is not null and length(trim(email)) > 3 then true
|
|
else false
|
|
end;
|
|
|
|
alter table public.tax_contacts
|
|
drop constraint if exists tax_contacts_google_chat_email_when_enabled;
|
|
|
|
alter table public.tax_contacts
|
|
add constraint tax_contacts_google_chat_email_when_enabled
|
|
check (not google_chat_enabled or length(trim(coalesce(email, ''))) > 3);
|
|
|
|
alter table public.tax_notification_log
|
|
drop constraint if exists tax_notification_log_channel_check;
|
|
|
|
alter table public.tax_notification_log
|
|
add constraint tax_notification_log_channel_check
|
|
check (channel in ('email', 'google_chat', 'whatsapp'));
|
|
|
|
-- CREATE OR REPLACE no permite cambiar las columnas retornadas, por eso se elimina
|
|
-- y se recrea la RPC usada por n8n.
|
|
drop function if exists public.tax_due_reminders(date);
|
|
|
|
create function public.tax_due_reminders(p_run_date date default current_date)
|
|
returns table (
|
|
obligation_id uuid,
|
|
contact_id uuid,
|
|
alert_date date,
|
|
alert_type text,
|
|
due_date date,
|
|
description text,
|
|
category text,
|
|
country_name text,
|
|
country_code text,
|
|
contact_name text,
|
|
contact_email text,
|
|
email_enabled boolean,
|
|
google_chat_enabled boolean
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
with due as (
|
|
select
|
|
obligation.id,
|
|
obligation.due_date,
|
|
obligation.description,
|
|
obligation.category,
|
|
obligation.country_id,
|
|
country.name as country_name,
|
|
country.code as country_code,
|
|
case
|
|
when p_run_date = obligation.due_date then 'same_day'
|
|
else 'previous_wednesday'
|
|
end as alert_type
|
|
from public.tax_obligations obligation
|
|
join public.tax_countries country on country.id = obligation.country_id
|
|
where obligation.active = true
|
|
and (
|
|
p_run_date = obligation.due_date
|
|
or p_run_date = public.tax_previous_wednesday(obligation.due_date)
|
|
)
|
|
)
|
|
select
|
|
due.id as obligation_id,
|
|
contact.id as contact_id,
|
|
p_run_date as alert_date,
|
|
due.alert_type,
|
|
due.due_date,
|
|
due.description,
|
|
due.category,
|
|
due.country_name,
|
|
due.country_code,
|
|
contact.full_name as contact_name,
|
|
contact.email as contact_email,
|
|
(
|
|
contact.email_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'email'
|
|
and log.status = 'sent'
|
|
)
|
|
) as email_enabled,
|
|
(
|
|
contact.google_chat_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'google_chat'
|
|
and log.status = 'sent'
|
|
)
|
|
) as google_chat_enabled
|
|
from due
|
|
join public.tax_contacts contact on contact.active = true
|
|
join public.tax_countries contact_country
|
|
on contact_country.id = contact.country_id
|
|
and (
|
|
contact.area = 'regional'
|
|
or (
|
|
contact_country.is_regional = true
|
|
and contact.area = due.category
|
|
)
|
|
or (
|
|
contact.country_id = due.country_id
|
|
and contact.area = due.category
|
|
)
|
|
)
|
|
where
|
|
(
|
|
contact.email_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'email'
|
|
and log.status = 'sent'
|
|
)
|
|
)
|
|
or
|
|
(
|
|
contact.google_chat_enabled
|
|
and contact.email is not null
|
|
and not exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = due.id
|
|
and log.contact_id = contact.id
|
|
and log.alert_date = p_run_date
|
|
and log.channel = 'google_chat'
|
|
and log.status = 'sent'
|
|
)
|
|
)
|
|
order by due.due_date, due.country_name, contact.full_name;
|
|
$$;
|
|
|
|
revoke all on function public.tax_due_reminders(date) from public, anon, authenticated;
|
|
grant execute on function public.tax_due_reminders(date) to service_role;
|
|
|
|
commit;
|
|
|
|
-- Verificación opcional:
|
|
-- select full_name, email_enabled, google_chat_enabled, calendar_enabled, whatsapp_enabled
|
|
-- from public.tax_contacts where active = true order by full_name;
|