export function parsePageQuery(query, defaultPageSize = 20) {
  const page = Math.max(1, Number(query.page) || 1);
  const pageSize = Math.min(Math.max(Number(query.pageSize) || defaultPageSize, 1), 200);
  const offset = (page - 1) * pageSize;
  return { page, pageSize, offset };
}

export function paginateArray(items, query, defaultPageSize = 20) {
  const { page, pageSize, offset } = parsePageQuery(query, defaultPageSize);
  const total = items.length;
  return {
    items: items.slice(offset, offset + pageSize),
    total,
    page,
    pageSize,
    totalPages: Math.max(1, Math.ceil(total / pageSize)),
  };
}

const MS_THRESHOLD = 1_000_000_000_000;

function parseUsageTimeFilters(query) {
  const startAt = Number(query.startAt);
  const endAt = Number(query.endAt);
  const hasStart = Number.isFinite(startAt) && startAt > 0;
  const hasEnd = Number.isFinite(endAt) && endAt > 0;
  if (!hasStart || !hasEnd || startAt > endAt) {
    return null;
  }
  // Frontend sends unix seconds; h5_usage_records.created_at is stored in milliseconds.
  const startAtMs = startAt < MS_THRESHOLD ? startAt * 1000 : startAt;
  const endAtMs = endAt < MS_THRESHOLD ? endAt * 1000 + 999 : endAt;
  return { startAt, endAt, startAtMs, endAtMs };
}

function formatSqlDay(value) {
  if (value instanceof Date) {
    const y = value.getFullYear();
    const m = String(value.getMonth() + 1).padStart(2, '0');
    const d = String(value.getDate()).padStart(2, '0');
    return `${y}-${m}-${d}`;
  }
  return String(value).slice(0, 10);
}

export async function getUsageTotals(pool, { userId = null, startAtMs = null, endAtMs = null } = {}) {
  const params = [];
  const clauses = [];
  if (userId) {
    clauses.push('r.user_id = ?');
    params.push(userId);
  }
  if (startAtMs != null && endAtMs != null) {
    clauses.push('r.created_at >= ?', 'r.created_at <= ?');
    params.push(startAtMs, endAtMs);
  }
  const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const [[row]] = await pool.query(
    `SELECT COUNT(*) AS count,
            COALESCE(SUM(r.input_tokens), 0) AS input_tokens,
            COALESCE(SUM(r.output_tokens), 0) AS output_tokens,
            COALESCE(SUM(r.cost_cents), 0) AS cost_cents
     FROM h5_usage_records r
     ${where}`,
    params,
  );
  return {
    count: Number(row.count),
    inputTokens: Number(row.input_tokens),
    outputTokens: Number(row.output_tokens),
    costCents: Number(row.cost_cents),
  };
}

export async function getUsageSummary(pool, query) {
  const userId = typeof query.userId === 'string' && query.userId ? query.userId : null;
  const range = parseUsageTimeFilters(query);
  const allTime = await getUsageTotals(pool, { userId });
  if (!range) {
    return { range: null, allTime };
  }
  const rangeTotals = await getUsageTotals(pool, {
    userId,
    startAtMs: range.startAtMs,
    endAtMs: range.endAtMs,
  });
  return {
    range: {
      ...rangeTotals,
      startAt: range.startAt,
      endAt: range.endAt,
    },
    allTime,
  };
}

