-- Migração: Integração Celcoin Payment Gateway
-- Data: 2026-04-07

USE saas_barbearia;

-- Tabela de gateways de pagamento suportados
CREATE TABLE payment_gateways (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL, -- 'celcoin', 'stripe', 'pagseguro', etc.
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    config JSON, -- Configurações específicas do gateway
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code_active (code, is_active)
);

-- Tabela de configurações de gateway por empresa
CREATE TABLE company_gateway_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    gateway_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_sandbox BOOLEAN DEFAULT TRUE,
    config JSON, -- Configurações específicas da empresa (chaves API, etc.)
    webhook_secret VARCHAR(255), -- Para validação de webhooks
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (gateway_id) REFERENCES payment_gateways(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company_gateway (company_id, gateway_id),
    INDEX idx_company_active (company_id, is_active)
);

-- Tabela de tokens OAuth da Celcoin
CREATE TABLE celcoin_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    gateway_setting_id INT NOT NULL,
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    token_type VARCHAR(50) DEFAULT 'Bearer',
    expires_at TIMESTAMP NOT NULL,
    scope TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (gateway_setting_id) REFERENCES company_gateway_settings(id) ON DELETE CASCADE,
    INDEX idx_company_expires (company_id, expires_at)
);

-- Tabela de clientes sincronizados na Celcoin
CREATE TABLE celcoin_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NOT NULL, -- Usuário do sistema
    celcoin_customer_id VARCHAR(100) UNIQUE NOT NULL, -- ID do cliente na Celcoin
    document VARCHAR(20), -- CPF/CNPJ
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address JSON, -- Endereço completo em JSON
    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',
    last_sync TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company_user (company_id, user_id),
    INDEX idx_celcoin_customer_id (celcoin_customer_id),
    INDEX idx_document (document)
);

-- Tabela de cobranças avulsas
CREATE TABLE celcoin_charges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    celcoin_customer_id VARCHAR(100),
    celcoin_charge_id VARCHAR(100) UNIQUE, -- ID da cobrança na Celcoin
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'BRL',
    description TEXT,
    due_date DATE NOT NULL,
    payment_date TIMESTAMP NULL,
    status ENUM('pending', 'paid', 'expired', 'canceled', 'failed') DEFAULT 'pending',
    payment_method JSON, -- Método de pagamento usado
    installments INT DEFAULT 1,
    fee_amount DECIMAL(10,2) DEFAULT 0.00, -- Taxa cobrada
    net_amount DECIMAL(10,2), -- Valor líquido (amount - fee)
    reference_id VARCHAR(255), -- ID de referência no sistema (appointment_id, etc.)
    reference_type VARCHAR(50), -- Tipo de referência (appointment, membership, etc.)
    qr_code TEXT, -- QR Code para pagamento
    qr_code_base64 TEXT, -- QR Code em base64
    billet_url TEXT, -- URL do boleto
    billet_base64 TEXT, -- Boleto em base64
    metadata JSON, -- Dados adicionais
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_celcoin_charge_id (celcoin_charge_id),
    INDEX idx_status_due_date (status, due_date),
    INDEX idx_reference (reference_type, reference_id)
);

-- Tabela de assinaturas recorrentes
CREATE TABLE celcoin_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    membership_id INT, -- Vinculação com assinatura do sistema
    celcoin_customer_id VARCHAR(100),
    celcoin_subscription_id VARCHAR(100) UNIQUE, -- ID da assinatura na Celcoin
    celcoin_charge_id VARCHAR(100), -- ID da cobrança inicial
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'BRL',
    billing_cycle ENUM('daily', 'weekly', 'monthly', 'quarterly', 'semiannual', 'annual') NOT NULL,
    status ENUM('active', 'past_due', 'canceled', 'expired', 'suspended') DEFAULT 'active',
    current_period_start TIMESTAMP NOT NULL,
    current_period_end TIMESTAMP NOT NULL,
    trial_end TIMESTAMP NULL,
    next_billing_date DATE NOT NULL,
    canceled_at TIMESTAMP NULL,
    cancel_reason TEXT,
    payment_method JSON, -- Método de pagamento padrão
    max_cycles INT, -- Número máximo de ciclos (NULL = indefinido)
    current_cycle INT DEFAULT 1,
    total_paid DECIMAL(10,2) DEFAULT 0.00,
    metadata JSON, -- Dados adicionais
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE SET NULL,
    INDEX idx_celcoin_subscription_id (celcoin_subscription_id),
    INDEX idx_status_next_billing (status, next_billing_date),
    INDEX idx_membership (membership_id)
);

