import { and, desc, eq, gt, inArray, isNull, or, sql } from 'drizzle-orm';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import type { DealType } from '@/lib/deal-types';
import type { DrizzleClient } from '../client.js';
import {
  userRecentlyViewed,
  deals,
  vendors,
  dealImages,
  dealVariantAxes,
  dealSkus,
} from '../schema.js';

export interface RecentlyViewedDeal {
  id: string;
  title: string;
  vendorName: string;
  city: string;
  originalPrice: number;
  discountedPrice: number;
  imageSrc: string;
  imageAlt: string;
  windowEnd: string | null;
  dealType: DealType;
  discountPercent: number;
  vendorAvatarUrl?: string | null;
  minPrice?: string | null;
  maxPrice?: string | null;
  maxDiscountPercent?: number | null;
  axesCount?: number;
  defaultSkuId?: string | null;
  skuCount?: number;
  viewedAt: string;
}

export async function recordRecentlyViewed(
  db: DrizzleClient,
  userId: string,
  dealId: string,
): Promise<void> {
  await db
    .insert(userRecentlyViewed)
    .values({ userId, dealId })
    .onConflictDoUpdate({
      target: [userRecentlyViewed.userId, userRecentlyViewed.dealId],
      set: { viewedAt: sql`NOW()` },
    });

  await db.delete(userRecentlyViewed).where(
    sql`${userRecentlyViewed.userId} = ${userId}::uuid AND ${userRecentlyViewed.dealId} IN (
      SELECT deal_id FROM (
        SELECT deal_id, ROW_NUMBER() OVER (ORDER BY viewed_at DESC) AS rn
        FROM user_recently_viewed WHERE user_id = ${userId}::uuid
      ) ranked WHERE rn > 20
    )`,
  );
}

export async function clearRecentlyViewed(db: DrizzleClient, userId: string): Promise<void> {
  await db.delete(userRecentlyViewed).where(eq(userRecentlyViewed.userId, userId));
}

function mapRow(r: {
  id: string;
  title: string;
  dealType: DealType;
  dealState: string;
  minPrice: string | null;
  maxPrice: string | null;
  maxDiscountPercent: number | null;
  windowEnd: Date | null;
  primaryImageUrl: string | null;
  vendorName: string;
  vendorLogoUrl: string | null;
  axesCount: number;
  defaultSkuId: string | null;
  skuCount: number;
  viewedAt: Date;
}): RecentlyViewedDeal {
  const discountedPrice = parseFloat(r.minPrice ?? '0');
  const pct = r.maxDiscountPercent;
  const originalPrice = pct ? Math.round((discountedPrice * 100) / (100 - pct)) : discountedPrice;

  return {
    id: r.id,
    title: r.title,
    vendorName: r.vendorName,
    city: '',
    originalPrice,
    discountedPrice,
    imageSrc: r.primaryImageUrl ?? '',
    imageAlt: r.title,
    windowEnd: r.windowEnd ? r.windowEnd.toISOString() : null,
    dealType: r.dealType,
    discountPercent: pct ?? 0,
    vendorAvatarUrl: r.vendorLogoUrl,
    minPrice: r.minPrice,
    maxPrice: r.maxPrice,
    maxDiscountPercent: r.maxDiscountPercent,
    axesCount: r.axesCount,
    defaultSkuId: r.defaultSkuId,
    skuCount: r.skuCount,
    viewedAt: r.viewedAt.toISOString(),
  };
}

export async function getRecentlyViewed(
  db: DrizzleClient,
  userId: string,
): Promise<RecentlyViewedDeal[]> {
  const axesCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM ${dealVariantAxes}
    WHERE ${dealVariantAxes.dealId} = ${deals.id}
    AND ${dealVariantAxes.isActive} = true
  )`;
  const defaultSkuIdSq = sql<string | null>`(
    SELECT id FROM ${dealSkus}
    WHERE ${dealSkus.dealId} = ${deals.id}
    AND ${dealSkus.optionIdsHash} = 'default'
    LIMIT 1
  )`;
  const skuCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM deal_skus s2 WHERE s2.deal_id = ${deals.id}
  )`;

  const rows = await db
    .select({
      id: deals.id,
      title: deals.title,
      dealType: deals.dealType,
      dealState: deals.dealState,
      minPrice: deals.minPrice,
      maxPrice: deals.maxPrice,
      maxDiscountPercent: deals.maxDiscountPercent,
      windowEnd: deals.windowEnd,
      primaryImageUrl: dealImages.url,
      vendorName: vendors.displayName,
      vendorLogoUrl: vendors.logoUrl,
      axesCount: axesCountSq,
      defaultSkuId: defaultSkuIdSq,
      skuCount: skuCountSq,
      viewedAt: userRecentlyViewed.viewedAt,
    })
    .from(userRecentlyViewed)
    .innerJoin(deals, eq(userRecentlyViewed.dealId, deals.id))
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
    .where(
      and(
        eq(userRecentlyViewed.userId, userId),
        eq(deals.dealState, 'ACTIVE'),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .orderBy(desc(userRecentlyViewed.viewedAt))
    .limit(20);

  return rows.map(mapRow);
}

export async function getBatchDeals(
  db: DrizzleClient,
  dealIds: string[],
): Promise<Omit<RecentlyViewedDeal, 'viewedAt'>[]> {
  if (dealIds.length === 0) return [];

  const axesCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM ${dealVariantAxes}
    WHERE ${dealVariantAxes.dealId} = ${deals.id}
    AND ${dealVariantAxes.isActive} = true
  )`;
  const defaultSkuIdSq = sql<string | null>`(
    SELECT id FROM ${dealSkus}
    WHERE ${dealSkus.dealId} = ${deals.id}
    AND ${dealSkus.optionIdsHash} = 'default'
    LIMIT 1
  )`;
  const skuCountSq = sql<number>`(
    SELECT COUNT(*)::int FROM deal_skus s2 WHERE s2.deal_id = ${deals.id}
  )`;

  const rows = await db
    .select({
      id: deals.id,
      title: deals.title,
      dealType: deals.dealType,
      dealState: deals.dealState,
      minPrice: deals.minPrice,
      maxPrice: deals.maxPrice,
      maxDiscountPercent: deals.maxDiscountPercent,
      windowEnd: deals.windowEnd,
      primaryImageUrl: dealImages.url,
      vendorName: vendors.displayName,
      vendorLogoUrl: vendors.logoUrl,
      axesCount: axesCountSq,
      defaultSkuId: defaultSkuIdSq,
      skuCount: skuCountSq,
      viewedAt: sql<Date>`NOW()`,
    })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
    .where(
      and(
        inArray(deals.id, dealIds),
        eq(deals.dealState, 'ACTIVE'),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    );

  return rows.map((r) => {
    // Neon HTTP returns sql`NOW()` as a raw string, not a Date — pass a real Date so
    // mapRow's .toISOString() call doesn't throw. viewedAt is discarded anyway.
    const { viewedAt: _, ...rest } = mapRow({ ...r, viewedAt: new Date() });
    return rest;
  });
}