AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/WIP-S5-IQC-INVPOSTING.sql

-- ============================================================================
-- WIP 占位（不占版本号、不注册 Copy、不自动执行）——提交那刻 rebase 后 rename 为 1.0.<next>.sql 并注册 Copy。
-- 用途：DOP 路线 B「IQC→WMS 入库」L3 影子库存账建表兜底（团队 EnableInitTable=false 首启时用）。
-- 主路径仍是 CodeFirst（实体在 MaterialWarehouse/InventoryPosting/Entity/）。本脚本与实体逐列对齐。
-- 红线：全部为 ado_* 新表；不 CREATE/ALTER/DROP 任何旧表（InvMaster/LocationDetail/InvTransHist/MissedPrint）。
-- 关键：lot_serial / refs 为 NOT NULL DEFAULT ''（空批次空串占位唯一，非 NULL）。
-- ============================================================================

CREATE TABLE IF NOT EXISTS `ado_inventory_master` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `location` VARCHAR(30) NOT NULL DEFAULT '',
  `qty_on_hand` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `avail_status_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `assay_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `freeze_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `last_rct_time` DATETIME NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_inv_master` (`tenant_id`,`domain_code`,`item_num`,`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3库存汇总账(影子,非旧InvMaster)';

CREATE TABLE IF NOT EXISTS `ado_inventory_location_detail` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `location` VARCHAR(30) NOT NULL DEFAULT '',
  `lot_serial` VARCHAR(50) NOT NULL DEFAULT '',
  `refs` VARCHAR(50) NOT NULL DEFAULT '',
  `qty_on_hand` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `avail_status_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `assay_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `freeze_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_inv_loc_detail` (`tenant_id`,`domain_code`,`item_num`,`location`,`lot_serial`,`refs`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3库位批次库存明细(影子,非旧LocationDetail)';

CREATE TABLE IF NOT EXISTS `ado_inventory_transaction` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `location` VARCHAR(30) NOT NULL DEFAULT '',
  `lot_serial` VARCHAR(50) NOT NULL DEFAULT '',
  `trans_type` VARCHAR(50) NOT NULL DEFAULT '',
  `qty_change` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `begin_balance` DECIMAL(18,5) NULL,
  `work_ord` VARCHAR(60) NULL,
  `shipper_num` VARCHAR(60) NULL,
  `remark` VARCHAR(500) NULL,
  `fbillno` VARCHAR(50) NULL,
  `receiver` VARCHAR(50) NULL,
  `rct_qc_nbr` VARCHAR(100) NULL,
  `posting_id` BIGINT NULL,
  `transaction_group_id` BIGINT NOT NULL DEFAULT 0,
  `seq` INT NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_inv_trans_group_seq` (`tenant_id`,`transaction_group_id`,`seq`),
  KEY `idx_ado_inv_trans_posting` (`tenant_id`,`posting_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3库存事务流水(影子,append-only,非旧InvTransHist)';

CREATE TABLE IF NOT EXISTS `ado_barcode_card` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `location` VARCHAR(30) NOT NULL DEFAULT '',
  `lot_serial` VARCHAR(50) NOT NULL DEFAULT '',
  `shelf` VARCHAR(50) NULL,
  `level_char` VARCHAR(20) NULL,
  `supply` VARCHAR(50) NULL,
  `status` VARCHAR(10) NULL,
  `qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `is_tag` TINYINT(1) NOT NULL DEFAULT 0,
  `barcode` VARCHAR(100) NULL,
  `type` VARCHAR(20) NULL,
  `trans_type` VARCHAR(50) NULL,
  `posting_id` BIGINT NULL,
  `source_domain` VARCHAR(20) NULL,
  `source_rec_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_barcode_card_source` (`tenant_id`,`source_domain`,`source_rec_id`),
  KEY `idx_ado_barcode_card_item_loc` (`tenant_id`,`domain_code`,`item_num`,`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3条码卡(影子,非旧MissedPrint;barcode非唯一)';

CREATE TABLE IF NOT EXISTS `ado_barcode_card_transaction` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `barcode_card_id` BIGINT NULL,
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `location` VARCHAR(30) NOT NULL DEFAULT '',
  `lot_serial` VARCHAR(50) NOT NULL DEFAULT '',
  `qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `status` VARCHAR(10) NULL,
  `trans_type` VARCHAR(50) NOT NULL DEFAULT '',
  `remark` VARCHAR(500) NULL,
  `posting_id` BIGINT NULL,
  `source_domain` VARCHAR(20) NULL,
  `source_rec_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_bctrans_source` (`tenant_id`,`source_domain`,`source_rec_id`),
  KEY `idx_ado_bctrans_card` (`tenant_id`,`barcode_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3条码卡历史流水(影子,非旧MissedPrintTransHist)';

CREATE TABLE IF NOT EXISTS `ado_iqc_inventory_posting` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `fbillno` VARCHAR(50) NOT NULL DEFAULT '',
  `receiver` VARCHAR(50) NULL,
  `rct_qc_nbr` VARCHAR(100) NULL,
  `item_num` VARCHAR(50) NULL,
  `posting_status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `posting_mode` VARCHAR(20) NOT NULL DEFAULT 'SHADOW',
  `mrb_status` INT NOT NULL DEFAULT 0,
  `branch_no` INT NOT NULL DEFAULT 0,
  `transaction_group_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_iqc_posting_fbillno` (`tenant_id`,`domain_code`,`fbillno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3 IQC入库过账头/幂等锚(tenant+domain+FBILLNO唯一)';

CREATE TABLE IF NOT EXISTS `ado_item_location_state` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `item_num` VARCHAR(50) NOT NULL DEFAULT '',
  `default_location` VARCHAR(30) NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_item_loc_state` (`tenant_id`,`domain_code`,`item_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3物料默认库位状态(ItemMaster.Location语义,影子)';

CREATE TABLE IF NOT EXISTS `ado_purchase_receipt_writeback` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `posting_id` BIGINT NOT NULL DEFAULT 0,
  `rct_nbr` VARCHAR(50) NULL,
  `pur_ord` VARCHAR(60) NOT NULL DEFAULT '',
  `pur_line` INT NOT NULL DEFAULT 0,
  `potype` VARCHAR(30) NULL,
  `accepted_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `sample_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `qty_ordered` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `returned_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `before_received_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `after_received_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `before_outstanding_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `after_outstanding_qty` DECIMAL(18,5) NOT NULL DEFAULT 0,
  `detail_completed` TINYINT(1) NOT NULL DEFAULT 0,
  `qc_nbr` VARCHAR(100) NULL,
  `receiver` VARCHAR(50) NULL,
  `fbillno` VARCHAR(50) NULL,
  `qc_status` VARCHAR(10) NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_pr_writeback` (`tenant_id`,`domain_code`,`posting_id`,`pur_ord`,`pur_line`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3采购收货回写(C6 shadow,非旧PurOrd*)';

CREATE TABLE IF NOT EXISTS `ado_inventory_mobile_task` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `posting_id` BIGINT NULL,
  `fbillno` VARCHAR(50) NULL,
  `task_id` VARCHAR(100) NOT NULL DEFAULT '',
  `type_id` VARCHAR(20) NULL,
  `name` VARCHAR(50) NULL,
  `description` VARCHAR(1000) NULL,
  `executer` VARCHAR(1000) NULL,
  `executer_type_id` INT NOT NULL DEFAULT 0,
  `plan_date` DATETIME NULL,
  `status` VARCHAR(20) NULL,
  `receiver` VARCHAR(50) NULL,
  `rct_qc_nbr` VARCHAR(100) NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_inv_mobile_task` (`tenant_id`,`domain_code`,`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3库存移动任务(C7 shadow,非旧MobileTask)';

-- Phase 7C：C2 业务事务完成锚（与 C6/C5/C7 同事务提交，闭合 commit→MarkPosted 崩溃窗口）
CREATE TABLE IF NOT EXISTS `ado_iqc_posting_completion` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain_code` VARCHAR(20) NOT NULL DEFAULT '',
  `fbillno` VARCHAR(50) NOT NULL DEFAULT '',
  `posting_id` BIGINT NOT NULL,
  `transaction_group_id` BIGINT NULL,
  `route_kind` VARCHAR(30) NULL,
  `completed_at` DATETIME NOT NULL,
  `create_time` DATETIME NOT NULL,
  `create_user` VARCHAR(50) NULL,
  `update_time` DATETIME NULL,
  `update_user` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ado_iqc_completion` (`tenant_id`,`domain_code`,`fbillno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DOP-L3 IQC过账完成锚(Phase7C,与C2业务事务同提交)';

-- ═══ L0（态①只读贴源）：IQC 收货标准层 + 双源 mdp_entity 登记（就位不启用） ═══
-- 与 IqcReceiptStateMdpSyncService.EnsureTablesAsync 一致
CREATE TABLE IF NOT EXISTS `mdp_std_iqc_receipt_state` (
  `id` BIGINT NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `domain` VARCHAR(20) NOT NULL DEFAULT '',
  `fbillno` VARCHAR(50) NOT NULL DEFAULT '',
  `qc_nbr` VARCHAR(50) NULL,
  `item_num` VARCHAR(100) NULL,
  `location` VARCHAR(50) NULL,
  `lot_serial` VARCHAR(100) NULL,
  `rct_nbr` VARCHAR(50) NULL,
  `pur_ord` VARCHAR(50) NULL,
  `pur_line` INT NULL,
  `potype` VARCHAR(20) NULL,
  `qty_ordered` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `received_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `returned_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `sample_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `receipt_pending_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `receivable_detail_count` INT NOT NULL DEFAULT 0,
  `source_system` VARCHAR(50) NULL,
  `sync_batch_id` VARCHAR(50) NULL,
  `sync_time` DATETIME NULL,
  `create_time` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mdp_std_iqc_rct` (`tenant_id`,`domain`,`fbillno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='L0 IQC收货标准层(只读贴源)';

-- 双源登记：同一实体 entity_code 两份配置，本库 status=1 / dopdemorq status=0（就位不启用，切源不改读代码）
-- 源库层 join 由 transform 完成；此处登记入站源实体（态①贴源模板，与 1.0.268/270 同构）。
-- 说明：IQC 收货态需多源表(qms_qcp_inspbill/inspecapplyn/insappnentry/PurOrdRctDetail/PurOrdDetail/MissedPrint)，
--       实际入站按单表拉 stg 再于主库 join；下方为标准层聚合实体登记占位，具体逐表 entity 明细在实库验证批(Phase 5B)补齐。
-- INSERT INTO mdp_entity (...) VALUES (... 'IQC_RECEIPT_STATE' ... AIDOPDEV_MYSQL ... status=1 ...);
-- INSERT INTO mdp_entity (...) VALUES (... 'IQC_RECEIPT_STATE' ... DOPDEMORQ_SQLSERVER ... status=0 ...);