-- =============================================
-- SKU Match Service 数据库初始化脚本
-- =============================================

CREATE DATABASE IF NOT EXISTS erp_db;
USE erp_db;

-- ---------------------------------------------
-- 平台信息表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `platform_info` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '平台ID',
    `platform_code` VARCHAR(50) NOT NULL COMMENT '平台代码：taobao/jd/pdd/douyin',
    `platform_name` VARCHAR(100) NOT NULL COMMENT '平台名称',
    `logo_url` VARCHAR(500) COMMENT '平台Logo',
    `status` TINYINT DEFAULT 1 COMMENT '状态：0禁用 1启用',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_platform_code` (`platform_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台信息表';

-- ---------------------------------------------
-- 店铺表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `shop` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `platform_code` VARCHAR(50) NOT NULL COMMENT '平台代码',
    `shop_id` VARCHAR(100) NOT NULL COMMENT '店铺在平台的ID',
    `shop_name` VARCHAR(200) NOT NULL COMMENT '店铺名称',
    `auth_status` TINYINT DEFAULT 0 COMMENT '授权状态：0未授权 1已授权 2授权过期',
    `access_token` VARCHAR(500) COMMENT '访问令牌(加密存储)',
    `refresh_token` VARCHAR(500) COMMENT '刷新令牌',
    `token_expires_at` DATETIME COMMENT '令牌过期时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_platform_shop` (`platform_code`, `shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺表';

