HotTRDealsBackend/services/deal.service.js

// services/deal.service.js
const dealDB = require("../db/deal.db")
const userDB = require("../db/user.db")
const { findSellerFromLink } = require("./sellerLookup.service")
const { makeDetailWebp, makeThumbWebp } = require("../utils/processImage")
const { v4: uuidv4 } = require("uuid")
const { uploadImage } = require("./uploadImage.service")
const categoryDB = require("../db/category.db")
const { enqueueDealClassification } = require("../jobs/dealClassification.queue")
const { getCommentsForDeal } = require("./redis/commentCache.service")
const { getOrCacheDeal } = require("./redis/dealCache.service")
const { mapDealToRedisJson } = require("./redis/dealIndexing.service")
const { setDealInRedis } = require("./redis/dealCache.service")
const { queueDealCreate } = require("./redis/dbSync.service")
const { generateDealId } = require("./redis/dealId.service")
const { getSellerById } = require("./redis/sellerCache.service")
const { getDealVoteFromRedis } = require("./redis/dealVote.service")
const { getHotDealIds, getHotRangeDealIds, getDealsByIdsFromRedis } = require("./redis/hotDealList.service")
const { getTrendingDealIds } = require("./redis/trendingDealList.service")
const { getNewDealIds } = require("./redis/newDealList.service")
const { setUserPublicInRedis } = require("./redis/userPublicCache.service")
const { getUserSavedIdsFromRedis, getUserSavedMapForDeals } = require("./redis/userCache.service")
const { getMyVotesForDeals } = require("./redis/dealVote.service")
const { getBarcodeForUrl } = require("./redis/linkPreviewCache.service")
const {
  buildDealSearchQuery,
  searchDeals,
  buildTitlePrefixQuery,
  buildTextSearchQuery,
  buildPrefixTextQuery,
  buildFuzzyTextQuery,
} = require("./redis/dealSearch.service")

const DEFAULT_LIMIT = 20
const MAX_LIMIT = 50
const MAX_SKIP = 5000
const MS_PER_DAY = 24 * 60 * 60 * 1000

  const DEAL_CARD_SELECT = {
    id: true,
    title: true,
    description: true,
    price: true,
    originalPrice: true,
    shippingPrice: true,
    couponCode: true,
    location: true,
    discountType: true,
    discountValue: true,
    barcodeId: true,
    score: true,
    commentCount: true,
    url: true,
    status: true,
  saletype: true,
  affiliateType: true,
  createdAt: true,
  updatedAt: true,
  customSeller: true,
  user: { select: { id: true, username: true, avatarUrl: true } },
  seller: { select: { id: true, name: true, url: true } },
  images: {
    orderBy: { order: "asc" },
    take: 1,
    select: { imageUrl: true },
  },
}

  const DEAL_DETAIL_SELECT = {
    id: true,
    title: true,
    description: true,
    url: true,
    price: true,
    originalPrice: true,
    shippingPrice: true,
    couponCode: true,
    location: true,
    discountType: true,
    discountValue: true,
    barcodeId: true,
    score: true,
    commentCount: true,
  status: true,
  saletype: true,
  affiliateType: true,
  createdAt: true,
  updatedAt: true,
  categoryId: true,
  sellerId: true,
  customSeller: true,
  user: { select: { id: true, username: true, avatarUrl: true } },
  seller: { select: { id: true, name: true, url: true } },
  images: { orderBy: { order: "asc" }, select: { id: true, imageUrl: true, order: true } },
  notices: {
    where: { isActive: true },
    orderBy: { createdAt: "desc" },
    take: 1,
    select: {
      id: true,
      dealId: true,
      title: true,
      body: true,
      severity: true,
      isActive: true,
      createdBy: true,
      createdAt: true,
      updatedAt: true,
    },
  },
  _count: { select: { comments: true } },
}

const SIMILAR_DEAL_SELECT = {
  id: true,
  title: true,
  price: true,
  score: true,
  createdAt: true,
  categoryId: true,
  sellerId: true,
  customSeller: true,
  seller: { select: { name: true } },
  images: { take: 1, orderBy: { order: "asc" }, select: { imageUrl: true } },
}

function formatDateAsString(value) {
  return value instanceof Date ? value.toISOString() : value ?? null
}

function clampPagination({ page, limit }) {
  const rawPage = Number(page)
  const rawLimit = Number(limit)
  const normalizedPage = Number.isFinite(rawPage) ? Math.max(1, Math.floor(rawPage)) : 1
  let normalizedLimit = Number.isFinite(rawLimit) ? Math.max(1, Math.floor(rawLimit)) : DEFAULT_LIMIT
  normalizedLimit = Math.min(MAX_LIMIT, normalizedLimit)
  const skip = (normalizedPage - 1) * normalizedLimit
  if (skip > MAX_SKIP) {
    const err = new Error("PAGE_TOO_DEEP")
    err.statusCode = 400
    throw err
  }
  return { page: normalizedPage, limit: normalizedLimit, skip }
}

