import { prisma } from '@/lib/prisma'
import { ModerationStatus } from '@prisma/client'

export async function getFeaturedSpots(take = 3) {
  const spots = await prisma.$queryRaw<(SpotListItem & { avgRating: number; reviewCount: number })[]>`
    SELECT
      s.id, s.name, s.description, s.lat, s.lng, s.difficulty,
      s.coverImage, s.tags, s.authorId, s.createdAt,
      COALESCE(AVG(r.rating), 0) as avgRating,
      COUNT(r.id) as reviewCount
    FROM Spot s
    LEFT JOIN Review r ON r.spotId = s.id AND r.status = ${ModerationStatus.APPROVED}
    WHERE s.status = ${ModerationStatus.APPROVED}
    GROUP BY s.id
    ORDER BY avgRating DESC, reviewCount DESC, s.createdAt DESC
    LIMIT ${take}
  `
  return spots
}

export async function getHomeStats() {
  const [spotCount, userCount] = await Promise.all([
    prisma.spot.count({ where: { status: ModerationStatus.APPROVED } }),
    prisma.user.count(),
  ])
  return { spotCount, userCount }
}

export interface SpotListItem {
  id: string
  name: string
  description: string
  lat: number
  lng: number
  difficulty: string
  coverImage: string | null
  tags: unknown
  authorId: string | null
  createdAt: Date
  avgRating?: number
  reviewCount?: number
  distanceKm?: number
}

export interface SpotListParams {
  cursor?: string
  take?: number
  lat?: number
  lng?: number
  radiusKm?: number
  difficulty?: string
}

export async function listApprovedSpots(params: SpotListParams = {}): Promise<{
  spots: SpotListItem[]
  nextCursor: string | null
}> {
  const take = Math.min(params.take ?? 20, 50)

  if (params.lat !== undefined && params.lng !== undefined) {
    const radiusKm = params.radiusKm ?? 50
    const spots = await prisma.$queryRaw<SpotListItem[]>`
      SELECT
        s.id, s.name, s.description, s.lat, s.lng, s.difficulty,
        s.coverImage, s.tags, s.authorId, s.createdAt,
        AVG(r.rating) as avgRating,
        COUNT(r.id) as reviewCount,
        (6371 * ACOS(
          COS(RADIANS(${params.lat})) * COS(RADIANS(s.lat)) *
          COS(RADIANS(s.lng) - RADIANS(${params.lng})) +
          SIN(RADIANS(${params.lat})) * SIN(RADIANS(s.lat))
        )) AS distanceKm
      FROM Spot s
      LEFT JOIN Review r ON r.spotId = s.id AND r.status = ${ModerationStatus.APPROVED}
      WHERE s.status = ${ModerationStatus.APPROVED}
      HAVING distanceKm <= ${radiusKm}
      GROUP BY s.id
      ORDER BY distanceKm ASC
      LIMIT ${take}
    `
    return { spots, nextCursor: null }
  }

  const where = {
    status: ModerationStatus.APPROVED,
    ...(params.difficulty ? { difficulty: params.difficulty as any } : {}),
    ...(params.cursor ? { id: { gt: params.cursor } } : {}),
  }

  const spots = await prisma.spot.findMany({
    where,
    take: take + 1,
    orderBy: { createdAt: 'desc' },
    include: {
      _count: { select: { reviews: { where: { status: ModerationStatus.APPROVED } } } },
    },
  })

  const hasMore = spots.length > take
  const page = hasMore ? spots.slice(0, take) : spots

  return {
    spots: page.map((s) => ({
      id: s.id,
      name: s.name,
      description: s.description,
      lat: s.lat,
      lng: s.lng,
      difficulty: s.difficulty,
      coverImage: s.coverImage,
      tags: s.tags,
      authorId: s.authorId,
      createdAt: s.createdAt,
      reviewCount: s._count.reviews,
    })),
    nextCursor: hasMore ? page[page.length - 1].id : null,
  }
}

export async function getSpotDetail(id: string) {
  return prisma.spot.findUnique({
    where: { id },
    include: {
      author: { select: { id: true, name: true, image: true } },
      reviews: {
        where: { status: ModerationStatus.APPROVED },
        orderBy: { createdAt: 'desc' },
        take: 20,
        include: { author: { select: { id: true, name: true, image: true } } },
      },
    },
  })
}