134 lines
8.7 KiB
SQL
134 lines
8.7 KiB
SQL
-- wxshop.mqtt_server definition
|
||
|
||
CREATE TABLE `mqtt_server` (
|
||
`mqtt_server_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`server_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'MQTT服务器地址',
|
||
`username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '连接账号',
|
||
`password` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '连接密码',
|
||
`topic_filter` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订阅主题过滤器',
|
||
`publish_topic` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '发布主题',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`mqtt_server_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MQTT服务配置表';
|
||
|
||
|
||
-- wxshop.shop definition
|
||
|
||
CREATE TABLE `shop` (
|
||
`shop_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`shop_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商店名称',
|
||
`corpid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '企业微信id',
|
||
`belong_type` tinyint NOT NULL DEFAULT '0' COMMENT '归属类型（0-借还柜 1-固资通）',
|
||
`mode` tinyint NOT NULL DEFAULT '0' COMMENT '运行模式（0-支付模式 1-审批模式 2-借还模式 3-会员模式 4-耗材模式）',
|
||
`balance_enable` tinyint NOT NULL DEFAULT '1' COMMENT '借呗支付（1-正常使用 0-禁止使用）',
|
||
`cover_img` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '封面图URL',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`shop_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商店表，每个柜子属于一个商店';
|
||
|
||
|
||
-- wxshop.smart_cabinet definition
|
||
|
||
CREATE TABLE `smart_cabinet` (
|
||
`cabinet_id` bigint NOT NULL AUTO_INCREMENT COMMENT '柜机唯一ID',
|
||
`cabinet_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '柜机名称',
|
||
`cabinet_type` tinyint NOT NULL DEFAULT '0' COMMENT '柜机类型（0主柜 1副柜）',
|
||
`main_cabinet` bigint DEFAULT NULL COMMENT '归属主柜ID',
|
||
`balance_enable` tinyint NOT NULL DEFAULT '1' COMMENT '借呗支付（1-正常使用 0-禁止使用）',
|
||
`mode` tinyint NOT NULL DEFAULT '0' COMMENT '运行模式（0-支付模式 1-审批模式 2-借还模式 3-会员模式）',
|
||
`belong_type` tinyint NOT NULL DEFAULT '0' COMMENT '归属类型（0-借还柜 1-固资通）',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '归属商店ID',
|
||
`mqtt_server_id` bigint DEFAULT NULL COMMENT 'MQTT服务ID',
|
||
`template_no` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '柜机模版编号',
|
||
`lock_control_no` int NOT NULL COMMENT '锁控板序号',
|
||
`location` int NOT NULL COMMENT '柜机位置',
|
||
`creator_id` bigint NOT NULL DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint NOT NULL DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
`return_deadline` int NOT NULL DEFAULT '0' COMMENT '归还期限（天），0表示不限制',
|
||
`corpid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '企业微信id',
|
||
PRIMARY KEY (`cabinet_id`),
|
||
KEY `idx_template_no` (`template_no`),
|
||
KEY `idx_location` (`location`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='智能柜信息表';
|
||
|
||
|
||
-- wxshop.cabinet_mainboard definition
|
||
|
||
CREATE TABLE `cabinet_mainboard` (
|
||
`mainboard_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主板唯一ID',
|
||
`cabinet_id` bigint NOT NULL COMMENT '关联柜机ID',
|
||
`lock_control_no` int NOT NULL COMMENT '锁控板序号',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`mainboard_id`),
|
||
KEY `idx_cabinet` (`cabinet_id`),
|
||
CONSTRAINT `fk_mainboard_cabinet` FOREIGN KEY (`cabinet_id`) REFERENCES `smart_cabinet` (`cabinet_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='机柜主板信息表';
|
||
|
||
|
||
-- wxshop.cabinet_cell definition
|
||
|
||
CREATE TABLE `cabinet_cell` (
|
||
`cell_id` bigint NOT NULL AUTO_INCREMENT COMMENT '格口唯一ID',
|
||
`cabinet_id` bigint NOT NULL COMMENT '关联柜机ID',
|
||
`mainboard_id` bigint DEFAULT NULL COMMENT '归属主板ID',
|
||
`goods_id` bigint DEFAULT NULL COMMENT '关联商品ID',
|
||
`cell_no` int NOT NULL COMMENT '格口号',
|
||
`pin_no` int NOT NULL COMMENT '针脚序号',
|
||
`stock` int NOT NULL DEFAULT '0' COMMENT '库存数量',
|
||
`cell_price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '格口租用价格',
|
||
`password` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '格口密码',
|
||
`password_create_time` datetime DEFAULT NULL COMMENT '格口密码创建时间',
|
||
`is_rented` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已租用：0-未租用，1-已租用',
|
||
`cell_type` tinyint NOT NULL DEFAULT '1' COMMENT '格口类型（1小格 2中格 3大格 4超大格）',
|
||
`usage_status` tinyint NOT NULL DEFAULT '1' COMMENT '使用状态（1空闲 2已占用）',
|
||
`available_status` tinyint NOT NULL DEFAULT '1' COMMENT '可用状态（1正常 2故障）',
|
||
`creator_id` bigint NOT NULL DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint NOT NULL DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`cell_id`),
|
||
KEY `idx_cabinet` (`cabinet_id`),
|
||
KEY `idx_usage_status` (`usage_status`),
|
||
KEY `idx_available_status` (`available_status`),
|
||
KEY `idx_goods` (`goods_id`),
|
||
CONSTRAINT `fk_cell_cabinet` FOREIGN KEY (`cabinet_id`) REFERENCES `smart_cabinet` (`cabinet_id`),
|
||
CONSTRAINT `fk_cell_goods` FOREIGN KEY (`goods_id`) REFERENCES `shop_goods` (`goods_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='柜机格口信息表';
|
||
|
||
CREATE TABLE `cabinet_cell_operation` (
|
||
`operation_id` bigint NOT NULL AUTO_INCREMENT COMMENT '操作流水号',
|
||
`cell_id` bigint NOT NULL COMMENT '关联格口ID',
|
||
`goods_id` bigint DEFAULT NULL COMMENT '关联商品ID',
|
||
`goods_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '商品名称',
|
||
`userid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '企业微信用户ID',
|
||
`is_internal` tinyint(1) DEFAULT '0' COMMENT '是否内部用户（0否 1是）',
|
||
`name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '成员名称',
|
||
`mobile` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号码',
|
||
`operation_type` tinyint NOT NULL COMMENT '操作类型（1用户 2管理员）',
|
||
`status` tinyint NOT NULL DEFAULT '1' COMMENT '操作状态（1正常 2操作失败）',
|
||
`creator_id` bigint NOT NULL DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint NOT NULL DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`operation_id`),
|
||
KEY `idx_cell` (`cell_id`),
|
||
KEY `idx_user` (`userid`),
|
||
KEY `idx_operation_time` (`create_time`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=5717 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='柜机格口操作记录表'; |