function buildSearchClause(q) {
  if (q === undefined || q === null) return null
  const normalized = String(q).trim()
  if (!normalized) return null
  return {
    OR: [
      { title: { contains: normalized, mode: "insensitive" } },
      { description: { contains: normalized, mode: "insensitive" } },
    ],
  }
}

const DEAL_STATUSES = new Set(["PENDING", "ACTIVE", "EXPIRED", "REJECTED"])
const SALE_TYPES = new Set(["ONLINE", "OFFLINE", "CODE"])
const AFFILIATE_TYPES = new Set(["AFFILIATE", "NON_AFFILIATE", "USER_AFFILIATE"])

function normalizeListInput(value) {
  if (Array.isArray(value)) {
    return value.flatMap((item) => String(item).split(","))
  }
  if (value === undefined || value === null) return []
  return String(value).split(",")
}

function parseInteger(value) {
  if (value === undefined || value === null || value === "") return null
  const num = Number(value)
  return Number.isInteger(num) ? num : null
}

function parseNumber(value) {
  if (value === undefined || value === null || value === "") return null
  const num = Number(value)
  return Number.isFinite(num) ? num : null
}

function parseBoolean(value) {
  if (typeof value === "boolean") return value
  if (typeof value === "number") return value === 1 ? true : value === 0 ? false : null
  if (typeof value === "string") {
    const normalized = value.trim().toLowerCase()
    if (["true", "1", "yes"].includes(normalized)) return true
    if (["false", "0", "no"].includes(normalized)) return false
  }
  return null
}

function parseDate(value) {
  if (value === undefined || value === null || value === "") return null
  const date = new Date(value)
  return Number.isNaN(date.getTime()) ? null : date
}

function parseIdList(value) {
  const items = normalizeListInput(value)
  const ids = items
    .map((item) => parseInteger(String(item).trim()))
    .filter((item) => item && item > 0)
  return ids.length ? Array.from(new Set(ids)) : null
}

function parseEnumList(value, allowedSet) {
  const items = normalizeListInput(value)
  const filtered = items
    .map((item) => String(item).trim().toUpperCase())
    .filter((item) => allowedSet.has(item))
  return filtered.length ? Array.from(new Set(filtered)) : null
}

function parseSort(value) {
  const normalized = String(value || "").trim().toLowerCase()
  if (["score", "price", "createdat", "createdatts"].includes(normalized)) return normalized
  return "createdAtTs"
}

function parseSortDir(value, sortBy) {
  const normalized = String(value || "").trim().toLowerCase()
  if (normalized === "asc") return "ASC"
  if (normalized === "desc") return "DESC"
  if (String(sortBy).toLowerCase() === "price") return "ASC"
  return "DESC"
}

function buildFilterWhere(rawFilters = {}, { allowStatus = false } = {}) {
  if (!rawFilters || typeof rawFilters !== "object") return null

  const clauses = []
  const categoryIds = parseIdList(rawFilters.categoryId ?? rawFilters.categoryIds)
  if (categoryIds?.length) {
    clauses.push({ categoryId: { in: categoryIds } })
  }

  const sellerIds = parseIdList(rawFilters.sellerId ?? rawFilters.sellerIds)
  if (sellerIds?.length) {
    clauses.push({ sellerId: { in: sellerIds } })
  }

  const saleTypes = parseEnumList(rawFilters.saleType, SALE_TYPES)
  if (saleTypes?.length) {
    const hasCode = saleTypes.includes("CODE")
    const others = saleTypes.filter((t) => t !== "CODE")
    if (hasCode) {
      const orClauses = []
      orClauses.push({
        saletype: "CODE",
        couponCode: { not: null },
      })
      if (others.length) {
        orClauses.push({ saletype: { in: others } })
      }
      clauses.push(orClauses.length === 1 ? orClauses[0] : { OR: orClauses })
    } else {
      clauses.push({ saletype: { in: saleTypes } })
    }
  }

  const affiliateTypes = parseEnumList(rawFilters.affiliateType, AFFILIATE_TYPES)
  if (affiliateTypes?.length) {
    clauses.push({ affiliateType: { in: affiliateTypes } })
  }

  if (allowStatus) {
    const statuses = parseEnumList(rawFilters.status, DEAL_STATUSES)
    if (statuses?.length) {
      clauses.push({ status: { in: statuses } })
    }
  }

  const minPrice = parseNumber(rawFilters.minPrice ?? rawFilters.priceMin)
  const maxPrice = parseNumber(rawFilters.maxPrice ?? rawFilters.priceMax)
  if (minPrice !== null || maxPrice !== null) {
    const price = {}
    if (minPrice !== null) price.gte = minPrice
    if (maxPrice !== null) price.lte = maxPrice
    clauses.push({ price })
  }

  const minScore = parseNumber(rawFilters.minScore)
  const maxScore = parseNumber(rawFilters.maxScore)
  if (minScore !== null || maxScore !== null) {
    const score = {}
    if (minScore !== null) score.gte = minScore
    if (maxScore !== null) score.lte = maxScore
    clauses.push({ score })
  }

  const createdAfter = parseDate(rawFilters.createdAfter ?? rawFilters.from)
  const createdBefore = parseDate(rawFilters.createdBefore ?? rawFilters.to)
  if (createdAfter || createdBefore) {
    const createdAt = {}
    if (createdAfter) createdAt.gte = createdAfter
    if (createdBefore) createdAt.lte = createdBefore
    clauses.push({ createdAt })
  }

  const hasImage = parseBoolean(rawFilters.hasImage)
  if (hasImage === true) {
    clauses.push({ images: { some: {} } })
  } else if (hasImage === false) {
    clauses.push({ images: { none: {} } })
  }

  if (!clauses.length) return null
  return clauses.length === 1 ? clauses[0] : { AND: clauses }
}

