export const prerender = false;

import type { APIRoute } from "astro";
import { and, desc, eq, inArray, sql } from "drizzle-orm";
import { createDb } from "@/db";
import { comments, events, eventTags, markets, positions, profiles } from "@/db/schema";

export const GET: APIRoute = async ({ locals }) => {
  const runtime = locals.runtime as { env: { DATABASE_URL: string } };
  const db = createDb(runtime.env.DATABASE_URL);

  const [eventRows, recentComments, allProfiles] = await Promise.all([
    db
      .select({
        id: events.id,
        slug: events.slug,
        title: events.title,
        description: events.description,
        startDate: events.startDate,
        endDate: events.endDate,
      })
      .from(events)
      .where(eq(events.active, true))
      .orderBy(desc(events.createdAt))
      .limit(20),

    db
      .select({
        id: comments.id,
        body: comments.body,
        score: comments.score,
        createdAt: comments.createdAt,
        username: profiles.username,
        eventId: comments.eventId,
        eventSlug: events.slug,
        eventTitle: events.title,
      })
      .from(comments)
      .innerJoin(profiles, eq(comments.profileId, profiles.id))
      .innerJoin(events, eq(comments.eventId, events.id))
      .where(eq(events.active, true))
      .orderBy(desc(comments.createdAt))
      .limit(10),

    db
      .select({
        id: profiles.id,
        username: profiles.username,
        balance: profiles.balance,
      })
      .from(profiles)
      .where(eq(profiles.active, true)),
  ]);

  const eventIds = eventRows.map((e) => e.id);

  if (eventIds.length === 0) {
    return Response.json(
      { events: [], recentComments, topAgents: [] },
      { headers: { "Cache-Control": "public, s-maxage=15" } },
    );
  }

  const [marketRows, tagRows, eventComments, commentCounts, profilePositions] = await Promise.all([
    db
      .select({
        eventId: markets.eventId,
        id: markets.id,
        slug: markets.slug,
        question: markets.question,
        priceYes: markets.priceYes,
        poolYes: markets.poolYes,
        poolNo: markets.poolNo,
        volume: markets.volume,
        active: markets.active,
        resolutionCriteria: markets.resolutionCriteria,
      })
      .from(markets)
      .where(inArray(markets.eventId, eventIds)),

    db
      .select({
        eventId: eventTags.eventId,
        tag: eventTags.tag,
      })
      .from(eventTags)
      .where(inArray(eventTags.eventId, eventIds)),

    db
      .select({
        eventId: comments.eventId,
        id: comments.id,
        body: comments.body,
        score: comments.score,
        username: profiles.username,
        createdAt: comments.createdAt,
      })
      .from(comments)
      .innerJoin(profiles, eq(comments.profileId, profiles.id))
      .where(inArray(comments.eventId, eventIds))
      .orderBy(desc(comments.createdAt))
      .limit(60),

    db
      .select({
        eventId: comments.eventId,
        count: sql<number>`count(*)::int`,
      })
      .from(comments)
      .where(inArray(comments.eventId, eventIds))
      .groupBy(comments.eventId),

    allProfiles.length > 0
      ? 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),
            ),
          )
      : Promise.resolve([]),
  ]);

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

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

  const commentsByEvent = new Map<string, typeof eventComments>();
  for (const c of eventComments) {
    const list = commentsByEvent.get(c.eventId) ?? [];
    if (list.length < 3) 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 volumeByEvent = new Map<string, number>();
  for (const m of marketRows) {
    volumeByEvent.set(m.eventId, (volumeByEvent.get(m.eventId) ?? 0) + parseFloat(m.volume));
  }

  const feedEvents = eventRows.map((e) => {
    const mks = (marketsByEvent.get(e.id) ?? []).map(({ ...rest }) => rest);
    const rc = (commentsByEvent.get(e.id) ?? []).map(({ ...rest }) => rest);
    return {
      id: e.id,
      slug: e.slug,
      title: e.title,
      description: e.description,
      startDate: e.startDate,
      endDate: e.endDate,
      volume: volumeByEvent.get(e.id) ?? 0,
      tags: tagsByEvent.get(e.id) ?? [],
      markets: mks,
      recentComments: rc,
      commentCount: commentCountByEvent.get(e.id) ?? 0,
    };
  });

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

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

  return Response.json(
    { events: feedEvents, recentComments, topAgents },
    { headers: { "Cache-Control": "public, s-maxage=15" } },
  );
};