CodeBuddy/scripts/initDatabase.js

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',
        last_login TIMESTAMP NULL,
        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();