리뷰/평점 시스템

CREATE TABLE reviews (
  id UUID PRIMARY KEY,
  product_id UUID NOT NULL,
  user_id UUID NOT NULL,
  order_id UUID,                    -- 검증된 구매 (verified)
  rating SMALLINT CHECK (rating BETWEEN 1 AND 5),
  title VARCHAR,
  content TEXT NOT NULL,
  images TEXT[],
  verified_purchase BOOLEAN DEFAULT false,
  helpful_count INT DEFAULT 0,
  status VARCHAR DEFAULT 'pending',  -- pending, published, rejected
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (product_id, user_id, order_id)  -- 1주문당 1리뷰
);

CREATE INDEX ON reviews(product_id, created_at DESC) WHERE status = 'published';
CREATE INDEX ON reviews(user_id);


-- 도움됨 표시
CREATE TABLE review_helpful (
  user_id UUID,
  review_id UUID,
  helpful BOOLEAN,  -- true=helpful, false=unhelpful
  PRIMARY KEY (user_id, review_id)
);


-- 상품 평점 집계 (캐싱)
ALTER TABLE products ADD COLUMN avg_rating DECIMAL(3,2) DEFAULT 0;
ALTER TABLE products ADD COLUMN reviews_count INT DEFAULT 0;
ALTER TABLE products ADD COLUMN ratings_breakdown JSONB DEFAULT '{}';
-- 예: { "1": 2, "2": 5, "3": 12, "4": 30, "5": 50 }

async function createReview(userId: string, input: CreateReviewInput) {
  // 1. 검증된 구매인가?
  const order = await db.order.findFirst({
    where: {
      id: input.orderId,
      userId,
      status: { in: ['delivered', 'completed'] },
      items: { some: { variant: { productId: input.productId } } },
    },
  });

  // 2. 이미 작성했는가?
  const existing = await db.review.findFirst({
    where: { productId: input.productId, userId, orderId: input.orderId },
  });
  if (existing) throw new ConflictError('Already reviewed');

  // 3. 작성 (검증된 구매면 즉시 published, 아니면 pending)
  return db.$transaction(async (tx) => {
    const review = await tx.review.create({
      data: {
        ...input,
        userId,
        verifiedPurchase: !!order,
        status: order ? 'published' : 'pending',  // 비검증은 모더레이션 후
      },
    });

    if (review.status === 'published') {
      await updateProductStats(tx, input.productId);
    }

    return review;
  });
}


async function updateProductStats(tx: any, productId: string) {
  const stats = await tx.review.groupBy({
    by: ['rating'],
    where: { productId, status: 'published' },
    _count: true,
  });

  const breakdown: any = { 1: 0, 2: 0, 3: 0, 4: 0, 5: 0 };
  let total = 0;
  let sum = 0;
  for (const s of stats) {
    breakdown[s.rating] = s._count;
    total += s._count;
    sum += s.rating * s._count;
  }

  await tx.product.update({
    where: { id: productId },
    data: {
      avgRating: total > 0 ? Number((sum / total).toFixed(2)) : 0,
      reviewsCount: total,
      ratingsBreakdown: breakdown,
    },
  });
}

async function toggleHelpful(userId: string, reviewId: string) {
  return db.$transaction(async (tx) => {
    const existing = await tx.reviewHelpful.findUnique({
      where: { userId_reviewId: { userId, reviewId } },
    });

    if (existing?.helpful) {
      // 취소
      await tx.reviewHelpful.delete({
        where: { userId_reviewId: { userId, reviewId } },
      });
      await tx.review.update({
        where: { id: reviewId },
        data: { helpfulCount: { decrement: 1 } },
      });
    } else {
      await tx.reviewHelpful.upsert({
        where: { userId_reviewId: { userId, reviewId } },
        create: { userId, reviewId, helpful: true },
        update: { helpful: true },
      });
      await tx.review.update({
        where: { id: reviewId },
        data: { helpfulCount: { increment: 1 } },
      });
    }
  });
}

// 어뷰즈 자동 검출
async function checkAbuse(content: string) {
  // 1. 부적절 단어 필터
  const banned = await db.bannedWord.findMany();
  if (banned.some(w => content.toLowerCase().includes(w.word))) {
    return { flagged: true, reason: 'banned_word' };
  }

  // 2. 도배 (반복 문자)
  if (/(.)\1{10,}/.test(content)) {
    return { flagged: true, reason: 'spam_pattern' };
  }

  // 3. 외부 링크 다수
  const links = (content.match(/https?:\/\//g) ?? []).length;
  if (links > 2) {
    return { flagged: true, reason: 'too_many_links' };
  }

  // 4. AI 모더레이션 (OpenAI Moderation API)
  const res = await fetch('https://api.openai.com/v1/moderations', {
    method: 'POST',
    headers: { 'Authorization': `Bearer ${process.env.OPENAI_KEY}` },
    body: JSON.stringify({ input: content }),
  });
  const data = await res.json();
  if (data.results[0].flagged) {
    return { flagged: true, reason: 'ai_flagged', categories: data.results[0].categories };
  }

  return { flagged: false };
}


async function createReview(userId: string, input: any) {
  const { flagged } = await checkAbuse(input.content);
  const review = await db.review.create({
    data: {
      ...input,
      userId,
      status: flagged ? 'pending' : 'published',
      // pending = 관리자 검토 후 published
    },
  });
}

app.get('/products/:id/reviews', async (req, res) => {
  const { sort = 'helpful', filter, page = 1 } = req.query;

  const orderBy = {
    helpful: [{ helpfulCount: 'desc' }, { createdAt: 'desc' }],
    recent: [{ createdAt: 'desc' }],
    high: [{ rating: 'desc' }, { createdAt: 'desc' }],
    low: [{ rating: 'asc' }, { createdAt: 'desc' }],
  }[sort as string] ?? [{ createdAt: 'desc' }];

  const where: any = {
    productId: req.params.id,
    status: 'published',
  };
  if (filter === 'verified') where.verifiedPurchase = true;
  if (filter === 'with_images') where.images = { isEmpty: false };

  const [items, total] = await Promise.all([
    db.review.findMany({
      where, orderBy,
      take: 10, skip: (Number(page) - 1) * 10,
      include: { user: { select: { name: true, avatarUrl: true } } },
    }),
    db.review.count({ where }),
  ]);

  res.json({ items, total });
});