src/db.js

import { Pool } from 'pg';

function mapBet(row) {
  if (!row) {
    return null;
  }

  return {
    id: Number(row.id),
    discordUserId: row.discord_user_id,
    betNumber: Number(row.bet_number),
    book: row.book,
    sport: row.sport,
    oddsInput: row.odds_input,
    normalizedDecimalOdds: Number(row.normalized_decimal_odds),
    prop: row.prop,
    stake: Number(row.stake),
    unitsValue: row.units_value === null ? null : Number(row.units_value),
    status: row.status,
    profitLoss: row.profit_loss === null ? null : Number(row.profit_loss),
    rawInput: row.raw_input,
    createdAt: row.created_at,
    resolvedAt: row.resolved_at,
    deletedAt: row.deleted_at,
    deletedReason: row.deleted_reason,
  };
}

function numberOrNull(value) {
  return value === null || value === undefined ? null : Number(value);
}

function buildWhereClause(filters = {}, options = {}) {
  const clauses = [];
  const values = [];
  let index = 1;

  clauses.push(`discord_user_id = $${index++}`);
  values.push(filters.userId);

  if (!options.includeDeleted) {
    clauses.push('deleted_at IS NULL');
  }

  if (filters.book) {
    clauses.push(`book = $${index++}`);
    values.push(filters.book);
  }

  if (filters.sport) {
    clauses.push(`sport = $${index++}`);
    values.push(filters.sport);
  }

  if (filters.status) {
    if (filters.status === 'deleted') {
      clauses.push('deleted_at IS NOT NULL');
    } else {
      clauses.push(`status = $${index++}`);
      values.push(filters.status);
    }
  }

  if (filters.dateWindow && filters.dateWindow !== 'all') {
    if (filters.dateWindow === 'today') {
      clauses.push('created_at >= DATE_TRUNC(\'day\', NOW())');
    } else if (filters.dateWindow === '7d') {
      clauses.push('created_at >= NOW() - INTERVAL \'7 days\'');
    } else if (filters.dateWindow === '30d') {
      clauses.push('created_at >= NOW() - INTERVAL \'30 days\'');
    }
  }

  return {
    clause: clauses.join(' AND '),
    values,
  };
}

function computeAnalytics(rows, profile) {
  const activeRows = rows.filter((row) => row.deletedAt === null);
  const wins = activeRows.filter((row) => row.status === 'win').length;
  const losses = activeRows.filter((row) => row.status === 'loss').length;
  const voids = activeRows.filter((row) => row.status === 'void').length;
  const openBets = activeRows.filter((row) => row.status === 'open').length;
  const settledRows = activeRows.filter((row) => row.status !== 'open');
  const gradedRows = activeRows.filter((row) => row.status === 'win' || row.status === 'loss');
  const settledStake = gradedRows.reduce((total, row) => total + row.stake, 0);
  const netProfit = settledRows.reduce((total, row) => total + (row.profitLoss ?? 0), 0);
  const totalUnits = settledRows.reduce(
    (total, row) => total + (row.unitsValue ?? deriveUnitsValue(row.stake, profile.unitSize)),
    0
  );
  const bankrollNow = profile.startingBankroll !== null ? profile.startingBankroll + netProfit : null;

  return {
    totalBets: activeRows.length,
    openBets,
    wins,
    losses,
    voids,
    settledStake,
    netProfit,
    roiPercent: settledStake > 0 ? (netProfit / settledStake) * 100 : 0,
    totalUnits,
    unitSize: profile.unitSize,
    startingBankroll: profile.startingBankroll,
    bankrollNow,
    winRatePercent: gradedRows.length > 0 ? (wins / gradedRows.length) * 100 : 0,
  };
}

function deriveUnitsValue(stake, unitSize) {
  if (!unitSize || unitSize <= 0) {
    return 0;
  }

  return Number((stake / unitSize).toFixed(4));
}

function computeStreak(rows) {
  const settled = rows
    .filter((row) => row.deletedAt === null && (row.status === 'win' || row.status === 'loss'))
    .sort((left, right) => left.betNumber - right.betNumber);

  if (settled.length === 0) {
    return { type: 'none', count: 0 };
  }

  const last = settled[settled.length - 1].status;
  let count = 0;
  for (let index = settled.length - 1; index >= 0; index -= 1) {
    if (settled[index].status !== last) {
      break;
    }
    count += 1;
  }

  return { type: last, count };
}

