cfc/scripts/sync_kmdiary.sql

-- ============================================================
-- 康美日记产品数据导入脚本（仅结构变更 + 分类品牌）
-- 用法：mysql -h 192.168.16.251 -u zxyj -p zxyj < sync_kmdiary.sql
-- ============================================================

SET NAMES utf8mb4;

-- 添加字段
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS price DECIMAL(10,2) DEFAULT 0 COMMENT '当前售价';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS original_price INT DEFAULT 0 COMMENT '原价（整数）';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS sold_num INT DEFAULT 0 COMMENT '已售数量';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_platform VARCHAR(50) DEFAULT '' COMMENT '来源平台';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_id VARCHAR(100) DEFAULT '' COMMENT '来源平台商品ID';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_url VARCHAR(500) DEFAULT '' COMMENT '来源平台商品链接';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS product_images TEXT COMMENT '商品图片URL（JSON数组）';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS spec VARCHAR(200) DEFAULT '' COMMENT '规格';

CREATE INDEX IF NOT EXISTS idx_source ON nutrition_product(source_platform, source_id);

-- 插入分类
INSERT INTO nutrition_category (name, sort_order, create_time, update_time) VALUES
('养生饮品', 1, NOW(), NOW()),
('保健食品', 2, NOW(), NOW()),
('中药材', 3, NOW(), NOW()),
('养生食材', 4, NOW(), NOW()),
('养生零食', 5, NOW(), NOW()),
('美妆个护', 6, NOW(), NOW()),
('健康器械', 7, NOW(), NOW()),
('药膳炖汤', 8, NOW(), NOW()),
('外用护理', 9, NOW(), NOW()),
('其他', 10, NOW(), NOW())
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

-- 插入品牌
INSERT INTO nutrition_brand (name, create_time, update_time) VALUES
('康美时代', NOW(), NOW()),
('福东海', NOW(), NOW()),
('谱洛旺思', NOW(), NOW()),
('康美', NOW(), NOW()),
('威斯健', NOW(), NOW()),
('云教授', NOW(), NOW()),
('许尤佳', NOW(), NOW()),
('莱思黎', NOW(), NOW()),
('食膳养', NOW(), NOW()),
('SKG', NOW(), NOW()),
('廿四养', NOW(), NOW()),
('世净', NOW(), NOW()),
('山茶润', NOW(), NOW()),
('白云山', NOW(), NOW()),
('纳富山', NOW(), NOW()),
('小米有品', NOW(), NOW()),
('好体知', NOW(), NOW()),
('参泽源萃', NOW(), NOW()),
('藏千岁', NOW(), NOW()),
('吉丽特', NOW(), NOW()),
('千年珍', NOW(), NOW()),
('PVPV', NOW(), NOW())
ON DUPLICATE KEY UPDATE create_time = create_time;