-- Fluxo transacional de assinaturas, cobranças e webhooks Asaas.
-- Compatível com os registros legados de contas, faturas e pagamentos.
-- Executar antes de publicar o código desta revisão.


ALTER TABLE planos
    ADD COLUMN IF NOT EXISTS ativo CHAR(1) NOT NULL DEFAULT 'S' AFTER periodo,
    ADD COLUMN IF NOT EXISTS permite_teste_gratis CHAR(1) NOT NULL DEFAULT 'N' AFTER ativo,
    ADD COLUMN IF NOT EXISTS dias_teste_gratis SMALLINT UNSIGNED NOT NULL DEFAULT 7 AFTER permite_teste_gratis,
    ADD INDEX IF NOT EXISTS idx_planos_ativo (ativo, deletado);

ALTER TABLE contas_usuarios
    ADD COLUMN IF NOT EXISTS trial_iniciado_em DATETIME NULL AFTER customer_id,
    ADD COLUMN IF NOT EXISTS trial_finalizado_em DATETIME NULL AFTER trial_iniciado_em,
    ADD COLUMN IF NOT EXISTS trial_utilizado_em DATETIME NULL AFTER trial_finalizado_em,
    ADD INDEX IF NOT EXISTS idx_contas_customer_asaas (customer_id),
    ADD INDEX IF NOT EXISTS idx_contas_vencimento_status (vencimento, status);

ALTER TABLE preregistro
    ADD COLUMN IF NOT EXISTS solicita_teste_gratis CHAR(1) NOT NULL DEFAULT 'N' AFTER id_plano;