function computeBestWorst(rows) {
  const settled = rows.filter((row) => row.deletedAt === null && row.profitLoss !== null);
  if (settled.length === 0) {
    return { best: null, worst: null };
  }

  const best = settled.reduce((top, row) => (row.profitLoss > top.profitLoss ? row : top), settled[0]);
  const worst = settled.reduce((low, row) => (row.profitLoss < low.profitLoss ? row : low), settled[0]);

  return { best, worst };
}

export class BetStore {
  constructor(databaseUrl, options = {}) {
    const usesSsl = /sslmode=(require|verify-ca|verify-full)/i.test(databaseUrl);
    this.pool = options.pool ?? new Pool({
      connectionString: databaseUrl,
      ssl: usesSsl ? { rejectUnauthorized: false } : undefined,
    });
  }

  async initialize() {
    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS users (
        discord_user_id TEXT PRIMARY KEY,
        username TEXT NOT NULL,
        display_name TEXT NOT NULL,
        next_bet_number INT NOT NULL DEFAULT 1,
        starting_bankroll DOUBLE PRECISION,
        unit_size DOUBLE PRECISION,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    `);

    await this.pool.query(`
      ALTER TABLE users ADD COLUMN IF NOT EXISTS username TEXT NOT NULL DEFAULT '';
      ALTER TABLE users ADD COLUMN IF NOT EXISTS display_name TEXT NOT NULL DEFAULT '';
      ALTER TABLE users ADD COLUMN IF NOT EXISTS next_bet_number INT NOT NULL DEFAULT 1;
      ALTER TABLE users ADD COLUMN IF NOT EXISTS starting_bankroll DOUBLE PRECISION;
      ALTER TABLE users ADD COLUMN IF NOT EXISTS unit_size DOUBLE PRECISION;
      ALTER TABLE users ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
      ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS bets (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        discord_user_id TEXT NOT NULL REFERENCES users(discord_user_id),
        bet_number INT NOT NULL,
        book TEXT NOT NULL,
        sport TEXT NOT NULL DEFAULT 'Other',
        odds_input TEXT NOT NULL,
        normalized_decimal_odds DOUBLE PRECISION NOT NULL,
        prop TEXT NOT NULL,
        stake DOUBLE PRECISION NOT NULL,
        units_value DOUBLE PRECISION,
        status TEXT NOT NULL DEFAULT 'open',
        profit_loss DOUBLE PRECISION,
        raw_input TEXT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        resolved_at TIMESTAMPTZ,
        deleted_at TIMESTAMPTZ,
        deleted_reason TEXT,
        CONSTRAINT bets_user_bet_number_unique UNIQUE (discord_user_id, bet_number)
      );
    `);

    await this.pool.query(`
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS discord_user_id TEXT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS bet_number INT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS book TEXT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS sport TEXT NOT NULL DEFAULT 'Other';
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS odds_input TEXT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS normalized_decimal_odds DOUBLE PRECISION;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS prop TEXT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS stake DOUBLE PRECISION;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS units_value DOUBLE PRECISION;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'open';
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS profit_loss DOUBLE PRECISION;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS raw_input TEXT;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMPTZ;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
      ALTER TABLE bets ADD COLUMN IF NOT EXISTS deleted_reason TEXT;
    `);

    await this.pool.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS bets_user_bet_number_unique
      ON bets (discord_user_id, bet_number);
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS scan_runs (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        scan_type TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'completed',
        captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        entry_count INT NOT NULL DEFAULT 0,
        error_text TEXT
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS scan_markets (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        scan_run_id INT NOT NULL REFERENCES scan_runs(id) ON DELETE CASCADE,
        market_key TEXT NOT NULL,
        source TEXT NOT NULL,
        book TEXT NOT NULL,
        event_name TEXT,
        event_commence_time TIMESTAMPTZ,
        player_name TEXT NOT NULL,
        market_type TEXT NOT NULL,
        market_label TEXT NOT NULL,
        side TEXT NOT NULL,
        line_value DOUBLE PRECISION,
        odds_input TEXT NOT NULL,
        implied_probability DOUBLE PRECISION NOT NULL,
        raw_label TEXT,
        captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    `);

    await this.pool.query(`
      ALTER TABLE scan_markets ADD COLUMN IF NOT EXISTS event_commence_time TIMESTAMPTZ;
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS scan_reports (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        report_date DATE NOT NULL,
        report_type TEXT NOT NULL,
        channel_id TEXT NOT NULL,
        primary_message_id TEXT,
        secondary_message_id TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        CONSTRAINT scan_reports_date_type_unique UNIQUE (report_date, report_type)
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS scan_alerts (
        alert_key TEXT PRIMARY KEY,
        last_sent_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS circa_snapshots (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        file_name TEXT NOT NULL,
        file_source TEXT,
        fingerprint TEXT NOT NULL UNIQUE,
        file_date DATE,
        seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        entry_count INT NOT NULL DEFAULT 0
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS circa_snapshot_entries (
        id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        snapshot_id INT NOT NULL REFERENCES circa_snapshots(id) ON DELETE CASCADE,
        market_key TEXT NOT NULL,
        player_name TEXT NOT NULL,
        team TEXT,
        market_type TEXT NOT NULL,
        market_label TEXT NOT NULL,
        side TEXT NOT NULL,
        line_value DOUBLE PRECISION,
        odds_input TEXT NOT NULL,
        implied_probability DOUBLE PRECISION NOT NULL,
        raw_label TEXT
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS circa_daily_posts (
        post_date DATE PRIMARY KEY,
        snapshot_id INT NOT NULL REFERENCES circa_snapshots(id),
        channel_id TEXT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    `);

    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS circa_movement_posts (
        movement_key TEXT PRIMARY KEY,
        last_snapshot_fingerprint TEXT NOT NULL,
        last_sent_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    `);
  }

  async upsertUser(user) {
    await this.pool.query(
      `
        INSERT INTO users (discord_user_id, username, display_name)
        VALUES ($1, $2, $3)
        ON CONFLICT (discord_user_id) DO UPDATE SET
          username = EXCLUDED.username,
          display_name = EXCLUDED.display_name,
          updated_at = NOW()
      `,
      [user.id, user.username, user.displayName ?? user.username]
    );
  }

  async getUserProfile(userId) {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM users
        WHERE discord_user_id = $1
      `,
      [userId]
    );

    if (!rows[0]) {
      return {
        startingBankroll: null,
        unitSize: null,
      };
    }

    return {
      startingBankroll: numberOrNull(rows[0].starting_bankroll),
      unitSize: numberOrNull(rows[0].unit_size),
    };
  }

