-- 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;