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

type Db = ReturnType<typeof createDb>;

export async function getProfilePageData(db: Db, username: string, page = 1) {
  const pageSize = 10;

  const [profile] = await db
    .select({
      id: profiles.id,
      username: profiles.username,
      name: profiles.name,
      bio: profiles.bio,
      balance: profiles.balance,
      active: profiles.active,
    })
    .from(profiles)
    .where(eq(profiles.username, username));
  if (!profile) return null;

  const [userPositions, commentsResult, totalCountResult, userTrades] = await Promise.all([
    db
      .select({
        id: positions.id,
        side: positions.side,
        shares: positions.shares,
        avgPrice: positions.avgPrice,
        realizedPnl: positions.realizedPnl,
        question: markets.question,
        poolYes: markets.poolYes,
        poolNo: markets.poolNo,
        eventId: events.id,
        eventTitle: events.title,
        eventSlug: events.slug,
      })
      .from(positions)
      .innerJoin(markets, eq(positions.marketId, markets.id))
      .innerJoin(events, eq(markets.eventId, events.id))
      .where(eq(positions.profileId, profile.id)),
    db
      .select({
        id: comments.id,
        body: comments.body,
        createdAt: comments.createdAt,
        score: comments.score,
        eventId: events.id,
        eventSlug: events.slug,
        eventTitle: events.title,
      })
      .from(comments)
      .innerJoin(events, eq(comments.eventId, events.id))
      .where(eq(comments.profileId, profile.id))
      .orderBy(desc(comments.createdAt))
      .limit(pageSize)
      .offset((page - 1) * pageSize),
    db.select({ count: count() }).from(comments).where(eq(comments.profileId, profile.id)),
    db
      .select({
        action: trades.action,
        side: trades.side,
        shares: trades.shares,
        amount: trades.amount,
        price: trades.price,
        createdAt: trades.createdAt,
        question: markets.question,
        eventTitle: events.title,
        eventSlug: events.slug,
      })
      .from(trades)
      .innerJoin(markets, eq(trades.marketId, markets.id))
      .innerJoin(events, eq(markets.eventId, events.id))
      .where(eq(trades.profileId, profile.id))
      .orderBy(desc(trades.createdAt))
      .limit(20),
  ]);

  const activePositions = userPositions.filter((p) => parseFloat(p.shares) > 0);
  const closedPositions = userPositions.filter((p) => parseFloat(p.shares) === 0 && parseFloat(p.realizedPnl) !== 0);

  const positionValue = activePositions.reduce((sum, p) => {
    const pct = yesPct(p.poolYes, p.poolNo);
    const price = p.side === "yes" ? pct / 100 : (100 - pct) / 100;
    return sum + parseFloat(p.shares) * price;
  }, 0);

  const totalComments = totalCountResult[0]?.count ?? 0;

  const positionByEvent = new Map<string, { side: string; shares: string; question: string }[]>();
  for (const p of userPositions) {
    if (parseFloat(p.shares) > 0) {
      const existing = positionByEvent.get(p.eventId) ?? [];
      existing.push({ side: p.side, shares: p.shares, question: p.question });
      positionByEvent.set(p.eventId, existing);
    }
  }

  return {
    profile,
    activePositions,
    closedPositions,
    netWorth: parseFloat(profile.balance) + positionValue,
    userComments: commentsResult,
    positionByEvent,
    totalComments,
    totalPages: Math.ceil(totalComments / pageSize),
    userTrades,
  };
}