api/lib/db.ts

import path from 'path';
import { fileURLToPath } from 'url';
import bcrypt from 'bcryptjs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 数据库文件存储在 /app/data 或项目根目录
const dataDir = process.env.DATA_DIR || path.resolve(process.cwd(), 'data');
const dbPath = path.resolve(dataDir, 'platform.db');

let db: any = null;

/**
 * 延迟加载数据库，防止 native 模块加载失败导致进程直接退出
 */
const getDB = async () => {
  if (db) return db;
  
  try {
    console.log(`[Database] 正在连接 SQLite: ${dbPath}`);
    // 使用动态导入，以便捕获加载原生模块时的错误
    const { default: Database } = await import('better-sqlite3');
    db = new Database(dbPath);
    return db;
  } catch (err: any) {
    console.error('[Database] SQLite 加载失败:', err.message);
    if (err.message.includes('bindings')) {
      console.error('[Database] 提示: 可能是 better-sqlite3 的原生模块未正确编译。');
    }
    throw err;
  }
};

// 初始化表结构
const initDB = async () => {
  const database = await getDB();
  
  // 1. 用户表 (增加配额与等级)
  database.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      name TEXT,
      avatar TEXT, -- 用户头像 URL
      role TEXT DEFAULT 'user', -- 'user', 'admin'
      plan TEXT DEFAULT 'free', -- 'free', 'pro', 'enterprise'
      quota_remaining INTEGER DEFAULT 500, -- 每月剩余 AI 消息额度
      two_factor_enabled BOOLEAN DEFAULT 0, -- 是否开启双重认证
      login_alert_enabled BOOLEAN DEFAULT 1, -- 是否开启登录提醒
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // 检查并增加新列 (针对已存在的数据库)
  try {
    const columns = database.prepare("PRAGMA table_info(users)").all();
    const hasAvatar = columns.some((c: any) => c.name === 'avatar');
    const has2FA = columns.some((c: any) => c.name === 'two_factor_enabled');
    const hasAlert = columns.some((c: any) => c.name === 'login_alert_enabled');
    
    if (!hasAvatar) {
      database.exec("ALTER TABLE users ADD COLUMN avatar TEXT");
      console.log('[Database] users 表已增加 avatar 列');
    }
    if (!has2FA) {
      database.exec("ALTER TABLE users ADD COLUMN two_factor_enabled BOOLEAN DEFAULT 0");
      console.log('[Database] users 表已增加 two_factor_enabled 列');
    }
    if (!hasAlert) {
      database.exec("ALTER TABLE users ADD COLUMN login_alert_enabled BOOLEAN DEFAULT 1");
      console.log('[Database] users 表已增加 login_alert_enabled 列');
    }
  } catch (err) {
    console.error('[Database] 更新 users 表结构失败:', err);
  }

  // 2. 产品/计划表 (增加库存管理)
  database.exec(`
    CREATE TABLE IF NOT EXISTS products (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      price INTEGER NOT NULL, -- 以分为单位
      type TEXT NOT NULL, -- 'subscription', 'one-time'
      stock INTEGER DEFAULT -1, -- -1 表示无限, 0+ 表示限购数量
      description TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // 3. 订单表 (支持支付闭环)
  database.exec(`
    CREATE TABLE IF NOT EXISTS orders (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      product_id TEXT NOT NULL,
      amount INTEGER NOT NULL,
      status TEXT DEFAULT 'pending', -- 'pending', 'paid', 'cancelled', 'refunded'
      payment_method TEXT, -- 'alipay', 'stripe', 'wechat'
      payment_id TEXT, -- 外部支付流水号
      idempotency_key TEXT UNIQUE, -- 幂等键
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id),
      FOREIGN KEY (product_id) REFERENCES products (id)
    )
  `);

  // 4. 聊天会话表 (增加知识库关联)
  database.exec(`
    CREATE TABLE IF NOT EXISTS chat_sessions (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      title TEXT DEFAULT '新对话',
      knowledge_base_id TEXT, -- 关联的知识库 ID
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id),
      FOREIGN KEY (knowledge_base_id) REFERENCES knowledge_bases (id)
    )
  `);

  // 检查并增加 knowledge_base_id 列 (针对已存在的数据库)
  try {
    const columns = database.prepare("PRAGMA table_info(chat_sessions)").all();
    const hasKbId = columns.some((c: any) => c.name === 'knowledge_base_id');
    if (!hasKbId) {
      database.exec("ALTER TABLE chat_sessions ADD COLUMN knowledge_base_id TEXT");
      console.log('[Database] chat_sessions 表已增加 knowledge_base_id 列');
    }
  } catch (err) {
    console.error('[Database] 更新 chat_sessions 表结构失败:', err);
  }

  // 3. 聊天消息表 (增加向量存储)
  database.exec(`
    CREATE TABLE IF NOT EXISTS chat_messages (
      id TEXT PRIMARY KEY,
      session_id TEXT NOT NULL,
      role TEXT NOT NULL, -- 'user' or 'assistant'
      content TEXT NOT NULL,
      embedding TEXT, -- 存储向量 JSON 字符串
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (session_id) REFERENCES chat_sessions (id)
    )
  `);

  // 5. 审计日志表 (System Engineering: Observability)
  database.exec(`
    CREATE TABLE IF NOT EXISTS audit_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id TEXT,
      action TEXT NOT NULL, -- 'LOGIN', 'CHAT', 'ORDER_CREATE', 'PAYMENT_COMPLETE'
      status TEXT, -- 'SUCCESS', 'FAILED', 'CIRCUIT_OPEN'
      payload TEXT, -- JSON data
      ip TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // 6. 系统通知表 (Business: Message)
  database.exec(`
    CREATE TABLE IF NOT EXISTS notifications (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      title TEXT NOT NULL,
      content TEXT NOT NULL,
      type TEXT DEFAULT 'info', -- 'info', 'success', 'warning', 'error'
      is_read BOOLEAN DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 7. 工作流表
  database.exec(`
    CREATE TABLE IF NOT EXISTS workflows (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      name TEXT NOT NULL,
      data TEXT NOT NULL, -- JSON string of nodes and edges
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 8. 知识库表 (System: RAG)
  database.exec(`
    CREATE TABLE IF NOT EXISTS knowledge_bases (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      status TEXT DEFAULT 'processing', -- 'processing', 'ready'
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 9. 知识切片表 (System: Vector Store)
  database.exec(`
    CREATE TABLE IF NOT EXISTS knowledge_chunks (
      id TEXT PRIMARY KEY,
      kb_id TEXT NOT NULL,
      content TEXT NOT NULL,
      embedding TEXT, -- JSON string of vector
      metadata TEXT, -- JSON string of extra info
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (kb_id) REFERENCES knowledge_bases (id)
    )
  `);

  // 10. API Keys 表 (System: OpenAPI)
  database.exec(`
    CREATE TABLE IF NOT EXISTS api_keys (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      key_name TEXT NOT NULL,
      key_secret TEXT UNIQUE NOT NULL,
      status TEXT DEFAULT 'active', -- 'active', 'inactive'
      scopes TEXT DEFAULT '["all"]', -- JSON array of scopes
      last_used DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 检查并增加 status 和 scopes 列 (针对已存在的数据库)
  try {
    const columns = database.prepare("PRAGMA table_info(api_keys)").all();
    const hasStatus = columns.some((c: any) => c.name === 'status');
    const hasScopes = columns.some((c: any) => c.name === 'scopes');
    
    if (!hasStatus) {
      database.exec("ALTER TABLE api_keys ADD COLUMN status TEXT DEFAULT 'active'");
      console.log('[Database] api_keys 表已增加 status 列');
    }
    if (!hasScopes) {
      database.exec("ALTER TABLE api_keys ADD COLUMN scopes TEXT DEFAULT '[\"all\"]'");
      console.log('[Database] api_keys 表已增加 scopes 列');
    }
  } catch (err) {
    console.error('[Database] 更新 api_keys 表结构失败:', err);
  }

  // 11. Hugging Face Projects Cache
  database.exec(`
    CREATE TABLE IF NOT EXISTS hf_projects (
      id TEXT PRIMARY KEY,
      full_name TEXT NOT NULL,
      name TEXT NOT NULL,
      title TEXT,
      description TEXT,
      url TEXT,
      iframe_url TEXT,
      type TEXT,
      created_at_hf DATETIME,
      likes INTEGER,
      sdk TEXT,
      synced_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  console.log('[Database] SQLite 已就绪:', dbPath);

  // 初始化基础产品数据
  const products = [
    { id: 'plan_pro', name: '专业版', price: 9900, type: 'subscription', stock: -1, description: '无限 AI 消息 + 高级工作流' },
    { id: 'plan_ent', name: '企业版', price: 29900, type: 'subscription', stock: -1, description: '全方位技术支持 + 专属域名' },
    { id: 'limited_offer', name: '限时秒杀 (Pro 体验卡)', price: 100, type: 'one-time', stock: 10, description: '限量 10 份，仅需 1 元' }
  ];

  const insertProduct = database.prepare(`
    INSERT OR IGNORE INTO products (id, name, price, type, stock, description)
    VALUES (?, ?, ?, ?, ?, ?)
  `);

  products.forEach(p => {
    insertProduct.run(p.id, p.name, p.price, p.type, p.stock, p.description);
  });

  // 初始化默认管理员 (如果不存在)
  const adminEmail = 'admin@codex.ai';
  const existingAdmin = database.prepare('SELECT id FROM users WHERE email = ?').get(adminEmail);
  if (!existingAdmin) {
    const hashedPassword = bcrypt.hashSync('admin123', 10);
    database.prepare('INSERT INTO users (id, email, password, name, role, plan) VALUES (?, ?, ?, ?, ?, ?)')
      .run('ADMIN_ROOT', adminEmail, hashedPassword, 'System Admin', 'admin', 'enterprise');
    console.log('[Database] 默认管理员已创建: admin@codex.ai / admin123');
  }
};

/**
 * 数据库代理对象，保持与现有代码的兼容性
 * 在调用 await initDB() 之后，可以直接使用 import db from './lib/db.js'
 */
const dbProxy = new Proxy({} as any, {
  get(target, prop) {
    if (!db) {
      throw new Error('[Database] 数据库未初始化。请确保在应用启动时先调用了 await initDB()');
    }
    const value = db[prop];
    if (typeof value === 'function') {
      return value.bind(db);
    }
    return value;
  }
});

export { getDB, initDB };
export default dbProxy;