// app/api/frontend/consultants/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket } from 'mysql2';
import pool from '../../../../lib/db/db';

interface Consultant extends RowDataPacket {
  id: number;
  user_id: number;
  name: string;
  company_name: string;
  city: string;
  state: string;
  nationality: string;
  average_rating: number;
  rating_count: number;
  services: string;
  specialization: string;
  consultation_fee: number;
  consultation_currency: string;
  profile_picture: string | null;
  experience_years: number | null;
  digital_only: number;
  premium_featured: string | null;
  seo_slug: string | null;
  phone: string | null;
  email: string | null;
  languages: string;
  target_countries: string;
  response_time: string;
  availability: string;
  designation: string;
  profile_status: string;
  active: string;
  active_profile: string;
  featured: string;
}

interface FilterParams {
  city?: string;
  state?: string;
  nationality?: string;
  specialization?: string;
  minRating?: string;
  maxFee?: string;
  verification?: boolean;
  digitalOnly?: boolean;
  availability?: string;
  targetCountry?: string;
  language?: string;
  search?: string;
}

export async function GET(request: NextRequest) {
  try {
    const searchParams = request.nextUrl.searchParams;
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '12');
    const searchQuery = searchParams.get('search') || '';
    const sortBy = searchParams.get('sort') || 'featured';
    
    // Build filters from query parameters
    const filters: FilterParams = {
      city: searchParams.get('city') || undefined,
      state: searchParams.get('state') || undefined,
      nationality: searchParams.get('nationality') || undefined,
      specialization: searchParams.get('specialization') || undefined,
      minRating: searchParams.get('minRating') || undefined,
      maxFee: searchParams.get('maxFee') || undefined,
      verification: searchParams.get('verification') === 'true',
      digitalOnly: searchParams.get('digitalOnly') === 'true',
      availability: searchParams.get('availability') || undefined,
      targetCountry: searchParams.get('targetCountry') || undefined,
      language: searchParams.get('language') || undefined,
      search: searchQuery
    };

    // Check if any filters are active (excluding sort)
    const isFilterActive = Object.entries(filters).some(([key, value]) => {
      // Don't count search query as a filter for sorting purposes
      if (key === 'search') return false;
      if (key === 'verification' || key === 'digitalOnly') {
        return value === true;
      }
      return value !== undefined && value !== '';
    });

    const offset = (page - 1) * limit;

    // Build base query
    let baseQuery = `
      SELECT 
        c.*
      FROM consultants c
      WHERE c.active = '1' 
      AND c.active_profile = '1'
      AND c.seo_slug != ''
      AND c.seo_slug IS NOT NULL
      AND c.profile_status IN ('approved', 'complete')
    `;

    const queryParams: any[] = [];

    // Apply search query
    if (searchQuery) {
      baseQuery += `
        AND (
          c.company_name LIKE ? 
          OR c.name LIKE ? 
          OR c.designation LIKE ?
          OR c.specialization LIKE ?
          OR c.services LIKE ?
          OR c.nationality LIKE ?
          OR c.target_countries LIKE ?
          OR c.languages LIKE ?
        )
      `;
      const searchPattern = `%${searchQuery}%`;
      queryParams.push(
        searchPattern, searchPattern, searchPattern, 
        searchPattern, searchPattern, searchPattern,
        searchPattern, searchPattern
      );
    }

    // Apply other filters
    if (filters.city) {
      baseQuery += ` AND c.city = ?`;
      queryParams.push(filters.city);
    }

    if (filters.state) {
      baseQuery += ` AND c.state = ?`;
      queryParams.push(filters.state);
    }

    if (filters.nationality) {
      baseQuery += ` AND c.nationality = ?`;
      queryParams.push(filters.nationality);
    }

    if (filters.specialization) {
      baseQuery += ` AND c.specialization LIKE ?`;
      queryParams.push(`%${filters.specialization}%`);
    }

    if (filters.minRating) {
      baseQuery += ` AND c.average_rating >= ?`;
      queryParams.push(parseFloat(filters.minRating));
    }

    if (filters.maxFee) {
      baseQuery += ` AND c.consultation_fee <= ?`;
      queryParams.push(parseFloat(filters.maxFee));
    }

    if (filters.verification) {
      baseQuery += ` AND c.profile_status = 'approved'`;
    }

    if (filters.digitalOnly) {
      baseQuery += ` AND c.digital_only = 1`;
    }

    if (filters.availability) {
      baseQuery += ` AND c.availability = ?`;
      queryParams.push(filters.availability);
    }

    if (filters.targetCountry) {
      baseQuery += ` AND c.target_countries LIKE ?`;
      queryParams.push(`%${filters.targetCountry}%`);
    }

    if (filters.language) {
      baseQuery += ` AND c.languages LIKE ?`;
      queryParams.push(`%${filters.language}%`);
    }

    // Get total count
    const countQuery = `SELECT COUNT(*) as total FROM (${baseQuery}) as filtered`;
    const [countResult] = await pool.query<RowDataPacket[]>(countQuery, queryParams);
    const total = countResult[0].total;

    // Apply sorting based on sortBy parameter
    switch (sortBy) {
      case 'featured':
        if (isFilterActive) {
          // When other filters are active, sort by rating
          baseQuery += ` ORDER BY c.average_rating DESC`;
        } else {
          // No filters: show premium_featured first, then by rating
          baseQuery += ` ORDER BY 
            CASE 
              WHEN c.premium_featured IS NULL OR c.premium_featured = '' THEN 1 
              ELSE 0 
            END,
            CAST(c.premium_featured AS UNSIGNED) ASC,
            c.average_rating DESC`;
        }
        break;
        
      case 'rating':
        baseQuery += ` ORDER BY c.average_rating DESC`;
        break;
        
      case 'fee-low':
        baseQuery += ` ORDER BY c.consultation_fee ASC`;
        break;
        
      case 'fee-high':
        baseQuery += ` ORDER BY c.consultation_fee DESC`;
        break;
        
      default:
        // Default to featured sorting
        baseQuery += ` ORDER BY 
          CASE 
            WHEN c.premium_featured IS NULL OR c.premium_featured = '' THEN 1 
            ELSE 0 
          END,
          CAST(c.premium_featured AS UNSIGNED) ASC,
          c.average_rating DESC`;
        break;
    }

    // Add pagination
    baseQuery += ` LIMIT ? OFFSET ?`;
    queryParams.push(limit, offset);

    // Execute main query
    const [consultants] = await pool.query<Consultant[]>(baseQuery, queryParams);

    // Process the results
    const processedConsultants = consultants.map(consultant => {
      // Helper function to parse JSON or comma-separated strings
      const parseField = (field: string | null): string[] => {
        if (!field) return [];
        
        // Try to parse as JSON first
        try {
          const parsed = JSON.parse(field);
          if (Array.isArray(parsed)) {
            return parsed.filter((item: any) => item && item.trim());
          }
        } catch (e) {
          // If not JSON, try comma-separated
          if (typeof field === 'string') {
            return field.split(',')
              .map(item => item.trim())
              .filter(item => item);
          }
        }
        return [];
      };

      return {
        ...consultant,
        verified: consultant.profile_status === 'approved',
        services: parseField(consultant.services),
        specialization: parseField(consultant.specialization),
        languages: parseField(consultant.languages),
        target_countries: parseField(consultant.target_countries),
        premium_featured: consultant.premium_featured ? parseInt(consultant.premium_featured) : null,
        consultation_fee: parseFloat(consultant.consultation_fee.toString()),
        average_rating: parseFloat(consultant.average_rating?.toString() || '0'),
        rating_count: parseInt(consultant.rating_count?.toString() || '0'),
        experience_years: consultant.experience_years ? parseInt(consultant.experience_years.toString()) : null,
        digital_only: Boolean(consultant.digital_only)
      };
    });

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

    return NextResponse.json({
      success: true,
      data: processedConsultants,
      pagination: {
        page,
        limit,
        total,
        totalPages,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1,
      }
    });

  } catch (error) {
    console.error('Error fetching consultants:', error);
    return NextResponse.json(
      { 
        success: false, 
        message: 'Failed to fetch consultants',
        error: error instanceof Error ? error.message : 'Unknown error'
      },
      { status: 500 }
    );
  }
}