-- ===================================================================== -- Sistema de Cobranças de Hospedagem | Nova Digital Web -- Schema MySQL 8.0+ / MariaDB 10.6+ -- Charset utf8mb4 / collation utf8mb4_unicode_ci -- Todos os valores monetários são armazenados em CENTAVOS (BIGINT). -- ===================================================================== SET NAMES utf8mb4; SET time_zone = '-03:00'; -- --------------------------------------------------------------------- -- USUÁRIOS DO PAINEL -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS usuarios ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, nome VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, senha_hash VARCHAR(255) NOT NULL, papel ENUM('admin','operador','leitura') NOT NULL DEFAULT 'operador', ativo TINYINT(1) NOT NULL DEFAULT 1, deve_trocar_senha TINYINT(1) NOT NULL DEFAULT 0, falhas_login SMALLINT UNSIGNED NOT NULL DEFAULT 0, bloqueado_ate DATETIME NULL, ultimo_login DATETIME NULL, ultimo_ip VARBINARY(16) NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_usuarios_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- TENTATIVAS DE LOGIN (rate limiting / auditoria de acesso) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS login_tentativas ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(190) NULL, ip VARBINARY(16) NOT NULL, sucesso TINYINT(1) NOT NULL DEFAULT 0, user_agent VARCHAR(255) NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_login_ip_data (ip, criado_em), KEY ix_login_email_data (email, criado_em) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- CONFIGURAÇÕES (valores sensíveis são gravados criptografados) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS configuracoes ( chave VARCHAR(80) NOT NULL, valor TEXT NULL, criptografado TINYINT(1) NOT NULL DEFAULT 0, descricao VARCHAR(255) NULL, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (chave) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- CLIENTES (sacado / pagador) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS clientes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, tipo_pessoa ENUM('FISICA','JURIDICA') NOT NULL DEFAULT 'JURIDICA', nome VARCHAR(160) NOT NULL, -- razão social / nome completo nome_fantasia VARCHAR(160) NULL, cpf_cnpj VARCHAR(14) NOT NULL, -- somente dígitos email VARCHAR(190) NOT NULL, email_copia VARCHAR(190) NULL, telefone VARCHAR(15) NULL, -- somente dígitos, com DDD (ex.: 21974109696) whatsapp VARCHAR(15) NULL, -- somente dígitos, com DDI+DDD (ex.: 5521974109696) cep VARCHAR(8) NULL, logradouro VARCHAR(160) NULL, numero VARCHAR(20) NULL, complemento VARCHAR(80) NULL, bairro VARCHAR(80) NULL, cidade VARCHAR(80) NULL, uf CHAR(2) NULL, dominio VARCHAR(190) NULL, -- domínio hospedado (referência comercial) observacoes TEXT NULL, ativo TINYINT(1) NOT NULL DEFAULT 1, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_clientes_doc (cpf_cnpj), KEY ix_clientes_nome (nome), KEY ix_clientes_ativo (ativo) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- PLANOS DE HOSPEDAGEM (opcional — atalho para preencher contratos) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS planos ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, nome VARCHAR(120) NOT NULL, descricao VARCHAR(255) NULL, valor_centavos BIGINT UNSIGNED NOT NULL, ciclo_meses TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1=mensal, 3=trimestral, 6, 12=anual ativo TINYINT(1) NOT NULL DEFAULT 1, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_planos_nome (nome) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- CONTRATOS (a assinatura recorrente do cliente) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contratos ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, cliente_id INT UNSIGNED NOT NULL, plano_id INT UNSIGNED NULL, descricao VARCHAR(160) NOT NULL, -- vai na mensagem do boleto valor_centavos BIGINT UNSIGNED NOT NULL, ciclo_meses TINYINT UNSIGNED NOT NULL DEFAULT 1, dia_vencimento TINYINT UNSIGNED NOT NULL DEFAULT 10, -- 1..28 (ou 31 = último dia) dias_antecedencia TINYINT UNSIGNED NOT NULL DEFAULT 7, -- emite N dias antes do vencimento data_inicio DATE NOT NULL, data_fim DATE NULL, proxima_competencia CHAR(7) NOT NULL, -- 'AAAA-MM' da próxima cobrança a gerar multa_percentual DECIMAL(5,2) NOT NULL DEFAULT 2.00, juros_mensal_percent DECIMAL(5,2) NOT NULL DEFAULT 1.00, desconto_centavos BIGINT UNSIGNED NOT NULL DEFAULT 0, desconto_ate_dias TINYINT UNSIGNED NOT NULL DEFAULT 0, -- desconto se pago até N dias antes do venc. enviar_email TINYINT(1) NOT NULL DEFAULT 1, enviar_whatsapp TINYINT(1) NOT NULL DEFAULT 1, status ENUM('ativo','pausado','cancelado','encerrado') NOT NULL DEFAULT 'ativo', observacoes TEXT NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_contratos_cliente (cliente_id), KEY ix_contratos_status (status, proxima_competencia), CONSTRAINT fk_contratos_cliente FOREIGN KEY (cliente_id) REFERENCES clientes (id) ON DELETE RESTRICT, CONSTRAINT fk_contratos_plano FOREIGN KEY (plano_id) REFERENCES planos (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- COBRANÇAS (cada boleto/Pix emitido no Banco Inter) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS cobrancas ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, cliente_id INT UNSIGNED NOT NULL, contrato_id INT UNSIGNED NULL, seu_numero VARCHAR(15) NOT NULL, -- identificador interno enviado ao Inter codigo_solicitacao CHAR(36) NULL, -- UUID devolvido pelo Inter competencia CHAR(7) NULL, -- 'AAAA-MM' descricao VARCHAR(160) NOT NULL, valor_centavos BIGINT UNSIGNED NOT NULL, data_vencimento DATE NOT NULL, data_emissao DATE NULL, situacao ENUM( 'PENDENTE_EMISSAO','FALHA_EMISSAO','EM_PROCESSAMENTO', 'A_RECEBER','ATRASADO','RECEBIDO','MARCADO_RECEBIDO', 'CANCELADO','EXPIRADO' ) NOT NULL DEFAULT 'PENDENTE_EMISSAO', linha_digitavel VARCHAR(60) NULL, codigo_barras VARCHAR(60) NULL, nosso_numero VARCHAR(30) NULL, pix_txid VARCHAR(80) NULL, pix_copia_cola TEXT NULL, valor_pago_centavos BIGINT UNSIGNED NULL, data_pagamento DATE NULL, origem_pagamento VARCHAR(40) NULL, -- BOLETO / PIX / MANUAL pdf_arquivo VARCHAR(255) NULL, -- caminho relativo em storage/boletos tentativas_emissao TINYINT UNSIGNED NOT NULL DEFAULT 0, ultimo_erro VARCHAR(500) NULL, origem ENUM('recorrencia','avulsa') NOT NULL DEFAULT 'recorrencia', criado_por INT UNSIGNED NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Vale 1 enquanto a cobrança "conta" para a competência e NULL quando ela -- deixa de contar (cancelada ou expirada sem pagamento). Como o MySQL -- permite NULLs repetidos numa chave única, isso trava a duplicidade sem -- impedir a reemissão de um mês cuja cobrança anterior morreu. ocupa_competencia TINYINT UNSIGNED GENERATED ALWAYS AS (IF(situacao IN ('CANCELADO','EXPIRADO'), NULL, 1)) STORED, PRIMARY KEY (id), UNIQUE KEY uq_cobrancas_seu_numero (seu_numero), UNIQUE KEY uq_cobrancas_codigo (codigo_solicitacao), UNIQUE KEY uq_cobrancas_contrato_comp (contrato_id, competencia, ocupa_competencia), KEY ix_cobrancas_cliente (cliente_id), KEY ix_cobrancas_situacao_venc (situacao, data_vencimento), KEY ix_cobrancas_venc (data_vencimento), KEY ix_cobrancas_pagamento (data_pagamento), CONSTRAINT fk_cobrancas_cliente FOREIGN KEY (cliente_id) REFERENCES clientes (id) ON DELETE RESTRICT, CONSTRAINT fk_cobrancas_contrato FOREIGN KEY (contrato_id) REFERENCES contratos (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- FILA DE ENVIOS (email + whatsapp) com retry -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS envios ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, cobranca_id BIGINT UNSIGNED NOT NULL, canal ENUM('email','whatsapp') NOT NULL, tipo ENUM('emissao','lembrete','vencimento','atraso','reenvio','confirmacao') NOT NULL DEFAULT 'emissao', destino VARCHAR(190) NOT NULL, assunto VARCHAR(190) NULL, status ENUM('fila','enviando','enviado','erro','cancelado') NOT NULL DEFAULT 'fila', tentativas TINYINT UNSIGNED NOT NULL DEFAULT 0, agendado_para DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, iniciado_em DATETIME NULL, -- momento em que o worker travou o item enviado_em DATETIME NULL, provider_id VARCHAR(120) NULL, -- messageid do WhatsApp / message-id do SMTP erro VARCHAR(500) NULL, criado_por INT UNSIGNED NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_envios_fila (status, agendado_para), KEY ix_envios_cobranca (cobranca_id, canal, tipo), CONSTRAINT fk_envios_cobranca FOREIGN KEY (cobranca_id) REFERENCES cobrancas (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- EVENTOS DE WEBHOOK RECEBIDOS (Banco Inter) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS webhook_eventos ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, origem VARCHAR(30) NOT NULL DEFAULT 'inter', hash_payload CHAR(64) NOT NULL, -- sha256 do corpo, evita reprocessar duplicata payload MEDIUMTEXT NOT NULL, ip VARBINARY(16) NULL, processado TINYINT(1) NOT NULL DEFAULT 0, erro VARCHAR(500) NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_webhook_hash (hash_payload), KEY ix_webhook_processado (processado, criado_em) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- AUDITORIA -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS auditoria ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, usuario_id INT UNSIGNED NULL, acao VARCHAR(60) NOT NULL, entidade VARCHAR(40) NULL, entidade_id VARCHAR(40) NULL, detalhes TEXT NULL, ip VARBINARY(16) NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_auditoria_data (criado_em), KEY ix_auditoria_entidade (entidade, entidade_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- LOG DE CHAMADAS ÀS APIS EXTERNAS (diagnóstico) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS api_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, servico VARCHAR(20) NOT NULL, -- inter | uazapi | smtp metodo VARCHAR(10) NOT NULL, endpoint VARCHAR(255) NOT NULL, http_status SMALLINT NULL, duracao_ms INT UNSIGNED NULL, sucesso TINYINT(1) NOT NULL DEFAULT 0, resumo VARCHAR(500) NULL, -- resposta truncada e sem dados sensíveis criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_apilog_servico (servico, criado_em) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SEQUENCIAL DO "seuNumero" (evita colisão sob concorrência) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sequencias ( nome VARCHAR(40) NOT NULL, valor BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (nome) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO sequencias (nome, valor) VALUES ('cobranca', 1000) ON DUPLICATE KEY UPDATE nome = nome; -- --------------------------------------------------------------------- -- VIEWS DE RELATÓRIO -- --------------------------------------------------------------------- CREATE OR REPLACE VIEW vw_cobrancas_completo AS SELECT c.id, c.seu_numero, c.codigo_solicitacao, c.competencia, c.descricao, c.valor_centavos, c.valor_pago_centavos, c.data_vencimento, c.data_emissao, c.data_pagamento, c.situacao, c.origem, c.pdf_arquivo, c.linha_digitavel, c.codigo_barras, c.nosso_numero, c.pix_txid, c.pix_copia_cola, c.origem_pagamento, c.tentativas_emissao, c.ultimo_erro, c.contrato_id, cl.id AS cliente_id, cl.nome AS cliente_nome, cl.cpf_cnpj, cl.email AS cliente_email, cl.email_copia AS cliente_email_copia, cl.whatsapp AS cliente_whatsapp, cl.telefone AS cliente_telefone, cl.dominio, cl.tipo_pessoa, DATEDIFF(CURDATE(), c.data_vencimento) AS dias_atraso FROM cobrancas c JOIN clientes cl ON cl.id = c.cliente_id; CREATE OR REPLACE VIEW vw_mrr_ativo AS SELECT SUM(ROUND(valor_centavos / ciclo_meses)) AS mrr_centavos, COUNT(*) AS contratos_ativos FROM contratos WHERE status = 'ativo';