/**
 * Feed queries — unified data layer for the Near-You feature.
 *
 * queryDealsFeed       — paginated deals with geo + hours + tag + category filters
 * buildHoursPredicate  — hours-mode → SQL fragment descriptor
 * canonicalFilterHash  — lives in @/lib/geo/canonicalFilterHash (shared client+server)
 */

import { executeRows } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import type { FeedFilter } from '@/server/schemas/feed.js';
import type { DealCardDeal } from '@/components/ui/domain/DealCard/index.js';
import type { DealType } from '@/lib/deal-types';
import { captureCaught } from '@/server/observability/capture.server.js';
import { notSoldOutSqlD } from './sold-out-filter.js';
import { formatMinutesAsTime } from '@/lib/format';
import { TZ } from '@/lib/datetime';

// ---------------------------------------------------------------------------
// Hours predicate
// ---------------------------------------------------------------------------

/**
 * Map JS day-of-week (0=Sun … 6=Sat) to the column prefix in business_hours.
 * Schema uses per-day columns: sundayOpen / sundayClose / sundayClosed, etc.
 */
const DOW_PREFIX = [
  'sunday',
  'monday',
  'tuesday',
  'wednesday',
  'thursday',
  'friday',
  'saturday',
] as const;

export interface HoursPredicate {
  /** SQL fragment to embed in WHERE (no leading AND). References alias `bh`. */
  sql: string;
  params: unknown[];
}

/**
 * Build a SQL predicate for the hours filter mode.
 *
 * Returns `null` for mode='any'.
 * Returned SQL references table alias `bh` joined as:
 *   LEFT JOIN business_hours bh ON bh.vendor_id = v.id
 */
export function buildHoursPredicate(hours: FeedFilter['hours']): HoursPredicate | null {
  if (hours.mode === 'any') return null;

  if (hours.mode === 'openNow') {
    // CASE WHEN per weekday using Israel local time.
    // EXTRACT(dow FROM now() AT TIME ZONE 'Asia/Jerusalem') → 0=Sun…6=Sat
    const cases = DOW_PREFIX.map(
      (prefix, dow) =>
        `WHEN dow_val.dow = ${dow} THEN ` +
        `(NOT bh.${prefix}_closed AND ` +
        `bh.${prefix}_open IS NOT NULL AND ` +
        `bh.${prefix}_close IS NOT NULL AND ` +
        `(now() AT TIME ZONE '${TZ}')::time BETWEEN bh.${prefix}_open AND bh.${prefix}_close)`,
    ).join(' ');

    return {
      sql: `(bh.vendor_id IS NOT NULL AND (
        SELECT CASE ${cases} ELSE false END
        FROM (SELECT EXTRACT(dow FROM now() AT TIME ZONE '${TZ}')::int AS dow) dow_val
      ))`,
      params: [],
    };
  }

  // mode === 'window'
  const { dayOfWeek, startMin, endMin } = hours;
  const prefix = DOW_PREFIX[dayOfWeek];

  const startTime = formatMinutesAsTime(startMin);
  const endTime = formatMinutesAsTime(endMin);

  let timePredicate: string;
  if (startMin <= endMin) {
    // Normal window (e.g. 09:00–18:00)
    timePredicate = `bh.${prefix}_open <= '${startTime}' AND bh.${prefix}_close >= '${endTime}'`;
  } else {
    // Midnight-spanning (e.g. 20:00–02:00)
    timePredicate = `(bh.${prefix}_open <= '${startTime}' OR bh.${prefix}_close >= '${endTime}')`;
  }

  return {
    sql: `(bh.vendor_id IS NOT NULL AND NOT bh.${prefix}_closed AND bh.${prefix}_open IS NOT NULL AND bh.${prefix}_close IS NOT NULL AND ${timePredicate})`,
    params: [dayOfWeek],
  };
}

// ---------------------------------------------------------------------------
// ---------------------------------------------------------------------------
// queryDealsFeed
// ---------------------------------------------------------------------------

