import { createServerFn } from "@tanstack/react-start";
import { getSql } from "@/lib/db";
import { authMiddleware } from "@/lib/auth/middleware";
import { optionalAuthMiddleware } from "@/lib/auth/optional-middleware";
import { newId } from "@/lib/server/ids";
import { mapCard, mapComment, mapPost, mapTrade, type CardRow } from "@/lib/server/mappers";
import { ensureProfile, getProfileByHandle, getProfileByUserId } from "@/lib/server/profile";
import type { CardItem, CommentItem, PostItem, Profile, Rarity, TradeItem } from "@/lib/types";
import { POSITIONS, RARITIES, TEAMS } from "@/lib/types";

// ── Profile ──────────────────────────────────────────────────────────────────

export const getMyProfile = createServerFn({ method: "GET" })
  .middleware([authMiddleware])
  .handler(async ({ context }): Promise<Profile> => {
    return ensureProfile(context.userId);
  });

export const updateMyProfile = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { displayName?: string; bio?: string; handle?: string; avatarUrl?: string | null }) => data)
  .handler(async ({ context, data }): Promise<Profile> => {
    await ensureProfile(context.userId);
    const sql = await getSql();

    if (data.handle) {
      const handle = data.handle
        .toLowerCase()
        .replace(/[^a-z0-9_]/g, "")
        .slice(0, 24);
      if (handle.length < 3) throw new Error("Handle must be at least 3 characters");
      const taken = await sql<{ user_id: string }>`
        select user_id from profiles where handle = ${handle} and user_id <> ${context.userId} limit 1
      `;
      if (taken[0]) throw new Error("Handle already taken");
      await sql`update profiles set handle = ${handle}, updated_at = now() where user_id = ${context.userId}`;
    }
    if (data.displayName !== undefined) {
      const name = data.displayName.trim().slice(0, 40) || "Player";
      await sql`update profiles set display_name = ${name}, updated_at = now() where user_id = ${context.userId}`;
    }
    if (data.bio !== undefined) {
      await sql`update profiles set bio = ${data.bio.trim().slice(0, 280)}, updated_at = now() where user_id = ${context.userId}`;
    }
    if (data.avatarUrl !== undefined) {
      await sql`update profiles set avatar_url = ${data.avatarUrl}, updated_at = now() where user_id = ${context.userId}`;
    }

    const p = await getProfileByUserId(context.userId);
    if (!p) throw new Error("Profile not found");
    return p;
  });

export const fetchProfile = createServerFn({ method: "GET" })
  .middleware([optionalAuthMiddleware])
  .validator((handle: string) => handle)
  .handler(async ({ context, data: handle }): Promise<Profile | null> => {
    return getProfileByHandle(handle, context.userId);
  });

export const toggleFollow = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((targetUserId: string) => targetUserId)
  .handler(async ({ context, data: targetUserId }): Promise<{ following: boolean }> => {
    if (targetUserId === context.userId) throw new Error("Cannot follow yourself");
    await ensureProfile(context.userId);
    const sql = await getSql();
    const existing = await sql<{ follower_id: string }>`
      select follower_id from follows
      where follower_id = ${context.userId} and following_id = ${targetUserId}
      limit 1
    `;
    if (existing[0]) {
      await sql`
        delete from follows
        where follower_id = ${context.userId} and following_id = ${targetUserId}
      `;
      return { following: false };
    }
    await sql`
      insert into follows (follower_id, following_id)
      values (${context.userId}, ${targetUserId})
    `;
    return { following: true };
  });

// ── Cards ────────────────────────────────────────────────────────────────────

export type CreateCardInput = {
  playerName: string;
  team: string;
  position: string;
  seasonYear: number;
  rarity: Rarity;
  battingAvg?: string;
  homeRuns?: number;
  rbi?: number;
  era?: string;
  wins?: number;
  strikeouts?: number;
  imageData?: string | null;
  description?: string;
  forTrade?: boolean;
  announce?: boolean;
};

