-- ============================================
-- Tabela: payment_gateways
-- Sistema de Pagamentos Multi-Gateway
-- ============================================

CREATE TABLE IF NOT EXISTS `payment_gateways` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT 'Identificador do gateway (ex: mercadopago, stripe, asaas)',
    `display_name` VARCHAR(100) NOT NULL COMMENT 'Nome para exibição',
    `public_key` VARCHAR(255) DEFAULT NULL COMMENT 'Chave pública (quando aplicável)',
    `secret_key` VARCHAR(255) NOT NULL COMMENT 'Access Token / Secret Key',
    `webhook_secret` VARCHAR(255) DEFAULT NULL COMMENT 'Secret para validação de webhooks',
    `is_active` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Apenas um gateway pode estar ativo',
    `environment` ENUM('sandbox', 'production') NOT NULL DEFAULT 'sandbox',
    `settings` JSON DEFAULT NULL COMMENT 'Configurações adicionais em JSON',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_gateway_name` (`name`),
    KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Inserir gateways padrão (inativos)
-- ============================================

INSERT INTO `payment_gateways` (`name`, `display_name`, `public_key`, `secret_key`, `is_active`, `environment`) VALUES
('mercadopago', 'Mercado Pago', '', '', 0, 'sandbox'),
('stripe', 'Stripe', '', '', 0, 'sandbox'),
('asaas', 'Asaas', '', '', 0, 'sandbox')
ON DUPLICATE KEY UPDATE `display_name` = VALUES(`display_name`);

-- ============================================
-- Tabela: payment_transactions
-- Histórico de transações
-- ============================================

CREATE TABLE IF NOT EXISTS `payment_transactions` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `gateway_id` INT(11) UNSIGNED NOT NULL,
    `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'ID da transação no gateway',
    `type` ENUM('pix', 'credit_card', 'boleto', 'checkout') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'BRL',
    `status` ENUM('pending', 'approved', 'rejected', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
    `plan_id` INT(11) UNSIGNED DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `pix_qr_code` TEXT DEFAULT NULL,
    `pix_qr_code_base64` LONGTEXT DEFAULT NULL,
    `pix_copy_paste` TEXT DEFAULT NULL,
    `checkout_url` VARCHAR(500) DEFAULT NULL,
    `paid_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_gateway_id` (`gateway_id`),
    KEY `idx_external_id` (`external_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_transaction_gateway` FOREIGN KEY (`gateway_id`) REFERENCES `payment_gateways`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
