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

type Db = ReturnType<typeof createDb>;

export async function getTrendingData(db: Db) {
  const h12ago = new Date(Date.now() - 12 * 60 * 60 * 1000);

  const [trendingRows, allTagRows] = await Promise.all([
    db
      .select({
        eventId: markets.eventId,
        volume: sql<number>`coalesce(sum(${trades.amount}::numeric), 0)::float`.as("volume"),
      })
      .from(trades)
      .innerJoin(markets, eq(trades.marketId, markets.id))
      .innerJoin(events, eq(markets.eventId, events.id))
      .where(and(gte(trades.createdAt, h12ago), eq(events.active, true)))
      .groupBy(markets.eventId)
      .orderBy(sql`sum(${trades.amount}::numeric) desc`),

    db.select().from(eventTags),
  ]);

  const tagsByEvent = new Map<string, string[]>();
  for (const t of allTagRows) {
    const list = tagsByEvent.get(t.eventId) ?? [];
    list.push(t.tag);
    tagsByEvent.set(t.eventId, list);
  }

  let trendingEventIds = trendingRows.map((r) => r.eventId);

  if (trendingEventIds.length === 0) {
    const fallbackEvents = await db
      .select({
        id: events.id,
        volume: sql<number>`coalesce(sum(${markets.volume}::numeric), 0)::float`.as("volume"),
      })
      .from(events)
      .leftJoin(markets, eq(markets.eventId, events.id))
      .where(eq(events.active, true))
      .groupBy(events.id)
      .orderBy(sql`coalesce(sum(${markets.volume}::numeric), 0) desc`)
      .limit(20);
    trendingEventIds = fallbackEvents.map((e) => e.id);
  }

  const [trendingEvents, trendingMarkets, trendingComments, commentCounts] =
    trendingEventIds.length > 0
      ? await Promise.all([
          db.select().from(events).where(inArray(events.id, trendingEventIds)),
          db.select().from(markets).where(inArray(markets.eventId, trendingEventIds)),
          db
            .select({
              id: comments.id,
              eventId: comments.eventId,
              body: comments.body,
              createdAt: comments.createdAt,
              profileUsername: profiles.username,
            })
            .from(comments)
            .innerJoin(profiles, eq(comments.profileId, profiles.id))
            .where(inArray(comments.eventId, trendingEventIds))
            .orderBy(desc(comments.createdAt))
            .limit(100),
          db
            .select({
              eventId: comments.eventId,
              count: sql<number>`count(*)::int`.as("count"),
            })
            .from(comments)
            .where(inArray(comments.eventId, trendingEventIds))
            .groupBy(comments.eventId),
        ])
      : [[], [], [], []];

  const trendingEventMap = new Map(trendingEvents.map((e) => [e.id, e]));
  const marketsByEvent = new Map<string, typeof trendingMarkets>();
  for (const m of trendingMarkets) {
    const list = marketsByEvent.get(m.eventId) ?? [];
    list.push(m);
    marketsByEvent.set(m.eventId, list);
  }

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

  const commentCountByEvent = new Map<string, number>();
  for (const cc of commentCounts) {
    commentCountByEvent.set(cc.eventId, cc.count);
  }

  const eventsByTag = new Map<string, typeof trendingEvents>();
  for (const tag of SELECTED_TAGS) {
    const tagEvents = trendingEventIds
      .filter((id) => (tagsByEvent.get(id) ?? []).includes(tag))
      .map((id) => trendingEventMap.get(id)!)
      .filter(Boolean)
      .slice(0, 3);
    if (tagEvents.length > 0) {
      eventsByTag.set(tag, tagEvents);
    }
  }

  const categories = SELECTED_TAGS.filter((key) => eventsByTag.has(key)).map((key) => ({
    key,
    label: TAG_LABELS[key] ?? key,
  }));

  return {
    categories,
    eventsByTag,
    marketsByEvent,
    tagsByEvent,
    commentsByEvent,
    commentCountByEvent,
  };
}

export async function getRankingData(db: Db) {
  const allProfiles = await db
    .select({
      id: profiles.id,
      username: profiles.username,
      balance: profiles.balance,
    })
    .from(profiles)
    .where(eq(profiles.active, true));

  const profilePositions =
    allProfiles.length > 0
      ? await db
          .select({
            profileId: positions.profileId,
            shares: positions.shares,
            side: positions.side,
            poolYes: markets.poolYes,
            poolNo: markets.poolNo,
          })
          .from(positions)
          .innerJoin(markets, eq(positions.marketId, markets.id))
          .where(
            inArray(
              positions.profileId,
              allProfiles.map((p) => p.id),
            ),
          )
      : [];

  const posValueByProfile = new Map<string, number>();
  for (const pos of profilePositions) {
    if (parseFloat(pos.shares) <= 0) continue;
    const pct = yesPct(pos.poolYes, pos.poolNo);
    const price = pos.side === "yes" ? pct / 100 : (100 - pct) / 100;
    const val = parseFloat(pos.shares) * price;
    posValueByProfile.set(pos.profileId, (posValueByProfile.get(pos.profileId) ?? 0) + val);
  }

  const ranked = allProfiles
    .map((p) => ({
      ...p,
      netWorth: parseFloat(p.balance) + (posValueByProfile.get(p.id) ?? 0),
    }))
    .sort((a, b) => b.netWorth - a.netWorth)
    .slice(0, 5);

  const rankedIds = ranked.map((r) => r.id);
  const rankComments =
    rankedIds.length > 0
      ? await db
          .select({
            id: comments.id,
            profileId: comments.profileId,
            body: comments.body,
            createdAt: comments.createdAt,
            profileUsername: profiles.username,
          })
          .from(comments)
          .innerJoin(profiles, eq(comments.profileId, profiles.id))
          .where(inArray(comments.profileId, rankedIds))
          .orderBy(desc(comments.createdAt))
          .limit(50)
      : [];

  const commentsByProfile = new Map<string, typeof rankComments>();
  for (const c of rankComments) {
    const list = commentsByProfile.get(c.profileId) ?? [];
    if (list.length < 5) list.push(c);
    commentsByProfile.set(c.profileId, list);
  }

  return { ranked, commentsByProfile };
}