-- Payments table for SSLCommerz transactions
CREATE TABLE IF NOT EXISTS `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tran_id` VARCHAR(64) NOT NULL UNIQUE,
  `invoice_id` BIGINT UNSIGNED NULL,
  `invoice_number` VARCHAR(64) NULL,
  `student_id` BIGINT UNSIGNED NULL,
  `student_uid` VARCHAR(64) NULL,
  `student_name` VARCHAR(191) NULL,
  `payer_name` VARCHAR(191) NULL,
  `payer_phone` VARCHAR(32) NULL,
  `payer_email` VARCHAR(191) NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `currency` VARCHAR(8) NOT NULL DEFAULT 'BDT',
  `method` VARCHAR(32) NULL, -- bkash / nagad / rocket / card / bank etc.
  `card_type` VARCHAR(64) NULL,
  `card_issuer` VARCHAR(64) NULL,
  `bank_tran_id` VARCHAR(128) NULL,
  `gateway` VARCHAR(32) NOT NULL DEFAULT 'sslcommerz',
  `status` VARCHAR(32) NOT NULL DEFAULT 'PENDING', -- PENDING/VALID/FAILED/CANCELLED
  `ipn_payload` JSON NULL,
  `note` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_payments_student` (`student_id`),
  INDEX `idx_payments_invoice` (`invoice_id`),
  INDEX `idx_payments_status` (`status`),
  INDEX `idx_payments_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;