export const createCard = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: CreateCardInput) => data)
  .handler(async ({ context, data }): Promise<CardItem> => {
    await ensureProfile(context.userId);
    const playerName = data.playerName.trim().slice(0, 48);
    if (!playerName) throw new Error("Player name is required");
    if (!TEAMS.includes(data.team as (typeof TEAMS)[number])) throw new Error("Invalid team");
    if (!POSITIONS.includes(data.position as (typeof POSITIONS)[number])) throw new Error("Invalid position");
    if (!RARITIES.includes(data.rarity)) throw new Error("Invalid rarity");
    const year = Number(data.seasonYear);
    if (year < 1900 || year > 2030) throw new Error("Invalid year");

    const imageData = data.imageData ?? null;
    if (imageData && imageData.length > 900_000) {
      throw new Error("Image is too large — try a smaller photo");
    }

    const id = newId("card");
    const sql = await getSql();
    await sql`
      insert into cards (
        id, owner_id, player_name, team, position, season_year, rarity,
        batting_avg, home_runs, rbi, era, wins, strikeouts,
        image_data, description, for_trade
      ) values (
        ${id},
        ${context.userId},
        ${playerName},
        ${data.team},
        ${data.position},
        ${year},
        ${data.rarity},
        ${data.battingAvg?.trim() || null},
        ${data.homeRuns ?? null},
        ${data.rbi ?? null},
        ${data.era?.trim() || null},
        ${data.wins ?? null},
        ${data.strikeouts ?? null},
        ${imageData},
        ${(data.description ?? "").trim().slice(0, 500)},
        ${data.forTrade !== false}
      )
    `;

    if (data.announce !== false) {
      const postId = newId("post");
      await sql`
        insert into posts (id, user_id, content, card_id)
        values (
          ${postId},
          ${context.userId},
          ${`Just minted a new ${data.rarity} card: ${playerName} (${data.team}). Open to trades.`},
          ${id}
        )
      `;
    }

    const rows = await sql<CardRow>`select * from cards where id = ${id} limit 1`;
    return mapCard(rows[0]!);
  });

export const listMyCards = createServerFn({ method: "GET" })
  .middleware([authMiddleware])
  .handler(async ({ context }): Promise<CardItem[]> => {
    await ensureProfile(context.userId);
    const sql = await getSql();
    const rows = await sql<CardRow>`
      select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
      from cards c
      left join profiles p on p.user_id = c.owner_id
      where c.owner_id = ${context.userId}
      order by c.created_at desc
    `;
    return rows.map(mapCard);
  });

export const listMarketplace = createServerFn({ method: "GET" })
  .validator((data?: { q?: string; rarity?: string; team?: string }) => data ?? {})
  .handler(async ({ data }): Promise<CardItem[]> => {
    const sql = await getSql();
    const q = data.q?.trim().toLowerCase() ?? "";
    const rarity = data.rarity && data.rarity !== "all" ? data.rarity : null;
    const team = data.team && data.team !== "all" ? data.team : null;

    let rows: CardRow[];
    if (q || rarity || team) {
      rows = await sql<CardRow>`
        select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
        from cards c
        left join profiles p on p.user_id = c.owner_id
        where c.for_trade = true
          and (${rarity}::text is null or c.rarity = ${rarity})
          and (${team}::text is null or c.team = ${team})
          and (
            ${q} = ''
            or lower(c.player_name) like ${"%" + q + "%"}
            or lower(c.team) like ${"%" + q + "%"}
            or lower(coalesce(p.handle, '')) like ${"%" + q + "%"}
          )
        order by c.created_at desc
        limit 60
      `;
    } else {
      rows = await sql<CardRow>`
        select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
        from cards c
        left join profiles p on p.user_id = c.owner_id
        where c.for_trade = true
        order by c.created_at desc
        limit 60
      `;
    }
    return rows.map(mapCard);
  });

