import pool from '../db';
import type { PoolClient } from 'pg';
import { recordLedgerEntry } from './ledger';

export const NEW_ACCOUNT_FORECASTS = 30;
export const SURVEY_BONUS_FORECASTS = 30;
export const DAILY_FORECAST_GRANT = 10;

export interface RmUser {
  id: string;
  email: string;
  password_hash: string;
  google_sub: string | null;
  is_weatherman: boolean;
  monthly_pass_start: string | null;
  monthly_pass_expires: string | null;
  monthly_pass_renewal_reminder_sent_at: string | null;
  single_picks: number;
  signup_bonus_forecasts: number;
  survey_bonus_forecasts: number;
  daily_pass_picks: number;
  daily_pass_date: string | null;
  stripe_customer_id: string | null;
  email_verified: boolean;
  email_verified_at: string | null;
  verification_token: string | null;
  verification_expires: string | null;
  verification_email_last_sent_at: string | null;
  password_reset_token: string | null;
  password_reset_expires: string | null;
  daily_free_forecasts: number;
  last_reset_date_pacific: string | null;
  marketing_consent: boolean;
  country_code: string | null;
  country_source: string | null;
  last_free_pick_date: string | null;
  last_free_pick_event_id: string | null;
  affiliate_code: string | null;
  affiliate_tracking_id: string | null;
  affiliate_signup_tracked_at: string | null;
  affiliate_purchase_tracked_at: string | null;
  preferences: Record<string, any> | null;
  signup_ip: string | null;
  last_login_ip: string | null;
  grace_started_at: string | null;
  grace_expires_at: string | null;
  email_changed_at: string | null;
  previous_email_hash: string | null;
  tourist_pass_expires_at: string | null;
  tourist_pass_timezone: string | null;
  tourist_pass_timezone_source: string | null;
  auth_token_version: number;
  created_at: string;
  updated_at: string;
}

export interface RmUserProfile {
  id: string;
  user_id: string;
  favorite_sports_ranked: string[] | null;
  interest_buckets: string[] | null;
  market_participant: string | null;
  profile_version: number;
  created_at: string;
  updated_at: string;
}

export async function recordLoginIp(userId: string, ip: string, isSignup = false): Promise<void> {
  if (isSignup) {
    await pool.query(
      'UPDATE rm_users SET signup_ip = $1, last_login_ip = $1, updated_at = NOW() WHERE id = $2',
      [ip, userId]
    );
  } else {
    await pool.query(
      'UPDATE rm_users SET last_login_ip = $1, updated_at = NOW() WHERE id = $2',
      [ip, userId]
    );
  }
}

export async function findUserByEmail(email: string): Promise<RmUser | null> {
  const { rows } = await pool.query(
    `SELECT *
     FROM rm_users
     WHERE lower(email) = lower($1)
     ORDER BY email_verified DESC, created_at ASC
     LIMIT 1`,
    [email],
  );
  return await normalizeMonthlyPassStatus(rows[0] || null);
}

export async function findUserByGoogleSub(googleSub: string): Promise<RmUser | null> {
  const { rows } = await pool.query('SELECT * FROM rm_users WHERE google_sub = $1', [googleSub]);
  return await normalizeMonthlyPassStatus(rows[0] || null);
}

export async function findUserById(id: string): Promise<RmUser | null> {
  const { rows } = await pool.query('SELECT * FROM rm_users WHERE id = $1', [id]);
  return await normalizeMonthlyPassStatus(rows[0] || null);
}

export async function createUser(
  email: string,
  passwordHash: string,
  marketingConsent = false,
  countryCode: string | null = null,
  countrySource: string = 'unknown',
): Promise<RmUser> {
  // Seed new accounts with a one-time signup bonus immediately.
  // Mark the current ET reset date so if they burn through it on day one,
  // the standard daily reset still waits until the next ET cycle.
  const todayET = getETDateString();
  const { rows } = await pool.query(
    `INSERT INTO rm_users (email, password_hash, marketing_consent, country_code, country_source,
       signup_bonus_forecasts, last_reset_date_pacific, grace_started_at, grace_expires_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7::date, NOW(), NOW() + INTERVAL '48 hours') RETURNING *`,
    [email.toLowerCase(), passwordHash, marketingConsent, countryCode, countrySource, NEW_ACCOUNT_FORECASTS, todayET]
  );
  return rows[0];
}

