-- ----------------------------
-- Table structure for pill_purchase
-- ----------------------------
DROP TABLE IF EXISTS `pill_purchase`;
CREATE TABLE `pill_purchase` (
  `purchase_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '进货单ID',
  `drug_id` bigint(20) NOT NULL COMMENT '药品ID（关联pill_drug.drug_id）',
  `factory_id` bigint(20) NOT NULL COMMENT '生产厂家ID（关联pill_factory.factory_id）',
  `purchase_time` datetime NOT NULL COMMENT '进货时间',
  `purchase_price` decimal(10,2) NOT NULL COMMENT '进货单价（元）',
  `purchase_num` int(10) NOT NULL COMMENT '进货数量',
  `remark` varchar(500) DEFAULT NULL COMMENT '进货备注',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`purchase_id`),
  KEY `idx_drug_id` (`drug_id`),
  KEY `idx_factory_id` (`factory_id`),
  CONSTRAINT `fk_purchase_drug` FOREIGN KEY (`drug_id`) REFERENCES `pill_drug` (`drug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_purchase_factory` FOREIGN KEY (`factory_id`) REFERENCES `pill_factory` (`factory_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='药品进货记录表';

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : storage1

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2022-09-25 17:33:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for pill_drug
-- ----------------------------
DROP TABLE IF EXISTS `pill_drug`;
CREATE TABLE `pill_drug` (
  `drug_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '药品ID',
  `drug_name` varchar(50) DEFAULT NULL COMMENT '药品名称',
  `drug_code` varchar(50) DEFAULT NULL COMMENT '药品编码',
  `factory_id` bigint(20) DEFAULT NULL COMMENT '生产厂家',
  `drug_type` char(1) DEFAULT NULL COMMENT '药品类型',
  `prescription_type` char(1) DEFAULT NULL COMMENT '处方类型',
  `unit` varchar(10) DEFAULT NULL COMMENT '单位',
  `price` int(10) DEFAULT NULL COMMENT '价格',
  `num` int(10) DEFAULT NULL COMMENT '库存量',
  `warn_value` int(10) DEFAULT NULL COMMENT '预警值',
  `conversion` int(10) DEFAULT NULL COMMENT '换算量',
  `status` char(1) DEFAULT NULL COMMENT '状态',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`drug_id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8 COMMENT='药品信息';

-- ----------------------------
-- Records of pill_drug
-- ----------------------------
INSERT INTO `pill_drug` VALUES ('101', '十全大补丸', 'dbw0001', '101', '0', '0', '瓶', '200', '100', '50', '1', '0', 'admin', '2022-08-27 16:03:29', null, null, null);
INSERT INTO `pill_drug` VALUES ('102', '阿莫西林', 'zcy0002', '102', '1', '1', '盒', '15', '300', '50', '1', '0', 'admin', '2022-09-15 16:03:29', null, null, null);

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : storage

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2022-09-25 15:48:01
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for pill_factory
-- ----------------------------
DROP TABLE IF EXISTS `pill_factory`;
CREATE TABLE `pill_factory` (
  `factory_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '厂家ID',
  `factory_name` varchar(50) DEFAULT NULL COMMENT '厂家名称',
  `factory_code` varchar(50) DEFAULT NULL COMMENT '厂家编码',
  `contact` varchar(30) DEFAULT NULL COMMENT '联系人',
  `phone` varchar(30) DEFAULT NULL COMMENT '电话',
  `keyword` varchar(20) DEFAULT NULL COMMENT '关键字',
  `status` char(1) DEFAULT NULL COMMENT '状态',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`factory_id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8 COMMENT='生产厂家信息';

-- ----------------------------
-- Records of pill_factory
-- ----------------------------
INSERT INTO `pill_factory` VALUES ('101', '云南白药集团股份有限公司', '000011', 'admin', '023-88887777', 'ynby', '0', 'admin', '2022-09-26 16:15:29', null, null, null);
INSERT INTO `pill_factory` VALUES ('102', '哈药集团股份有限公司', '000022', 'admin', '023-88887777', 'hyjt', '0', 'admin', '2022-09-26 16:15:29', 'admin', '2022-09-26 20:34:01', null);
INSERT INTO `pill_factory` VALUES ('103', '太极集团有限公司', '000033', 'admin', '023-88887777', 'tjjt', '0', 'admin', '2022-09-26 16:15:29', null, null, null);
INSERT INTO `pill_factory` VALUES ('104', '上海医药集团有限公司', '000033', 'admin', '023-99998888', 'shyy', '0', 'admin', '2022-09-26 20:12:57', null, null, null);

帮我建个数据库表（有效期预警）
要显示 药品名称 生产厂家 药品生产日期 保质期 进货时间（pill_purchase） 药品距离多少天过期 库存
将purchase_time由pill_purchase表联合查询，让该表时需同步。根据上面的的表