export async function getUsageStats(pool, query) {
  const userId = typeof query.userId === 'string' && query.userId ? query.userId : null;
  const range = parseUsageTimeFilters(query);
  if (!range) {
    const err = new Error('INVALID_TIME_RANGE');
    err.code = 'INVALID_TIME_RANGE';
    throw err;
  }
  const { startAt, endAt, startAtMs, endAtMs } = range;
  const params = [startAtMs, endAtMs];
  const clauses = ['r.created_at >= ?', 'r.created_at <= ?'];
  if (userId) {
    clauses.push('r.user_id = ?');
    params.push(userId);
  }
  const where = `WHERE ${clauses.join(' AND ')}`;
  const [rows] = await pool.query(
    `SELECT DATE(FROM_UNIXTIME(r.created_at / 1000)) AS day,
            COUNT(*) AS count,
            COALESCE(SUM(r.input_tokens), 0) AS input_tokens,
            COALESCE(SUM(r.output_tokens), 0) AS output_tokens,
            COALESCE(SUM(r.cost_cents), 0) AS cost_cents
     FROM h5_usage_records r
     ${where}
     GROUP BY day
     ORDER BY day ASC`,
    params,
  );
  return {
    buckets: rows.map((row) => ({
      day: formatSqlDay(row.day),
      count: Number(row.count),
      inputTokens: Number(row.input_tokens),
      outputTokens: Number(row.output_tokens),
      costCents: Number(row.cost_cents),
    })),
    startAt,
    endAt,
  };
}

export async function listUsagePaged(pool, query) {
  const { page, pageSize, offset } = parsePageQuery(query, 20);
  const userId = typeof query.userId === 'string' && query.userId ? query.userId : null;
  const range = parseUsageTimeFilters(query);
  const params = [];
  const clauses = [];
  if (userId) {
    clauses.push('r.user_id = ?');
    params.push(userId);
  }
  if (range) {
    clauses.push('r.created_at >= ?', 'r.created_at <= ?');
    params.push(range.startAtMs, range.endAtMs);
  }
  const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const [[{ total }]] = await pool.query(
    `SELECT COUNT(*) AS total FROM h5_usage_records r ${where}`,
    params,
  );
  const [rows] = await pool.query(
    `SELECT r.id, r.user_id, u.username, u.display_name, r.agent_session_id, r.request_id,
            r.input_tokens, r.output_tokens, r.cost_cents, r.balance_after_cents, r.created_at
     FROM h5_usage_records r
     JOIN h5_users u ON u.id = r.user_id
     ${where}
     ORDER BY r.created_at DESC
     LIMIT ? OFFSET ?`,
    [...params, pageSize, offset],
  );
  return {
    records: rows.map((row) => ({
      id: Number(row.id),
      userId: row.user_id,
      username: row.username,
      displayName: row.display_name,
      agentSessionId: row.agent_session_id,
      requestId: row.request_id,
      inputTokens: Number(row.input_tokens),
      outputTokens: Number(row.output_tokens),
      costCents: Number(row.cost_cents),
      balanceAfterCents: Number(row.balance_after_cents),
      createdAt: Number(row.created_at),
    })),
    total: Number(total),
    page,
    pageSize,
    totalPages: Math.max(1, Math.ceil(Number(total) / pageSize)),
  };
}

export async function listLedgerPaged(pool, query) {
  const { page, pageSize, offset } = parsePageQuery(query, 20);
  const userId = typeof query.userId === 'string' && query.userId ? query.userId : null;
  const params = [];
  const clauses = [];
  if (userId) {
    clauses.push('l.user_id = ?');
    params.push(userId);
  }
  const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const [[{ total }]] = await pool.query(
    `SELECT COUNT(*) AS total FROM h5_billing_ledger l ${where}`,
    params,
  );
  const [rows] = await pool.query(
    `SELECT l.id, l.user_id, u.username, l.type, l.amount_cents, l.tokens,
            l.session_id, l.note, l.created_at
     FROM h5_billing_ledger l
     JOIN h5_users u ON u.id = l.user_id
     ${where}
     ORDER BY l.created_at DESC
     LIMIT ? OFFSET ?`,
    [...params, pageSize, offset],
  );
  return {
    entries: rows.map((row) => ({
      id: Number(row.id),
      userId: row.user_id,
      username: row.username,
      type: row.type,
      amountCents: Number(row.amount_cents),
      tokens: Number(row.tokens),
      sessionId: row.session_id,
      note: row.note,
      createdAt: Number(row.created_at),
    })),
    total: Number(total),
    page,
    pageSize,
    totalPages: Math.max(1, Math.ceil(Number(total) / pageSize)),
  };
}