-- migration-version: 20260909_130000_CREATE_ORDER_REFUND
USE `tour_guide`;

CREATE TABLE IF NOT EXISTS `tour_order_refund` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Refund record primary key',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Tour order primary key',
    `refund_order_no` VARCHAR(64) NOT NULL COMMENT 'Refund request sequence',
    `refund_amount` DECIMAL(10,2) NOT NULL COMMENT 'Amount of this refund request',
    `refund_status` VARCHAR(32) NOT NULL DEFAULT 'REFUNDING' COMMENT 'NONE, REFUNDING, PARTIAL, REFUNDED or REFUND_FAILED',
    `refund_time` DATETIME DEFAULT NULL COMMENT 'Successful refund time',
    `refund_method` VARCHAR(32) DEFAULT NULL COMMENT 'Refund payment method',
    `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT 'Logic delete flag',
    `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_tour_order_refund_no` (`refund_order_no`),
    KEY `idx_tour_order_refund_order` (`order_id`, `created_at`),
    CONSTRAINT `fk_tour_order_refund_order` FOREIGN KEY (`order_id`) REFERENCES `tour_order` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Refund request history for tour orders';

INSERT IGNORE INTO `tour_order_refund`
    (`order_id`, `refund_order_no`, `refund_amount`, `refund_status`, `refund_time`, `refund_method`, `deleted`, `created_at`, `updated_at`)
SELECT `id`, `refund_order_no`,
       COALESCE(`refund_amount`, `refunded_amount`, 0.00),
       CASE WHEN `status` = 'REFUNDED' THEN 'REFUNDED' ELSE COALESCE(NULLIF(`refund_status`, ''), 'REFUNDING') END,
       `refund_time`, `refund_method`, 0, COALESCE(`refund_time`, `updated_at`), `updated_at`
FROM `tour_order`
WHERE `refund_order_no` IS NOT NULL
  AND (`refund_amount` IS NOT NULL OR `refunded_amount` > 0);

INSERT IGNORE INTO `sys_schema_migration` (`version`, `description`)
VALUES ('20260909_130000_CREATE_ORDER_REFUND', 'create refund request history for each order refund');