/**
 * Wishlist query module — all DB access for wishlist_items goes through here.
 *
 * toggleWishlist   — add/remove a deal from a user's wishlist (returns new state)
 * getWishlist      — full wishlist with deal + vendor snapshots
 * getWishlistedDealIds — lightweight set of saved deal IDs (for UI badge state)
 * setWishlistNotify    — update notify flag for a single item
 * setWishlistNotifyAll — bulk notify toggle for all items
 * getUsersToNotifyForDeal — used by push-notification cron
 * getExpiringWishlistDeals — deals expiring within 24 h with notify=true
 * getWishlistUpsell — active wishlisted deals excluding given IDs (cart upsell)
 */

import { eq, and, between, sql, inArray, notInArray, or, gt, isNull } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { bumpMhVersion } from '@/server/auth/session.js';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import { wishlistItems, deals, vendors, dealTranslations } from '../schema.js';

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

export type ToggleResult = { saved: boolean; notify: boolean };

export type WishlistRow = {
  id: string;
  dealId: string;
  notify: boolean;
  createdAt: Date;
  deal: {
    id: string;
    title: string;
    /** Minimum SKU price (cache col, may be null before first cache refresh). */
    discountedPrice: string | null;
    /** Maximum SKU original price (cache col, may be null before first cache refresh). */
    originalPrice: string | null;
    /** Maximum discount percent across SKUs (cache col, may be null). */
    discountPercent: number | null;
    dealState: string;
    windowEnd: Date | null;
    vendorId: string;
    vendorName: string;
    heSlug?: string | null;
  };
};

// ---------------------------------------------------------------------------
// toggleWishlist
// ---------------------------------------------------------------------------

/**
 * Toggle a deal in/out of the user's wishlist.
 * If it exists → delete → { saved: false, notify: false }
 * If it doesn't → insert → { saved: true, notify }
 */
export async function toggleWishlist(
  db: DrizzleClient,
  userId: string,
  dealId: string,
  notify = false,
): Promise<ToggleResult> {
  const [existing] = await db
    .select({ id: wishlistItems.id, notify: wishlistItems.notify })
    .from(wishlistItems)
    .where(and(eq(wishlistItems.userId, userId), eq(wishlistItems.dealId, dealId)));

  if (existing) {
    await db.delete(wishlistItems).where(eq(wishlistItems.id, existing.id));
    await bumpMhVersion(db, userId);
    return { saved: false, notify: false };
  }

  const [inserted] = await db
    .insert(wishlistItems)
    .values({ userId, dealId, notify })
    .returning({ id: wishlistItems.id, notify: wishlistItems.notify });

  await bumpMhVersion(db, userId);
  return { saved: true, notify: inserted?.notify ?? notify };
}

// ---------------------------------------------------------------------------
// getWishlist
// ---------------------------------------------------------------------------

