spider_work/jiwu_spider/schema.sql

-- 集物星球爬虫建表 DDL
-- 规范：自增 id 物理主键；业务唯一键单独建 UNIQUE；时间字段固定 gmt_create_time/gmt_modified_time datetime；表名 jw_ 前缀 + _record 后缀。
-- 金额字段：入库前已 ÷1000000 换算成「元」存储(DECIMAL，两位小数)。实测原始 99000000 → 存 99.00 元(App ¥99.00)。
SET NAMES utf8mb4;

-- ========== 1. 在售商品（最新状态，index/top 抓取）==========
CREATE TABLE IF NOT EXISTS `jw_onsale_product_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `goods_id` BIGINT NOT NULL COMMENT '商品ID(goodsId)',
  `program_id` BIGINT DEFAULT NULL COMMENT '节目/团ID(programId)',
  `goods_name` VARCHAR(500) DEFAULT NULL COMMENT '商品名',
  `product_type` VARCHAR(8) DEFAULT NULL COMMENT '商品类型码(1福袋2变风3错版卡4原盒)',
  `business_type` INT DEFAULT NULL COMMENT '业务线businessType',
  `block_type` INT DEFAULT NULL COMMENT '板块类型blockType',
  `goods_ip_id` INT DEFAULT NULL COMMENT 'IP分类ID',
  `goods_ip_name` VARCHAR(64) DEFAULT NULL COMMENT 'IP名(宝可梦/球星卡/海贼王等)',
  `gift_series` VARCHAR(255) DEFAULT NULL COMMENT '赠品系列(cardGoods.giftSeries，App 赠品系列页的"系列"，详情补全)',
  `random_type` INT DEFAULT NULL COMMENT '玩法类型(randomType)',
  `corp_info_id` BIGINT DEFAULT NULL COMMENT '商家ID',
  `corp_info_name` VARCHAR(128) DEFAULT NULL COMMENT '商家名',
  `amount` DECIMAL(12,2) DEFAULT NULL COMMENT '价格(元)，含首购优惠价',
  `highest_price` DECIMAL(12,2) DEFAULT NULL COMMENT '最高价(元)',
  `lowest_price` DECIMAL(12,2) DEFAULT NULL COMMENT '最低价(元)',
  `stock_amount` INT DEFAULT NULL COMMENT '总库存/总份数',
  `residue_stock_amount` INT DEFAULT NULL COMMENT '剩余库存',
  `sold_count` INT DEFAULT NULL COMMENT '已售数(stock-residue或字段)',
  `specification_name` VARCHAR(128) DEFAULT NULL COMMENT '规格(如10盒/3包手雷+2包Hobby)',
  `report_status` INT DEFAULT NULL COMMENT '拆卡报告状态',
  `live_status` INT DEFAULT NULL COMMENT '直播状态',
  `sold_time` DATETIME DEFAULT NULL COMMENT '上架/开售时间',
  `product_pic` VARCHAR(255) DEFAULT NULL COMMENT '商品图相对路径',
  `is_on_sale` TINYINT NOT NULL DEFAULT 1 COMMENT '是否在售:1是0已下架',
  -- 详情页补全字段（来自 merchantGoodsId，由 jw_detail 补拉；detail_fetched 控制每商品补一次）
  `img` VARCHAR(500) DEFAULT NULL COMMENT '商品图片完整URL(FILE_DOMAIN+resList首图resAddr)',
  `goods_type` VARCHAR(32) DEFAULT NULL COMMENT '商品类型(goodsType,如MERCHANT_PRODUCT)',
  `price` DECIMAL(12,2) DEFAULT NULL COMMENT '价格(元)',
  `original_price` DECIMAL(12,2) DEFAULT NULL COMMENT '原价(元)',
  `plan_up_time` DATETIME DEFAULT NULL COMMENT '计划上架时间(planUpTime)',
  `off_shelf_time` DATETIME DEFAULT NULL COMMENT '下架时间(offShelfTime)',
  `stock` INT DEFAULT NULL COMMENT '详情总库存(stock)',
  `surplus_stock` INT DEFAULT NULL COMMENT '详情剩余库存(surplusStock)',
  `status` INT DEFAULT NULL COMMENT '详情状态(status)',
  `collection_card_name` VARCHAR(255) DEFAULT NULL COMMENT '集卡名称(cardGoods.collectionCardName)',
  `gift_way` VARCHAR(64) DEFAULT NULL COMMENT '赠卡方式(cardGoods.giftWay)',
  `random_way` VARCHAR(64) DEFAULT NULL COMMENT '随机方式(cardGoods.randomWay)',
  `detail_fetched` TINYINT NOT NULL DEFAULT 0 COMMENT '详情字段是否已补:1是0否',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_goods_id` (`goods_id`),
  KEY `idx_corp` (`corp_info_id`),
  KEY `idx_onsale` (`is_on_sale`),
  KEY `idx_detail_fetched` (`detail_fetched`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球在售商品(最新状态)';

-- ========== 2. 已售商品（只增，corp/history 抓取）==========
CREATE TABLE IF NOT EXISTS `jw_sold_product_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `goods_id` BIGINT NOT NULL COMMENT '商品ID(goodsId)',
  `program_id` BIGINT DEFAULT NULL COMMENT '节目/团ID',
  `goods_name` VARCHAR(500) DEFAULT NULL COMMENT '商品名',
  `product_type` VARCHAR(8) DEFAULT NULL COMMENT '商品类型码',
  `business_type` INT DEFAULT NULL COMMENT '业务线',
  `goods_ip_id` INT DEFAULT NULL COMMENT 'IP分类ID',
  `goods_ip_name` VARCHAR(64) DEFAULT NULL COMMENT 'IP名',
  `gift_series` VARCHAR(255) DEFAULT NULL COMMENT '赠品系列(cardGoods.giftSeries，App 赠品系列页的"系列"，详情补全)',
  `random_type` INT DEFAULT NULL COMMENT '玩法类型',
  `corp_info_id` BIGINT DEFAULT NULL COMMENT '商家ID',
  `corp_info_name` VARCHAR(128) DEFAULT NULL COMMENT '商家名',
  `amount` DECIMAL(12,2) DEFAULT NULL COMMENT '价格(元)',
  `highest_price` DECIMAL(12,2) DEFAULT NULL COMMENT '最高价(元)',
  `lowest_price` DECIMAL(12,2) DEFAULT NULL COMMENT '最低价(元)',
  `stock_amount` INT DEFAULT NULL COMMENT '总库存/总份数',
  `residue_stock_amount` INT DEFAULT NULL COMMENT '剩余库存',
  `specification_name` VARCHAR(128) DEFAULT NULL COMMENT '规格',
  `report_status` INT DEFAULT NULL COMMENT '拆卡报告状态',
  `live_replay_url` VARCHAR(255) DEFAULT NULL COMMENT '回放地址',
  `sold_time` DATETIME DEFAULT NULL COMMENT '开售时间',
  `soldout_time` DATETIME DEFAULT NULL COMMENT '售罄时间',
  `finish_time` DATETIME DEFAULT NULL COMMENT '结束时间',
  `buy_fetched` TINYINT NOT NULL DEFAULT 0 COMMENT '购买记录是否已抓全并入库:1是0否',
  -- 详情页补全字段（来自 merchantGoodsId，由 jw_detail 补拉；detail_fetched 控制每商品补一次）
  `img` VARCHAR(500) DEFAULT NULL COMMENT '商品图片完整URL(FILE_DOMAIN+resList首图resAddr)',
  `goods_type` VARCHAR(32) DEFAULT NULL COMMENT '商品类型(goodsType,如MERCHANT_PRODUCT)',
  `price` DECIMAL(12,2) DEFAULT NULL COMMENT '价格(元)',
  `original_price` DECIMAL(12,2) DEFAULT NULL COMMENT '原价(元)',
  `plan_up_time` DATETIME DEFAULT NULL COMMENT '计划上架时间(planUpTime)',
  `off_shelf_time` DATETIME DEFAULT NULL COMMENT '下架时间(offShelfTime)',
  `stock` INT DEFAULT NULL COMMENT '详情总库存(stock)',
  `surplus_stock` INT DEFAULT NULL COMMENT '详情剩余库存(surplusStock)',
  `status` INT DEFAULT NULL COMMENT '详情状态(status)',
  `collection_card_name` VARCHAR(255) DEFAULT NULL COMMENT '集卡名称(cardGoods.collectionCardName)',
  `gift_way` VARCHAR(64) DEFAULT NULL COMMENT '赠卡方式(cardGoods.giftWay)',
  `random_way` VARCHAR(64) DEFAULT NULL COMMENT '随机方式(cardGoods.randomWay)',
  `detail_fetched` TINYINT NOT NULL DEFAULT 0 COMMENT '详情字段是否已补:1是0否',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_goods_id` (`goods_id`),
  KEY `idx_corp` (`corp_info_id`),
  KEY `idx_sold_time` (`sold_time`),
  KEY `idx_buy_fetched` (`buy_fetched`),
  KEY `idx_detail_fetched` (`detail_fetched`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球已售商品(只增)';

-- ========== 3. 商家（最新状态，hotRecommend 抓取）==========
-- hotRecommend 商家字段：corpInfoId/corpInfoName/number(粉丝数)/saleNum(在售商品数)
-- 注：响应里的 userId 是查询者本人账号id(每行恒等)、非商家id，不存；好评/新品/简介接口体系无此字段，不建列
CREATE TABLE IF NOT EXISTS `jw_shop_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `corp_info_id` BIGINT NOT NULL COMMENT '商家ID(corpInfoId)',
  `corp_name` VARCHAR(128) DEFAULT NULL COMMENT '商家名(corpInfoName)',
  `fans_amount` INT DEFAULT NULL COMMENT '粉丝数(number)',
  `onsale_amount` INT DEFAULT NULL COMMENT '在售商品数(saleNum)',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_corp_info_id` (`corp_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球商家(hotRecommend 热门推荐, 最新状态)';

-- ========== 4. 购买记录（只增，赠品公示·玩家维度 publicity/user/group/pager，每商品每买家一行）==========
-- 不设唯一键、不 DB 去重：靠 jw_sold_product_record.buy_fetched 状态位控制「每个已售商品抓全一次」
-- （翻页取全→批量入库成功→再置 buy_fetched=1；失败不置位、下轮重试）。
CREATE TABLE IF NOT EXISTS `jw_player_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `goods_id` BIGINT NOT NULL COMMENT '商品ID',
  `user_id` BIGINT DEFAULT NULL COMMENT '买家用户ID(userId)',
  `user_nick` VARCHAR(128) DEFAULT NULL COMMENT '买家昵称(userNick，可能为"匿名购买")',
  `pic_id` VARCHAR(255) DEFAULT NULL COMMENT '买家头像路径(picId)',
  `buy_count` INT DEFAULT NULL COMMENT '购买份数(count)',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods` (`goods_id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球购买记录(赠品公示玩家维度，每商品每买家一行，全站已售)';

-- ========== 5. 在售监控提醒状态（盯 jake/九叔，去重用）==========
CREATE TABLE IF NOT EXISTS `jw_onsale_alert_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `goods_id` BIGINT NOT NULL COMMENT '商品ID',
  `corp_info_id` BIGINT DEFAULT NULL COMMENT '商家ID',
  `corp_info_name` VARCHAR(128) DEFAULT NULL COMMENT '商家名',
  `goods_name` VARCHAR(500) DEFAULT NULL COMMENT '商品名',
  `new_notified` TINYINT NOT NULL DEFAULT 0 COMMENT '新品上架已提醒:1是0否',
  `half_notified` TINYINT NOT NULL DEFAULT 0 COMMENT '进度过半已提醒:1是0否',
  `ended_notified` TINYINT NOT NULL DEFAULT 0 COMMENT '结束战报已提醒:1是0否',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_goods_id` (`goods_id`),
  KEY `idx_corp` (`corp_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球在售监控提醒状态';

-- ========== 6. 拆卡报告（只增，gift/report/query/search/pager 逐条）==========
CREATE TABLE IF NOT EXISTS `jw_report_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `gift_report_id` BIGINT NOT NULL COMMENT '拆卡报告唯一ID(giftReportId，递增，天然去重键)',
  `goods_id` BIGINT DEFAULT NULL COMMENT '商品ID(来自查询参数，响应体不含)',
  `user_id` BIGINT DEFAULT NULL COMMENT '用户ID',
  `username` VARCHAR(128) DEFAULT NULL COMMENT '用户脱敏昵称',
  `corp_id` BIGINT DEFAULT NULL COMMENT '商家ID',
  `corp_name` VARCHAR(128) DEFAULT NULL COMMENT '商家名',
  `goods_name` VARCHAR(500) DEFAULT NULL COMMENT '商品名',
  `serial_item_name` VARCHAR(255) DEFAULT NULL COMMENT '开出的卡/系列名(serialItemName)',
  `res_addr_list` TEXT COMMENT '图片路径列表(resAddrList，JSON 串)',
  `winner_status` INT DEFAULT NULL COMMENT '中奖状态',
  `anonymous_status` INT DEFAULT NULL COMMENT '匿名状态',
  `my_gift_status` INT DEFAULT NULL COMMENT '状态(myGiftStatus)',
  `report_time` DATETIME DEFAULT NULL COMMENT '拆卡时间(createTime)',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_gift_report_id` (`gift_report_id`),
  KEY `idx_goods` (`goods_id`),
  KEY `idx_corp` (`corp_id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球拆卡报告(逐条只增)';

-- ========== 7. 在售每日快照（商品+日期唯一，四档 upsert 保留当天最新值，供在售趋势差分）==========
CREATE TABLE IF NOT EXISTS `jw_onsale_daily_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `goods_id` BIGINT NOT NULL COMMENT '商品ID',
  `corp_info_id` BIGINT DEFAULT NULL COMMENT '所属商家ID',
  `corp_info_name` VARCHAR(128) DEFAULT NULL COMMENT '所属商家名',
  `snapshot_date` DATE NOT NULL COMMENT '快照日期(YYYY-MM-DD)',
  `product_type` VARCHAR(8) DEFAULT NULL COMMENT '商品类型码(1福袋2变风盒3错版卡4原盒)',
  `stock_amount` INT DEFAULT NULL COMMENT '当日总份数',
  `sold_count` INT DEFAULT NULL COMMENT '当日累计已售',
  `residue_stock_amount` INT DEFAULT NULL COMMENT '当日剩余库存',
  `live_status` INT DEFAULT NULL COMMENT '当日直播/在售状态',
  `amount` DECIMAL(12,2) DEFAULT NULL COMMENT '当日单价(元)',
  `gmt_create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_goods_date` (`goods_id`,`snapshot_date`),
  KEY `idx_snapshot_date` (`snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集物星球在售每日快照(商品+日期唯一,当天多档更新为最新值)';