function buildRedisSearchFilters(rawFilters = {}, baseWhere = null) {
  const filters = rawFilters || {}
  const statuses = parseEnumList(filters.status, DEAL_STATUSES)

  const categoryIds = parseIdList(filters.categoryId ?? filters.categoryIds)
  const sellerIds = parseIdList(filters.sellerId ?? filters.sellerIds)
  const saleTypes = parseEnumList(filters.saleType, SALE_TYPES)

  const minPrice = parseNumber(filters.minPrice ?? filters.priceMin)
  const maxPrice = parseNumber(filters.maxPrice ?? filters.priceMax)

  const minScore = parseNumber(filters.minScore)
  const maxScore = parseNumber(filters.maxScore)

  const merged = {
    statuses,
    categoryIds,
    sellerIds,
    saleTypes,
    minPrice,
    maxPrice,
    minScore,
    maxScore,
  }

  if (baseWhere) {
    if (baseWhere.status) {
      merged.statuses = parseEnumList(baseWhere.status, DEAL_STATUSES) || merged.statuses
    }
    if (baseWhere.categoryId?.in) {
      merged.categoryIds = Array.from(new Set([...(merged.categoryIds || []), ...baseWhere.categoryId.in]))
    } else if (Number.isInteger(baseWhere.categoryId)) {
      merged.categoryIds = Array.from(new Set([...(merged.categoryIds || []), baseWhere.categoryId]))
    }
    if (baseWhere.sellerId?.in) {
      merged.sellerIds = Array.from(new Set([...(merged.sellerIds || []), ...baseWhere.sellerId.in]))
    } else if (Number.isInteger(baseWhere.sellerId)) {
      merged.sellerIds = Array.from(new Set([...(merged.sellerIds || []), baseWhere.sellerId]))
    }
  }

  return merged
}

const REDIS_SEARCH_LIMIT = 20

async function getDealsFromRedisSearch({
  q,
  page,
  viewer,
  filters,
  baseWhere,
} = {}) {
  const pagination = clampPagination({ page, limit: REDIS_SEARCH_LIMIT })
  const filterValues = buildRedisSearchFilters(filters, baseWhere)
  const filterQuery = buildDealSearchQuery(filterValues)
  const primaryTextQuery = buildPrefixTextQuery(q) ?? buildTextSearchQuery(q)
  const primaryQuery = [filterQuery, primaryTextQuery].filter(Boolean).join(" ") || "*"
  const sortBy = parseSort(filters?.sortBy)
  const sortDir = parseSortDir(filters?.sortDir, sortBy)

  let searchResult = await searchDeals({
    query: primaryQuery,
    page: pagination.page,
    limit: REDIS_SEARCH_LIMIT,
    sortBy,
    sortDir,
    includeMinMax: pagination.page === 1,
  })

  if (!searchResult) {
    return getDealsFromDbPreset({
      preset: "RAW",
      q,
      page,
      limit,
      viewer,
      filters,
      baseWhere,
      scope: "USER",
    })
  }

  if (searchResult.total === 0 && q) {
    const fuzzyTextQuery = buildFuzzyTextQuery(q)
    if (fuzzyTextQuery) {
      const fuzzyQuery = [filterQuery, fuzzyTextQuery].filter(Boolean).join(" ") || "*"
      searchResult = await searchDeals({
        query: fuzzyQuery,
        page: pagination.page,
        limit: REDIS_SEARCH_LIMIT,
        sortBy,
        sortDir,
        includeMinMax: pagination.page === 1,
      })
      if (!searchResult) {
        return getDealsFromDbPreset({
          preset: "RAW",
          q,
          page,
          limit,
          viewer,
          filters,
          baseWhere,
          scope: "USER",
        })
      }
    }
  }

  if (!searchResult.dealIds.length) {
    return {
      page: pagination.page,
      total: 0,
      totalPages: 0,
      results: [],
    }
  }

  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(searchResult.dealIds, viewerId)
  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return {
    page: searchResult.page,
    total: searchResult.total,
    totalPages: searchResult.totalPages,
    results: enriched,
    minPrice: searchResult.minPrice,
    maxPrice: searchResult.maxPrice,
  }
}
function buildPresetCriteria(preset, { viewer, targetUserId } = {}) {
  const now = new Date()
  switch (preset) {
    case "NEW":
      return { where: { status: "ACTIVE" }, orderBy: [{ createdAt: "desc" }] }
    case "HOT": {
      const cutoff = new Date(now.getTime() - 3 * MS_PER_DAY)
      return {
        where: { status: "ACTIVE", createdAt: { gte: cutoff } },
        orderBy: [{ score: "desc" }, { createdAt: "desc" }],
      }
    }
    case "TRENDING": {
      const cutoff = new Date(now.getTime() - 2 * MS_PER_DAY)
      return {
        where: { status: "ACTIVE", createdAt: { gte: cutoff } },
        orderBy: [{ score: "desc" }, { createdAt: "desc" }],
      }
    }
    case "MY": {
      if (!viewer?.userId) {
        const err = new Error("AUTH_REQUIRED")
        err.statusCode = 401
        throw err
      }
      return { where: { userId: viewer.userId }, orderBy: [{ createdAt: "desc" }] }
    }
    case "USER_PUBLIC": {
      if (!targetUserId) {
        const err = new Error("TARGET_USER_REQUIRED")
        err.statusCode = 400
        throw err
      }
      return { where: { userId: targetUserId, status: "ACTIVE" }, orderBy: [{ createdAt: "desc" }] }
    }
    case "HOT_DAY": {
      const cutoff = new Date(now.getTime() - 1 * MS_PER_DAY)
      return {
        where: { status: "ACTIVE", createdAt: { gte: cutoff } },
        orderBy: [{ score: "desc" }, { createdAt: "desc" }],
      }
    }
    case "HOT_WEEK": {
      const cutoff = new Date(now.getTime() - 7 * MS_PER_DAY)
      return {
        where: { status: "ACTIVE", createdAt: { gte: cutoff } },
        orderBy: [{ score: "desc" }, { createdAt: "desc" }],
      }
    }
    case "HOT_MONTH": {
      const cutoff = new Date(now.getTime() - 30 * MS_PER_DAY)
      return {
        where: { status: "ACTIVE", createdAt: { gte: cutoff } },
        orderBy: [{ score: "desc" }, { createdAt: "desc" }],
      }
    }
    case "RAW": {
      return { where: {}, orderBy: [{ createdAt: "desc" }] }
    }
    default: {
      const err = new Error("INVALID_PRESET")
      err.statusCode = 400
      throw err
    }
  }
}

