-- 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');