/**
 * Group waitlist queries - typed async functions for the group_waitlist table.
 */

import { eq, asc, count, sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { groupWaitlist } from '../schema.js';

// ─── Read helpers ─────────────────────────────────────────────────────────────

/**
 * Returns all waitlist entries for a group deal, sorted by position ascending (next in line first).
 */
export async function findByGroupDealId(db: DrizzleClient, groupDealId: string) {
  return db
    .select()
    .from(groupWaitlist)
    .where(eq(groupWaitlist.groupDealId, groupDealId))
    .orderBy(asc(groupWaitlist.position));
}

/**
 * Returns the current position (1-indexed) of a specific waitlist entry.
 * Returns null if the entry does not exist.
 */
export async function getPosition(db: DrizzleClient, waitlistId: string): Promise<number | null> {
  const [row] = await db
    .select({ position: groupWaitlist.position })
    .from(groupWaitlist)
    .where(eq(groupWaitlist.id, waitlistId))
    .limit(1);
  return row?.position ?? null;
}

/**
 * Returns the total number of entries in the waitlist for a group deal.
 */
export async function countByGroupDeal(db: DrizzleClient, groupDealId: string): Promise<number> {
  const [row] = await db
    .select({ cnt: count() })
    .from(groupWaitlist)
    .where(eq(groupWaitlist.groupDealId, groupDealId));
  return Number(row?.cnt ?? 0);
}

// ─── Write helpers ────────────────────────────────────────────────────────────

export type AddWaitlistInput = {
  groupDealId: string;
  userId?: string;
  guestEmail?: string;
};

/**
 * Inserts a new waitlist entry at the next available position.
 * Uses MAX(position) + 1 to ensure no gaps on concurrent inserts.
 * Falls back to position 1 if the list is empty.
 */
export async function add(db: DrizzleClient, input: AddWaitlistInput) {
  // Compute next position atomically via a subquery
  const [maxRow] = await db
    .select({ maxPosition: sql<number>`COALESCE(MAX(${groupWaitlist.position}), 0)` })
    .from(groupWaitlist)
    .where(eq(groupWaitlist.groupDealId, input.groupDealId));

  const nextPosition = (maxRow?.maxPosition ?? 0) + 1;

  const [row] = await db
    .insert(groupWaitlist)
    .values({
      groupDealId: input.groupDealId,
      userId: input.userId,
      guestEmail: input.guestEmail,
      position: nextPosition,
    })
    .returning();
  return row!;
}

/**
 * Removes a waitlist entry by ID.
 */
export async function removeById(db: DrizzleClient, waitlistId: string) {
  const [row] = await db.delete(groupWaitlist).where(eq(groupWaitlist.id, waitlistId)).returning();
  return row ?? null;
}

/**
 * Gets and removes the first waitlist entry (lowest position) for a group deal.
 * Returns null if the waitlist is empty.
 * Used by promoteFromWaitlist to claim the next eligible user.
 */
export async function promoteNext(db: DrizzleClient, groupDealId: string) {
  // Find the entry with the minimum position
  const [minEntry] = await db
    .select()
    .from(groupWaitlist)
    .where(eq(groupWaitlist.groupDealId, groupDealId))
    .orderBy(asc(groupWaitlist.position))
    .limit(1);

  if (!minEntry) return null;

  // Delete it and return it
  const [deleted] = await db
    .delete(groupWaitlist)
    .where(eq(groupWaitlist.id, minEntry.id))
    .returning();
  return deleted ?? null;
}