export const getCard = createServerFn({ method: "GET" })
  .validator((id: string) => id)
  .handler(async ({ data: id }): Promise<CardItem | null> => {
    const sql = await getSql();
    const rows = await sql<CardRow>`
      select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
      from cards c
      left join profiles p on p.user_id = c.owner_id
      where c.id = ${id}
      limit 1
    `;
    return rows[0] ? mapCard(rows[0]) : null;
  });

export const listUserCards = createServerFn({ method: "GET" })
  .validator((userId: string) => userId)
  .handler(async ({ data: userId }): Promise<CardItem[]> => {
    const sql = await getSql();
    const rows = await sql<CardRow>`
      select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
      from cards c
      left join profiles p on p.user_id = c.owner_id
      where c.owner_id = ${userId}
      order by c.created_at desc
    `;
    return rows.map(mapCard);
  });

export const toggleCardTrade = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { cardId: string; forTrade: boolean }) => data)
  .handler(async ({ context, data }): Promise<CardItem> => {
    const sql = await getSql();
    await sql`
      update cards set for_trade = ${data.forTrade}
      where id = ${data.cardId} and owner_id = ${context.userId}
    `;
    const rows = await sql<CardRow>`
      select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
      from cards c
      left join profiles p on p.user_id = c.owner_id
      where c.id = ${data.cardId}
      limit 1
    `;
    if (!rows[0]) throw new Error("Card not found");
    return mapCard(rows[0]);
  });

// ── Social feed ──────────────────────────────────────────────────────────────

export const listFeed = createServerFn({ method: "GET" })
  .middleware([optionalAuthMiddleware])
  .handler(async ({ context }): Promise<PostItem[]> => {
    const viewerId = context.userId ?? "";
    const sql = await getSql();
    const rows = await sql<{
      id: string;
      user_id: string;
      content: string;
      card_id: string | null;
      created_at: string | Date;
      author_handle: string;
      author_display_name: string;
      author_avatar_url: string | null;
      like_count: number;
      comment_count: number;
      liked_by_me: number;
    }>`
      select
        p.id, p.user_id, p.content, p.card_id, p.created_at,
        coalesce(pr.handle, 'player') as author_handle,
        coalesce(pr.display_name, 'Player') as author_display_name,
        pr.avatar_url as author_avatar_url,
        (select count(*)::int from post_likes pl where pl.post_id = p.id) as like_count,
        (select count(*)::int from comments c where c.post_id = p.id) as comment_count,
        (select count(*)::int from post_likes pl where pl.post_id = p.id and pl.user_id = ${viewerId}) as liked_by_me
      from posts p
      left join profiles pr on pr.user_id = p.user_id
      order by p.created_at desc
      limit 40
    `;

    const posts = rows.map(mapPost);
    const cardIds = [...new Set(posts.map((p) => p.cardId).filter(Boolean))] as string[];
    if (cardIds.length === 0) return posts;

    const cardMap = new Map<string, CardItem>();
    for (const cid of cardIds) {
      const r = await sql<CardRow>`
        select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
        from cards c
        left join profiles p on p.user_id = c.owner_id
        where c.id = ${cid}
        limit 1
      `;
      if (r[0]) cardMap.set(cid, mapCard(r[0]));
    }

    return posts.map((p) => ({
      ...p,
      card: p.cardId ? cardMap.get(p.cardId) ?? null : null,
    }));
  });

