cakeMallSystem/sql/init.sql

CREATE DATABASE IF NOT EXISTS ssm;
USE ssm;

-- 1. 蛋糕分类
CREATE TABLE IF NOT EXISTS cake_category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    sort_order INT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 蛋糕
CREATE TABLE IF NOT EXISTS cake (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    main_image VARCHAR(255),
    taste VARCHAR(50),
    layers VARCHAR(50),
    filling VARCHAR(100),
    style VARCHAR(50),
    is_customizable TINYINT DEFAULT 0,
    is_new TINYINT DEFAULT 0,
    is_hot TINYINT DEFAULT 0,
    is_discount TINYINT DEFAULT 0,
    discount_price DECIMAL(10,2),
    stock INT DEFAULT 0,
    status VARCHAR(20) DEFAULT '上架',
    sales_count INT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 蛋糕图片
CREATE TABLE IF NOT EXISTS cake_image (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cake_id INT,
    image_url VARCHAR(255) NOT NULL,
    is_main TINYINT DEFAULT 0,
    sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 蛋糕规格
CREATE TABLE IF NOT EXISTS cake_spec (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cake_id INT,
    spec_name VARCHAR(50) NOT NULL,
    add_price DECIMAL(10,2) DEFAULT 0.00,
    sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 定制选项
CREATE TABLE IF NOT EXISTS custom_option (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cake_id INT,
    option_type VARCHAR(30) NOT NULL,
    option_name VARCHAR(50) NOT NULL,
    add_price DECIMAL(10,2) DEFAULT 0.00,
    sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 用户
CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    avatar VARCHAR(255),
    status VARCHAR(20) DEFAULT '正常',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. 地址
CREATE TABLE IF NOT EXISTS address (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail_address VARCHAR(255) NOT NULL,
    is_default TINYINT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. 购物车
CREATE TABLE IF NOT EXISTS cart (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. 购物车项
CREATE TABLE IF NOT EXISTS cart_item (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT,
    cake_id INT,
    spec_id INT,
    quantity INT DEFAULT 1,
    custom_json TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. 订单
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id INT,
    total_amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    pay_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT '待付款',
    receiver_name VARCHAR(50),
    receiver_phone VARCHAR(20),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail_address VARCHAR(255),
    delivery_date DATE,
    delivery_time VARCHAR(20),
    remark VARCHAR(500),
    refuse_reason VARCHAR(500),
    payment_method VARCHAR(20) DEFAULT '模拟支付',
    payment_time DATETIME,
    delivery_fee DECIMAL(10,2) DEFAULT 0.00,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. 订单项
CREATE TABLE IF NOT EXISTS order_item (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    cake_id INT,
    cake_name VARCHAR(100),
    spec_name VARCHAR(50),
    price DECIMAL(10,2),
    quantity INT DEFAULT 1,
    custom_json TEXT,
    image_url VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. 订单跟踪
CREATE TABLE IF NOT EXISTS order_tracking (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    from_status VARCHAR(20),
    to_status VARCHAR(20) NOT NULL,
    operator VARCHAR(50),
    remark VARCHAR(255),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. 收藏
CREATE TABLE IF NOT EXISTS favorite (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    cake_id INT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_cake (user_id, cake_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 14. 评价
CREATE TABLE IF NOT EXISTS review (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    cake_id INT,
    user_id INT,
    rating TINYINT NOT NULL,
    content VARCHAR(500),
    reply_content VARCHAR(500),
    status VARCHAR(20) DEFAULT '显示',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 15. 优惠券
CREATE TABLE IF NOT EXISTS coupon (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    condition_amount DECIMAL(10,2),
    discount_amount DECIMAL(10,2),
    discount_rate DECIMAL(3,2),
    total_count INT DEFAULT 0,
    remain_count INT DEFAULT 0,
    start_time DATETIME,
    end_time DATETIME,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 16. 用户优惠券
CREATE TABLE IF NOT EXISTS user_coupon (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    coupon_id INT,
    used TINYINT DEFAULT 0,
    used_time DATETIME,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 17. 活动
CREATE TABLE IF NOT EXISTS activity (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    content TEXT,
    start_time DATETIME,
    end_time DATETIME,
    image_url VARCHAR(255),
    status VARCHAR(20) DEFAULT '启用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 18. 留言
CREATE TABLE IF NOT EXISTS message (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(100),
    content TEXT NOT NULL,
    reply_content TEXT,
    reply_time DATETIME,
    status VARCHAR(20) DEFAULT '待回复',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 19. 管理员
CREATE TABLE IF NOT EXISTS admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    role VARCHAR(20) DEFAULT '管理员',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 20. 配送配置
CREATE TABLE IF NOT EXISTS delivery_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    region VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) DEFAULT 0.00,
    free_amount DECIMAL(10,2) DEFAULT 0.00,
    is_active TINYINT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 21. 公告
CREATE TABLE IF NOT EXISTS announcement (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    content TEXT,
    is_active TINYINT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 默认管理员，密码是123456
INSERT INTO admin (username, password, nickname, role) VALUES ('admin', 'e10adc3949ba59abbe56e057f20f883e', '系统管理员', '管理员');

-- ============================================
-- 模拟数据（覆盖所有业务功能流程）
-- ============================================

-- 1. cake_category — 5 个分类
INSERT INTO cake_category (id, name, sort_order) VALUES
(1, '水果款', 1),
(2, '慕斯款', 2),
(3, '儿童款', 3),
(4, '祝寿款', 4),
(5, '网红定制款', 5);

-- 2. cake — 15 款蛋糕, 每类 3 款
INSERT INTO cake (id, category_id, name, description, base_price, main_image, taste, layers, filling, style, is_customizable, is_new, is_hot, is_discount, discount_price, stock, status, sales_count) VALUES
(1, 1, '鲜果缤纷', '当季新鲜水果搭配轻盈奶油，清爽不腻', 168.00, '/upload/cake01.jpg', '甜/清爽', '单层/双层', '鲜奶油+时令水果', 'ins风', 1, 1, 1, 0, NULL, 100, '上架', 256),
(2, 1, '芒果千层', '进口芒果层层叠加，入口即化', 198.00, '/upload/cake02.jpg', '甜/果香', '单层/双层/三层', '芒果泥+淡奶油', '简约', 1, 0, 1, 1, 158.00, 80, '上架', 412),
(3, 1, '草莓慕斯蛋糕', '新鲜草莓搭配丝滑慕斯，少女心满满', 188.00, '/upload/cake03.jpg', '甜/微酸', '单层/双层', '草莓果酱+奶油芝士', 'ins风', 0, 0, 0, 1, 148.00, 60, '上架', 189),
(4, 2, '经典提拉米苏', '意式经典，咖啡与奶酪的完美融合', 228.00, '/upload/cake04.jpg', '苦甜/浓郁', '单层/双层', '马斯卡彭+浓缩咖啡', '欧式', 1, 0, 1, 0, NULL, 50, '上架', 321),
(5, 2, '黑森林蛋糕', '巧克力与樱桃的经典搭配', 208.00, '/upload/cake05.jpg', '甜/巧克力', '单层/双层', '巧克力碎+樱桃酱', '欧式', 0, 0, 0, 1, 168.00, 70, '上架', 278),
(6, 2, '抹茶红豆慕斯', '日式抹茶搭配蜜红豆，清新回甘', 198.00, '/upload/cake06.jpg', '微苦/清甜', '单层/双层', '抹茶慕斯+蜜红豆', '日式', 1, 1, 0, 0, NULL, 65, '上架', 145),
(7, 3, '卡通小汽车', '汽车造型蛋糕，孩子最爱', 238.00, '/upload/cake07.jpg', '甜/奶香', '单层', '奶油+果酱', '卡通', 0, 0, 1, 0, NULL, 40, '上架', 167),
(8, 3, 'Hello Kitty', '粉色Kitty造型，萌趣可爱', 258.00, '/upload/cake08.jpg', '甜/草莓', '单层', '草莓奶油+布丁', '卡通', 1, 0, 0, 0, NULL, 35, '上架', 134),
(9, 3, '恐龙世界', '立体恐龙造型，适合小男孩生日', 268.00, '/upload/cake09.jpg', '甜/巧克力', '单层', '巧克力奶油+脆珠', '卡通', 0, 1, 0, 0, NULL, 30, '上架', 89),
(10, 4, '松鹤延年', '传统祝寿主题，松鹤图案寓意长寿', 328.00, '/upload/cake10.jpg', '甜/清淡', '双层/三层', '鲜奶油+水果夹层', '中式传统', 1, 0, 0, 0, NULL, 25, '上架', 198),
(11, 4, '寿桃献瑞', '寿桃造型配寿字，大气喜庆', 368.00, '/upload/cake11.jpg', '甜/蜜桃', '双层', '蜜桃果肉+淡奶油', '中式传统', 0, 0, 1, 0, NULL, 20, '上架', 223),
(12, 4, '福寿双全', '双层祝寿蛋糕，福寿二字点缀', 398.00, '/upload/cake12.jpg', '甜/坚果', '双层/三层', '坚果碎+奶油', '中式传统', 1, 1, 0, 1, 338.00, 15, '上架', 76),
(13, 5, '星空流心', '神秘星空外观，切开流心惊艳', 258.00, '/upload/cake13.jpg', '甜/多种', '单层', '巧克力流心+蓝莓', 'ins风', 1, 1, 1, 0, NULL, 55, '上架', 345),
(14, 5, '网红脏脏蛋糕', '可可粉覆盖，切开爆浆', 188.00, '/upload/cake14.jpg', '苦甜', '单层', '巧克力流心+奶油', '网红', 0, 0, 1, 0, NULL, 90, '上架', 567),
(15, 5, '鲜花蛋糕', '可食用鲜花装饰，浪漫唯美', 298.00, '/upload/cake15.jpg', '甜/花香', '单层/双层', '玫瑰奶油+水果', 'ins风', 1, 0, 0, 0, NULL, 45, '上架', 234);

-- 3. cake_image — 每款蛋糕 2-3 张图
INSERT INTO cake_image (id, cake_id, image_url, is_main, sort_order) VALUES
(1, 1, '/upload/cake01.jpg', 1, 1),
(2, 1, '/upload/cake01_2.jpg', 0, 2),
(3, 2, '/upload/cake02.jpg', 1, 1),
(4, 2, '/upload/cake02_2.jpg', 0, 2),
(5, 3, '/upload/cake03.jpg', 1, 1),
(6, 4, '/upload/cake04.jpg', 1, 1),
(7, 4, '/upload/cake04_2.jpg', 0, 2),
(8, 5, '/upload/cake05.jpg', 1, 1),
(9, 6, '/upload/cake06.jpg', 1, 1),
(10, 6, '/upload/cake06_2.jpg', 0, 2),
(11, 7, '/upload/cake07.jpg', 1, 1),
(12, 8, '/upload/cake08.jpg', 1, 1),
(13, 8, '/upload/cake08_2.jpg', 0, 2),
(14, 9, '/upload/cake09.jpg', 1, 1),
(15, 10, '/upload/cake10.jpg', 1, 1),
(16, 10, '/upload/cake10_2.jpg', 0, 2),
(17, 11, '/upload/cake11.jpg', 1, 1),
(18, 12, '/upload/cake12.jpg', 1, 1),
(19, 13, '/upload/cake13.jpg', 1, 1),
(20, 13, '/upload/cake13_2.jpg', 0, 2),
(21, 14, '/upload/cake14.jpg', 1, 1),
(22, 15, '/upload/cake15.jpg', 1, 1),
(23, 15, '/upload/cake15_2.jpg', 0, 2);

-- 4. cake_spec — 尺寸规格
INSERT INTO cake_spec (id, cake_id, spec_name, add_price, sort_order) VALUES
(1,  1,  '6寸',  0.00,  1),
(2,  1,  '8寸',  50.00,  2),
(3,  1,  '10寸', 100.00, 3),
(4,  2,  '6寸',  0.00,  1),
(5,  2,  '8寸',  60.00,  2),
(6,  2,  '10寸', 120.00, 3),
(7,  3,  '6寸',  0.00,  1),
(8,  3,  '8寸',  50.00,  2),
(9,  4,  '6寸',  0.00,  1),
(10, 4,  '8寸',  60.00,  2),
(11, 5,  '6寸',  0.00,  1),
(12, 5,  '8寸',  50.00,  2),
(13, 6,  '6寸',  0.00,  1),
(14, 6,  '8寸',  55.00,  2),
(15, 7,  '6寸',  0.00,  1),
(16, 7,  '8寸',  80.00,  2),
(17, 8,  '6寸',  0.00,  1),
(18, 9,  '6寸',  0.00,  1),
(19, 9,  '8寸',  70.00,  2),
(20, 10, '8寸', 0.00,  1),
(21, 10, '10寸', 80.00,  2),
(22, 10, '12寸', 150.00, 3),
(23, 11, '8寸', 0.00,  1),
(24, 11, '10寸', 90.00,  2),
(25, 12, '8寸', 0.00,  1),
(26, 12, '10寸', 100.00, 2),
(27, 13, '6寸', 0.00,  1),
(28, 13, '8寸', 60.00,  2),
(29, 14, '6寸', 0.00,  1),
(30, 14, '8寸', 50.00,  2),
(31, 15, '6寸', 0.00,  1),
(32, 15, '8寸', 70.00,  2);

-- 5. custom_option — 定制选项(全局 + 部分蛋糕专属)
INSERT INTO custom_option (id, cake_id, option_type, option_name, add_price, sort_order) VALUES
-- 全局: 层数
(1,  NULL, '层数', '单层',      0.00,  1),
(2,  NULL, '层数', '双层',      80.00,  2),
(3,  NULL, '层数', '三层',      180.00, 3),
-- 全局: 蛋糕胚
(4,  NULL, '蛋糕胚', '原味戚风', 0.00,  1),
(5,  NULL, '蛋糕胚', '巧克力戚风', 10.00, 2),
(6,  NULL, '蛋糕胚', '抹茶戚风', 15.00, 3),
-- 全局: 奶油类型
(7,  NULL, '奶油', '淡奶油',    0.00,  1),
(8,  NULL, '奶油', '奶油芝士',  20.00,  2),
(9,  NULL, '奶油', '酸奶奶油',  15.00,  3),
-- 全局: 配饰
(10, NULL, '配饰', '祝福语卡片',  5.00,  1),
(11, NULL, '配饰', '生日蜡烛套装', 10.00, 2),
(12, NULL, '配饰', '鲜花装饰',  30.00,  3),
-- 蛋糕 1 专属: 水果款可加水果
(13, 1,   '配饰', '额外水果层', 25.00,  4),
-- 蛋糕 13 专属: 星空款流心颜色
(14, 13,  '配饰', '蓝色流心',   15.00,  1),
(15, 13,  '配饰', '粉色流心',   15.00,  2),
-- 蛋糕 15 专属: 鲜花款玫瑰花束
(16, 15,  '配饰', '玫瑰花束',   50.00,  1),
-- 全局: 尺寸
(17, NULL, '尺寸', '6寸',        0.00,  1),
(18, NULL, '尺寸', '8寸',       30.00,  2),
(19, NULL, '尺寸', '10寸',      60.00,  3),
(20, NULL, '尺寸', '12寸',     100.00,  4);

-- 6. user — 3 个模拟用户 (密码都是 123456 的 MD5)
INSERT INTO user (id, username, password, nickname, phone, email, status) VALUES
(1, 'zhangsan', 'e10adc3949ba59abbe56e057f20f883e', '张三', '13800138001', 'zhangsan@qq.com', '正常'),
(2, 'lisi',     'e10adc3949ba59abbe56e057f20f883e', '李四', '13800138002', 'lisi@qq.com', '正常'),
(3, 'wangwu',   'e10adc3949ba59abbe56e057f20f883e', '王五', '13800138003', 'wangwu@qq.com', '正常');

-- 7. address — 每个用户 1-2 个地址
INSERT INTO address (id, user_id, receiver_name, receiver_phone, province, city, district, detail_address, is_default) VALUES
(1, 1, '张三', '13800138001', '广东省', '深圳市', '南山区', '科技园南路100号A栋201', 1),
(2, 1, '张三', '13800138001', '广东省', '深圳市', '福田区', '华强北路200号B栋301', 0),
(3, 2, '李四', '13800138002', '广东省', '广州市', '天河区', '天河路300号C栋402', 1),
(4, 3, '王五', '13800138003', '北京市', '北京市', '朝阳区', '建国路88号D栋501', 1);

-- 8. cart — 每个用户一个购物车
INSERT INTO cart (id, user_id) VALUES
(1, 1),
(2, 2),
(3, 3);

-- 9. cart_item — 用户购物车中已有商品
INSERT INTO cart_item (id, cart_id, cake_id, spec_id, quantity, custom_json) VALUES
(1, 1, 1,  1,  2, NULL),
(2, 1, 14, 29, 1, NULL),
(3, 2, 6,  13, 1, '{"layers":"双层","cream":"奶油芝士"}'),
(4, 3, 13, 27, 1, NULL),
(5, 3, 3,  7,  1, NULL);

-- 10. orders — 覆盖完整订单状态机：待付款 → 待制作 → 制作中 → 配送中 → 已完成 / 已取消 / 已拒绝
INSERT INTO orders (id, order_no, user_id, total_amount, discount_amount, pay_amount, status, receiver_name, receiver_phone, province, city, district, detail_address, delivery_date, delivery_time, remark, refuse_reason, payment_method, payment_time, delivery_fee, create_time) VALUES
-- 张三的订单
(1, '202601010001', 1, 336.00,  0.00,  336.00,  '已完成',  '张三', '13800138001', '广东省', '深圳市', '南山区', '科技园南路100号A栋201', '2026-01-01', '10:00-12:00', '麻烦放门口', NULL, '模拟支付', '2026-01-01 10:30:00', 0.00, '2026-01-01 10:00:00'),
(2, '202601020002', 1, 198.00,  0.00,  198.00,  '已完成',  '张三', '13800138001', '广东省', '深圳市', '南山区', '科技园南路100号A栋201', '2026-01-03', '14:00-16:00', NULL, NULL, '模拟支付', '2026-01-02 09:00:00', 0.00, '2026-01-02 08:30:00'),
(3, '202601100003', 1, 516.00,  30.00, 486.00,  '配送中',  '张三', '13800138001', '广东省', '深圳市', '南山区', '科技园南路100号A栋201', '2026-01-11', '10:00-12:00', '生日用，请准时', NULL, '模拟支付', '2026-01-10 10:00:00', 10.00, '2026-01-10 09:30:00'),
(4, '202601150004', 1, 168.00,  0.00,  168.00,  '待付款',  '张三', '13800138001', '广东省', '深圳市', '福田区', '华强北路200号B栋301', '2026-01-16', '14:00-16:00', NULL, NULL, '模拟支付', NULL, 10.00, '2026-01-15 12:00:00'),
-- 李四的订单
(5, '202601050005', 2, 228.00,  0.00,  228.00,  '已完成',  '李四', '13800138002', '广东省', '广州市', '天河区', '天河路300号C栋402', '2026-01-05', '10:00-12:00', NULL, NULL, '模拟支付', '2026-01-05 09:00:00', 0.00, '2026-01-05 08:00:00'),
(6, '202601080006', 2, 258.00,  20.00, 238.00,  '制作中',  '李四', '13800138002', '广东省', '广州市', '天河区', '天河路300号C栋402', '2026-01-09', '14:00-16:00', '加急', NULL, '模拟支付', '2026-01-08 08:00:00', 0.00, '2026-01-08 07:00:00'),
(7, '202601120007', 2, 188.00,  0.00,  188.00,  '已取消',  '李四', '13800138002', '广东省', '广州市', '天河区', '天河路300号C栋402', '2026-01-13', '10:00-12:00', NULL, NULL, '模拟支付', NULL, 0.00, '2026-01-12 11:00:00'),
(8, '202601140008', 2, 368.00,  0.00,  368.00,  '已拒绝',  '李四', '13800138002', '广东省', '广州市', '天河区', '天河路300号C栋402', '2026-01-15', '14:00-16:00', '请加一个寿桃', '当日材料不足，无法制作加料版', '模拟支付', '2026-01-14 08:00:00', 0.00, '2026-01-14 07:30:00'),
-- 王五的订单
(9, '202601200009', 3, 258.00,  0.00,  258.00,  '待制作',  '王五', '13800138003', '北京市', '北京市', '朝阳区', '建国路88号D栋501', '2026-01-21', '10:00-12:00', NULL, NULL, '模拟支付', '2026-01-20 10:00:00', 0.00, '2026-01-20 09:30:00'),
(10, '202601220010', 3, 536.00, 0.00,  536.00,  '已完成',  '王五', '13800138003', '北京市', '北京市', '朝阳区', '建国路88号D栋501', '2026-01-22', '14:00-16:00', '不要坚果，过敏', NULL, '模拟支付', '2026-01-22 09:00:00', 10.00, '2026-01-22 08:30:00'),
(11, '202602010011', 3, 298.00,  0.00,  298.00,  '已完成',  '王五', '13800138003', '北京市', '北京市', '朝阳区', '建国路88号D栋501', '2026-02-01', '10:00-12:00', NULL, NULL, '模拟支付', '2026-02-01 08:00:00', 0.00, '2026-02-01 07:30:00'),
(12, '202602150012', 1, 258.00,  0.00,  258.00,  '已完成',  '张三', '13800138001', '广东省', '深圳市', '南山区', '科技园南路100号A栋201', '2026-02-14', '14:00-16:00', '情人节惊喜', NULL, '模拟支付', '2026-02-14 10:00:00', 0.00, '2026-02-14 09:30:00');

-- 11. order_item — 每个订单包含 1-2 个商品
INSERT INTO order_item (id, order_id, cake_id, cake_name, spec_name, price, quantity, custom_json, image_url) VALUES
(1,  1, 1,  '鲜果缤纷',    '6寸',  168.00, 2, NULL, '/upload/cake01.jpg'),
(2,  2, 2,  '芒果千层',    '6寸',  198.00, 1, NULL, '/upload/cake02.jpg'),
(3,  3, 1,  '鲜果缤纷',    '8寸',  218.00, 1, NULL, '/upload/cake01.jpg'),
(4,  3, 14, '网红脏脏蛋糕', '6寸',  188.00, 1, NULL, '/upload/cake14.jpg'),
(5,  3, 11, '寿桃献瑞',    '8寸',  100.00, 1, NULL, '/upload/cake11.jpg'),
(6,  4, 1,  '鲜果缤纷',    '6寸',  168.00, 1, NULL, '/upload/cake01.jpg'),
(7,  5, 4,  '经典提拉米苏', '6寸',  228.00, 1, NULL, '/upload/cake04.jpg'),
(8,  6, 13, '星空流心',    '6寸',  258.00, 1, NULL, '/upload/cake13.jpg'),
(9,  7, 14, '网红脏脏蛋糕', '6寸',  188.00, 1, NULL, '/upload/cake14.jpg'),
(10, 8, 11, '寿桃献瑞',    '8寸',  368.00, 1, NULL, '/upload/cake11.jpg'),
(11, 9, 13, '星空流心',    '6寸',  258.00, 1, NULL, '/upload/cake13.jpg'),
(12, 10, 10,'松鹤延年',    '8寸',  328.00, 1, NULL, '/upload/cake10.jpg'),
(13, 10, 15,'鲜花蛋糕',    '6寸',  298.00, 1, NULL, '/upload/cake15.jpg'),
(14, 11, 15,'鲜花蛋糕',    '6寸',  298.00, 1, NULL, '/upload/cake15.jpg'),
(15, 12, 13,'星空流心',    '6寸',  258.00, 1, NULL, '/upload/cake13.jpg');

-- 12. order_tracking — 订单状态变更日志
INSERT INTO order_tracking (id, order_id, from_status, to_status, operator, remark, create_time) VALUES
(1,  1,  NULL,   '待付款', '系统', '订单创建', '2026-01-01 10:00:00'),
(2,  1,  '待付款', '待制作', '系统', '支付成功', '2026-01-01 10:30:00'),
(3,  1,  '待制作', '制作中', 'admin', '已接单', '2026-01-01 11:00:00'),
(4,  1,  '制作中', '配送中', 'admin', '已出库配送', '2026-01-01 14:00:00'),
(5,  1,  '配送中', '已完成', '系统', '签收完成', '2026-01-01 15:00:00'),
(6,  2,  NULL,   '待付款', '系统', '订单创建', '2026-01-02 08:30:00'),
(7,  2,  '待付款', '待制作', '系统', '支付成功', '2026-01-02 09:00:00'),
(8,  2,  '待制作', '制作中', 'admin', '已接单', '2026-01-02 10:00:00'),
(9,  2,  '制作中', '配送中', 'admin', '已配送', '2026-01-03 09:00:00'),
(10, 2,  '配送中', '已完成', '系统', '签收完成', '2026-01-03 12:00:00'),
(11, 3,  NULL,   '待付款', '系统', '订单创建', '2026-01-10 09:30:00'),
(12, 3,  '待付款', '待制作', '系统', '支付成功', '2026-01-10 10:00:00'),
(13, 3,  '待制作', '制作中', 'admin', '已接单', '2026-01-10 11:00:00'),
(14, 3,  '制作中', '配送中', 'admin', '已出库', '2026-01-11 08:00:00'),
(15, 4,  NULL,   '待付款', '系统', '订单创建', '2026-01-15 12:00:00'),
(16, 5,  NULL,   '待付款', '系统', '订单创建', '2026-01-05 08:00:00'),
(17, 5,  '待付款', '待制作', '系统', '支付成功', '2026-01-05 09:00:00'),
(18, 5,  '待制作', '制作中', 'admin', '已接单', '2026-01-05 10:00:00'),
(19, 5,  '制作中', '配送中', 'admin', '已配送', '2026-01-05 11:00:00'),
(20, 5,  '配送中', '已完成', '系统', '签收完成', '2026-01-05 14:00:00'),
(21, 6,  NULL,   '待付款', '系统', '订单创建', '2026-01-08 07:00:00'),
(22, 6,  '待付款', '待制作', '系统', '支付成功', '2026-01-08 08:00:00'),
(23, 6,  '待制作', '制作中', 'admin', '已接单加工', '2026-01-08 10:00:00'),
(24, 7,  NULL,   '待付款', '系统', '订单创建', '2026-01-12 11:00:00'),
(25, 7,  '待付款', '已取消', '系统', '用户取消订单', '2026-01-12 11:30:00'),
(26, 8,  NULL,   '待付款', '系统', '订单创建', '2026-01-14 07:30:00'),
(27, 8,  '待付款', '待制作', '系统', '支付成功', '2026-01-14 08:00:00'),
(28, 8,  '待制作', '已拒绝', 'admin', '当日材料不足，无法制作加料版', '2026-01-14 09:00:00'),
(29, 9,  NULL,   '待付款', '系统', '订单创建', '2026-01-20 09:30:00'),
(30, 9,  '待付款', '待制作', '系统', '支付成功', '2026-01-20 10:00:00'),
(31, 10, NULL,   '待付款', '系统', '订单创建', '2026-01-22 08:30:00'),
(32, 10, '待付款', '待制作', '系统', '支付成功', '2026-01-22 09:00:00'),
(33, 10, '待制作', '制作中', 'admin', '已接单，注意过敏备注', '2026-01-22 10:00:00'),
(34, 10, '制作中', '配送中', 'admin', '已配送', '2026-01-22 13:00:00'),
(35, 10, '配送中', '已完成', '系统', '签收完成', '2026-01-22 15:00:00'),
(36, 11, NULL,   '待付款', '系统', '订单创建', '2026-02-01 07:30:00'),
(37, 11, '待付款', '待制作', '系统', '支付成功', '2026-02-01 08:00:00'),
(38, 11, '待制作', '制作中', 'admin', '已接单', '2026-02-01 09:00:00'),
(39, 11, '制作中', '配送中', 'admin', '已配送', '2026-02-01 10:00:00'),
(40, 11, '配送中', '已完成', '系统', '签收完成', '2026-02-01 12:00:00'),
(41, 12, NULL,   '待付款', '系统', '订单创建', '2026-02-14 09:30:00'),
(42, 12, '待付款', '待制作', '系统', '情人节订单', '2026-02-14 10:00:00'),
(43, 12, '待制作', '制作中', 'admin', '已接单', '2026-02-14 11:00:00'),
(44, 12, '制作中', '配送中', 'admin', '已配送', '2026-02-14 12:00:00'),
(45, 12, '配送中', '已完成', '系统', '签收完成', '2026-02-14 14:00:00');

-- 13. favorite — 收藏
INSERT INTO favorite (id, user_id, cake_id, create_time) VALUES
(1, 1, 1,  '2026-01-01 10:00:00'),
(2, 1, 13, '2026-01-02 12:00:00'),
(3, 1, 15, '2026-01-05 08:00:00'),
(4, 2, 4,  '2026-01-03 09:00:00'),
(5, 2, 6,  '2026-01-04 10:00:00'),
(6, 3, 13, '2026-01-06 11:00:00'),
(7, 3, 10, '2026-01-08 14:00:00');

-- 14. review — 已完成订单的评价
INSERT INTO review (id, order_id, cake_id, user_id, rating, content, reply_content, status, create_time) VALUES
(1, 1, 1, 1, 5, '非常好吃！水果很新鲜，奶油不腻', '感谢您的喜爱，欢迎再次光临！', '显示', '2026-01-02 10:00:00'),
(2, 2, 2, 1, 4, '芒果很甜，千层皮很薄，稍微有点甜', '谢谢建议，我们会调整甜度', '显示', '2026-01-04 09:00:00'),
(3, 5, 4, 2, 5, '提拉米苏非常正宗，咖啡味浓郁', NULL, '显示', '2026-01-06 11:00:00'),
(4, 10, 10, 3, 5, '老人很喜欢，松鹤图案很精美', '谢谢，祝老人健康长寿！', '显示', '2026-01-23 10:00:00'),
(5, 11, 15, 3, 4, '鲜花很漂亮，蛋糕味道也不错，就是价格稍贵', NULL, '显示', '2026-02-02 09:00:00'),
(6, 12, 13, 1, 5, '情人节惊喜成功！星空流心切开很漂亮', '祝您情人节快乐！', '显示', '2026-02-15 08:00:00'),
(7, 1, 1, 1, 3, '第二次买了，这次配送有点慢', '非常抱歉，我们会改进配送服务', '隐藏', '2026-01-03 12:00:00');

-- 15. coupon — 优惠券
INSERT INTO coupon (id, name, type, condition_amount, discount_amount, discount_rate, total_count, remain_count, start_time, end_time) VALUES
(1, '新人满100减20',   '满减', 100.00, 20.00, NULL, 1000, 500, '2026-01-01 00:00:00', '2026-12-31 23:59:59'),
(2, '满200减30',      '满减', 200.00, 30.00, NULL, 500,  200, '2026-01-01 00:00:00', '2026-12-31 23:59:59'),
(3, '满300减50',      '满减', 300.00, 50.00, NULL, 300,  100, '2026-01-01 00:00:00', '2026-12-31 23:59:59'),
(4, '全场9折券',       '折扣', NULL,   NULL,  0.90, 200,  150, '2026-01-01 00:00:00', '2026-06-30 23:59:59'),
(5, '会员9折券',       '折扣', NULL,   NULL,  0.85, 100,  80,  '2026-03-01 00:00:00', '2026-12-31 23:59:59');

-- 16. user_coupon — 用户领券记录
INSERT INTO user_coupon (id, user_id, coupon_id, used, used_time, create_time) VALUES
(1, 1, 1, 1, '2026-01-10 10:00:00', '2026-01-01 09:00:00'),
(2, 1, 2, 1, '2026-01-10 10:00:00', '2026-01-02 10:00:00'),
(3, 1, 3, 0, NULL,              '2026-01-05 11:00:00'),
(4, 2, 1, 0, NULL,              '2026-01-03 08:00:00'),
(5, 2, 2, 1, '2026-01-08 08:00:00', '2026-01-04 09:00:00'),
(6, 3, 1, 0, NULL,              '2026-01-06 12:00:00'),
(7, 3, 4, 0, NULL,              '2026-01-07 14:00:00');

-- 17. activity — 营销活动
INSERT INTO activity (id, title, type, content, start_time, end_time, image_url, status) VALUES
(1, '新年特惠全场8折',        '满减', '新年期间全场蛋糕8折优惠，活动时间1月1日-1月15日', '2026-01-01 00:00:00', '2026-01-15 23:59:59', '/upload/act01.jpg', '停用'),
(2, '夏日冰爽特惠',           '满减', '炎炎夏日，全场蛋糕满299减50，再送冰饮一杯', '2026-06-15 00:00:00', '2026-07-15 23:59:59', '/upload/act02.jpg', '启用'),
(3, '夏日缤纷蛋糕套餐',       '套餐', '芒果千层+冰激凌蛋糕套餐价仅298元（原价356元）', '2026-06-01 00:00:00', '2026-07-31 23:59:59', '/upload/act03.jpg', '启用'),
(4, '毕业季团购',             '团购', '4人成团，12寸大蛋糕仅需298元/个', '2026-06-10 00:00:00', '2026-06-30 23:59:59', '/upload/act04.jpg', '启用');

-- 18. message — 用户留言咨询
INSERT INTO message (id, user_id, title, content, reply_content, status, create_time) VALUES
(1, 1, '关于配送时间', '请问可以指定晚上7点后配送吗？', '可以的，配送时段中有18:00-20:00可选', '已回复', '2026-01-05 10:00:00'),
(2, 2, '定制蛋糕问题', '我想定制一个双层蛋糕，需要提前多久下单？', '建议提前48小时下单，复杂定制请提前72小时', '已回复', '2026-01-08 14:00:00'),
(3, 3, '蛋糕过敏原', '请问蛋糕中是否含有坚果？', NULL, '待回复', '2026-02-01 09:00:00');

-- 19. delivery_config — 配送配置
INSERT INTO delivery_config (id, region, fee, free_amount, is_active) VALUES
(1, '深圳市南山区',         5.00,  100.00, 1),
(2, '深圳市福田区',         8.00,  150.00, 1),
(3, '深圳市宝安区',         10.00, 200.00, 1),
(4, '深圳市龙岗区',         12.00, 200.00, 1),
(5, '广州市天河区',         8.00,  150.00, 1),
(6, '北京市朝阳区',         10.00, 200.00, 1),
(7, '北京市海淀区',         10.00, 200.00, 1);

-- 20. announcement — 公告
INSERT INTO announcement (id, title, content, is_active, create_time) VALUES
(1, '春节期间配送安排', '春节期间1月28日-2月3日配送时间调整为10:00-16:00', 1, '2026-01-20 08:00:00'),
(2, '新品上架：星空流心蛋糕', '全新星空流心蛋糕已上架，欢迎品尝！', 1, '2026-01-15 09:00:00'),
(3, '门店升级通知', '本店将于3月进行门店升级，期间正常营业', 0, '2026-02-20 10:00:00');