  async updateUserPerformanceConfig(user, settings) {
    await this.upsertUser(user);
    const current = await this.getUserProfile(user.id);
    const startingBankroll = settings.startingBankroll ?? current.startingBankroll;
    const unitSize = settings.unitSize ?? current.unitSize;

    await this.pool.query(
      `
        UPDATE users
        SET starting_bankroll = $2,
            unit_size = $3,
            username = $4,
            display_name = $5,
            updated_at = NOW()
        WHERE discord_user_id = $1
      `,
      [user.id, startingBankroll, unitSize, user.username, user.displayName ?? user.username]
    );

    if (unitSize !== null && unitSize > 0) {
      await this.pool.query(
        `
          UPDATE bets
          SET units_value = stake / $2
          WHERE discord_user_id = $1
        `,
        [user.id, unitSize]
      );
    }

    return this.getUserProfile(user.id);
  }

  async createBet(user, bet) {
    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');
      await client.query(
        `
          INSERT INTO users (discord_user_id, username, display_name)
          VALUES ($1, $2, $3)
          ON CONFLICT (discord_user_id) DO UPDATE SET
            username = EXCLUDED.username,
            display_name = EXCLUDED.display_name,
            updated_at = NOW()
        `,
        [user.id, user.username, user.displayName ?? user.username]
      );

      const { rows: userRows } = await client.query(
        `
          SELECT unit_size
          FROM users
          WHERE discord_user_id = $1
        `,
        [user.id]
      );
      const unitSize = numberOrNull(userRows[0]?.unit_size);
      const unitsValue = unitSize && unitSize > 0 ? deriveUnitsValue(bet.stake, unitSize) : 0;

      const { rows: counterRows } = await client.query(
        `
          UPDATE users
          SET next_bet_number = next_bet_number + 1,
              username = $2,
              display_name = $3,
              updated_at = NOW()
          WHERE discord_user_id = $1
          RETURNING next_bet_number - 1 AS bet_number
        `,
        [user.id, user.username, user.displayName ?? user.username]
      );
      const betNumber = Number(counterRows[0].bet_number);

      await client.query(
        `
          INSERT INTO bets (
            discord_user_id,
            bet_number,
            book,
            sport,
            odds_input,
            normalized_decimal_odds,
            prop,
            stake,
            units_value,
            raw_input
          )
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        `,
        [
          user.id,
          betNumber,
          bet.book,
          bet.sport,
          bet.oddsInput,
          bet.normalizedDecimalOdds,
          bet.prop,
          bet.stake,
          unitsValue,
          bet.rawInput,
        ]
      );

      await client.query('COMMIT');
      return this.findBet(user.id, betNumber);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async findBet(userId, betNumber, options = {}) {
    const includeDeleted = options.includeDeleted ?? true;
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM bets
        WHERE discord_user_id = $1 AND bet_number = $2
        ${includeDeleted ? '' : 'AND deleted_at IS NULL'}
      `,
      [userId, betNumber]
    );

    return mapBet(rows[0]);
  }

  async updateBet(userId, betNumber, patch) {
    const existingBet = await this.findBet(userId, betNumber);
    if (!existingBet) {
      return { type: 'missing' };
    }

    if (existingBet.deletedAt) {
      return { type: 'deleted', bet: existingBet };
    }

    const attemptsFinancialEdit = [
      patch.oddsInput,
      patch.normalizedDecimalOdds,
      patch.stake,
      patch.unitsValue,
    ].some((value) => value !== undefined);

    if (existingBet.status !== 'open' && attemptsFinancialEdit) {
      return { type: 'financial_locked', bet: existingBet };
    }

    const updates = [];
    const values = [];
    let index = 1;

    for (const [column, value] of Object.entries({
      book: patch.book,
      sport: patch.sport,
      prop: patch.prop,
      odds_input: patch.oddsInput,
      normalized_decimal_odds: patch.normalizedDecimalOdds,
      stake: patch.stake,
      units_value: patch.unitsValue,
      raw_input: patch.rawInput,
    })) {
      if (value !== undefined) {
        updates.push(`${column} = $${index++}`);
        values.push(value);
      }
    }

    if (updates.length === 0) {
      return { type: 'no_changes', bet: existingBet };
    }

    values.push(userId, betNumber);
    await this.pool.query(
      `
        UPDATE bets
        SET ${updates.join(', ')}
        WHERE discord_user_id = $${index++} AND bet_number = $${index} AND deleted_at IS NULL
      `,
      values
    );

    return { type: 'updated', bet: await this.findBet(userId, betNumber) };
  }

  async softDeleteBet(userId, betNumber, reason = null) {
    const existingBet = await this.findBet(userId, betNumber);
    if (!existingBet) {
      return { type: 'missing' };
    }

    if (existingBet.deletedAt) {
      return { type: 'already_deleted', bet: existingBet };
    }

    await this.pool.query(
      `
        UPDATE bets
        SET deleted_at = NOW(),
            deleted_reason = $3
        WHERE discord_user_id = $1 AND bet_number = $2
      `,
      [userId, betNumber, reason]
    );

    return { type: 'deleted', bet: await this.findBet(userId, betNumber) };
  }

  async resolveBet(userId, betNumber, result) {
    const existingBet = await this.findBet(userId, betNumber);
    if (!existingBet) {
      return { type: 'missing' };
    }

    if (existingBet.deletedAt) {
      return { type: 'deleted', bet: existingBet };
    }

    if (existingBet.status !== 'open') {
      return { type: 'already_resolved', bet: existingBet };
    }

    const profitLoss = calculateProfitLoss(existingBet.stake, existingBet.normalizedDecimalOdds, result);
    await this.pool.query(
      `
        UPDATE bets
        SET status = $1, profit_loss = $2, resolved_at = NOW()
        WHERE discord_user_id = $3 AND bet_number = $4 AND status = 'open' AND deleted_at IS NULL
      `,
      [result, profitLoss, userId, betNumber]
    );

    return { type: 'resolved', bet: await this.findBet(userId, betNumber) };
  }

  async getBetRows(userId, filters = {}, options = {}) {
    const { clause, values } = buildWhereClause({ ...filters, userId }, options);
    const limitClause = options.limit ? ` LIMIT ${Number(options.limit)}` : '';
    const offsetClause = options.offset ? ` OFFSET ${Number(options.offset)}` : '';
    const orderClause = options.orderBy ?? 'bet_number DESC';

    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM bets
        WHERE ${clause}
        ORDER BY ${orderClause}
        ${limitClause}
        ${offsetClause}
      `,
      values
    );

    return rows.map(mapBet);
  }

