import { desc, asc, eq, like, or, and, sql, isNotNull, ne, isNull } from 'drizzle-orm'
import { useDB, schema } from '../../utils/db.js'
import { requireAdmin } from '../../utils/auth.js'
import { parsePagination } from '../../utils/pagination.js'

export default defineEventHandler(async (event) => {
  requireAdmin(event)

  const db = useDB()
  const { page, limit, offset, search } = parsePagination(event)
  const q = getQuery(event)

  const conditions = []
  if (search) {
    conditions.push(or(
      like(schema.reviews.author, `%${search}%`),
      like(schema.reviews.content, `%${search}%`),
      like(schema.reviews.title, `%${search}%`),
    ))
  }
  if (q.published === 'true' || q.published === '1') conditions.push(eq(schema.reviews.published, true))
  if (q.published === 'false' || q.published === '0') conditions.push(eq(schema.reviews.published, false))
  if (q.hasScreenshot === 'true' || q.hasScreenshot === '1') {
    conditions.push(and(isNotNull(schema.reviews.screenshot), ne(schema.reviews.screenshot, '')))
  }
  if (q.hasScreenshot === 'false' || q.hasScreenshot === '0') {
    conditions.push(or(isNull(schema.reviews.screenshot), eq(schema.reviews.screenshot, '')))
  }
  if (q.scene) conditions.push(like(schema.reviews.scenes, `%"${q.scene}"%`))
  if (q.concern) conditions.push(like(schema.reviews.concerns, `%"${q.concern}"%`))

  const whereClause = conditions.length ? and(...conditions) : sql`1=1`

  const [{ count: total }] = db
    .select({ count: sql`count(*)` })
    .from(schema.reviews)
    .where(whereClause)
    .all()

  const rows = db
    .select()
    .from(schema.reviews)
    .where(whereClause)
    .orderBy(asc(schema.reviews.sortOrder), desc(schema.reviews.id))
    .limit(limit)
    .offset(offset)
    .all()

  // 解析 JSON 字段方便前端展示
  const items = rows.map(r => ({
    ...r,
    scenes: (() => { try { return JSON.parse(r.scenes || '[]') } catch { return [] } })(),
    concerns: (() => { try { return JSON.parse(r.concerns || '[]') } catch { return [] } })(),
  }))

  return { items, total, page, limit, totalPages: Math.ceil(total / limit) }
})