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

export type CanonicalParticipant = typeof schema.participants.$inferSelect;
export type NewCanonicalParticipant = typeof schema.participants.$inferInsert;

export async function createCanonicalParticipant(
  data: NewCanonicalParticipant
): Promise<CanonicalParticipant> {
  const db = database();
  const [participant] = await db
    .insert(schema.participants)
    .values(data)
    .returning();
  return participant;
}

export async function getCanonicalParticipantById(
  id: string
): Promise<CanonicalParticipant | null> {
  const db = database();
  const results = await db
    .select()
    .from(schema.participants)
    .where(eq(schema.participants.id, id))
    .limit(1);
  return results[0] ?? null;
}

export async function findCanonicalParticipantsBySport(
  sportId: string
): Promise<CanonicalParticipant[]> {
  const db = database();
  return await db
    .select()
    .from(schema.participants)
    .where(eq(schema.participants.sportId, sportId))
    .orderBy(asc(schema.participants.name));
}

export async function findCanonicalParticipantBySportName(
  sportId: string,
  name: string
): Promise<CanonicalParticipant | null> {
  const db = database();
  const results = await db
    .select()
    .from(schema.participants)
    .where(
      and(
        eq(schema.participants.sportId, sportId),
        eq(schema.participants.name, name)
      )
    )
    .limit(1);
  return results[0] ?? null;
}

export async function upsertCanonicalParticipantBySportName(
  sportId: string,
  name: string,
  extra?: Partial<NewCanonicalParticipant>
): Promise<CanonicalParticipant> {
  const existing = await findCanonicalParticipantBySportName(sportId, name);

  if (existing) {
    return existing;
  }

  return await createCanonicalParticipant({
    sportId,
    name,
    ...extra,
  });
}