import pool from '../db';

export interface RmPurchase {
  id: string;
  user_id: string;
  product_type: string;
  amount_cents: number;
  picks_granted: number;
  stripe_payment_intent_id: string | null;
  stripe_session_id: string | null;
  attribution: any;
  created_at: string;
}

export async function createPurchase(data: {
  userId: string;
  productType: string;
  amountCents: number;
  picksGranted: number;
  stripePaymentIntentId?: string;
  stripeSessionId?: string;
  attribution?: any;
}): Promise<RmPurchase> {
  const { rows } = await pool.query(
    `INSERT INTO rm_purchases (user_id, product_type, amount_cents, picks_granted, stripe_payment_intent_id, stripe_session_id, attribution)
     VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
    [
      data.userId,
      data.productType,
      data.amountCents,
      data.picksGranted,
      data.stripePaymentIntentId || null,
      data.stripeSessionId || null,
      data.attribution ? JSON.stringify(data.attribution) : null,
    ]
  );
  return rows[0];
}

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

export async function getRecentPurchases(limit = 100): Promise<any[]> {
  const { rows } = await pool.query(
    `SELECT p.*, u.email FROM rm_purchases p
     JOIN rm_users u ON p.user_id = u.id
     ORDER BY p.created_at DESC LIMIT $1`,
    [limit]
  );
  return rows;
}

export async function getPurchaseStats(period: string = 'daily'): Promise<any> {
  let interval = '1 day';
  if (period === 'weekly') interval = '7 days';
  if (period === 'monthly') interval = '30 days';

  const { rows } = await pool.query(
    `SELECT
       COUNT(*) as total_purchases,
       SUM(amount_cents) as total_revenue_cents,
       SUM(picks_granted) as total_picks_sold,
       COUNT(DISTINCT user_id) as unique_buyers
     FROM rm_purchases
     WHERE created_at >= NOW() - $1::interval`,
    [interval]
  );
  return rows[0];
}