import mysql from 'mysql2/promise';
import { config } from './config.js';

let pool;

function getPool() {
  if (!pool) {
    if (!config.db.host || !config.db.user || !config.db.password) {
      console.log('[DB] 未配置数据库环境变量，跳过持久化');
      return null;
    }
    pool = mysql.createPool({
      host: config.db.host,
      port: config.db.port,
      user: config.db.user,
      password: config.db.password,
      database: config.db.database,
      waitForConnections: true,
      connectionLimit: 5,
    });
  }
  return pool;
}

export async function ensureSchema() {
  const p = getPool();
  if (!p) return;
  await p.query(`CREATE TABLE IF NOT EXISTS accounts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uniq_email (email)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`);
}

export async function saveAccount({ email, password, createdAt, expiresAt, status, credits }) {
  const p = getPool();
  if (!p) return;
  await ensureSchema();
  const sql = `INSERT INTO accounts (email, password, status, created_at, expires_at, credits)
               VALUES (?, ?, ?, ?, ?, ?)
               ON DUPLICATE KEY UPDATE password=VALUES(password), status=VALUES(status), created_at=VALUES(created_at), expires_at=VALUES(expires_at), credits=VALUES(credits)`;
  await p.execute(sql, [
    email,
    password,
    status,
    formatDateTime(createdAt),
    formatDateTime(expiresAt),
    credits
  ]);
}

export async function listAccounts({ status } = {}) {
  const p = getPool();
  if (!p) return [];
  await ensureSchema();
  let sql = 'SELECT * FROM accounts';
  const params = [];
  if (status) {
    sql += ' WHERE status=?';
    params.push(status);
  }
  sql += ' ORDER BY created_at DESC LIMIT 200';
  const [rows] = await p.execute(sql, params);
  return rows;
}

function pad2(n) { return String(n).padStart(2, '0'); }
function formatDateTime(d) {
  const dt = new Date(d);
  const Y = dt.getFullYear();
  const M = pad2(dt.getMonth()+1);
  const D = pad2(dt.getDate());
  const h = pad2(dt.getHours());
  const m = pad2(dt.getMinutes());
  const s = pad2(dt.getSeconds());
  return `${Y}-${M}-${D} ${h}:${m}:${s}`;
}