OnlinePharmacyManagementSystem/pharmacy.sql

/*
 Navicat Premium Dump SQL

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 90001 (9.0.1)
 Source Host           : localhost:3306
 Source Schema         : pharmacy

 Target Server Type    : MySQL
 Target Server Version : 90001 (9.0.1)
 File Encoding         : 65001

 Date: 02/01/2025 19:26:12
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for address
-- ----------------------------
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '收货地址唯一标识，自增长主键',
  `patient_id` int NOT NULL COMMENT '关联的患者的唯一标识',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '收件人姓名',
  `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所在省份',
  `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所在城市',
  `district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所在区县',
  `detailed_address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '详细地址信息',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `patient_id`(`patient_id` ASC) USING BTREE,
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '收货地址表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for address_log
-- ----------------------------
DROP TABLE IF EXISTS `address_log`;
CREATE TABLE `address_log`  (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `address_id` int NOT NULL,
  `operation_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `operation_time` datetime NOT NULL,
  PRIMARY KEY (`log_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for disease
-- ----------------------------
DROP TABLE IF EXISTS `disease`;
CREATE TABLE `disease`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '疾病唯一标识，自增长主键',
  `patient_id` int NOT NULL COMMENT '对应患者id',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '疾病名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '疾病信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for disease_insert_log
-- ----------------------------
DROP TABLE IF EXISTS `disease_insert_log`;
CREATE TABLE `disease_insert_log`  (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `disease_id` int NOT NULL,
  `insert_time` datetime NOT NULL,
  PRIMARY KEY (`log_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工唯一标识，自增长主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工职称',
  `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工性别',
  `age` int NULL DEFAULT NULL COMMENT '员工年龄',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `status` int NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_username`(`username` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 85 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for information
-- ----------------------------
DROP TABLE IF EXISTS `information`;
CREATE TABLE `information`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '联系方式唯一标识，自增长主键',
  `patient_id` int NOT NULL COMMENT '关联的患者的唯一标识',
  `type` int NOT NULL COMMENT '联系方式类型，0手机、1邮箱',
  `phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电话号码',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电子邮箱地址',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `patient_id`(`patient_id` ASC) USING BTREE,
  CONSTRAINT `information_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '联系方式表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for information_log
-- ----------------------------
DROP TABLE IF EXISTS `information_log`;
CREATE TABLE `information_log`  (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `information_id` int NOT NULL,
  `operation_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `operation_time` datetime NOT NULL,
  PRIMARY KEY (`log_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for medicine
-- ----------------------------
DROP TABLE IF EXISTS `medicine`;
CREATE TABLE `medicine`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '药品唯一标识，自增长主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '药品名称',
  `dosage_form` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '药品剂型，例如片剂、胶囊等',
  `specification` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '药品规格',
  `expiration_date` date NOT NULL COMMENT '药品有效期截止日期',
  `stock_quantity` int NOT NULL COMMENT '药品库存数量',
  `purchase_price` decimal(10, 2) NOT NULL COMMENT '药品进货价格',
  `retail_price` decimal(10, 2) NOT NULL COMMENT '药品零售价格',
  `status` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '药品信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '订单唯一标识，自增长主键',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  `patient_id` int NOT NULL COMMENT '下单患者的唯一标识',
  `employee_id` int NOT NULL COMMENT '处理该订单的员工的唯一标识',
  `total_amount` decimal(10, 2) NOT NULL COMMENT '订单总金额',
  `order_status` int NOT NULL DEFAULT 1 COMMENT '订单当前状态，未完成1，已完成2，取消3',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `patient_id`(`patient_id` ASC) USING BTREE,
  INDEX `employee_id`(`employee_id` ASC) USING BTREE,
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `order_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for order_medicine_relation
-- ----------------------------
DROP TABLE IF EXISTS `order_medicine_relation`;
CREATE TABLE `order_medicine_relation`  (
  `order_id` int NOT NULL COMMENT '关联的订单的唯一标识',
  `medicine_id` int NOT NULL COMMENT '关联的药品的唯一标识',
  `quantity` int NOT NULL COMMENT '该药品在对应订单中的数量',
  PRIMARY KEY (`order_id`, `medicine_id`) USING BTREE,
  INDEX `medicine_id`(`medicine_id` ASC) USING BTREE,
  CONSTRAINT `order_medicine_relation_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `order_medicine_relation_ibfk_2` FOREIGN KEY (`medicine_id`) REFERENCES `medicine` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单药品关联表，用于体现订单与药品之间多对多的关联关系以及对应的数量信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for patient
-- ----------------------------
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '患者唯一标识，自增长主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '患者姓名',
  `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '患者性别',
  `age` int NULL DEFAULT NULL COMMENT '患者年龄',
  `id_card_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '患者身份证号码',
  `status` int NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '患者信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for promotion_activitie
-- ----------------------------
DROP TABLE IF EXISTS `promotion_activitie`;
CREATE TABLE `promotion_activitie`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '促销活动唯一标识，自增长主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '促销活动名称',
  `start_time` datetime NOT NULL COMMENT '促销活动开始时间',
  `end_time` datetime NOT NULL COMMENT '促销活动结束时间',
  `preferential_rules` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '促销活动的优惠规则内容',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '促销活动表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for promotion_medicine_relation
-- ----------------------------
DROP TABLE IF EXISTS `promotion_medicine_relation`;
CREATE TABLE `promotion_medicine_relation`  (
  `promotion_id` int NOT NULL COMMENT '关联的促销活动的唯一标识',
  `medicine_id` int NOT NULL COMMENT '关联的药品的唯一标识',
  PRIMARY KEY (`promotion_id`, `medicine_id`) USING BTREE,
  INDEX `medicine_id`(`medicine_id` ASC) USING BTREE,
  CONSTRAINT `promotion_medicine_relation_ibfk_1` FOREIGN KEY (`promotion_id`) REFERENCES `promotion_activitie` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `promotion_medicine_relation_ibfk_2` FOREIGN KEY (`medicine_id`) REFERENCES `medicine` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '促销药品关联表，用于体现促销活动与药品之间多对多的关联关系' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Triggers structure for table address
-- ----------------------------
DROP TRIGGER IF EXISTS `check_patient_exists_before_insert_address`;
delimiter ;;
CREATE TRIGGER `check_patient_exists_before_insert_address` BEFORE INSERT ON `address` FOR EACH ROW BEGIN
    DECLARE patient_count INT;
    SELECT COUNT(*) INTO patient_count FROM `patient` WHERE `id` = NEW.patient_id;
    IF patient_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入收货地址记录时，对应的患者不存在，请检查 patient_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table address
-- ----------------------------
DROP TRIGGER IF EXISTS `log_address_insert`;
delimiter ;;
CREATE TRIGGER `log_address_insert` AFTER INSERT ON `address` FOR EACH ROW BEGIN
    INSERT INTO `address_log` (`address_id`, `operation_type`, `operation_time`)
    VALUES (NEW.id, 'INSERT', NOW());
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table address
-- ----------------------------
DROP TRIGGER IF EXISTS `check_new_patient_exists_before_update_address`;
delimiter ;;
CREATE TRIGGER `check_new_patient_exists_before_update_address` BEFORE UPDATE ON `address` FOR EACH ROW BEGIN
    DECLARE patient_count INT;
    SELECT COUNT(*) INTO patient_count FROM `patient` WHERE `id` = NEW.patient_id;
    IF patient_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新收货地址记录关联的患者时，新的患者不存在，请检查 patient_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table disease
-- ----------------------------
DROP TRIGGER IF EXISTS `log_disease_insert`;
delimiter ;;
CREATE TRIGGER `log_disease_insert` AFTER INSERT ON `disease` FOR EACH ROW BEGIN
    INSERT INTO `disease_insert_log` (`disease_id`, `insert_time`)
    VALUES (NEW.id, NOW());
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `check_age_before_insert`;
delimiter ;;
CREATE TRIGGER `check_age_before_insert` BEFORE INSERT ON `employee` FOR EACH ROW BEGIN
    IF NEW.age < 0 OR NEW.age > 120 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到120岁之间';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `check_phone_before_insert`;
delimiter ;;
CREATE TRIGGER `check_phone_before_insert` BEFORE INSERT ON `employee` FOR EACH ROW BEGIN
    IF NOT NEW.phone REGEXP '^[0-9]{11}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '手机号码格式不正确';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `check_age_before_insert_employee`;
delimiter ;;
CREATE TRIGGER `check_age_before_insert_employee` BEFORE INSERT ON `employee` FOR EACH ROW BEGIN
    IF NEW.age < 0 OR NEW.age > 120 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到120岁之间';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `check_age_before_update`;
delimiter ;;
CREATE TRIGGER `check_age_before_update` BEFORE UPDATE ON `employee` FOR EACH ROW BEGIN
    IF NEW.age < 0 OR NEW.age > 120 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到120岁之间';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table information
-- ----------------------------
DROP TRIGGER IF EXISTS `check_patient_exists_before_insert_information`;
delimiter ;;
CREATE TRIGGER `check_patient_exists_before_insert_information` BEFORE INSERT ON `information` FOR EACH ROW BEGIN
    DECLARE patient_count INT;
    SELECT COUNT(*) INTO patient_count FROM `patient` WHERE `id` = NEW.patient_id;
    IF patient_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入联系方式记录时，对应的患者不存在，请检查 patient_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table information
-- ----------------------------
DROP TRIGGER IF EXISTS `log_information_insert`;
delimiter ;;
CREATE TRIGGER `log_information_insert` AFTER INSERT ON `information` FOR EACH ROW BEGIN
    INSERT INTO `information_log` (`information_id`, `operation_type`, `operation_time`)
    VALUES (NEW.id, 'INSERT', NOW());
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table information
-- ----------------------------
DROP TRIGGER IF EXISTS `check_new_patient_exists_before_update_information`;
delimiter ;;
CREATE TRIGGER `check_new_patient_exists_before_update_information` BEFORE UPDATE ON `information` FOR EACH ROW BEGIN
    DECLARE patient_count INT;
    SELECT COUNT(*) INTO patient_count FROM `patient` WHERE `id` = NEW.patient_id;
    IF patient_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新联系方式记录关联的患者时，新的患者不存在，请检查 patient_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `check_expiration_date_before_insert_medicine`;
delimiter ;;
CREATE TRIGGER `check_expiration_date_before_insert_medicine` BEFORE INSERT ON `medicine` FOR EACH ROW BEGIN
    IF NEW.expiration_date < CURDATE() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '药品有效期截止日期不能早于当前日期，请重新填写！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `convert_name_to_uppercase_before_insert_medicine`;
delimiter ;;
CREATE TRIGGER `convert_name_to_uppercase_before_insert_medicine` BEFORE INSERT ON `medicine` FOR EACH ROW BEGIN
    SET NEW.name = UPPER(NEW.name);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `check_stock_quantity_before_update_medicine`;
delimiter ;;
CREATE TRIGGER `check_stock_quantity_before_update_medicine` BEFORE UPDATE ON `medicine` FOR EACH ROW BEGIN
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '药品库存数量不能小于0，请重新设置库存数量！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `check_retail_price_before_update_medicine`;
delimiter ;;
CREATE TRIGGER `check_retail_price_before_update_medicine` BEFORE UPDATE ON `medicine` FOR EACH ROW BEGIN
    IF NEW.retail_price < NEW.purchase_price THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '药品零售价格不能低于进货价格，请重新设置零售价格！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `convert_name_to_uppercase_before_update_medicine`;
delimiter ;;
CREATE TRIGGER `convert_name_to_uppercase_before_update_medicine` BEFORE UPDATE ON `medicine` FOR EACH ROW BEGIN
    SET NEW.name = UPPER(NEW.name);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table medicine
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_relation_on_medicine_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_relation_on_medicine_delete` AFTER DELETE ON `medicine` FOR EACH ROW BEGIN
    DELETE FROM `order_medicine_relation` WHERE `medicine_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `check_patient_employee_exists_before_insert_order`;
delimiter ;;
CREATE TRIGGER `check_patient_employee_exists_before_insert_order` BEFORE INSERT ON `order` FOR EACH ROW BEGIN
    DECLARE patient_count INT;
    DECLARE employee_count INT;

    -- 检查患者是否存在
    SELECT COUNT(*) INTO patient_count FROM `patient` WHERE `id` = NEW.patient_id;
    IF patient_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入订单记录时，对应的患者不存在，请检查 patient_id！';
    END IF;

    -- 检查员工是否存在
    SELECT COUNT(*) INTO employee_count FROM `employee` WHERE `id` = NEW.employee_id;
    IF employee_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入订单记录时，对应的员工不存在，请检查 employee_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `set_default_order_status_before_insert_order`;
delimiter ;;
CREATE TRIGGER `set_default_order_status_before_insert_order` BEFORE INSERT ON `order` FOR EACH ROW BEGIN
    IF NEW.total_amount > 1000 THEN
        SET NEW.order_status = 2;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `check_order_status_before_update_order`;
delimiter ;;
CREATE TRIGGER `check_order_status_before_update_order` BEFORE UPDATE ON `order` FOR EACH ROW BEGIN
    IF NEW.order_status NOT IN (1, 2, 3) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单状态值只能为1（未完成）、2（已完成）或3（取消），请重新设置！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_relation_on_order_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_relation_on_order_delete` AFTER DELETE ON `order` FOR EACH ROW BEGIN
    DELETE FROM `order_medicine_relation` WHERE `order_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order_medicine_relation
-- ----------------------------
DROP TRIGGER IF EXISTS `check_order_medicine_exists_before_insert_relation`;
delimiter ;;
CREATE TRIGGER `check_order_medicine_exists_before_insert_relation` BEFORE INSERT ON `order_medicine_relation` FOR EACH ROW BEGIN
    DECLARE order_count INT;
    DECLARE medicine_count INT;

    -- 检查订单是否存在
    SELECT COUNT(*) INTO order_count FROM `order` WHERE `id` = NEW.order_id;
    IF order_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入订单药品关联记录时，对应的订单不存在，请检查 order_id！';
    END IF;

    -- 检查药品是否存在
    SELECT COUNT(*) INTO medicine_count FROM `medicine` WHERE `id` = NEW.medicine_id;
    IF medicine_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入订单药品关联记录时，对应的药品不存在，请检查 medicine_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order_medicine_relation
-- ----------------------------
DROP TRIGGER IF EXISTS `check_quantity_before_update_relation`;
delimiter ;;
CREATE TRIGGER `check_quantity_before_update_relation` BEFORE UPDATE ON `order_medicine_relation` FOR EACH ROW BEGIN
    IF NEW.quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单中药品的数量不能小于0，请重新设置数量！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `check_age_before_insert_patient`;
delimiter ;;
CREATE TRIGGER `check_age_before_insert_patient` BEFORE INSERT ON `patient` FOR EACH ROW BEGIN
    IF NEW.age < 0 OR NEW.age > 120 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到120岁之间';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `check_id_card_number_before_insert_patient`;
delimiter ;;
CREATE TRIGGER `check_id_card_number_before_insert_patient` BEFORE INSERT ON `patient` FOR EACH ROW BEGIN
    IF CHAR_LENGTH(NEW.id_card_number) <> 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '身份证号码格式不正确';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `check_age_before_update_patient`;
delimiter ;;
CREATE TRIGGER `check_age_before_update_patient` BEFORE UPDATE ON `patient` FOR EACH ROW BEGIN
    IF NEW.age < 0 OR NEW.age > 120 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到120岁之间';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_disease_on_patient_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_disease_on_patient_delete` AFTER DELETE ON `patient` FOR EACH ROW BEGIN
    DELETE FROM `disease` WHERE `patient_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_address_on_patient_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_address_on_patient_delete` AFTER DELETE ON `patient` FOR EACH ROW BEGIN
    DELETE FROM `address` WHERE `patient_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table patient
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_information_on_patient_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_information_on_patient_delete` AFTER DELETE ON `patient` FOR EACH ROW BEGIN
    DELETE FROM `information` WHERE `patient_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_activitie
-- ----------------------------
DROP TRIGGER IF EXISTS `check_time_order_before_insert_promotion`;
delimiter ;;
CREATE TRIGGER `check_time_order_before_insert_promotion` BEFORE INSERT ON `promotion_activitie` FOR EACH ROW BEGIN
    IF NEW.start_time >= NEW.end_time THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '促销活动开始时间必须早于结束时间，请重新设置时间！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_activitie
-- ----------------------------
DROP TRIGGER IF EXISTS `convert_name_to_uppercase_before_insert_promotion`;
delimiter ;;
CREATE TRIGGER `convert_name_to_uppercase_before_insert_promotion` BEFORE INSERT ON `promotion_activitie` FOR EACH ROW BEGIN
    SET NEW.name = UPPER(NEW.name);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_activitie
-- ----------------------------
DROP TRIGGER IF EXISTS `check_time_order_before_update_promotion`;
delimiter ;;
CREATE TRIGGER `check_time_order_before_update_promotion` BEFORE UPDATE ON `promotion_activitie` FOR EACH ROW BEGIN
    IF NEW.start_time >= NEW.end_time THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '促销活动开始时间必须早于结束时间，请重新设置时间！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_activitie
-- ----------------------------
DROP TRIGGER IF EXISTS `convert_name_to_uppercase_before_update_promotion`;
delimiter ;;
CREATE TRIGGER `convert_name_to_uppercase_before_update_promotion` BEFORE UPDATE ON `promotion_activitie` FOR EACH ROW BEGIN
    SET NEW.name = UPPER(NEW.name);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_activitie
-- ----------------------------
DROP TRIGGER IF EXISTS `cascade_delete_relation_on_promotion_delete`;
delimiter ;;
CREATE TRIGGER `cascade_delete_relation_on_promotion_delete` AFTER DELETE ON `promotion_activitie` FOR EACH ROW BEGIN
    DELETE FROM `promotion_medicine_relation` WHERE `promotion_id` = OLD.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_medicine_relation
-- ----------------------------
DROP TRIGGER IF EXISTS `check_promotion_medicine_exists_before_insert_relation`;
delimiter ;;
CREATE TRIGGER `check_promotion_medicine_exists_before_insert_relation` BEFORE INSERT ON `promotion_medicine_relation` FOR EACH ROW BEGIN
    DECLARE promotion_count INT;
    DECLARE medicine_count INT;

    -- 检查促销活动是否存在
    SELECT COUNT(*) INTO promotion_count FROM `promotion_activitie` WHERE `id` = NEW.promotion_id;
    IF promotion_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入促销药品关联记录时，对应的促销活动不存在，请检查 promotion_id！';
    END IF;

    -- 检查药品是否存在
    SELECT COUNT(*) INTO medicine_count FROM `medicine` WHERE `id` = NEW.medicine_id;
    IF medicine_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入促销药品关联记录时，对应的药品不存在，请检查 medicine_id！';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table promotion_medicine_relation
-- ----------------------------
DROP TRIGGER IF EXISTS `prevent_duplicate_relation_insert`;
delimiter ;;
CREATE TRIGGER `prevent_duplicate_relation_insert` BEFORE INSERT ON `promotion_medicine_relation` FOR EACH ROW BEGIN
    SET @count = (SELECT COUNT(*) FROM `promotion_medicine_relation`
                  WHERE `promotion_id` = NEW.promotion_id
                    AND `medicine_id` = NEW.medicine_id);
    IF @count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该促销活动已关联此药品，请勿重复关联！';
    END IF;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;