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;