export async function updateUserStripeCustomer(userId: string, stripeCustomerId: string): Promise<void> {
  await pool.query(
    'UPDATE rm_users SET stripe_customer_id = $1, updated_at = NOW() WHERE id = $2',
    [stripeCustomerId, userId]
  );
}

export async function creditSinglePicks(userId: string, count: number): Promise<void> {
  await pool.query(
    'UPDATE rm_users SET single_picks = single_picks + $1, updated_at = NOW() WHERE id = $2',
    [count, userId]
  );
}

/**
 * Compute the next 2:00 a.m. in the given IANA timezone, returned as UTC ISO string.
 * If before 2 AM local → expires at 2 AM today (short pass).
 * If after 2 AM local  → expires at 2 AM tomorrow.
 */
export function computeExpiresAtUtc(ianaTimezone: string): string {
  const now = new Date();
  // Get current local time in the target timezone
  const localStr = now.toLocaleString('en-US', { timeZone: ianaTimezone });
  const localNow = new Date(localStr);
  const localHour = localNow.getHours();

  // Build target 2:00 AM local
  const target = new Date(localNow);
  if (localHour >= 2) {
    // After 2 AM → next day at 2 AM
    target.setDate(target.getDate() + 1);
  }
  target.setHours(2, 0, 0, 0);

  // Convert back to UTC: offset = (real_now - local_now_as_date)
  const offsetMs = now.getTime() - localNow.getTime();
  return new Date(target.getTime() + offsetMs).toISOString();
}

export type TzSource = 'ACCOUNT' | 'CLIENT' | 'IP' | 'DEFAULT';

export async function creditDailyPass(
  userId: string,
  picks: number,
  timezone: string = 'America/New_York',
  tzSource: TzSource = 'DEFAULT',
): Promise<{ expiresAtUtc: string }> {
  const expiresAtUtc = computeExpiresAtUtc(timezone);

  await pool.query(
    `UPDATE rm_users
     SET daily_pass_picks = $1,
         daily_pass_date = (NOW() AT TIME ZONE 'America/New_York')::date,
         tourist_pass_expires_at = $3,
         tourist_pass_timezone = $4,
         tourist_pass_timezone_source = $5,
         updated_at = NOW()
     WHERE id = $2`,
    [picks, userId, expiresAtUtc, timezone, tzSource]
  );

  return { expiresAtUtc };
}

export async function creditMonthlyPass(userId: string): Promise<void> {
  // Extend from the current expiry when renewing early so users do not lose paid time.
  await pool.query(
    `UPDATE rm_users
     SET is_weatherman = TRUE,
         monthly_pass_start = COALESCE(monthly_pass_start, NOW()),
         monthly_pass_expires = GREATEST(COALESCE(monthly_pass_expires, NOW()), NOW()) + INTERVAL '30 days',
         monthly_pass_renewal_reminder_sent_at = NULL,
         updated_at = NOW()
     WHERE id = $1`,
    [userId]
  );
}

function isExpiredMonthlyPass(user: Pick<RmUser, 'is_weatherman' | 'monthly_pass_expires'> | null | undefined): boolean {
  if (!user?.is_weatherman || !user.monthly_pass_expires) return false;
  const expiresAt = new Date(user.monthly_pass_expires);
  return !Number.isNaN(expiresAt.getTime()) && expiresAt <= new Date();
}

async function expireMonthlyPassIfNeeded(userId: string): Promise<boolean> {
  const { rows } = await pool.query(
    `UPDATE rm_users
     SET is_weatherman = FALSE,
         monthly_pass_start = NULL,
         monthly_pass_expires = NULL,
         updated_at = NOW()
     WHERE id = $1
       AND is_weatherman = TRUE
       AND monthly_pass_expires IS NOT NULL
       AND monthly_pass_expires <= NOW()
     RETURNING id`,
    [userId],
  );
  return rows.length > 0;
}

async function normalizeMonthlyPassStatus<T extends RmUser | null>(user: T): Promise<T> {
  if (!user || !isExpiredMonthlyPass(user)) return user;

  await expireMonthlyPassIfNeeded(user.id);
  return {
    ...user,
    is_weatherman: false,
    monthly_pass_start: null,
    monthly_pass_expires: null,
  } as T;
}