export const createPost = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { content: string; cardId?: string | null }) => data)
  .handler(async ({ context, data }): Promise<PostItem> => {
    await ensureProfile(context.userId);
    const content = data.content.trim().slice(0, 500);
    if (!content) throw new Error("Post cannot be empty");

    const sql = await getSql();
    if (data.cardId) {
      const own = await sql<{ id: string }>`
        select id from cards where id = ${data.cardId} and owner_id = ${context.userId} limit 1
      `;
      if (!own[0]) throw new Error("You can only attach your own cards");
    }

    const id = newId("post");
    await sql`
      insert into posts (id, user_id, content, card_id)
      values (${id}, ${context.userId}, ${content}, ${data.cardId ?? null})
    `;

    const rows = await sql<{
      id: string;
      user_id: string;
      content: string;
      card_id: string | null;
      created_at: string | Date;
      author_handle: string;
      author_display_name: string;
      author_avatar_url: string | null;
      like_count: number;
      comment_count: number;
      liked_by_me: number;
    }>`
      select
        p.id, p.user_id, p.content, p.card_id, p.created_at,
        coalesce(pr.handle, 'player') as author_handle,
        coalesce(pr.display_name, 'Player') as author_display_name,
        pr.avatar_url as author_avatar_url,
        0 as like_count, 0 as comment_count, 0 as liked_by_me
      from posts p
      left join profiles pr on pr.user_id = p.user_id
      where p.id = ${id}
      limit 1
    `;
    const post = mapPost(rows[0]!);
    if (data.cardId) {
      const card = await sql<CardRow>`
        select c.*, p.handle as owner_handle, p.display_name as owner_display_name, p.avatar_url as owner_avatar_url
        from cards c
        left join profiles p on p.user_id = c.owner_id
        where c.id = ${data.cardId}
        limit 1
      `;
      if (card[0]) post.card = mapCard(card[0]);
    }
    return post;
  });

export const toggleLike = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((postId: string) => postId)
  .handler(async ({ context, data: postId }): Promise<{ liked: boolean; likeCount: number }> => {
    await ensureProfile(context.userId);
    const sql = await getSql();
    const existing = await sql<{ post_id: string }>`
      select post_id from post_likes where post_id = ${postId} and user_id = ${context.userId} limit 1
    `;
    if (existing[0]) {
      await sql`delete from post_likes where post_id = ${postId} and user_id = ${context.userId}`;
    } else {
      await sql`insert into post_likes (post_id, user_id) values (${postId}, ${context.userId})`;
    }
    const [count] = await sql<{ c: number }>`
      select count(*)::int as c from post_likes where post_id = ${postId}
    `;
    return { liked: !existing[0], likeCount: Number(count?.c ?? 0) };
  });

export const listComments = createServerFn({ method: "GET" })
  .validator((postId: string) => postId)
  .handler(async ({ data: postId }): Promise<CommentItem[]> => {
    const sql = await getSql();
    const rows = await sql<{
      id: string;
      post_id: string;
      user_id: string;
      content: string;
      created_at: string | Date;
      author_handle: string;
      author_display_name: string;
      author_avatar_url: string | null;
    }>`
      select
        c.id, c.post_id, c.user_id, c.content, c.created_at,
        coalesce(pr.handle, 'player') as author_handle,
        coalesce(pr.display_name, 'Player') as author_display_name,
        pr.avatar_url as author_avatar_url
      from comments c
      left join profiles pr on pr.user_id = c.user_id
      where c.post_id = ${postId}
      order by c.created_at asc
      limit 100
    `;
    return rows.map(mapComment);
  });

export const addComment = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { postId: string; content: string }) => data)
  .handler(async ({ context, data }): Promise<CommentItem> => {
    await ensureProfile(context.userId);
    const content = data.content.trim().slice(0, 280);
    if (!content) throw new Error("Comment cannot be empty");
    const id = newId("cmt");
    const sql = await getSql();
    await sql`
      insert into comments (id, post_id, user_id, content)
      values (${id}, ${data.postId}, ${context.userId}, ${content})
    `;
    const rows = await sql<{
      id: string;
      post_id: string;
      user_id: string;
      content: string;
      created_at: string | Date;
      author_handle: string;
      author_display_name: string;
      author_avatar_url: string | null;
    }>`
      select
        c.id, c.post_id, c.user_id, c.content, c.created_at,
        coalesce(pr.handle, 'player') as author_handle,
        coalesce(pr.display_name, 'Player') as author_display_name,
        pr.avatar_url as author_avatar_url
      from comments c
      left join profiles pr on pr.user_id = c.user_id
      where c.id = ${id}
      limit 1
    `;
    return mapComment(rows[0]!);
  });