// --------------------
// Similar deals helpers (tagsiz, lightweight)
// --------------------
function clamp(n, min, max) {
  return Math.max(min, Math.min(max, n))
}

function tokenizeTitle(title = "") {
  return String(title)
    .toLowerCase()
    .replace(/[^a-z0-9çğıöşü\s]/gi, " ")
    .split(/\s+/)
    .filter(Boolean)
    .filter((w) => w.length >= 3)
}

function titleOverlapScore(aTitle, bTitle) {
  const a = tokenizeTitle(aTitle)
  const b = tokenizeTitle(bTitle)
  if (!a.length || !b.length) return 0

  const aset = new Set(a)
  const bset = new Set(b)
  let hit = 0
  for (const w of bset) if (aset.has(w)) hit++

  const denom = Math.min(aset.size, bset.size) || 1
  return hit / denom // 0..1
}

/**
 * SimilarDeals: DealCard değil, minimal summary döndürür.
 * Beklenen candidate shape:
 * - id, title, price, score, createdAt, categoryId, sellerId, customSeller
 * - seller?: { name }
 * - images?: [{ imageUrl }]
 */
async function buildSimilarDealsForDetail(targetDeal, { limit = 12 } = {}) {
  const take = clamp(Number(limit) || 12, 1, 12)

  // Bu 2 DB fonksiyonu: ACTIVE filter + images(take:1) + seller(name) getirmeli
  const [byCategory, bySeller] = await Promise.all([
    dealDB.findSimilarCandidates(
      {
        id: { not: Number(targetDeal.id) },
        status: "ACTIVE",
        categoryId: Number(targetDeal.categoryId),
      },
      { take: 80, select: SIMILAR_DEAL_SELECT }
    ),
    targetDeal.sellerId
      ? dealDB.findSimilarCandidates(
          {
            id: { not: Number(targetDeal.id) },
            status: "ACTIVE",
            sellerId: Number(targetDeal.sellerId),
          },
          { take: 30, select: SIMILAR_DEAL_SELECT }
        )
      : Promise.resolve([]),
  ])

  const dedup = new Map()
  for (const d of [...byCategory, ...bySeller]) dedup.set(d.id, d)
  const candidates = Array.from(dedup.values())

  const now = Date.now()

  const scored = candidates.map((d) => {
    const sameCategory = d.categoryId === targetDeal.categoryId
    const sameSeller = Boolean(targetDeal.sellerId && d.sellerId === targetDeal.sellerId)

    const titleSim = titleOverlapScore(targetDeal.title, d.title) // 0..1
    const titlePoints = Math.round(titleSim * 25)

    const scoreVal = Number.isFinite(d.score) ? d.score : 0
    const scorePoints = clamp(Math.round(scoreVal / 10), 0, 25)

    const ageDays = Math.floor((now - new Date(d.createdAt).getTime()) / (1000 * 60 * 60 * 24))
    const recencyPoints = ageDays <= 3 ? 10 : ageDays <= 10 ? 6 : ageDays <= 30 ? 3 : 0

    const rank =
      (sameCategory ? 60 : 0) +
      (sameSeller ? 25 : 0) +
      titlePoints +
      scorePoints +
      recencyPoints

    return { d, rank }
  })

  scored.sort((a, b) => b.rank - a.rank)

  return scored.slice(0, take).map(({ d }) => ({
    id: d.id,
    title: d.title,
    price: d.price ?? null,
    score: Number.isFinite(d.score) ? d.score : 0,
    imageUrl: d.images?.[0]?.imageUrl || "",
    sellerName: d.seller?.name || d.customSeller || "Bilinmiyor",
    createdAt: formatDateAsString(d.createdAt),
    // url istersen:
    // url: d.url ?? null,
  }))
}