// ---------- Verification ----------

export async function setVerificationToken(userId: string, token: string, expires: Date): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET verification_token = $1, verification_expires = $2, updated_at = NOW() WHERE id = $3`,
    [token, expires.toISOString(), userId]
  );
}

export async function reserveVerificationEmailCooldown(userId: string, cooldownSeconds = 60): Promise<string | null> {
  const { rows } = await pool.query(
    `UPDATE rm_users
     SET verification_email_last_sent_at = NOW(),
         updated_at = NOW()
     WHERE id = $1
       AND (
         verification_email_last_sent_at IS NULL
         OR verification_email_last_sent_at <= NOW() - make_interval(secs => $2::int)
       )
     RETURNING verification_email_last_sent_at::text as verification_email_last_sent_at`,
    [userId, cooldownSeconds],
  );
  return rows[0]?.verification_email_last_sent_at || null;
}

export async function releaseVerificationEmailCooldown(userId: string, reservedAt: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users
     SET verification_email_last_sent_at = NULL,
         updated_at = NOW()
     WHERE id = $1
       AND verification_email_last_sent_at = $2::timestamptz`,
    [userId, reservedAt],
  );
}

export async function verifyEmail(userId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET email_verified = TRUE, email_verified_at = NOW(),
     verification_token = NULL, verification_expires = NULL,
     verification_email_last_sent_at = NULL, updated_at = NOW()
     WHERE id = $1`,
    [userId]
  );
}

export async function linkGoogleAccount(userId: string, googleSub: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users
     SET google_sub = $1,
         email_verified = TRUE,
         email_verified_at = COALESCE(email_verified_at, NOW()),
         verification_token = NULL,
         verification_expires = NULL,
         verification_email_last_sent_at = NULL,
         updated_at = NOW()
     WHERE id = $2`,
    [googleSub, userId],
  );
}

export async function findByVerificationToken(token: string): Promise<RmUser | null> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_users WHERE verification_token = $1 AND verification_expires > NOW()`,
    [token]
  );
  return rows[0] || null;
}

export async function setPasswordResetToken(userId: string, token: string, expires: Date): Promise<void> {
  await pool.query(
    `UPDATE rm_users
     SET password_reset_token = $1,
         password_reset_expires = $2,
         updated_at = NOW()
     WHERE id = $3`,
    [token, expires.toISOString(), userId],
  );
}

export async function findByPasswordResetToken(token: string): Promise<RmUser | null> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_users
     WHERE password_reset_token = $1
       AND password_reset_expires IS NOT NULL
       AND password_reset_expires > NOW()`,
    [token],
  );
  return await normalizeMonthlyPassStatus(rows[0] || null);
}

