import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import type { DealCardDeal } from '@/components/ui/domain/DealCard';
import {
  queryMoreFromVendor,
  querySimilarDeals,
  queryBuyersAlsoBought,
  type RelatedDealRow,
} from './queries';
import { captureCaught } from '@/server/observability/capture.server';

export interface RelatedDeals {
  moreFromVendor: DealCardDeal[];
  similar: DealCardDeal[];
  alsoBought: DealCardDeal[];
}

const EMPTY: RelatedDeals = { moreFromVendor: [], similar: [], alsoBought: [] };
const FETCH_LIMIT = 8;

function toDealCard(r: RelatedDealRow): DealCardDeal {
  const orig = Number(r.minPrice);
  const pct = Number(r.maxDiscountPercent);
  return {
    id: r.id,
    title: r.title,
    vendorName: r.vendorName,
    city: r.city ?? '',
    originalPrice: orig,
    discountedPrice: Math.round(orig * (100 - pct)) / 100,
    discountPercent: pct,
    stockRemaining: r.stockRemaining != null ? Number(r.stockRemaining) : undefined,
    stockTotal: r.quantityTotal != null ? Number(r.quantityTotal) : undefined,
    windowEnd: r.windowEnd ? new Date(r.windowEnd).toISOString() : null,
    dealType: r.dealType as DealCardDeal['dealType'],
    imageSrc: r.imageSrc ?? '',
    imageAlt: r.title,
    isHotDeal: false,
    heSlug: r.heSlug ?? undefined,
  };
}

export async function queryRelatedDeals(
  db: DrizzleClient,
  deal: { id: string; vendorId: string; categoryId: string | null; tagIds?: string[] },
  opts: { userId?: string },
): Promise<RelatedDeals> {
  try {
    let tagIds = deal.tagIds;
    if (tagIds === undefined) {
      const tagResult = (await db.execute(sql`
        SELECT tag_id AS "tagId" FROM deal_tag_assignments WHERE deal_id = ${deal.id}
      `)) as { rows: Array<{ tagId: string }> };
      tagIds = (tagResult.rows as Array<{ tagId: string }>).map((r) => r.tagId);
    }

    const [vendorRows, similarRows, boughtRows] = await Promise.all([
      queryMoreFromVendor(db, { dealId: deal.id, vendorId: deal.vendorId, limit: FETCH_LIMIT }),
      querySimilarDeals(db, {
        dealId: deal.id,
        categoryId: deal.categoryId,
        tagIds,
        limit: FETCH_LIMIT,
      }),
      queryBuyersAlsoBought(db, { dealId: deal.id, limit: FETCH_LIMIT }),
    ]);

    const seen = new Set<string>([deal.id]);

    if (opts.userId) {
      const ownedResult = (await db.execute(sql`
        SELECT ds.deal_id AS "dealId"
        FROM "order" o
        JOIN order_line ol ON ol.order_id = o.id
        JOIN deal_skus ds ON ds.id = ol.variant_id
        WHERE o.buyer_user_id = ${opts.userId}
      `)) as { rows: Array<{ dealId: string }> };
      for (const row of ownedResult.rows as Array<{ dealId: string }>) {
        seen.add(row.dealId);
      }
    }

    const consume = (rows: RelatedDealRow[]): DealCardDeal[] => {
      const result: DealCardDeal[] = [];
      for (const r of rows) {
        if (seen.has(r.id) || !r.imageSrc) continue;
        seen.add(r.id);
        result.push(toDealCard(r));
        if (result.length === 6) break;
      }
      return result;
    };

    const alsoBought = consume(boughtRows);
    const similar = consume(similarRows);
    const moreFromVendor = consume(vendorRows);

    return { alsoBought, similar, moreFromVendor };
  } catch (err) {
    captureCaught(err, { scope: 'server.catalog.related.queryRelatedDeals', severity: 'warning' });
    return EMPTY;
  }
}