// app/api/frontend/consultants/[slug]/route.ts (RENAME from [id] to [slug])
import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket } from 'mysql2';
import pool from '../../../../../lib/db/db';

export async function GET(
  request: NextRequest,
  context: { params: Promise<{ slug: string }> }
) {
  const { slug } = await context.params;
  const connection = await pool.getConnection();

  try {
    // Determine if slug is numeric (ID) or string (seo_slug)
    const isNumeric = !isNaN(Number(slug));
    
    let query = '';
    let queryParams: any[] = [];

    if (isNumeric) {
      // Search by ID
      query = `
        SELECT 
          c.*,
          CASE 
            WHEN c.profile_status = 'approved'
             AND c.active = '1'
             AND c.active_profile = '1'
            THEN 1
            ELSE 0
          END AS verified
        FROM consultants c
        WHERE c.id = ?
      `;
      queryParams = [parseInt(slug)];
    } else {
      // Search by seo_slug
      query = `
        SELECT 
          c.*,
          CASE 
            WHEN c.profile_status = 'approved'
             AND c.active = '1'
             AND c.active_profile = '1'
            THEN 1
            ELSE 0
          END AS verified
        FROM consultants c
        WHERE c.seo_slug = ?
      `;
      queryParams = [slug];
    }

    const [consultants] = await connection.query<RowDataPacket[]>(query, queryParams);

    // If not found by primary method, try alternative
    if (consultants.length === 0) {
      const altQuery = `
        SELECT 
          c.*,
          CASE 
            WHEN c.profile_status = 'approved'
             AND c.active = '1'
             AND c.active_profile = '1'
            THEN 1
            ELSE 0
          END AS verified
        FROM consultants c
        WHERE c.id = ? OR c.seo_slug = ?
      `;
      
      const [altConsultants] = await connection.query<RowDataPacket[]>(altQuery, [slug, slug]);
      
      if (altConsultants.length === 0) {
        return NextResponse.json(
          { success: false, message: 'Consultant not found' },
          { status: 404 }
        );
      }
      
      // Use the first matching consultant
      const consultant = altConsultants[0];
      const parseJSON = (str: string | null): any[] => {
        if (!str) return [];
        try {
          return JSON.parse(str);
        } catch {
          return [];
        }
      };

      const parsedConsultant = {
        ...consultant,
        services: parseJSON(consultant.services),
        specialization: parseJSON(consultant.specialization),
        target_countries: parseJSON(consultant.target_countries),
        languages: parseJSON(consultant.languages),
        education_background: parseJSON(consultant.education_background),
        certifications: parseJSON(consultant.certifications),
        consultation_modes: parseJSON(consultant.consultation_modes),
        verified: consultant.verified === 1,
        digital_only: consultant.digital_only === 1
      };

      return NextResponse.json(
        { success: true, data: parsedConsultant },
        { status: 200 }
      );
    }

    const consultant = consultants[0];

    const parseJSON = (str: string | null): any[] => {
      if (!str) return [];
      try {
        return JSON.parse(str);
      } catch {
        return [];
      }
    };

    const parsedConsultant = {
      ...consultant,
      services: parseJSON(consultant.services),
      specialization: parseJSON(consultant.specialization),
      target_countries: parseJSON(consultant.target_countries),
      languages: parseJSON(consultant.languages),
      education_background: parseJSON(consultant.education_background),
      certifications: parseJSON(consultant.certifications),
      consultation_modes: parseJSON(consultant.consultation_modes),
      verified: consultant.verified === 1,
      digital_only: consultant.digital_only === 1
    };

    return NextResponse.json(
      { success: true, data: parsedConsultant },
      { status: 200 }
    );
  } catch (error: any) {
    console.error('Consultant API Error:', error);
    return NextResponse.json(
      { success: false, message: error.message || 'Failed to fetch consultant' },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}