export interface FeedResult {
  deals: DealCardDeal[];
  nextCursor: string | null;
  total: number;
  totalPages?: number; // defined only in page-mode; undefined in cursor-mode
  center: { lat: number; lng: number } | null;
}

type FeedRow = {
  id: string;
  title: string;
  max_price: string;
  min_price: string;
  max_discount_percent: number;
  window_end: string | null;
  deal_type: string;
  stock_remaining: number | null;
  created_at: string;
  vendor_id: string;
  vendor_name: string;
  vendor_avatar_url: string | null;
  city: string;
  image_src: string | null;
  qty_tier_top: { minQty: number; discountPercent: number } | string | null;
  sku_count: number | string | null;
  axes_count: number | string | null;
  default_sku_id: string | null;
  distance_km: number | null;
  lat: string | null;
  lng: string | null;
  he_slug: string | null;
};

function mapFeedRowToDealCard(r: FeedRow): DealCardDeal {
  return {
    id: String(r.id),
    title: String(r.title ?? ''),
    vendorId: r.vendor_id ? String(r.vendor_id) : undefined,
    vendorName: String(r.vendor_name ?? ''),
    vendorAvatarUrl: r.vendor_avatar_url ?? undefined,
    city: String(r.city ?? ''),
    originalPrice: parseFloat(String(r.max_price ?? '0')),
    discountedPrice: parseFloat(String(r.min_price ?? '0')),
    discountPercent: Number(r.max_discount_percent ?? 0),
    imageSrc: r.image_src ?? '',
    imageAlt: String(r.title ?? ''),
    windowEnd: r.window_end
      ? new Date(String(r.window_end)).toISOString()
      : new Date(Date.now() + 24 * 3600 * 1000).toISOString(),
    dealType: (r.deal_type ?? 'COUPON') as DealType,
    stockRemaining: r.stock_remaining != null ? Number(r.stock_remaining) : undefined,
    stockTotal: r.stock_remaining != null ? Number(r.stock_remaining) : undefined,
    lat: r.lat != null ? parseFloat(String(r.lat)) : undefined,
    lng: r.lng != null ? parseFloat(String(r.lng)) : undefined,
    minPrice: r.min_price != null ? String(r.min_price) : null,
    maxPrice: r.max_price != null ? String(r.max_price) : null,
    maxDiscountPercent: r.max_discount_percent != null ? Number(r.max_discount_percent) : null,
    axesCount: Number(r.axes_count ?? 0),
    defaultSkuId: r.default_sku_id ? String(r.default_sku_id) : null,
    qtyTierTop:
      r.qty_tier_top != null
        ? ((typeof r.qty_tier_top === 'string' ? JSON.parse(r.qty_tier_top) : r.qty_tier_top) as {
            minQty: number;
            discountPercent: number;
          })
        : null,
    skuCount: Number(r.sku_count ?? 1),
    heSlug: r.he_slug ? String(r.he_slug) : undefined,
  };
}

/**
 * Paginated deal feed with optional geo + hours + type + category + tag filters.
 *
 * Uses Drizzle sql`` template for parameterized queries (safe from injection).
 * Bounding-box pre-filter before haversine for index efficiency.
 * Cursor = `sortKey:dealId` (lexicographic).
 */
