JewelryMall/server/migrations/001_init.sql

-- 用户表
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid VARCHAR(20) DEFAULT NULL,
  open_id VARCHAR(128) NOT NULL UNIQUE,
  nickname VARCHAR(64) NOT NULL DEFAULT '',
  avatar VARCHAR(512) NOT NULL DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品分类表
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  icon VARCHAR(512) DEFAULT NULL,
  parent_id INT DEFAULT NULL,
  sort INT NOT NULL DEFAULT 0,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品表
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  base_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  style_no VARCHAR(64) NOT NULL DEFAULT '',
  stock INT NOT NULL DEFAULT 0,
  total_stock INT NOT NULL DEFAULT 0,
  loss DECIMAL(8,4) NOT NULL DEFAULT 0,
  labor_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  category_id VARCHAR(255) DEFAULT NULL,
  banner_images JSON DEFAULT NULL,
  banner_video JSON DEFAULT NULL,
  detail_images JSON DEFAULT NULL,
  thumb VARCHAR(512) DEFAULT NULL,
  side_stone VARCHAR(64) DEFAULT '',
  style VARCHAR(64) DEFAULT '',
  setting VARCHAR(64) DEFAULT '',
  status ENUM('on','off') NOT NULL DEFAULT 'on',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 详细参数配置表
CREATE TABLE IF NOT EXISTS detail_parameter_configs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  fineness JSON DEFAULT NULL,
  main_stone JSON DEFAULT NULL,
  ring_size JSON DEFAULT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 规格数据表
CREATE TABLE IF NOT EXISTS spec_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  model_name VARCHAR(128) NOT NULL DEFAULT '',
  barcode VARCHAR(64) DEFAULT NULL,
  fineness VARCHAR(64) NOT NULL DEFAULT '',
  main_stone VARCHAR(64) NOT NULL DEFAULT '',
  sub_stone VARCHAR(100) DEFAULT '',
  ring_size VARCHAR(32) NOT NULL DEFAULT '',
  gold_total_weight DECIMAL(10,4) NOT NULL DEFAULT 0,
  gold_net_weight DECIMAL(10,4) NOT NULL DEFAULT 0,
  loss DECIMAL(8,4) NOT NULL DEFAULT 0,
  gold_loss DECIMAL(10,4) NOT NULL DEFAULT 0,
  gold_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  gold_value DECIMAL(12,2) NOT NULL DEFAULT 0,
  main_stone_count INT NOT NULL DEFAULT 0,
  main_stone_weight DECIMAL(10,4) NOT NULL DEFAULT 0,
  main_stone_unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  main_stone_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  side_stone_count INT NOT NULL DEFAULT 0,
  side_stone_weight DECIMAL(10,4) NOT NULL DEFAULT 0,
  side_stone_unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  side_stone_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  accessory_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  processing_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  setting_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_labor_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  UNIQUE INDEX idx_barcode (barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 购物车项表
CREATE TABLE IF NOT EXISTS cart_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  spec_data_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (spec_data_id) REFERENCES spec_data(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL UNIQUE,
  user_id INT NOT NULL,
  status ENUM('pending','paid','shipped','received','cancelled') NOT NULL DEFAULT 'pending',
  total_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  receiver_name VARCHAR(64) NOT NULL DEFAULT '',
  receiver_phone VARCHAR(20) NOT NULL DEFAULT '',
  receiver_address VARCHAR(512) NOT NULL DEFAULT '',
  payment_time DATETIME DEFAULT NULL,
  payment_proof VARCHAR(512) DEFAULT NULL,
  shipping_company VARCHAR(64) DEFAULT NULL,
  shipping_no VARCHAR(64) DEFAULT NULL,
  received_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单商品项表
CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  spec_data_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (spec_data_id) REFERENCES spec_data(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 版房信息表
CREATE TABLE IF NOT EXISTS mold_infos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  style_no VARCHAR(64) DEFAULT NULL,
  barcode_no VARCHAR(64) DEFAULT NULL,
  style VARCHAR(64) DEFAULT NULL,
  images JSON DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 收货地址表
CREATE TABLE IF NOT EXISTS addresses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(64) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  province VARCHAR(32) NOT NULL DEFAULT '',
  city VARCHAR(32) NOT NULL DEFAULT '',
  district VARCHAR(32) NOT NULL DEFAULT '',
  detail VARCHAR(256) NOT NULL DEFAULT '',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 分类筛选配置表
CREATE TABLE IF NOT EXISTS category_filters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  filter_name VARCHAR(64) NOT NULL,
  filter_key VARCHAR(64) NOT NULL,
  options JSON DEFAULT NULL,
  sort INT NOT NULL DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_configs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  config_key VARCHAR(64) NOT NULL UNIQUE,
  config_value TEXT DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 金价记录表
CREATE TABLE IF NOT EXISTS gold_price_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  price DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;