/*
 Navicat Premium Data Transfer

 Source Server         : a
 Source Server Type    : MySQL
 Source Server Version : 90100
 Source Host           : localhost:3306
 Source Schema         : db

 Target Server Type    : MySQL
 Target Server Version : 90100
 File Encoding         : 65001

 Date: 30/12/2024 13:03:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `wage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `work` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, '江远', '5000', '制作豆类糖水');
INSERT INTO `employee` VALUES (2, '沈煜', '3000', '制作果类糖水');
INSERT INTO `employee` VALUES (3, '周余', '3000', '制作奶类糖水');
INSERT INTO `employee` VALUES (4, '许中', '5000', '收银员');
INSERT INTO `employee` VALUES (5, '周米', '1000', '制作滋补类糖水');
INSERT INTO `employee` VALUES (6, '家回', '5000', '收银员');

-- ----------------------------
-- Table structure for good
-- ----------------------------
DROP TABLE IF EXISTS `good`;
CREATE TABLE `good`  (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `price` float NOT NULL,
  `surplus` int NULL DEFAULT NULL,
  `ingredient` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `employee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `name`, `price`) USING BTREE,
  INDEX `ingredient_id`(`ingredient` ASC) USING BTREE,
  UNIQUE INDEX `name`(`name` ASC) USING BTREE,
  INDEX `employee`(`employee` ASC) USING BTREE,
  UNIQUE INDEX `id`(`id` ASC) USING BTREE,
  INDEX `price`(`price` ASC) USING BTREE,
  CONSTRAINT `employee` FOREIGN KEY (`employee`) REFERENCES `employee` (`name`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `ingredient_id` FOREIGN KEY (`ingredient`) REFERENCES `ingredient` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of good
-- ----------------------------
INSERT INTO `good` VALUES (1, '红豆糖水', '豆类', 14, 10, '红豆', '江远');
INSERT INTO `good` VALUES (2, '绿豆沙', '豆类', 12, 10, '绿豆', '许中');
INSERT INTO `good` VALUES (3, '芋圆西米露', '果类', 15, 2, '芋头', '沈煜');
INSERT INTO `good` VALUES (4, '姜汁撞奶', '奶类', 14, 3, '牛奶', '周余');
INSERT INTO `good` VALUES (5, '莲子百合红豆沙', '豆类', 18, 4, '莲子', '江远');
INSERT INTO `good` VALUES (6, '双皮奶', '奶类', 16, 4, '牛奶', '周余');
INSERT INTO `good` VALUES (7, '雪梨银耳汤', '果类', 13, 5, '雪梨', '沈煜');
INSERT INTO `good` VALUES (8, '椰汁西米露', '果类', 17, 5, '西米', '沈煜');
INSERT INTO `good` VALUES (9, '红枣枸杞糖水', '滋补类', 19, 6, '红枣', '周米');
INSERT INTO `good` VALUES (10, '地瓜糖水', '滋补类', 10, 4, '地瓜', '周米');

-- ----------------------------
-- Table structure for ingredient
-- ----------------------------
DROP TABLE IF EXISTS `ingredient`;
CREATE TABLE `ingredient`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `surplus` int NULL DEFAULT NULL,
  `supplier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `money` float NULL DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`, `name`) USING BTREE,
  UNIQUE INDEX `name`(`name` ASC) USING BTREE,
  INDEX `id`(`id` ASC) USING BTREE,
  INDEX `supplier`(`supplier` ASC) USING BTREE,
  CONSTRAINT `supplier` FOREIGN KEY (`supplier`) REFERENCES `supplier` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ingredient
-- ----------------------------
INSERT INTO `ingredient` VALUES (1, '红豆', 20, '王五', 200, '2024-12-27 20:24:03');
INSERT INTO `ingredient` VALUES (2, '绿豆', 20, '王五', 100, '2025-02-01 20:36:21');
INSERT INTO `ingredient` VALUES (3, '椰浆', 15, '许伟', 600, '2025-01-05 20:37:15');
INSERT INTO `ingredient` VALUES (4, '冰糖', 36, '西西', 300, '2025-02-02 20:37:49');
INSERT INTO `ingredient` VALUES (5, '西米', 24, '李守', 600, '2024-12-17 20:38:16');
INSERT INTO `ingredient` VALUES (6, '牛奶', 12, '刘远', 700, '2025-01-04 20:38:49');
INSERT INTO `ingredient` VALUES (7, '莲子', 43, '刘明', 500, '2024-12-23 20:39:32');
INSERT INTO `ingredient` VALUES (8, '鸡蛋', 65, '谢芳', 300, '2025-01-01 20:40:14');
INSERT INTO `ingredient` VALUES (9, '雪梨', 13, '余周', 100, '2024-12-24 20:40:46');
INSERT INTO `ingredient` VALUES (10, '红枣', 45, '黄瑞', 800, '2024-12-24 20:41:32');
INSERT INTO `ingredient` VALUES (11, '地瓜', 45, '明寒', 400, '2025-01-02 20:42:03');
INSERT INTO `ingredient` VALUES (12, '芋头', 12, '明寒', 100, '2024-12-16 20:48:15');
INSERT INTO `ingredient` VALUES (13, '奶油', 56, '西西', 600, '2024-12-13 13:29:17');

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` int NOT NULL,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `date` datetime NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `money` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `name`) USING BTREE,
  UNIQUE INDEX `id`(`id` ASC) USING BTREE,
  INDEX `user_name`(`name` ASC) USING BTREE,
  CONSTRAINT `name` FOREIGN KEY (`name`) REFERENCES `users` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, '2芋圆西米露', '2024-12-26 01:22:00', 'Amber Mills', 22.00);
INSERT INTO `order` VALUES (2, '2绿豆沙', '2024-12-17 12:31:55', 'Nakamura Hina', 24.00);
INSERT INTO `order` VALUES (3, '1芋圆西米露', '2024-12-07 12:32:01', 'Nakamura Hina', 15.00);
INSERT INTO `order` VALUES (4, '1姜汁撞奶', '2024-12-11 12:32:05', 'Kono Ryota', 14.00);
INSERT INTO `order` VALUES (5, '2双皮奶', '2024-12-25 15:23:26', 'Lu Rui', 18.00);
INSERT INTO `order` VALUES (6, '3椰汁西米露', '2024-12-18 12:32:11', 'Fan Kwok Yin', 16.00);
INSERT INTO `order` VALUES (7, '1双皮奶', '2024-12-29 12:32:16', 'Noguchi Takuya', 16.00);
INSERT INTO `order` VALUES (8, '1地瓜糖水', '2024-12-09 12:32:21', 'Noguchi Takuya', 10.00);
INSERT INTO `order` VALUES (9, '2双皮奶', '2024-12-12 12:32:27', 'Esther Woods', 32.00);
INSERT INTO `order` VALUES (10, '1红枣枸杞糖水', '2024-12-25 12:32:31', 'Fan Kwok Yin', 19.00);
INSERT INTO `order` VALUES (11, '1姜汁撞奶', '2024-12-17 12:32:39', 'Nakamura Hina', 14.00);
INSERT INTO `order` VALUES (12, '3芋圆西米露', '2024-12-16 12:32:45', 'Noguchi Takuya', 15.00);
INSERT INTO `order` VALUES (13, NULL, NULL, 'Esther Woods', NULL);

-- ----------------------------
-- Table structure for ordercon
-- ----------------------------
DROP TABLE IF EXISTS `ordercon`;
CREATE TABLE `ordercon`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `orderid` int NULL DEFAULT NULL,
  `good` int NULL DEFAULT NULL,
  `number` int NULL DEFAULT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  `money` decimal(10, 2) NULL DEFAULT NULL,
  `state` enum('已完成','未完成') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `good_id`(`good` ASC) USING BTREE,
  INDEX `id`(`orderid` ASC) USING BTREE,
  CONSTRAINT `id` FOREIGN KEY (`orderid`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `good_id` FOREIGN KEY (`good`) REFERENCES `good` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ordercon
-- ----------------------------
INSERT INTO `ordercon` VALUES (1, 1, 1, 1, 10.00, 12.00, NULL);
INSERT INTO `ordercon` VALUES (2, 1, 2, 1, 12.00, 12.00, NULL);
INSERT INTO `ordercon` VALUES (3, 2, 3, 2, 12.00, 24.00, NULL);
INSERT INTO `ordercon` VALUES (4, 3, 3, 1, 15.00, 15.00, NULL);
INSERT INTO `ordercon` VALUES (5, 4, 4, 1, 14.00, 14.00, NULL);
INSERT INTO `ordercon` VALUES (6, 5, 6, 2, 16.00, 32.00, NULL);
INSERT INTO `ordercon` VALUES (7, 6, 8, 3, 17.00, 51.00, NULL);
INSERT INTO `ordercon` VALUES (8, 7, 6, 1, 16.00, 16.00, NULL);
INSERT INTO `ordercon` VALUES (9, 8, 10, 1, 10.00, 10.00, NULL);
INSERT INTO `ordercon` VALUES (10, 9, 6, 2, 16.00, 16.00, NULL);
INSERT INTO `ordercon` VALUES (11, 10, 9, 1, 19.00, 19.00, NULL);
INSERT INTO `ordercon` VALUES (12, 11, 4, 1, 14.00, 14.00, NULL);
INSERT INTO `ordercon` VALUES (13, 12, 3, 3, 15.00, 45.00, NULL);
INSERT INTO `ordercon` VALUES (14, 13, 7, 2, 13.00, 26.00, NULL);

-- ----------------------------
-- Table structure for supplier
-- ----------------------------
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier`  (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `ingredient` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `number` int NULL DEFAULT NULL,
  `state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `name`) USING BTREE,
  UNIQUE INDEX `name`(`name` ASC) USING BTREE,
  INDEX `ingredient`(`ingredient` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of supplier
-- ----------------------------
INSERT INTO `supplier` VALUES (1, '王五', '760-029-3801', '广西', '红豆，绿豆', 649, '合作');
INSERT INTO `supplier` VALUES (2, '许伟', '760-079-0782', '广西', '椰浆', 908, '合作');
INSERT INTO `supplier` VALUES (3, '西西', '838-088-7149', '广西', '冰糖', 832, '停止');
INSERT INTO `supplier` VALUES (4, '李守', '(20) 7943 9862', '广东', '西米', 268, '停止');
INSERT INTO `supplier` VALUES (5, '刘远', '760-3593-6621', '贵州', '牛奶', 619, '停止');
INSERT INTO `supplier` VALUES (6, '刘明', '838-198-4024', '云南', '莲子', 516, '合作');
INSERT INTO `supplier` VALUES (7, '谢芳', '162-4143-1043', '云南', '鸡蛋', 82, '合作');
INSERT INTO `supplier` VALUES (8, '余周', '(151) 485 3440', '湖南', '雪梨', 273, '合作');
INSERT INTO `supplier` VALUES (9, '黄瑞', '614-712-5158', '湖南', '红枣', 746, '合作');
INSERT INTO `supplier` VALUES (10, '明寒', '(116) 727 1411', '湖北', '地瓜', 542, '合作');

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int NOT NULL,
  `account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`, `name`) USING BTREE,
  UNIQUE INDEX `name`(`name` ASC) USING BTREE,
  INDEX `id`(`id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, '993', '123456', 'Amber Mills', '213-997-2362');
INSERT INTO `users` VALUES (2, '380', 'LCIbMnJ3CN', 'Murakami Kasumi', '10-0045-3026');
INSERT INTO `users` VALUES (3, '600', 'F3fNKq0FkG', 'Fan Kwok Yin', '213-516-0115');
INSERT INTO `users` VALUES (4, '899', 'OBZFIStIf2', 'Nakamura Hina', '186-0063-2734');
INSERT INTO `users` VALUES (5, '936', 'BlZxkl0FZP', 'Kevin Washington', '5832 060970');
INSERT INTO `users` VALUES (6, '992', 'FWf0bzYgyA', 'Esther Woods', '213-769-7280');
INSERT INTO `users` VALUES (7, '954', 'etgXH3MsNj', 'Kono Ryota', '52-164-7344');
INSERT INTO `users` VALUES (8, '23', 'iyXymwxLNV', 'Lu Rui', '7459 798893');
INSERT INTO `users` VALUES (9, '983', '8hi7jrqR38', 'Noguchi Takuya', '7784 176690');
INSERT INTO `users` VALUES (10, '839', 'XGQmJC8lch', 'Fong Kwok Yin', '10-6181-9374');
INSERT INTO `users` VALUES (11, '658', '123456789', 'xingxu', '124-654-753');

-- ----------------------------
-- View structure for 原料花费表
-- ----------------------------
DROP VIEW IF EXISTS `原料花费表`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `原料花费表` AS select `ingredient`.`name` AS `name`,`ingredient`.`money` AS `money` from `ingredient` order by `ingredient`.`money`;

-- ----------------------------
-- View structure for 原料补货表
-- ----------------------------
DROP VIEW IF EXISTS `原料补货表`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `原料补货表` AS select `ingredient`.`name` AS `name`,`ingredient`.`surplus` AS `surplus`,`ingredient`.`supplier` AS `supplier`,`ingredient`.`money` AS `money`,`supplier`.`phone` AS `phone`,`supplier`.`address` AS `address`,`supplier`.`number` AS `number` from (`ingredient` join `supplier` on((`ingredient`.`supplier` = `supplier`.`name`)));

-- ----------------------------
-- View structure for 甜品制作所需信息表
-- ----------------------------
DROP VIEW IF EXISTS `甜品制作所需信息表`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `甜品制作所需信息表` AS select `g`.`employee` AS `employee`,`g`.`name` AS `name`,`g`.`type` AS `type`,`g`.`price` AS `price`,`g`.`ingredient` AS `ingredient`,`ingredient`.`surplus` AS `surplus` from ((`employee` `e` join `good` `g` on((`e`.`name` = `g`.`employee`))) join `ingredient` on((`g`.`ingredient` = `ingredient`.`name`)));

-- ----------------------------
-- View structure for 订单和用户信息表
-- ----------------------------
DROP VIEW IF EXISTS `订单和用户信息表`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `订单和用户信息表` AS select `o`.`content` AS `content`,`o`.`date` AS `date`,`u`.`name` AS `name`,`o`.`money` AS `money`,`u`.`phone` AS `phone`,`u`.`account` AS `account` from (`order` `o` join `users` `u` on((`o`.`name` = `u`.`name`)));

-- ----------------------------
-- View structure for 订单数量最多的甜品
-- ----------------------------
DROP VIEW IF EXISTS `订单数量最多的甜品`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `订单数量最多的甜品` AS select `good`.`name` AS `name`,`order`.`id` AS `id`,`order`.`content` AS `content`,`ordercon`.`number` AS `number` from ((`good` join `ordercon` on((`good`.`id` = `ordercon`.`good`))) join `order` on((`order`.`id` = `ordercon`.`orderid`))) order by `ordercon`.`number` desc;

-- ----------------------------
-- Procedure structure for cancel_order
-- ----------------------------
DROP PROCEDURE IF EXISTS `cancel_order`;
delimiter ;;
CREATE PROCEDURE `cancel_order`(IN p_user VARCHAR(255),
    IN p_order_id INT)
BEGIN
    -- 删除记录
    DELETE FROM `order`
    WHERE `name` = p_user AND id = p_order_id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for create_new_user
-- ----------------------------
DROP PROCEDURE IF EXISTS `create_new_user`;
delimiter ;;
CREATE PROCEDURE `create_new_user`(IN p_account VARCHAR(255),
    IN p_password VARCHAR(255), -- 注意：这里存储的是明文密码，通常应该存储哈希值
    IN p_name VARCHAR(255),
    IN p_phone VARCHAR(255))
BEGIN
    -- 检查用户名是否已存在
    IF EXISTS (SELECT 1 FROM users WHERE account = p_account) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username already exists';
    ELSE
        -- 如果用户名不存在，则插入新用户
        INSERT INTO users (account, password, name, phone)
        VALUES (p_account, p_password, p_name, p_phone);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for getUserOrders
-- ----------------------------
DROP PROCEDURE IF EXISTS `getUserOrders`;
delimiter ;;
CREATE PROCEDURE `getUserOrders`(IN p_user VARCHAR(255))
BEGIN
    
    SELECT 
        o.id AS order_id,
        o.content, -- 订单内容的字段
        o.`name` AS order_name, 
        o.date,
        oc.good, -- 商品名称的字段
        oc.price AS good_price -- 商品价格的字段
    FROM 
        `order` o
    LEFT JOIN 
        `ordercon` oc ON o.id = oc.orderid -- 正确的连接条件
    WHERE 
       
        o.`name` = p_user; -- 
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for place_dessert_order
-- ----------------------------
DROP PROCEDURE IF EXISTS `place_dessert_order`;
delimiter ;;
CREATE PROCEDURE `place_dessert_order`()
BEGIN 
UPDATE ingredient
INNER JOIN good ON ingredient.`name`=good.`name`
SET ingredient.surplus=ingredient.surplus-(NEW.surplus-OLD.surplus)
WHERE good.id=NEW.id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for update_user_info
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_user_info`;
delimiter ;;
CREATE PROCEDURE `update_user_info`(IN p_user_id INT,
    IN p_account VARCHAR(255),
    IN p_password VARCHAR(255), -- 注意：通常应存储密码的哈希值
    IN p_name VARCHAR(255),
    IN p_phone VARCHAR(255))
BEGIN
    -- 更新用户信息
    UPDATE users
    SET account = p_account,
        `password` = p_password, 
        name = p_name,
        phone = p_phone
    WHERE id = p_user_id;

    
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table good
-- ----------------------------
DROP TRIGGER IF EXISTS `ingredient_number`;
delimiter ;;
CREATE TRIGGER `ingredient_number` AFTER UPDATE ON `good` FOR EACH ROW BEGIN
   
    UPDATE ingredient
    INNER JOIN good ON ingredient.`name` = good.`name`
    SET ingredient.surplus = ingredient.surplus - 
        (NEW.surplus - OLD.surplus) 
    WHERE good.id = NEW.id;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table ordercon
-- ----------------------------
DROP TRIGGER IF EXISTS `reduce_supplier`;
delimiter ;;
CREATE TRIGGER `reduce_supplier` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
    IF new.state = '已完成'  THEN
        UPDATE good
        SET `supplier` = `supplier` - 1
        WHERE id = new.good;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table ordercon
-- ----------------------------
DROP TRIGGER IF EXISTS `order_get_price`;
delimiter ;;
CREATE TRIGGER `order_get_price` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
    DECLARE good_price DECIMAL(10, 2);
    SELECT price INTO good_price
    FROM good
    WHERE good.id = NEW.good;
		
    SET NEW.price = good_price;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table ordercon
-- ----------------------------
DROP TRIGGER IF EXISTS `count_money`;
delimiter ;;
CREATE TRIGGER `count_money` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
    SET NEW.money = NEW.price * NEW.number;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;