process_sheet/fastadmin/application/sql/ps_schema.sql

-- 生产流程单云平台 · 工厂租户与核心业务表
-- 表前缀 fa_ ，字符集 utf8mb4

-- ----------------------------
-- 工厂
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_factory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '工厂名称',
  `code` varchar(32) NOT NULL DEFAULT '' COMMENT '工厂编码',
  `invite_code` varchar(16) NOT NULL DEFAULT '' COMMENT '邀请码',
  `logo` varchar(255) NOT NULL DEFAULT '' COMMENT 'Logo',
  `contact` varchar(50) NOT NULL DEFAULT '' COMMENT '联系人',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '电话',
  `address` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',
  `score` int(10) NOT NULL DEFAULT '0' COMMENT '工厂积分',
  `owner_user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '负责人会员ID',
  `status` enum('normal','hidden','locked') NOT NULL DEFAULT 'normal' COMMENT '状态',
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  `updatetime` bigint(16) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `invite_code` (`invite_code`),
  KEY `owner_user_id` (`owner_user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-工厂';

-- ----------------------------
-- 会员入厂
-- ----------------------------
ALTER TABLE `fa_user`
  ADD COLUMN `factory_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所属工厂' AFTER `group_id`,
  ADD COLUMN `factory_role` varchar(20) NOT NULL DEFAULT '' COMMENT 'owner=管理员,operator=操作员' AFTER `factory_id`,
  ADD COLUMN `factory_perms` text NULL COMMENT '工厂端权限JSON，空=操作员默认全开' AFTER `factory_role`;

ALTER TABLE `fa_user` ADD INDEX `factory_id` (`factory_id`);

-- ----------------------------
-- 工厂积分流水
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_factory_score_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '工厂ID',
  `operator_user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作人',
  `score` int(10) NOT NULL DEFAULT '0' COMMENT '变更积分',
  `before` int(10) NOT NULL DEFAULT '0' COMMENT '变更前',
  `after` int(10) NOT NULL DEFAULT '0' COMMENT '变更后',
  `biz_type` varchar(32) NOT NULL DEFAULT '' COMMENT '业务类型',
  `biz_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '业务ID',
  `memo` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-工厂积分流水';

-- ----------------------------
-- 可绑定字段字典
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_field_dict` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group` varchar(32) NOT NULL DEFAULT '' COMMENT 'product/workorder/process/user/print',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '绑定键，如 product.name',
  `title` varchar(64) NOT NULL DEFAULT '' COMMENT '显示名',
  `weigh` int(10) NOT NULL DEFAULT '0' COMMENT '权重',
  `status` enum('normal','hidden') NOT NULL DEFAULT 'normal',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-字段字典';

-- ----------------------------
-- 模板
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_template` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0=官方模板',
  `creator_user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建人',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '模板名称',
  `cover` varchar(255) NOT NULL DEFAULT '' COMMENT '封面',
  `paper_size` varchar(20) NOT NULL DEFAULT 'A4' COMMENT 'A4/A5/custom',
  `page_w` decimal(8,2) NOT NULL DEFAULT '210.00' COMMENT '纸宽毫米',
  `page_h` decimal(8,2) NOT NULL DEFAULT '297.00' COMMENT '纸高毫米',
  `mark_cover` varchar(255) NOT NULL DEFAULT '' COMMENT '色块标注图',
  `source` varchar(20) NOT NULL DEFAULT 'manual' COMMENT 'manual/ai/system/market',
  `license_type` varchar(20) NOT NULL DEFAULT 'self' COMMENT 'self/system_copy/market_single/market_buyout',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '单次授权确认套打后锁定',
  `locked_time` bigint(16) DEFAULT NULL COMMENT '锁定时间',
  `source_template_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '复制/购买来源',
  `source_version_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '来源版本',
  `current_version_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当前版本',
  `is_system` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '官方模板',
  `status` enum('normal','hidden') NOT NULL DEFAULT 'normal',
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `factory_id` (`factory_id`),
  KEY `license_locked` (`factory_id`,`license_type`,`locked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-模板';

CREATE TABLE IF NOT EXISTS `fa_ps_template_version` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int(10) unsigned NOT NULL DEFAULT '0',
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version` int(10) unsigned NOT NULL DEFAULT '1',
  `schema_json` longtext COMMENT '排版JSON',
  `remark` varchar(255) NOT NULL DEFAULT '',
  `createtime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-模板版本';

-- ----------------------------
-- AI 识版任务
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_ai_job` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `file_url` varchar(255) NOT NULL DEFAULT '',
  `file_type` varchar(20) NOT NULL DEFAULT '' COMMENT 'image/pdf',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/running/success/fail',
  `model` varchar(64) NOT NULL DEFAULT '' COMMENT '实际使用的模型',
  `raw_text` longtext COMMENT '模型原文',
  `schema_json` longtext COMMENT '解析后的草稿',
  `error_msg` varchar(500) NOT NULL DEFAULT '',
  `template_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '确认入库后的模板',
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `factory_id` (`factory_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-AI识版任务';

-- ----------------------------
-- 产品 / 工序
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `creator_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `code` varchar(64) NOT NULL DEFAULT '' COMMENT '产品编码',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '产品名称',
  `spec` varchar(100) NOT NULL DEFAULT '' COMMENT '规格',
  `unit` varchar(20) NOT NULL DEFAULT '' COMMENT '单位',
  `qty` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '总数量',
  `sheet_qty` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '每版数量，一版一张流程单',
  `remark` varchar(255) NOT NULL DEFAULT '',
  `status` enum('normal','hidden') NOT NULL DEFAULT 'normal',
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `factory_code` (`factory_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-产品';

CREATE TABLE IF NOT EXISTS `fa_ps_product_process` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '序号',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '工序名',
  `workstation` varchar(64) NOT NULL DEFAULT '' COMMENT '工位',
  `standard_time` varchar(32) NOT NULL DEFAULT '' COMMENT '标准工时',
  `params` text COMMENT '工艺参数',
  `qc_points` text COMMENT '质检要点',
  `remark` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-产品工序';

-- ----------------------------
-- 工单 / 工序
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_workorder` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `creator_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `no` varchar(64) NOT NULL DEFAULT '' COMMENT '工单号',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0',
  `product_name` varchar(100) NOT NULL DEFAULT '' COMMENT '产品名称，可不关联产品档案',
  `qty` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '总数量',
  `sheet_qty` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '每版数量，一版一张流程单',
  `batch_no` varchar(64) NOT NULL DEFAULT '' COMMENT '批次',
  `plan_date` date DEFAULT NULL COMMENT '计划日期',
  `customer` varchar(100) NOT NULL DEFAULT '' COMMENT '客户',
  `remark` varchar(255) NOT NULL DEFAULT '',
  `status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT 'draft/doing/done/cancel',
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `factory_no` (`factory_id`,`no`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-工单';

CREATE TABLE IF NOT EXISTS `fa_ps_workorder_process` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `workorder_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL DEFAULT '',
  `workstation` varchar(64) NOT NULL DEFAULT '',
  `standard_time` varchar(32) NOT NULL DEFAULT '',
  `params` text,
  `qc_points` text,
  `remark` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `workorder_id` (`workorder_id`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-工单工序';

-- ----------------------------
-- 打印任务 / 快照
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_print_job` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `operator_user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作员',
  `print_no` varchar(40) NOT NULL DEFAULT '' COMMENT '打印流水号',
  `biz_type` varchar(20) NOT NULL DEFAULT 'workorder' COMMENT 'product/workorder',
  `biz_id` int(10) unsigned NOT NULL DEFAULT '0',
  `template_id` int(10) unsigned NOT NULL DEFAULT '0',
  `template_version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `copies` int(10) unsigned NOT NULL DEFAULT '1',
  `lodop_mode` varchar(20) NOT NULL DEFAULT 'preview' COMMENT 'preview/print',
  `status` varchar(20) NOT NULL DEFAULT 'previewed' COMMENT 'previewed/printed/canceled',
  `createtime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `print_no` (`print_no`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-打印任务';

CREATE TABLE IF NOT EXISTS `fa_ps_print_snapshot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `print_job_id` int(10) unsigned NOT NULL DEFAULT '0',
  `fill_json` longtext COMMENT '填充后的数据',
  `schema_json` longtext COMMENT '当时模板schema',
  `lodop_html` longtext COMMENT '下发给Lodop的HTML',
  PRIMARY KEY (`id`),
  KEY `print_job_id` (`print_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-打印快照';

-- ----------------------------
-- 商城
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_market_goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '卖家工厂',
  `seller_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `template_id` int(10) unsigned NOT NULL DEFAULT '0',
  `template_version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL DEFAULT '',
  `cover` varchar(255) NOT NULL DEFAULT '',
  `category` varchar(50) NOT NULL DEFAULT '',
  `intro` text,
  `price_single` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '单次积分价',
  `price_buyout` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '买断积分价',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/on_sale/rejected/off',
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-模板商品';

CREATE TABLE IF NOT EXISTS `fa_ps_market_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_no` varchar(40) NOT NULL DEFAULT '',
  `goods_id` int(10) unsigned NOT NULL DEFAULT '0',
  `license_type` varchar(20) NOT NULL DEFAULT 'single' COMMENT 'single/buyout',
  `score` int(10) unsigned NOT NULL DEFAULT '0',
  `buyer_factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `buyer_user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seller_factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `copy_template_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '买方副本模板',
  `status` varchar(20) NOT NULL DEFAULT 'paid',
  `createtime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_no` (`order_no`),
  KEY `buyer_factory_id` (`buyer_factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-模板订单';

CREATE TABLE IF NOT EXISTS `fa_ps_market_license` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '买方工厂',
  `order_id` int(10) unsigned NOT NULL DEFAULT '0',
  `template_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '买方副本',
  `license_type` varchar(20) NOT NULL DEFAULT 'single',
  `remain_times` int(10) NOT NULL DEFAULT '1' COMMENT '单次=1，买断=-1',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `used_time` bigint(16) DEFAULT NULL,
  `createtime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `factory_id` (`factory_id`),
  KEY `template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-模板授权';

-- ----------------------------
-- 支付预留（不实现渠道）
-- ----------------------------
CREATE TABLE IF NOT EXISTS `fa_ps_pay_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `pay_no` varchar(40) NOT NULL DEFAULT '',
  `channel` varchar(20) NOT NULL DEFAULT '' COMMENT 'wechat/alipay，预留',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '拟充值积分',
  `status` varchar(20) NOT NULL DEFAULT 'created' COMMENT 'created/paid/closed',
  `notify_payload` text,
  `createtime` bigint(16) DEFAULT NULL,
  `updatetime` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pay_no` (`pay_no`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流程单-支付单预留';