  async getFilteredCount(userId, filters = {}, options = {}) {
    const { clause, values } = buildWhereClause({ ...filters, userId }, options);
    const { rows } = await this.pool.query(
      `
        SELECT COUNT(*) AS count
        FROM bets
        WHERE ${clause}
      `,
      values
    );

    return Number(rows[0].count ?? 0);
  }

  async getUserSummary(userId, filters = {}) {
    const rows = await this.getBetRows(userId, filters, { orderBy: 'bet_number ASC' });
    const profile = await this.getUserProfile(userId);
    const analytics = computeAnalytics(rows, profile);
    const streak = computeStreak(rows);
    const { best, worst } = computeBestWorst(rows);

    return {
      ...analytics,
      streak,
      bestBet: best,
      worstBet: worst,
    };
  }

  async getRecentBets(userId, filters = {}, page = 1, limit = 5) {
    const offset = Math.max(0, page - 1) * limit;
    const bets = await this.getBetRows(userId, filters, {
      limit,
      offset,
      orderBy: 'bet_number DESC',
      includeDeleted: filters.status === 'deleted',
    });
    const totalCount = await this.getFilteredCount(userId, filters, {
      includeDeleted: filters.status === 'deleted',
    });

    return {
      bets,
      totalCount,
      currentPage: page,
      totalPages: Math.max(1, Math.ceil(totalCount / limit)),
    };
  }