-- ---------------------------------------------
-- 平台商品表（SPU）
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `platform_spu` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `shop_id` BIGINT NOT NULL COMMENT '店铺ID',
    `platform_code` VARCHAR(50) NOT NULL COMMENT '平台代码',
    `spu_id` VARCHAR(100) NOT NULL COMMENT '平台SPU ID',
    `spu_name` VARCHAR(300) COMMENT '商品名称',
    `spu_code` VARCHAR(100) COMMENT '商品编码',
    `price` DECIMAL(12,2) COMMENT '售价',
    `original_price` DECIMAL(12,2) COMMENT '原价',
    `stock` INT DEFAULT 0 COMMENT '库存',
    `images` TEXT COMMENT '图片JSON',
    `specs` TEXT COMMENT '规格属性JSON',
    `status` VARCHAR(20) DEFAULT 'onsale' COMMENT '状态：onsale/off_sale/deleted',
    `sync_status` VARCHAR(20) DEFAULT 'synced' COMMENT '同步状态',
    `last_sync_at` DATETIME COMMENT '最后同步时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_shop_spu` (`shop_id`, `spu_id`),
    KEY `idx_platform_code` (`platform_code`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台商品SPU表';

-- ---------------------------------------------
-- 平台SKU表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `platform_sku` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `spu_id` BIGINT NOT NULL COMMENT 'SPU ID',
    `shop_id` BIGINT NOT NULL COMMENT '店铺ID',
    `platform_code` VARCHAR(50) NOT NULL COMMENT '平台代码',
    `sku_id` VARCHAR(100) NOT NULL COMMENT '平台SKU ID',
    `sku_code` VARCHAR(100) COMMENT 'SKU编码',
    `sku_name` VARCHAR(300) COMMENT 'SKU名称(规格组合)',
    `price` DECIMAL(12,2) COMMENT '售价',
    `stock` INT DEFAULT 0 COMMENT '库存',
    `status` VARCHAR(20) DEFAULT 'onsale' COMMENT '状态：onsale/off_sale/deleted',
    `erp_sku_id` BIGINT COMMENT '绑定的ERP SKU ID',
    `erp_sku_code` VARCHAR(100) COMMENT '绑定的ERP SKU编码',
    `erp_sku_name` VARCHAR(200) COMMENT '绑定的ERP SKU名称',
    `match_type` VARCHAR(20) COMMENT '匹配方式：manual手动/rule规则/ai',
    `match_time` DATETIME COMMENT '匹配时间',
    `matched_rule_id` BIGINT COMMENT '匹配的规则ID',
    `sync_status` VARCHAR(20) DEFAULT 'synced',
    `last_sync_at` DATETIME,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_shop_sku` (`shop_id`, `sku_id`),
    KEY `idx_erp_sku_id` (`erp_sku_id`),
    KEY `idx_match_status` (`erp_sku_id`, `status`),
    KEY `idx_platform_code` (`platform_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台SKU表';

-- ---------------------------------------------
-- ERP商品SKU表（本地副本，用于匹配）
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `erp_sku` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `sku_code` VARCHAR(100) NOT NULL COMMENT 'SKU编码',
    `sku_name` VARCHAR(200) NOT NULL COMMENT 'SKU名称',
    `barcode` VARCHAR(100) COMMENT '条形码',
    `category` VARCHAR(100) COMMENT '分类',
    `brand` VARCHAR(100) COMMENT '品牌',
    `price` DECIMAL(12,2) COMMENT '售价',
    `cost_price` DECIMAL(12,2) COMMENT '成本价',
    `stock` INT DEFAULT 0 COMMENT '库存',
    `status` VARCHAR(20) DEFAULT 'active' COMMENT '状态：active/inactive',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_sku_code` (`sku_code`),
    KEY `idx_barcode` (`barcode`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ERP商品SKU表';

-- ---------------------------------------------
-- SKU匹配记录表（历史）
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `sku_match_record` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `platform_sku_id` BIGINT NOT NULL COMMENT '平台SKU ID',
    `platform_sku_code` VARCHAR(100) NOT NULL COMMENT '平台SKU编码',
    `platform_sku_name` VARCHAR(300) COMMENT '平台SKU名称',
    `erp_sku_id` BIGINT NOT NULL COMMENT 'ERP SKU ID',
    `erp_sku_code` VARCHAR(100) NOT NULL COMMENT 'ERP SKU编码',
    `erp_sku_name` VARCHAR(200) COMMENT 'ERP SKU名称',
    `match_type` VARCHAR(20) NOT NULL COMMENT '匹配类型：manual手动/rule规则/ai',
    `match_rule_id` BIGINT COMMENT '匹配规则ID(rule类型时)',
    `confidence` DECIMAL(5,4) COMMENT '置信度(AI匹配)',
    `matched` TINYINT DEFAULT 1 COMMENT '是否匹配：0取消匹配 1匹配',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_platform_sku_id` (`platform_sku_id`),
    KEY `idx_erp_sku_id` (`erp_sku_id`),
    KEY `idx_match_type` (`match_type`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SKU匹配记录表';

-- ---------------------------------------------
-- 匹配规则表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `sku_match_rule` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '规则名称',
    `priority` INT DEFAULT 0 COMMENT '优先级(数字越大优先级越高)',
    `platform` VARCHAR(50) COMMENT '适用平台，空表示全部',
    `field` VARCHAR(20) NOT NULL COMMENT '匹配字段：name名称/code编码/barcode',
    `pattern` VARCHAR(500) NOT NULL COMMENT '匹配模式/关键词',
    `replace_pattern` VARCHAR(500) COMMENT '替换模式',
    `match_type` VARCHAR(20) NOT NULL COMMENT '匹配方式：exact精确/contains包含/regex正则',
    `enabled` TINYINT DEFAULT 1 COMMENT '是否启用：0禁用 1启用',
    `match_count` INT DEFAULT 0 COMMENT '匹配次数',
    `success_count` INT DEFAULT 0 COMMENT '成功次数',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_platform` (`platform`),
    KEY `idx_enabled` (`enabled`),
    KEY `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SKU匹配规则表';

-- ---------------------------------------------
-- AI匹配配置表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_match_config` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `enabled` TINYINT DEFAULT 0 COMMENT '是否启用AI匹配',
    `model` VARCHAR(100) DEFAULT 'gpt-4' COMMENT 'AI模型',
    `threshold` DECIMAL(5,4) DEFAULT 0.8000 COMMENT '相似度阈值',
    `auto_match` TINYINT DEFAULT 0 COMMENT '是否自动匹配(超过阈值自动绑定)',
    `similarity_field` VARCHAR(20) DEFAULT 'name' COMMENT '相似度字段：name/name+code/embedding',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI匹配配置表';

-- 初始化默认AI配置
INSERT INTO `ai_match_config` (`id`, `enabled`, `model`, `threshold`, `auto_match`, `similarity_field`) 
VALUES (1, 0, 'gpt-4', 0.8000, 0, 'name') ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- ---------------------------------------------
-- 匹配任务表
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `sku_match_task` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `task_id` VARCHAR(100) NOT NULL COMMENT '任务ID',
    `task_type` VARCHAR(20) NOT NULL COMMENT '任务类型：rule规则/ai',
    `platform` VARCHAR(50) COMMENT '执行平台',
    `status` VARCHAR(20) DEFAULT 'pending' COMMENT '状态：pending/running/completed/failed',
    `total_count` INT DEFAULT 0 COMMENT '总SKU数',
    `matched_count` INT DEFAULT 0 COMMENT '匹配成功数',
    `failed_count` INT DEFAULT 0 COMMENT '失败数',
    `error_msg` TEXT COMMENT '错误信息',
    `started_at` DATETIME COMMENT '开始时间',
    `completed_at` DATETIME COMMENT '完成时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_task_id` (`task_id`),
    KEY `idx_status` (`status`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='匹配任务表';

-- ---------------------------------------------
-- 初始化平台数据
-- ---------------------------------------------
INSERT INTO `platform_info` (`platform_code`, `platform_name`, `logo_url`, `status`, `sort_order`) VALUES
('taobao', '淘宝', 'https://img.alicdn.com/tfs/TB1XlF3RpXXXXc6XXXXXXXXXXXX-200-200.png', 1, 1),
('jd', '京东', 'https://img1.360buyimg.com/pop_jdshop/imgs/200-200.png', 1, 2),
('pdd', '拼多多', 'https://static.pinduoduo.com/favicon.ico', 1, 3),
('douyin', '抖音', 'https://www.douyin.com/favicon.ico', 1, 4)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- ---------------------------------------------
-- 示例店铺数据
-- ---------------------------------------------
INSERT INTO `shop` (`platform_code`, `shop_id`, `shop_name`, `auth_status`) VALUES
('taobao', 'tb1', '淘宝官方旗舰店', 1),
('taobao', 'tb2', '淘宝专营店', 1),
('jd', 'jd1', '京东自营店', 1),
('jd', 'jd2', '京东专营店', 1),
('pdd', 'pdd1', '拼多多官方旗舰店', 1),
('pdd', 'pdd2', '拼多多专营店', 1),
('douyin', 'dy1', '抖音小店', 1),
('douyin', 'dy2', '抖音专营店', 1)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;