SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for attractions
-- ----------------------------
CREATE TABLE `attractions` (
 `id` int NOT NULL AUTO_INCREMENT,
 `destination` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所属城市',
 `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '景点名称',
 `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '景点描述',
 `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '景点类别(自然景观/人文历史/美食体验等)',
 `duration` int NULL DEFAULT NULL COMMENT '建议游玩时间(小时)',
 `opening_hours` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开放时间',
 `ticket_price` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '门票价格',
 `tips` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '游玩小贴士',
 `popularity` int NULL DEFAULT 0 COMMENT '受欢迎程度(1-10)',
 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`) USING BTREE,
 INDEX `idx_destination`(`destination` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '旅游景点知识库' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of attractions
-- ----------------------------
INSERT INTO `attractions` VALUES (1, '杭州', '西湖', '杭州的标志性景点,三面环山,一面濒城,有\"人间天堂\"之称', '自然景观', 4, '全天开放(景区开放时间,内部景点各异)', '免费(部分景点收费)', '建议清晨或傍晚游览,避开人流高峰', 10, '2025-12-10 10:36:19', '2025-12-10 10:36:19');
INSERT INTO `attractions` VALUES (2, '杭州', '灵隐寺', '创建于东晋咸和元年,距今已有约1700年历史,是杭州最早的名刹', '人文历史', 2, '7:30-17:00(冬季16:30停止入场)', '飞来峰45元,灵隐寺香火券30元', '进寺需先购飞来峰门票,衣着需整洁', 9, '2025-12-10 10:36:19', '2025-12-10 10:36:19');
INSERT INTO `attractions` VALUES (3, '杭州', '龙井村', '著名的龙井茶产地,拥有成片的茶园和古朴的村落', '美食体验', 3, '全天开放', '免费', '可以体验采茶和品茶,注意比价', 8, '2025-12-10 10:36:19', '2025-12-10 10:36:19');
INSERT INTO `attractions` VALUES (4, '杭州', '清河坊古街', '杭州历史上最著名的商业街,保留了南宋的古风', '人文历史', 2, '全天开放(店铺一般9:00-22:00)', '免费', '适合晚上游览,小吃丰富', 8, '2025-12-10 10:36:19', '2025-12-10 10:36:19');
INSERT INTO `attractions` VALUES (5, '北京', '故宫博物院', '中国明清两代的皇家宫殿,世界上现存规模最大、保存最为完整的木质结构古建筑之一', '人文历史', 5, '8:30-17:00(周一闭馆,法定节假日除外)', '旺季60元,淡季40元', '需提前网上预约,建议早到', 10, '2025-12-10 10:36:19', '2025-12-10 10:36:19');
INSERT INTO `attractions` VALUES (6, '北京', '八达岭长城', '明长城中保存最好的一段,也是最具代表性的一段', '自然景观', 4, '7:30-17:30(冬季8:00-17:00)', '40元', '建议乘坐缆车或滑车上下,节省体力', 9, '2025-12-10 10:36:19', '2025-12-10 10:36:19');

SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for itineraries
-- ----------------------------
CREATE TABLE `itineraries` (
 `id` int NOT NULL AUTO_INCREMENT,
 `plan_id` int NOT NULL COMMENT '关联的旅游计划ID',
 `day_order` int NOT NULL COMMENT '第几天的行程',
 `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '行程内容',
 PRIMARY KEY (`id`) USING BTREE,
 INDEX `plan_id`(`plan_id` ASC) USING BTREE,
 CONSTRAINT `itineraries_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `travel_plans` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for travel_plans
-- ----------------------------
CREATE TABLE `travel_plans` (
 `id` int NOT NULL AUTO_INCREMENT,
 `destination` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '旅游目的地',
 `days` int NOT NULL COMMENT '游玩天数',
 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for travel_tips
-- ----------------------------
CREATE TABLE `travel_tips` (
 `id` int NOT NULL AUTO_INCREMENT,
 `plan_id` int NOT NULL COMMENT '关联的旅游计划ID',
 `tip_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '建议内容',
 PRIMARY KEY (`id`) USING BTREE,
 INDEX `plan_id`(`plan_id` ASC) USING BTREE,
 CONSTRAINT `travel_tips_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `travel_plans` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `chat_history` (
 `id` int NOT NULL AUTO_INCREMENT,
 `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
 `destination` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
 `role` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
 `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
 `session_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
 `timestamp` datetime NOT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 INDEX `idx_user_dest`(`user_id` ASC, `destination` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;