-- Run this script against the configured planning MySQL/MariaDB database.
CREATE TABLE IF NOT EXISTS alibaba_1688_products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '服务端主键',
  product_id VARCHAR(80) NOT NULL COMMENT '1688 商品 ID，全局唯一',
  source_url VARCHAR(2000) NULL COMMENT '1688 商品链接',
  title VARCHAR(500) NOT NULL COMMENT '商品标题',
  description MEDIUMTEXT NULL COMMENT '商品描述',
  store_name VARCHAR(300) NULL COMMENT '1688 店铺/厂家',
  category VARCHAR(500) NULL COMMENT '1688 商品分类',
  import_tag VARCHAR(40) NOT NULL DEFAULT '测款' COMMENT '测款/风格化',
  price_text VARCHAR(300) NULL COMMENT '价格展示文本',
  min_price DECIMAL(18,2) NULL COMMENT '最低采购价',
  max_price DECIMAL(18,2) NULL COMMENT '最高采购价',
  main_image_url VARCHAR(2000) NULL COMMENT '首张主图',
  main_image_count INT NOT NULL DEFAULT 0 COMMENT '主图数量',
  detail_image_count INT NOT NULL DEFAULT 0 COMMENT '详情图数量',
  sku_count INT NOT NULL DEFAULT 0 COMMENT 'SKU 组合数量',
  sku_fingerprint CHAR(64) NULL COMMENT '有序 SKU 规格指纹',
  attribute_count INT NOT NULL DEFAULT 0 COMMENT '属性数量',
  product_snapshot_json LONGTEXT NOT NULL COMMENT '客户端采集完整快照',
  source_collected_at DATETIME NULL COMMENT '客户端采集时间',
  sync_operator_name VARCHAR(120) NULL COMMENT '同步运营花名',
  source_type VARCHAR(30) NOT NULL DEFAULT 'product' COMMENT 'product/store',
  source_import_id VARCHAR(80) NULL COMMENT '客户端导入任务 ID',
  api_key_id VARCHAR(120) NULL COMMENT '同步 API Key ID',
  api_key_name VARCHAR(120) NULL COMMENT '同步 API Key 名称',
  publish_status VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT '后续上架状态',
  last_publish_error VARCHAR(1000) NULL COMMENT '最近上架失败原因',
  published_goods_json LONGTEXT NULL COMMENT '各店铺上架商品 ID',
  last_published_at DATETIME NULL COMMENT '最近上架完成时间',
  sync_version INT NOT NULL DEFAULT 1 COMMENT '完整覆盖版本号',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  UNIQUE KEY uk_alibaba_1688_product_id (product_id),
  KEY idx_alibaba_1688_product_store (store_name),
  KEY idx_alibaba_1688_product_tag (import_tag, updated_at),
  KEY idx_alibaba_1688_product_publish (publish_status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='1688 分销商品服务端快照';

-- Encoding records intentionally have no foreign key to alibaba_1688_products.
-- Deleting and re-importing a source product must not allow a second encoding.
CREATE TABLE IF NOT EXISTS alibaba_1688_product_encodings (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '编码登记主键',
  product_id VARCHAR(80) NOT NULL COMMENT '1688 商品 ID，全局唯一且删除商品后仍保留',
  status VARCHAR(20) NOT NULL DEFAULT 'encoding' COMMENT 'encoding/encoded/failed/conflict',
  claim_token CHAR(36) NULL COMMENT '本次编码占用令牌',
  request_json LONGTEXT NULL COMMENT '提交编码服务的完整业务参数',
  response_json LONGTEXT NULL COMMENT '编码服务原始成功响应',
  style_code VARCHAR(160) NULL COMMENT '主款式编码',
  style_codes_json LONGTEXT NULL COMMENT '全部款式编码',
  product_codes_json LONGTEXT NULL COMMENT '全部规格商品编码',
  sku_mapping_json LONGTEXT NULL COMMENT '1688 SKU 与商品编码映射',
  source_sku_count INT NULL COMMENT '编码时的 SKU 数量',
  source_sku_fingerprint CHAR(64) NULL COMMENT '编码时的有序 SKU 规格指纹',
  upstream_created TINYINT(1) NULL COMMENT '上游本次是否新建编码',
  operator_user_id VARCHAR(120) NULL COMMENT '编码操作人用户 ID',
  operator_name VARCHAR(120) NULL COMMENT '编码操作人',
  operator_department VARCHAR(120) NULL COMMENT '编码操作人部门',
  attempt_count INT NOT NULL DEFAULT 1 COMMENT '提交尝试次数',
  started_at DATETIME NULL COMMENT '最近一次提交开始时间',
  encoded_at DATETIME NULL COMMENT '编码成功时间',
  last_error VARCHAR(1000) NULL COMMENT '最近一次失败原因',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  UNIQUE KEY uk_alibaba_1688_encoding_product (product_id),
  KEY idx_alibaba_1688_encoding_status (status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='1688 商品一次性编码登记';