import pool from '../db';

export interface RmUserPick {
  id: string;
  user_id: string;
  forecast_id: string;
  event_id: string;
  was_weatherman: boolean;
  created_at: string;
}

export interface RecordPickResult extends RmUserPick {
  inserted: boolean;
}

function isUuid(value: string): boolean {
  return /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(value);
}

export async function hasUserPurchasedPick(
  userId: string,
  eventId: string,
  forecastId?: string | null,
): Promise<boolean> {
  if (!isUuid(userId)) return false;
  const hasForecastId = typeof forecastId === 'string' && forecastId.trim().length > 0;
  const { rows } = await pool.query(
    hasForecastId
      ? 'SELECT id FROM rm_user_picks WHERE user_id = $1 AND (event_id = $2 OR forecast_id = $3) LIMIT 1'
      : 'SELECT id FROM rm_user_picks WHERE user_id = $1 AND event_id = $2 LIMIT 1',
    hasForecastId ? [userId, eventId, forecastId] : [userId, eventId]
  );
  return rows.length > 0;
}

export async function recordPick(data: {
  userId: string;
  forecastId: string;
  eventId: string;
  wasRainMan: boolean;
}): Promise<RecordPickResult> {
  if (!isUuid(data.userId)) {
    throw new Error(`Invalid userId for recordPick: ${data.userId}`);
  }
  const { rows } = await pool.query(
    `WITH inserted AS (
       INSERT INTO rm_user_picks (user_id, forecast_id, event_id, was_weatherman)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (user_id, event_id) DO NOTHING
       RETURNING *, TRUE AS inserted
     )
     SELECT * FROM inserted
     UNION ALL
     SELECT p.*, FALSE AS inserted
     FROM rm_user_picks p
     WHERE p.user_id = $1
       AND p.event_id = $3
       AND NOT EXISTS (SELECT 1 FROM inserted)
     LIMIT 1`,
    [data.userId, data.forecastId, data.eventId, data.wasRainMan]
  );
  return rows[0];
}

export async function deletePick(userId: string, eventId: string): Promise<void> {
  if (!isUuid(userId)) return;
  await pool.query(
    'DELETE FROM rm_user_picks WHERE user_id = $1 AND event_id = $2',
    [userId, eventId]
  );
}

export async function getUserPicks(userId: string, limit = 50): Promise<any[]> {
  if (!isUuid(userId)) return [];
  const { rows } = await pool.query(
    `SELECT p.*,
       COALESCE(c.home_team, e.home_team) AS home_team,
       COALESCE(c.away_team, e.away_team) AS away_team,
       COALESCE(c.league, e.league) AS league,
       c.confidence_score,
       COALESCE(c.starts_at, e.starts_at) AS starts_at
     FROM rm_user_picks p
     LEFT JOIN rm_forecast_cache c ON p.forecast_id = c.id
     LEFT JOIN rm_events e ON p.event_id = e.event_id
     WHERE p.user_id = $1
     ORDER BY p.created_at DESC LIMIT $2`,
    [userId, limit]
  );
  return rows;
}

export async function getPickCount(userId: string): Promise<number> {
  if (!isUuid(userId)) return 0;
  const { rows } = await pool.query(
    'SELECT COUNT(*) as count FROM rm_user_picks WHERE user_id = $1',
    [userId]
  );
  return parseInt(rows[0].count, 10);
}