export async function resetPassword(userId: string, newPasswordHash: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users
     SET password_hash = $1,
         auth_token_version = COALESCE(auth_token_version, 0) + 1,
         password_reset_token = NULL,
         password_reset_expires = NULL,
         updated_at = NOW()
     WHERE id = $2`,
    [newPasswordHash, userId],
  );
}

export async function setMarketingConsent(userId: string, consent: boolean): Promise<void> {
  await pool.query(
    'UPDATE rm_users SET marketing_consent = $1, updated_at = NOW() WHERE id = $2',
    [consent, userId]
  );
}

// ---------- Daily Free Forecasts ----------

export function getETDateString(): string {
  return new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });
}

/**
 * Get the next reset time. If user has a stored tourist_pass_expires_at, return that.
 * Otherwise compute next 2 AM ET as fallback (for daily free grants).
 */
function getNextResetET(touristExpiresAt?: string | null): string {
  if (touristExpiresAt && new Date(touristExpiresAt) > new Date()) {
    return touristExpiresAt;
  }
  // Fallback: next 2 AM ET
  const now = new Date();
  const etNow = new Date(now.toLocaleString('en-US', { timeZone: 'America/New_York' }));
  const reset = new Date(etNow);
  if (etNow.getHours() < 2) {
    reset.setHours(2, 0, 0, 0);
  } else {
    reset.setDate(reset.getDate() + 1);
    reset.setHours(2, 0, 0, 0);
  }
  const offsetMs = now.getTime() - etNow.getTime();
  return new Date(reset.getTime() + offsetMs).toISOString();
}

/**
 * Is the daily pass still valid?
 * Primary: check tourist_pass_expires_at UTC timestamp (new system).
 * Fallback: legacy date-based check for passes purchased before migration.
 */
function isDailyPassValid(passDate: any, expiresAt?: string | null): boolean {
  // New system: deterministic UTC expiration
  if (expiresAt) {
    return new Date(expiresAt) > new Date();
  }

  // Legacy fallback for pre-migration passes
  if (!passDate) return false;
  const passDateStr = passDate instanceof Date
    ? passDate.toISOString().split('T')[0]
    : String(passDate);

  const todayET = getETDateString();
  if (passDateStr === todayET) return true;

  const etNow = new Date(new Date().toLocaleString('en-US', { timeZone: 'America/New_York' }));
  if (etNow.getHours() < 2) {
    const yesterday = new Date(etNow);
    yesterday.setDate(yesterday.getDate() - 1);
    const yesterdayStr = yesterday.toISOString().split('T')[0];
    if (passDateStr === yesterdayStr) return true;
  }

  return false;
}

export async function ensureDailyGrant(userId: string): Promise<void> {
  const todayET = getETDateString();

  const { rows } = await pool.query(
    `SELECT email_verified, last_reset_date_pacific, daily_free_forecasts,
            signup_bonus_forecasts, survey_bonus_forecasts, grace_expires_at
     FROM rm_users
     WHERE id = $1`,
    [userId]
  );
  if (!rows[0]) return;

  const user = rows[0];
  const inGrace = !user.email_verified && user.grace_expires_at && new Date() < new Date(user.grace_expires_at);
  if (!user.email_verified && !inGrace) return;

  // Users must complete at least one survey before daily grants are enabled
  const { rows: surveyCheck } = await pool.query(
    `SELECT 1 FROM rm_survey_responses WHERE user_id = $1 LIMIT 1`,
    [userId]
  );
  if (surveyCheck.length === 0) return;
  if ((user.signup_bonus_forecasts || 0) > 0) return;
  if ((user.survey_bonus_forecasts || 0) > 0) return;

  // Column is named last_reset_date_pacific but now tracks ET date
  const lastReset = user.last_reset_date_pacific;
  const lastResetStr = lastReset instanceof Date
    ? lastReset.toISOString().split('T')[0]
    : (lastReset || '');

  if (lastResetStr === todayET) return;

  // Grant the standard daily verified-user reset amount.
  const { rows: updated } = await pool.query(
    `UPDATE rm_users SET daily_free_forecasts = $3, last_reset_date_pacific = $1, updated_at = NOW()
     WHERE id = $2 AND (last_reset_date_pacific IS NULL OR last_reset_date_pacific < $1::date)
     RETURNING daily_free_forecasts`,
    [todayET, userId, DAILY_FORECAST_GRANT]
  );

  if (updated.length > 0) {
    await recordLedgerEntry(userId, DAILY_FORECAST_GRANT, 'DAILY_GRANT', DAILY_FORECAST_GRANT, { date: todayET });
  }
}

// ---------- Pick Balance ----------

export interface PickBalance {
  single_picks: number;
  signup_bonus_forecasts: number;
  survey_bonus_forecasts: number;
  daily_pass_picks: number;
  daily_pass_valid: boolean;
  daily_free_forecasts: number;
  email_verified: boolean;
  next_reset_at: string;
  tourist_pass_expires_at: string | null;
  tourist_pass_timezone: string | null;
}

export async function getPickBalance(userId: string): Promise<PickBalance> {
  await expireMonthlyPassIfNeeded(userId);
  await ensureDailyGrant(userId);

  const { rows } = await pool.query(
    `SELECT single_picks, signup_bonus_forecasts, survey_bonus_forecasts,
            daily_pass_picks, daily_pass_date, daily_free_forecasts,
            email_verified, tourist_pass_expires_at, tourist_pass_timezone
     FROM rm_users
     WHERE id = $1`,
    [userId]
  );
  if (!rows[0]) {
    return {
      single_picks: 0,
      signup_bonus_forecasts: 0,
      survey_bonus_forecasts: 0,
      daily_pass_picks: 0,
      daily_pass_valid: false,
      daily_free_forecasts: 0,
      email_verified: false,
      next_reset_at: getNextResetET(),
      tourist_pass_expires_at: null,
      tourist_pass_timezone: null,
    };
  }

  const user = rows[0];
  const passValid = isDailyPassValid(user.daily_pass_date, user.tourist_pass_expires_at);

  return {
    single_picks: user.single_picks,
    signup_bonus_forecasts: user.signup_bonus_forecasts || 0,
    survey_bonus_forecasts: user.survey_bonus_forecasts || 0,
    daily_pass_picks: passValid ? user.daily_pass_picks : 0,
    daily_pass_valid: !!passValid && user.daily_pass_picks > 0,
    daily_free_forecasts: user.daily_free_forecasts || 0,
    email_verified: user.email_verified || false,
    next_reset_at: getNextResetET(user.tourist_pass_expires_at),
    tourist_pass_expires_at: user.tourist_pass_expires_at || null,
    tourist_pass_timezone: user.tourist_pass_timezone || null,
  };
}

// ---------- Deduct Pick (priority: signup_bonus > survey_bonus > daily_free > daily_pass > single) ----------

export type DeductSource = 'daily_free' | 'daily_pass' | 'signup_bonus' | 'survey_bonus' | 'single_pick';

async function deductPickWithClient(
  client: PoolClient,
  userId: string,
): Promise<{ success: boolean; source?: DeductSource }> {
  const { rows } = await client.query(
    `SELECT daily_free_forecasts, daily_pass_picks, daily_pass_date, tourist_pass_expires_at,
            signup_bonus_forecasts, survey_bonus_forecasts, single_picks
     FROM rm_users
     WHERE id = $1
     FOR UPDATE`,
    [userId],
  );

  const user = rows[0];
  if (!user) {
    return { success: false };
  }

  if ((user.signup_bonus_forecasts || 0) > 0) {
    await client.query(
      `UPDATE rm_users
       SET signup_bonus_forecasts = signup_bonus_forecasts - 1, updated_at = NOW()
       WHERE id = $1`,
      [userId],
    );
    return { success: true, source: 'signup_bonus' };
  }

  if ((user.survey_bonus_forecasts || 0) > 0) {
    await client.query(
      `UPDATE rm_users
       SET survey_bonus_forecasts = survey_bonus_forecasts - 1, updated_at = NOW()
       WHERE id = $1`,
      [userId],
    );
    return { success: true, source: 'survey_bonus' };
  }

  if ((user.daily_free_forecasts || 0) > 0) {
    await client.query(
      `UPDATE rm_users
       SET daily_free_forecasts = daily_free_forecasts - 1, updated_at = NOW()
       WHERE id = $1`,
      [userId],
    );
    return { success: true, source: 'daily_free' };
  }

  const passValid = isDailyPassValid(user.daily_pass_date, user.tourist_pass_expires_at);
  if (passValid && (user.daily_pass_picks || 0) > 0) {
    await client.query(
      `UPDATE rm_users
       SET daily_pass_picks = daily_pass_picks - 1, updated_at = NOW()
       WHERE id = $1`,
      [userId],
    );
    return { success: true, source: 'daily_pass' };
  }

  if ((user.single_picks || 0) > 0) {
    await client.query(
      `UPDATE rm_users
       SET single_picks = single_picks - 1, updated_at = NOW()
       WHERE id = $1`,
      [userId],
    );
    return { success: true, source: 'single_pick' };
  }

  return { success: false };
}

export async function deductPick(userId: string): Promise<{ success: boolean; source?: DeductSource }> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await deductPickWithClient(client, userId);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

// ---------- Weatherman ----------

export async function setRainMan(userId: string, isWeatherman: boolean): Promise<void> {
  await pool.query(
    'UPDATE rm_users SET is_weatherman = $1, updated_at = NOW() WHERE id = $2',
    [isWeatherman, userId]
  );
}

export async function getAllRainMen(): Promise<RmUser[]> {
  const { rows } = await pool.query('SELECT * FROM rm_users WHERE is_weatherman = TRUE ORDER BY email');
  return rows;
}

// ---------- Legacy free pick (kept for compatibility but unused by new flow) ----------

export async function hasUsedFreePick(userId: string): Promise<{ used: boolean; eventId: string | null }> {
  const { rows } = await pool.query(
    `SELECT last_free_pick_date, last_free_pick_event_id FROM rm_users WHERE id = $1`,
    [userId]
  );
  if (!rows[0]) return { used: false, eventId: null };
  const today = new Date().toISOString().split('T')[0];
  const pickDate = rows[0].last_free_pick_date;
  const dateStr = pickDate instanceof Date ? pickDate.toISOString().split('T')[0] : pickDate;
  const used = dateStr === today;
  return { used, eventId: used ? rows[0].last_free_pick_event_id : null };
}

export async function recordFreePick(userId: string, eventId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET last_free_pick_date = (NOW() AT TIME ZONE 'America/New_York')::date, last_free_pick_event_id = $1, updated_at = NOW() WHERE id = $2`,
    [eventId, userId]
  );
}

