import pool from '../../../../lib/db/db';
import { NextRequest, NextResponse } from 'next/server';
import { ResultSetHeader, RowDataPacket } from 'mysql2';
import bcrypt from 'bcryptjs';
import jwt from 'jsonwebtoken';
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';

// Configure S3 client
const s3 = new S3Client({
  region: process.env.AWS_REGION || 'eu-north-1',
  credentials: {
    accessKeyId: process.env.AWS_ACCESS_KEY_ID || '',
    secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY || '',
  },
});

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

function createSEOSlug(text: string) {
  if (!text) return '';
  return text
    .toLowerCase()
    .replace(/[^\w\s-]/g, '')
    .replace(/\s+/g, '-')
    .replace(/-+/g, '-')
    .trim();
}

function generateSEOTitle(first_name: string, last_name: string, designation: string, company_name: string, specialization: string) {
  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);
}

function generateSEODescription(first_name: string, last_name: string, designation: string, company_name: string, services: string, languages: string, target_countries: string) {
  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);
}

function generateSEOKeywords(first_name: string, last_name: string, designation: string, company_name: string, services: string, specialization: string, languages: string, target_countries: string) {
  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(', ');
}

// Calculate profile completeness
function calculateProfileCompleteness(data: any) {
  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 percentage = totalFields > 0 ? Math.round((filledFields / totalFields) * 100) : 0;
  return Math.min(100, percentage);
}

// Function to convert base64 to smaller data or just store reference
async function handleProfilePicture(profile_picture) {
  if (!profile_picture) {
    console.log('No profile picture provided');
    return null;
  }
  
  try {
    // Check if it's base64 data
    if (profile_picture.startsWith('data:image/')) {
      console.log('Processing base64 image...');
      const matches = profile_picture.match(/^data:(.+);base64,(.+)$/);
      if (!matches || matches.length !== 3) {
        console.error('Invalid base64 data format');
        return null;
      }

      const contentType = matches[1];
      const data = Buffer.from(matches[2], 'base64');
      
      console.log('Image details - Content Type:', contentType, 'Size:', data.length, 'bytes');
      
      // Generate unique filename
      const timestamp = Date.now();
      const uniqueId = Math.random().toString(36).substring(2, 9);
      const key = `consultant-profiles/${timestamp}_${uniqueId}_profile.jpg`;
      
      const bucket = process.env.AWS_BUCKET_NAME || 'unipage-website';
      const region = process.env.AWS_REGION || 'eu-north-1';
      
      console.log('S3 Upload Details:');
      console.log('- Bucket:', bucket);
      console.log('- Region:', region);
      console.log('- Key:', key);
      
      try {
        const command = new PutObjectCommand({
          Bucket: bucket,
          Key: key,
          Body: data,
          ContentType: contentType,
          // REMOVE THIS LINE: ACL: 'public-read',
        });
        
        console.log('Attempting S3 upload without ACL...');
        await s3.send(command);
        console.log('S3 upload successful');
        
        // Return the public URL (objects are public if bucket has public policy)
        const s3Url = `https://${bucket}.s3.${region}.amazonaws.com/${key}`;
        console.log('Generated S3 URL:', s3Url);
        return s3Url;
        
      } catch (s3Error) {
        console.error('S3 upload error:', s3Error.message);
        
        // Alternative: Try with different settings
        console.log('Trying alternative upload method...');
        try {
          const fallbackCommand = new PutObjectCommand({
            Bucket: bucket,
            Key: key,
            Body: data,
            ContentType: contentType,
            // Some buckets require this instead
            // ACL: 'bucket-owner-full-control',
          });
          
          await s3.send(fallbackCommand);
          console.log('Fallback S3 upload successful');
          const s3Url = `https://${bucket}.s3.${region}.amazonaws.com/${key}`;
          return s3Url;
        } catch (fallbackError) {
          console.error('Fallback upload failed:', fallbackError.message);
          
          // Store truncated base64 as last resort
          console.log('Using base64 fallback storage');
          const truncated = profile_picture.length > 50000 
            ? profile_picture.substring(0, 50000) + '...TRUNCATED...' 
            : profile_picture;
          return truncated;
        }
      }
    }
    
    // If it's already a URL, return as is
    if (profile_picture.startsWith('http')) {
      console.log('Profile picture is already a URL:', profile_picture);
      return profile_picture;
    }
    
    console.log('Profile picture is not base64 or URL, returning null');
    return null;
  } catch (error) {
    console.error('Error in handleProfilePicture:', error);
    return null;
  }
}





