-- Regiões como fonte única da verdade para parcelas.
-- Migração idempotente: pode ser executada novamente sem duplicar dados.

begin;

alter table public.parcelas
  add column if not exists region_id uuid;

-- Normalize os nomes existentes antes da deduplicação e do índice único.
update public.regioes
set nome = trim(regexp_replace(nome, '\\s+', ' ', 'g'))
where nome is distinct from trim(regexp_replace(nome, '\\s+', ' ', 'g'));

-- Se só existir o nome legado para um usuário, reutilize o próprio registro.
update public.regioes legacy
set nome = 'Copar''s'
where lower(trim(legacy.nome)) = lower('Copal 3')
  and not exists (
    select 1
    from public.regioes canonical
    where canonical.user_id = legacy.user_id
      and lower(trim(canonical.nome)) = lower('Copar''s')
  );

-- Preserve nomes legados reais criando apenas os cadastros que ainda não existem.
insert into public.regioes (user_id, nome, status)
select distinct
  parcela.user_id,
  case
    when lower(trim(parcela.regiao)) = lower('Copal 3') then 'Copar''s'
    else trim(regexp_replace(parcela.regiao, '\s+', ' ', 'g'))
  end,
  'ativo'
from public.parcelas parcela
where nullif(trim(coalesce(parcela.regiao, '')), '') is not null
  and not exists (
    select 1
    from public.regioes region
    where region.user_id = parcela.user_id
      and lower(trim(region.nome)) = lower(
        case
          when lower(trim(parcela.regiao)) = lower('Copal 3') then 'Copar''s'
          else trim(regexp_replace(parcela.regiao, '\s+', ' ', 'g'))
        end
      )
  );

-- Vincule textos legados ao registro oficial, inclusive Copal 3 -> Copar's.
update public.parcelas parcela
set region_id = region.id
from public.regioes region
where parcela.user_id = region.user_id
  and parcela.region_id is null
  and lower(trim(region.nome)) = lower(
    case when lower(trim(coalesce(parcela.regiao, ''))) = lower('Copal 3')
      then 'Copar''s'
      else trim(coalesce(parcela.regiao, ''))
    end
  );

-- Quando os dois nomes coexistirem, mova todos os vínculos para Copar's.
update public.parcelas parcela
set region_id = canonical.id
from public.regioes legacy
join public.regioes canonical
  on canonical.user_id = legacy.user_id
 and lower(trim(canonical.nome)) = lower('Copar''s')
where parcela.user_id = legacy.user_id
  and parcela.region_id = legacy.id
  and lower(trim(legacy.nome)) = lower('Copal 3');

update public.clima_registros clima
set regiao_id = canonical.id
from public.regioes legacy
join public.regioes canonical
  on canonical.user_id = legacy.user_id
 and lower(trim(canonical.nome)) = lower('Copar''s')
where clima.regiao_id = legacy.id
  and lower(trim(legacy.nome)) = lower('Copal 3');

-- Campo textual permanece somente como espelho temporário de compatibilidade.
update public.parcelas parcela
set regiao = region.nome
from public.regioes region
where parcela.region_id = region.id
  and parcela.regiao is distinct from region.nome;

-- Inative o registro legado duplicado somente depois de migrar os vínculos.
update public.regioes legacy
set status = 'inativo'
where lower(trim(legacy.nome)) = lower('Copal 3')
  and exists (
    select 1
    from public.regioes canonical
    where canonical.user_id = legacy.user_id
      and canonical.id <> legacy.id
      and lower(trim(canonical.nome)) = lower('Copar''s')
  )
  and not exists (select 1 from public.parcelas p where p.region_id = legacy.id)
  and not exists (select 1 from public.clima_registros c where c.regiao_id = legacy.id);

-- Consolide quaisquer outras duplicidades normalizadas antes do índice único.
with ranked as (
  select
    id,
    first_value(id) over (
      partition by user_id, lower(trim(nome))
      order by case when status = 'ativo' then 0 else 1 end, created_at, id
    ) as keeper_id
  from public.regioes
)
update public.parcelas parcela
set region_id = ranked.keeper_id
from ranked
where parcela.region_id = ranked.id
  and ranked.id <> ranked.keeper_id;

with ranked as (
  select
    id,
    first_value(id) over (
      partition by user_id, lower(trim(nome))
      order by case when status = 'ativo' then 0 else 1 end, created_at, id
    ) as keeper_id
  from public.regioes
)
update public.clima_registros clima
set regiao_id = ranked.keeper_id
from ranked
where clima.regiao_id = ranked.id
  and ranked.id <> ranked.keeper_id;

with ranked as (
  select
    id,
    row_number() over (
      partition by user_id, lower(trim(nome))
      order by case when status = 'ativo' then 0 else 1 end, created_at, id
    ) as position
  from public.regioes
)
update public.regioes region
set status = 'inativo'
from ranked
where region.id = ranked.id
  and ranked.position > 1;

create index if not exists idx_parcelas_region_id on public.parcelas(region_id);

-- Novas gravações não podem voltar a criar parcelas sem região. O NOT VALID
-- preserva registros legados sem texto suficiente para inferir um vínculo.
do $$
begin
  if not exists (
    select 1 from pg_constraint where conname = 'parcelas_region_id_required'
  ) then
    alter table public.parcelas
      add constraint parcelas_region_id_required check (region_id is not null) not valid;
  end if;
end $$;

do $$
begin
  if not exists (
    select 1 from pg_constraint where conname = 'parcelas_region_id_fkey'
  ) then
    alter table public.parcelas
      add constraint parcelas_region_id_fkey
      foreign key (region_id) references public.regioes(id)
      on update cascade on delete restrict;
  end if;
end $$;

-- Nomes ativos não podem se repetir ignorando caixa e espaços externos.
create unique index if not exists regioes_user_nome_normalizado_ativo_uidx
  on public.regioes(user_id, lower(trim(nome)))
  where status = 'ativo';

commit;