memind/scripts/compensate-billing-token-estimate-overcharge.mjs

#!/usr/bin/env node
/**
 * Compensate users overcharged when billing fell back to token estimate
 * instead of Goose accumulated_cost (DeepSeek cache-aware upstream cost).
 *
 * Usage:
 *   node scripts/compensate-billing-token-estimate-overcharge.mjs
 *   node scripts/compensate-billing-token-estimate-overcharge.mjs --since=2026-08-04
 *   node scripts/compensate-billing-token-estimate-overcharge.mjs --since=2026-08-04 --apply
 */
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import mysql from 'mysql2/promise';
import pg from 'pg';
import { resolveGooseSessionPgUrl } from '../goose-session-cost.mjs';

const root = path.join(path.dirname(fileURLToPath(import.meta.url)), '..');

function loadEnvFile(filePath) {
  if (!fs.existsSync(filePath)) return;
  for (const line of fs.readFileSync(filePath, 'utf8').split('\n')) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const eq = trimmed.indexOf('=');
    if (eq < 0) continue;
    const key = trimmed.slice(0, eq).trim();
    const value = trimmed.slice(eq + 1).trim();
    if (!process.env[key]) process.env[key] = value;
  }
}

loadEnvFile(path.join(root, '.env'));

const apply = process.argv.includes('--apply');
const sinceArg = process.argv.find((a) => a.startsWith('--since='));
const sinceRaw = sinceArg ? sinceArg.slice('--since='.length) : '2026-08-04';
const startMs = sinceRaw.includes('T')
  ? new Date(sinceRaw).getTime()
  : new Date(`${sinceRaw}T00:00:00+08:00`).getTime();
const sinceLabel = sinceRaw.includes('T')
  ? sinceRaw.replace('T', ' ').replace('+08:00', ' CST')
  : `${sinceRaw} 00:00 CST`;
const DEDupe_NOTE_PREFIX = `补偿：Token估价超扣(${sinceLabel}起)`;

function loadBillingConfig() {
  const marginMultiplier = Number(process.env.H5_MARGIN_MULTIPLIER ?? 1);
  return {
    useBackendCost: process.env.H5_USE_BACKEND_COST === '1',
    usdCnyRate: Number(process.env.H5_USD_CNY_RATE ?? 7.2),
    marginMultiplier: Number.isFinite(marginMultiplier) && marginMultiplier > 0 ? marginMultiplier : 1,
    minBillCents: Number(process.env.H5_MIN_BILL_CENTS ?? 1),
  };
}

function correctTotalCents(gooseCostUsd, config) {
  if (gooseCostUsd == null || gooseCostUsd <= 0) return 0;
  return Math.max(
    config.minBillCents,
    Math.ceil(gooseCostUsd * config.usdCnyRate * 100 * config.marginMultiplier),
  );
}

function resolvePgUrl() {
  return resolveGooseSessionPgUrl(process.env);
}