export async function queryDealsFeed(
  db: DrizzleClient,
  filter: FeedFilter,
  locale: 'he' | 'en' = 'he',
): Promise<FeedResult> {
  const limit = filter.limit;
  const hasRadius = !!filter.radius;
  const sortByDistance = hasRadius || filter.preset === 'near-you';

  // Decode cursor
  let cursorSortKey: string | null = null;
  let cursorDealId: string | null = null;
  if (filter.cursor) {
    const sep = filter.cursor.lastIndexOf(':');
    if (sep > 0) {
      cursorSortKey = filter.cursor.slice(0, sep);
      cursorDealId = filter.cursor.slice(sep + 1);
    }
  }

  // --- Build SQL using Drizzle sql template tag with inline interpolation ---
  // All user-supplied values go through sql`` interpolation (parameterized).

  const haversineExpr = hasRadius
    ? sql`6371.0 * acos(LEAST(1.0, GREATEST(-1.0,
        cos(radians(${filter.radius!.lat})) * cos(radians(va.lat::float)) *
        cos(radians(va.lng::float) - radians(${filter.radius!.lng})) +
        sin(radians(${filter.radius!.lat})) * sin(radians(va.lat::float))
      )))`
    : sql`NULL::float`;

  // When filtering by city, push the city_code filter into the LATERAL so it
  // picks the vendor's address for that city (not an arbitrary public address).
  // Without this, LIMIT 1 can return a non-matching city → outer WHERE drops the deal.
  const cityCodeClause = filter.cityCode ? sql`AND va2.city_code = ${filter.cityCode}` : sql``;

  // WHERE conditions
  const conditions = [
    sql`d.deal_state = 'ACTIVE'`,
    sql`(d.window_end IS NULL OR d.window_end > now())`,
    sql`v.account_state IN ('ACTIVE', 'VETERAN')`,
    sql`va.is_public = true`,
    notSoldOutSqlD(),
  ];

  // Bounding-box pre-filter
  if (hasRadius) {
    const { lat, lng, km } = filter.radius!;
    const degDelta = km / 111.0;
    conditions.push(
      sql`va.lat::float BETWEEN ${lat - degDelta} AND ${lat + degDelta}`,
      sql`va.lng::float BETWEEN ${lng - degDelta} AND ${lng + degDelta}`,
    );
  }

  if (filter.cityCode) {
    conditions.push(sql`va.city_code = ${filter.cityCode}`);
  }

  if (filter.dealType) {
    conditions.push(sql`d.deal_type = ${filter.dealType}`);
  }

  if (filter.categoryId) {
    conditions.push(sql`d.category_id = ${filter.categoryId}::uuid`);
  }

  if (filter.tagIds && filter.tagIds.length > 0) {
    conditions.push(
      sql`EXISTS (
        SELECT 1 FROM deal_tag_assignments dta
        WHERE dta.deal_id = d.id AND dta.tag_id = ANY(${filter.tagIds}::uuid[])
      )`,
    );
  }

  if (filter.minPrice !== undefined) {
    conditions.push(sql`d.min_price >= ${String(filter.minPrice)}`);
  }

  if (filter.maxPrice !== undefined) {
    conditions.push(sql`d.min_price <= ${String(filter.maxPrice)}`);
  }

  // Hours predicate
  const hp = buildHoursPredicate(filter.hours);
  if (hp) {
    conditions.push(sql.raw(hp.sql));
  }

  // Cursor condition — only applied in cursor mode, not offset/page mode
  if (filter.page === undefined && cursorSortKey && cursorDealId) {
    if (sortByDistance && hasRadius) {
      const dist = parseFloat(cursorSortKey);
      conditions.push(
        sql`(${haversineExpr} > ${dist} OR (${haversineExpr} = ${dist} AND d.id > ${cursorDealId}))`,
      );
    } else {
      conditions.push(
        sql`(d.created_at < ${cursorSortKey}::timestamptz OR (d.created_at = ${cursorSortKey}::timestamptz AND d.id < ${cursorDealId}))`,
      );
    }
  }

  const whereClause =
    conditions.length > 0
      ? sql`WHERE ${conditions.reduce((acc, c, i) => (i === 0 ? c : sql`${acc} AND ${c}`))}`
      : sql``;

  const orderClause =
    sortByDistance && hasRadius
      ? sql`ORDER BY distance_km ASC, id ASC`
      : sql`ORDER BY created_at DESC, id DESC`;

  const center = hasRadius ? { lat: filter.radius!.lat, lng: filter.radius!.lng } : null;

  // ── Offset mode (page-based) ──────────────────────────────────────────────
  if (filter.page !== undefined) {
    const pageNum = filter.page;
    const offset = (pageNum - 1) * limit;

    const whereClause =
      conditions.length > 0
        ? sql`WHERE ${conditions.reduce((acc, c, i) => (i === 0 ? c : sql`${acc} AND ${c}`))}`
        : sql``;

    // Lean count — no correlated subqueries for image/qty/sku
    const leanInnerSql = sql`
      SELECT d.id${hasRadius ? sql`, ${haversineExpr} AS distance_km` : sql``}
      FROM deals d
      JOIN vendors v ON v.id = d.vendor_id
      JOIN LATERAL (
        SELECT va2.city, va2.city_code, va2.lat, va2.lng, va2.is_public
        FROM vendor_addresses va2
        WHERE va2.vendor_id = d.vendor_id AND va2.is_public = true ${cityCodeClause}
        LIMIT 1
      ) va ON true
      LEFT JOIN business_hours bh ON bh.vendor_id = v.id
      LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = ${locale} AND dt.status = 'OK'
      ${whereClause}
    `;

    const countSql = hasRadius
      ? sql`SELECT COUNT(*)::int AS cnt FROM (${leanInnerSql}) c WHERE distance_km <= ${filter.radius!.km}`
      : sql`SELECT COUNT(*)::int AS cnt FROM (${leanInnerSql}) c`;

    // Full data query with correlated subqueries
    const innerSql = sql`
      SELECT
        d.id,
        COALESCE(dt.title, d.title) AS title,
        d.max_price,
        d.min_price,
        d.max_discount_percent,
        d.window_end,
        d.deal_type,
        d.stock_remaining,
        d.created_at,
        v.id AS vendor_id,
        v.display_name AS vendor_name,
        v.logo_url AS vendor_avatar_url,
        va.city,
        va.lat,
        va.lng,
        (SELECT url FROM deal_images di
         WHERE di.deal_id = d.id AND di.is_primary = true
         LIMIT 1) AS image_src,
        (SELECT json_build_object('minQty', t.min_qty, 'discountPercent', t.discount_percent)
           FROM sku_qty_tiers t
           JOIN deal_skus s ON s.id = t.deal_sku_id
           WHERE s.deal_id = d.id
           ORDER BY t.discount_percent DESC, t.min_qty ASC
           LIMIT 1) AS qty_tier_top,
        (SELECT COUNT(*)::int FROM deal_skus s2 WHERE s2.deal_id = d.id) AS sku_count,
        (SELECT COUNT(*)::int FROM deal_variant_axes dva
         WHERE dva.deal_id = d.id AND dva.is_active = true) AS axes_count,
        (SELECT s.id FROM deal_skus s
         WHERE s.deal_id = d.id AND s.option_ids_hash = 'default'
         LIMIT 1) AS default_sku_id,
        ${haversineExpr} AS distance_km,
        dt_he.slug AS he_slug
      FROM deals d
      JOIN vendors v ON v.id = d.vendor_id
      JOIN LATERAL (
        SELECT va2.city, va2.city_code, va2.lat, va2.lng, va2.is_public
        FROM vendor_addresses va2
        WHERE va2.vendor_id = d.vendor_id AND va2.is_public = true ${cityCodeClause}
        LIMIT 1
      ) va ON true
      LEFT JOIN business_hours bh ON bh.vendor_id = v.id
      LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = ${locale} AND dt.status = 'OK'
      LEFT JOIN deal_translations dt_he ON dt_he.deal_id = d.id AND dt_he.locale = 'he' AND dt_he.status = 'OK'
      ${whereClause}
    `;

    const dataSql = hasRadius
      ? sql`SELECT * FROM (${innerSql}) feed_inner WHERE distance_km <= ${filter.radius!.km} ${orderClause} LIMIT ${limit} OFFSET ${offset}`
      : sql`SELECT * FROM (${innerSql}) feed_inner ${orderClause} LIMIT ${limit} OFFSET ${offset}`;

    const [countResult, dataResult] = await Promise.all([
      db.execute(countSql).catch((err): null => {
        captureCaught(err, { scope: 'feed.queryDealsFeed.count', severity: 'warning' });
        return null;
      }),
      db.execute(dataSql),
    ]);
    let countVal: number;
    if (countResult === null) {
      countVal = limit;
    } else {
      const countRows = executeRows(countResult) as Array<{ cnt: number | string }>;
      countVal = Number(countRows[0]?.cnt ?? 0);
    }
    const rows = executeRows(dataResult) as FeedRow[];

    const deals: DealCardDeal[] = rows.map((r) => mapFeedRowToDealCard(r));

    const totalPages = Math.max(1, Math.ceil(countVal / limit));
    return { deals, nextCursor: null, total: countVal, totalPages, center };
  }
  // ── End offset mode ───────────────────────────────────────────────────────

  const fetchLimit = limit + 1;

  // Radius filter: wrap inner query as subquery, apply distance <= km in outer WHERE.
  // HAVING without GROUP BY only works for aggregates — haversine is a per-row scalar.
  const innerSql = sql`
    SELECT
      d.id,
      COALESCE(dt.title, d.title) AS title,
      d.max_price,
      d.min_price,
      d.max_discount_percent,
      d.window_end,
      d.deal_type,
      d.stock_remaining,
      d.created_at,
      v.id AS vendor_id,
      v.display_name AS vendor_name,
      v.logo_url AS vendor_avatar_url,
      va.city,
      va.lat,
      va.lng,
      (SELECT url FROM deal_images di
       WHERE di.deal_id = d.id AND di.is_primary = true
       LIMIT 1) AS image_src,
      (SELECT json_build_object('minQty', t.min_qty, 'discountPercent', t.discount_percent)
         FROM sku_qty_tiers t
         JOIN deal_skus s ON s.id = t.deal_sku_id
         WHERE s.deal_id = d.id
         ORDER BY t.discount_percent DESC, t.min_qty ASC
         LIMIT 1) AS qty_tier_top,
      (SELECT COUNT(*)::int FROM deal_skus s2 WHERE s2.deal_id = d.id) AS sku_count,
      (SELECT COUNT(*)::int FROM deal_variant_axes dva
       WHERE dva.deal_id = d.id AND dva.is_active = true) AS axes_count,
      (SELECT s.id FROM deal_skus s
       WHERE s.deal_id = d.id AND s.option_ids_hash = 'default'
       LIMIT 1) AS default_sku_id,
      ${haversineExpr} AS distance_km,
      dt_he.slug AS he_slug
    FROM deals d
    JOIN vendors v ON v.id = d.vendor_id
    JOIN LATERAL (
      SELECT va2.city, va2.city_code, va2.lat, va2.lng, va2.is_public
      FROM vendor_addresses va2
      WHERE va2.vendor_id = d.vendor_id AND va2.is_public = true ${cityCodeClause}
      LIMIT 1
    ) va ON true
    LEFT JOIN business_hours bh ON bh.vendor_id = v.id
    LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = ${locale} AND dt.status = 'OK'
    LEFT JOIN deal_translations dt_he ON dt_he.deal_id = d.id AND dt_he.locale = 'he' AND dt_he.status = 'OK'
    ${whereClause}
  `;

  const querySql = hasRadius
    ? sql`
        SELECT * FROM (${innerSql}) feed_inner
        WHERE distance_km <= ${filter.radius!.km}
        ${orderClause}
        LIMIT ${fetchLimit}
      `
    : sql`
        SELECT * FROM (${innerSql}) feed_inner
        ${orderClause}
        LIMIT ${fetchLimit}
      `;

  const result = await db.execute(querySql);
  const rows = executeRows(result) as FeedRow[];

  const hasMore = rows.length > limit;
  const pageRows = hasMore ? rows.slice(0, limit) : rows;

  const deals: DealCardDeal[] = pageRows.map((r) => mapFeedRowToDealCard(r));

  // Build next cursor
  let nextCursor: string | null = null;
  if (hasMore && pageRows.length > 0) {
    const last = pageRows[pageRows.length - 1] as FeedRow;
    if (sortByDistance && hasRadius) {
      const dist =
        last.distance_km != null ? String(Math.round(last.distance_km * 1000) / 1000) : '0';
      nextCursor = `${dist}:${last.id}`;
    } else {
      const ts = last.created_at ? new Date(String(last.created_at)).toISOString() : '';
      nextCursor = `${ts}:${last.id}`;
    }
  }

  return { deals, nextCursor, total: pageRows.length, center };
}