  async getChartPoints(userId, filters = {}) {
    const rows = await this.getBetRows(userId, filters, { orderBy: 'bet_number ASC' });
    let runningProfit = 0;

    return rows
      .filter((row) => row.deletedAt === null && row.status !== 'open')
      .map((row) => {
        runningProfit += Number(row.profitLoss ?? 0);
        return {
          betNumber: row.betNumber,
          status: row.status,
          profitLoss: Number(row.profitLoss ?? 0),
          runningProfit,
          label: `#${row.betNumber}`,
        };
      });
  }

  async getBookBreakdown(userId, filters = {}) {
    const rows = await this.getBetRows(userId, filters, { orderBy: 'bet_number ASC' });
    return computeGroupedBreakdown(rows, 'book');
  }

  async getSportBreakdown(userId, filters = {}) {
    const rows = await this.getBetRows(userId, filters, { orderBy: 'bet_number ASC' });
    return computeGroupedBreakdown(rows, 'sport');
  }

  async exportBets(userId, filters = {}) {
    return this.getBetRows(userId, filters, {
      orderBy: 'bet_number ASC',
      includeDeleted: filters.status === 'deleted',
    });
  }

  async recordMarketSnapshot(scanType, entries) {
    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');
      const { rows } = await client.query(
        `
          INSERT INTO scan_runs (scan_type, status, entry_count)
          VALUES ($1, 'completed', $2)
          RETURNING id
        `,
        [scanType, entries.length]
      );
      const scanRunId = Number(rows[0].id);

      for (const entry of entries) {
        await client.query(
          `
            INSERT INTO scan_markets (
              scan_run_id,
              market_key,
              source,
              book,
              event_name,
              event_commence_time,
              player_name,
              market_type,
              market_label,
              side,
              line_value,
              odds_input,
              implied_probability,
              raw_label
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
          `,
          [
            scanRunId,
            entry.marketKey,
            entry.source,
            entry.book,
            entry.eventName,
            entry.eventCommenceTime ?? null,
            entry.playerName,
            entry.marketType,
            entry.marketLabel,
            entry.side,
            entry.lineValue,
            entry.oddsInput,
            entry.impliedProbability,
            entry.rawLabel,
          ]
        );
      }

      await client.query('COMMIT');
      return scanRunId;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async getScanRunById(scanRunId) {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM scan_runs
        WHERE id = $1
        LIMIT 1
      `,
      [scanRunId]
    );

    if (!rows[0]) {
      return null;
    }

    const run = rows[0];
    const entriesResult = await this.pool.query(
      `
        SELECT *
        FROM scan_markets
        WHERE scan_run_id = $1
        ORDER BY player_name, market_type, side, line_value NULLS FIRST, book
      `,
      [scanRunId]
    );

    return {
      id: Number(run.id),
      scanType: run.scan_type,
      status: run.status,
      entryCount: Number(run.entry_count ?? 0),
      createdAt: run.created_at?.toISOString?.() ?? String(run.created_at),
      errorText: run.error_text ?? null,
      entries: entriesResult.rows.map((entry) => ({
        marketKey: entry.market_key,
        source: entry.source,
        book: entry.book,
        eventName: entry.event_name,
        eventCommenceTime: entry.event_commence_time?.toISOString?.() ?? (entry.event_commence_time ? String(entry.event_commence_time) : null),
        playerName: entry.player_name,
        marketType: entry.market_type,
        marketLabel: entry.market_label,
        side: entry.side,
        lineValue: numberOrNull(entry.line_value),
        oddsInput: entry.odds_input,
        impliedProbability: Number(entry.implied_probability),
        rawLabel: entry.raw_label ?? null,
      })),
    };
  }

  async getLatestScanRun(scanType = null) {
    const { rows } = await this.pool.query(
      `
        SELECT id
        FROM scan_runs
        ${scanType ? 'WHERE scan_type = $1' : ''}
        ORDER BY created_at DESC, id DESC
        LIMIT 1
      `,
      scanType ? [scanType] : []
    );

    if (!rows[0]) {
      return null;
    }

    return this.getScanRunById(Number(rows[0].id));
  }

  async getPreviousScanRun(scanRunId, scanType = null) {
    const { rows } = await this.pool.query(
      `
        SELECT id
        FROM scan_runs
        WHERE id < $1
        ${scanType ? 'AND scan_type = $2' : ''}
        ORDER BY id DESC
        LIMIT 1
      `,
      scanType ? [scanRunId, scanType] : [scanRunId]
    );

    if (!rows[0]) {
      return null;
    }

    return this.getScanRunById(Number(rows[0].id));
  }

  async hasScanReport(reportDate, reportType) {
    const { rows } = await this.pool.query(
      `
        SELECT 1
        FROM scan_reports
        WHERE report_date = $1 AND report_type = $2
      `,
      [reportDate, reportType]
    );

    return Boolean(rows[0]);
  }

  async recordScanReport(reportDate, reportType, channelId, primaryMessageId, secondaryMessageId = null) {
    await this.pool.query(
      `
        INSERT INTO scan_reports (report_date, report_type, channel_id, primary_message_id, secondary_message_id)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (report_date, report_type) DO UPDATE SET
          channel_id = EXCLUDED.channel_id,
          primary_message_id = EXCLUDED.primary_message_id,
          secondary_message_id = EXCLUDED.secondary_message_id,
          created_at = NOW()
      `,
      [reportDate, reportType, channelId, primaryMessageId, secondaryMessageId]
    );
  }

  async canSendScanAlert(alertKey, cooldownMinutes) {
    const { rows } = await this.pool.query(
      `
        SELECT last_sent_at
        FROM scan_alerts
        WHERE alert_key = $1
      `,
      [alertKey]
    );

    if (!rows[0]) {
      return true;
    }

    const lastSentAt = new Date(rows[0].last_sent_at);
    return Date.now() - lastSentAt.getTime() >= cooldownMinutes * 60 * 1000;
  }

  async recordScanAlert(alertKey) {
    await this.pool.query(
      `
        INSERT INTO scan_alerts (alert_key, last_sent_at)
        VALUES ($1, NOW())
        ON CONFLICT (alert_key) DO UPDATE SET
          last_sent_at = NOW()
      `,
      [alertKey]
    );
  }

  async getLatestCircaSnapshot() {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM circa_snapshots
        ORDER BY seen_at DESC
        LIMIT 1
      `
    );

    if (!rows[0]) {
      return null;
    }

    return this.getCircaSnapshotById(Number(rows[0].id));
  }

