1105 lines
46 KiB
JavaScript
1105 lines
46 KiB
JavaScript
const mysql = require('mysql2/promise');
|
||
const bcrypt = require('bcryptjs');
|
||
const { initDB, getDB, dbConfig } = require('../database');
|
||
|
||
/**
|
||
* 数据库初始化函数
|
||
* 创建所有必要的表结构和初始数据
|
||
*/
|
||
async function initDatabase() {
|
||
try {
|
||
// 首先创建数据库（如果不存在）
|
||
const tempConnection = await mysql.createConnection({
|
||
host: dbConfig.host,
|
||
user: dbConfig.user,
|
||
password: dbConfig.password
|
||
});
|
||
|
||
// 创建数据库
|
||
await tempConnection.execute(`CREATE DATABASE IF NOT EXISTS ${dbConfig.database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
|
||
await tempConnection.end();
|
||
|
||
// 初始化数据库连接池
|
||
await initDB();
|
||
console.log('数据库连接池初始化成功');
|
||
|
||
// 创建所有表
|
||
await createTables();
|
||
|
||
// 添加字段（处理表结构升级）
|
||
await addMissingFields();
|
||
|
||
// 创建默认数据
|
||
await createDefaultData();
|
||
|
||
console.log('数据库初始化完成');
|
||
} catch (error) {
|
||
console.error('数据库初始化失败:', error);
|
||
throw error;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 创建所有数据库表
|
||
*/
|
||
async function createTables() {
|
||
// 用户表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
phone VARCHAR(20) UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
role ENUM('user', 'admin') DEFAULT 'user',
|
||
avatar VARCHAR(255),
|
||
points INT DEFAULT 0,
|
||
rongdou INT DEFAULT 0,
|
||
balance DECIMAL(10,2) DEFAULT 0.00,
|
||
real_name VARCHAR(100),
|
||
id_card VARCHAR(18),
|
||
wechat_qr VARCHAR(255),
|
||
alipay_qr VARCHAR(255),
|
||
bank_card VARCHAR(30),
|
||
unionpay_qr VARCHAR(255),
|
||
business_license VARCHAR(500),
|
||
id_card_front VARCHAR(500),
|
||
id_card_back VARCHAR(500),
|
||
audit_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
|
||
audit_note TEXT,
|
||
audited_by INT,
|
||
audited_at TIMESTAMP NULL,
|
||
city VARCHAR(50),
|
||
district_id INT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (audited_by) REFERENCES users(id),
|
||
FOREIGN KEY (district_id) REFERENCES zhejiang_regions(id)
|
||
)
|
||
`);
|
||
|
||
// 商品表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS products (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL,
|
||
description TEXT,
|
||
price INT NOT NULL,
|
||
points_price INT NOT NULL,
|
||
rongdou_price INT NOT NULL DEFAULT 0,
|
||
original_price INT,
|
||
stock INT DEFAULT 0,
|
||
sales INT DEFAULT 0,
|
||
rating DECIMAL(3,2) DEFAULT 5.00,
|
||
category VARCHAR(100),
|
||
image_url VARCHAR(500),
|
||
images JSON,
|
||
videos JSON,
|
||
details TEXT,
|
||
shop_name VARCHAR(255),
|
||
shop_avatar VARCHAR(500),
|
||
payment_methods JSON,
|
||
status ENUM('active', 'inactive') DEFAULT 'active',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
)
|
||
`);
|
||
|
||
// 订单表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS orders (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
order_no VARCHAR(50) UNIQUE NOT NULL,
|
||
total_amount INT NOT NULL,
|
||
total_points INT NOT NULL,
|
||
total_rongdou INT NOT NULL DEFAULT 0,
|
||
status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'pre_order') DEFAULT 'pending',
|
||
address JSON,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
)
|
||
`);
|
||
|
||
// 创建转账记录表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS transfers (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
from_user_id INT NULL,
|
||
to_user_id INT NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
transfer_type ENUM('initial', 'return', 'user_to_user', 'system_to_user', 'user_to_system') DEFAULT 'user_to_user',
|
||
status ENUM('pending', 'confirmed', 'rejected', 'received', 'not_received') DEFAULT 'pending',
|
||
voucher_url VARCHAR(500),
|
||
description TEXT,
|
||
batch_id VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 创建转账确认表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS transfer_confirmations (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
transfer_id INT NOT NULL,
|
||
confirmer_id INT NOT NULL,
|
||
action ENUM('confirm', 'reject') NOT NULL,
|
||
note TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (transfer_id) REFERENCES transfers(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (confirmer_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 订单商品表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS order_items (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
order_id INT NOT NULL,
|
||
product_id INT NOT NULL,
|
||
spec_combination_id INT NULL COMMENT '规格组合ID',
|
||
quantity INT NOT NULL,
|
||
price INT NOT NULL,
|
||
points INT NOT NULL,
|
||
points_price INT NOT NULL DEFAULT 0,
|
||
rongdou INT DEFAULT 0 COMMENT '融豆价格',
|
||
rongdou_price INT NOT NULL DEFAULT 0,
|
||
spec_info JSON COMMENT '规格信息快照',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (order_id) REFERENCES orders(id),
|
||
FOREIGN KEY (product_id) REFERENCES products(id),
|
||
FOREIGN KEY (spec_combination_id) REFERENCES product_spec_combinations(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
|
||
// 积分记录表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS points_history (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
type ENUM('earn', 'spend') NOT NULL,
|
||
amount INT NOT NULL,
|
||
description VARCHAR(255),
|
||
order_id INT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
FOREIGN KEY (order_id) REFERENCES orders(id)
|
||
)
|
||
`);
|
||
|
||
// 融豆记录表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS rongdou_history (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
type ENUM('earn', 'spend') NOT NULL,
|
||
amount INT NOT NULL,
|
||
description VARCHAR(255),
|
||
order_id INT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
FOREIGN KEY (order_id) REFERENCES orders(id)
|
||
)
|
||
`);
|
||
|
||
// 管理员操作日志表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS admin_operation_logs (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
admin_id INT NOT NULL,
|
||
operation_type VARCHAR(50) NOT NULL,
|
||
target_type VARCHAR(50) NOT NULL,
|
||
target_id INT NOT NULL,
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 商品评价表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS product_reviews (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
product_id INT NOT NULL,
|
||
user_id INT NOT NULL,
|
||
order_id INT NOT NULL,
|
||
rating INT NOT NULL,
|
||
comment TEXT,
|
||
images JSON,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (product_id) REFERENCES products(id),
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
FOREIGN KEY (order_id) REFERENCES orders(id)
|
||
)
|
||
`);
|
||
|
||
// 规格名称表（规格维度）
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS spec_names (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL COMMENT '规格名称，如：颜色、尺寸、材质',
|
||
display_name VARCHAR(100) NOT NULL COMMENT '显示名称',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
status ENUM('active', 'inactive') DEFAULT 'active',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY unique_name (name)
|
||
)
|
||
`);
|
||
|
||
// 规格值表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS spec_values (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
spec_name_id INT NOT NULL COMMENT '规格名称ID',
|
||
value VARCHAR(100) NOT NULL COMMENT '规格值，如：红色、XL、棉质',
|
||
display_value VARCHAR(100) NOT NULL COMMENT '显示值',
|
||
color_code VARCHAR(20) COMMENT '颜色代码（仅颜色规格使用）',
|
||
image_url VARCHAR(500) COMMENT '规格图片',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
status ENUM('active', 'inactive') DEFAULT 'active',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (spec_name_id) REFERENCES spec_names(id) ON DELETE CASCADE,
|
||
UNIQUE KEY unique_spec_value (spec_name_id, value)
|
||
)
|
||
`);
|
||
|
||
// 商品规格组合表（笛卡尔积结果）
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS product_spec_combinations (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
product_id INT NOT NULL COMMENT '商品ID',
|
||
combination_key VARCHAR(255) NOT NULL COMMENT '组合键，如：color_1-size_2-material_3',
|
||
spec_values JSON NOT NULL COMMENT '规格值组合，存储spec_value_id数组',
|
||
price_adjustment INT DEFAULT 0 COMMENT '价格调整',
|
||
points_adjustment INT DEFAULT 0 COMMENT '积分调整',
|
||
rongdou_adjustment INT DEFAULT 0 COMMENT '融豆调整',
|
||
stock INT DEFAULT 0 COMMENT '库存',
|
||
sku_code VARCHAR(100) COMMENT 'SKU编码',
|
||
barcode VARCHAR(100) COMMENT '条形码',
|
||
weight DECIMAL(8,3) COMMENT '重量（kg）',
|
||
volume DECIMAL(10,3) COMMENT '体积（cm³）',
|
||
status ENUM('active', 'inactive') DEFAULT 'active',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
|
||
UNIQUE KEY unique_product_combination (product_id, combination_key),
|
||
INDEX idx_product_status (product_id, status),
|
||
INDEX idx_sku_code (sku_code)
|
||
)
|
||
`);
|
||
|
||
// 商品规格关联表（定义商品使用哪些规格维度）
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS product_spec_names (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
product_id INT NOT NULL COMMENT '商品ID',
|
||
spec_name_id INT NOT NULL COMMENT '规格名称ID',
|
||
is_required BOOLEAN DEFAULT TRUE COMMENT '是否必选规格',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (spec_name_id) REFERENCES spec_names(id) ON DELETE CASCADE,
|
||
UNIQUE KEY unique_product_spec_name (product_id, spec_name_id)
|
||
)
|
||
`);
|
||
|
||
// 商品属性表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS product_attributes (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
product_id INT NOT NULL,
|
||
attribute_key VARCHAR(100) NOT NULL,
|
||
attribute_value TEXT NOT NULL,
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 商品收藏表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS product_favorites (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
product_id INT NOT NULL,
|
||
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,
|
||
UNIQUE KEY unique_user_product (user_id, product_id)
|
||
)
|
||
`);
|
||
|
||
// 购物车表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS cart_items (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
product_id INT NOT NULL,
|
||
quantity INT NOT NULL DEFAULT 1,
|
||
spec_combination_id INT NULL COMMENT '规格组合ID',
|
||
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,
|
||
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (spec_combination_id) REFERENCES product_spec_combinations(id) ON DELETE CASCADE,
|
||
UNIQUE KEY unique_user_product_spec (user_id, product_id, spec_combination_id)
|
||
)
|
||
`);
|
||
|
||
// 用户收货地址表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS user_addresses (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
recipient_name VARCHAR(100) NOT NULL,
|
||
phone VARCHAR(20) NOT NULL,
|
||
province_code VARCHAR(20),
|
||
province_name VARCHAR(50) NOT NULL,
|
||
city_code VARCHAR(20),
|
||
city_name VARCHAR(50) NOT NULL,
|
||
district_code VARCHAR(20),
|
||
district_name VARCHAR(50) NOT NULL,
|
||
detailed_address TEXT NOT NULL,
|
||
postal_code VARCHAR(10),
|
||
label_id INT,
|
||
is_default BOOLEAN DEFAULT FALSE,
|
||
deleted_at TIMESTAMP 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,
|
||
FOREIGN KEY (label_id) REFERENCES address_labels(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
|
||
// 地址标签表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS address_labels (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL,
|
||
color VARCHAR(20) DEFAULT '#1890ff',
|
||
user_id INT NULL COMMENT '用户ID，NULL表示系统标签',
|
||
is_system BOOLEAN DEFAULT FALSE,
|
||
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
|
||
)
|
||
`);
|
||
|
||
// 全国省市区表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS china_regions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(20) NOT NULL UNIQUE,
|
||
name VARCHAR(100) NOT NULL,
|
||
parent_code VARCHAR(20),
|
||
level TINYINT NOT NULL COMMENT '1:省 2:市 3:区',
|
||
sort_order INT DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_parent_code (parent_code),
|
||
INDEX idx_level (level)
|
||
)
|
||
`);
|
||
|
||
// 匹配订单表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS matching_orders (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
initiator_id INT NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
status ENUM('pending', 'matching', 'completed', 'cancelled') DEFAULT 'pending',
|
||
cycle_count INT DEFAULT 0,
|
||
max_cycles INT DEFAULT 3,
|
||
matching_type ENUM('small', 'large', 'system_reverse') DEFAULT 'small',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (initiator_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 匹配订单分配表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS order_allocations (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
matching_order_id INT NOT NULL,
|
||
from_user_id INT NOT NULL,
|
||
to_user_id INT NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
cycle_number INT NOT NULL,
|
||
status ENUM('pending', 'confirmed', 'rejected', 'completed') DEFAULT 'pending',
|
||
transfer_id INT,
|
||
outbound_date DATE,
|
||
return_date DATE,
|
||
can_return_after TIMESTAMP,
|
||
confirmed_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (matching_order_id) REFERENCES matching_orders(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (transfer_id) REFERENCES transfers(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
|
||
// 用户匹配池表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS user_matching_pool (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
available_amount DECIMAL(10,2) DEFAULT 0.00,
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
last_matched_at TIMESTAMP 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,
|
||
UNIQUE KEY unique_user (user_id)
|
||
)
|
||
`);
|
||
|
||
// 匹配记录表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS matching_records (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
matching_order_id INT NOT NULL,
|
||
user_id INT NOT NULL,
|
||
action ENUM('join', 'confirm', 'reject', 'complete') NOT NULL,
|
||
amount DECIMAL(10,2),
|
||
note TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (matching_order_id) REFERENCES matching_orders(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
// 创建系统设置表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS system_settings (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
setting_key VARCHAR(100) NOT NULL UNIQUE,
|
||
setting_value TEXT,
|
||
description VARCHAR(255),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
)
|
||
`);
|
||
|
||
// 创建激活码表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS registration_codes (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(10) UNIQUE NOT NULL,
|
||
expires_at TIMESTAMP NOT NULL,
|
||
is_used BOOLEAN DEFAULT FALSE,
|
||
used_at TIMESTAMP NULL,
|
||
created_by_admin_id INT,
|
||
used_by_user_id INT,
|
||
agent_id INT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (created_by_admin_id) REFERENCES users(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (used_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
|
||
FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
|
||
// 创建浙江省区域表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS zhejiang_regions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
city_name VARCHAR(50) NOT NULL,
|
||
district_name VARCHAR(50) NOT NULL,
|
||
region_code VARCHAR(20) UNIQUE NOT NULL,
|
||
is_available BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY unique_region (city_name, district_name)
|
||
)
|
||
`);
|
||
|
||
// 创建区域代理表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS regional_agents (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
region_id INT NOT NULL,
|
||
agent_code VARCHAR(20) UNIQUE NOT NULL,
|
||
status ENUM('pending', 'active', 'suspended', 'terminated') DEFAULT 'pending',
|
||
commission_rate DECIMAL(5,4) DEFAULT 0.1000,
|
||
total_earnings DECIMAL(10,2) DEFAULT 0.00,
|
||
recruited_merchants INT DEFAULT 0,
|
||
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,
|
||
FOREIGN KEY (region_id) REFERENCES zhejiang_regions(id) ON DELETE CASCADE,
|
||
UNIQUE KEY unique_agent_region (user_id, region_id)
|
||
)
|
||
`);
|
||
|
||
// 创建代理商户关系表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS agent_merchants (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
agent_id INT NOT NULL,
|
||
merchant_id INT NOT NULL,
|
||
registration_code_id INT,
|
||
matching_count INT DEFAULT 0,
|
||
commission_earned DECIMAL(10,2) DEFAULT 0.00,
|
||
is_qualified BOOLEAN DEFAULT FALSE,
|
||
qualified_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (agent_id) REFERENCES regional_agents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (registration_code_id) REFERENCES registration_codes(id) ON DELETE SET NULL,
|
||
UNIQUE KEY unique_agent_merchant (agent_id, merchant_id)
|
||
)
|
||
`);
|
||
|
||
// 创建代理佣金记录表
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS agent_commission_records (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
agent_id INT NOT NULL,
|
||
merchant_id INT NOT NULL,
|
||
order_id INT,
|
||
commission_amount DECIMAL(10,2) NOT NULL,
|
||
commission_type ENUM('registration', 'matching') DEFAULT 'matching',
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (agent_id) REFERENCES regional_agents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (merchant_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (order_id) REFERENCES matching_orders(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
}
|
||
|
||
/**
|
||
* 添加缺失的字段（处理数据库升级）
|
||
*/
|
||
async function addMissingFields() {
|
||
// 为现有的matching_orders表添加字段
|
||
const matchingOrderFields = [
|
||
{ name: 'matching_type', sql: 'ALTER TABLE matching_orders ADD COLUMN matching_type ENUM(\'small\', \'large\') DEFAULT \'small\'' },
|
||
{ name: 'is_system_reverse', sql: 'ALTER TABLE matching_orders ADD COLUMN is_system_reverse BOOLEAN DEFAULT FALSE' }
|
||
];
|
||
|
||
for (const field of matchingOrderFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为现有的users表添加字段
|
||
const userFields = [
|
||
{ name: 'balance', sql: 'ALTER TABLE users ADD COLUMN balance DECIMAL(10,2) DEFAULT 0.00' },
|
||
{ name: 'is_system_account', sql: 'ALTER TABLE users ADD COLUMN is_system_account BOOLEAN DEFAULT FALSE' },
|
||
{ name: 'points', sql: 'ALTER TABLE users ADD COLUMN points INT DEFAULT 0' },
|
||
{ name: 'avatar', sql: 'ALTER TABLE users ADD COLUMN avatar VARCHAR(255)' },
|
||
{ name: 'real_name', sql: 'ALTER TABLE users ADD COLUMN real_name VARCHAR(100)' },
|
||
{ name: 'id_card', sql: 'ALTER TABLE users ADD COLUMN id_card VARCHAR(18)' },
|
||
{ name: 'wechat_qr', sql: 'ALTER TABLE users ADD COLUMN wechat_qr VARCHAR(255)' },
|
||
{ name: 'alipay_qr', sql: 'ALTER TABLE users ADD COLUMN alipay_qr VARCHAR(255)' },
|
||
{ name: 'bank_card', sql: 'ALTER TABLE users ADD COLUMN bank_card VARCHAR(30)' },
|
||
{ name: 'unionpay_qr', sql: 'ALTER TABLE users ADD COLUMN unionpay_qr VARCHAR(255)' },
|
||
{ name: 'phone', sql: 'ALTER TABLE users ADD COLUMN phone VARCHAR(20) UNIQUE' },
|
||
{ name: 'completed_withdrawals', sql: 'ALTER TABLE users ADD COLUMN completed_withdrawals INT DEFAULT 0' },
|
||
{ name: 'business_license', sql: 'ALTER TABLE users ADD COLUMN business_license VARCHAR(500)' },
|
||
{ name: 'id_card_front', sql: 'ALTER TABLE users ADD COLUMN id_card_front VARCHAR(500)' },
|
||
{ name: 'id_card_back', sql: 'ALTER TABLE users ADD COLUMN id_card_back VARCHAR(500)' },
|
||
{ name: 'audit_status', sql: 'ALTER TABLE users ADD COLUMN audit_status ENUM(\'pending\', \'approved\', \'rejected\') DEFAULT \'pending\'' },
|
||
{ name: 'audit_note', sql: 'ALTER TABLE users ADD COLUMN audit_note TEXT' },
|
||
{ name: 'audited_by', sql: 'ALTER TABLE users ADD COLUMN audited_by INT' },
|
||
{ name: 'audited_at', sql: 'ALTER TABLE users ADD COLUMN audited_at TIMESTAMP NULL' },
|
||
{ name: 'city', sql: 'ALTER TABLE users ADD COLUMN city VARCHAR(50)' },
|
||
{ name: 'district_id', sql: 'ALTER TABLE users ADD COLUMN district_id INT' }
|
||
];
|
||
|
||
for (const field of userFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加用户表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为现有的products表添加字段
|
||
const productFields = [
|
||
{ name: 'points_price', sql: 'ALTER TABLE products ADD COLUMN points_price INT NOT NULL DEFAULT 0' },
|
||
{ name: 'rongdou_price', sql: 'ALTER TABLE products ADD COLUMN rongdou_price INT NOT NULL DEFAULT 0' },
|
||
{ name: 'image_url', sql: 'ALTER TABLE products ADD COLUMN image_url VARCHAR(500)' },
|
||
{ name: 'images', sql: 'ALTER TABLE products ADD COLUMN images JSON' },
|
||
{ name: 'videos', sql: 'ALTER TABLE products ADD COLUMN videos JSON' },
|
||
{ name: 'details', sql: 'ALTER TABLE products ADD COLUMN details TEXT' },
|
||
{ name: 'shop_name', sql: 'ALTER TABLE products ADD COLUMN shop_name VARCHAR(255)' },
|
||
{ name: 'shop_avatar', sql: 'ALTER TABLE products ADD COLUMN shop_avatar VARCHAR(500)' },
|
||
{ name: 'payment_methods', sql: 'ALTER TABLE products ADD COLUMN payment_methods JSON' }
|
||
];
|
||
|
||
for (const field of productFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加商品表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为现有的transfers表添加字段
|
||
const transferFields = [
|
||
{ name: 'is_overdue', sql: 'ALTER TABLE transfers ADD COLUMN is_overdue BOOLEAN DEFAULT FALSE' },
|
||
{ name: 'is_bad_debt', sql: 'ALTER TABLE transfers ADD COLUMN is_bad_debt BOOLEAN DEFAULT FALSE' },
|
||
{ name: 'confirmed_at', sql: 'ALTER TABLE transfers ADD COLUMN confirmed_at TIMESTAMP NULL' },
|
||
{ name: 'deadline_at', sql: 'ALTER TABLE transfers ADD COLUMN deadline_at TIMESTAMP NULL' },
|
||
{ name: 'admin_note', sql: 'ALTER TABLE transfers ADD COLUMN admin_note TEXT' },
|
||
{ name: 'admin_modified_at', sql: 'ALTER TABLE transfers ADD COLUMN admin_modified_at TIMESTAMP NULL' },
|
||
{ name: 'admin_modified_by', sql: 'ALTER TABLE transfers ADD COLUMN admin_modified_by INT' }
|
||
];
|
||
|
||
for (const field of transferFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加转账表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 修改transfers表的字段类型
|
||
try {
|
||
await getDB().execute(`
|
||
ALTER TABLE transfers
|
||
MODIFY COLUMN status ENUM('pending', 'confirmed', 'rejected', 'received', 'not_received', 'cancelled') DEFAULT 'pending'
|
||
`);
|
||
} catch (error) {
|
||
console.log('修改transfers状态字段错误:', error.message);
|
||
}
|
||
|
||
try {
|
||
await getDB().execute(`
|
||
ALTER TABLE transfers
|
||
MODIFY COLUMN from_user_id INT NULL
|
||
`);
|
||
} catch (error) {
|
||
console.log('修改transfers from_user_id字段错误:', error.message);
|
||
}
|
||
|
||
try {
|
||
await getDB().execute(`
|
||
ALTER TABLE transfers
|
||
MODIFY COLUMN transfer_type ENUM('initial', 'return', 'user_to_user', 'system_to_user', 'user_to_system') DEFAULT 'user_to_user'
|
||
`);
|
||
} catch (error) {
|
||
console.log('修改transfers transfer_type字段错误:', error.message);
|
||
}
|
||
|
||
// 为现有的order_allocations表添加字段
|
||
const allocationFields = [
|
||
{ name: 'confirmed_at', sql: 'ALTER TABLE order_allocations ADD COLUMN confirmed_at TIMESTAMP NULL' },
|
||
{ name: 'outbound_date', sql: 'ALTER TABLE order_allocations ADD COLUMN outbound_date DATE' },
|
||
{ name: 'return_date', sql: 'ALTER TABLE order_allocations ADD COLUMN return_date DATE' },
|
||
{ name: 'can_return_after', sql: 'ALTER TABLE order_allocations ADD COLUMN can_return_after TIMESTAMP' }
|
||
];
|
||
|
||
for (const field of allocationFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加分配表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为现有的regional_agents表添加字段
|
||
const agentFields = [
|
||
{ name: 'approved_at', sql: 'ALTER TABLE regional_agents ADD COLUMN approved_at TIMESTAMP NULL' },
|
||
{ name: 'approved_by_admin_id', sql: 'ALTER TABLE regional_agents ADD COLUMN approved_by_admin_id INT' },
|
||
// 提现相关字段
|
||
{ name: 'withdrawn_amount', sql: 'ALTER TABLE regional_agents ADD COLUMN withdrawn_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT "已提现金额"' },
|
||
{ name: 'pending_withdrawal', sql: 'ALTER TABLE regional_agents ADD COLUMN pending_withdrawal DECIMAL(10,2) DEFAULT 0.00 COMMENT "待审核提现金额"' },
|
||
{ name: 'payment_type', sql: 'ALTER TABLE regional_agents ADD COLUMN payment_type ENUM("bank", "wechat", "alipay", "unionpay") DEFAULT "bank" COMMENT "收款方式类型"' },
|
||
{ name: 'bank_name', sql: 'ALTER TABLE regional_agents ADD COLUMN bank_name VARCHAR(100) COMMENT "银行名称"' },
|
||
{ name: 'account_number', sql: 'ALTER TABLE regional_agents ADD COLUMN account_number VARCHAR(50) COMMENT "账号/银行账号"' },
|
||
{ name: 'account_holder', sql: 'ALTER TABLE regional_agents ADD COLUMN account_holder VARCHAR(100) COMMENT "持有人姓名"' },
|
||
{ name: 'qr_code_url', sql: 'ALTER TABLE regional_agents ADD COLUMN qr_code_url VARCHAR(255) COMMENT "收款码图片URL"' },
|
||
{ name: 'bank_account', sql: 'ALTER TABLE regional_agents ADD COLUMN bank_account VARCHAR(50) COMMENT "银行账号（兼容旧版本）"' }
|
||
];
|
||
|
||
for (const field of agentFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加代理表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为现有的registration_codes表添加字段
|
||
const registrationCodeFields = [
|
||
{ name: 'agent_id', sql: 'ALTER TABLE registration_codes ADD COLUMN agent_id INT NULL' }
|
||
];
|
||
|
||
for (const field of registrationCodeFields) {
|
||
try {
|
||
await getDB().execute(field.sql);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate column name')) {
|
||
console.log(`添加激活码表${field.name}字段错误:`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 为registration_codes表的agent_id字段添加外键约束
|
||
try {
|
||
await getDB().execute(`
|
||
ALTER TABLE registration_codes
|
||
ADD CONSTRAINT fk_registration_codes_agent_id
|
||
FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL
|
||
`);
|
||
} catch (error) {
|
||
if (!error.message.includes('Duplicate foreign key constraint name')) {
|
||
console.log('添加激活码表agent_id外键约束错误:', error.message);
|
||
}
|
||
}
|
||
|
||
// 注意：MySQL不支持带WHERE条件的唯一索引
|
||
// 区域激活代理的唯一性通过应用层验证来确保
|
||
// 每个区域只能有一个激活状态的代理，这在代理申请、审核和启用时都会进行验证
|
||
|
||
// 创建代理提现记录表
|
||
try {
|
||
await getDB().execute(`
|
||
CREATE TABLE IF NOT EXISTS agent_withdrawals (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
agent_id INT NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
payment_type ENUM('bank', 'wechat', 'alipay', 'unionpay') DEFAULT 'bank' COMMENT '收款方式类型',
|
||
bank_name VARCHAR(100) COMMENT '银行名称',
|
||
account_number VARCHAR(50) COMMENT '账号/银行账号',
|
||
account_holder VARCHAR(100) COMMENT '持有人姓名',
|
||
qr_code_url VARCHAR(255) COMMENT '收款码图片URL',
|
||
status ENUM('pending', 'approved', 'rejected', 'completed') DEFAULT 'pending',
|
||
apply_note TEXT,
|
||
admin_note TEXT,
|
||
processed_by INT NULL,
|
||
processed_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
bank_account VARCHAR(50) COMMENT '银行账号（兼容旧版本）',
|
||
FOREIGN KEY (agent_id) REFERENCES regional_agents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL
|
||
)
|
||
`);
|
||
console.log('代理提现记录表创建成功');
|
||
} catch (error) {
|
||
if (!error.message.includes('Table') || !error.message.includes('already exists')) {
|
||
console.log('创建代理提现记录表错误:', error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 创建默认数据
|
||
*/
|
||
async function createDefaultData() {
|
||
// 创建默认管理员账号
|
||
const defaultPassword = await bcrypt.hash('admin123', 10);
|
||
|
||
try {
|
||
await getDB().execute(`
|
||
INSERT IGNORE INTO users (username, phone, password, role)
|
||
VALUES ('admin', '13800000000', ?, 'admin')
|
||
`, [defaultPassword]);
|
||
console.log('默认管理员账号已创建: 用户名: admin, 密码: admin123');
|
||
} catch (error) {
|
||
console.log('默认管理员账号已存在或创建失败:', error.message);
|
||
}
|
||
|
||
// 创建多个系统公户账号
|
||
const publicPassword = await bcrypt.hash('public123', 10);
|
||
const systemAccounts = [
|
||
{ username: 'merchant_001', phone: '13800000001', real_name: '优选商户' },
|
||
{ username: 'merchant_002', phone: '13800000002', real_name: '品质商家' },
|
||
{ username: 'merchant_003', phone: '13800000003', real_name: '信誉商户' },
|
||
{ username: 'merchant_004', phone: '13800000004', real_name: '金牌商家' },
|
||
{ username: 'merchant_005', phone: '13800000005', real_name: '钻石商户' }
|
||
];
|
||
|
||
for (const account of systemAccounts) {
|
||
try {
|
||
const [result] = await getDB().execute(`
|
||
INSERT IGNORE INTO users (username, phone, password, role, real_name, is_system_account)
|
||
VALUES (?, ?, ?, 'user', ?, TRUE)
|
||
`, [account.username, account.phone, publicPassword, account.real_name]);
|
||
|
||
if (result.affectedRows > 0) {
|
||
// 为新创建的系统账户设置初始余额
|
||
await getDB().execute(`
|
||
UPDATE users SET balance = 0.00 WHERE id = ?
|
||
`, [result.insertId]);
|
||
console.log(`系统账户已创建: ${account.real_name} (${account.username})`);
|
||
} else {
|
||
// 确保现有系统账户的is_system_account字段正确设置
|
||
await getDB().execute(`
|
||
UPDATE users SET is_system_account = TRUE WHERE username = ?
|
||
`, [account.username]);
|
||
}
|
||
} catch (error) {
|
||
console.log(`系统账户${account.username}已存在或创建失败:`, error.message);
|
||
}
|
||
}
|
||
|
||
// 初始化浙江省区域数据
|
||
await initializeZhejiangRegions();
|
||
|
||
// 初始化默认地址标签
|
||
await initializeDefaultAddressLabels();
|
||
|
||
// 初始化全国省市区数据
|
||
await initializeChinaRegions();
|
||
}
|
||
|
||
/**
|
||
* 初始化浙江省区域数据
|
||
*/
|
||
async function initializeZhejiangRegions() {
|
||
const zhejiangRegions = [
|
||
// 杭州市
|
||
{ city: '杭州市', district: '上城区', code: 'HZ_SC' },
|
||
{ city: '杭州市', district: '拱墅区', code: 'HZ_GS' },
|
||
{ city: '杭州市', district: '西湖区', code: 'HZ_XH' },
|
||
{ city: '杭州市', district: '滨江区', code: 'HZ_BJ' },
|
||
{ city: '杭州市', district: '萧山区', code: 'HZ_XS' },
|
||
{ city: '杭州市', district: '余杭区', code: 'HZ_YH' },
|
||
{ city: '杭州市', district: '临平区', code: 'HZ_LP' },
|
||
{ city: '杭州市', district: '钱塘区', code: 'HZ_QT' },
|
||
{ city: '杭州市', district: '富阳区', code: 'HZ_FY' },
|
||
{ city: '杭州市', district: '临安区', code: 'HZ_LA' },
|
||
{ city: '杭州市', district: '桐庐县', code: 'HZ_TL' },
|
||
{ city: '杭州市', district: '淳安县', code: 'HZ_CA' },
|
||
{ city: '杭州市', district: '建德市', code: 'HZ_JD' },
|
||
// 宁波市
|
||
{ city: '宁波市', district: '海曙区', code: 'NB_HS' },
|
||
{ city: '宁波市', district: '江北区', code: 'NB_JB' },
|
||
{ city: '宁波市', district: '北仑区', code: 'NB_BL' },
|
||
{ city: '宁波市', district: '镇海区', code: 'NB_ZH' },
|
||
{ city: '宁波市', district: '鄞州区', code: 'NB_YZ' },
|
||
{ city: '宁波市', district: '奉化区', code: 'NB_FH' },
|
||
{ city: '宁波市', district: '象山县', code: 'NB_XS' },
|
||
{ city: '宁波市', district: '宁海县', code: 'NB_NH' },
|
||
{ city: '宁波市', district: '余姚市', code: 'NB_YY' },
|
||
{ city: '宁波市', district: '慈溪市', code: 'NB_CX' },
|
||
// 温州市
|
||
{ city: '温州市', district: '鹿城区', code: 'WZ_LC' },
|
||
{ city: '温州市', district: '龙湾区', code: 'WZ_LW' },
|
||
{ city: '温州市', district: '瓯海区', code: 'WZ_OH' },
|
||
{ city: '温州市', district: '洞头区', code: 'WZ_DT' },
|
||
{ city: '温州市', district: '永嘉县', code: 'WZ_YJ' },
|
||
{ city: '温州市', district: '平阳县', code: 'WZ_PY' },
|
||
{ city: '温州市', district: '苍南县', code: 'WZ_CN' },
|
||
{ city: '温州市', district: '文成县', code: 'WZ_WC' },
|
||
{ city: '温州市', district: '泰顺县', code: 'WZ_TS' },
|
||
{ city: '温州市', district: '瑞安市', code: 'WZ_RA' },
|
||
{ city: '温州市', district: '乐清市', code: 'WZ_LQ' },
|
||
// 嘉兴市
|
||
{ city: '嘉兴市', district: '南湖区', code: 'JX_NH' },
|
||
{ city: '嘉兴市', district: '秀洲区', code: 'JX_XZ' },
|
||
{ city: '嘉兴市', district: '嘉善县', code: 'JX_JS' },
|
||
{ city: '嘉兴市', district: '海盐县', code: 'JX_HY' },
|
||
{ city: '嘉兴市', district: '海宁市', code: 'JX_HN' },
|
||
{ city: '嘉兴市', district: '平湖市', code: 'JX_PH' },
|
||
{ city: '嘉兴市', district: '桐乡市', code: 'JX_TX' },
|
||
// 湖州市
|
||
{ city: '湖州市', district: '吴兴区', code: 'HuZ_WX' },
|
||
{ city: '湖州市', district: '南浔区', code: 'HuZ_NX' },
|
||
{ city: '湖州市', district: '德清县', code: 'HuZ_DQ' },
|
||
{ city: '湖州市', district: '长兴县', code: 'HuZ_CX' },
|
||
{ city: '湖州市', district: '安吉县', code: 'HuZ_AJ' },
|
||
// 绍兴市
|
||
{ city: '绍兴市', district: '越城区', code: 'SX_YC' },
|
||
{ city: '绍兴市', district: '柯桥区', code: 'SX_KQ' },
|
||
{ city: '绍兴市', district: '上虞区', code: 'SX_SY' },
|
||
{ city: '绍兴市', district: '新昌县', code: 'SX_XC' },
|
||
{ city: '绍兴市', district: '诸暨市', code: 'SX_ZJ' },
|
||
{ city: '绍兴市', district: '嵊州市', code: 'SX_SZ' },
|
||
// 金华市
|
||
{ city: '金华市', district: '婺城区', code: 'JH_WC' },
|
||
{ city: '金华市', district: '金东区', code: 'JH_JD' },
|
||
{ city: '金华市', district: '武义县', code: 'JH_WY' },
|
||
{ city: '金华市', district: '浦江县', code: 'JH_PJ' },
|
||
{ city: '金华市', district: '磐安县', code: 'JH_PA' },
|
||
{ city: '金华市', district: '兰溪市', code: 'JH_LX' },
|
||
{ city: '金华市', district: '义乌市', code: 'JH_YW' },
|
||
{ city: '金华市', district: '东阳市', code: 'JH_DY' },
|
||
{ city: '金华市', district: '永康市', code: 'JH_YK' },
|
||
// 衢州市
|
||
{ city: '衢州市', district: '柯城区', code: 'QZ_KC' },
|
||
{ city: '衢州市', district: '衢江区', code: 'QZ_QJ' },
|
||
{ city: '衢州市', district: '常山县', code: 'QZ_CS' },
|
||
{ city: '衢州市', district: '开化县', code: 'QZ_KH' },
|
||
{ city: '衢州市', district: '龙游县', code: 'QZ_LY' },
|
||
{ city: '衢州市', district: '江山市', code: 'QZ_JS' },
|
||
// 舟山市
|
||
{ city: '舟山市', district: '定海区', code: 'ZS_DH' },
|
||
{ city: '舟山市', district: '普陀区', code: 'ZS_PT' },
|
||
{ city: '舟山市', district: '岱山县', code: 'ZS_DS' },
|
||
{ city: '舟山市', district: '嵊泗县', code: 'ZS_SS' },
|
||
// 台州市
|
||
{ city: '台州市', district: '椒江区', code: 'TZ_JJ' },
|
||
{ city: '台州市', district: '黄岩区', code: 'TZ_HY' },
|
||
{ city: '台州市', district: '路桥区', code: 'TZ_LQ' },
|
||
{ city: '台州市', district: '三门县', code: 'TZ_SM' },
|
||
{ city: '台州市', district: '天台县', code: 'TZ_TT' },
|
||
{ city: '台州市', district: '仙居县', code: 'TZ_XJ' },
|
||
{ city: '台州市', district: '温岭市', code: 'TZ_WL' },
|
||
{ city: '台州市', district: '临海市', code: 'TZ_LH' },
|
||
{ city: '台州市', district: '玉环市', code: 'TZ_YH' },
|
||
// 丽水市
|
||
{ city: '丽水市', district: '莲都区', code: 'LS_LD' },
|
||
{ city: '丽水市', district: '青田县', code: 'LS_QT' },
|
||
{ city: '丽水市', district: '缙云县', code: 'LS_JY' },
|
||
{ city: '丽水市', district: '遂昌县', code: 'LS_SC' },
|
||
{ city: '丽水市', district: '松阳县', code: 'LS_SY' },
|
||
{ city: '丽水市', district: '云和县', code: 'LS_YH' },
|
||
{ city: '丽水市', district: '庆元县', code: 'LS_QY' },
|
||
{ city: '丽水市', district: '景宁县', code: 'LS_JN' },
|
||
{ city: '丽水市', district: '龙泉市', code: 'LS_LQ' }
|
||
];
|
||
|
||
// 批量插入区域数据
|
||
for (const region of zhejiangRegions) {
|
||
try {
|
||
await getDB().execute(
|
||
'INSERT IGNORE INTO zhejiang_regions (city_name, district_name, region_code) VALUES (?, ?, ?)',
|
||
[region.city, region.district, region.code]
|
||
);
|
||
} catch (error) {
|
||
console.log(`插入区域数据失败: ${region.city} ${region.district}`, error.message);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 初始化默认地址标签
|
||
*/
|
||
async function initializeDefaultAddressLabels() {
|
||
const defaultLabels = [
|
||
{ name: '家', color: '#52c41a' },
|
||
{ name: '公司', color: '#1890ff' },
|
||
{ name: '学校', color: '#722ed1' }
|
||
];
|
||
|
||
for (const label of defaultLabels) {
|
||
try {
|
||
await getDB().execute(`
|
||
INSERT IGNORE INTO address_labels (name, color, user_id, is_system)
|
||
VALUES (?, ?, NULL, TRUE)
|
||
`, [label.name, label.color]);
|
||
} catch (error) {
|
||
console.log(`默认标签${label.name}创建失败:`, error.message);
|
||
}
|
||
}
|
||
console.log('默认地址标签初始化完成');
|
||
}
|
||
|
||
/**
|
||
* 初始化全国省市区数据
|
||
*/
|
||
async function initializeChinaRegions() {
|
||
const regions = [
|
||
// 省级
|
||
{ code: '110000', name: '北京市', parent_code: null, level: 1 },
|
||
{ code: '120000', name: '天津市', parent_code: null, level: 1 },
|
||
{ code: '130000', name: '河北省', parent_code: null, level: 1 },
|
||
{ code: '140000', name: '山西省', parent_code: null, level: 1 },
|
||
{ code: '150000', name: '内蒙古自治区', parent_code: null, level: 1 },
|
||
{ code: '210000', name: '辽宁省', parent_code: null, level: 1 },
|
||
{ code: '220000', name: '吉林省', parent_code: null, level: 1 },
|
||
{ code: '230000', name: '黑龙江省', parent_code: null, level: 1 },
|
||
{ code: '310000', name: '上海市', parent_code: null, level: 1 },
|
||
{ code: '320000', name: '江苏省', parent_code: null, level: 1 },
|
||
{ code: '330000', name: '浙江省', parent_code: null, level: 1 },
|
||
{ code: '340000', name: '安徽省', parent_code: null, level: 1 },
|
||
{ code: '350000', name: '福建省', parent_code: null, level: 1 },
|
||
{ code: '360000', name: '江西省', parent_code: null, level: 1 },
|
||
{ code: '370000', name: '山东省', parent_code: null, level: 1 },
|
||
{ code: '410000', name: '河南省', parent_code: null, level: 1 },
|
||
{ code: '420000', name: '湖北省', parent_code: null, level: 1 },
|
||
{ code: '430000', name: '湖南省', parent_code: null, level: 1 },
|
||
{ code: '440000', name: '广东省', parent_code: null, level: 1 },
|
||
{ code: '450000', name: '广西壮族自治区', parent_code: null, level: 1 },
|
||
{ code: '460000', name: '海南省', parent_code: null, level: 1 },
|
||
{ code: '500000', name: '重庆市', parent_code: null, level: 1 },
|
||
{ code: '510000', name: '四川省', parent_code: null, level: 1 },
|
||
{ code: '520000', name: '贵州省', parent_code: null, level: 1 },
|
||
{ code: '530000', name: '云南省', parent_code: null, level: 1 },
|
||
{ code: '540000', name: '西藏自治区', parent_code: null, level: 1 },
|
||
{ code: '610000', name: '陕西省', parent_code: null, level: 1 },
|
||
{ code: '620000', name: '甘肃省', parent_code: null, level: 1 },
|
||
{ code: '630000', name: '青海省', parent_code: null, level: 1 },
|
||
{ code: '640000', name: '宁夏回族自治区', parent_code: null, level: 1 },
|
||
{ code: '650000', name: '新疆维吾尔自治区', parent_code: null, level: 1 },
|
||
|
||
// 浙江省市级
|
||
{ code: '330100', name: '杭州市', parent_code: '330000', level: 2 },
|
||
{ code: '330200', name: '宁波市', parent_code: '330000', level: 2 },
|
||
{ code: '330300', name: '温州市', parent_code: '330000', level: 2 },
|
||
{ code: '330400', name: '嘉兴市', parent_code: '330000', level: 2 },
|
||
{ code: '330500', name: '湖州市', parent_code: '330000', level: 2 },
|
||
{ code: '330600', name: '绍兴市', parent_code: '330000', level: 2 },
|
||
{ code: '330700', name: '金华市', parent_code: '330000', level: 2 },
|
||
{ code: '330800', name: '衢州市', parent_code: '330000', level: 2 },
|
||
{ code: '330900', name: '舟山市', parent_code: '330000', level: 2 },
|
||
{ code: '331000', name: '台州市', parent_code: '330000', level: 2 },
|
||
{ code: '331100', name: '丽水市', parent_code: '330000', level: 2 },
|
||
|
||
// 杭州市区级
|
||
{ code: '330102', name: '上城区', parent_code: '330100', level: 3 },
|
||
{ code: '330105', name: '拱墅区', parent_code: '330100', level: 3 },
|
||
{ code: '330106', name: '西湖区', parent_code: '330100', level: 3 },
|
||
{ code: '330108', name: '滨江区', parent_code: '330100', level: 3 },
|
||
{ code: '330109', name: '萧山区', parent_code: '330100', level: 3 },
|
||
{ code: '330110', name: '余杭区', parent_code: '330100', level: 3 },
|
||
{ code: '330111', name: '富阳区', parent_code: '330100', level: 3 },
|
||
{ code: '330112', name: '临安区', parent_code: '330100', level: 3 },
|
||
{ code: '330113', name: '临平区', parent_code: '330100', level: 3 },
|
||
{ code: '330114', name: '钱塘区', parent_code: '330100', level: 3 },
|
||
{ code: '330122', name: '桐庐县', parent_code: '330100', level: 3 },
|
||
{ code: '330127', name: '淳安县', parent_code: '330100', level: 3 },
|
||
{ code: '330182', name: '建德市', parent_code: '330100', level: 3 }
|
||
];
|
||
|
||
for (const region of regions) {
|
||
try {
|
||
await getDB().execute(`
|
||
INSERT IGNORE INTO china_regions (code, name, parent_code, level)
|
||
VALUES (?, ?, ?, ?)
|
||
`, [region.code, region.name, region.parent_code, region.level]);
|
||
} catch (error) {
|
||
console.log(`区域${region.name}创建失败:`, error.message);
|
||
}
|
||
}
|
||
console.log('全国省市区数据初始化完成');
|
||
}
|
||
|
||
module.exports = {
|
||
initDatabase,
|
||
createTables,
|
||
addMissingFields,
|
||
createDefaultData,
|
||
initializeZhejiangRegions,
|
||
initializeDefaultAddressLabels,
|
||
initializeChinaRegions
|
||
}; |