import { and, desc, eq, inArray, isNotNull, sql } from "drizzle-orm";
import type { createDb } from "@/db";
import { comments, events, markets, positions, profiles } from "@/db/schema";
import { yesPct } from "@/lib/format";

type Db = ReturnType<typeof createDb>;

export async function getEventResolutionData(db: Db) {
  const resolvedFilter = and(
    eq(markets.active, false),
    isNotNull(markets.result),
  );

  const [resolvedMarkets, commentCountRows, topCommentRows, winnerPositions] =
    await Promise.all([
      db
        .select({
          marketId: markets.id,
          marketSlug: markets.slug,
          question: markets.question,
          result: markets.result,
          poolYes: markets.poolYes,
          poolNo: markets.poolNo,
          volume: markets.volume,
          eventId: markets.eventId,
          eventSlug: events.slug,
          eventTitle: events.title,
        })
        .from(markets)
        .innerJoin(events, eq(markets.eventId, events.id))
        .where(resolvedFilter),

      db
        .select({
          eventId: comments.eventId,
          count: sql<number>`count(*)::int`.as("count"),
        })
        .from(comments)
        .innerJoin(events, eq(comments.eventId, events.id))
        .where(eq(events.active, false))
        .groupBy(comments.eventId),

      db
        .select({
          eventId: comments.eventId,
          profileId: comments.profileId,
          body: comments.body,
          username: profiles.username,
          score: comments.score,
        })
        .from(comments)
        .innerJoin(profiles, eq(comments.profileId, profiles.id))
        .innerJoin(events, eq(comments.eventId, events.id))
        .where(eq(events.active, false))
        .orderBy(desc(comments.score), desc(comments.createdAt))
        .limit(200),

      db
        .select({
          profileId: positions.profileId,
          marketId: positions.marketId,
        })
        .from(positions)
        .innerJoin(markets, eq(positions.marketId, markets.id))
        .where(
          and(
            resolvedFilter,
            sql`${positions.side} = ${markets.result}`,
            sql`${positions.realizedPnl}::numeric > 0`,
          ),
        ),
    ]);

  type MarketResult = {
    id: string;
    slug: string;
    question: string;
    result: string;
    crowdPct: number;
    majorityCorrect: boolean;
    volume: number;
  };

  const eventMap = new Map<
    string,
    {
      slug: string;
      title: string;
      volume: number;
      markets: MarketResult[];
    }
  >();

  for (const m of resolvedMarkets) {
    const pct = yesPct(m.poolYes, m.poolNo);
    const majorityPick = pct >= 50 ? "yes" : "no";
    const isCorrect = majorityPick === m.result;

    if (!eventMap.has(m.eventId)) {
      eventMap.set(m.eventId, {
        slug: m.eventSlug,
        title: m.eventTitle,
        volume: 0,
        markets: [],
      });
    }
    const ev = eventMap.get(m.eventId)!;
    ev.volume += parseFloat(m.volume);
    ev.markets.push({
      id: m.marketId,
      slug: m.marketSlug,
      question: m.question,
      result: m.result!,
      crowdPct: pct,
      majorityCorrect: isCorrect,
      volume: parseFloat(m.volume),
    });
  }

  const commentCountMap = new Map<string, number>();
  for (const r of commentCountRows) {
    commentCountMap.set(r.eventId, r.count);
  }

  function pickHighlights(mkts: MarketResult[]) {
    if (mkts.length <= 2) return mkts;
    const crowdAcc = (m: MarketResult) => m.result === "yes" ? m.crowdPct : 100 - m.crowdPct;
    const bestCall = [...mkts].sort((a, b) => crowdAcc(b) - crowdAcc(a))[0];
    const mostVolume = [...mkts].sort((a, b) => b.volume - a.volume)[0];
    const picks = [bestCall];
    if (mostVolume !== bestCall) picks.push(mostVolume);
    if (picks.length < 2) {
      const remaining = mkts.filter((m) => !picks.includes(m));
      if (remaining.length > 0) picks.push(remaining.sort((a, b) => crowdAcc(a) - crowdAcc(b))[0]);
    }
    return picks;
  }

  const winnersByMarket = new Map<string, Set<string>>();
  for (const wp of winnerPositions) {
    const set = winnersByMarket.get(wp.marketId) ?? new Set();
    set.add(wp.profileId);
    winnersByMarket.set(wp.marketId, set);
  }

  const commentsByEvent = new Map<string, typeof topCommentRows>();
  for (const c of topCommentRows) {
    const list = commentsByEvent.get(c.eventId) ?? [];
    list.push(c);
    commentsByEvent.set(c.eventId, list);
  }

  const eventsArr = Array.from(eventMap.entries())
    .filter(([_, ev]) => ev.markets.every((m) => m.majorityCorrect))
    .map(([id, ev]) => {
      const highlights = pickHighlights(ev.markets);
      const highlightMarketIds = new Set(highlights.map((m) => m.id));
      const winnerProfileIds = new Set<string>();
      for (const mid of highlightMarketIds) {
        const winners = winnersByMarket.get(mid);
        if (winners) winners.forEach((pid) => winnerProfileIds.add(pid));
      }
      const evComments = commentsByEvent.get(id) ?? [];
      const winnerComments = evComments
        .filter((c) => winnerProfileIds.has(c.profileId))
        .slice(0, 3)
        .map((c) => ({ username: c.username, body: c.body }));

      return {
        slug: ev.slug,
        title: ev.title,
        volume: ev.volume,
        commentCount: commentCountMap.get(id) ?? 0,
        markets: highlights,
        winnerComments,
      };
    })
    .sort((a, b) => {
      if (a.slug === "big-game-champion-2026") return -1;
      if (b.slug === "big-game-champion-2026") return 1;
      return b.volume + b.commentCount * 10 - (a.volume + a.commentCount * 10);
    })
    .slice(0, 6);

  return { events: eventsArr };
}