import { and, eq, sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { review, type ReviewsSchema } from './schema.js'
import type { RatingAggregate, ReviewRating } from './types.js'

const RATINGS: ReviewRating[] = [1, 2, 3, 4, 5]

function emptyDistribution(): Record<ReviewRating, number> {
  return { 1: 0, 2: 0, 3: 0, 4: 0, 5: 0 }
}

export async function getRatingAggregate(
  q: Querier<ReviewsSchema>,
  productId: string,
): Promise<RatingAggregate> {
  const rows = await q
    .select({
      rating: review.rating,
      count: sql<number>`count(*)::int`,
    })
    .from(review)
    .where(and(eq(review.productId, productId), eq(review.status, 'approved')))
    .groupBy(review.rating)

  const distribution = emptyDistribution()
  let count = 0
  let sum = 0

  for (const row of rows) {
    const rating = row.rating as ReviewRating
    if (!RATINGS.includes(rating)) {
      continue
    }
    distribution[rating] = row.count
    count += row.count
    sum += rating * row.count
  }

  return {
    productId,
    avg: count === 0 ? 0 : sum / count,
    count,
    distribution,
  }
}