-- ---------------------------------------------------------------------------
-- Normalização de telefone brasileiro para E.164, com o nono dígito.
--
--   docker compose exec -T postgres psql -U franquiabv -d franquiabv_dev \
--     -v ON_ERROR_STOP=1 -q < postgres/funcoes-telefone.sql
--
-- Usada em dois momentos: na migração, para normalizar os 242 mil cadastros de
-- uma vez; e em toda gravação, para o mesmo número digitado de formas
-- diferentes não virar duas pessoas.
--
-- A base tem cadastros desde junho de 2015, e o nono dígito só terminou de ser
-- implantado no Brasil em 2016 — então há celular antigo, de 8 dígitos, guardado
-- aí dentro.
-- ---------------------------------------------------------------------------

SET search_path = bvip, public;

CREATE OR REPLACE FUNCTION normalizar_telefone(entrada text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    digitos   text;
    ddd       text;
    assinante text;
BEGIN
    IF entrada IS NULL THEN
        RETURN NULL;
    END IF;

    -- Fora tudo que não é dígito: máscara, espaço, traço, parêntese, o "+".
    digitos := regexp_replace(entrada, '[^0-9]', '', 'g');

    -- Código do país. Aceita com e sem, mas só descarta o 55 quando o que sobra
    -- tem tamanho de número brasileiro — senão um fixo de São Paulo começando
    -- com 55 (como 5512-3456) perderia os dois primeiros dígitos.
    IF length(digitos) IN (12, 13) AND left(digitos, 2) = '55' THEN
        digitos := substr(digitos, 3);
    END IF;

    IF length(digitos) NOT IN (10, 11) THEN
        RETURN NULL;   -- não é número brasileiro reconhecível
    END IF;

    ddd       := left(digitos, 2);
    assinante := substr(digitos, 3);

    -- DDD brasileiro válido vai de 11 a 99, e nenhum termina em 0.
    IF ddd::integer < 11 OR right(ddd, 1) = '0' THEN
        RETURN NULL;
    END IF;

    -- ESTA É A REGRA QUE IMPORTA.
    --
    -- Com 8 dígitos, o número pode ser celular antigo ou telefone fixo, e o
    -- primeiro dígito do assinante é o que distingue:
    --
    --   2, 3, 4, 5  -> fixo. Prefixar 9 aqui ESTRAGA o número.
    --   6, 7, 8, 9  -> celular no formato antigo. Recebe o nono dígito.
    --
    -- Aplicar o 9 a tudo que tem 8 dígitos é o erro clássico desta migração, e
    -- ele é silencioso: o número continua parecendo válido e simplesmente não
    -- toca em ninguém.
    IF length(assinante) = 8 THEN
        IF left(assinante, 1) IN ('6', '7', '8', '9') THEN
            assinante := '9' || assinante;
        END IF;

    -- Com 9 dígitos já é celular no formato atual, e tem que começar com 9.
    -- Qualquer outra coisa é dígito a mais por engano de digitação.
    ELSIF length(assinante) = 9 AND left(assinante, 1) <> '9' THEN
        RETURN NULL;
    END IF;

    -- Dígito repetido é preenchimento, não telefone. Na base de produção,
    -- +554799999999 aparece em 193 cadastros e +5548999999999 em 31 — e os dois
    -- são formalmente válidos, então nenhuma validação de formato os pega.
    --
    -- Isto resolve a parte automática do D32. A outra parte não tem regra: o
    -- número de balcão da unidade (+554733692043, 355 cadastros) é um telefone
    -- perfeitamente real, e só sai por lista.
    IF assinante ~ '^(.)\1*$' THEN
        RETURN NULL;
    END IF;

    RETURN '+55' || ddd || assinante;
END;
$$;

COMMENT ON FUNCTION normalizar_telefone(text) IS
    'Normaliza telefone brasileiro para E.164 aplicando o nono dígito quando o '
    'assinante começa com 6-9. Devolve NULL para o que não for reconhecível.';

-- ---------------------------------------------------------------------------
-- A chave de identidade é DDD + 9 dígitos, ou seja, celular.
--
-- Fixo tem DDD + 8 e nunca vira DDD + 9. Isso não é limitação da função, é da
-- realidade: o vínculo entre pessoa e telefone só se cria quando a própria
-- pessoa confirma um código, e código chega por SMS ou WhatsApp. Um fixo não
-- recebe nenhum dos dois.
--
-- Por isso o fixo continua guardado em `clientes` como contato — a recepção
-- liga para ele —, mas não entra em `pessoas_contatos` como identidade.
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION normalizar_celular(entrada text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE
        -- +55 (3) + DDD (2) + assinante (9) = 14 caracteres, e o assinante
        -- começa com 9. É exatamente o formato DDD + 9 dígitos.
        WHEN length(normalizar_telefone(entrada)) = 14
         AND substr(normalizar_telefone(entrada), 6, 1) = '9'
        THEN normalizar_telefone(entrada)
        ELSE NULL
    END;
$$;

COMMENT ON FUNCTION normalizar_celular(text) IS
    'Devolve o celular em E.164 (DDD + 9 dígitos) ou NULL se não for celular. '
    'É a função que define a chave de identidade da pessoa.';

-- ---------------------------------------------------------------------------
-- Testes. Falham com código diferente de zero, então servem no CI.
-- ---------------------------------------------------------------------------

DO $$
DECLARE
    caso   record;
    obtido text;
    falhas integer := 0;
BEGIN
    FOR caso IN
        SELECT * FROM (VALUES
            -- celular atual, em todos os formatos que aparecem na base
            ('(11) 98765-4321',   '+5511987654321', 'celular com máscara'),
            ('11987654321',       '+5511987654321', 'celular só dígitos'),
            ('+55 11 98765-4321', '+5511987654321', 'celular com código do país'),
            ('5511987654321',     '+5511987654321', 'código do país sem o mais'),
            ('  11 98765 4321 ',  '+5511987654321', 'celular com espaços'),

            -- celular antigo: ganha o nono dígito
            ('(11) 8765-4321',    '+5511987654321', 'celular antigo começando com 8'),
            ('1176543210',        '+5511976543210', 'celular antigo começando com 7'),
            ('1196543210',        '+5511996543210', 'celular antigo começando com 9'),
            ('1166543210',        '+5511966543210', 'celular antigo começando com 6'),

            -- FIXO: não pode ganhar o nono dígito
            ('(11) 3765-4321',    '+551137654321',  'fixo começando com 3'),
            ('1123456789',        '+551123456789',  'fixo começando com 2'),
            ('1145678901',        '+551145678901',  'fixo começando com 4'),
            ('1155678901',        '+551155678901',  'fixo começando com 5'),

            -- fixo cujo assinante começa com 55, para conferir que o descarte
            -- do código do país não o mutila
            ('1155123456',        '+551155123456',  'fixo com assinante 55'),

            -- o que não dá para reconhecer vira nulo, em vez de virar palpite
            -- Formatos reais medidos na produção em 19/08/2026. A base já
            -- guarda em E.164: 91,2% com 14 caracteres (DDD + 9 dígitos) e
            -- 8,1% com 13 (assinante de 8, que pode ser fixo ou celular antigo).
            ('+5547991744313',    '+5547991744313', 'formato dominante, já correto'),
            ('+554191774547',     '+5541991774547', 'celular antigo em E.164 ganha o nono dígito'),
            ('+554733692043',     '+554733692043',  'fixo em E.164 permanece com 8'),
            ('+55',               NULL, 'só o código do país — 320 cadastros assim'),
            ('+554799999999',     NULL, 'dígito repetido: preenchimento, 193 cadastros'),
            ('+5548999999999',    NULL, 'dígito repetido com nove dígitos'),
            ('+5500000000000',    NULL, 'zeros'),
            ('+5548012345678',    NULL, 'sequência'),

            ('12345',             NULL, 'curto demais'),
            ('',                  NULL, 'vazio'),
            ('0000000000',        NULL, 'DDD inválido'),
            ('1012345678',        NULL, 'DDD terminando em zero'),
            ('11123456789',       NULL, 'nove dígitos sem começar com 9'),
            ('123456789012345',   NULL, 'longo demais')
        ) AS t(entrada, esperado, descricao)
    LOOP
        obtido := normalizar_telefone(caso.entrada);

        IF obtido IS DISTINCT FROM caso.esperado THEN
            RAISE WARNING 'FALHOU  %  entrada=%  esperado=%  obtido=%',
                caso.descricao, caso.entrada, coalesce(caso.esperado, 'NULL'), coalesce(obtido, 'NULL');
            falhas := falhas + 1;
        END IF;
    END LOOP;

    -- A chave de identidade: só celular, em DDD + 9 dígitos.
    FOR caso IN
        SELECT * FROM (VALUES
            ('(11) 98765-4321', '+5511987654321', 'celular atual é identidade'),
            ('(11) 8765-4321',  '+5511987654321', 'celular antigo vira identidade com o nono dígito'),
            ('(11) 3765-4321',  NULL, 'fixo NÃO é identidade — não recebe código'),
            ('1123456789',      NULL, 'fixo começando com 2 não é identidade'),
            ('12345',           NULL, 'irreconhecível não é identidade')
        ) AS t(entrada, esperado, descricao)
    LOOP
        obtido := normalizar_celular(caso.entrada);

        IF obtido IS DISTINCT FROM caso.esperado THEN
            RAISE WARNING 'FALHOU  %  entrada=%  esperado=%  obtido=%',
                caso.descricao, caso.entrada, coalesce(caso.esperado, 'NULL'), coalesce(obtido, 'NULL');
            falhas := falhas + 1;
        END IF;
    END LOOP;

    IF falhas > 0 THEN
        RAISE EXCEPTION '% caso(s) de normalização falharam', falhas;
    END IF;

    RAISE NOTICE 'ok  34 casos de normalização de telefone';
END $$;

SELECT 'normalizar_telefone: todos os casos passaram' AS resultado;