async function getHotDealsFromRedis({ page, limit, viewer, hotListId } = {}) {
  const pagination = clampPagination({ page, limit })
  const { hotListId: listId, dealIds } = await getHotDealIds({ hotListId })

  if (!dealIds.length) {
    const fallback = await getDealsFromDbPreset({
      preset: "HOT",
      page,
      limit,
      viewer,
    })
    return { ...fallback, hotListId: listId }
  }

  const pageIds = dealIds.slice(pagination.skip, pagination.skip + pagination.limit)
  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(pageIds, viewerId)

  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return {
    page: pagination.page,
    total: dealIds.length,
    totalPages: Math.ceil(dealIds.length / pagination.limit),
    results: enriched,
    hotListId: listId,
  }
}

async function getTrendingDealsFromRedis({ page, limit, viewer, trendingListId } = {}) {
  const pagination = clampPagination({ page, limit })
  const { trendingListId: listId, dealIds } = await getTrendingDealIds({ trendingListId })

  if (!dealIds.length) {
    const fallback = await getDealsFromDbPreset({
      preset: "TRENDING",
      page,
      limit,
      viewer,
    })
    return { ...fallback, trendingListId: listId }
  }

  const pageIds = dealIds.slice(pagination.skip, pagination.skip + pagination.limit)
  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(pageIds, viewerId)

  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return {
    page: pagination.page,
    total: dealIds.length,
    totalPages: Math.ceil(dealIds.length / pagination.limit),
    results: enriched,
    trendingListId: listId,
  }
}

async function getHotRangeDealsFromRedis({ page, limit, viewer, range } = {}) {
  const pagination = clampPagination({ page, limit })
  const { listId, dealIds } = await getHotRangeDealIds({ range })

  if (!dealIds.length) {
    const preset =
      range === "day" ? "HOT_DAY" : range === "week" ? "HOT_WEEK" : range === "month" ? "HOT_MONTH" : "HOT"
    const fallback = await getDealsFromDbPreset({
      preset,
      page,
      limit,
      viewer,
    })
    return { ...fallback, hotListId: listId }
  }

  const pageIds = dealIds.slice(pagination.skip, pagination.skip + pagination.limit)
  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(pageIds, viewerId)
  if (!deals.length) {
    return getDealsFromDbPreset({
      preset: "NEW",
      page,
      limit: REDIS_SEARCH_LIMIT,
      viewer,
    })
  }
  if (!deals.length) {
    const preset =
      range === "day" ? "HOT_DAY" : range === "week" ? "HOT_WEEK" : range === "month" ? "HOT_MONTH" : "HOT"
    const fallback = await getDealsFromDbPreset({
      preset,
      page,
      limit,
      viewer,
    })
    return { ...fallback, hotListId: listId }
  }
  if (!deals.length) {
    const fallback = await getDealsFromDbPreset({
      preset: "TRENDING",
      page,
      limit,
      viewer,
    })
    return { ...fallback, trendingListId: listId }
  }
  if (!deals.length) {
    const fallback = await getDealsFromDbPreset({
      preset: "HOT",
      page,
      limit,
      viewer,
    })
    return { ...fallback, hotListId: listId }
  }

  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return {
    page: pagination.page,
    total: dealIds.length,
    totalPages: Math.ceil(dealIds.length / pagination.limit),
    results: enriched,
    hotListId: listId,
  }
}

async function getDealsFromDbPreset({
  preset,
  q,
  page,
  limit,
  viewer = null,
  targetUserId = null,
  filters = null,
  baseWhere = null,
  scope = "USER",
} = {}) {
  const pagination = clampPagination({ page, limit })
  const { where: presetWhere, orderBy: presetOrder } = buildPresetCriteria(preset, {
    viewer,
    targetUserId,
  })
  const searchClause = buildSearchClause(q)
  const allowStatus = preset === "MY" || scope === "MOD"
  const filterWhere = buildFilterWhere(filters, { allowStatus })

  const clauses = []
  if (presetWhere && Object.keys(presetWhere).length > 0) clauses.push(presetWhere)
  if (baseWhere && Object.keys(baseWhere).length > 0) clauses.push(baseWhere)
  if (searchClause) clauses.push(searchClause)
  if (filterWhere) clauses.push(filterWhere)

  const finalWhere = clauses.length === 0 ? {} : clauses.length === 1 ? clauses[0] : { AND: clauses }
  const orderBy = presetOrder ?? [{ createdAt: "desc" }]

  const [deals, total] = await Promise.all([
    dealDB.findDeals(finalWhere, {
      skip: pagination.skip,
      take: pagination.limit,
      orderBy,
      select: DEAL_CARD_SELECT,
    }),
    dealDB.countDeals(finalWhere),
  ])

  const dealIds = deals.map((d) => Number(d.id)).filter((id) => Number.isInteger(id) && id > 0)
  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const [voteMap, savedMap] = await Promise.all([
    viewerId ? getMyVotesForDeals(dealIds, viewerId) : Promise.resolve(new Map()),
    viewerId ? getUserSavedMapForDeals(viewerId, dealIds) : Promise.resolve(new Map()),
  ])

  const enriched = deals.map((deal) => {
    const id = Number(deal.id)
    return {
      ...deal,
      myVote: viewerId ? Number(voteMap.get(id) ?? 0) : 0,
      isSaved: viewerId ? savedMap.get(id) === true : false,
    }
  })

  return {
    page: pagination.page,
    total,
    totalPages: Math.ceil(total / pagination.limit),
    results: enriched,
  }
}

