AnimationManager/server/sql.js

// SQLite 数据库操作模块

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');
const { promisify } = require('util');

const mkdir = promisify(fs.mkdir);
const access = promisify(fs.access);

class DatabaseManager {
  constructor() {
    this.dbPath = path.join(__dirname, 'data.db');
    this.db = null;
  }

  // 初始化数据库连接
  async init() {
    try {
      this.db = new Database(this.dbPath);
      console.log('[SQL] 数据库连接成功');
      this.createTables();
    } catch (err) {
      console.error('[SQL] 数据库连接失败:', err);
      throw err;
    }
  }

  // 创建表
  createTables() {
    try {
      const createUsersTable = `
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          username TEXT UNIQUE NOT NULL,
          phone TEXT UNIQUE NOT NULL,
          password TEXT NOT NULL,
          avatar TEXT,
          points INTEGER DEFAULT 0,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `;
      
      // 如果表已存在但没有 points 字段，添加该字段
      try {
        this.db.exec('ALTER TABLE users ADD COLUMN points INTEGER DEFAULT 0');
        console.log('[SQL] 已添加 points 字段到 users 表');
      } catch (err) {
        // 字段已存在，忽略错误
      }

      this.db.exec(createUsersTable);
      console.log('[SQL] users表已创建或已存在');
    } catch (err) {
      console.error('[SQL] 创建users表失败:', err);
      throw err;
    }
  }

  // 根据用户名查找用户（不区分大小写）
  findUserByUsername(username) {
    // 将输入转换为小写进行查询，但返回原始数据
    const normalizedUsername = username.toLowerCase();
    const sql = 'SELECT * FROM users WHERE LOWER(username) = ?';
    return this.db.prepare(sql).get(normalizedUsername) || null;
  }

  // 根据手机号查找用户
  findUserByPhone(phone) {
    const sql = 'SELECT * FROM users WHERE phone = ?';
    return this.db.prepare(sql).get(phone) || null;
  }

  // 根据ID查找用户
  findUserById(id) {
    const sql = 'SELECT * FROM users WHERE id = ?';
    return this.db.prepare(sql).get(id) || null;
  }

  // 获取所有用户
  getAllUsers() {
    const sql = 'SELECT id, username, phone, points, avatar, created_at, updated_at FROM users ORDER BY id DESC';
    return this.db.prepare(sql).all() || [];
  }

  // 创建新用户
  createUser(userData) {
    const { username, phone, password, avatar, points } = userData;
    const sql = `
      INSERT INTO users (username, phone, password, avatar, points)
      VALUES (?, ?, ?, ?, ?)
    `;
    const stmt = this.db.prepare(sql);
    const result = stmt.run(username, phone, password, avatar || null, points || 0);
    return { id: result.lastInsertRowid, ...userData };
  }
  
  // 获取用户点数
  getUserPoints(username) {
    const normalizedUsername = username.toLowerCase();
    const sql = 'SELECT points FROM users WHERE LOWER(username) = ?';
    const result = this.db.prepare(sql).get(normalizedUsername);
    return result ? result.points : 0;
  }
  
  // 扣除用户点数
  deductPoints(username, points) {
    const normalizedUsername = username.toLowerCase();
    const sql = 'UPDATE users SET points = points - ?, updated_at = CURRENT_TIMESTAMP WHERE LOWER(username) = ? AND points >= ?';
    const stmt = this.db.prepare(sql);
    const result = stmt.run(points, normalizedUsername, points);
    return result.changes > 0;
  }
  
  // 增加用户点数
  addPoints(username, points) {
    const normalizedUsername = username.toLowerCase();
    const sql = 'UPDATE users SET points = points + ?, updated_at = CURRENT_TIMESTAMP WHERE LOWER(username) = ?';
    const stmt = this.db.prepare(sql);
    const result = stmt.run(points, normalizedUsername);
    return result.changes > 0;
  }

  // 更新用户信息
  updateUser(id, updates) {
    const fields = [];
    const values = [];
    
    if (updates.username !== undefined) {
      fields.push('username = ?');
      values.push(updates.username);
    }
    if (updates.phone !== undefined) {
      fields.push('phone = ?');
      values.push(updates.phone);
    }
    if (updates.password !== undefined) {
      fields.push('password = ?');
      values.push(updates.password);
    }
    if (updates.avatar !== undefined) {
      fields.push('avatar = ?');
      values.push(updates.avatar);
    }
    
    fields.push('updated_at = CURRENT_TIMESTAMP');
    values.push(id);

    const sql = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;
    const stmt = this.db.prepare(sql);
    const result = stmt.run(...values);
    return { changes: result.changes };
  }

  // 关闭数据库连接
  close() {
    if (this.db) {
      this.db.close();
      console.log('[SQL] 数据库连接已关闭');
    }
  }
}

// 创建单例
let dbInstance = null;

async function getDatabase() {
  if (!dbInstance) {
    dbInstance = new DatabaseManager();
    await dbInstance.init();
  }
  return dbInstance;
}

module.exports = {
  Database: DatabaseManager,
  getDatabase
};