// ---------------------------------------------------------------------------
// queryFeedMarkers — lean query for map view (no LIMIT, no correlated subqueries)
// ---------------------------------------------------------------------------

export interface FeedMarker {
  id: string;
  lat: number;
  lng: number;
  title: string;
  vendorId: string; // groups deals at the same vendor location
  vendorName: string;
  address: string | null; // street + number from vendor_addresses, fallback to city
  discountedPrice: number;
  discountPercent: number | null;
  originalPrice: number;
  city: string | null;
  /** Slug for the requested locale's deal URL. */
  slug?: string;
  /** Hebrew slug for the canonical Hebrew deal URL — present when a he translation exists. */
  heSlug?: string;
}

type MarkerRow = {
  id: string;
  title: string;
  vendor_id: string;
  vendor_name: string;
  lat: string | null;
  lng: string | null;
  street_name: string | null;
  house_number: string | null;
  discounted_price: string;
  discount_percent: number | string | null;
  original_price: string;
  city: string | null;
  distance_km?: number | null;
  slug: string | null;
  he_slug: string | null;
};

/**
 * Returns all deals matching the filter (no LIMIT) as lean map pins.
 * Safe: radius ≤ 50 km is enforced by feedFilterSchema (km: max(50)).
 * No correlated subqueries — fetches only fields MapView actually renders.
 */
