-- ---------------------------------------------------------------------------
-- O irmao no PostgreSQL de `scripts/migracao-indicacao-porta.sql` (03/11/2026, a fatia 75,
-- REL-INDICACAO-PORTA; a I3 de `docs/f3-relatorios/spec-indicacao.md` e as respostas 264 (b) e 265 (b)
-- do dono), para a COPIA LOCAL de pe -- a coluna como o `scripts/gerar-ddl-postgres.php` a emite, e o
-- passado do formulario pela mesma regra. Os bancos da suite, o do contrato e a producao nascem do
-- `postgres/schema.sql`; a producao recebe o passado do MySQL pela carga (`scripts/migrar-dados.sh`),
-- depois do passo 0 de la.
--
--   docker exec -i franquiabv-postgres-1 sh -c 'psql -U "$POSTGRES_USER" -d franquiabv_dev -v ON_ERROR_STOP=1' \
--     < postgres/migracao-indicacao-porta.sql
--
-- AS LINHAS QUE JA ESTAO LA, e O PASSADO, como no MySQL: cada cliente com a marca "Indicação - <id>" de quem indicou e existe, e sem
-- linha ainda, vira uma linha do formulario -- a unidade do amigo, quem indicou, o amigo, a porta, a `url`
-- vazia e a `data_criacao` do amigo; sem `clientes.indicacao`, `data_venda`, `comissao`, `primeira_venda`
-- nem `data_adesao` (sem comissao). O id da marca e comparado como `numeric`: o numero grande demais para
-- `integer` nao derruba o roteiro, so nao casa com ninguem.
--
-- O INQUILINO E O DA BASE (o 1, como `postgres/semente-usuarios-horarios.sql`): as duas tabelas tem RLS
-- forcado, e a linha nova recebe o `inquilino_id` pelo padrao da coluna.
--
-- IDEMPOTENTE, numa transacao so: `ADD COLUMN IF NOT EXISTS`, e o INSERT pula quem ja tem linha. NENHUM
-- DELETE. PARA DESFAZER, so `ALTER TABLE indicacoes DROP COLUMN porta;` -- as linhas do passado ficam
-- (o `FK_CLIENTES_indicacao` cascateia para os clientes).
-- ---------------------------------------------------------------------------

-- O "Indicação" DA MARCA TEM ACENTO: o literal so casa lido em UTF-8.
SET client_encoding = 'UTF8';

BEGIN;

SET LOCAL app.inquilino_id = '1';

ALTER TABLE indicacoes ADD COLUMN IF NOT EXISTS porta varchar(12) NOT NULL DEFAULT 'aplicativo';

-- AS LINHAS QUE JA ESTAO LA, como no MySQL: a do formulario com a ligacao da 53 (c) cujo amigo ainda tem a
-- marca de quem convidou na linha volta ao formulario; o resto fica do aplicativo.
UPDATE indicacoes i
   SET porta = 'formulario'
  FROM clientes a
 WHERE a.id = i.convidado
   AND i.porta = 'aplicativo'
   AND a.origem ~ '^[[:space:]]*Indicação[[:space:]]*-[[:space:]]*[0-9]+[[:space:]]*$'
   AND i.cliente = substring(a.origem from '[0-9]+')::numeric;

INSERT INTO indicacoes (unidade, cliente, convidado, porta, url, data_convite)
SELECT a.unidade, quem.id, a.id, 'formulario', '', a.data_criacao
  FROM clientes a
  JOIN clientes quem ON quem.id = substring(a.origem from '[0-9]+')::numeric
 WHERE a.origem ~ '^[[:space:]]*Indicação[[:space:]]*-[[:space:]]*[0-9]+[[:space:]]*$'
   AND a.unidade IS NOT NULL
   AND a.data_criacao IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM indicacoes i WHERE i.convidado = a.id)
 ORDER BY a.id;

COMMIT;

-- Conferencia, como a do MySQL: as linhas por porta, e as marcas sem linha (0), de indicador inexistente e
-- sem unidade ou data, a parte.
SET app.inquilino_id = '1';

SELECT i.porta, COUNT(*) AS linhas, COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM clientes c WHERE c.indicacao = i.id)) AS com_a_ligacao
  FROM indicacoes i GROUP BY i.porta ORDER BY i.porta;

SELECT
    COUNT(*) FILTER (WHERE quem.id IS NOT NULL AND a.unidade IS NOT NULL AND a.data_criacao IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM indicacoes i WHERE i.convidado = a.id)) AS marcas_sem_linha,
    COUNT(*) FILTER (WHERE quem.id IS NULL) AS marcas_de_indicador_inexistente,
    COUNT(*) FILTER (WHERE quem.id IS NOT NULL AND (a.unidade IS NULL OR a.data_criacao IS NULL)) AS marcas_sem_unidade_ou_data
  FROM clientes a
  LEFT JOIN clientes quem ON quem.id = substring(a.origem from '[0-9]+')::numeric
 WHERE a.origem ~ '^[[:space:]]*Indicação[[:space:]]*-[[:space:]]*[0-9]+[[:space:]]*$';