/*
 Navicat Premium Dump SQL

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 90100 (9.1.0)
 Source Host           : localhost:3306
 Source Schema         : demo

 Target Server Type    : MySQL
 Target Server Version : 90100 (9.1.0)
 File Encoding         : 65001

 Date: 29/12/2024 21:43:27
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`  (
  `CustomerID` int NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `ContactNumber` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CustomerAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`CustomerID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (1, '客户A', '13800000001', 'customerA@example.com', '北京市海淀区XX路XX号');
INSERT INTO `customer` VALUES (2, '客户B', '13800000002', 'customerB@example.com', '上海市黄浦区XX路XX号');
INSERT INTO `customer` VALUES (3, '客户C', '13800000003', 'customerC@example.com', '广州市越秀区XX路XX号');
INSERT INTO `customer` VALUES (4, '客户D', '13800000004', 'customerD@example.com', '深圳市福田区XX路XX号');
INSERT INTO `customer` VALUES (5, '客户E', '13800000005', 'customerE@example.com', '成都市锦江区XX路XX号');
INSERT INTO `customer` VALUES (6, '客户F', '13800000006', 'customerF@example.com', '杭州市拱墅区XX路XX号');
INSERT INTO `customer` VALUES (7, '客户G', '13800000007', 'customerG@example.com', '武汉市武昌区XX路XX号');
INSERT INTO `customer` VALUES (8, '客户H', '13800000008', 'customerH@example.com', '南京市玄武区XX路XX号');
INSERT INTO `customer` VALUES (9, '客户I', '13800000009', 'customerI@example.com', '西安市碑林区XX路XX号');
INSERT INTO `customer` VALUES (10, '客户J', '13800000010', 'customerJ@example.com', '重庆市江北区XX路XX号');
INSERT INTO `customer` VALUES (11, '客户A', '13800000001', 'customerA@example.com', '北京市海淀区XX路XX号');
INSERT INTO `customer` VALUES (12, '客户B', '13800000002', 'customerB@example.com', '上海市黄浦区XX路XX号');
INSERT INTO `customer` VALUES (13, '客户C', '13800000003', 'customerC@example.com', '广州市越秀区XX路XX号');
INSERT INTO `customer` VALUES (14, '客户D', '13800000004', 'customerD@example.com', '深圳市福田区XX路XX号');
INSERT INTO `customer` VALUES (15, '客户E', '13800000005', 'customerE@example.com', '成都市锦江区XX路XX号');
INSERT INTO `customer` VALUES (16, '客户F', '13800000006', 'customerF@example.com', '杭州市拱墅区XX路XX号');
INSERT INTO `customer` VALUES (17, '客户G', '13800000007', 'customerG@example.com', '武汉市武昌区XX路XX号');
INSERT INTO `customer` VALUES (18, '客户H', '13800000008', 'customerH@example.com', '南京市玄武区XX路XX号');
INSERT INTO `customer` VALUES (19, '客户王继威', '13800000009', 'customerI@example.com', '西安市碑林区XX路XX号');
INSERT INTO `customer` VALUES (20, '客户李保航', '13800000010', 'customerJ@example.com', '重庆市江北区XX路XX号');

-- ----------------------------
-- Table structure for deleted_goods
-- ----------------------------
DROP TABLE IF EXISTS `deleted_goods`;
CREATE TABLE `deleted_goods`  (
  `DeletedGoodsID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `GoodsName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `DeletedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of deleted_goods
-- ----------------------------

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `EmployeeID` int NOT NULL AUTO_INCREMENT,
  `EmployeeName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `EmployeePosition` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `ContactNumber` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`EmployeeID`) USING BTREE,
  INDEX `idx_employee_name`(`EmployeeName` ASC) USING BTREE,
  UNIQUE INDEX `idx_employee_employeeid`(`EmployeeID` ASC) USING BTREE,
  INDEX `idx_employee_EmployeePosition`(`EmployeePosition` ASC) USING BTREE,
  INDEX `idx_employee_employeename`(`EmployeeName` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, '员工A', '仓库管理员', '13900000001', 'employeeA@example.com');
INSERT INTO `employee` VALUES (2, '员工B', '物流配送人员', '13900000002', 'employeeB@example.com');
INSERT INTO `employee` VALUES (3, '员工C', '采购人员', '13900000003', 'employeeC@example.com');
INSERT INTO `employee` VALUES (4, '员工D', '仓库管理员', '13900000004', 'employeeD@example.com');
INSERT INTO `employee` VALUES (5, '员工E', '物流配送人员', '13900000005', 'employeeE@example.com');
INSERT INTO `employee` VALUES (6, '员工F', '采购人员', '13900000006', 'employeeF@example.com');
INSERT INTO `employee` VALUES (7, '员工G', '仓库管理员', '13900000007', 'employeeG@example.com');
INSERT INTO `employee` VALUES (8, '员工H', '物流配送人员', '13900000008', 'employeeH@example.com');
INSERT INTO `employee` VALUES (9, '员工I', '采购人员', '13900000009', 'employeeI@example.com');
INSERT INTO `employee` VALUES (10, '员工J', '仓库管理员', '13900000010', 'employeeJ@example.com');

-- ----------------------------
-- Table structure for employee_log
-- ----------------------------
DROP TABLE IF EXISTS `employee_log`;
CREATE TABLE `employee_log`  (
  `LogID` int NOT NULL AUTO_INCREMENT,
  `EmployeeID` int NULL DEFAULT NULL,
  `OldEmployeeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `NewEmployeeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `OldDepartment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `NewDepartment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `ChangeDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`LogID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee_log
-- ----------------------------

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `GoodsName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `GoodsType` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `ProductionDate` date NULL DEFAULT NULL,
  `SafetyStock` int NOT NULL,
  `PurchaseUnitPrice` decimal(8, 2) NOT NULL,
  `SupplierID` int NULL DEFAULT NULL,
  PRIMARY KEY (`GoodsID`) USING BTREE,
  INDEX `FK_Goods_Supplier`(`SupplierID` ASC) USING BTREE,
  INDEX `idx_goods_name`(`GoodsName` ASC) USING BTREE,
  INDEX `idx_goods_type`(`GoodsType` ASC) USING BTREE,
  INDEX `idx_production_date`(`ProductionDate` ASC) USING BTREE,
  UNIQUE INDEX `idx_goods_goodsid`(`GoodsID` ASC) USING BTREE,
  INDEX `idx_goods_GoodsType`(`GoodsType` ASC) USING BTREE,
  INDEX `idx_goods_PurchaseUnitPrice`(`PurchaseUnitPrice` ASC) USING BTREE,
  INDEX `idx_goods_GoodsType_PurchaseUnitPrice`(`GoodsType` ASC, `PurchaseUnitPrice` ASC) USING BTREE,
  CONSTRAINT `FK_Goods_Supplier` FOREIGN KEY (`SupplierID`) REFERENCES `supplier` (`SupplierID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `goods_chk_1` CHECK (`SafetyStock` >= 0),
  CONSTRAINT `goods_chk_2` CHECK (`PurchaseUnitPrice` >= 0)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('G001', '电脑显示器', '电子产品', '2023-01-01', 10, 600.00, 1);
INSERT INTO `goods` VALUES ('G002', '运动鞋', '服装', '2023-02-01', 20, 80.00, 2);
INSERT INTO `goods` VALUES ('G003', '苹果手机', '电子产品', '2023-03-01', 15, 800.00, 3);
INSERT INTO `goods` VALUES ('G004', '牛奶', '食品', '2023-04-01', 30, 5.00, 4);
INSERT INTO `goods` VALUES ('G005', '衬衫', '服装', '2023-05-01', 18, 50.00, 5);
INSERT INTO `goods` VALUES ('G006', '打印机', '电子产品', '2023-06-01', 8, 300.00, 6);
INSERT INTO `goods` VALUES ('G007', '面包', '食品', '2023-07-01', 25, 3.00, 7);
INSERT INTO `goods` VALUES ('G008', '牛仔裤', '服装', '2023-08-01', 22, 60.00, 8);
INSERT INTO `goods` VALUES ('G009', '平板电脑', '电子产品', '2023-09-01', 12, 600.00, 9);
INSERT INTO `goods` VALUES ('G010', '果汁', '食品', '2023-10-01', 35, 4.00, 10);

-- ----------------------------
-- Table structure for inboundrecord
-- ----------------------------
DROP TABLE IF EXISTS `inboundrecord`;
CREATE TABLE `inboundrecord`  (
  `InboundRecordID` int NOT NULL AUTO_INCREMENT,
  `GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `InboundQuantity` int NOT NULL,
  `InboundDate` date NOT NULL,
  `EmployeeID` int NOT NULL,
  PRIMARY KEY (`InboundRecordID`) USING BTREE,
  INDEX `idx_inbound_record_goods_id`(`GoodsID` ASC) USING BTREE,
  INDEX `idx_inbound_record_warehouse_id`(`WarehouseID` ASC) USING BTREE,
  INDEX `idx_inbound_record_employee_id`(`EmployeeID` ASC) USING BTREE,
  CONSTRAINT `FK_InboundRecord_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_InboundRecord_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_InboundRecord_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `inboundrecord_chk_1` CHECK (`InboundQuantity` > 0)
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of inboundrecord
-- ----------------------------
INSERT INTO `inboundrecord` VALUES (1, 'G001', 'WH01', 10, '2024-01-01', 1);
INSERT INTO `inboundrecord` VALUES (2, 'G002', 'WH02', 20, '2024-01-02', 2);
INSERT INTO `inboundrecord` VALUES (3, 'G003', 'WH03', 15, '2024-01-03', 3);
INSERT INTO `inboundrecord` VALUES (4, 'G004', 'WH04', 30, '2024-01-04', 4);
INSERT INTO `inboundrecord` VALUES (5, 'G005', 'WH05', 18, '2024-01-05', 5);
INSERT INTO `inboundrecord` VALUES (6, 'G006', 'WH06', 8, '2024-01-06', 6);
INSERT INTO `inboundrecord` VALUES (7, 'G007', 'WH07', 25, '2024-01-07', 7);
INSERT INTO `inboundrecord` VALUES (8, 'G008', 'WH08', 22, '2024-01-08', 8);
INSERT INTO `inboundrecord` VALUES (9, 'G009', 'WH09', 12, '2024-01-09', 9);
INSERT INTO `inboundrecord` VALUES (10, 'G010', 'WH10', 35, '2024-01-10', 10);
INSERT INTO `inboundrecord` VALUES (11, 'G001', 'WH01', 5, '2024-01-11', 1);
INSERT INTO `inboundrecord` VALUES (12, 'G002', 'WH02', 10, '2024-01-12', 2);
INSERT INTO `inboundrecord` VALUES (13, 'G003', 'WH03', 8, '2024-01-13', 3);
INSERT INTO `inboundrecord` VALUES (14, 'G004', 'WH04', 15, '2024-01-14', 4);
INSERT INTO `inboundrecord` VALUES (15, 'G005', 'WH05', 10, '2024-01-15', 5);
INSERT INTO `inboundrecord` VALUES (16, 'G006', 'WH06', 5, '2024-01-16', 6);
INSERT INTO `inboundrecord` VALUES (17, 'G007', 'WH07', 15, '2024-01-17', 7);
INSERT INTO `inboundrecord` VALUES (18, 'G008', 'WH08', 10, '2024-01-18', 8);
INSERT INTO `inboundrecord` VALUES (19, 'G009', 'WH09', 8, '2024-01-19', 9);
INSERT INTO `inboundrecord` VALUES (20, 'G010', 'WH10', 20, '2024-01-20', 10);

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `OrderDate` datetime NOT NULL,
  `CustomerID` int NOT NULL,
  `OrderStatus` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `customer_id` int NULL DEFAULT NULL,
  PRIMARY KEY (`OrderID`) USING BTREE,
  INDEX `idx_order_date`(`OrderDate` ASC) USING BTREE,
  INDEX `idx_customer_id`(`CustomerID` ASC) USING BTREE,
  UNIQUE INDEX `idx_order_orderid`(`OrderID` ASC) USING BTREE,
  INDEX `idx_order_customerid`(`CustomerID` ASC) USING BTREE,
  INDEX `idx_order_orderdate`(`OrderDate` ASC) USING BTREE,
  INDEX `idx_order_status_date`(`OrderStatus` ASC, `OrderDate` ASC) USING BTREE,
  CONSTRAINT `FK_Order_Customer` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '2024-01-01 00:00:00', 1, 'Completed', 101);
INSERT INTO `order` VALUES ('10', '2024-01-10 00:00:00', 5, 'Delivered', 105);
INSERT INTO `order` VALUES ('2', '2024-01-02 00:00:00', 2, 'Pending', 102);
INSERT INTO `order` VALUES ('3', '2024-01-03 00:00:00', 3, 'Processing', 103);
INSERT INTO `order` VALUES ('4', '2024-01-04 00:00:00', 4, 'Shipped', 104);
INSERT INTO `order` VALUES ('5', '2024-01-05 00:00:00', 5, 'Cancelled', 105);
INSERT INTO `order` VALUES ('6', '2024-01-06 00:00:00', 1, 'Completed', 101);
INSERT INTO `order` VALUES ('7', '2024-01-07 00:00:00', 2, 'Pending', 102);
INSERT INTO `order` VALUES ('8', '2024-01-08 00:00:00', 3, 'Processing', 103);
INSERT INTO `order` VALUES ('9', '2024-01-09 00:00:00', 4, 'Shipped', 104);

-- ----------------------------
-- Table structure for order_log
-- ----------------------------
DROP TABLE IF EXISTS `order_log`;
CREATE TABLE `order_log`  (
  `LogID` int NOT NULL AUTO_INCREMENT,
  `OrderID` int NULL DEFAULT NULL,
  `OldStatus` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `NewStatus` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `ChangeDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`LogID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_log
-- ----------------------------

-- ----------------------------
-- Table structure for orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `orderdetail`;
CREATE TABLE `orderdetail`  (
  `OrderDetailID` int NOT NULL AUTO_INCREMENT,
  `OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `GoodsName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `GoodsSpecification` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `OrderQuantity` int NOT NULL,
  `UnitPrice` decimal(10, 2) NOT NULL,
  `TotalPrice` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`OrderDetailID`) USING BTREE,
  INDEX `idx_order_detail_order_id`(`OrderID` ASC) USING BTREE,
  INDEX `idx_order_detail_goods_id`(`GoodsID` ASC) USING BTREE,
  CONSTRAINT `FK_OrderDetail_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_OrderDetail_Order` FOREIGN KEY (`OrderID`) REFERENCES `order` (`OrderID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `orderdetail_chk_1` CHECK (`OrderQuantity` >= 0),
  CONSTRAINT `orderdetail_chk_2` CHECK (`UnitPrice` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 111 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orderdetail
-- ----------------------------
INSERT INTO `orderdetail` VALUES (1, '1', 'G001', '电脑显示器', '24 英寸高清显示器', 2, 500.00, 1000.00);
INSERT INTO `orderdetail` VALUES (2, '1', 'G003', '苹果手机', 'iPhone 13 128GB', 1, 800.00, 800.00);
INSERT INTO `orderdetail` VALUES (3, '2', 'G002', '运动鞋', '男款跑步鞋，尺码 42', 3, 80.00, 240.00);
INSERT INTO `orderdetail` VALUES (4, '2', 'G005', '衬衫', '白色纯棉衬衫，尺码 M', 2, 50.00, 100.00);
INSERT INTO `orderdetail` VALUES (5, '3', 'G004', '牛奶', '1L 装纯牛奶', 5, 5.00, 25.00);
INSERT INTO `orderdetail` VALUES (6, '3', 'G006', '打印机', '彩色喷墨打印机', 1, 300.00, 300.00);
INSERT INTO `orderdetail` VALUES (7, '4', 'G007', '面包', '全麦面包，500g', 4, 3.00, 12.00);
INSERT INTO `orderdetail` VALUES (8, '4', 'G008', '牛仔裤', '蓝色牛仔裤，尺码 32', 1, 60.00, 60.00);
INSERT INTO `orderdetail` VALUES (9, '5', 'G009', '平板电脑', 'iPad Air 10.5 英寸', 2, 600.00, 1200.00);
INSERT INTO `orderdetail` VALUES (10, '5', 'G010', '果汁', '橙汁，500ml', 6, 4.00, 24.00);
INSERT INTO `orderdetail` VALUES (101, '1', 'G001', '27寸高清电脑显示器', '分辨率：2560x1440，刷新率：144Hz', 3, 450.00, 1350.00);
INSERT INTO `orderdetail` VALUES (102, '1', 'G002', '专业跑鞋', '适合长跑，轻便透气，尺码：43', 2, 120.00, 240.00);
INSERT INTO `orderdetail` VALUES (103, '2', 'G003', '智能手机', '存储：256GB，摄像头：4800万像素', 1, 750.00, 750.00);
INSERT INTO `orderdetail` VALUES (104, '2', 'G004', '纯牛奶', '有机，2L装', 4, 8.00, 32.00);
INSERT INTO `orderdetail` VALUES (105, '3', 'G005', '男士衬衫', '纯棉材质，长袖，尺码：L', 5, 60.00, 300.00);
INSERT INTO `orderdetail` VALUES (106, '3', 'G006', '彩色激光打印机', '打印速度：20页/分钟', 1, 400.00, 400.00);
INSERT INTO `orderdetail` VALUES (107, '4', 'G007', '全麦面包', '无麸质，700g装', 6, 5.00, 30.00);
INSERT INTO `orderdetail` VALUES (108, '4', 'G008', '女士牛仔裤', '修身版型，蓝色，尺码：28', 3, 80.00, 240.00);
INSERT INTO `orderdetail` VALUES (109, '5', 'G009', '平板电脑', '10.1寸屏幕，64GB存储', 2, 550.00, 1100.00);
INSERT INTO `orderdetail` VALUES (110, '5', 'G010', '苹果汁', '鲜榨，1L装', 7, 6.00, 42.00);

-- ----------------------------
-- Table structure for outboundrecord
-- ----------------------------
DROP TABLE IF EXISTS `outboundrecord`;
CREATE TABLE `outboundrecord`  (
  `OutboundRecordID` int NOT NULL AUTO_INCREMENT,
  `GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `OutboundQuantity` int NOT NULL,
  `OutboundDate` date NOT NULL,
  `OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `EmployeeID` int NOT NULL,
  PRIMARY KEY (`OutboundRecordID`) USING BTREE,
  INDEX `idx_outbound_record_goods_id`(`GoodsID` ASC) USING BTREE,
  INDEX `idx_outbound_record_warehouse_id`(`WarehouseID` ASC) USING BTREE,
  INDEX `idx_outbound_record_order_id`(`OrderID` ASC) USING BTREE,
  INDEX `idx_outbound_record_employee_id`(`EmployeeID` ASC) USING BTREE,
  UNIQUE INDEX `idx_outboundrecord_recordid`(`OutboundRecordID` ASC) USING BTREE,
  INDEX `idx_outboundrecord_warehouseid`(`WarehouseID` ASC) USING BTREE,
  INDEX `idx_outboundrecord_orderid`(`OrderID` ASC) USING BTREE,
  INDEX `idx_outboundrecord_warehouse_date`(`WarehouseID` ASC, `OutboundDate` ASC) USING BTREE,
  CONSTRAINT `FK_OutboundRecord_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_OutboundRecord_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_OutboundRecord_Order` FOREIGN KEY (`OrderID`) REFERENCES `order` (`OrderID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_OutboundRecord_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `outboundrecord_chk_1` CHECK (`OutboundQuantity` > 0)
) ENGINE = InnoDB AUTO_INCREMENT = 141 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of outboundrecord
-- ----------------------------
INSERT INTO `outboundrecord` VALUES (1, 'G001', 'WH01', 20, '2024-01-01', '1', 1);
INSERT INTO `outboundrecord` VALUES (2, 'G002', 'WH02', 10, '2024-01-02', '2', 2);
INSERT INTO `outboundrecord` VALUES (3, 'G003', 'WH03', 8, '2024-01-03', '3', 3);
INSERT INTO `outboundrecord` VALUES (4, 'G004', 'WH04', 3, '2024-01-04', '4', 4);
INSERT INTO `outboundrecord` VALUES (5, 'G005', 'WH05', 12, '2024-01-05', '5', 5);
INSERT INTO `outboundrecord` VALUES (6, 'G006', 'WH06', 7, '2024-01-06', '6', 6);
INSERT INTO `outboundrecord` VALUES (7, 'G007', 'WH07', 9, '2024-01-07', '7', 7);
INSERT INTO `outboundrecord` VALUES (8, 'G008', 'WH08', 4, '2024-01-08', '8', 8);
INSERT INTO `outboundrecord` VALUES (9, 'G009', 'WH09', 6, '2024-01-09', '9', 9);
INSERT INTO `outboundrecord` VALUES (10, 'G010', 'WH10', 15, '2024-01-10', '10', 10);

-- ----------------------------
-- Table structure for supplier
-- ----------------------------
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier`  (
  `SupplierID` int NOT NULL AUTO_INCREMENT,
  `SupplyPrice` decimal(10, 2) NOT NULL,
  `SupplierName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`SupplierID`) USING BTREE,
  INDEX `idx_supplier_name`(`SupplierName` ASC) USING BTREE,
  CONSTRAINT `supplier_chk_1` CHECK (`SupplyPrice` >= 0)
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of supplier
-- ----------------------------
INSERT INTO `supplier` VALUES (1, 10.50, '供应商A');
INSERT INTO `supplier` VALUES (2, 12.30, '供应商B');
INSERT INTO `supplier` VALUES (3, 9.80, '供应商C');
INSERT INTO `supplier` VALUES (4, 15.20, '供应商D');
INSERT INTO `supplier` VALUES (5, 11.00, '供应商E');
INSERT INTO `supplier` VALUES (6, 13.50, '供应商F');
INSERT INTO `supplier` VALUES (7, 14.10, '供应商G');
INSERT INTO `supplier` VALUES (8, 8.90, '供应商H');
INSERT INTO `supplier` VALUES (9, 16.00, '供应商I');
INSERT INTO `supplier` VALUES (10, 12.80, '供应商J');

-- ----------------------------
-- Table structure for warehouse
-- ----------------------------
DROP TABLE IF EXISTS `warehouse`;
CREATE TABLE `warehouse`  (
  `WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseArea` int NOT NULL,
  PRIMARY KEY (`WarehouseID`) USING BTREE,
  INDEX `idx_warehouse_name`(`WarehouseName` ASC) USING BTREE,
  INDEX `idx_warehouse_address`(`WarehouseAddress` ASC) USING BTREE,
  UNIQUE INDEX `idx_warehouse_warehouseid`(`WarehouseID` ASC) USING BTREE,
  CONSTRAINT `warehouse_chk_1` CHECK (`WarehouseArea` > 0)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of warehouse
-- ----------------------------
INSERT INTO `warehouse` VALUES ('WH01', '北京仓库', '北京市朝阳区XX路XX号', 1000);
INSERT INTO `warehouse` VALUES ('WH02', '上海仓库', '上海市浦东新区XX路XX号', 1200);
INSERT INTO `warehouse` VALUES ('WH03', '广州仓库', '广州市天河区XX路XX号', 800);
INSERT INTO `warehouse` VALUES ('WH04', '深圳仓库', '深圳市南山区XX路XX号', 900);
INSERT INTO `warehouse` VALUES ('WH05', '成都仓库', '成都市高新区XX路XX号', 700);
INSERT INTO `warehouse` VALUES ('WH06', '杭州仓库', '杭州市西湖区XX路XX号', 600);
INSERT INTO `warehouse` VALUES ('WH07', '武汉仓库', '武汉市江汉区XX路XX号', 500);
INSERT INTO `warehouse` VALUES ('WH08', '南京仓库', '南京市鼓楼区XX路XX号', 400);
INSERT INTO `warehouse` VALUES ('WH09', '西安仓库', '西安市雁塔区XX路XX号', 300);
INSERT INTO `warehouse` VALUES ('WH10', '重庆仓库', '重庆市渝中区XX路XX号', 550);

-- ----------------------------
-- Table structure for warehousestoragearea
-- ----------------------------
DROP TABLE IF EXISTS `warehousestoragearea`;
CREATE TABLE `warehousestoragearea`  (
  `AreaID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `AreaType` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `AreaCapacity` int NOT NULL,
  PRIMARY KEY (`AreaID`) USING BTREE,
  INDEX `FK_Area_Warehouse`(`WarehouseID` ASC) USING BTREE,
  INDEX `idx_area_type`(`AreaType` ASC) USING BTREE,
  CONSTRAINT `FK_Area_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `warehousestoragearea_chk_1` CHECK (`AreaCapacity` >= 0)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of warehousestoragearea
-- ----------------------------
INSERT INTO `warehousestoragearea` VALUES ('A01', 'WH01', '货架区', 500);
INSERT INTO `warehousestoragearea` VALUES ('A02', 'WH01', '堆垛区', 300);
INSERT INTO `warehousestoragearea` VALUES ('A03', 'WH02', '货架区', 400);
INSERT INTO `warehousestoragearea` VALUES ('A04', 'WH02', '堆垛区', 250);
INSERT INTO `warehousestoragearea` VALUES ('A05', 'WH03', '货架区', 350);
INSERT INTO `warehousestoragearea` VALUES ('A06', 'WH03', '堆垛区', 200);
INSERT INTO `warehousestoragearea` VALUES ('A07', 'WH04', '货架区', 300);
INSERT INTO `warehousestoragearea` VALUES ('A08', 'WH04', '堆垛区', 180);
INSERT INTO `warehousestoragearea` VALUES ('A09', 'WH05', '货架区', 250);
INSERT INTO `warehousestoragearea` VALUES ('A10', 'WH05', '堆垛区', 150);
INSERT INTO `warehousestoragearea` VALUES ('A11', 'WH06', '货架区', 200);
INSERT INTO `warehousestoragearea` VALUES ('A12', 'WH06', '堆垛区', 120);
INSERT INTO `warehousestoragearea` VALUES ('A13', 'WH07', '货架区', 180);
INSERT INTO `warehousestoragearea` VALUES ('A14', 'WH07', '堆垛区', 100);
INSERT INTO `warehousestoragearea` VALUES ('A15', 'WH08', '货架区', 150);
INSERT INTO `warehousestoragearea` VALUES ('A16', 'WH08', '堆垛区', 80);
INSERT INTO `warehousestoragearea` VALUES ('A17', 'WH09', '货架区', 120);
INSERT INTO `warehousestoragearea` VALUES ('A18', 'WH09', '堆垛区', 60);
INSERT INTO `warehousestoragearea` VALUES ('A19', 'WH10', '货架区', 180);
INSERT INTO `warehousestoragearea` VALUES ('A20', 'WH10', '堆垛区', 100);

-- ----------------------------
-- View structure for employee_order_info
-- ----------------------------
DROP VIEW IF EXISTS `employee_order_info`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `employee_order_info` AS select `e`.`EmployeeName` AS `EmployeeName`,`e`.`EmployeePosition` AS `EmployeePosition`,`o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`OrderStatus` AS `OrderStatus` from (`employee` `e` join `order` `o` on((`e`.`EmployeeID` = `o`.`OrderID`)));

-- ----------------------------
-- View structure for goods_with_outbound
-- ----------------------------
DROP VIEW IF EXISTS `goods_with_outbound`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `goods_with_outbound` AS select `g`.`GoodsID` AS `GoodsID`,`g`.`GoodsName` AS `GoodsName`,`g`.`GoodsType` AS `GoodsType`,`g`.`ProductionDate` AS `ProductionDate`,`g`.`SafetyStock` AS `SafetyStock`,`g`.`PurchaseUnitPrice` AS `PurchaseUnitPrice`,`g`.`SupplierID` AS `SupplierID`,`orr`.`OutboundQuantity` AS `OutboundQuantity`,`orr`.`OutboundDate` AS `OutboundDate` from (`goods` `g` join `outboundrecord` `orr` on((`g`.`GoodsID` = `orr`.`GoodsID`)));

-- ----------------------------
-- View structure for high_value_orders
-- ----------------------------
DROP VIEW IF EXISTS `high_value_orders`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `high_value_orders` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,sum(`od`.`TotalPrice`) AS `OrderTotalPrice` from (`order` `o` join `orderdetail` `od` on((`o`.`OrderID` = `od`.`OrderID`))) group by `o`.`OrderID`,`o`.`OrderDate`,`o`.`CustomerID` having (sum(`od`.`TotalPrice`) > 1000);

-- ----------------------------
-- View structure for incomplete_orders
-- ----------------------------
DROP VIEW IF EXISTS `incomplete_orders`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `incomplete_orders` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,`o`.`OrderStatus` AS `OrderStatus`,`od`.`GoodsID` AS `GoodsID`,`od`.`GoodsName` AS `GoodsName` from (`order` `o` join `orderdetail` `od` on((`o`.`OrderID` = `od`.`OrderID`))) where (`o`.`OrderStatus` <> 'Completed');

-- ----------------------------
-- View structure for order_with_customer
-- ----------------------------
DROP VIEW IF EXISTS `order_with_customer`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `order_with_customer` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,`o`.`OrderStatus` AS `OrderStatus`,`o`.`customer_id` AS `customer_id`,`c`.`CustomerName` AS `CustomerName`,`c`.`CustomerAddress` AS `CustomerAddress` from (`order` `o` join `customer` `c` on((`o`.`CustomerID` = `c`.`CustomerID`)));

-- ----------------------------
-- View structure for warehouse_outbound_total
-- ----------------------------
DROP VIEW IF EXISTS `warehouse_outbound_total`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `warehouse_outbound_total` AS select `w`.`WarehouseID` AS `WarehouseID`,`w`.`WarehouseName` AS `WarehouseName`,sum(`orr`.`OutboundQuantity`) AS `TotalOutboundQuantity` from (`warehouse` `w` join `outboundrecord` `orr` on((`w`.`WarehouseID` = `orr`.`WarehouseID`))) group by `w`.`WarehouseID`,`w`.`WarehouseName`;

-- ----------------------------
-- Procedure structure for CalculateWarehouseOutboundTotal
-- ----------------------------
DROP PROCEDURE IF EXISTS `CalculateWarehouseOutboundTotal`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateWarehouseOutboundTotal`(
    IN p_WarehouseID VARCHAR(255),
    OUT p_TotalOutbound INT
)
BEGIN
    SELECT SUM(OutboundQuantity) INTO p_TotalOutbound
    FROM outboundrecord
    WHERE WarehouseID = p_WarehouseID;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for DeleteOrderAndOutbound
-- ----------------------------
DROP PROCEDURE IF EXISTS `DeleteOrderAndOutbound`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteOrderAndOutbound`(
    IN p_OrderID INT
)
BEGIN
    DELETE FROM outboundrecord WHERE OrderID = p_OrderID;
    DELETE FROM `order` WHERE OrderID = p_OrderID;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for GetOutboundRecordsByDateRange
-- ----------------------------
DROP PROCEDURE IF EXISTS `GetOutboundRecordsByDateRange`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOutboundRecordsByDateRange`(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    SELECT *
    FROM outboundrecord
    WHERE OutboundDate BETWEEN p_StartDate AND p_EndDate;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for InsertNewOrder
-- ----------------------------
DROP PROCEDURE IF EXISTS `InsertNewOrder`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertNewOrder`(
    IN p_OrderDate DATE,
    IN p_CustomerID INT,
    IN p_OrderStatus VARCHAR(50)
)
BEGIN
    INSERT INTO `order` (OrderDate, CustomerID, OrderStatus)
    VALUES (p_OrderDate, p_CustomerID, p_OrderStatus);
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for InsertOutboundRecordAndUpdateStock
-- ----------------------------
DROP PROCEDURE IF EXISTS `InsertOutboundRecordAndUpdateStock`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOutboundRecordAndUpdateStock`(
    IN p_GoodsID VARCHAR(255),
    IN p_WarehouseID VARCHAR(255),
    IN p_OutboundQuantity INT,
    IN p_OutboundDate DATE,
    IN p_OrderID INT,
    IN p_EmployeeID INT
)
BEGIN
    DECLARE current_stock INT;
    SELECT StockQuantity INTO current_stock FROM goods WHERE GoodsID = p_GoodsID;
    IF current_stock >= p_OutboundQuantity THEN
        INSERT INTO outboundrecord (GoodsID, WarehouseID, OutboundQuantity, OutboundDate, OrderID, EmployeeID)
        VALUES (p_GoodsID, p_WarehouseID, p_OutboundQuantity, p_OutboundDate, p_OrderID, p_EmployeeID);
        UPDATE goods
        SET StockQuantity = StockQuantity - p_OutboundQuantity
        WHERE GoodsID = p_GoodsID;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock for the outbound operation.';
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UpdateOrderStatus
-- ----------------------------
DROP PROCEDURE IF EXISTS `UpdateOrderStatus`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrderStatus`(
    IN p_OrderID INT,
    IN p_NewStatus VARCHAR(50)
)
BEGIN
    UPDATE `order`
    SET OrderStatus = p_NewStatus
    WHERE OrderID = p_OrderID;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table employee
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_before_update_employee`;
delimiter ;;
CREATE TRIGGER `tr_before_update_employee` BEFORE UPDATE ON `employee` FOR EACH ROW BEGIN
    INSERT INTO employee_log (EmployeeID, OldEmployeeName, NewEmployeeName, OldEmployeePosition, NewEmployeePosition)
    VALUES (OLD.EmployeeID, OLD.EmployeeName, NEW.EmployeeName, OLD.EmployeePosition, NEW.EmployeePosition);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table goods
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_before_delete_goods`;
delimiter ;;
CREATE TRIGGER `tr_before_delete_goods` BEFORE DELETE ON `goods` FOR EACH ROW BEGIN
    INSERT INTO deleted_goods (DeletedGoodsID, GoodsName)
    VALUES (OLD.GoodsID, OLD.GoodsName);
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_before_insert_order`;
delimiter ;;
CREATE TRIGGER `tr_before_insert_order` BEFORE INSERT ON `order` FOR EACH ROW BEGIN
    SET NEW.OrderDate = CURDATE();
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table order
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_after_update_order_status`;
delimiter ;;
CREATE TRIGGER `tr_after_update_order_status` AFTER UPDATE ON `order` FOR EACH ROW BEGIN
    IF OLD.OrderStatus!= NEW.OrderStatus THEN
        INSERT INTO order_log (OrderID, OldStatus, NewStatus)
        VALUES (OLD.OrderID, OLD.OrderStatus, NEW.OrderStatus);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table outboundrecord
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_after_insert_outboundrecord`;
delimiter ;;
CREATE TRIGGER `tr_after_insert_outboundrecord` AFTER INSERT ON `outboundrecord` FOR EACH ROW BEGIN
    UPDATE goods
    SET StockQuantity = StockQuantity - NEW.OutboundQuantity
    WHERE GoodsID = NEW.GoodsID;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table outboundrecord
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_before_delete_outboundrecord`;
delimiter ;;
CREATE TRIGGER `tr_before_delete_outboundrecord` BEFORE DELETE ON `outboundrecord` FOR EACH ROW BEGIN
    UPDATE goods
    SET StockQuantity = StockQuantity + OLD.OutboundQuantity
    WHERE GoodsID = OLD.GoodsID;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;