export async function queryFeedMarkers(
  db: DrizzleClient,
  filter: FeedFilter,
  locale: 'he' | 'en' = 'he',
): Promise<FeedMarker[]> {
  const hasRadius = !!filter.radius;
  const sortByDistance = hasRadius || filter.preset === 'near-you';

  const haversineExpr = hasRadius
    ? sql`6371.0 * acos(LEAST(1.0, GREATEST(-1.0,
        cos(radians(${filter.radius!.lat})) * cos(radians(va.lat::float)) *
        cos(radians(va.lng::float) - radians(${filter.radius!.lng})) +
        sin(radians(${filter.radius!.lat})) * sin(radians(va.lat::float))
      )))`
    : sql`NULL::float`;

  const cityCodeClause = filter.cityCode ? sql`AND va2.city_code = ${filter.cityCode}` : sql``;

  const conditions = [
    sql`d.deal_state = 'ACTIVE'`,
    sql`(d.window_end IS NULL OR d.window_end > now())`,
    sql`v.account_state IN ('ACTIVE', 'VETERAN')`,
    sql`va.is_public = true`,
    notSoldOutSqlD(),
  ];

  if (hasRadius) {
    const { lat, lng, km } = filter.radius!;
    const degDelta = km / 111.0;
    conditions.push(
      sql`va.lat::float BETWEEN ${lat - degDelta} AND ${lat + degDelta}`,
      sql`va.lng::float BETWEEN ${lng - degDelta} AND ${lng + degDelta}`,
    );
  }
  if (filter.cityCode) conditions.push(sql`va.city_code = ${filter.cityCode}`);
  if (filter.dealType) conditions.push(sql`d.deal_type = ${filter.dealType}`);
  if (filter.categoryId) conditions.push(sql`d.category_id = ${filter.categoryId}::uuid`);
  if (filter.tagIds && filter.tagIds.length > 0) {
    conditions.push(sql`EXISTS (
      SELECT 1 FROM deal_tag_assignments dta
      WHERE dta.deal_id = d.id AND dta.tag_id = ANY(${filter.tagIds}::uuid[])
    )`);
  }
  if (filter.minPrice !== undefined) {
    conditions.push(sql`d.min_price >= ${String(filter.minPrice)}`);
  }
  if (filter.maxPrice !== undefined) {
    conditions.push(sql`d.min_price <= ${String(filter.maxPrice)}`);
  }
  const hp = buildHoursPredicate(filter.hours);
  if (hp) conditions.push(sql.raw(hp.sql));

  const whereClause =
    conditions.length > 0
      ? sql`WHERE ${conditions.reduce((acc, c, i) => (i === 0 ? c : sql`${acc} AND ${c}`))}`
      : sql``;

  // Inner SELECT doesn't include created_at; only distance_km and id are safe for ordering.
  const orderClause =
    sortByDistance && hasRadius ? sql`ORDER BY distance_km ASC, id ASC` : sql`ORDER BY id DESC`;

  const innerSql = sql`
    SELECT
      d.id,
      COALESCE(dt.title, d.title) AS title,
      v.id AS vendor_id,
      v.display_name AS vendor_name,
      va.lat,
      va.lng,
      va.street_name,
      va.house_number,
      d.min_price AS discounted_price,
      d.max_discount_percent AS discount_percent,
      d.max_price AS original_price,
      va.city,
      ${haversineExpr} AS distance_km,
      dt.slug AS slug,
      dt_he.slug AS he_slug
    FROM deals d
    JOIN vendors v ON v.id = d.vendor_id
    JOIN LATERAL (
      SELECT va2.city, va2.city_code, va2.lat, va2.lng, va2.is_public,
             va2.street_name, va2.house_number
      FROM vendor_addresses va2
      WHERE va2.vendor_id = d.vendor_id AND va2.is_public = true ${cityCodeClause}
      LIMIT 1
    ) va ON true
    LEFT JOIN business_hours bh ON bh.vendor_id = v.id
    LEFT JOIN deal_translations dt ON dt.deal_id = d.id AND dt.locale = ${locale} AND dt.status = 'OK'
    LEFT JOIN deal_translations dt_he ON dt_he.deal_id = d.id AND dt_he.locale = 'he' AND dt_he.status = 'OK'
    ${whereClause}
  `;

  const querySql = hasRadius
    ? sql`SELECT * FROM (${innerSql}) markers_inner WHERE distance_km <= ${filter.radius!.km} ${orderClause}`
    : sql`SELECT * FROM (${innerSql}) markers_inner ${orderClause}`;

  const result = await db.execute(querySql);
  const rows = executeRows(result) as MarkerRow[];

  return rows
    .filter(
      (r) =>
        r.lat != null &&
        r.lng != null &&
        !isNaN(parseFloat(String(r.lat))) &&
        !isNaN(parseFloat(String(r.lng))),
    )
    .map((r) => ({
      id: String(r.id),
      lat: parseFloat(String(r.lat)),
      lng: parseFloat(String(r.lng)),
      title: String(r.title ?? ''),
      vendorId: String(r.vendor_id ?? ''),
      vendorName: String(r.vendor_name ?? ''),
      address: (() => {
        const street = [r.street_name, r.house_number].filter(Boolean).join(' ');
        return street || r.city || null;
      })(),
      discountedPrice: parseFloat(String(r.discounted_price ?? '0')),
      discountPercent: r.discount_percent != null ? Number(r.discount_percent) : null,
      originalPrice: parseFloat(String(r.original_price ?? '0')),
      city: r.city != null ? String(r.city) : null,
      slug: r.slug ? String(r.slug) : undefined,
      heSlug: r.he_slug ? String(r.he_slug) : undefined,
    }));
}