async function getBestWidgetDeals({ viewer = null, limit = 5 } = {}) {
  const take = Math.max(1, Math.min(Number(limit) || 5, 20))
  const viewerId = viewer?.userId ? Number(viewer.userId) : null

  const [dayList, weekList, monthList] = await Promise.all([
    getHotRangeDealIds({ range: "day" }),
    getHotRangeDealIds({ range: "week" }),
    getHotRangeDealIds({ range: "month" }),
  ])

  const pickTop = async (ids = []) => {
    const pageIds = ids.slice(0, take)
    const deals = await getDealsByIdsFromRedis(pageIds, viewerId)
    return deals.map((deal) => ({
      ...deal,
      id: Number(deal.id),
      score: Number.isFinite(deal.score) ? deal.score : 0,
      commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
      price: deal.price ?? null,
      originalPrice: deal.originalPrice ?? null,
      shippingPrice: deal.shippingPrice ?? null,
      discountValue: deal.discountValue ?? null,
    }))
  }

  const [hotDay, hotWeek, hotMonth] = await Promise.all([
    dayList?.dealIds?.length
      ? pickTop(dayList.dealIds)
      : (await getDealsFromDbPreset({ preset: "HOT_DAY", page: 1, limit: take, viewer })).results,
    weekList?.dealIds?.length
      ? pickTop(weekList.dealIds)
      : (await getDealsFromDbPreset({ preset: "HOT_WEEK", page: 1, limit: take, viewer })).results,
    monthList?.dealIds?.length
      ? pickTop(monthList.dealIds)
      : (await getDealsFromDbPreset({ preset: "HOT_MONTH", page: 1, limit: take, viewer })).results,
  ])

  return { hotDay, hotWeek, hotMonth }
}

async function getDealSuggestions({ q, limit = 8, viewer } = {}) {
  const term = String(q || "").trim()
  if (!term) return { results: [] }

  const query = buildTitlePrefixQuery(term)
  if (!query) return { results: [] }

  const normalizedLimit = Math.max(1, Math.min(Number(limit) || 8, 20))
  const searchResult = await searchDeals({
    query,
    page: 1,
    limit: normalizedLimit,
    sortBy: "score",
    sortDir: "DESC",
  })

  if (!searchResult) return { results: [] }

  if (!searchResult.dealIds.length) return { results: [] }

  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(searchResult.dealIds, viewerId)
  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return { results: enriched }
}

function hasSearchFilters(filters = {}) {
  if (!filters || typeof filters !== "object") return false
  const keys = [
    "status",
    "categoryId",
    "categoryIds",
    "sellerId",
    "sellerIds",
    "saleType",
    "minPrice",
    "maxPrice",
    "priceMin",
    "priceMax",
    "minScore",
    "maxScore",
    "sortBy",
    "sortDir",
  ]
  return keys.some((key) => filters[key] !== undefined && filters[key] !== null && String(filters[key]) !== "")
}

async function getNewDealsFromRedis({ page, viewer, newListId } = {}) {
  const pagination = clampPagination({ page, limit: REDIS_SEARCH_LIMIT })
  const { newListId: listId, dealIds } = await getNewDealIds({ newListId })

  if (!dealIds.length) {
    return getDealsFromDbPreset({
      preset: "NEW",
      page,
      limit: REDIS_SEARCH_LIMIT,
      viewer,
    })
  }

  const pageIds = dealIds.slice(pagination.skip, pagination.skip + pagination.limit)
  const viewerId = viewer?.userId ? Number(viewer.userId) : null
  const deals = await getDealsByIdsFromRedis(pageIds, viewerId)

  const enriched = deals.map((deal) => ({
    ...deal,
    id: Number(deal.id),
    score: Number.isFinite(deal.score) ? deal.score : 0,
    commentCount: Number.isFinite(deal.commentCount) ? deal.commentCount : 0,
    price: deal.price ?? null,
    originalPrice: deal.originalPrice ?? null,
    shippingPrice: deal.shippingPrice ?? null,
    discountValue: deal.discountValue ?? null,
  }))

  return {
    page: pagination.page,
    total: dealIds.length,
    totalPages: Math.ceil(dealIds.length / pagination.limit),
    results: enriched,
  }
}

