import { eq, count } from "drizzle-orm";
import { database } from "~/database/context";
import * as schema from "~/database/schema";

export type DraftSlot = typeof schema.draftSlots.$inferSelect;
export type NewDraftSlot = typeof schema.draftSlots.$inferInsert;

export interface DraftSlotWithTeam extends DraftSlot {
  team: {
    id: string;
    name: string;
    ownerId: string | null;
  };
}

/**
 * Create a single draft slot
 */
export async function createDraftSlot(data: NewDraftSlot): Promise<DraftSlot> {
  const db = database();
  const [draftSlot] = await db
    .insert(schema.draftSlots)
    .values(data)
    .returning();
  return draftSlot;
}

/**
 * Create multiple draft slots at once
 */
export async function createManyDraftSlots(
  slots: NewDraftSlot[]
): Promise<DraftSlot[]> {
  const db = database();
  return await db.insert(schema.draftSlots).values(slots).returning();
}

/**
 * Find all draft slots for a season, ordered by draft order
 */
export async function findDraftSlotsBySeasonId(
  seasonId: string
): Promise<DraftSlotWithTeam[]> {
  const db = database();
  return await db.query.draftSlots.findMany({
    where: eq(schema.draftSlots.seasonId, seasonId),
    orderBy: (draftSlots, { asc }) => [asc(draftSlots.draftOrder)],
    with: {
      team: {
        columns: {
          id: true,
          name: true,
          ownerId: true,
        },
      },
    },
  }) as DraftSlotWithTeam[];
}

/**
 * Find a draft slot by team ID
 */
export async function findDraftSlotByTeamId(
  teamId: string
): Promise<DraftSlot | undefined> {
  const db = database();
  return await db.query.draftSlots.findFirst({
    where: eq(schema.draftSlots.teamId, teamId),
  });
}

/**
 * Update a draft slot's order
 */
export async function updateDraftSlotOrder(
  id: string,
  draftOrder: number
): Promise<DraftSlot> {
  const db = database();
  const [draftSlot] = await db
    .update(schema.draftSlots)
    .set({ draftOrder, updatedAt: new Date() })
    .where(eq(schema.draftSlots.id, id))
    .returning();
  return draftSlot;
}

/**
 * Delete all draft slots for a season
 */
export async function deleteDraftSlotsBySeasonId(
  seasonId: string
): Promise<void> {
  const db = database();
  await db
    .delete(schema.draftSlots)
    .where(eq(schema.draftSlots.seasonId, seasonId));
}

/**
 * Delete a specific draft slot
 */
export async function deleteDraftSlot(id: string): Promise<void> {
  const db = database();
  await db.delete(schema.draftSlots).where(eq(schema.draftSlots.id, id));
}

/**
 * Set the draft order for all teams in a season
 * This will delete existing slots and create new ones
 */
export async function setDraftOrder(
  seasonId: string,
  teamIds: string[]
): Promise<DraftSlot[]> {
  // Delete existing draft slots for this season
  await deleteDraftSlotsBySeasonId(seasonId);

  // Create new draft slots with the specified order
  const slots = teamIds.map((teamId, index) => ({
    seasonId,
    teamId,
    draftOrder: index + 1,
  }));

  return await createManyDraftSlots(slots);
}

/**
 * Get the number of teams (draft slots) in a season
 */
export async function getNumTeamsInSeason(seasonId: string): Promise<number> {
  const db = database();
  const [result] = await db
    .select({ count: count() })
    .from(schema.draftSlots)
    .where(eq(schema.draftSlots.seasonId, seasonId));
  return result?.count ?? 0;
}

/**
 * Randomize the draft order for a season
 */
export async function randomizeDraftOrder(
  seasonId: string,
  teamIds: string[]
): Promise<DraftSlot[]> {
  // Shuffle the team IDs using Fisher-Yates algorithm
  const shuffled = [...teamIds];
  for (let i = shuffled.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [shuffled[i], shuffled[j]] = [shuffled[j], shuffled[i]];
  }

  return await setDraftOrder(seasonId, shuffled);
}
