function getProfileStats({ db, userId }) {
  if (!userId) {
    return { stats: [] };
  }
  const accountsTable = resolveTable(db, "accounts");
  const transactionsTable = resolveTable(db, "transactions");
  if (!accountsTable || !transactionsTable) {
    return {
      stats: [
        { label: "Balance", value: 0 },
        { label: "Total earned", value: 0 },
        { label: "Total spent", value: 0 },
        { label: "Given to others", value: 0 },
        { label: "Received from others", value: 0 }
      ]
    };
  }
  const account = db
    .prepare(`SELECT balance FROM ${quoteIdentifier(accountsTable)} WHERE user_id = ?`)
    .get(userId);
  const earned = db
    .prepare(
      `SELECT COALESCE(SUM(amount), 0) AS total FROM ${quoteIdentifier(transactionsTable)} ` +
        "WHERE to_user_id = ? AND (from_user_id IS NULL OR from_user_id = '')"
    )
    .get(userId);
  const spent = db
    .prepare(
      `SELECT COALESCE(SUM(amount), 0) AS total FROM ${quoteIdentifier(transactionsTable)} ` +
        "WHERE from_user_id = ? AND (to_user_id IS NULL OR to_user_id = '')"
    )
    .get(userId);
  const transfersOut = db
    .prepare(
      `SELECT COALESCE(SUM(amount), 0) AS total FROM ${quoteIdentifier(transactionsTable)} ` +
        "WHERE from_user_id = ? AND to_user_id IS NOT NULL AND to_user_id != ''"
    )
    .get(userId);
  const transfersIn = db
    .prepare(
      `SELECT COALESCE(SUM(amount), 0) AS total FROM ${quoteIdentifier(transactionsTable)} ` +
        "WHERE to_user_id = ? AND from_user_id IS NOT NULL AND from_user_id != ''"
    )
    .get(userId);

  return {
    stats: [
      { label: "Balance", value: account?.balance ?? 0 },
      { label: "Total earned", value: earned?.total ?? 0 },
      { label: "Total spent", value: spent?.total ?? 0 },
      { label: "Given to others", value: transfersOut?.total ?? 0 },
      { label: "Received from others", value: transfersIn?.total ?? 0 }
    ]
  };
}

function getLeaderboards({ db, limit = 10 }) {
  const accountsTable = resolveTable(db, "accounts");
  if (!accountsTable) {
    return {
      boards: [
        {
          title: "Top balances",
          valueLabel: "Balance",
          rows: []
        }
      ]
    };
  }
  const rows = db
    .prepare(
      `SELECT user_profiles.internal_username AS username, ${quoteIdentifier(accountsTable)}.balance AS value ` +
        `FROM ${quoteIdentifier(accountsTable)} ` +
        `JOIN user_profiles ON user_profiles.id = ${quoteIdentifier(accountsTable)}.user_id ` +
        `ORDER BY ${quoteIdentifier(accountsTable)}.balance DESC LIMIT ?`
    )
    .all(limit);

  return {
    boards: [
      {
        title: "Top balances",
        valueLabel: "Balance",
        rows
      }
    ]
  };
}

function resolveTable(db, suffix) {
  const legacyStem = ["echo", "nomy"].join("");
  const current = `economy_${suffix}`;
  const legacy = `${legacyStem}_${suffix}`;
  if (tableExists(db, current)) {
    return current;
  }
  if (tableExists(db, legacy)) {
    return legacy;
  }
  return null;
}

function tableExists(db, name) {
  return Boolean(
    db.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?").get(name)
  );
}

function quoteIdentifier(name) {
  return `"${name.replace(/"/g, '""')}"`;
}

module.exports = {
  getProfileStats,
  getLeaderboards
};