db.js

/**
 * db.js - PostgreSQL 持久化层
 *
 * 当 DATABASE_URL 环境变量设置时启用 PostgreSQL，
 * 否则 pool.js 会走原有的文件系统路径。
 *
 * 表结构自动创建 (CREATE TABLE IF NOT EXISTS)。
 */

let pool = null;

export function isDbEnabled() {
  return !!process.env.DATABASE_URL;
}

export async function initDb() {
  if (!isDbEnabled()) return;

  const pg = await import('pg');
  const { Pool } = pg.default;

  const connStr = process.env.DATABASE_URL;
  pool = new Pool({
    connectionString: connStr,
    ssl: { rejectUnauthorized: false },
    max: 5,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
  });

  // 自动建表
  await pool.query(`
    CREATE TABLE IF NOT EXISTS accounts (
      id            SERIAL PRIMARY KEY,
      email         TEXT NOT NULL UNIQUE,
      password      TEXT NOT NULL,
      cookies       JSONB NOT NULL DEFAULT '{}',
      site          TEXT NOT NULL DEFAULT 'chataibot.pro',
      registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      state         TEXT NOT NULL DEFAULT 'active',
      updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);
  await pool.query(`CREATE INDEX IF NOT EXISTS idx_accounts_state ON accounts(state)`);
  await pool.query(`CREATE INDEX IF NOT EXISTS idx_accounts_email ON accounts(email)`);

  console.log('[DB] PostgreSQL connected, table ready');
}

export async function loadAccounts() {
  const { rows } = await pool.query(
    `SELECT email, password, cookies FROM accounts WHERE state != 'dead'`
  );
  return rows;
}

export async function saveNewAccount(email, password, cookies) {
  await pool.query(
    `INSERT INTO accounts (email, password, cookies)
     VALUES ($1, $2, $3)
     ON CONFLICT (email) DO UPDATE SET
       password = EXCLUDED.password,
       cookies = EXCLUDED.cookies,
       state = 'active',
       updated_at = NOW()`,
    [email, password, cookies]
  );
}

export async function updateCookies(email, cookies) {
  await pool.query(
    `UPDATE accounts SET cookies = $1, updated_at = NOW() WHERE email = $2`,
    [cookies, email]
  );
}

export async function markDead(email) {
  await pool.query(
    `UPDATE accounts SET state = 'dead', updated_at = NOW() WHERE email = $1`,
    [email]
  );
}

export async function closeDb() {
  if (pool) {
    await pool.end();
    pool = null;
  }
}