import pool from '../db';

export type LedgerReason =
  | 'DAILY_GRANT'
  | 'FORECAST_UNLOCK'
  | 'TEAM_PROPS_UNLOCK'
  | 'STEAM_INSIGHT_UNLOCK'
  | 'SHARP_INSIGHT_UNLOCK'
  | 'DVP_INSIGHT_UNLOCK'
  | 'HCW_INSIGHT_UNLOCK'
  | 'PLAYER_PROP_UNLOCK'
  | 'PURCHASE_SINGLE'
  | 'PURCHASE_DAILY_PASS'
  | 'PURCHASE_MONTHLY'
  | 'SURVEY_REWARD'
  | 'TOURIST_EXPIRE'
  | 'VERIFICATION_GRANT'
  | 'ADMIN_ADJUST';

export async function recordLedgerEntry(
  userId: string,
  changeAmount: number,
  reason: LedgerReason,
  balanceAfter: number,
  metadata?: Record<string, any>
): Promise<void> {
  await pool.query(
    `INSERT INTO rm_forecast_ledger (user_id, change_amount, reason, balance_after, metadata)
     VALUES ($1, $2, $3, $4, $5)`,
    [userId, changeAmount, reason, balanceAfter, metadata ? JSON.stringify(metadata) : null]
  );
}

export async function getLedgerEntries(userId: string, limit = 50): Promise<any[]> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_forecast_ledger WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2`,
    [userId, limit]
  );
  return rows;
}

export async function getDailyGrantCount(userId: string, pacificDate: string): Promise<number> {
  const { rows } = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_forecast_ledger
     WHERE user_id = $1 AND reason = 'DAILY_GRANT'
     AND created_at::date = $2::date`,
    [userId, pacificDate]
  );
  return parseInt(rows[0]?.cnt || '0', 10);
}