  async getPreviousCircaSnapshot(snapshotId) {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM circa_snapshots
        WHERE id < $1
        ORDER BY id DESC
        LIMIT 1
      `,
      [snapshotId]
    );

    if (!rows[0]) {
      return null;
    }

    return this.getCircaSnapshotById(Number(rows[0].id));
  }

  async getCircaSnapshotByFingerprint(fingerprint) {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM circa_snapshots
        WHERE fingerprint = $1
        LIMIT 1
      `,
      [fingerprint]
    );

    if (!rows[0]) {
      return null;
    }

    return this.getCircaSnapshotById(Number(rows[0].id));
  }

  async getCircaSnapshotById(snapshotId) {
    const { rows } = await this.pool.query(
      `
        SELECT *
        FROM circa_snapshots
        WHERE id = $1
        LIMIT 1
      `,
      [snapshotId]
    );

    if (!rows[0]) {
      return null;
    }

    const snapshotRow = rows[0];
    const entryResult = await this.pool.query(
      `
        SELECT *
        FROM circa_snapshot_entries
        WHERE snapshot_id = $1
        ORDER BY market_label, player_name, side, line_value NULLS FIRST
      `,
      [snapshotId]
    );

    return {
      id: Number(snapshotRow.id),
      fileName: snapshotRow.file_name,
      fileSource: snapshotRow.file_source,
      fingerprint: snapshotRow.fingerprint,
      fileDate: snapshotRow.file_date ? new Date(snapshotRow.file_date).toISOString().slice(0, 10) : null,
      seenAt: snapshotRow.seen_at?.toISOString?.() ?? String(snapshotRow.seen_at),
      entryCount: Number(snapshotRow.entry_count),
      entries: entryResult.rows.map((entry) => ({
        marketKey: entry.market_key,
        source: 'circa',
        book: 'Circa',
        eventName: 'Circa MLB',
        eventId: null,
        playerName: entry.player_name,
        team: entry.team,
        marketType: entry.market_type,
        marketLabel: entry.market_label,
        side: entry.side,
        lineValue: numberOrNull(entry.line_value),
        oddsInput: entry.odds_input,
        impliedProbability: Number(entry.implied_probability),
        rawLabel: entry.raw_label,
      })),
    };
  }

  async recordCircaSnapshot(snapshot) {
    const existing = await this.getCircaSnapshotByFingerprint(snapshot.fingerprint);
    if (existing) {
      return existing;
    }

    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');
      const { rows } = await client.query(
        `
          INSERT INTO circa_snapshots (file_name, file_source, fingerprint, file_date, entry_count)
          VALUES ($1, $2, $3, $4, $5)
          ON CONFLICT (fingerprint) DO NOTHING
          RETURNING id
        `,
        [
          snapshot.fileName,
          snapshot.fileSource ?? null,
          snapshot.fingerprint,
          snapshot.fileDate ?? null,
          snapshot.entries.length,
        ]
      );
      if (!rows[0]) {
        await client.query('ROLLBACK');
        return this.getCircaSnapshotByFingerprint(snapshot.fingerprint);
      }

      const snapshotId = Number(rows[0].id);

      for (const entry of snapshot.entries) {
        await client.query(
          `
            INSERT INTO circa_snapshot_entries (
              snapshot_id,
              market_key,
              player_name,
              team,
              market_type,
              market_label,
              side,
              line_value,
              odds_input,
              implied_probability,
              raw_label
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
          `,
          [
            snapshotId,
            entry.marketKey,
            entry.playerName,
            entry.team ?? null,
            entry.marketType,
            entry.marketLabel,
            entry.side,
            entry.lineValue ?? null,
            entry.oddsInput,
            entry.impliedProbability,
            entry.rawLabel ?? null,
          ]
        );
      }

      await client.query('COMMIT');
      return this.getCircaSnapshotById(snapshotId);
    } catch (error) {
      await client.query('ROLLBACK');
      if (error?.code === '23505') {
        return this.getCircaSnapshotByFingerprint(snapshot.fingerprint);
      }
      throw error;
    } finally {
      client.release();
    }
  }

  async getCircaDailyPost(postDate) {
    const { rows } = await this.pool.query(
      `
        SELECT post_date, snapshot_id, channel_id, created_at
        FROM circa_daily_posts
        WHERE post_date = $1
      `,
      [postDate]
    );

    if (!rows[0]) {
      return null;
    }

    return {
      postDate: rows[0].post_date?.toISOString?.().slice(0, 10) ?? String(rows[0].post_date),
      snapshotId: Number(rows[0].snapshot_id),
      channelId: rows[0].channel_id,
      createdAt: rows[0].created_at?.toISOString?.() ?? String(rows[0].created_at),
    };
  }

  async recordCircaDailyPost(postDate, snapshotId, channelId) {
    await this.pool.query(
      `
        INSERT INTO circa_daily_posts (post_date, snapshot_id, channel_id, created_at)
        VALUES ($1, $2, $3, NOW())
        ON CONFLICT (post_date) DO UPDATE SET
          snapshot_id = EXCLUDED.snapshot_id,
          channel_id = EXCLUDED.channel_id,
          created_at = NOW()
      `,
      [postDate, snapshotId, channelId]
    );
  }

  async getLatestCircaDailyPost() {
    const { rows } = await this.pool.query(
      `
        SELECT post_date, snapshot_id, channel_id, created_at
        FROM circa_daily_posts
        ORDER BY post_date DESC
        LIMIT 1
      `
    );

    if (!rows[0]) {
      return null;
    }

    return {
      postDate: rows[0].post_date?.toISOString?.().slice(0, 10) ?? String(rows[0].post_date),
      snapshotId: Number(rows[0].snapshot_id),
      channelId: rows[0].channel_id,
      createdAt: rows[0].created_at?.toISOString?.() ?? String(rows[0].created_at),
    };
  }

  async canSendCircaMovement(movementKey, fingerprint) {
    const { rows } = await this.pool.query(
      `
        SELECT last_snapshot_fingerprint
        FROM circa_movement_posts
        WHERE movement_key = $1
      `,
      [movementKey]
    );

    if (!rows[0]) {
      return true;
    }

    return rows[0].last_snapshot_fingerprint !== fingerprint;
  }

  async recordCircaMovement(movementKey, fingerprint) {
    await this.pool.query(
      `
        INSERT INTO circa_movement_posts (movement_key, last_snapshot_fingerprint, last_sent_at)
        VALUES ($1, $2, NOW())
        ON CONFLICT (movement_key) DO UPDATE SET
          last_snapshot_fingerprint = EXCLUDED.last_snapshot_fingerprint,
          last_sent_at = NOW()
      `,
      [movementKey, fingerprint]
    );
  }

  async close() {
    await this.pool.end();
  }
}