// ── Trades ───────────────────────────────────────────────────────────────────

export const proposeTrade = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { offeredCardId: string; requestedCardId: string; message?: string }) => data)
  .handler(async ({ context, data }): Promise<TradeItem> => {
    await ensureProfile(context.userId);
    const sql = await getSql();

    const offered = await sql<CardRow>`
      select * from cards where id = ${data.offeredCardId} and owner_id = ${context.userId} limit 1
    `;
    if (!offered[0]) throw new Error("You don't own the offered card");

    const requested = await sql<CardRow>`
      select * from cards where id = ${data.requestedCardId} limit 1
    `;
    if (!requested[0]) throw new Error("Requested card not found");
    if (requested[0].owner_id === context.userId) throw new Error("Cannot trade with yourself");
    if (!requested[0].for_trade) throw new Error("That card is not open for trade");

    const dup = await sql<{ id: string }>`
      select id from trades
      where status = 'pending'
        and from_user_id = ${context.userId}
        and offered_card_id = ${data.offeredCardId}
        and requested_card_id = ${data.requestedCardId}
      limit 1
    `;
    if (dup[0]) throw new Error("You already have a pending trade for these cards");

    const id = newId("trade");
    await sql`
      insert into trades (id, from_user_id, to_user_id, offered_card_id, requested_card_id, message, status)
      values (
        ${id},
        ${context.userId},
        ${requested[0].owner_id},
        ${data.offeredCardId},
        ${data.requestedCardId},
        ${(data.message ?? "").trim().slice(0, 280)},
        ${"pending"}
      )
    `;

    const postId = newId("post");
    await sql`
      insert into posts (id, user_id, content, card_id)
      values (
        ${postId},
        ${context.userId},
        ${`Proposed a trade: offering ${offered[0].player_name} for ${requested[0].player_name}.`},
        ${data.offeredCardId}
      )
    `;

    return (await loadTrade(id))!;
  });

export const listMyTrades = createServerFn({ method: "GET" })
  .middleware([authMiddleware])
  .handler(async ({ context }): Promise<TradeItem[]> => {
    await ensureProfile(context.userId);
    const sql = await getSql();
    const rows = await sql<{ id: string }>`
      select id from trades
      where from_user_id = ${context.userId} or to_user_id = ${context.userId}
      order by created_at desc
      limit 50
    `;
    const trades: TradeItem[] = [];
    for (const r of rows) {
      const t = await loadTrade(r.id);
      if (t) trades.push(t);
    }
    return trades;
  });

