import pool from '../../../../../../lib/db/db';
import { NextResponse } from 'next/server';
import { verifyAuthentication } from '../../../../../utils/verifyAuthentication';
import { S3Client, PutObjectCommand, DeleteObjectCommand } from '@aws-sdk/client-s3';
import { RowDataPacket, ResultSetHeader } from 'mysql2/promise';

interface ConsultantProfileBody {
  first_name?: string;
  last_name?: string;
  designation?: string;
  email?: string;
  phone?: string;
  company_name?: string;
  number_of_employees?: number | string;
  address?: string;
  nationality?: string;
  state?: string;
  city?: string;
  consultation_fee?: number;
  consultation_currency?: string;
  target_countries?: string;
  services?: string;
  specialization?: string;
  languages?: string;
  response_time?: string;
  digital_only?: boolean;
  availability?: string;
  comment?: string;
  seo_title?: string;
  seo_description?: string;
  seo_keywords?: string;
  seo_slug?: string;
  request_approval?: boolean | string;
}

// Helper function to generate unique slug
function generateUniqueSlug(baseSlug) {
  const timestamp = Date.now();
  const randomString = Math.random().toString(36).substring(2, 8);
  return `${baseSlug}-${timestamp}-${randomString}`.toLowerCase();
}

// Helper function to create SEO-friendly slug
function createSEOSlug(text) {
  if (!text) return '';
  return text
    .toLowerCase()
    .replace(/[^\w\s-]/g, '') // Remove special characters
    .replace(/\s+/g, '-') // Replace spaces with hyphens
    .replace(/-+/g, '-') // Replace multiple hyphens with single hyphen
    .trim();
}

// Helper function to auto-generate SEO title
function generateSEOTitle(first_name, last_name, designation, company_name, specialization) {
  if (!first_name || !last_name) return '';
  
  let title = `${first_name} ${last_name}`;
  if (designation) title += ` - ${designation}`;
  if (company_name) title += ` at ${company_name}`;
  
  if (specialization) {
    try {
      const specArray = typeof specialization === 'string' ? JSON.parse(specialization) : specialization;
      if (Array.isArray(specArray) && specArray.length > 0) {
        const specializations = specArray.slice(0, 3).join(', ');
        title += ` | ${specializations} Expert`;
      }
    } catch {
      // If parsing fails, continue without specialization
    }
  }
  
  return title.slice(0, 60);
}

// Helper function to auto-generate SEO description
function generateSEODescription(first_name, last_name, designation, company_name, services, languages, target_countries) {
  if (!first_name || !last_name || !designation || !company_name) return '';
  
  let desc = `${first_name} ${last_name} is a ${designation} at ${company_name}`;
  
  if (services) {
    try {
      const servicesArray = typeof services === 'string' ? JSON.parse(services) : services;
      if (Array.isArray(servicesArray) && servicesArray.length > 0) {
        desc += ` specializing in ${servicesArray.slice(0, 3).join(', ')}`;
      }
    } catch {
      // If parsing fails, continue without services
    }
  }
  
  if (languages) {
    try {
      const languagesArray = typeof languages === 'string' ? JSON.parse(languages) : languages;
      if (Array.isArray(languagesArray) && languagesArray.length > 0) {
        desc += `. Fluent in ${languagesArray.slice(0, 3).join(', ')}`;
      }
    } catch {
      // If parsing fails, continue without languages
    }
  }
  
  if (target_countries) {
    try {
      const countriesArray = typeof target_countries === 'string' ? JSON.parse(target_countries) : target_countries;
      if (Array.isArray(countriesArray) && countriesArray.length > 0) {
        desc += `. Expert in visa services for ${countriesArray.slice(0, 3).join(', ')}`;
      }
    } catch {
      // If parsing fails, continue without countries
    }
  }
  
  return desc.slice(0, 160);
}