CREATE TABLE IF NOT EXISTS assinaturas (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_conta INT NOT NULL,
    id_plano INT NOT NULL,
    asaas_subscription_id VARCHAR(100) NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'PENDING_PAYMENT',
    forma_pagamento VARCHAR(30) NULL,
    renovacao_automatica TINYINT(1) NOT NULL DEFAULT 0,
    trial_inicio DATETIME NULL,
    trial_fim DATETIME NULL,
    periodo_atual_inicio DATETIME NULL,
    periodo_atual_fim DATETIME NULL,
    proxima_cobranca DATE NULL,
    cancelar_ao_fim_periodo TINYINT(1) NOT NULL DEFAULT 0,
    cancelado_em DATETIME NULL,
    expirado_em DATETIME NULL,
    cartao_bandeira VARCHAR(30) NULL,
    cartao_final VARCHAR(4) NULL,
    dthr_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dthr_atualizacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_assinaturas_conta (id_conta),
    UNIQUE KEY uq_assinaturas_asaas (asaas_subscription_id),
    KEY idx_assinaturas_plano (id_plano),
    KEY idx_assinaturas_status (status),
    KEY idx_assinaturas_proxima_cobranca (proxima_cobranca),
    CONSTRAINT fk_assinaturas_conta FOREIGN KEY (id_conta) REFERENCES contas_usuarios (id),
    CONSTRAINT fk_assinaturas_plano FOREIGN KEY (id_plano) REFERENCES planos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE contas_usuario_faturas
    ADD COLUMN IF NOT EXISTS id_assinatura BIGINT UNSIGNED NULL AFTER id_conta,
    ADD COLUMN IF NOT EXISTS id_plano INT NULL AFTER id_assinatura,
    ADD COLUMN IF NOT EXISTS descricao VARCHAR(255) NULL AFTER valor,
    ADD COLUMN IF NOT EXISTS periodo_inicio DATETIME NULL AFTER vencimento,
    ADD COLUMN IF NOT EXISTS periodo_fim DATETIME NULL AFTER periodo_inicio,
    ADD COLUMN IF NOT EXISTS pago_em DATETIME NULL AFTER status,
    ADD COLUMN IF NOT EXISTS cancelado_em DATETIME NULL AFTER pago_em,
    ADD COLUMN IF NOT EXISTS dthr_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS dthr_atualizacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS conta_pendente_unica INT
        GENERATED ALWAYS AS (CASE WHEN status = 'PE' AND deletado = 'N' THEN id_conta ELSE NULL END) PERSISTENT,
    ADD UNIQUE INDEX IF NOT EXISTS uq_fatura_pendente_conta (conta_pendente_unica),
    ADD INDEX IF NOT EXISTS idx_faturas_assinatura (id_assinatura),
    ADD INDEX IF NOT EXISTS idx_faturas_plano (id_plano),
    ADD INDEX IF NOT EXISTS idx_faturas_status_vencimento (status, vencimento);

ALTER TABLE contas_faturas_pagamentos
    ADD COLUMN IF NOT EXISTS id_assinatura BIGINT UNSIGNED NULL AFTER id_conta,
    ADD COLUMN IF NOT EXISTS id_plano INT NULL AFTER id_assinatura,
    ADD COLUMN IF NOT EXISTS forma_pagamento VARCHAR(30) NULL AFTER tipo,
    ADD COLUMN IF NOT EXISTS referencia_externa VARCHAR(150) NULL AFTER forma_pagamento,
    ADD COLUMN IF NOT EXISTS chave_idempotencia VARCHAR(191) NULL AFTER referencia_externa,
    ADD COLUMN IF NOT EXISTS vencimento DATE NULL AFTER valor,
    ADD COLUMN IF NOT EXISTS invoice_url VARCHAR(500) NULL AFTER ticket_url,
    ADD COLUMN IF NOT EXISTS linha_digitavel VARCHAR(255) NULL AFTER invoice_url,
    ADD COLUMN IF NOT EXISTS criado_em DATETIME NULL AFTER payment_data,
    ADD COLUMN IF NOT EXISTS pago_em DATETIME NULL AFTER criado_em,
    ADD COLUMN IF NOT EXISTS cancelado_em DATETIME NULL AFTER pago_em,
    ADD COLUMN IF NOT EXISTS estornado_em DATETIME NULL AFTER cancelado_em,
    ADD COLUMN IF NOT EXISTS valor_estornado DECIMAL(15,2) NULL AFTER estornado_em,
    ADD COLUMN IF NOT EXISTS dthr_atualizacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ADD UNIQUE INDEX IF NOT EXISTS uq_pagamentos_idempotencia (chave_idempotencia),
    ADD INDEX IF NOT EXISTS idx_pagamentos_assinatura (id_assinatura),
    ADD INDEX IF NOT EXISTS idx_pagamentos_plano (id_plano),
    ADD INDEX IF NOT EXISTS idx_pagamentos_status_vencimento (status, vencimento),
    ADD INDEX IF NOT EXISTS idx_pagamentos_referencia (referencia_externa);

CREATE TABLE IF NOT EXISTS asaas_webhook_eventos (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id VARCHAR(191) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payment_id VARCHAR(100) NULL,
    subscription_id VARCHAR(100) NULL,
    payload LONGTEXT NOT NULL,
    headers TEXT NULL,
    ip_origem VARCHAR(45) NULL,
    status_processamento VARCHAR(20) NOT NULL DEFAULT 'RECEIVED',
    tentativas INT UNSIGNED NOT NULL DEFAULT 0,
    recebido_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processado_em DATETIME NULL,
    ultimo_erro TEXT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_asaas_webhook_event_id (event_id),
    KEY idx_asaas_webhook_payment (payment_id),
    KEY idx_asaas_webhook_subscription (subscription_id),
    KEY idx_asaas_webhook_status (status_processamento, recebido_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS assinaturas_historico (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_assinatura BIGINT UNSIGNED NOT NULL,
    id_conta INT NOT NULL,
    id_pagamento INT NULL,
    id_evento_webhook BIGINT UNSIGNED NULL,
    evento VARCHAR(100) NOT NULL,
    status_anterior VARCHAR(30) NULL,
    status_novo VARCHAR(30) NULL,
    origem VARCHAR(30) NOT NULL DEFAULT 'SYSTEM',
    dados LONGTEXT NULL,
    criado_por INT NULL,
    dthr_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_historico_assinatura (id_assinatura, dthr_registro),
    KEY idx_historico_conta (id_conta, dthr_registro),
    KEY idx_historico_pagamento (id_pagamento),
    KEY idx_historico_webhook (id_evento_webhook),
    CONSTRAINT fk_historico_assinatura FOREIGN KEY (id_assinatura) REFERENCES assinaturas (id),
    CONSTRAINT fk_historico_conta FOREIGN KEY (id_conta) REFERENCES contas_usuarios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS asaas_processamento_tentativas (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    operacao VARCHAR(100) NOT NULL,
    chave_idempotencia VARCHAR(191) NULL,
    id_conta INT NULL,
    id_assinatura BIGINT UNSIGNED NULL,
    id_pagamento INT NULL,
    request_resumo TEXT NULL,
    response_resumo TEXT NULL,
    http_status SMALLINT NULL,
    sucesso TINYINT(1) NOT NULL DEFAULT 0,
    erro TEXT NULL,
    dthr_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_tentativas_chave (chave_idempotencia),
    KEY idx_tentativas_conta (id_conta, dthr_registro),
    KEY idx_tentativas_sucesso (sucesso, dthr_registro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS idempotencia_operacoes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    chave VARCHAR(191) NOT NULL,
    operacao VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PROCESSING',
    referencia_tipo VARCHAR(50) NULL,
    referencia_id VARCHAR(100) NULL,
    resposta LONGTEXT NULL,
    expira_em DATETIME NULL,
    dthr_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dthr_atualizacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_idempotencia_chave (chave),
    KEY idx_idempotencia_status_expira (status, expira_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Compatibilidade: cria uma assinatura local para cada conta existente.
INSERT INTO assinaturas (
    id_conta, id_plano, status, periodo_atual_fim, proxima_cobranca,
    renovacao_automatica, dthr_registro
)
SELECT
    c.id,
    c.id_plano,
    CASE
        WHEN c.status <> 'A' THEN 'SUSPENDED'
        WHEN c.vencimento IS NULL THEN 'PENDING_PAYMENT'
        WHEN c.vencimento >= CURRENT_DATE THEN 'ACTIVE'
        ELSE 'OVERDUE'
    END,
    c.vencimento,
    c.vencimento,
    0,
    CURRENT_TIMESTAMP
FROM contas_usuarios c
LEFT JOIN assinaturas a ON a.id_conta = c.id
WHERE a.id IS NULL AND c.deletado = 'N';

UPDATE contas_usuario_faturas f
JOIN assinaturas a ON a.id_conta = f.id_conta
SET f.id_assinatura = COALESCE(f.id_assinatura, a.id),
    f.id_plano = COALESCE(f.id_plano, a.id_plano)
WHERE f.id_assinatura IS NULL OR f.id_plano IS NULL;

UPDATE contas_faturas_pagamentos p
JOIN assinaturas a ON a.id_conta = p.id_conta
SET p.id_assinatura = COALESCE(p.id_assinatura, a.id),
    p.id_plano = COALESCE(p.id_plano, a.id_plano),
    p.forma_pagamento = COALESCE(
        p.forma_pagamento,
        CASE p.tipo WHEN 'P' THEN 'PIX' WHEN 'B' THEN 'BOLETO' WHEN 'C' THEN 'CREDIT_CARD' ELSE 'UNDEFINED' END
    ),
    p.invoice_url = COALESCE(p.invoice_url, p.ticket_url),
    p.criado_em = COALESCE(p.criado_em, p.dthr_registro)
WHERE p.id_assinatura IS NULL
   OR p.id_plano IS NULL
   OR p.forma_pagamento IS NULL
   OR p.criado_em IS NULL;

ALTER TABLE assinaturas_historico
    MODIFY COLUMN id_pagamento INT NULL;

ALTER TABLE asaas_processamento_tentativas
    MODIFY COLUMN id_pagamento INT NULL;

ALTER TABLE contas_usuario_faturas
    DROP FOREIGN KEY IF EXISTS fk_faturas_assinatura,
    DROP FOREIGN KEY IF EXISTS fk_faturas_plano;
ALTER TABLE contas_usuario_faturas
    ADD CONSTRAINT fk_faturas_assinatura
        FOREIGN KEY (id_assinatura) REFERENCES assinaturas (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_faturas_plano
        FOREIGN KEY (id_plano) REFERENCES planos (id) ON DELETE SET NULL;

ALTER TABLE contas_faturas_pagamentos
    DROP FOREIGN KEY IF EXISTS fk_pagamentos_assinatura,
    DROP FOREIGN KEY IF EXISTS fk_pagamentos_plano;
ALTER TABLE contas_faturas_pagamentos
    ADD CONSTRAINT fk_pagamentos_assinatura
        FOREIGN KEY (id_assinatura) REFERENCES assinaturas (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_pagamentos_plano
        FOREIGN KEY (id_plano) REFERENCES planos (id) ON DELETE SET NULL;

ALTER TABLE assinaturas_historico
    DROP FOREIGN KEY IF EXISTS fk_historico_pagamento,
    DROP FOREIGN KEY IF EXISTS fk_historico_webhook;
ALTER TABLE assinaturas_historico
    ADD CONSTRAINT fk_historico_pagamento
        FOREIGN KEY (id_pagamento) REFERENCES contas_faturas_pagamentos (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_historico_webhook
        FOREIGN KEY (id_evento_webhook) REFERENCES asaas_webhook_eventos (id) ON DELETE SET NULL;

ALTER TABLE asaas_processamento_tentativas
    DROP FOREIGN KEY IF EXISTS fk_tentativas_conta,
    DROP FOREIGN KEY IF EXISTS fk_tentativas_assinatura,
    DROP FOREIGN KEY IF EXISTS fk_tentativas_pagamento;
ALTER TABLE asaas_processamento_tentativas
    ADD CONSTRAINT fk_tentativas_conta
        FOREIGN KEY (id_conta) REFERENCES contas_usuarios (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_tentativas_assinatura
        FOREIGN KEY (id_assinatura) REFERENCES assinaturas (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_tentativas_pagamento
        FOREIGN KEY (id_pagamento) REFERENCES contas_faturas_pagamentos (id) ON DELETE SET NULL;

