-- +goose Up
-- +goose StatementBegin

-- Novos campos na tabela clientes para a Base CRM
ALTER TABLE clientes
  ADD COLUMN IF NOT EXISTS segmento         TEXT DEFAULT 'escola',
  ADD COLUMN IF NOT EXISTS recorrente       BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS score_crm        INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS prox_renovacao   DATE,
  ADD COLUMN IF NOT EXISTS notas            TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS contato_nome     TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS contato_email    TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS contato_whatsapp TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS num_alunos       INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS num_funcionarios INTEGER DEFAULT 0;

-- Novos campos na tabela leads para segmentos expandidos
ALTER TABLE leads
  ADD COLUMN IF NOT EXISTS segmento          TEXT DEFAULT 'escola',
  ADD COLUMN IF NOT EXISTS nome_fantasia     TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS contato_nome      TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS contato_cargo     TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS site              TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS populacao         INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS secretaria        TEXT DEFAULT '',
  ADD COLUMN IF NOT EXISTS num_servidores    INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS num_funcionarios  INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS ramo_atividade    TEXT DEFAULT '';

-- Índice para busca por segmento
CREATE INDEX IF NOT EXISTS idx_clientes_segmento ON clientes(tenant_id, segmento);
CREATE INDEX IF NOT EXISTS idx_clientes_score    ON clientes(tenant_id, score_crm DESC);
CREATE INDEX IF NOT EXISTS idx_clientes_renovacao ON clientes(tenant_id, prox_renovacao);
CREATE INDEX IF NOT EXISTS idx_leads_segmento    ON leads(tenant_id, segmento);

-- Atualiza score_crm baseado no historico de pedidos existentes
UPDATE clientes cl
SET
  score_crm = LEAST(100, (
    SELECT COUNT(*) * 10 FROM pedidos p
    WHERE p.cliente_id = cl.id AND p.status IN ('aprovado','entregue')
  )),
  recorrente = (
    SELECT COUNT(*) >= 2 FROM pedidos p
    WHERE p.cliente_id = cl.id AND p.status IN ('aprovado','entregue')
  )
WHERE cl.tenant_id IS NOT NULL;

-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
ALTER TABLE clientes
  DROP COLUMN IF EXISTS segmento,
  DROP COLUMN IF EXISTS recorrente,
  DROP COLUMN IF EXISTS score_crm,
  DROP COLUMN IF EXISTS prox_renovacao,
  DROP COLUMN IF EXISTS notas,
  DROP COLUMN IF EXISTS contato_nome,
  DROP COLUMN IF EXISTS contato_email,
  DROP COLUMN IF EXISTS contato_whatsapp,
  DROP COLUMN IF EXISTS num_alunos,
  DROP COLUMN IF EXISTS num_funcionarios;
ALTER TABLE leads
  DROP COLUMN IF EXISTS segmento,
  DROP COLUMN IF EXISTS nome_fantasia,
  DROP COLUMN IF EXISTS contato_nome,
  DROP COLUMN IF EXISTS contato_cargo,
  DROP COLUMN IF EXISTS site,
  DROP COLUMN IF EXISTS populacao,
  DROP COLUMN IF EXISTS secretaria,
  DROP COLUMN IF EXISTS num_servidores,
  DROP COLUMN IF EXISTS num_funcionarios,
  DROP COLUMN IF EXISTS ramo_atividade;
-- +goose StatementEnd