-- migration-version: 20260730_140000_CREATE_GUIDE_RECEIVING_SESSION
USE `tour_guide`;

CREATE TABLE IF NOT EXISTS `guide_receiving_session` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guide_id` BIGINT UNSIGNED NOT NULL,
    `scenic_spot_id` BIGINT UNSIGNED NOT NULL,
    `meeting_point_id` BIGINT UNSIGNED NOT NULL,
    `receiving_mode` VARCHAR(16) NOT NULL COMMENT 'GROUP or PRIVATE',
    `service_types` VARCHAR(128) NOT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'RECEIVING',
    `current_people_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `first_order_at` DATETIME DEFAULT NULL,
    `service_start_at` DATETIME DEFAULT NULL,
    `order_cutoff_at` DATETIME DEFAULT NULL,
    `deleted` TINYINT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_receiving_match` (`scenic_spot_id`, `meeting_point_id`, `receiving_mode`, `status`),
    KEY `idx_receiving_guide_status` (`guide_id`, `status`),
    KEY `idx_receiving_start` (`status`, `service_start_at`),
    CONSTRAINT `fk_receiving_guide` FOREIGN KEY (`guide_id`) REFERENCES `guide_user` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_receiving_scenic` FOREIGN KEY (`scenic_spot_id`) REFERENCES `operation_scenic_spot` (`id`),
    CONSTRAINT `fk_receiving_meeting_point` FOREIGN KEY (`meeting_point_id`) REFERENCES `operation_scenic_meeting_point` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `tour_order`
    ADD COLUMN `scenic_spot_id` BIGINT UNSIGNED DEFAULT NULL AFTER `visitor_mobile`,
    ADD COLUMN `meeting_point_id` BIGINT UNSIGNED DEFAULT NULL AFTER `scenic_name`,
    ADD COLUMN `service_package_id` BIGINT UNSIGNED DEFAULT NULL AFTER `package_name`,
    ADD COLUMN `receiving_session_id` BIGINT UNSIGNED DEFAULT NULL AFTER `guide_mobile`,
    ADD KEY `idx_tour_order_match` (`status`, `scenic_spot_id`, `meeting_point_id`, `order_type`),
    ADD KEY `idx_tour_order_session` (`receiving_session_id`);

UPDATE `tour_order` o
LEFT JOIN `operation_scenic_spot` s ON s.scenic_name = o.scenic_name AND s.deleted = 0
LEFT JOIN `operation_scenic_meeting_point` m ON m.scenic_spot_id = s.id AND m.point_name = o.meeting_point_name AND m.deleted = 0
LEFT JOIN `operation_scenic_pricing` p ON p.scenic_spot_id = s.id AND p.service_type = o.order_type AND p.deleted = 0
SET o.scenic_spot_id = s.id, o.meeting_point_id = m.id, o.service_package_id = p.id
WHERE o.scenic_spot_id IS NULL;

INSERT INTO `sys_schema_migration` (`version`, `description`)
VALUES ('20260730_140000_CREATE_GUIDE_RECEIVING_SESSION', 'create receiving session and order relation columns');