export const respondTrade = createServerFn({ method: "POST" })
  .middleware([authMiddleware])
  .validator((data: { tradeId: string; action: "accept" | "decline" | "cancel" }) => data)
  .handler(async ({ context, data }): Promise<TradeItem> => {
    const sql = await getSql();
    const tradeRows = await sql<{
      id: string;
      from_user_id: string;
      to_user_id: string;
      offered_card_id: string;
      requested_card_id: string;
      status: string;
    }>`
      select id, from_user_id, to_user_id, offered_card_id, requested_card_id, status
      from trades where id = ${data.tradeId} limit 1
    `;
    const trade = tradeRows[0];
    if (!trade) throw new Error("Trade not found");
    if (trade.status !== "pending") throw new Error("Trade is already resolved");

    if (data.action === "cancel") {
      if (trade.from_user_id !== context.userId) throw new Error("Only the proposer can cancel");
      await sql`
        update trades set status = 'cancelled', resolved_at = now() where id = ${trade.id}
      `;
      return (await loadTrade(trade.id))!;
    }

    if (trade.to_user_id !== context.userId) throw new Error("Only the recipient can respond");

    if (data.action === "decline") {
      await sql`
        update trades set status = 'declined', resolved_at = now() where id = ${trade.id}
      `;
      return (await loadTrade(trade.id))!;
    }

    const offered = await sql<CardRow>`
      select * from cards where id = ${trade.offered_card_id} limit 1
    `;
    const requested = await sql<CardRow>`
      select * from cards where id = ${trade.requested_card_id} limit 1
    `;
    if (!offered[0] || offered[0].owner_id !== trade.from_user_id) {
      throw new Error("Offered card is no longer available");
    }
    if (!requested[0] || requested[0].owner_id !== trade.to_user_id) {
      throw new Error("Requested card is no longer available");
    }

    await sql`
      update cards set owner_id = ${trade.to_user_id}, for_trade = true
      where id = ${trade.offered_card_id}
    `;
    await sql`
      update cards set owner_id = ${trade.from_user_id}, for_trade = true
      where id = ${trade.requested_card_id}
    `;
    await sql`
      update trades set status = 'accepted', resolved_at = now() where id = ${trade.id}
    `;

    await sql`
      update trades set status = 'cancelled', resolved_at = now()
      where status = 'pending'
        and id <> ${trade.id}
        and (
          offered_card_id = ${trade.offered_card_id}
          or offered_card_id = ${trade.requested_card_id}
          or requested_card_id = ${trade.offered_card_id}
          or requested_card_id = ${trade.requested_card_id}
        )
    `;

    const postId = newId("post");
    await sql`
      insert into posts (id, user_id, content, card_id)
      values (
        ${postId},
        ${context.userId},
        ${`Trade completed! Swapped ${requested[0].player_name} for ${offered[0].player_name}.`},
        ${trade.requested_card_id}
      )
    `;

    return (await loadTrade(trade.id))!;
  });

export const getPendingTradeCount = createServerFn({ method: "GET" })
  .middleware([authMiddleware])
  .handler(async ({ context }): Promise<number> => {
    const sql = await getSql();
    const [row] = await sql<{ c: number }>`
      select count(*)::int as c from trades
      where to_user_id = ${context.userId} and status = 'pending'
    `;
    return Number(row?.c ?? 0);
  });

async function loadTrade(id: string): Promise<TradeItem | null> {
  const sql = await getSql();
  const rows = await sql<{
    id: string;
    from_user_id: string;
    to_user_id: string;
    offered_card_id: string;
    requested_card_id: string;
    message: string;
    status: string;
    created_at: string | Date;
    resolved_at: string | Date | null;
    from_handle: string;
    from_display_name: string;
    to_handle: string;
    to_display_name: string;
  }>`
    select
      t.id, t.from_user_id, t.to_user_id, t.offered_card_id, t.requested_card_id,
      t.message, t.status, t.created_at, t.resolved_at,
      coalesce(fp.handle, 'player') as from_handle,
      coalesce(fp.display_name, 'Player') as from_display_name,
      coalesce(tp.handle, 'player') as to_handle,
      coalesce(tp.display_name, 'Player') as to_display_name
    from trades t
    left join profiles fp on fp.user_id = t.from_user_id
    left join profiles tp on tp.user_id = t.to_user_id
    where t.id = ${id}
    limit 1
  `;
  const row = rows[0];
  if (!row) return null;

  const offered = await sql<CardRow>`select * from cards where id = ${row.offered_card_id} limit 1`;
  const requested = await sql<CardRow>`select * from cards where id = ${row.requested_card_id} limit 1`;
  if (!offered[0] || !requested[0]) return null;

  return mapTrade({
    ...row,
    offered: offered[0],
    requested: requested[0],
  });
}