async function main() {
  if (!process.env.DATABASE_URL) {
    console.error('DATABASE_URL is not configured');
    process.exit(1);
  }

  const config = loadBillingConfig();
  const pool = mysql.createPool({ uri: process.env.DATABASE_URL, connectionLimit: 4 });
  const pgClient = new pg.Client({ connectionString: resolvePgUrl() });
  await pgClient.connect();

  try {
    const [records] = await pool.query(
      `SELECT r.user_id, u.username, u.status, r.agent_session_id, r.cost_cents
       FROM h5_usage_records r
       JOIN h5_users u ON u.id = r.user_id
       WHERE r.created_at >= ?
       ORDER BY r.created_at ASC`,
      [startMs],
    );

    const bySession = new Map();
    for (const row of records) {
      const sid = row.agent_session_id;
      if (!bySession.has(sid)) {
        bySession.set(sid, {
          userId: row.user_id,
          username: row.username,
          status: row.status,
          sinceStart: 0,
          all: 0,
        });
      }
      bySession.get(sid).sinceStart += Number(row.cost_cents);
    }

    const sessionIds = [...bySession.keys()];
    if (sessionIds.length === 0) {
      console.log('No usage records since', sinceLabel);
      return;
    }

    const [allRows] = await pool.query(
      `SELECT agent_session_id, SUM(cost_cents) AS total
       FROM h5_usage_records
       WHERE agent_session_id IN (?)
       GROUP BY agent_session_id`,
      [sessionIds],
    );
    for (const row of allRows) {
      bySession.get(row.agent_session_id).all = Number(row.total);
    }

    const gooseRes = await pgClient.query(
      `SELECT id, accumulated_cost FROM sessions WHERE id = ANY($1::text[])`,
      [sessionIds],
    );
    const gooseCostBySession = new Map(
      gooseRes.rows.map((row) => [row.id, Number(row.accumulated_cost)]),
    );

    const byUser = new Map();
    for (const [sid, session] of bySession) {
      const correct = correctTotalCents(gooseCostBySession.get(sid), config);
      const overcharge = Math.max(0, session.all - correct);
      const refund = Math.min(session.sinceStart, overcharge);
      if (refund <= 0) continue;

      if (!byUser.has(session.userId)) {
        byUser.set(session.userId, {
          userId: session.userId,
          username: session.username,
          status: session.status,
          refundCents: 0,
          sessions: [],
        });
      }
      const user = byUser.get(session.userId);
      user.refundCents += refund;
      user.sessions.push({ sid, refund, chargedSinceStart: session.sinceStart, correctTotal: correct });
    }

    const users = [...byUser.values()].sort((a, b) => b.refundCents - a.refundCents);
    const totalRefund = users.reduce((sum, user) => sum + user.refundCents, 0);

    console.log('Billing config:', config);
    console.log('Since:', sinceLabel, `(${startMs})`);
    console.log('Affected users:', users.length);
    console.log('Total refund:', `¥${(totalRefund / 100).toFixed(2)}`, `(${totalRefund} cents)`);
    console.log('');

    for (const user of users) {
      console.log(`- ${user.username}: ¥${(user.refundCents / 100).toFixed(2)} (${user.sessions.length} sessions)`);
    }

    if (!apply) {
      console.log('');
      console.log('Dry run only. Re-run with --apply to execute.');
      return;
    }

    const now = Date.now();
    for (const user of users) {
      const note = `${DEDupe_NOTE_PREFIX} ¥${(user.refundCents / 100).toFixed(2)}`;
      const [existing] = await pool.query(
        `SELECT id FROM h5_billing_ledger
         WHERE user_id = ? AND type = 'adjust' AND note = ?
         LIMIT 1`,
        [user.userId, note],
      );
      if (existing.length) {
        console.log(`SKIP ${user.username}: already compensated (${note})`);
        continue;
      }

      const conn = await pool.getConnection();
      try {
        await conn.beginTransaction();
        await conn.query(
          `INSERT INTO h5_user_wallets (user_id, balance_cents, tokens_used, updated_at)
           VALUES (?, ?, 0, ?)
           ON DUPLICATE KEY UPDATE
             balance_cents = balance_cents + VALUES(balance_cents),
             updated_at = VALUES(updated_at)`,
          [user.userId, user.refundCents, now],
        );
        await conn.query(
          `INSERT INTO h5_billing_ledger
           (user_id, type, amount_cents, tokens, note, operator_id, created_at)
           VALUES (?, 'adjust', ?, 0, ?, NULL, ?)`,
          [user.userId, user.refundCents, note, now],
        );
        if (user.status === 'suspended') {
          await conn.query(`UPDATE h5_users SET status = 'active', updated_at = ? WHERE id = ?`, [
            now,
            user.userId,
          ]);
        }
        await conn.commit();
        console.log(`APPLIED ${user.username}: +¥${(user.refundCents / 100).toFixed(2)}`);
      } catch (err) {
        await conn.rollback();
        throw err;
      } finally {
        conn.release();
      }
    }
  } finally {
    await pgClient.end();
    await pool.end();
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});