import { getSql } from "@/lib/db";
import { newId, slugifyHandle } from "@/lib/server/ids";
import { mapProfile, type ProfileRow } from "@/lib/server/mappers";
import type { Profile } from "@/lib/types";

type AuthUser = {
  id: string;
  name?: string | null;
  email?: string | null;
  image?: string | null;
};

async function loadAuthUser(userId: string): Promise<AuthUser> {
  const sql = await getSql();
  try {
    const rows = await sql<{ id: string; name: string; email: string; image: string | null }>`
      select id, name, email, image from "user" where id = ${userId} limit 1
    `;
    if (rows[0]) {
      return {
        id: rows[0].id,
        name: rows[0].name,
        email: rows[0].email,
        image: rows[0].image,
      };
    }
  } catch {
    /* user table may be empty in edge cases */
  }
  return { id: userId, name: null, email: null, image: null };
}

export async function ensureProfile(userOrId: AuthUser | string): Promise<Profile> {
  const user = typeof userOrId === "string" ? await loadAuthUser(userOrId) : userOrId;
  const sql = await getSql();
  const existing = await sql<ProfileRow>`
    select user_id, handle, display_name, bio, avatar_url, created_at
    from profiles where user_id = ${user.id} limit 1
  `;
  if (existing[0]) {
    await grantStarterPack(user.id);
    return mapProfile(existing[0]);
  }

  const displayName = (user.name?.trim() || user.email?.split("@")[0] || "Player").slice(0, 40);
  let handle = slugifyHandle(displayName);
  const taken = await sql<{ handle: string }>`
    select handle from profiles where handle = ${handle} or handle like ${handle + "_%"}
  `;
  if (taken.length > 0) {
    handle = `${handle}_${Math.random().toString(36).slice(2, 6)}`.slice(0, 24);
  }

  await sql`
    insert into profiles (user_id, handle, display_name, bio, avatar_url)
    values (${user.id}, ${handle}, ${displayName}, ${""}, ${user.image ?? null})
  `;

  await grantStarterPack(user.id);

  const rows = await sql<ProfileRow>`
    select user_id, handle, display_name, bio, avatar_url, created_at
    from profiles where user_id = ${user.id} limit 1
  `;
  return mapProfile(rows[0]!);
}

async function grantStarterPack(userId: string): Promise<void> {
  const sql = await getSql();
  const already = await sql<{ user_id: string }>`
    select user_id from starter_grants where user_id = ${userId} limit 1
  `;
  if (already[0]) return;

  const starters: Array<{
    player: string;
    team: string;
    position: string;
    year: number;
    rarity: string;
    avg?: string;
    hr?: number;
    rbi?: number;
    era?: string;
    wins?: number;
    so?: number;
  }> = [
    {
      player: "Rookie Flash",
      team: "Yankees",
      position: "CF",
      year: 2024,
      rarity: "common",
      avg: ".285",
      hr: 12,
      rbi: 48,
    },
    {
      player: "Ace Rivera",
      team: "Dodgers",
      position: "P",
      year: 2025,
      rarity: "rare",
      era: "2.91",
      wins: 14,
      so: 186,
    },
    {
      player: "Diamond Cruz",
      team: "Braves",
      position: "SS",
      year: 2023,
      rarity: "epic",
      avg: ".312",
      hr: 28,
      rbi: 91,
    },
  ];

  for (const s of starters) {
    const id = newId("card");
    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}, ${userId}, ${s.player}, ${s.team}, ${s.position}, ${s.year}, ${s.rarity},
        ${s.avg ?? null},
        ${s.hr ?? null},
        ${s.rbi ?? null},
        ${s.era ?? null},
        ${s.wins ?? null},
        ${s.so ?? null},
        ${null},
        ${"Starter pack card — welcome to Dugout."},
        ${true}
      )
    `;
  }

  await sql`insert into starter_grants (user_id) values (${userId}) on conflict do nothing`;
}

export async function getProfileByHandle(handle: string, viewerId?: string | null): Promise<Profile | null> {
  const sql = await getSql();
  const rows = await sql<ProfileRow>`
    select user_id, handle, display_name, bio, avatar_url, created_at
    from profiles where lower(handle) = ${handle.toLowerCase()} limit 1
  `;
  const row = rows[0];
  if (!row) return null;

  const [cardCount] = await sql<{ c: number }>`
    select count(*)::int as c from cards where owner_id = ${row.user_id}
  `;
  const [followers] = await sql<{ c: number }>`
    select count(*)::int as c from follows where following_id = ${row.user_id}
  `;
  const [following] = await sql<{ c: number }>`
    select count(*)::int as c from follows where follower_id = ${row.user_id}
  `;

  let isFollowing = false;
  if (viewerId && viewerId !== row.user_id) {
    const f = await sql<{ follower_id: string }>`
      select follower_id from follows
      where follower_id = ${viewerId} and following_id = ${row.user_id}
      limit 1
    `;
    isFollowing = Boolean(f[0]);
  }

  return mapProfile(row, {
    cardCount: Number(cardCount?.c ?? 0),
    followerCount: Number(followers?.c ?? 0),
    followingCount: Number(following?.c ?? 0),
    isFollowing,
  });
}

export async function getProfileByUserId(userId: string): Promise<Profile | null> {
  const sql = await getSql();
  const rows = await sql<ProfileRow>`
    select user_id, handle, display_name, bio, avatar_url, created_at
    from profiles where user_id = ${userId} limit 1
  `;
  return rows[0] ? mapProfile(rows[0]) : null;
}