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

export async function GET(request: NextRequest) {

                  const commingHeaders = request.headers.get("authorization");

    if(commingHeaders !== process.env.APP_API_KEY)
    {
        return NextResponse.json(
            { success: false, message: 'Unauthorized Access' },
            { status: 401 }
        );
    }

  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 15;
    const search = searchParams.get('search') || '';
    const featured = searchParams.get('featured') || '';
    const active = searchParams.get('active') || '';
    const startDate = searchParams.get('start_date') || '';
    const endDate = searchParams.get('end_date') || '';

    console.log('🔍 Guides API called with params:', { page, limit, search, featured, active, startDate, endDate });

    // Build WHERE clause for filtering
    let whereClauses = [];
    let queryParams = [];

    // Add search filter
    if (search) {
      whereClauses.push(`
        (g.title LIKE ? OR g.sub_title LIKE ? OR g.guide_type LIKE ?)
      `);
      queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }

    // Add featured filter
    if (featured !== '') {
      whereClauses.push('g.is_featured = ?');
      queryParams.push(featured === 'true' ? 1 : 0);
    }

    // Add active filter
    if (active !== '') {
      whereClauses.push('g.is_active = ?');
      queryParams.push(active === 'true' ? 1 : 0);
    }

    // Add date filters
    if (startDate) {
      whereClauses.push('g.created_at >= ?');
      queryParams.push(new Date(startDate).toISOString().slice(0, 19).replace('T', ' '));
    }
    if (endDate) {
      whereClauses.push('g.created_at <= ?');
      queryParams.push(new Date(endDate).toISOString().slice(0, 19).replace('T', ' '));
    }

    // Combine where clauses
    const whereClause = whereClauses.length > 0 ? 'WHERE ' + whereClauses.join(' AND ') : 'WHERE g.is_active = true';

    // Get total count for pagination
    const countQuery = `
      SELECT COUNT(*) AS totalItems 
      FROM guides g
      ${whereClause}
    `;
    const [countResults] = await pool.query<RowDataPacket[]>(countQuery, queryParams);
    const totalItems = countResults[0].totalItems;
    const totalPages = Math.ceil(totalItems / limit);

    console.log('🔍 Total guides in database:', totalItems);
    console.log('🔍 Total pages:', totalPages);

    // Calculate pagination (OFFSET)
    const offset = (page - 1) * limit;

    // Fetch guides with author information
    const guidesQuery = `
      SELECT 
        g.id, 
        g.title, 
        g.sub_title, 
        g.description, 
        g.image, 
        g.guide_type, 
        g.university_id, 
        g.subject_id, 
        g.slug, 
        g.sort_order, 
        g.is_active, 
        g.is_featured, 
        g.sm_question, 
        g.sm_answer, 
        g.review_detail, 
        g.seo, 
        g.created_at, 
        g.updated_at,
        a.id as author_id,
        a.full_name as author_name,
        a.slug as author_slug,
        a.profile_image as author_image,
        a.bio as author_bio
      FROM guides g
      LEFT JOIN authors a ON g.author_id = a.id AND a.status = 'active'
      ${whereClause}
      GROUP BY g.id
      ORDER BY g.created_at DESC
      LIMIT ? OFFSET ?;
    `;
    const [guides] = await pool.query<RowDataPacket[]>(guidesQuery, [...queryParams, limit, offset]);

    console.log('🔍 Fetched guides count:', guides.length);

    return Response.json({
      success: true,
      data: guides,
      meta: {
        totalItems: totalItems,
        totalPages: totalPages,
        currentPage: page,
        limit: limit,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      }
    });

  } catch (error) {
    console.error('🔍 Error in guides API:', error);
    return Response.json({
      success: false,
      error: 'Failed to fetch guides',
      details: error.message
    }, { status: 500 });
  }
}