-- Tabela de logs de webhooks recebidos
CREATE TABLE celcoin_webhook_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    webhook_id VARCHAR(100) UNIQUE, -- ID único do webhook
    event_type VARCHAR(100) NOT NULL, -- Tipo do evento (charge.paid, subscription.canceled, etc.)
    payload JSON NOT NULL, -- Payload completo do webhook
    signature VARCHAR(255), -- Assinatura para validação
    processed BOOLEAN DEFAULT FALSE,
    processing_attempts INT DEFAULT 0,
    last_attempt_at TIMESTAMP NULL,
    error_message TEXT,
    processed_at TIMESTAMP NULL,
    idempotency_key VARCHAR(255), -- Chave de idempotência
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_event_type_processed (event_type, processed),
    INDEX idx_webhook_id (webhook_id),
    INDEX idx_idempotency_key (idempotency_key),
    INDEX idx_created_at (created_at)
);

-- Tabela de logs de sincronização
CREATE TABLE celcoin_sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    sync_type ENUM('customers', 'charges', 'subscriptions', 'reconciliation') NOT NULL,
    reference_id VARCHAR(100), -- ID de referência (customer_id, charge_id, etc.)
    action ENUM('create', 'update', 'cancel', 'sync') NOT NULL,
    status ENUM('success', 'error', 'partial') NOT NULL,
    request_data JSON, -- Dados enviados
    response_data JSON, -- Dados recebidos
    error_message TEXT,
    execution_time DECIMAL(8,3), -- Tempo de execução em segundos
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_sync_type_status (sync_type, status),
    INDEX idx_reference (sync_type, reference_id),
    INDEX idx_created_at (created_at)
);

-- Tabela de chaves de idempotência
CREATE TABLE celcoin_idempotency_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    idempotency_key VARCHAR(255) NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    request_hash VARCHAR(64) NOT NULL, -- Hash do request para detectar duplicatas
    response_data JSON,
    status ENUM('processing', 'completed', 'failed') DEFAULT 'processing',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_idempotency_key (company_id, idempotency_key),
    INDEX idx_expires_at (expires_at),
    INDEX idx_status_expires (status, expires_at)
);

-- Inserir gateway Celcoin
INSERT INTO payment_gateways (code, name, description, config) VALUES
('celcoin', 'Celcoin Payments', 'Gateway de pagamentos da Celcoin com suporte a PIX, boleto e cartão', JSON_OBJECT(
    'supported_currencies', JSON_ARRAY('BRL'),
    'supported_payment_methods', JSON_ARRAY('pix', 'boleto', 'credit_card', 'debit_card'),
    'webhook_events', JSON_ARRAY('charge.paid', 'charge.expired', 'charge.canceled', 'subscription.created', 'subscription.canceled', 'subscription.expired'),
    'api_base_urls', JSON_OBJECT(
        'sandbox', 'https://sandbox.api.celcoin.com.br',
        'production', 'https://api.celcoin.com.br'
    ),
    'oauth_endpoints', JSON_OBJECT(
        'token', '/v5/token',
        'authorize', '/v5/authorize'
    )
));

-- Índices adicionais para performance
CREATE INDEX idx_celcoin_charges_status_date ON celcoin_charges (status, created_at);
CREATE INDEX idx_celcoin_subscriptions_status_date ON celcoin_subscriptions (status, created_at);
CREATE INDEX idx_celcoin_webhook_logs_event_date ON celcoin_webhook_logs (event_type, created_at);
CREATE INDEX idx_celcoin_sync_logs_type_date ON celcoin_sync_logs (sync_type, created_at);