-- MySQL dump 10.13  Distrib 8.0.19, for Win64 (x86_64)
--
-- Host: localhost    Database: DrivingSchool
-- ------------------------------------------------------
-- Server version	8.0.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
  `account_id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `account_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admin` (`admin_name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `account`
--

LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES (1,'管理员','活跃','张伟'),(2,'学员','活跃','张伟'),(3,'教练','活跃','李娜'),(4,'学员','停用','李娜'),(5,'管理员','活跃','李娜'),(6,'学员','活跃','张伟'),(7,'教练','活跃','张伟'),(8,'学员','活跃','李娜'),(9,'教练','停用','张伟'),(10,'学员','活跃','李娜');
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `admin`
--

DROP TABLE IF EXISTS `admin`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `admin` (
  `admin_name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`admin_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `admin`
--

LOCK TABLES `admin` WRITE;
/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
INSERT INTO `admin` VALUES ('张伟','password123'),('李娜','securepass');
/*!40000 ALTER TABLE `admin` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `appointment`
--

DROP TABLE IF EXISTS `appointment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `appointment` (
  `appointment_id` int NOT NULL AUTO_INCREMENT,
  `student_id` varchar(255) NOT NULL,
  `coach_id` varchar(255) NOT NULL,
  `venue_id` int NOT NULL,
  `period` varchar(50) NOT NULL,
  `status` varchar(50) NOT NULL,
  `feedback_id` int DEFAULT NULL,
  PRIMARY KEY (`appointment_id`),
  KEY `feedback_id` (`feedback_id`),
  KEY `idx_appointment_student` (`student_id`),
  KEY `idx_appointment_coach` (`coach_id`),
  KEY `idx_appointment_venue` (`venue_id`),
  KEY `idx_appointment_period` (`period`),
  KEY `idx_appointment_status` (`status`),
  CONSTRAINT `appointment_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`),
  CONSTRAINT `appointment_ibfk_2` FOREIGN KEY (`coach_id`) REFERENCES `coach` (`coach_id`),
  CONSTRAINT `appointment_ibfk_3` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`),
  CONSTRAINT `appointment_ibfk_4` FOREIGN KEY (`feedback_id`) REFERENCES `feedback` (`feedback_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `appointment`
--

LOCK TABLES `appointment` WRITE;
/*!40000 ALTER TABLE `appointment` DISABLE KEYS */;
INSERT INTO `appointment` VALUES (1,'S001','C001',1,'上午','已确认',NULL),(2,'S002','C002',2,'下午','待确认',NULL),(3,'S003','C003',3,'上午','已取消',NULL),(4,'S004','C004',4,'下午','已确认',NULL),(5,'S005','C005',5,'上午','待确认',NULL),(6,'S006','C006',6,'下午','已确认',NULL),(7,'S007','C007',7,'上午','已确认',NULL),(8,'S008','C008',8,'下午','待确认',NULL),(9,'S009','C009',9,'上午','已取消',NULL),(10,'S010','C010',10,'下午','已确认',NULL);
/*!40000 ALTER TABLE `appointment` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_appointment_update` AFTER UPDATE ON `appointment` FOR EACH ROW BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO log (table_name, record_id, action, details)
        VALUES ('appointment', NEW.appointment_id, 'update', CONCAT('Status changed from ', OLD.status, ' to ', NEW.status));
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Temporary view structure for view `appointment_details`
--

DROP TABLE IF EXISTS `appointment_details`;
/*!50001 DROP VIEW IF EXISTS `appointment_details`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `appointment_details` AS SELECT 
 1 AS `appointment_id`,
 1 AS `student_name`,
 1 AS `coach_name`,
 1 AS `venue_name`,
 1 AS `period`,
 1 AS `status`*/;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `booking_slot`
--

DROP TABLE IF EXISTS `booking_slot`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `booking_slot` (
  `slot_id` int NOT NULL AUTO_INCREMENT,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `venue_id` int NOT NULL,
  PRIMARY KEY (`slot_id`),
  KEY `venue_id` (`venue_id`),
  CONSTRAINT `booking_slot_ibfk_1` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`),
  CONSTRAINT `booking_slot_chk_1` CHECK ((`end_time` > `start_time`))
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `booking_slot`
--

LOCK TABLES `booking_slot` WRITE;
/*!40000 ALTER TABLE `booking_slot` DISABLE KEYS */;
INSERT INTO `booking_slot` VALUES (1,'09:00:00','10:00:00',1),(2,'10:00:00','11:00:00',1),(3,'11:00:00','12:00:00',1),(4,'14:00:00','15:00:00',2),(5,'15:00:00','16:00:00',2),(6,'16:00:00','17:00:00',2),(7,'09:00:00','10:00:00',3),(8,'10:00:00','11:00:00',3),(9,'11:00:00','12:00:00',3),(10,'14:00:00','15:00:00',3);
/*!40000 ALTER TABLE `booking_slot` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `coach`
--

DROP TABLE IF EXISTS `coach`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `coach` (
  `coach_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `personal_info_id` int DEFAULT NULL,
  PRIMARY KEY (`coach_id`),
  KEY `personal_info_id` (`personal_info_id`),
  KEY `idx_coach_name` (`name`),
  CONSTRAINT `coach_ibfk_1` FOREIGN KEY (`personal_info_id`) REFERENCES `personal_info` (`info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `coach`
--

LOCK TABLES `coach` WRITE;
/*!40000 ALTER TABLE `coach` DISABLE KEYS */;
INSERT INTO `coach` VALUES ('C001','王五','coachpass1',3),('C002','赵六','coachpass2',4),('C003','陈翔宇','coachpass3',7),('C004','赵嘉琪','coachpass4',9),('C005','黄涛','coachpass5',NULL),('C006','钱芳','coachpass6',NULL),('C007','朱刚','coachpass7',NULL),('C008','孙莉','coachpass8',NULL),('C009','郑凯','coachpass9',NULL),('C010','林敏','coachpass10',NULL);
/*!40000 ALTER TABLE `coach` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_coach_update` AFTER UPDATE ON `coach` FOR EACH ROW BEGIN
    IF OLD.name <> NEW.name OR OLD.password <> NEW.password THEN
        INSERT INTO log (table_name, record_id, action, details)
        VALUES ('coach', NEW.coach_id, 'update', CONCAT('Name or password updated'));
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Temporary view structure for view `coach_details`
--

DROP TABLE IF EXISTS `coach_details`;
/*!50001 DROP VIEW IF EXISTS `coach_details`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `coach_details` AS SELECT 
 1 AS `coach_id`,
 1 AS `coach_name`,
 1 AS `phone`,
 1 AS `email`,
 1 AS `status`*/;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `feedback`
--

DROP TABLE IF EXISTS `feedback`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `feedback` (
  `feedback_id` int NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`feedback_id`),
  KEY `idx_feedback_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `feedback`
--

LOCK TABLES `feedback` WRITE;
/*!40000 ALTER TABLE `feedback` DISABLE KEYS */;
INSERT INTO `feedback` VALUES (1,'教练非常耐心，教学很好！','2023-10-01'),(2,'场地设施齐全，体验不错。','2023-10-02'),(3,'课程安排合理，推荐这里学车。','2023-10-03'),(4,'教练态度好，进步很快。','2023-10-04'),(5,'学习氛围浓厚，值得信赖。','2023-10-05'),(6,'教练很专业，感谢指导。','2023-10-06'),(7,'场地干净整洁，感觉舒适。','2023-10-07'),(8,'课程内容丰富，收获颇丰。','2023-10-08'),(9,'教练很有经验，帮助很大。','2023-10-09'),(10,'场地条件优越，性价比高。','2023-10-10');
/*!40000 ALTER TABLE `feedback` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `before_feedback_delete` BEFORE DELETE ON `feedback` FOR EACH ROW BEGIN
    DECLARE expiry_date DATE;
    SET expiry_date = CURDATE() - INTERVAL 1 YEAR; -- 假设过期时间为一年前

    IF OLD.date < expiry_date THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete expired feedback';
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `invoice`
--

DROP TABLE IF EXISTS `invoice`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `invoice` (
  `invoice_id` int NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2) NOT NULL,
  `date` date NOT NULL,
  `appointment_id` int DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `appointment_id` (`appointment_id`),
  KEY `idx_invoice_amount` (`amount`),
  KEY `idx_invoice_date` (`date`),
  CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`appointment_id`),
  CONSTRAINT `invoice_chk_1` CHECK ((`amount` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoice`
--

LOCK TABLES `invoice` WRITE;
/*!40000 ALTER TABLE `invoice` DISABLE KEYS */;
INSERT INTO `invoice` VALUES (1,1500.00,'2023-10-01',1),(2,1800.00,'2023-10-02',2),(3,2000.00,'2023-10-03',3),(4,1700.00,'2023-10-04',4),(5,1600.00,'2023-10-05',5),(6,1900.00,'2023-10-06',6),(7,1400.00,'2023-10-07',7),(8,1550.00,'2023-10-08',8),(9,1650.00,'2023-10-09',9),(10,1750.00,'2023-10-10',10);
/*!40000 ALTER TABLE `invoice` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_invoice_insert` AFTER INSERT ON `invoice` FOR EACH ROW BEGIN
    UPDATE account
    SET status = '已支付'
    WHERE account_id = (
        SELECT student_id FROM appointment WHERE appointment_id = NEW.appointment_id
    );
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Temporary view structure for view `invoice_details`
--

DROP TABLE IF EXISTS `invoice_details`;
/*!50001 DROP VIEW IF EXISTS `invoice_details`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `invoice_details` AS SELECT 
 1 AS `invoice_id`,
 1 AS `amount`,
 1 AS `invoice_date`,
 1 AS `student_name`,
 1 AS `coach_name`*/;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `table_name` varchar(50) DEFAULT NULL,
  `record_id` int DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `details` text,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `log`
--

LOCK TABLES `log` WRITE;
/*!40000 ALTER TABLE `log` DISABLE KEYS */;
/*!40000 ALTER TABLE `log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `personal_info`
--

DROP TABLE IF EXISTS `personal_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `personal_info` (
  `info_id` int NOT NULL AUTO_INCREMENT,
  `student_id` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`info_id`),
  UNIQUE KEY `student_id` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `personal_info`
--

LOCK TABLES `personal_info` WRITE;
/*!40000 ALTER TABLE `personal_info` DISABLE KEYS */;
INSERT INTO `personal_info` VALUES (1,'S001','13800138000','zhangsan@example.com'),(2,'S002','13900139000','lisi@example.com'),(3,'C001','13700137000','wangwu@example.com'),(4,'C002','13600136000','zhaoliu@example.com'),(5,'S003','13500135000','sunbaohua@example.com'),(6,'S004','13400134000','liwenbo@example.com'),(7,'C003','13300133000','chenxiangyu@example.com'),(8,'S005','13200132000','yangming@example.com'),(9,'C004','13100131000','zhaojiaqi@example.com'),(10,'S006','13000130000','wulifei@example.com');
/*!40000 ALTER TABLE `personal_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `student_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `personal_info_id` int DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `personal_info_id` (`personal_info_id`),
  KEY `idx_student_name` (`name`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`personal_info_id`) REFERENCES `personal_info` (`info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES ('S001','张三','studypass1',1),('S002','李四','studypass2',2),('S003','孙宝华','studypass3',5),('S004','李文博','studypass4',6),('S005','杨明','studypass5',8),('S006','吴雷飞','studypass6',10),('S007','刘强','studypass7',NULL),('S008','王丽','studypass8',NULL),('S009','陈杰','studypass9',NULL),('S010','周梅','studypass10',NULL);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_student_insert` AFTER INSERT ON `student` FOR EACH ROW BEGIN
    INSERT INTO account (account_id, type, status, created_by) 
    VALUES (NEW.student_id, '学员', '活跃', '张伟');
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Temporary view structure for view `student_appointment_feedback`
--

DROP TABLE IF EXISTS `student_appointment_feedback`;
/*!50001 DROP VIEW IF EXISTS `student_appointment_feedback`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `student_appointment_feedback` AS SELECT 
 1 AS `student_name`,
 1 AS `coach_name`,
 1 AS `appointment_period`,
 1 AS `appointment_status`,
 1 AS `feedback_content`,
 1 AS `feedback_date`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `student_details`
--

DROP TABLE IF EXISTS `student_details`;
/*!50001 DROP VIEW IF EXISTS `student_details`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `student_details` AS SELECT 
 1 AS `student_id`,
 1 AS `student_name`,
 1 AS `phone`,
 1 AS `email`,
 1 AS `status`*/;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `training_record`
--

DROP TABLE IF EXISTS `training_record`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `training_record` (
  `record_id` int NOT NULL AUTO_INCREMENT,
  `student_id` varchar(255) NOT NULL,
  `coach_id` varchar(255) NOT NULL,
  `feedback` text,
  `appointment_id` int DEFAULT NULL,
  PRIMARY KEY (`record_id`),
  KEY `student_id` (`student_id`),
  KEY `coach_id` (`coach_id`),
  KEY `appointment_id` (`appointment_id`),
  CONSTRAINT `training_record_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`),
  CONSTRAINT `training_record_ibfk_2` FOREIGN KEY (`coach_id`) REFERENCES `coach` (`coach_id`),
  CONSTRAINT `training_record_ibfk_3` FOREIGN KEY (`appointment_id`) REFERENCES `appointment` (`appointment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `training_record`
--

LOCK TABLES `training_record` WRITE;
/*!40000 ALTER TABLE `training_record` DISABLE KEYS */;
INSERT INTO `training_record` VALUES (1,'S001','C001','很好的一次训练！',1),(2,'S002','C002','还需要多加练习。',2),(3,'S003','C003','继续保持进步。',3),(4,'S004','C004','表现不错，加油！',4),(5,'S005','C005','需要改进的地方很多。',5),(6,'S006','C006','进步明显，继续努力。',6),(7,'S007','C007','基础还行，还需加强。',7),(8,'S008','C008','整体不错，细节需注意。',8),(9,'S009','C009','还有提升空间。',9),(10,'S010','C010','做得很好，保持下去。',10);
/*!40000 ALTER TABLE `training_record` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `venue`
--

DROP TABLE IF EXISTS `venue`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `venue` (
  `venue_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `capacity` int NOT NULL,
  PRIMARY KEY (`venue_id`),
  KEY `idx_venue_name` (`name`),
  CONSTRAINT `venue_chk_1` CHECK ((`capacity` > 0))
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `venue`
--

LOCK TABLES `venue` WRITE;
/*!40000 ALTER TABLE `venue` DISABLE KEYS */;
INSERT INTO `venue` VALUES (1,'训练场A',50),(2,'训练场B',30),(3,'理论教室',40),(4,'模拟驾驶室',20),(5,'休息区',60),(6,'户外练习场',70),(7,'室内练习场',55),(8,'考试中心',25),(9,'培训办公室',10),(10,'接待厅',35);
/*!40000 ALTER TABLE `venue` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'DrivingSchool'
--
/*!50003 DROP PROCEDURE IF EXISTS `AddNewStudent` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddNewStudent`(

    IN p_student_id VARCHAR(255),

    IN p_name VARCHAR(255),

    IN p_password VARCHAR(255),

    IN p_phone VARCHAR(20),

    IN p_email VARCHAR(255)

)
BEGIN

    DECLARE v_info_id INT;



    -- 插入个人基本信息

    INSERT INTO personal_info (phone, email) VALUES (p_phone, p_email);

    SET v_info_id = LAST_INSERT_ID();



    -- 插入学生信息

    INSERT INTO student (student_id, name, password, personal_info_id) 

    VALUES (p_student_id, p_name, p_password, v_info_id);



    -- 插入账户信息

    INSERT INTO account (account_id, type, status, created_by) 

    VALUES (p_student_id, '学员', '活跃', '张伟');

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CalculateCoachTotalIncome` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateCoachTotalIncome`(

    IN p_coach_id VARCHAR(255)

)
BEGIN

    SELECT 

        c.coach_id,

        c.name AS coach_name,

        SUM(i.amount) AS total_income

    FROM 

        invoice i

    JOIN 

        appointment ap ON i.appointment_id = ap.appointment_id

    JOIN 

        coach c ON ap.coach_id = c.coach_id

    WHERE 

        c.coach_id = p_coach_id

    GROUP BY 

        c.coach_id, c.name;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteExpiredInvoices` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteExpiredInvoices`(

    IN p_expiry_date DATE

)
BEGIN

    DELETE FROM invoice 

    WHERE date < p_expiry_date;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `GenerateStudentFeedbackReport` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GenerateStudentFeedbackReport`(

    IN p_student_id VARCHAR(255)

)
BEGIN

    SELECT 

        f.feedback_id,

        f.content AS feedback_content,

        f.date AS feedback_date

    FROM 

        feedback f

    JOIN 

        training_record tr ON f.feedback_id = tr.feedback_id

    WHERE 

        tr.student_id = p_student_id;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateAppointmentStatus` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateAppointmentStatus`(

    IN p_appointment_id INT,

    IN p_new_status VARCHAR(50)

)
BEGIN

    UPDATE appointment 

    SET status = p_new_status 

    WHERE appointment_id = p_appointment_id;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Final view structure for view `appointment_details`
--

/*!50001 DROP VIEW IF EXISTS `appointment_details`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `appointment_details` AS select `ap`.`appointment_id` AS `appointment_id`,`st`.`name` AS `student_name`,`co`.`name` AS `coach_name`,`v`.`name` AS `venue_name`,`ap`.`period` AS `period`,`ap`.`status` AS `status` from (((`appointment` `ap` join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) join `venue` `v` on((`ap`.`venue_id` = `v`.`venue_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `coach_details`
--

/*!50001 DROP VIEW IF EXISTS `coach_details`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `coach_details` AS select `c`.`coach_id` AS `coach_id`,`c`.`name` AS `coach_name`,`pi`.`phone` AS `phone`,`pi`.`email` AS `email`,`a`.`status` AS `status` from ((`coach` `c` join `personal_info` `pi` on((`c`.`personal_info_id` = `pi`.`info_id`))) join `account` `a` on((`c`.`coach_id` = `a`.`account_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `invoice_details`
--

/*!50001 DROP VIEW IF EXISTS `invoice_details`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `invoice_details` AS select `i`.`invoice_id` AS `invoice_id`,`i`.`amount` AS `amount`,`i`.`date` AS `invoice_date`,`st`.`name` AS `student_name`,`co`.`name` AS `coach_name` from (((`invoice` `i` join `appointment` `ap` on((`i`.`appointment_id` = `ap`.`appointment_id`))) join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `student_appointment_feedback`
--

/*!50001 DROP VIEW IF EXISTS `student_appointment_feedback`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `student_appointment_feedback` AS select `st`.`name` AS `student_name`,`co`.`name` AS `coach_name`,`ap`.`period` AS `appointment_period`,`ap`.`status` AS `appointment_status`,`f`.`content` AS `feedback_content`,`f`.`date` AS `feedback_date` from (((`appointment` `ap` join `student` `st` on((`ap`.`student_id` = `st`.`student_id`))) join `coach` `co` on((`ap`.`coach_id` = `co`.`coach_id`))) left join `feedback` `f` on((`ap`.`feedback_id` = `f`.`feedback_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `student_details`
--

/*!50001 DROP VIEW IF EXISTS `student_details`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `student_details` AS select `s`.`student_id` AS `student_id`,`s`.`name` AS `student_name`,`pi`.`phone` AS `phone`,`pi`.`email` AS `email`,`a`.`status` AS `status` from ((`student` `s` join `personal_info` `pi` on((`s`.`personal_info_id` = `pi`.`info_id`))) join `account` `a` on((`s`.`student_id` = `a`.`account_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-25 14:58:02