// Helper function to auto-generate SEO keywords
function generateSEOKeywords(first_name, last_name, designation, company_name, services, specialization, languages, target_countries) {
  const keywords = [];
  
  if (first_name && last_name) {
    keywords.push(`${first_name} ${last_name}`);
  }
  if (designation) keywords.push(designation);
  if (company_name) keywords.push(company_name);
  
  keywords.push('immigration consultant', 'visa expert');
  
  if (services) {
    try {
      const servicesArray = typeof services === 'string' ? JSON.parse(services) : services;
      if (Array.isArray(servicesArray) && servicesArray.length > 0) {
        keywords.push(...servicesArray.slice(0, 5));
      }
    } catch {
      // If parsing fails, continue
    }
  }
  
  if (specialization) {
    try {
      const specArray = typeof specialization === 'string' ? JSON.parse(specialization) : specialization;
      if (Array.isArray(specArray) && specArray.length > 0) {
        keywords.push(...specArray.slice(0, 5));
      }
    } catch {
      // If parsing fails, continue
    }
  }
  
  if (languages) {
    try {
      const languagesArray = typeof languages === 'string' ? JSON.parse(languages) : languages;
      if (Array.isArray(languagesArray) && languagesArray.length > 0) {
        keywords.push(...languagesArray.map(lang => `${lang} speaking consultant`));
      }
    } catch {
      // If parsing fails, continue
    }
  }
  
  if (target_countries) {
    try {
      const countriesArray = typeof target_countries === 'string' ? JSON.parse(target_countries) : target_countries;
      if (Array.isArray(countriesArray) && countriesArray.length > 0) {
        keywords.push(...countriesArray.map(country => `${country} visa`));
      }
    } catch {
      // If parsing fails, continue
    }
  }
  
  return keywords.slice(0, 10).join(', ');
}

// S3 Client configuration
const s3 = new S3Client({
  region: process.env.AWS_DEFAULT_REGION || 'us-east-1',
  credentials: {
    accessKeyId: process.env.AWS_ACCESS_KEY_ID || '',
    secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY || '',
  },
  forcePathStyle: process.env.AWS_USE_PATH_STYLE_ENDPOINT === 'true',
});

// Helper function to generate S3 key
function generateS3Key(folder, fileName) {
  const timestamp = Date.now();
  const sanitizedFileName = fileName.replace(/\s+/g, '_').replace(/[^a-zA-Z0-9._-]/g, '');
  const uniqueId = Math.random().toString(36).substring(2, 9);
  return `${folder}/${timestamp}_${uniqueId}_${sanitizedFileName}`;
}

// Helper function to delete file from S3
async function deleteFromS3(url) {
  try {
    const bucket = process.env.AWS_BUCKET;
    if (!bucket || !url.includes(bucket)) return false;
    
    const key = url.split(`${bucket}/`)[1];
    if (!key) return false;
    
    await s3.send(
      new DeleteObjectCommand({
        Bucket: bucket,
        Key: key,
      })
    );
    return true;
  } catch (error) {
    console.error('Error deleting from S3:', error);
    return false;
  }
}

// Helper function to upload file to S3
async function uploadToS3(file, folder) {
  try {
    const fileName = file.name || 'profile.jpg';
    const s3Key = generateS3Key(folder, fileName);
    const buffer = Buffer.from(await file.arrayBuffer());
    const bucket = process.env.AWS_BUCKET;

    if (!bucket) {
      throw new Error('AWS bucket not configured');
    }

    await s3.send(
      new PutObjectCommand({
        Bucket: bucket,
        Key: s3Key,
        Body: buffer,
        ContentType: file.type || 'image/jpeg',
        ACL: 'public-read',
      })
    );

    const region = process.env.AWS_DEFAULT_REGION || 'us-east-1';
    return `https://${bucket}.s3.${region}.amazonaws.com/${s3Key}`;
  } catch (uploadError) {
    console.error('File upload error:', uploadError);
    throw uploadError;
  }
}

