-- migration-version: 20260803_120000_CREATE_ORDER_PAYMENT_ATTEMPT
|
USE `tour_guide`;
|
|
CREATE TABLE IF NOT EXISTS `tour_order_payment_attempt` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Payment attempt primary key',
|
`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Tour order primary key',
|
`req_seq_id` VARCHAR(64) NOT NULL COMMENT 'Huifu request sequence',
|
`req_date` CHAR(8) NOT NULL COMMENT 'Huifu request date yyyyMMdd',
|
`amount` DECIMAL(10,2) NOT NULL COMMENT 'Payment attempt amount',
|
`status` VARCHAR(16) NOT NULL DEFAULT 'CREATED' COMMENT 'CREATED or SUCCEEDED',
|
`provider_transaction_id` VARCHAR(64) DEFAULT NULL COMMENT 'Huifu transaction id',
|
`paid_at` DATETIME DEFAULT NULL COMMENT 'Payment success time',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created time',
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated time',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_order_payment_attempt_req_seq` (`req_seq_id`),
|
UNIQUE KEY `uk_order_payment_attempt_provider_tx` (`provider_transaction_id`),
|
KEY `idx_order_payment_attempt_order` (`order_id`, `status`),
|
CONSTRAINT `fk_order_payment_attempt_order` FOREIGN KEY (`order_id`) REFERENCES `tour_order` (`id`) ON DELETE RESTRICT
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tour order payment attempts';
|
|
INSERT INTO `sys_schema_migration` (`version`, `description`)
|
VALUES ('20260803_120000_CREATE_ORDER_PAYMENT_ATTEMPT', 'create payment attempt history for retry and callback idempotency');
|
