/**
 * Query-layer module for deal autocomplete suggestions.
 *
 * Owns ALL query construction for GET /api/deals/suggest:
 *   - Input schema (exported for route re-use)
 *   - 4 parallel ilike sub-queries: deals, categories, tags, vendors
 *   - Typed result shapes
 *
 * Per query-layer exclusivity rule, the API route must call runSuggest() and
 * must not inline any DB logic. No db.transaction() — neon-http driver.
 */

import { executeRows } from '../execute-rows.js';
import { and, eq, ilike, inArray, isNull, isNotNull, gt, desc, sql, or } from 'drizzle-orm';
import { z } from 'zod';
import type { DrizzleClient } from '../client.js';
import type { DealType } from '@/lib/deal-types';
import { deals, vendors, dealImages, dealTranslations } from '../schema.js';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import { getLanguageCached } from '@/server/i18n/languages/cache.js';

// ─── Types ───────────────────────────────────────────────────────────────────

export interface DealSuggestion {
  id: string;
  title: string;
  vendorName: string;
  discountedPrice: number;
  discountPercent: number;
  imageSrc: string;
  dealType: DealType;
  slug?: string;
}

export interface EntitySuggestion {
  slug: string;
  name: string;
}

export interface VendorSuggestion {
  id: string;
  displayName: string;
  logoUrl: string;
  city: string;
}

export interface SuggestResult {
  deals: DealSuggestion[];
  categories: EntitySuggestion[];
  tags: EntitySuggestion[];
  vendors: VendorSuggestion[];
}

// ─── Input schema ────────────────────────────────────────────────────────────

export const SuggestInputSchema = z.object({
  q: z.string().min(1).max(100),
  locale: z.enum(['he', 'en']).optional().default('he'),
  limit: z.coerce.number().int().min(1).max(10).optional().default(6),
});

export type SuggestInput = z.infer<typeof SuggestInputSchema>;

// ─── Sub-query: deals ─────────────────────────────────────────────────────────

async function suggestDeals(
  db: DrizzleClient,
  q: string,
  locale: string,
  limit: number,
): Promise<DealSuggestion[]> {
  const pattern = `${q}%`;
  const now = new Date();

  // For non-Hebrew locales, try the translated title path first.
  if (locale !== 'he') {
    const langRow = await getLanguageCached(locale);
    if (langRow) {
      // Query dealTranslations joined to deals joined to vendors, LEFT JOIN dealImages.
      const rows = await db
        .select({
          id: deals.id,
          title: dealTranslations.title,
          vendorName: vendors.displayName,
          discountedPrice: deals.minPrice,
          discountPercent: deals.maxDiscountPercent,
          imageSrc: dealImages.url,
          dealType: deals.dealType,
          heSlug: sql<
            string | null
          >`(SELECT dt2.slug FROM deal_translations dt2 WHERE dt2.deal_id = ${deals.id} AND dt2.locale = 'he' LIMIT 1)`,
        })
        .from(dealTranslations)
        .innerJoin(deals, eq(dealTranslations.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(dealTranslations.locale, locale),
            eq(dealTranslations.status, 'OK'),
            ilike(dealTranslations.title, pattern),
            eq(deals.dealState, 'ACTIVE'),
            isNotNull(deals.minPrice),
            or(isNull(deals.windowEnd), gt(deals.windowEnd, now)),
            inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
          ),
        )
        .orderBy(desc(deals.maxDiscountPercent))
        .limit(limit);

      return rows.map((r) => ({
        id: r.id,
        title: r.title,
        vendorName: r.vendorName,
        discountedPrice: parseFloat(r.discountedPrice ?? '0'),
        discountPercent: r.discountPercent ?? 0,
        imageSrc: r.imageSrc ?? '',
        dealType: r.dealType as DealType,
        slug: r.heSlug ?? undefined,
      }));
    }
  }

  // Hebrew (default) path — query deals.title directly.
  const rows = await db
    .select({
      id: deals.id,
      title: deals.title,
      vendorName: vendors.displayName,
      discountedPrice: deals.minPrice,
      discountPercent: deals.maxDiscountPercent,
      imageSrc: dealImages.url,
      dealType: deals.dealType,
      slug: dealTranslations.slug,
    })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
    .leftJoin(
      dealTranslations,
      and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
    )
    .where(
      and(
        ilike(deals.title, pattern),
        eq(deals.dealState, 'ACTIVE'),
        isNotNull(deals.minPrice),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, now)),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
      ),
    )
    .orderBy(desc(deals.maxDiscountPercent))
    .limit(limit);

  return rows.map((r) => ({
    id: r.id,
    title: r.title,
    vendorName: r.vendorName,
    discountedPrice: parseFloat(r.discountedPrice ?? '0'),
    discountPercent: r.discountPercent ?? 0,
    imageSrc: r.imageSrc ?? '',
    dealType: r.dealType as DealType,
    slug: r.slug ?? undefined,
  }));
}