// Helper function to calculate profile completeness
function calculateProfileCompleteness(data) {
  let filledFields = 0;
  let totalFields = 0;

  const requiredFields = [
    { field: 'first_name', weight: 5 },
    { field: 'last_name', weight: 5 },
    { field: 'designation', weight: 5 },
    { field: 'email', weight: 5 },
    { field: 'phone', weight: 5 },
    { field: 'company_name', weight: 5 },
    { field: 'address', weight: 5 },
    { field: 'number_of_employees', weight: 5 },
    { field: 'nationality', weight: 5 },
    { field: 'state', weight: 5 },
    { field: 'city', weight: 5 },
    { field: 'target_countries', weight: 10, isArray: true },
    { field: 'services', weight: 10, isArray: true },
    { field: 'specialization', weight: 10, isArray: true },
    { field: 'languages', weight: 10, isArray: true },
  ];

  requiredFields.forEach(({ field, weight, isArray }) => {
    totalFields += weight;
    
    if (isArray) {
      let arrayData;
      try {
        arrayData = typeof data[field] === 'string' ? JSON.parse(data[field]) : data[field];
      } catch {
        arrayData = [];
      }
      
      if (Array.isArray(arrayData) && arrayData.length > 0) {
        filledFields += weight;
      }
    } else {
      if (data[field] && data[field].toString().trim() !== '') {
        filledFields += weight;
      }
    }
  });

  const bonusFields = [
    'consultation_fee',
    'response_time',
    'availability',
    'comment'
  ];
  
  const bonusWeight = 1.25;
  bonusFields.forEach(field => {
    totalFields += bonusWeight;
    if (data[field]) {
      if (Array.isArray(data[field])) {
        if (data[field].length > 0) filledFields += bonusWeight;
      } else if (typeof data[field] === 'boolean') {
        filledFields += bonusWeight;
      } else if (data[field].toString().trim() !== '') {
        filledFields += bonusWeight;
      }
    }
  });

  const percentage = totalFields > 0 ? Math.round((filledFields / totalFields) * 100) : 0;
  return Math.min(100, percentage);
}

// Function to send data to external CRM API
async function sendToCRMAPI(consultantData, userId, consultantRecordId) {
  try {
    console.log('=== SEND TO CRM API FUNCTION ===');
    console.log('User ID:', userId);
    console.log('Consultant Record ID:', consultantRecordId);
    console.log('CRM API Key exists:', !!process.env.CRM_API_KEY);
    
    const crmUrl = `https://crm-universitiespage.com/reactapis/api/website_consultant_crm/update/${consultantRecordId}`;
    
    console.log('CRM URL:', crmUrl);
    console.log('Request payload:', JSON.stringify(consultantData, null, 2));
    
    const response = await fetch(crmUrl, {
      method: "POST",
      body: JSON.stringify(consultantData),
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `${process.env.CRM_API_KEY}`,
      },
    });

    console.log('Response status:', response.status);
    console.log('Response status text:', response.statusText);
    
    const responseText = await response.text();
    console.log('Raw response:', responseText);
    
    let data;
    try {
      data = JSON.parse(responseText);
      console.log('Parsed response:', JSON.stringify(data, null, 2));
    } catch (parseError) {
      console.error('Failed to parse response as JSON:', parseError);
      data = { error: 'Invalid JSON response', raw: responseText.substring(0, 200) };
    }

    if (data?.status_code !== 200) {
      console.error('CRM API update failed. Response:', data);
      throw new Error(`Failed to update consultant in CRM: ${data?.message || data?.error || 'Unknown error'}`);
    }

    console.log('CRM API call successful');
    return data;
  } catch (error) {
    console.error('CRM API ERROR:', error);
    throw error;
  }
}

