-- ============================================================================
-- LASER CUT SAAS - PAYMENT SCHEMA
-- Tables for payment processing with Asaas integration
-- ============================================================================

-- Payments Table
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED NOT NULL,
  `gateway` VARCHAR(50) NOT NULL DEFAULT 'asaas',
  `method` ENUM('pix', 'boleto', 'credit_card', 'debit_card') NOT NULL,
  `gateway_charge_id` VARCHAR(100) NULL COMMENT 'Asaas payment ID',
  `gateway_customer_id` VARCHAR(100) NULL COMMENT 'Asaas customer ID',
  `gateway_status` VARCHAR(50) NULL COMMENT 'Original status from gateway',
  `status` ENUM('pending', 'processing', 'paid', 'failed', 'expired', 'refunded', 'refunding', 'chargeback', 'cancelled') DEFAULT 'pending',
  `amount` DECIMAL(10,2) NOT NULL,
  `net_amount` DECIMAL(10,2) NULL COMMENT 'Amount after fees',
  `fee_amount` DECIMAL(10,2) NULL COMMENT 'Gateway fee',
  `installments` TINYINT UNSIGNED DEFAULT 1,
  `pix_code` TEXT NULL COMMENT 'PIX copia e cola',
  `pix_qrcode` LONGTEXT NULL COMMENT 'PIX QR code base64',
  `pix_expiration` DATETIME NULL,
  `boleto_url` VARCHAR(500) NULL,
  `boleto_barcode` VARCHAR(100) NULL,
  `boleto_pdf_url` VARCHAR(500) NULL,
  `due_date` DATE NULL,
  `paid_at` DATETIME NULL,
  `refunded_at` DATETIME NULL,
  `metadata` TEXT NULL COMMENT 'JSON additional data',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_gateway_charge_id` (`gateway_charge_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_method` (`method`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_payments_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Transactions (log of all payment attempts/events)
CREATE TABLE IF NOT EXISTS `payment_transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NULL,
  `order_id` INT UNSIGNED NULL,
  `payment_id` INT UNSIGNED NULL,
  `type` VARCHAR(50) NOT NULL COMMENT 'pix_created, boleto_created, card_created, status_update, refund, etc',
  `status` ENUM('success', 'failed', 'pending') DEFAULT 'pending',
  `request_data` TEXT NULL COMMENT 'JSON request sent to gateway',
  `response_data` TEXT NULL COMMENT 'JSON response from gateway',
  `error_message` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_payment_id` (`payment_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Webhooks (log of all incoming webhooks)
CREATE TABLE IF NOT EXISTS `payment_webhooks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NULL,
  `gateway` VARCHAR(50) NOT NULL,
  `event` VARCHAR(100) NOT NULL,
  `payload` LONGTEXT NOT NULL COMMENT 'Raw JSON payload',
  `processed` TINYINT(1) DEFAULT 0,
  `processing_result` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_gateway` (`gateway`),
  INDEX `idx_event` (`event`),
  INDEX `idx_processed` (`processed`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add asaas_customer_id to customers if not exists
-- ALTER TABLE `customers` ADD COLUMN IF NOT EXISTS `asaas_customer_id` VARCHAR(100) NULL AFTER `active`;

-- Add payment fields to orders if not exists  
-- ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `payment_method` VARCHAR(50) NULL AFTER `payment_status`;
-- ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `paid_at` DATETIME NULL AFTER `payment_method`;