-- +goose Up
-- +goose StatementBegin

-- ─────────────────────────────────────────────────────────────────────────────
-- CLIENTES: adicionar campos CRM (score IA, tipo escola, segmento, responsavel)
-- A tabela base existe em 003_foundation; extendemos sem recriar.
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE clientes
  ADD COLUMN IF NOT EXISTS tipo_escola  TEXT    DEFAULT '' CHECK (tipo_escola IN ('','particular','publica','bilingue','tecnica','municipal','federal')),
  ADD COLUMN IF NOT EXISTS num_alunos   INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS segmento     TEXT    DEFAULT '',
  ADD COLUMN IF NOT EXISTS responsavel_id UUID  REFERENCES users(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS score_ia     INTEGER DEFAULT 0 CHECK (score_ia BETWEEN 0 AND 100),
  ADD COLUMN IF NOT EXISTS ltv          NUMERIC(15,4) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS ultima_compra DATE,
  ADD COLUMN IF NOT EXISTS notas        TEXT    DEFAULT '',
  ADD COLUMN IF NOT EXISTS tags         TEXT[]  DEFAULT '{}';

CREATE INDEX IF NOT EXISTS idx_clientes_responsavel ON clientes(tenant_id, responsavel_id);
CREATE INDEX IF NOT EXISTS idx_clientes_score       ON clientes(tenant_id, score_ia DESC);

-- ─────────────────────────────────────────────────────────────────────────────
-- PEDIDOS: adicionar campos CRM
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE pedidos
  ADD COLUMN IF NOT EXISTS numero          SERIAL,
  ADD COLUMN IF NOT EXISTS validade        DATE,
  ADD COLUMN IF NOT EXISTS tipo            TEXT    DEFAULT 'pedido' CHECK (tipo IN ('orcamento','pedido')),
  ADD COLUMN IF NOT EXISTS origem          TEXT    DEFAULT 'manual',
  ADD COLUMN IF NOT EXISTS responsavel_id  UUID    REFERENCES users(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS prazo_entrega   DATE,
  ADD COLUMN IF NOT EXISTS forma_pagamento TEXT    DEFAULT '',
  ADD COLUMN IF NOT EXISTS aprovado_em     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS entregue_em     TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_pedidos_responsavel ON pedidos(tenant_id, responsavel_id);
CREATE INDEX IF NOT EXISTS idx_pedidos_tipo        ON pedidos(tenant_id, tipo);

-- ─────────────────────────────────────────────────────────────────────────────
-- PEDIDO_ITENS: adicionar subtotal computado
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS pedido_itens (
    id          UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id   UUID          NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    pedido_id   UUID          NOT NULL REFERENCES pedidos(id)  ON DELETE CASCADE,
    produto_id  UUID          REFERENCES produtos(id) ON DELETE SET NULL,
    descricao   TEXT          NOT NULL DEFAULT '',
    quantidade  INTEGER       NOT NULL DEFAULT 1,
    preco_unit  NUMERIC(15,4) NOT NULL DEFAULT 0,
    subtotal    NUMERIC(15,4) GENERATED ALWAYS AS (quantidade * preco_unit) STORED,
    criado_em   TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_pedido_itens_pedido  ON pedido_itens(tenant_id, pedido_id);
CREATE INDEX IF NOT EXISTS idx_pedido_itens_produto ON pedido_itens(tenant_id, produto_id);

ALTER TABLE pedido_itens ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE tablename='pedido_itens' AND policyname='tenant_isolation'
  ) THEN
    CREATE POLICY tenant_isolation ON pedido_itens
      USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
  END IF;
END $$;

-- ─────────────────────────────────────────────────────────────────────────────
-- LEADS  (pipeline CRM)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS leads (
    id               UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id        UUID          NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    nome             TEXT          NOT NULL,
    email            CITEXT,
    telefone         TEXT,
    documento        TEXT,
    tipo             TEXT          NOT NULL DEFAULT 'pf' CHECK (tipo IN ('pf','pj')),
    tipo_escola      TEXT          DEFAULT '',
    num_alunos       INTEGER       DEFAULT 0,
    cidade           TEXT          DEFAULT '',
    uf               TEXT          DEFAULT '',
    origem           TEXT          DEFAULT 'manual',
    status           TEXT          NOT NULL DEFAULT 'novo'
                                   CHECK (status IN ('novo','contatado','qualificado','proposta','negociacao','ganho','perdido')),
    score_ia         INTEGER       DEFAULT 0 CHECK (score_ia BETWEEN 0 AND 100),
    valor_potencial  NUMERIC(15,4) DEFAULT 0,
    probabilidade    INTEGER       DEFAULT 0 CHECK (probabilidade BETWEEN 0 AND 100),
    responsavel_id   UUID          REFERENCES users(id) ON DELETE SET NULL,
    cliente_id       UUID          REFERENCES clientes(id) ON DELETE SET NULL,  -- convertido
    perdido_motivo   TEXT          DEFAULT '',
    notas            TEXT          DEFAULT '',
    proxima_acao     DATE,
    criado_em        TIMESTAMPTZ   NOT NULL DEFAULT now(),
    atualizado_em    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_leads_tenant_criado  ON leads(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_leads_status         ON leads(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_leads_responsavel    ON leads(tenant_id, responsavel_id);
CREATE INDEX IF NOT EXISTS idx_leads_score          ON leads(tenant_id, score_ia DESC);

ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON leads
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE TRIGGER trg_leads_updated BEFORE UPDATE ON leads
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- CONTATOS  (agenda CRM — pode estar vinculado a cliente, lead ou escola)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS contatos (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id      UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    nome           TEXT        NOT NULL,
    cargo          TEXT        DEFAULT '',
    email          CITEXT,
    telefone       TEXT,
    whatsapp       TEXT,
    cliente_id     UUID        REFERENCES clientes(id) ON DELETE SET NULL,
    lead_id        UUID        REFERENCES leads(id)    ON DELETE SET NULL,
    aniversario    DATE,
    preferencia_contato TEXT   DEFAULT 'whatsapp',
    perfil_ia      TEXT        DEFAULT '',  -- análise IA do perfil de decisão
    ativo          BOOLEAN     NOT NULL DEFAULT true,
    criado_em      TIMESTAMPTZ NOT NULL DEFAULT now(),
    atualizado_em  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_contatos_tenant_criado ON contatos(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_contatos_cliente       ON contatos(tenant_id, cliente_id);
CREATE INDEX IF NOT EXISTS idx_contatos_lead          ON contatos(tenant_id, lead_id);

ALTER TABLE contatos ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON contatos
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE TRIGGER trg_contatos_updated BEFORE UPDATE ON contatos
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- ATIVIDADES  (timeline CRM — calls, emails, visits, reuniões)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS atividades (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id      UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    tipo           TEXT        NOT NULL
                               CHECK (tipo IN ('ligacao','email','whatsapp','reuniao','visita','proposta','nota','sistema')),
    descricao      TEXT        NOT NULL,
    resultado      TEXT        DEFAULT '',
    entidade       TEXT        NOT NULL DEFAULT 'cliente', -- 'cliente','lead','pedido'
    entidade_id    UUID        NOT NULL,
    user_id        UUID        REFERENCES users(id) ON DELETE SET NULL,
    agendado_para  TIMESTAMPTZ,
    concluida      BOOLEAN     NOT NULL DEFAULT true,
    criado_em      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_atividades_tenant_criado ON atividades(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_atividades_entidade      ON atividades(tenant_id, entidade, entidade_id);
CREATE INDEX IF NOT EXISTS idx_atividades_user          ON atividades(tenant_id, user_id);

ALTER TABLE atividades ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON atividades
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- ─────────────────────────────────────────────────────────────────────────────
-- TAREFAS  (to-do list CRM com prioridade e vínculo)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tarefas (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id      UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    titulo         TEXT        NOT NULL,
    descricao      TEXT        DEFAULT '',
    prioridade     TEXT        NOT NULL DEFAULT 'media'
                               CHECK (prioridade IN ('alta','media','baixa')),
    status         TEXT        NOT NULL DEFAULT 'aberta'
                               CHECK (status IN ('aberta','concluida','cancelada')),
    responsavel_id UUID        REFERENCES users(id) ON DELETE SET NULL,
    entidade       TEXT        DEFAULT '',  -- 'cliente','lead','pedido'
    entidade_id    UUID,
    vence_em       TIMESTAMPTZ,
    concluida_em   TIMESTAMPTZ,
    origem_ia      BOOLEAN     NOT NULL DEFAULT false,
    criado_em      TIMESTAMPTZ NOT NULL DEFAULT now(),
    atualizado_em  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_tarefas_tenant_criado    ON tarefas(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_tarefas_responsavel      ON tarefas(tenant_id, responsavel_id);
CREATE INDEX IF NOT EXISTS idx_tarefas_status           ON tarefas(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_tarefas_vence            ON tarefas(tenant_id, vence_em);

ALTER TABLE tarefas ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tarefas
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE TRIGGER trg_tarefas_updated BEFORE UPDATE ON tarefas
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- PROPOSTAS  (orçamentos formais gerados via IA)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS propostas (
    id              UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID          NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    numero          TEXT          NOT NULL,
    cliente_id      UUID          REFERENCES clientes(id) ON DELETE SET NULL,
    lead_id         UUID          REFERENCES leads(id)    ON DELETE SET NULL,
    pedido_id       UUID          REFERENCES pedidos(id)  ON DELETE SET NULL,
    titulo          TEXT          NOT NULL DEFAULT '',
    status          TEXT          NOT NULL DEFAULT 'rascunho'
                                  CHECK (status IN ('rascunho','enviada','visualizada','aprovada','rejeitada','expirada')),
    valor_total     NUMERIC(15,4) NOT NULL DEFAULT 0,
    desconto        NUMERIC(15,4) NOT NULL DEFAULT 0,
    validade        DATE,
    conteudo_html   TEXT          DEFAULT '',  -- proposta gerada por IA
    itens           JSONB         NOT NULL DEFAULT '[]',
    enviada_em      TIMESTAMPTZ,
    visualizada_em  TIMESTAMPTZ,
    aprovada_em     TIMESTAMPTZ,
    gerada_por_ia   BOOLEAN       NOT NULL DEFAULT false,
    responsavel_id  UUID          REFERENCES users(id) ON DELETE SET NULL,
    criado_em       TIMESTAMPTZ   NOT NULL DEFAULT now(),
    atualizado_em   TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_propostas_tenant_criado ON propostas(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_propostas_status        ON propostas(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_propostas_cliente       ON propostas(tenant_id, cliente_id);

ALTER TABLE propostas ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON propostas
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE TRIGGER trg_propostas_updated BEFORE UPDATE ON propostas
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- MENSAGENS  (caixa de entrada unificada email + whatsapp)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS mensagens (
    id              UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    canal           TEXT        NOT NULL CHECK (canal IN ('email','whatsapp','sms')),
    direcao         TEXT        NOT NULL CHECK (direcao IN ('entrada','saida')),
    para            TEXT        NOT NULL,
    de              TEXT        NOT NULL DEFAULT '',
    assunto         TEXT        DEFAULT '',
    corpo           TEXT        NOT NULL DEFAULT '',
    lida            BOOLEAN     NOT NULL DEFAULT false,
    cliente_id      UUID        REFERENCES clientes(id) ON DELETE SET NULL,
    lead_id         UUID        REFERENCES leads(id)    ON DELETE SET NULL,
    proposta_id     UUID        REFERENCES propostas(id) ON DELETE SET NULL,
    aberta_em       TIMESTAMPTZ,
    clicada_em      TIMESTAMPTZ,
    user_id         UUID        REFERENCES users(id) ON DELETE SET NULL,
    criado_em       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_mensagens_tenant_criado ON mensagens(tenant_id, criado_em DESC);
CREATE INDEX IF NOT EXISTS idx_mensagens_cliente       ON mensagens(tenant_id, cliente_id);
CREATE INDEX IF NOT EXISTS idx_mensagens_canal         ON mensagens(tenant_id, canal);
CREATE INDEX IF NOT EXISTS idx_mensagens_lida          ON mensagens(tenant_id, lida);

ALTER TABLE mensagens ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON mensagens
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- ─────────────────────────────────────────────────────────────────────────────
-- CATALOGO_ITENS  (catálogo de peças para orçamentos)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS catalogo_itens (
    id              UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       UUID          NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    produto_id      UUID          REFERENCES produtos(id) ON DELETE SET NULL,
    nome            TEXT          NOT NULL,
    descricao       TEXT          DEFAULT '',
    tipo_peca       TEXT          DEFAULT '',
    preco_base      NUMERIC(15,4) NOT NULL DEFAULT 0,
    preco_varejo    NUMERIC(15,4) NOT NULL DEFAULT 0,
    margem_pct      NUMERIC(5,2)  DEFAULT 38,
    unidade         TEXT          DEFAULT 'pç',
    foto_url        TEXT          DEFAULT '',
    ativo           BOOLEAN       NOT NULL DEFAULT true,
    criado_em       TIMESTAMPTZ   NOT NULL DEFAULT now(),
    atualizado_em   TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_catalogo_tenant ON catalogo_itens(tenant_id, ativo);

ALTER TABLE catalogo_itens ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON catalogo_itens
    USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

CREATE TRIGGER trg_catalogo_updated BEFORE UPDATE ON catalogo_itens
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- CONFIGURACOES_CRM  (config por tenant: chave API IA, metas, WhatsApp etc)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS configuracoes_crm (
    id                UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id         UUID        NOT NULL UNIQUE REFERENCES tenants(id) ON DELETE CASCADE,
    meta_mensal       NUMERIC(15,4) DEFAULT 160000,
    meta_deals        INTEGER     DEFAULT 15,
    ciclo_max_dias    INTEGER     DEFAULT 15,
    taxa_conversao_alvo NUMERIC(5,2) DEFAULT 70,
    pico_meses        TEXT[]      DEFAULT '{"01","02","06","07"}',
    anthropic_key     TEXT        DEFAULT '',
    whatsapp_token    TEXT        DEFAULT '',
    email_smtp_host   TEXT        DEFAULT '',
    email_smtp_porta  INTEGER     DEFAULT 587,
    email_smtp_user   TEXT        DEFAULT '',
    email_smtp_pass   TEXT        DEFAULT '',
    notif_proposta_aberta  BOOLEAN DEFAULT true,
    notif_lead_frio        BOOLEAN DEFAULT true,
    notif_ia_oportunidade  BOOLEAN DEFAULT true,
    notif_estoque_baixo    BOOLEAN DEFAULT true,
    notif_aniversario      BOOLEAN DEFAULT false,
    criado_em         TIMESTAMPTZ NOT NULL DEFAULT now(),
    atualizado_em     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_config_crm_updated BEFORE UPDATE ON configuracoes_crm
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS configuracoes_crm CASCADE;
DROP TABLE IF EXISTS catalogo_itens    CASCADE;
DROP TABLE IF EXISTS mensagens         CASCADE;
DROP TABLE IF EXISTS propostas         CASCADE;
DROP TABLE IF EXISTS tarefas           CASCADE;
DROP TABLE IF EXISTS atividades        CASCADE;
DROP TABLE IF EXISTS contatos          CASCADE;
DROP TABLE IF EXISTS leads             CASCADE;
DROP TABLE IF EXISTS pedido_itens      CASCADE;
ALTER TABLE pedidos  DROP COLUMN IF EXISTS numero, DROP COLUMN IF EXISTS validade,
  DROP COLUMN IF EXISTS tipo, DROP COLUMN IF EXISTS origem,
  DROP COLUMN IF EXISTS responsavel_id, DROP COLUMN IF EXISTS prazo_entrega,
  DROP COLUMN IF EXISTS forma_pagamento, DROP COLUMN IF EXISTS aprovado_em,
  DROP COLUMN IF EXISTS entregue_em;
ALTER TABLE clientes DROP COLUMN IF EXISTS tipo_escola, DROP COLUMN IF EXISTS num_alunos,
  DROP COLUMN IF EXISTS segmento, DROP COLUMN IF EXISTS responsavel_id,
  DROP COLUMN IF EXISTS score_ia, DROP COLUMN IF EXISTS ltv,
  DROP COLUMN IF EXISTS ultima_compra, DROP COLUMN IF EXISTS notas,
  DROP COLUMN IF EXISTS tags;
-- +goose StatementEnd

-- Adicionar constraint unique para permitir upsert por produto_id
ALTER TABLE catalogo_itens
  ADD COLUMN IF NOT EXISTS produto_id UUID REFERENCES produtos(id) ON DELETE SET NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_catalogo_produto_tenant
  ON catalogo_itens(tenant_id, produto_id)
  WHERE produto_id IS NOT NULL;