/** Return the full wishlist with deal + vendor snapshots, ordered oldest-first. */
export async function getWishlist(db: DrizzleClient, userId: string): Promise<WishlistRow[]> {
  return db
    .select({
      id: wishlistItems.id,
      dealId: wishlistItems.dealId,
      notify: wishlistItems.notify,
      createdAt: wishlistItems.createdAt,
      deal: {
        id: deals.id,
        title: deals.title,
        discountedPrice: deals.minPrice,
        originalPrice: deals.maxPrice,
        discountPercent: deals.maxDiscountPercent,
        dealState: deals.dealState,
        windowEnd: deals.windowEnd,
        vendorId: deals.vendorId,
        vendorName: vendors.displayName,
        heSlug: dealTranslations.slug,
      },
    })
    .from(wishlistItems)
    .innerJoin(deals, eq(wishlistItems.dealId, deals.id))
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(
      dealTranslations,
      and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
    )
    .where(
      and(
        eq(wishlistItems.userId, userId),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .orderBy(wishlistItems.createdAt);
}

// ---------------------------------------------------------------------------
// getWishlistedDealIds
// ---------------------------------------------------------------------------

/** Return just the deal IDs saved by this user (for fast UI badge checks). */
export async function getWishlistedDealIds(db: DrizzleClient, userId: string): Promise<string[]> {
  const rows = await db
    .select({ dealId: wishlistItems.dealId })
    .from(wishlistItems)
    .where(eq(wishlistItems.userId, userId));
  return rows.map((r) => r.dealId);
}

// ---------------------------------------------------------------------------
// setWishlistNotify
// ---------------------------------------------------------------------------

/** Set the notify flag for a single wishlist entry. */
export async function setWishlistNotify(
  db: DrizzleClient,
  userId: string,
  dealId: string,
  notify: boolean,
): Promise<void> {
  const [updated] = await db
    .update(wishlistItems)
    .set({ notify })
    .where(and(eq(wishlistItems.userId, userId), eq(wishlistItems.dealId, dealId)))
    .returning({ id: wishlistItems.id });

  if (updated) await bumpMhVersion(db, userId);
}

// ---------------------------------------------------------------------------
// setWishlistNotifyAll
// ---------------------------------------------------------------------------

/** Bulk-set notify flag for all of a user's wishlist items. */
export async function setWishlistNotifyAll(
  db: DrizzleClient,
  userId: string,
  notify: boolean,
): Promise<void> {
  const updated = await db
    .update(wishlistItems)
    .set({ notify })
    .where(eq(wishlistItems.userId, userId))
    .returning({ id: wishlistItems.id });

  if (updated.length > 0) await bumpMhVersion(db, userId);
}

// ---------------------------------------------------------------------------
// getUsersToNotifyForDeal
// ---------------------------------------------------------------------------

/** Return user IDs that have a deal wishlisted with notify=true (for push cron). */
export async function getUsersToNotifyForDeal(
  db: DrizzleClient,
  dealId: string,
): Promise<string[]> {
  const rows = await db
    .select({ userId: wishlistItems.userId })
    .from(wishlistItems)
    .where(and(eq(wishlistItems.dealId, dealId), eq(wishlistItems.notify, true)));
  return rows.map((r) => r.userId);
}

// ---------------------------------------------------------------------------
// getExpiringWishlistDeals
// ---------------------------------------------------------------------------

/**
 * Return deals that expire within the next 24 hours and have at least one
 * wishlisted user with notify=true. Grouped by dealId.
 * Used by the expiry-notification cron job.
 */
export async function getExpiringWishlistDeals(
  db: DrizzleClient,
): Promise<{ dealId: string; userIds: string[] }[]> {
  const now = new Date();
  const in24h = new Date(now.getTime() + 24 * 60 * 60 * 1000);

  const rows = await db
    .select({ dealId: wishlistItems.dealId, userId: wishlistItems.userId })
    .from(wishlistItems)
    .innerJoin(deals, eq(wishlistItems.dealId, deals.id))
    .where(
      and(
        eq(wishlistItems.notify, true),
        eq(deals.dealState, 'ACTIVE'),
        between(deals.windowEnd, now, in24h),
      ),
    );

  const grouped = new Map<string, string[]>();
  for (const row of rows) {
    const arr = grouped.get(row.dealId) ?? [];
    arr.push(row.userId);
    grouped.set(row.dealId, arr);
  }
  return Array.from(grouped.entries()).map(([dealId, userIds]) => ({ dealId, userIds }));
}

// ---------------------------------------------------------------------------
// getWishlistUpsell
// ---------------------------------------------------------------------------

/**
 * Return up to `limit` active wishlisted deals, excluding the given deal IDs.
 * Used on the cart/checkout page to surface "you also saved…" upsell cards.
 */
export async function getWishlistUpsell(
  db: DrizzleClient,
  userId: string,
  excludeDealIds: string[],
  limit = 3,
): Promise<WishlistRow[]> {
  return db
    .select({
      id: wishlistItems.id,
      dealId: wishlistItems.dealId,
      notify: wishlistItems.notify,
      createdAt: wishlistItems.createdAt,
      deal: {
        id: deals.id,
        title: deals.title,
        discountedPrice: deals.minPrice,
        originalPrice: deals.maxPrice,
        discountPercent: deals.maxDiscountPercent,
        dealState: deals.dealState,
        windowEnd: deals.windowEnd,
        vendorId: deals.vendorId,
        vendorName: vendors.displayName,
        heSlug: dealTranslations.slug,
      },
    })
    .from(wishlistItems)
    .innerJoin(deals, eq(wishlistItems.dealId, deals.id))
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(
      dealTranslations,
      and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
    )
    .where(
      and(
        eq(wishlistItems.userId, userId),
        eq(deals.dealState, 'ACTIVE'),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
        excludeDealIds.length > 0
          ? sql`${deals.id} NOT IN (${sql.join(
              excludeDealIds.map((id) => sql`${id}::uuid`),
              sql`, `,
            )})`
          : sql`true`,
      ),
    )
    .limit(limit);
}

// ---------------------------------------------------------------------------
// bulkInsertWishlistIgnoreConflict
// ---------------------------------------------------------------------------

/**
 * Bulk-insert wishlist entries for a user, skipping conflicts and filtering
 * out deals that don't exist or are expired/rejected/archived.
 *
 * Returns:
 *   merged  — number of rows actually inserted
 *   skipped — number of dealIds filtered out (invalid/expired/not found)
 *   total   — same as merged (rows newly in wishlist)
 */
export async function bulkInsertWishlistIgnoreConflict(
  db: DrizzleClient,
  userId: string,
  dealIds: string[],
): Promise<{ merged: number; skipped: number; total: number }> {
  if (dealIds.length === 0) return { merged: 0, skipped: 0, total: 0 };

  // Filter to deals that exist and are not expired/rejected/archived
  const validDeals = await db
    .select({ id: deals.id })
    .from(deals)
    .where(
      and(
        inArray(deals.id, dealIds),
        notInArray(deals.dealState, ['EXPIRED', 'REJECTED', 'ARCHIVED']),
      ),
    );

  const validIds = validDeals.map((d) => d.id);
  const skipped = dealIds.length - validIds.length;

  if (validIds.length === 0) {
    return { merged: 0, skipped, total: 0 };
  }

  const rows = validIds.map((dealId) => ({ userId, dealId }));

  const result = await db
    .insert(wishlistItems)
    .values(rows)
    .onConflictDoNothing()
    .returning({ dealId: wishlistItems.dealId });

  const merged = result.length;
  if (merged > 0) await bumpMhVersion(db, userId);
  return { merged, skipped, total: merged };
}