-- =====================================================================
--  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).
-- =====================================================================


-- ---------------------------------------------------------------------
-- 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';
