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

interface FilterOptions {
  cities: string[];
  states: string[];
  nationalities: string[];
  specializations: string[];
  languages: string[];
  targetCountries: string[];
  availabilities: string[];
}

export async function GET(request: NextRequest) {
  try {
    // Get all active consultants
    const [consultants] = await pool.query<RowDataPacket[]>(`
      SELECT 
        city,
        state,
        nationality,
        specialization,
        languages,
        target_countries,
        availability
      FROM consultants 
      WHERE active = '1' 
      AND active_profile = '1'
      AND profile_status IN ('approved', 'complete')
    `);

    // Initialize sets for unique values
    const cities = new Set<string>();
    const states = new Set<string>();
    const nationalities = new Set<string>();
    const specializations = new Set<string>();
    const languages = new Set<string>();
    const targetCountries = new Set<string>();
    const availabilities = new Set<string>();

    // Helper function to parse field values
    const parseFieldValues = (field: any): string[] => {
      if (!field) return [];
      
      // Try to parse as JSON array
      if (typeof field === 'string') {
        // Check if it looks like JSON
        if (field.trim().startsWith('[') && field.trim().endsWith(']')) {
          try {
            const parsed = JSON.parse(field);
            if (Array.isArray(parsed)) {
              return parsed
                .map(item => item?.toString().trim())
                .filter(item => item && item !== 'null' && item !== 'undefined');
            }
          } catch (e) {
            // If JSON parsing fails, fall back to comma separation
          }
        }
        
        // Try comma-separated string
        return field.split(',')
          .map(item => item.trim())
          .filter(item => item && item !== 'null' && item !== 'undefined');
      }
      
      return [];
    };

    // Process each consultant
    consultants.forEach((consultant: any) => {
      // Simple string fields
      if (consultant.city && consultant.city.trim() && consultant.city.trim() !== 'null') {
        cities.add(consultant.city.trim());
      }
      
      if (consultant.state && consultant.state.trim() && consultant.state.trim() !== 'null') {
        states.add(consultant.state.trim());
      }
      
      if (consultant.nationality && consultant.nationality.trim() && consultant.nationality.trim() !== 'null') {
        nationalities.add(consultant.nationality.trim());
      }
      
      if (consultant.availability && consultant.availability.trim() && consultant.availability.trim() !== 'null') {
        availabilities.add(consultant.availability.trim());
      }
      
      // Parse array fields
      const specValues = parseFieldValues(consultant.specialization);
      specValues.forEach(spec => {
        if (spec && spec.trim()) {
          specializations.add(spec.trim());
        }
      });
      
      const langValues = parseFieldValues(consultant.languages);
      langValues.forEach(lang => {
        if (lang && lang.trim()) {
          languages.add(lang.trim());
        }
      });
      
      const countryValues = parseFieldValues(consultant.target_countries);
      countryValues.forEach(country => {
        if (country && country.trim()) {
          targetCountries.add(country.trim());
        }
      });
    });

    // Prepare response data
    const filterOptions: FilterOptions = {
      cities: Array.from(cities).sort(),
      states: Array.from(states).sort(),
      nationalities: Array.from(nationalities).sort(),
      specializations: Array.from(specializations).sort(),
      languages: Array.from(languages).sort(),
      targetCountries: Array.from(targetCountries).sort(),
      availabilities: Array.from(availabilities).sort()
    };

    return NextResponse.json({
      success: true,
      ...filterOptions
    });

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