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] 创建表失败:', 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 };
  }

  // ============ AI 历史记录操作（保存到用户目录） ============
  
  // 获取用户历史文件路径
  _getHistoryFilePath(username) {
    const userDir = path.join(__dirname, 'users', username.toLowerCase(), 'generate-history');
    if (!fs.existsSync(userDir)) {
      fs.mkdirSync(userDir, { recursive: true });
    }
    return path.join(userDir, 'history.json');
  }
  
  // 加载用户历史
  _loadUserHistory(username) {
    const filePath = this._getHistoryFilePath(username);
    try {
      if (fs.existsSync(filePath)) {
        return JSON.parse(fs.readFileSync(filePath, 'utf-8'));
      }
    } catch (err) {
      console.error('[SQL] 加载用户历史失败:', err);
    }
    return [];
  }
  
  // 保存用户历史
  _saveUserHistory(username, history) {
    const filePath = this._getHistoryFilePath(username);
    try {
      fs.writeFileSync(filePath, JSON.stringify(history, null, 2), 'utf-8');
    } catch (err) {
      console.error('[SQL] 保存用户历史失败:', err);
    }
  }
  
  // 添加 AI 历史记录
  addAIHistory(taskId, username, status, previewUrl, retryData = null, createdAt = null) {
    const history = this._loadUserHistory(username);
    const timestamp = createdAt || new Date().toISOString();
    
    history.unshift({
      id: taskId,
      status: status,
      imageUrl: null,
      previewUrl: previewUrl,
      error: null,
      isRetry: false,
      retryData: retryData,
      createdAt: timestamp,
      completedAt: null
    });
    
    this._saveUserHistory(username, history);
    return history.length;
  }
  
  // 获取用户 AI 历史记录
  getAIHistory(username) {
    return this._loadUserHistory(username);
  }
  
  // 更新 AI 任务状态
  updateAITaskStatus(taskId, status, imageUrl = null, error = null) {
    // 需要找到任务所属用户
    const usersDir = path.join(__dirname, 'users');
    if (!fs.existsSync(usersDir)) return false;
    
    const users = fs.readdirSync(usersDir);
    for (const username of users) {
      const history = this._loadUserHistory(username);
      const task = history.find(t => t.id === taskId);
      if (task) {
        task.status = status;
        if (imageUrl) task.imageUrl = imageUrl;
        if (error) task.error = error;
        task.completedAt = new Date().toISOString();
        this._saveUserHistory(username, history);
        return true;
      }
    }
    return false;
  }
  
  // 获取 AI 任务
  getAITask(taskId) {
    const usersDir = path.join(__dirname, 'users');
    if (!fs.existsSync(usersDir)) return null;
    
    const users = fs.readdirSync(usersDir);
    for (const username of users) {
      const history = this._loadUserHistory(username);
      const task = history.find(t => t.id === taskId);
      if (task) {
        return { ...task, username };
      }
    }
    return null;
  }
  
  // 删除 AI 任务
  deleteAITask(taskId) {
    const usersDir = path.join(__dirname, 'users');
    if (!fs.existsSync(usersDir)) return false;
    
    const users = fs.readdirSync(usersDir);
    for (const username of users) {
      const history = this._loadUserHistory(username);
      const index = history.findIndex(t => t.id === taskId);
      if (index !== -1) {
        history.splice(index, 1);
        this._saveUserHistory(username, history);
        return true;
      }
    }
    return false;
  }
  
  // 更新 AI 任务重试数据
  updateAITaskRetryData(taskId, retryData) {
    const usersDir = path.join(__dirname, 'users');
    if (!fs.existsSync(usersDir)) return false;
    
    const users = fs.readdirSync(usersDir);
    for (const username of users) {
      const history = this._loadUserHistory(username);
      const task = history.find(t => t.id === taskId);
      if (task) {
        task.retryData = retryData;
        this._saveUserHistory(username, history);
        return true;
      }
    }
    return false;
  }

  // 关闭数据库连接
  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
};