/**
 * Group reservation queries - typed async functions for the group_reservations table.
 */

import { eq, and, ne, count, sql, isNotNull } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { groupReservations } from '../schema.js';
import type { reservationStatusEnum } from '../schema.js';
type ReservationStatus = (typeof reservationStatusEnum.enumValues)[number];

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

export async function findById(db: DrizzleClient, reservationId: string) {
  const [row] = await db
    .select()
    .from(groupReservations)
    .where(eq(groupReservations.id, reservationId))
    .limit(1);
  return row ?? null;
}

export async function findByGroupDealId(
  db: DrizzleClient,
  groupDealId: string,
  opts: { status?: ReservationStatus } = {},
) {
  const conditions = [eq(groupReservations.groupDealId, groupDealId)];
  if (opts.status !== undefined) {
    conditions.push(eq(groupReservations.status, opts.status));
  }
  return db
    .select()
    .from(groupReservations)
    .where(and(...conditions));
}

export async function findByUserAndGroupDeal(
  db: DrizzleClient,
  userId: string,
  groupDealId: string,
) {
  return db
    .select()
    .from(groupReservations)
    .where(
      and(eq(groupReservations.userId, userId), eq(groupReservations.groupDealId, groupDealId)),
    );
}

/**
 * Counts non-cancelled reservations for a user on a given group deal.
 * Used to enforce perCustomerLimit.
 */
export async function countByUserAndGroupDeal(
  db: DrizzleClient,
  userId: string,
  groupDealId: string,
): Promise<number> {
  const [row] = await db
    .select({ cnt: count() })
    .from(groupReservations)
    .where(
      and(
        eq(groupReservations.userId, userId),
        eq(groupReservations.groupDealId, groupDealId),
        ne(groupReservations.status, 'CANCELLED'),
      ),
    );
  return Number(row?.cnt ?? 0);
}

/**
 * Counts non-cancelled guest reservations for a group deal (encrypted guest email).
 * Used to enforce perCustomerLimit on the guest reserve path.
 */
export async function countByGuestEmailAndGroupDeal(
  db: DrizzleClient,
  guestEmail: string,
  groupDealId: string,
): Promise<number> {
  const [row] = await db
    .select({ cnt: count() })
    .from(groupReservations)
    .where(
      and(
        eq(groupReservations.guestEmail, guestEmail),
        eq(groupReservations.groupDealId, groupDealId),
        ne(groupReservations.status, 'CANCELLED'),
      ),
    );
  return Number(row?.cnt ?? 0);
}

/**
 * Returns all HELD reservations for a group deal.
 * Used during executeGroupDeal to capture all holds.
 */
export async function findHeldByGroupDeal(db: DrizzleClient, groupDealId: string) {
  return db
    .select()
    .from(groupReservations)
    .where(
      and(
        eq(groupReservations.groupDealId, groupDealId),
        eq(groupReservations.status, 'HELD'),
        isNotNull(groupReservations.providerAuthorizationId),
      ),
    );
}

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

export type CreateGroupReservationInput = {
  groupDealId: string;
  dealId: string;
  userId?: string;
  guestEmail?: string;
  guestPhone?: string;
  paymentMethodId?: string;
  quantity: number;
  unitPrice: string;
  totalAmount: string;
  commissionAmount: string;
  vendorAmount: string;
  providerAuthorizationId?: string;
  idempotencyKey: string;
  holdExpiresAt?: Date;
};

export async function create(db: DrizzleClient, input: CreateGroupReservationInput) {
  const [row] = await db
    .insert(groupReservations)
    .values({
      groupDealId: input.groupDealId,
      dealId: input.dealId,
      userId: input.userId,
      guestEmail: input.guestEmail,
      guestPhone: input.guestPhone,
      paymentMethodId: input.paymentMethodId,
      quantity: input.quantity,
      unitPrice: input.unitPrice,
      totalAmount: input.totalAmount,
      commissionAmount: input.commissionAmount,
      vendorAmount: input.vendorAmount,
      providerAuthorizationId: input.providerAuthorizationId,
      idempotencyKey: input.idempotencyKey,
      holdExpiresAt: input.holdExpiresAt,
    })
    .returning();
  return row!;
}

export async function updateStatus(
  db: DrizzleClient,
  reservationId: string,
  newStatus: ReservationStatus,
  extraFields?: Partial<typeof groupReservations.$inferInsert>,
) {
  const [row] = await db
    .update(groupReservations)
    .set({
      status: newStatus,
      ...extraFields,
    })
    .where(eq(groupReservations.id, reservationId))
    .returning();
  return row ?? null;
}

/**
 * Marks a reservation as CAPTURED, sets the providerTransactionId and purchaseId.
 */
export async function setCaptured(
  db: DrizzleClient,
  reservationId: string,
  params: { providerTransactionId: string; orderLineId: string },
) {
  const [row] = await db
    .update(groupReservations)
    .set({
      status: 'CAPTURED',
      providerTransactionId: params.providerTransactionId,
      orderLineId: params.orderLineId,
    })
    .where(eq(groupReservations.id, reservationId))
    .returning();
  return row ?? null;
}

/**
 * Updates holdExpiresAt for a refreshed hold (J5 refresh).
 */
export async function updateHoldExpiry(
  db: DrizzleClient,
  reservationId: string,
  holdExpiresAt: Date,
) {
  const [row] = await db
    .update(groupReservations)
    .set({ holdExpiresAt })
    .where(eq(groupReservations.id, reservationId))
    .returning();
  return row ?? null;
}

/**
 * Returns all reservations with holdExpiresAt within the given threshold.
 * Used to find holds that need refreshing.
 */
export async function findHoldsExpiringBefore(db: DrizzleClient, threshold: Date) {
  return db
    .select()
    .from(groupReservations)
    .where(
      and(
        eq(groupReservations.status, 'HELD'),
        sql`${groupReservations.holdExpiresAt} <= ${threshold.toISOString()}`,
      ),
    );
}