import { RowDataPacket } from "mysql2";
import pool from "../../../../lib/db/db";
import { NextResponse } from "next/server";

const connection = await pool.getConnection();

export async function GET(request) {
  try {
    const { searchParams } = new URL(request.url);

    const page = parseInt(searchParams.get("page") || "1", 10);
    const limit = parseInt(searchParams.get("limit") || "15", 10);
    const search = searchParams.get("search") || "";
    const status = searchParams.get("status") || "all";
    const fromDate = searchParams.get("from_date") || "";
    const toDate = searchParams.get("to_date") || "";
    const scholarshipId = searchParams.get("scholarship_id") || "";

    const skip = (page - 1) * limit;

    // Build WHERE conditions
    let where = "WHERE 1=1";
    let params = [];

    if (search) {
      where += " AND (sc.first_name LIKE ? OR sc.last_name LIKE ? OR sc.email LIKE ? OR sc.comment LIKE ?)";
      params.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    if (status !== "all") {
      where += " AND sc.is_approved = ?";
      params.push(status === "1" ? 1 : 0);
    }

    if (fromDate) {
      where += " AND DATE(sc.created_at) >= ?";
      params.push(fromDate);
    }

    if (toDate) {
      where += " AND DATE(sc.created_at) <= ?";
      params.push(toDate);
    }

    if (scholarshipId) {
      where += " AND sc.scholarship_id = ?";
      params.push(scholarshipId);
    }

    // Only get top-level comments (not replies)
    where += " AND sc.parent_id IS NULL";

    try {
      // Count total with filters
      const [countRows] = await connection.query(
        `SELECT COUNT(DISTINCT sc.id) as count 
         FROM scholarships_comments sc 
         ${where}`,
        params
      );
      const totalCount = countRows[0].count;

      // Fetch paginated comments with scholarship info
      const [rows] = await connection.query<RowDataPacket[]>(
        `SELECT 
          sc.id as comment_id,
          sc.first_name,
          sc.last_name,
          sc.email,
          sc.phone,
          sc.comment,
          sc.is_approved as status,
          sc.created_at,
          sc.scholarship_id,
          s.title as scholarship_title,
          s.slug as scholarship_slug
        FROM scholarships_comments sc
        LEFT JOIN scholarships s ON sc.scholarship_id = s.id
        ${where}
        ORDER BY sc.created_at DESC
        LIMIT ? OFFSET ?`,
        [...params, limit, skip]
      );

      // Get reply counts for each comment
      for (let comment of rows) {
        const [replyCountRows] = await connection.query(
          `SELECT COUNT(*) as reply_count 
           FROM scholarships_comments 
           WHERE parent_id = ?`,
          [comment.comment_id]
        );
        comment.reply_count = replyCountRows[0].reply_count;
      }

      const totalPages = Math.ceil(totalCount / limit);

      return NextResponse.json({
        success: true,
        data: rows,
        meta: {
          currentPage: page,
          totalPages: totalPages,
          totalItems: totalCount,
          itemsPerPage: limit,
          hasNextPage: page < totalPages,
          hasPrevPage: page > 1
        }
      });
    } finally {
      connection.release();
    }
  } catch (err) {
    console.error("❌ GET Scholarship Comments API Error:", err);
    return NextResponse.json(
      {
        success: false,
        error: "Failed to fetch scholarship comments",
        details: err.message,
      },
      { status: 500 }
    );
  }
}