async function getDeals({
  preset = "NEW",
  q,
  page,
  limit,
  viewer = null,
  targetUserId = null,
  filters = null,
  baseWhere = null,
  scope = "USER",
  hotListId = null,
  trendingListId = null,
  useRedisSearch = false,
}) {
  const pagination = clampPagination({ page, limit })
  if (preset === "HOT") {
    const listId = hotListId ?? filters?.hotListId ?? filters?.hotlistId ?? null
    return getHotDealsFromRedis({ page, limit, viewer, hotListId: listId })
  }
  if (preset === "HOT_DAY") {
    return getHotRangeDealsFromRedis({ page, limit, viewer, range: "day" })
  }
  if (preset === "HOT_WEEK") {
    return getHotRangeDealsFromRedis({ page, limit, viewer, range: "week" })
  }
  if (preset === "HOT_MONTH") {
    return getHotRangeDealsFromRedis({ page, limit, viewer, range: "month" })
  }
  if (preset === "TRENDING") {
    const listId = trendingListId ?? filters?.trendingListId ?? filters?.trendinglistId ?? null
    return getTrendingDealsFromRedis({ page, limit, viewer, trendingListId: listId })
  }

  if (preset === "NEW" && !q && !hasSearchFilters(filters) && !useRedisSearch) {
    const listId = filters?.newListId ?? filters?.newlistId ?? null
    return getNewDealsFromRedis({ page, viewer, newListId: listId })
  }

  if (useRedisSearch) {
    return getDealsFromRedisSearch({
      q,
      page,
      limit,
      viewer,
      filters,
      baseWhere,
    })
  }

  return getDealsFromDbPreset({
    preset,
    q,
    page,
    limit,
    viewer,
    targetUserId,
    filters,
    baseWhere,
    scope,
  })
}

async function getDealById(id, viewer = null) {
  const deal = await getOrCacheDeal(Number(id), { ttlSeconds: 15 * 60 })

  if (!deal) return null

  const dealUserId = Number(deal.userId ?? deal.user?.id)

  if (deal.status === "PENDING" || deal.status === "REJECTED") {
    const isOwner = viewer?.userId && dealUserId === Number(viewer.userId)
    const isMod = viewer?.role === "MOD" || viewer?.role === "ADMIN"
    if (!isOwner && !isMod) return null
  }

  const [breadcrumb, similarDeals, userStatsAgg, myVote, commentsResp, seller, savedCache] = await Promise.all([
    categoryDB.getCategoryBreadcrumb(deal.categoryId, { includeUndefined: false }),
    buildSimilarDealsForDetail(
      {
        id: deal.id,
        title: deal.title,
        categoryId: deal.categoryId,
        sellerId: deal.sellerId ?? null,
      },
      { limit: 12 }
    ),
    dealUserId
      ? dealDB.aggregateDeals({ userId: dealUserId, status: { in: ["ACTIVE", "EXPIRED"] } })
      : Promise.resolve(null),
    viewer?.userId ? getDealVoteFromRedis(deal.id, viewer.userId) : Promise.resolve(0),
    getCommentsForDeal({ dealId: deal.id, parentId: null, page: 1, limit: 10, sort: "NEW", viewerId: viewer?.userId ?? null }).catch(() => ({ results: [] })),
    deal.sellerId ? getSellerById(Number(deal.sellerId)) : Promise.resolve(null),
    viewer?.userId ? getUserSavedIdsFromRedis(viewer.userId) : Promise.resolve(null),
  ])

  const userStats = {
    totalLikes: userStatsAgg?._sum?.score ?? 0,
    totalDeals: userStatsAgg?._count?._all ?? 0,
  }

  return {
    ...deal,
    seller: deal.seller ?? seller ?? null,
    comments: commentsResp?.results || [],
    breadcrumb,
    similarDeals,
    userStats,
    myVote,
    isSaved: viewer?.userId
      ? Boolean(savedCache?.savedSet?.has(Number(deal.id)))
      : false,
  }
}