function computeGroupedBreakdown(rows, key) {
  const groups = new Map();

  for (const row of rows) {
    if (row.deletedAt) {
      continue;
    }

    const groupKey = row[key];
    if (!groups.has(groupKey)) {
      groups.set(groupKey, []);
    }
    groups.get(groupKey).push(row);
  }

  return [...groups.entries()].map(([label, groupRows]) => {
    const wins = groupRows.filter((row) => row.status === 'win').length;
    const losses = groupRows.filter((row) => row.status === 'loss').length;
    const voids = groupRows.filter((row) => row.status === 'void').length;
    const openBets = groupRows.filter((row) => row.status === 'open').length;
    const settledStake = groupRows
      .filter((row) => row.status === 'win' || row.status === 'loss')
      .reduce((total, row) => total + row.stake, 0);
    const netProfit = groupRows
      .filter((row) => row.status !== 'open')
      .reduce((total, row) => total + (row.profitLoss ?? 0), 0);
    const gradedBets = wins + losses;

    return {
      [key]: label,
      label,
      totalBets: groupRows.length,
      openBets,
      wins,
      losses,
      voids,
      settledStake,
      netProfit,
      roiPercent: settledStake > 0 ? (netProfit / settledStake) * 100 : 0,
      winRatePercent: gradedBets > 0 ? (wins / gradedBets) * 100 : 0,
    };
  });
}

export function calculateProfitLoss(stake, normalizedDecimalOdds, result) {
  if (result === 'win') {
    return Number((stake * (normalizedDecimalOdds - 1)).toFixed(2));
  }

  if (result === 'loss') {
    return Number((-stake).toFixed(2));
  }

  return 0;
}