// POST - Get consultant profile
export async function POST(request) {
  try {
    const token = request.headers.get('cookie')?.split('university-token=')[1]?.split(';')[0] || null;
    const decoded = await verifyAuthentication(token);

    if (decoded instanceof NextResponse || !decoded || decoded.role !== "consultant") {
      return NextResponse.json(
        {
          success: false,
          message: "You are not authorized to perform this action"
        },
        { status: 401 }
      );
    }

    const [getUser] = await pool.query<RowDataPacket[]>(
      `SELECT 
        u.*,
        c.profile_picture,
        c.nationality,
        c.city,
        c.state,
        c.company_name,
        c.designation,
        c.phone,
        c.address,
        c.employeeno AS number_of_employees,
        c.target_countries,
        c.services,
        c.specialization,
        c.languages,
        c.consultation_fee,
        c.consultation_currency,
        c.response_time,
        c.digital_only,
        c.availability,
        c.comment,
        c.seo_title,
        c.seo_description,
        c.seo_keywords,
        c.seo_slug,
        c.average_rating,
        c.rating_count,
        c.active_profile,
        c.profile_status,
        c.profile_completed_date,
        c.approval_requested_date,
        c.approved_date,
        c.rejection_reason
      FROM users u
      LEFT JOIN consultants c ON u.id = c.user_id
      WHERE u.id = ? AND u.user_type = 'consultant'`, 
      [decoded.id]
    );

    if (getUser.length === 0) {
      return NextResponse.json(
        {
          success: false,
          message: "Consultant profile not found"
        },
        { status: 404 }
      );
    }

    const userData = getUser[0];
    
    const completeness = calculateProfileCompleteness(userData);
    
    const formattedData = {
      first_name: userData.first_name || '',
      last_name: userData.last_name || '',
      designation: userData.designation || '',
      email: userData.email || '',
      phone: userData.phone || '',
      company_name: userData.company_name || '',
      number_of_employees: userData.number_of_employees || '',
      address: userData.address || '',
      nationality: userData.nationality || '',
      state: userData.state || '',
      city: userData.city || '',
      consultation_fee: userData.consultation_fee || 0,
      consultation_currency: userData.consultation_currency || 'USD',
      target_countries: userData.target_countries || '[]',
      services: userData.services || '[]',
      specialization: userData.specialization || '[]',
      languages: userData.languages || '[]',
      response_time: userData.response_time || 'normal',
      digital_only: userData.digital_only || 0,
      availability: userData.availability || 'normal',
      comment: userData.comment || '',
      seo_title: userData.seo_title || '',
      seo_description: userData.seo_description || '',
      seo_keywords: userData.seo_keywords || '',
      seo_slug: userData.seo_slug || '',
      profile_picture: userData.profile_picture || null,
      average_rating: userData.average_rating || 0,
      rating_count: userData.rating_count || 0,
      active_profile: userData.active_profile || '0',
      profile_status: userData.profile_status || 'incomplete',
      profile_completed_date: userData.profile_completed_date || null,
      approval_requested_date: userData.approval_requested_date || null,
      approved_date: userData.approved_date || null,
      rejection_reason: userData.rejection_reason || null,
      completeness_percentage: completeness,
      is_eligible_for_approval: completeness >= 100,
      is_approved: userData.active_profile === '1',
      is_pending: userData.profile_status === 'pending'
    };

    return NextResponse.json(
      {
        success: true,
        message: "Profile retrieved successfully",
        data: formattedData
      },
      { status: 200 }
    );

  } catch (err) {
    console.error('GET profile error:', err);
    return NextResponse.json(
      { 
        success: false, 
        message: "Internal server error" 
      },
      { status: 500 }
    );
  }
}

