-- 袋牌模块MySQL建表语句

-- 创建袋牌表
CREATE TABLE IF NOT EXISTS `bag_tags` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `TagNumber` VARCHAR(100) NOT NULL COMMENT '袋牌号',
  `ChannelName` VARCHAR(50) NOT NULL COMMENT '渠道商名称',
  `Status` VARCHAR(20) DEFAULT 'Generated' COMMENT '状态：Generated, Opened, Closed',
  `CreatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `OpenedAt` DATETIME DEFAULT NULL COMMENT '打开时间',
  `ClosedAt` DATETIME DEFAULT NULL COMMENT '关闭时间',
  `Creator` VARCHAR(50) NOT NULL COMMENT '创建人',
  `Remark` VARCHAR(200) NULL COMMENT '备注',
  PRIMARY KEY (`Id`),
  KEY `idx_tag_number` (`TagNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='袋牌关联运单表';

-- 创建袋牌与尾程运单号关联表
CREATE TABLE IF NOT EXISTS `bag_tag_waybills` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `TagNumber` VARCHAR(100) NOT NULL COMMENT '袋牌号',
  `FinalMileTrackingNumber` VARCHAR(100) NOT NULL COMMENT '尾程运单号',
  `CreatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `Creator` VARCHAR(50) NOT NULL COMMENT '创建人',
  PRIMARY KEY (`Id`),
  KEY `IX_TagNumber` (`TagNumber`),
  KEY `IX_FinalMileTrackingNumber` (`FinalMileTrackingNumber`),
  CONSTRAINT `FK_bag_tag_waybills_bag_tags` FOREIGN KEY (`TagNumber`) REFERENCES `bag_tags` (`TagNumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='袋牌与尾程运单号关联表';

-- 插入测试数据（可选）
INSERT INTO `bag_tags` (`TagNumber`, `ChannelName`, `Status`, `Creator`) VALUES
('USPS202601271200000001', 'USPS', 'Generated', 'test_user'),
('UPS202601271200000001', 'UPS', 'Generated', 'test_user'),
('FEDEX202601271200000001', 'FEDEX', 'Generated', 'test_user');

-- 插入测试关联数据（可选）
-- 注意：需要先将袋牌状态改为Opened
UPDATE `bag_tags` SET `Status` = 'Opened', `OpenedAt` = CURRENT_TIMESTAMP WHERE `TagNumber` = 'USPS202601271200000001';

INSERT INTO `bag_tag_waybills` (`TagNumber`, `FinalMileTrackingNumber`, `Creator`) VALUES
('USPS202601271200000001', '1Z999AA10123456789', 'test_user'),
('USPS202601271200000001', '1Z999AA10123456790', 'test_user');