async function createDeal(dealCreateData, files = []) {
  const dealId = await generateDealId()
  const now = new Date()

  let sellerId = null
  if (dealCreateData.url) {
    const seller = await findSellerFromLink(dealCreateData.url)
    if (seller) {
      sellerId = seller.id
      dealCreateData.customSeller = null
    }
    const cachedBarcode = await getBarcodeForUrl(dealCreateData.url)
    if (cachedBarcode) {
      dealCreateData.barcodeId = cachedBarcode
    }
  }

  const userId = Number(dealCreateData?.user?.connect?.id)
  if (!Number.isInteger(userId) || userId <= 0) {
    const err = new Error("INVALID_USER")
    err.statusCode = 400
    throw err
  }

  const user = await userDB.findUser(
    { id: userId },
    {
      select: {
        id: true,
        username: true,
        avatarUrl: true,
        userBadges: {
          orderBy: { earnedAt: "desc" },
          select: {
            earnedAt: true,
            badge: { select: { id: true, name: true, iconUrl: true, description: true } },
          },
        },
      },
    }
  )
  if (!user) {
    const err = new Error("USER_NOT_FOUND")
    err.statusCode = 404
    throw err
  }

  const images = []
  for (let i = 0; i < files.length && i < 5; i++) {
    const file = files[i]
    const order = i
    const key = uuidv4()
    const detailPath = `images/details/${key}.webp`
    const thumbPath = `images/thumbnail/${key}.webp`
    const detailBuffer = await makeDetailWebp(file.buffer)
    const detailUrl = await uploadImage({
      path: detailPath,
      fileBuffer: detailBuffer,
      contentType: "image/webp",
    })

    if (order === 0) {
      const thumbBuffer = await makeThumbWebp(file.buffer)
      await uploadImage({
        path: thumbPath,
        fileBuffer: thumbBuffer,
        contentType: "image/webp",
      })
    }

    images.push({ id: 0, order, imageUrl: detailUrl })
  }

  const dealPayload = {
    id: dealId,
    title: dealCreateData.title,
    description: dealCreateData.description ?? null,
    url: dealCreateData.url ?? null,
    price: dealCreateData.price ?? null,
    originalPrice: dealCreateData.originalPrice ?? null,
    shippingPrice: dealCreateData.shippingPrice ?? null,
    percentOff: dealCreateData.percentOff ?? null,
    couponCode: dealCreateData.couponCode ?? null,
    location: dealCreateData.location ?? null,
    discountType: dealCreateData.discountType ?? null,
    discountValue: dealCreateData.discountValue ?? null,
    barcodeId: dealCreateData.barcodeId ?? null,
    maxNotifiedMilestone: 0,
    userId,
    score: 0,
    commentCount: 0,
    status: "PENDING",
    saletype: dealCreateData.saletype ?? "ONLINE",
    affiliateType: dealCreateData.affiliateType ?? "NON_AFFILIATE",
    sellerId: sellerId ?? null,
    customSeller: dealCreateData.customSeller ?? null,
    categoryId: dealCreateData.categoryId ?? 0,
    createdAt: now,
    updatedAt: now,
    user,
    images,
    dealTags: [],
    comments: [],
    aiReview: null,
  }

  const redisPayload = mapDealToRedisJson(dealPayload)
  await setUserPublicInRedis(user, { ttlSeconds: 31 * 24 * 60 * 60 })
  await setDealInRedis(dealId, redisPayload, {
    ttlSeconds: 31 * 24 * 60 * 60,
    skipAnalyticsInit: true,
  })

  queueDealCreate({
    dealId,
    data: {
      id: dealId,
      title: dealPayload.title,
      description: dealPayload.description,
      url: dealPayload.url,
      price: dealPayload.price,
      originalPrice: dealPayload.originalPrice,
      shippingPrice: dealPayload.shippingPrice,
      percentOff: dealPayload.percentOff,
      couponCode: dealPayload.couponCode,
      location: dealPayload.location,
        discountType: dealPayload.discountType,
        discountValue: dealPayload.discountValue,
        barcodeId: dealPayload.barcodeId,
        maxNotifiedMilestone: dealPayload.maxNotifiedMilestone,
      userId,
      status: dealPayload.status,
      saletype: dealPayload.saletype,
      affiliateType: dealPayload.affiliateType,
      sellerId: dealPayload.sellerId,
      customSeller: dealPayload.customSeller,
      categoryId: dealPayload.categoryId,
      createdAt: now.toISOString(),
      updatedAt: now.toISOString(),
    },
    images: images.map((img) => ({ imageUrl: img.imageUrl, order: img.order })),
    createdAt: now.toISOString(),
  }).catch((err) => console.error("DB sync deal create failed:", err?.message || err))

  await enqueueDealClassification({ dealId })

  const seller = dealPayload.sellerId ? await getSellerById(dealPayload.sellerId) : null
  const responseDeal = {
    ...dealPayload,
    seller: seller ?? null,
    images,
    comments: [],
    notices: [],
    breadcrumb: [],
    similarDeals: [],
    userStats: { totalLikes: 0, totalDeals: 0 },
    myVote: 0,
    _count: { comments: 0 },
  }

  return responseDeal
}

async function getDealEngagement(ids = [], viewer = null) {
  const normalized = (Array.isArray(ids) ? ids : [])
    .map((id) => Number(id))
    .filter((id) => Number.isInteger(id) && id > 0)

  const uniqueIds = Array.from(new Set(normalized))

  if (!viewer?.userId || uniqueIds.length === 0) {
    return normalized.map((id) => ({ id, myVote: 0 }))
  }

  const votes = await dealDB.findVotes(
    { userId: viewer.userId, dealId: { in: uniqueIds } },
    { select: { dealId: true, voteType: true } }
  )
  const voteByDealId = new Map()
  votes.forEach((vote) => voteByDealId.set(vote.dealId, vote.voteType))

  return normalized.map((id) => ({
    id,
    myVote: voteByDealId.get(id) ?? 0,
  }))
}

module.exports = {
  getDeals,
  getDealById,
  createDeal,
  getDealEngagement,
  getDealSuggestions,
  getBestWidgetDeals,
}