// ─── Sub-query: categories ────────────────────────────────────────────────────

async function suggestCategories(
  db: DrizzleClient,
  q: string,
  locale: string,
  limit: number,
): Promise<EntitySuggestion[]> {
  const rows = await db.execute(
    sql`SELECT c.slug, ct.name
        FROM deal_categories c
        JOIN category_translations ct ON ct.category_id = c.id AND ct.locale = ${locale}
        WHERE c.is_active = true
          AND ct.name ILIKE ${`${q}%`}
        ORDER BY c.sort_order ASC
        LIMIT ${limit}`,
  );

  return executeRows<{ slug: string; name: string }>(rows).map((r) => ({
    slug: r.slug,
    name: r.name,
  }));
}

// ─── Sub-query: tags ──────────────────────────────────────────────────────────

async function suggestTags(
  db: DrizzleClient,
  q: string,
  locale: string,
  limit: number,
): Promise<EntitySuggestion[]> {
  const rows = await db.execute(
    sql`SELECT t.slug, tt.name
        FROM deal_tags t
        JOIN tag_translations tt ON tt.tag_id = t.id AND tt.locale = ${locale}
        WHERE t.is_active = true
          AND tt.name ILIKE ${`${q}%`}
        ORDER BY tt.name ASC
        LIMIT ${limit}`,
  );

  return executeRows<{ slug: string; name: string }>(rows).map((r) => ({
    slug: r.slug,
    name: r.name,
  }));
}

// ─── Sub-query: vendors ───────────────────────────────────────────────────────

async function suggestVendors(
  db: DrizzleClient,
  q: string,
  limit: number,
): Promise<VendorSuggestion[]> {
  const pattern = `${q}%`;

  const rows = await db
    .select({
      id: vendors.id,
      displayName: vendors.displayName,
      logoUrl: vendors.logoUrl,
    })
    .from(vendors)
    .where(
      and(
        ilike(vendors.displayName, pattern),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
      ),
    )
    .orderBy(desc(vendors.totalSales))
    .limit(limit);

  return rows.map((r) => ({
    id: r.id,
    displayName: r.displayName,
    logoUrl: r.logoUrl ?? '',
    // vendors table has no city column — city lives on vendorAddresses (too expensive for typeahead)
    city: '',
  }));
}

// ─── Main entry point ─────────────────────────────────────────────────────────

/**
 * Run all 4 autocomplete sub-queries in parallel.
 *
 * Returns typed SuggestResult. All sub-queries fail independently — if one
 * rejects, the Promise.all will bubble up; callers should catch and return
 * partial/empty results if needed.
 */
export async function runSuggest(db: DrizzleClient, input: SuggestInput): Promise<SuggestResult> {
  const { q, locale, limit } = input;

  const [dealResults, categoryResults, tagResults, vendorResults] = await Promise.all([
    suggestDeals(db, q, locale, limit),
    suggestCategories(db, q, locale, limit),
    suggestTags(db, q, locale, limit),
    suggestVendors(db, q, limit),
  ]);

  return {
    deals: dealResults,
    categories: categoryResults,
    tags: tagResults,
    vendors: vendorResults,
  };
}