/**
 * Club membership queries.
 */

import { eq, and, desc } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { clubMemberships, vendors } from '../schema.js';

/**
 * Ensure a club membership exists for user+vendor.
 * If one already exists (even inactive), reactivates it.
 * Returns the membership row.
 */
export async function ensureMembership(
  db: DrizzleClient,
  userId: string,
  vendorId: string,
  joinedViaOrderLineId: string,
) {
  const existing = await db
    .select()
    .from(clubMemberships)
    .where(and(eq(clubMemberships.userId, userId), eq(clubMemberships.vendorId, vendorId)))
    .limit(1);

  if (existing[0]) {
    if (!existing[0].isActive) {
      const [updated] = await db
        .update(clubMemberships)
        .set({ isActive: true })
        .where(eq(clubMemberships.id, existing[0].id))
        .returning();
      return updated!;
    }
    return existing[0];
  }

  const [row] = await db
    .insert(clubMemberships)
    .values({ userId, vendorId, joinedViaOrderLineId })
    .returning();
  return row!;
}

export async function listUserMemberships(db: DrizzleClient, userId: string) {
  return db
    .select()
    .from(clubMemberships)
    .where(and(eq(clubMemberships.userId, userId), eq(clubMemberships.isActive, true)));
}

export async function listVendorMembers(db: DrizzleClient, vendorId: string) {
  return db
    .select()
    .from(clubMemberships)
    .where(and(eq(clubMemberships.vendorId, vendorId), eq(clubMemberships.isActive, true)));
}

// ─── Customer list queries ────────────────────────────────────────────────────

/**
 * Canonical implementation of subscribed-clubs lookup.
 * Returns vendors the user has an active club membership with.
 * Returns empty array for guests (undefined userId).
 */
export async function listSubscribedClubsByUser(
  db: DrizzleClient,
  userId: string | undefined,
  limit: number,
) {
  if (!userId) return [];
  const rows = await db
    .select({ vendor: vendors })
    .from(clubMemberships)
    .innerJoin(vendors, eq(clubMemberships.vendorId, vendors.id))
    .where(and(eq(clubMemberships.userId, userId), eq(clubMemberships.isActive, true)))
    .orderBy(desc(clubMemberships.joinedAt))
    .limit(limit);
  return rows.map((r) => r.vendor);
}