1122355665

CREATE TABLE products (-- 产品表  
    product_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',  
    product_name VARCHAR(255) NOT NULL COMMENT '商品名称',  
    product_category VARCHAR(100) NOT NULL COMMENT '商品类别',  
    specification VARCHAR(255) COMMENT '规格',  
    unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',  
    stock_quantity INT NOT NULL DEFAULT 0 COMMENT '库存数量'  
);
CREATE TABLE warehouses (-- 仓库表  
    warehouse_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '库房ID',  
    warehouse_name VARCHAR(255) NOT NULL COMMENT '库房名称',  
    location VARCHAR(255) NOT NULL COMMENT '位置',  
    area DECIMAL(10, 2) NOT NULL COMMENT '面积'  
);
CREATE TABLE employees  (-- 职工表
  employee_id int(11) NOT NULL AUTO_INCREMENT COMMENT '员工序号',
  employee_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  department varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属部门',
  inventory_responsibility varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工职责',
  inventory_count int(11) NULL DEFAULT NULL COMMENT '记录员工的库存数量',
  PRIMARY KEY (`employee_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE inventory_transactions (-- 库存表  
    transaction_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '出入库记录ID',  
    product_id INT,  
    warehouse_id INT,  
    employee_id INT,  
    transaction_type ENUM('In', 'Out') NOT NULL COMMENT '出入库类型',  
    quantity INT NOT NULL COMMENT '数量',  
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',  
    FOREIGN KEY (product_id) REFERENCES products(product_id),  
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),  
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)  
);
CREATE TABLE suppliers (-- 供应商表   
    supplier_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '供应商ID',  
    supplier_name VARCHAR(255) NOT NULL COMMENT '供应商名称',  
    contact_person VARCHAR(100) COMMENT '联系人',  
    phone_number VARCHAR(20) COMMENT '联系电话',  
    email VARCHAR(100) COMMENT '电子邮箱',  
    address VARCHAR(255) COMMENT '地址'  
);
CREATE TABLE orders (-- 订单表   
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',  
    customer_name VARCHAR(255) NOT NULL COMMENT '客户名称',  
    order_date DATE NOT NULL COMMENT '订单日期',  
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',  
    status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL COMMENT '订单状态'  
);
CREATE TABLE order_details (-- 订单明细表  
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单明细ID',  
    order_id INT NOT NULL,  
    product_id INT NOT NULL,  
    quantity INT NOT NULL COMMENT '数量',  
    unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',  
    FOREIGN KEY (order_id) REFERENCES orders(order_id),  
    FOREIGN KEY (product_id) REFERENCES products(product_id)  
);
CREATE TABLE purchase_orders (-- 采购单表  
    po_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '采购单ID',  
    supplier_id INT NOT NULL,  
    purchase_date DATE NOT NULL COMMENT '采购日期',  
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '采购总金额',  
    status ENUM('Pending', 'Received', 'Cancelled') NOT NULL COMMENT '采购单状态',  
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)  
);
CREATE TABLE inventory (-- 库存表  
    inventory_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '库存记录ID',  
    product_id INT NOT NULL COMMENT '商品ID',  
    warehouse_id INT NOT NULL COMMENT '库房ID',  
    quantity INT NOT NULL DEFAULT 0 COMMENT '库存数量',  
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',  
    FOREIGN KEY (product_id) REFERENCES products(product_id),  
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)  
);

CREATE TABLE users (-- 用户表  
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',  
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',  
    password VARCHAR(255) NOT NULL COMMENT '密码（加密后）',  
    email VARCHAR(100) UNIQUE COMMENT '电子邮箱',  
    phone_number VARCHAR(20) COMMENT '联系电话',  
    role ENUM('Admin', 'User') NOT NULL COMMENT '角色'  
);


CREATE TABLE return_orders (-- 退货单表  
    return_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '退货单ID',  
    order_id INT NOT NULL COMMENT '原始订单ID',  
    customer_name VARCHAR(255) NOT NULL COMMENT '客户名称',  
    return_date DATE NOT NULL COMMENT '退货日期',  
    status ENUM('Pending', 'Refunded', 'Rejected') NOT NULL COMMENT '退货状态',  
    FOREIGN KEY (order_id) REFERENCES orders(order_id)  
);
CREATE TABLE return_details (-- 退货明细表  
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '退货明细ID',  
    return_id INT NOT NULL COMMENT '退货单ID',  
    product_id INT NOT NULL COMMENT '商品ID',  
    quantity INT NOT NULL COMMENT '退货数量',  
    FOREIGN KEY (return_id) REFERENCES return_orders(return_id),  
    FOREIGN KEY (product_id) REFERENCES products(product_id)  
);