CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `method` enum('online','card_transfer','cod','cash','pos','credit','check','transfer') NOT NULL,
  `amount` bigint(20) unsigned NOT NULL,
  `status` enum('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `gateway` varchar(50) DEFAULT NULL COMMENT 'e.g., zarinpal',
  `gateway_ref` varchar(100) DEFAULT NULL,
  `gateway_track_id` varchar(100) DEFAULT NULL,
  `transfer_receipt_image` varchar(255) DEFAULT NULL,
  `transfer_verified_at` datetime DEFAULT NULL,
  `transfer_verified_by` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `check_number` varchar(50) DEFAULT NULL COMMENT 'شماره چک',
  `check_date` date DEFAULT NULL COMMENT 'تاریخ چک',
  `check_bank` varchar(100) DEFAULT NULL COMMENT 'نام بانک',
  `check_account_holder` varchar(100) DEFAULT NULL COMMENT 'نام صاحب حساب',
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_status` (`status`),
  KEY `idx_gateway_ref` (`gateway_ref`),
  KEY `idx_check_number` (`check_number`),
  KEY `fk_payments_verified_by` (`transfer_verified_by`),
  KEY `idx_method_status` (`method`,`status`),
  KEY `idx_order_status` (`order_id`,`status`),
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `fk_payments_verified_by` FOREIGN KEY (`transfer_verified_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `chk_pay_amount_positive` CHECK (`amount` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