// Function to send data to external CRM API
async function sendToCRMAPI(consultantData: any, consultantId: number) {
  try {
    console.log('=== SENDING TO EXTERNAL CRM API ===');
    console.log('Consultant ID:', consultantId);
    console.log('CRM API Key exists:', !!process.env.CRM_API_KEY);
    
    const crmUrl = "https://crm-universitiespage.com/reactapis/api/website_consultant_crm/store";
    
    // Remove large base64 data for external API
    const crmData = { ...consultantData };
    if (crmData.profile_picture && crmData.profile_picture.length > 10000) {
      // Only send small reference or skip
      crmData.profile_picture = 'base64_image_provided';
    }
    
    console.log('CRM URL:', crmUrl);
    console.log('Sending CRM data (truncated):', JSON.stringify(crmData, null, 2).substring(0, 500) + '...');
    
    const response = await fetch(crmUrl, {
      method: "POST",
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `${process.env.CRM_API_KEY}`,
      },
      body: JSON.stringify(crmData),
    });

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

    if (data?.status_code !== 200) {
      console.error('CRM API call failed. Response:', data);
      throw new Error(`Failed to send data to 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;
  }
}

export async function POST(req: NextRequest) {
  const connection = await pool.getConnection();
  try {
    const body = await req.json();
    
    console.log('=== REGISTRATION REQUEST ===');
    console.log('Request body keys:', Object.keys(body));
    console.log('Profile picture present:', !!body.profile_picture);
    console.log('Profile picture length:', body.profile_picture?.length || 0);

    // Registration fields
    const {
      company_name,
      designation,
      number_of_employees,
      address,
      first_name,
      last_name,
      mobile_number,
      comment,
      agree,
      email,
      password,
      nationality,
      state,
      city,
      
      // Profile completion fields
      consultation_fee = 0,
      consultation_currency = 'USD',
      target_countries = '[]',
      services = '[]',
      specialization = '[]',
      languages = '[]',
      response_time = 'normal',     
      digital_only = false,       
      availability = 'normal', 
      profile_picture = null,
    } = body;

    // Validate required fields
    if (!first_name || !last_name || !email || !password || !company_name || !designation) {
      console.error('Missing required fields:', { first_name, last_name, email, company_name, designation });
      connection.release();
      return NextResponse.json(
        {
          success: false,
          message: "Missing required fields. Please fill all required fields."
        },
        { status: 400 }
      );
    }

    // Validate email format
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(email)) {
      connection.release();
      return NextResponse.json(
        {
          success: false,
          message: "Please enter a valid email address"
        },
        { status: 400 }
      );
    }

    // Email check
    const [checkEmail] = await connection.query<RowDataPacket[]>(
      "SELECT * FROM users WHERE email = ? LIMIT 1",
      [email]
    );

    if (checkEmail.length > 0) {
      connection.release();
      return NextResponse.json(
        {
          success: false,
          message: "This email is already registered. Please choose another one or login."
        },
        { status: 400 }
      );
    }

    // Start transaction
    await connection.beginTransaction();

    const hashedPassword = await bcrypt.hash(password, 10);

    // Insert into users table
    const [userResults] = await connection.query<ResultSetHeader>(
      "INSERT INTO users (first_name, last_name, email, password, user_type, is_active, phone, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())",
      [first_name, last_name, email, hashedPassword, "consultant", 1, mobile_number]
    );

    if (userResults.affectedRows === 0) {
      throw new Error("Failed to insert user into database");
    }

    const userId = userResults.insertId;
    const full_name = `${first_name} ${last_name}`;
    
    console.log('User inserted successfully. User ID:', userId);

    // Handle profile picture - convert base64 if too large
    const processedProfilePicture = await handleProfilePicture(profile_picture);

    // 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
      const [existingSlug] = await connection.query<RowDataPacket[]>(
        "SELECT id FROM consultants WHERE seo_slug = ?",
        [baseSlug]
      );
      
      // If slug exists, generate unique slug
      if (existingSlug.length > 0) {
        seo_slug = generateUniqueSlug(baseSlug);
      } else {
        seo_slug = baseSlug;
      }
    }

    // Calculate profile completeness
    const profileData = {
      first_name,
      last_name,
      designation,
      email,
      phone: mobile_number,
      company_name,
      address,
      number_of_employees,
      nationality,
      state,
      city,
      target_countries,
      services,
      specialization,
      languages
    };
    
    const completeness = calculateProfileCompleteness(profileData);
    const profile_completed_date = completeness >= 100 ? new Date().toISOString().slice(0, 19).replace('T', ' ') : null;
    const approval_requested_date = completeness >= 100 ? new Date().toISOString().slice(0, 19).replace('T', ' ') : null;

    // Insert into consultants table
 const [consultantResults] = await connection.query<ResultSetHeader>(
      `INSERT INTO consultants 
        (user_id, name, company_name, nationality, state, city, address, 
         designation, email, phone, comment, employeeno,
         consultation_fee, consultation_currency, target_countries, services,
         specialization, languages, response_time, digital_only, availability, 
         seo_title, seo_description, seo_keywords, seo_slug,
         profile_picture, active_profile, profile_status, 
         profile_completed_date, approval_requested_date, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [
        userId, 
        full_name, 
        company_name, 
        nationality, 
        state, 
        city, 
        address,
        designation, 
        email, 
        mobile_number, 
        comment || '', 
        number_of_employees,
        consultation_fee || 0, 
        consultation_currency || 'USD', 
        target_countries || '[]', 
        services || '[]',
        specialization || '[]', 
        languages || '[]', 
        response_time || 'normal',     
        digital_only ? 1 : 0,           
        availability || 'normal',      
        seo_title, 
        seo_description, 
        seo_keywords, 
        seo_slug,
        processedProfilePicture,
        '0',
        completeness >= 100 ? 'pending' : 'incomplete',
        profile_completed_date,
        approval_requested_date
      ]
    );

    if (consultantResults.affectedRows === 0) {
      throw new Error("Failed to insert consultant profile into database");
    }

    const consultantId = consultantResults.insertId;
    console.log('Consultant profile inserted successfully. Consultant ID:', consultantId);

    // Get the newly created consultant data
    const [newConsultant] = await connection.query<RowDataPacket[]>(
      `SELECT 
        u.*, 
        c.profile_status,
        c.active_profile,
        c.seo_slug,
        c.company_name,
        c.designation,
        c.phone,
        c.address
      FROM users u
      LEFT JOIN consultants c ON u.id = c.user_id
      WHERE u.id = ? 
        AND u.is_active = 1 
        AND u.user_type = 'consultant' 
      LIMIT 1`,
      [userId]
    );

    if (newConsultant.length === 0) {
      throw new Error("Failed to retrieve created consultant from database");
    }

    const user = newConsultant[0];
    console.log('User retrieved successfully:', user.id, user.email);

    // Commit transaction
    await connection.commit();
    console.log("Transaction committed successfully");

    // Prepare data for external CRM API
    const crmData = {
      name: full_name,
      company_name: company_name,
      nationality: nationality,
      state: state,
      city: city,
      user_id: userId,
      address: address,
      designation: designation,
      email: email,
      phone: mobile_number,
      comment: comment || '',
      employeeno: number_of_employees,
      consultant_id: userId,
      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: completeness >= 100 ? 'pending' : 'incomplete',
      active_profile: '0',
      profile_completed_date: profile_completed_date,
      approval_requested_date: approval_requested_date,
      profile_picture: processedProfilePicture,
      created_at: new Date().toISOString(),
      source: 'website_registration'
    };

    // // Send to external CRM API (async, don't block registration)
    try {
      console.log('Attempting to send data to external CRM API...');
      await sendToCRMAPI(crmData, consultantId);
      console.log('CRM API call successful');
    } catch (crmError) {
      console.error('CRM API call failed (non-critical):', crmError);
      // Don't throw error, just log it. Registration should succeed even if CRM fails.
    }

    // Create user data object for response
    const userData = {
      id: user.id,
      name: user.first_name,
      email: user.email,
      role: user.user_type,
      consultant_profile: {
        profile_status: user.profile_status || 'pending',
        active_profile: user.active_profile || '0',
        seo_slug: user.seo_slug || '',
        company_name: user.company_name || '',
        designation: user.designation || ''
      }
    };

    // Create JWT token
    const token = jwt.sign(
      {
        id: user.id,
        name: user.first_name,
        email: user.email,
        role: user.user_type,
        consultant_profile: userData.consultant_profile
      },
      process.env.JWT_SECRET as string,
      {
        expiresIn: "1d"
      }
    );

    console.log('JWT token created for user:', user.id);

    // Create response
    const apiResponse = NextResponse.json(
      {
        success: true,
        message: completeness >= 100 
          ? "Consultant Registered Successfully! Your profile is now pending admin approval." 
          : "Consultant Registered Successfully! Please complete your profile to submit for approval.",
        role: user.user_type,
        data: userData,
        consultant_status: {
          profile_status: user.profile_status || 'incomplete',
          active_profile: user.active_profile || '0',
          is_pending: user.profile_status === 'pending',
          is_approved: user.active_profile === '1',
          completeness_percentage: completeness
        }
      },
      { status: 200 }
    );

    // Set authentication cookies
    apiResponse.cookies.set('university-token', token, {
      httpOnly: true,
      secure: process.env.NODE_ENV === 'production',
      sameSite: 'strict',
      path: '/',
      maxAge: 24 * 60 * 60 // 1 day
    });

    apiResponse.cookies.set('user-role', user.user_type, {
      httpOnly: false,
      secure: process.env.NODE_ENV === 'production',
      sameSite: 'strict',
      path: '/',
      maxAge: 24 * 60 * 60
    });

    apiResponse.cookies.set('user-id', user.id.toString(), {
      httpOnly: false,
      secure: process.env.NODE_ENV === 'production',
      sameSite: 'strict',
      path: '/',
      maxAge: 24 * 60 * 60
    });

    console.log("Registration completed successfully for user:", user.id, user.email);
    
    return apiResponse;

  } catch (err: any) {
    // Rollback transaction if active
    try {
      await connection.rollback();
      console.log('Transaction rolled back due to error');
    } catch (rollbackErr) {
      console.error("Rollback failed:", rollbackErr);
    }

    console.error("POST /register-and-complete error:", err);
    console.error("Error stack:", err.stack);
    
    // Check for specific MySQL errors
    if (err.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { 
          success: false, 
          message: "This email is already registered. Please use a different email or login." 
        },
        { status: 400 }
      );
    }
    
    if (err.code === 'ER_DATA_TOO_LONG') {
      return NextResponse.json(
        { 
          success: false, 
          message: "Some data is too long. Please check your input and try again." 
        },
        { status: 400 }
      );
    }

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