// PUT - Update consultant profile (SEO fields auto-generated)
export async function PUT(request) {
  const connection = await pool.getConnection();
  
  try {
    const token = request.headers.get('cookie')?.split('university-token=')[1]?.split(';')[0] || null;
    const decoded = await verifyAuthentication(token);

    if (decoded instanceof NextResponse || !decoded || decoded.role !== "consultant") {
      return NextResponse.json(
        {
          success: false,
          message: "You are not authorized to perform this action"
        },
        { status: 401 }
      );
    }

    const [verifyConsultant] = await pool.query<RowDataPacket[]>(
      "SELECT * FROM users WHERE id = ? AND user_type = 'consultant'", 
      [decoded.id]
    );

    if (verifyConsultant.length === 0) {
      return NextResponse.json(
        {
          success: false,
          message: "Consultant not found"
        },
        { status: 404 }
      );
    }

    const contentType = request.headers.get('content-type') || '';
    let body: ConsultantProfileBody = {};
    let profilePictureUrl = null;
    let deleteOldPicture = false;

    if (contentType.includes('multipart/form-data')) {
      const formData = await request.formData();
      
      const file = formData.get('profile_picture') as File;
      const deleteOld = formData.get('delete_old_picture');
      
      if (file && file.size > 0) {
        try {
          profilePictureUrl = await uploadToS3(file, 'consultant-profiles');
          
          const [oldData] = await connection.query<RowDataPacket[]>(
            "SELECT profile_picture FROM consultants WHERE user_id = ?",
            [decoded.id]
          );
          
          if (oldData.length > 0 && oldData[0].profile_picture) {
            deleteOldPicture = true;
          }
        } catch (uploadError) {
          console.error('Profile picture upload error:', uploadError);
          return NextResponse.json(
            { 
              success: false, 
              message: "Failed to upload profile picture" 
            },
            { status: 500 }
          );
        }
      } else if (deleteOld === 'true') {
        const [oldData] = await connection.query<RowDataPacket[]>(
          "SELECT profile_picture FROM consultants WHERE user_id = ?",
          [decoded.id]
        );
        
        if (oldData.length > 0 && oldData[0].profile_picture) {
          await deleteFromS3(oldData[0].profile_picture);
          profilePictureUrl = null;
        }
      }
      
      body = {
        first_name: formData.get('first_name'),
        last_name: formData.get('last_name'),
        designation: formData.get('designation'),
        email: formData.get('email'),
        phone: formData.get('phone'),
        company_name: formData.get('company_name'),
        number_of_employees: formData.get('number_of_employees'),
        address: formData.get('address'),
        nationality: formData.get('nationality'),
        state: formData.get('state'),
        city: formData.get('city'),
        consultation_fee: formData.get('consultation_fee') ? parseFloat(formData.get('consultation_fee')) : 0,
        consultation_currency: formData.get('consultation_currency') || 'USD',
        target_countries: formData.get('target_countries'),
        services: formData.get('services'),
        specialization: formData.get('specialization'),
        languages: formData.get('languages'),
        response_time: formData.get('response_time') || 'normal',
        digital_only: formData.get('digital_only') === 'true' || formData.get('digital_only') === '1',
        availability: formData.get('availability') || 'normal',
        comment: formData.get('comment'),
        request_approval: formData.get('request_approval') === 'true'
      };
    } else {
      body = await request.json();
    }

    const requestApproval =
      body.request_approval === true ||
      body.request_approval === 'true';

    const {
      first_name,
      last_name,
      designation,
      email,
      phone,
      company_name,
      number_of_employees,
      address,
      nationality,
      state,
      city,
      consultation_fee = 0,
      consultation_currency = 'USD',
      target_countries,
      services,
      specialization,
      languages,
      response_time = 'normal',
      digital_only = false,
      availability = 'normal',
      comment = ''
    } = body;

    await connection.beginTransaction();

    const [userUpdate] = await connection.query<ResultSetHeader>(
      `UPDATE users 
       SET first_name = ?, last_name = ?, email = ?, phone = ?, updated_at = NOW()
       WHERE id = ? AND user_type = 'consultant'`,
      [first_name, last_name, email, phone, decoded.id]
    );

    if (userUpdate.affectedRows === 0) {
      throw new Error("Failed to update user profile");
    }

    const full_name = `${first_name} ${last_name}`;

    const [checkConsultant] = await connection.query<RowDataPacket[]>(
      "SELECT * FROM consultants WHERE user_id = ?",
      [decoded.id]
    );

    const profileData = {
      first_name,
      last_name,
      designation,
      email,
      phone,
      company_name,
      address,
      number_of_employees,
      nationality,
      state,
      city,
      target_countries: target_countries || '[]',
      services: services || '[]',
      specialization: specialization || '[]',
      languages: languages || '[]',
      consultation_fee,
      response_time,
      availability,
      comment
    };
    
    const completeness = calculateProfileCompleteness(profileData);
    const isComplete = completeness >= 100;

    let profile_status = 'incomplete';
    let active_profile = '0';
    let profile_completed_date = null;
    let approval_requested_date = null;

    // Check if there are actual changes
    let hasChanges = false;
    if (checkConsultant.length > 0) {
      const existingData = checkConsultant[0];
      hasChanges = (
        existingData.first_name !== first_name ||
        existingData.last_name !== last_name ||
        existingData.designation !== designation ||
        existingData.email !== email ||
        existingData.phone !== phone ||
        existingData.company_name !== company_name ||
        existingData.employeeno !== number_of_employees ||
        existingData.address !== address ||
        existingData.nationality !== nationality ||
        existingData.state !== state ||
        existingData.city !== city ||
        existingData.consultation_fee !== consultation_fee ||
        existingData.consultation_currency !== consultation_currency ||
        existingData.target_countries !== (target_countries || '[]') ||
        existingData.services !== (services || '[]') ||
        existingData.specialization !== (specialization || '[]') ||
        existingData.languages !== (languages || '[]') ||
        existingData.response_time !== response_time ||
        existingData.digital_only !== (digital_only ? 1 : 0) ||
        existingData.availability !== availability ||
        existingData.comment !== comment
      );
    }

    const [currentStatus] = await connection.query<RowDataPacket[]>(
      "SELECT profile_status, active_profile FROM consultants WHERE user_id = ?",
      [decoded.id]
    );

    const currentProfileStatus = currentStatus.length > 0 ? currentStatus[0].profile_status : 'incomplete';
    const isCurrentlyApproved = currentStatus.length > 0 ? currentStatus[0].active_profile === '1' : false;

    if (isComplete) {
      // Set completion date if this is the first time completing
      if (currentProfileStatus === 'incomplete') {
        profile_completed_date = new Date().toISOString().slice(0, 19).replace('T', ' ');
      }
      
      // If profile is already approved and user is making changes
      if (isCurrentlyApproved && hasChanges) {
        // Revert to pending for re-approval
        profile_status = 'pending';
        active_profile = '0'; // Deactivate until re-approved
        approval_requested_date = new Date().toISOString().slice(0, 19).replace('T', ' ');
      }
      // If user requests approval and not already pending or approved
      else if (requestApproval && currentProfileStatus !== 'pending' && currentProfileStatus !== 'approved') {
        profile_status = 'pending';
        active_profile = '0';
        approval_requested_date = new Date().toISOString().slice(0, 19).replace('T', ' ');
      }
      // If already pending
      else if (currentProfileStatus === 'pending') {
        profile_status = 'pending';
        active_profile = '0';
      }
      // If already approved and no changes
      else if (currentProfileStatus === 'approved' && !hasChanges) {
        profile_status = 'approved';
        active_profile = '1';
      }
      // If complete but not requesting approval
      else {
        profile_status = 'complete';
        active_profile = '0';
      }
    } else {
      // If incomplete
      if (currentProfileStatus === 'pending') {
        profile_status = 'pending';
        active_profile = '0';
      } else if (currentProfileStatus === 'approved') {
        // If approved user makes profile incomplete, revert to pending
        profile_status = 'pending';
        active_profile = '0';
      } else {
        profile_status = 'incomplete';
        active_profile = '0';
      }
    }

    // AUTO-GENERATE SEO FIELDS
    const seo_title = generateSEOTitle(first_name, last_name, designation, company_name, specialization);
    const seo_description = generateSEODescription(first_name, last_name, designation, company_name, services, languages, target_countries);
    const seo_keywords = generateSEOKeywords(first_name, last_name, designation, company_name, services, specialization, languages, target_countries);
    
    // Generate SEO slug
    let seo_slug = '';
    if (first_name || last_name || company_name) {
      let baseSlug = '';
      if (first_name && last_name) {
        baseSlug = `${first_name.toLowerCase()}-${last_name.toLowerCase()}`;
        if (company_name) {
          baseSlug += `-${company_name.toLowerCase()}`;
        }
      } else if (first_name) {
        baseSlug = first_name.toLowerCase();
        if (company_name) {
          baseSlug += `-${company_name.toLowerCase()}`;
        }
      } else if (company_name) {
        baseSlug = company_name.toLowerCase();
      }
      
      baseSlug = createSEOSlug(baseSlug);
      
      // Check if slug already exists for other consultants
      const [existingSlug] = await connection.query<RowDataPacket[]>(
        "SELECT id FROM consultants WHERE seo_slug = ? AND user_id != ?",
        [baseSlug, decoded.id]
      );
      
      // If slug exists, generate unique slug
      if (existingSlug.length > 0) {
        seo_slug = generateUniqueSlug(baseSlug);
      } else {
        seo_slug = baseSlug;
      }
    }

    let profilePictureUpdate = '';
    let profilePictureParams = [];
    
    if (profilePictureUrl !== null) {
      profilePictureUpdate = 'profile_picture = ?, ';
      profilePictureParams = [profilePictureUrl];
      
      if (deleteOldPicture && checkConsultant.length > 0 && checkConsultant[0].profile_picture) {
        await deleteFromS3(checkConsultant[0].profile_picture);
      }
    }

    let consultantRecordId;

    if (checkConsultant.length === 0) {
      const [createConsultant] = await connection.query<ResultSetHeader>(
        `INSERT INTO consultants (
          user_id, name, profile_picture, nationality, state, city, company_name, 
          designation, employeeno, address, target_countries, services,
          specialization, languages, consultation_fee, response_time, consultation_currency,
          digital_only, availability, comment, 
          seo_title, seo_description, seo_keywords, seo_slug,
          active_profile, profile_status, profile_completed_date,
          approval_requested_date, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [
          decoded.id, full_name, profilePictureUrl, nationality, state, city, company_name,
          designation, number_of_employees, address, 
          target_countries || '[]', services || '[]', specialization || '[]', 
          languages || '[]', consultation_fee || 0, consultation_currency || 'USD', response_time || 'normal',
          digital_only ? 1 : 0, availability || 'normal', comment || '',
          seo_title || '', seo_description || '', seo_keywords || '', seo_slug,
          active_profile, profile_status, profile_completed_date,
          approval_requested_date
        ]
      );

      if (createConsultant.affectedRows === 0) {
        throw new Error("Failed to create consultant profile");
      }
      consultantRecordId = createConsultant.insertId;
    } else {
      // Get the consultant record ID (consultants.id, not user_id)
      consultantRecordId = checkConsultant[0].user_id;
      
      const updateQuery = `
        UPDATE consultants 
         SET name = ?, ${profilePictureUpdate}
             nationality = ?, state = ?, city = ?, 
             company_name = ?, designation = ?, employeeno = ?, address = ?,
             target_countries = ?, services = ?, specialization = ?, 
             languages = ?, consultation_fee = ?, consultation_currency = ?, response_time = ?, 
             digital_only = ?, availability = ?, comment = ?,
             seo_title = ?, seo_description = ?, seo_keywords = ?, seo_slug = ?,
             active_profile = ?, profile_status = ?, 
             profile_completed_date = IF(? IS NOT NULL, ?, profile_completed_date),
             approval_requested_date = IF(? IS NOT NULL, ?, approval_requested_date),
             updated_at = NOW()
         WHERE user_id = ?`;
      
      const params = [
        full_name,
        ...profilePictureParams,
        nationality, state, city, company_name,
        designation, number_of_employees, address,
        target_countries || '[]', services || '[]', specialization || '[]',
        languages || '[]', consultation_fee || 0, consultation_currency || 'USD', response_time || 'normal',
        digital_only ? 1 : 0, availability || 'normal', comment || '',
        seo_title || '', seo_description || '', seo_keywords || '', seo_slug,
        active_profile, profile_status,
        profile_completed_date, profile_completed_date,
        approval_requested_date, approval_requested_date,
        decoded.id
      ];

      const [consultantUpdate] = await connection.query<ResultSetHeader>(
        updateQuery,
        params
      );

      if (consultantUpdate.affectedRows === 0) {
        throw new Error("Failed to update consultant profile");
      }
    }

    // Commit transaction first
    await connection.commit();

    // Prepare data for CRM API
    const consultantDataForCRM = {
      name: full_name,
      company_name: company_name,
      nationality: nationality,
      state: state,
      city: city,
      address: address,
      designation: designation,
      email: email,
      phone: phone,
      comment: comment,
      employeeno: number_of_employees,
      consultant_id: decoded.id,
      consultation_fee: consultation_fee || 0,
      consultation_currency: consultation_currency || 'USD',
      target_countries: target_countries || '[]',
      services: services || '[]',
      specialization: specialization || '[]',
      languages: languages || '[]',
      response_time: response_time || 'normal',
      digital_only: digital_only ? 1 : 0,
      availability: availability || 'normal',
      seo_title: seo_title || '',
      seo_description: seo_description || '',
      seo_keywords: seo_keywords || '',
      seo_slug: seo_slug || '',
      profile_status: profile_status,
      active_profile: active_profile,
      profile_completed_date: profile_completed_date,
      approval_requested_date: approval_requested_date,
      profile_picture: profilePictureUrl,
      updated_at: new Date().toISOString(),
      requires_reapproval: isCurrentlyApproved && hasChanges
    };

    // Send to CRM API (don't rollback if this fails, just log error)
    try {
      console.log('=== CRM API DEBUG ===');
      console.log('Consultant Record ID to use in URL:', consultantRecordId);
      console.log('CRM Data:', JSON.stringify(consultantDataForCRM, null, 2));
      
      await sendToCRMAPI(consultantDataForCRM, decoded.id, consultantRecordId);
      console.log('CRM API update successful');
    } catch (crmError) {
      console.error('CRM API update failed (non-critical):', crmError);
    }

    let message = "Profile updated successfully";
    if (profilePictureUrl !== null) {
      message = "Profile picture updated successfully";
      if (isComplete && requestApproval && currentProfileStatus !== 'pending' && currentProfileStatus !== 'approved') {
        message = "Profile picture updated and profile submitted for approval!";
      }
    } else if (isComplete && requestApproval && currentProfileStatus !== 'pending' && currentProfileStatus !== 'approved') {
      message = "Profile submitted for approval! Our team will review your profile within 24-48 hours. You'll be notified once approved.";
    } else if (isComplete && !requestApproval && currentProfileStatus !== 'complete') {
      message = "Profile is now complete! You can now submit for approval to appear in search results.";
    } else if (isCurrentlyApproved && hasChanges) {
      message = "Profile updated successfully! Your changes require re-approval. Your profile is now pending admin review.";
    }

    return NextResponse.json(
      { 
        success: true, 
        message: message,
        data: {
          completeness_percentage: completeness,
          is_eligible_for_approval: isComplete,
          profile_status: profile_status,
          profile_picture: profilePictureUrl,
          seo_slug: seo_slug,
          seo_title: seo_title,
          seo_description: seo_description,
          seo_keywords: seo_keywords,
          is_pending: profile_status === 'pending',
          is_approved: profile_status === 'approved'
        }
      },
      { status: 200 }
    );

  } catch (err) {
    try {
      await connection.rollback();
    } catch (rollbackErr) {
      console.error("Rollback failed:", rollbackErr);
    }

    console.error('UPDATE profile error:', err);

    if (err?.issues) {
      const fieldErrors = {};
      for (const issue of err.issues) {
        const key = (issue.path?.[0]) ?? "_";
        fieldErrors[key] = fieldErrors[key] || [];
        fieldErrors[key].push(issue.message);
      }
      return NextResponse.json(
        { 
          success: false, 
          message: fieldErrors 
        },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        message: err.message || "Internal server error" 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}