// ---------- Affiliate Attribution ----------

export async function updateAffiliateAttribution(
  userId: string,
  code: string,
  trackingId: string,
): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET affiliate_code = $1, affiliate_tracking_id = $2, updated_at = NOW()
     WHERE id = $3 AND affiliate_code IS NULL`,
    [code, trackingId, userId]
  );
}

export async function markAffiliateSignupTracked(userId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET affiliate_signup_tracked_at = NOW(), updated_at = NOW() WHERE id = $1`,
    [userId]
  );
}

export async function markAffiliatePurchaseTracked(userId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_users SET affiliate_purchase_tracked_at = NOW(), updated_at = NOW() WHERE id = $1`,
    [userId]
  );
}

// ---------- Grace Period Helpers ----------

export function isInGracePeriod(user: RmUser): boolean {
  return !user.email_verified && !!user.grace_expires_at && new Date() < new Date(user.grace_expires_at);
}

export function needsGraceVerification(user: RmUser): boolean {
  return !user.email_verified && !!user.grace_expires_at && new Date() >= new Date(user.grace_expires_at);
}

// ---------- Credential Update ----------

export async function updateCredentials(
  userId: string,
  newEmail: string,
  newPasswordHash: string,
  oldEmailHash?: string,
): Promise<number> {
  if (oldEmailHash) {
    const { rows } = await pool.query(
      `UPDATE rm_users SET email = $1, password_hash = $2, email_changed_at = NOW(),
       previous_email_hash = $3, email_verified = FALSE, email_verified_at = NULL,
       auth_token_version = COALESCE(auth_token_version, 0) + 1,
       updated_at = NOW() WHERE id = $4
       RETURNING auth_token_version`,
      [newEmail.toLowerCase(), newPasswordHash, oldEmailHash, userId]
    );
    return Number(rows[0]?.auth_token_version ?? 0);
  } else {
    const { rows } = await pool.query(
      `UPDATE rm_users
       SET password_hash = $1,
           auth_token_version = COALESCE(auth_token_version, 0) + 1,
           updated_at = NOW()
       WHERE id = $2
       RETURNING auth_token_version`,
      [newPasswordHash, userId]
    );
    return Number(rows[0]?.auth_token_version ?? 0);
  }
}

// ---------- User Profile ----------

export async function getProfile(userId: string): Promise<RmUserProfile | null> {
  const { rows } = await pool.query(
    'SELECT * FROM rm_user_profiles WHERE user_id = $1',
    [userId]
  );
  return rows[0] || null;
}

export async function upsertProfile(
  userId: string,
  data: { favoriteSports?: string[]; interestBuckets?: string[]; marketParticipant?: string },
): Promise<RmUserProfile> {
  const { rows } = await pool.query(
    `INSERT INTO rm_user_profiles (user_id, favorite_sports_ranked, interest_buckets, market_participant)
     VALUES ($1, $2, $3, $4)
     ON CONFLICT (user_id) DO UPDATE SET
       favorite_sports_ranked = COALESCE($2, rm_user_profiles.favorite_sports_ranked),
       interest_buckets = COALESCE($3, rm_user_profiles.interest_buckets),
       market_participant = COALESCE($4, rm_user_profiles.market_participant),
       profile_version = rm_user_profiles.profile_version + 1,
       updated_at = NOW()
     RETURNING *`,
    [userId, data.favoriteSports || null, data.interestBuckets || null, data.marketParticipant || null]
  );
  return rows[0];
}