require('dotenv').config(); const mysql = require('mysql2/promise'); const dbConfig = { host: process.env.DB_HOST, port: process.env.DB_PORT, user: process.env.DB_USER, password: process.env.DB_PASSWORD, }; async function initDatabase() { let connection; try { connection = await mysql.createConnection(dbConfig); console.log('数据库连接成功'); // 创建数据库 await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`); console.log(`数据库 ${process.env.DB_NAME} 创建成功`); await connection.query(`USE ${process.env.DB_NAME}`); // 用户表 await connection.query(` CREATE TABLE IF NOT EXISTS users ( id VARCHAR(36) PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, real_name VARCHAR(50) NOT NULL, email VARCHAR(100), phone VARCHAR(20), role ENUM('sales', 'sales_manager', 'sales_director', 'admin') NOT NULL DEFAULT 'sales', department VARCHAR(50), team VARCHAR(50), status ENUM('active', 'inactive') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_role (role), INDEX idx_department (department), INDEX idx_team (team) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('用户表创建成功'); // 客户报备表 await connection.query(` CREATE TABLE IF NOT EXISTS customers ( id VARCHAR(36) PRIMARY KEY, customer_name VARCHAR(200) NOT NULL, industry VARCHAR(100), region VARCHAR(100), contact_person VARCHAR(50), contact_phone VARCHAR(20), demand_description TEXT, source VARCHAR(50), sales_owner VARCHAR(36) NOT NULL, report_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, protected_end_date TIMESTAMP, status ENUM('following', 'won', 'lost', 'released') DEFAULT 'following', last_followup TIMESTAMP, release_reason VARCHAR(200), is_in_pool BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (sales_owner) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_customer_name (customer_name), INDEX idx_sales_owner (sales_owner), INDEX idx_status (status), INDEX idx_protected_end_date (protected_end_date), INDEX idx_is_in_pool (is_in_pool), FULLTEXT idx_ft_customer_name (customer_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('客户报备表创建成功'); // 跟进记录表 await connection.query(` CREATE TABLE IF NOT EXISTS followup_records ( id VARCHAR(36) PRIMARY KEY, customer_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, followup_type ENUM('call', 'visit', 'email', 'wechat', 'other') DEFAULT 'call', content TEXT, next_plan TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_customer_id (customer_id), INDEX idx_user_id (user_id), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('跟进记录表创建成功'); // 公海池领取记录表 await connection.query(` CREATE TABLE IF NOT EXISTS pool_claim_records ( id VARCHAR(36) PRIMARY KEY, customer_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, claim_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_customer_id (customer_id), INDEX idx_user_id (user_id), INDEX idx_claim_time (claim_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('公海池领取记录表创建成功'); // 审批流程表 await connection.query(` CREATE TABLE IF NOT EXISTS approvals ( id VARCHAR(36) PRIMARY KEY, type ENUM('extension', 'force_release', 'collaboration') NOT NULL, applicant_id VARCHAR(36) NOT NULL, approver_id VARCHAR(36), customer_id VARCHAR(36), reason TEXT, extension_days INT, status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending', result_comment TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, processed_at TIMESTAMP, FOREIGN KEY (applicant_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (approver_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, INDEX idx_applicant_id (applicant_id), INDEX idx_approver_id (approver_id), INDEX idx_status (status), INDEX idx_type (type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('审批流程表创建成功'); // 操作日志表 await connection.query(` CREATE TABLE IF NOT EXISTS operation_logs ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36), action VARCHAR(50) NOT NULL, target_type VARCHAR(50), target_id VARCHAR(36), details TEXT, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_user_id (user_id), INDEX idx_action (action), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('操作日志表创建成功'); // 附件表 await connection.query(` CREATE TABLE IF NOT EXISTS attachments ( id VARCHAR(36) PRIMARY KEY, customer_id VARCHAR(36) NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, file_path VARCHAR(500) NOT NULL, file_size INT, mime_type VARCHAR(100), uploaded_by VARCHAR(36) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_customer_id (customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); console.log('附件表创建成功'); // 插入默认管理员账号 (密码: admin123) const bcrypt = require('bcryptjs'); const crypto = require('crypto'); const adminPassword = await bcrypt.hash('admin123', 10); const adminId = crypto.randomUUID(); await connection.query(` INSERT INTO users (id, username, password, real_name, role, department) VALUES (?, 'admin', ?, '系统管理员', 'admin', '管理部') ON DUPLICATE KEY UPDATE username=username `, [adminId, adminPassword]); console.log('默认管理员账号创建成功 (用户名: admin, 密码: admin123)'); console.log('\n数据库初始化完成！'); } catch (error) { console.error('数据库初始化失败:', error); process.exit(1); } finally { if (connection) { await connection.end(); } } } initDatabase();