import pool from '../../../../lib/db/db';
import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket, ResultSetHeader } from 'mysql2';

interface Consultant {
  id: number;
  user_id: number;
  name: string;
  company_name: string;
  state: string;
  city: string;
  address: string;
  email: string;
  phone: string;
  average_rating: number;
  rating_count: number;
  consultation_fee: number;
  response_time: string;
  digital_only: boolean;
  target_countries: string[];
  services: string[];
  languages: string[];
  specialization: string[];
  availability: string;
  active: string;
  comment: string;
  designation: string;
  employeeno: string;
  nationality: string;
  created_at: string;
  updated_at: string;
}

interface User {
  id: number;
  first_name: string;
  last_name: string;
  phone: string;
  email: string;
  user_type: string;
  is_active: boolean;
  created_at: string;
  updated_at: string;
  country?: string;
  city?: string;
  consultant?: Consultant;
  featured: string;
  premium_featured: string | null; // New field
}

// Helper function to safely parse JSON fields
function safeParseJSON(field: any): any[] {
  if (!field || field === 'NULL' || field === 'null' || field === '' || field === '[]') {
    return [];
  }
  
  if (Array.isArray(field)) {
    return field;
  }
  
  if (typeof field === 'string') {
    const trimmed = field.trim();
    
    if (!trimmed || trimmed === '[]' || trimmed === '""') {
      return [];
    }
    
    if (trimmed.startsWith('[') && trimmed.endsWith(']')) {
      try {
        return JSON.parse(trimmed);
      } catch (e) {
        // Try to extract values from malformed JSON
        const matches = trimmed.match(/"([^"]+)"/g);
        return matches ? matches.map(m => m.replace(/"/g, '')) : [];
      }
    }
    
    // Handle comma-separated values
    if (trimmed.includes(',')) {
      return trimmed.split(',').map(item => item.trim()).filter(item => item.length > 0);
    }
    
    // Single value
    return [trimmed];
  }
  
  return [];
}

// -------------------- GET --------------------
export async function GET(req: NextRequest) {
  try {
    console.log('🔍 API: Fetching consultant users');
    
    const { searchParams } = new URL(req.url);

    const pageParam = parseInt(searchParams.get('page') || '1');
    const limitParam = parseInt(searchParams.get('limit') || '15');
    const search = searchParams.get('search')?.trim() || '';
    const status = searchParams.get('status') || '';
    const city = searchParams.get('city')?.trim() || '';
    const country = searchParams.get('country')?.trim() || '';
    const startDate = searchParams.get('start_date') || '';
    const endDate = searchParams.get('end_date') || '';
    const premiumFeatured = searchParams.get('premium_featured')?.trim() || ''; // New parameter

    const page = Number.isNaN(pageParam) || pageParam < 1 ? 1 : pageParam;
    const limit = Number.isNaN(limitParam) || limitParam < 1 ? 15 : limitParam;
    const skip = (page - 1) * limit;

    console.log('📊 API Params:', {
      page, limit, search, status, city, country, startDate, endDate, premiumFeatured
    });

    // ---------------- Build WHERE conditions ----------------
    let whereConditions: string[] = ["u.user_type = 'consultant'"];
    const params: any[] = [];

    if (search) {
      whereConditions.push(`(
        u.first_name LIKE ? 
        OR u.last_name LIKE ? 
        OR u.email LIKE ? 
        OR u.phone LIKE ?
        OR c.company_name LIKE ?
        OR c.city LIKE ?
        OR c.name LIKE ?
      )`);
      params.push(
        `%${search}%`, 
        `%${search}%`, 
        `%${search}%`,
        `%${search}%`,
        `%${search}%`,
        `%${search}%`,
        `%${search}%`
      );
    }

    if (status) {
      whereConditions.push(`u.is_active = ?`);
      params.push(status === 'true' ? 1 : 0);
    }

    if (city) {
      whereConditions.push(`(c.city LIKE ? OR u.city LIKE ?)`);
      params.push(`%${city}%`, `%${city}%`);
    }

    if (country) {
      whereConditions.push(`(c.nationality LIKE ? OR u.country LIKE ?)`);
      params.push(`%${country}%`, `%${country}%`);
    }

    if (premiumFeatured) {
      if (premiumFeatured === '0') {
        whereConditions.push(`(c.premium_featured IS NULL OR c.premium_featured = '0' OR c.premium_featured = '')`);
      } else if (premiumFeatured === 'custom') {
        whereConditions.push(`(c.premium_featured IS NOT NULL AND c.premium_featured != '0' AND c.premium_featured != '')`);
      } else {
        whereConditions.push(`c.premium_featured = ?`);
        params.push(premiumFeatured);
      }
    }

    if (startDate && endDate) {
      whereConditions.push(`DATE(u.created_at) BETWEEN ? AND ?`);
      params.push(startDate, endDate);
    } else if (startDate) {
      whereConditions.push(`DATE(u.created_at) >= ?`);
      params.push(startDate);
    } else if (endDate) {
      whereConditions.push(`DATE(u.created_at) <= ?`);
      params.push(endDate);
    }

    const whereClause = whereConditions.length > 0 
      ? `WHERE ${whereConditions.join(' AND ')}` 
      : '';

    console.log('📝 SQL WHERE clause:', whereClause);
    console.log('🔢 SQL Parameters:', params);

    // ---------------- Count total ----------------
    console.log('🔢 Counting total users...');
    
    let countQuery = `
      SELECT COUNT(DISTINCT u.id) as totalItems 
      FROM users u
      LEFT JOIN consultants c ON u.id = c.user_id
      ${whereClause}
    `;
    
    console.log('📝 Count Query:', countQuery);
    
    const [countResult] = await pool.execute<RowDataPacket[]>(countQuery, params);
    const totalItems = countResult[0]?.totalItems || 0;
    
    console.log('✅ Total items found:', totalItems);

    // ---------------- Fetch data with consultant details ----------------
    if (totalItems === 0) {
      console.log('📊 No users found, returning empty array');
      return NextResponse.json({
        success: true,
        users: [],
        meta: {
          page,
          limit,
          totalItems: 0,
          totalPages: 0,
          startIndex: 0,
          endIndex: 0,
        },
      });
    }

    console.log('📥 Fetching users with details...');
    
    let dataQuery = `
      SELECT 
        u.id,
        u.first_name,
        u.last_name,
        u.phone,
        u.email,
        u.user_type,
        u.is_active,
        c.featured,
        c.premium_featured, -- NEW: Select premium_featured field
        u.created_at,
        u.updated_at,
        u.country as user_country,
        u.city as user_city,
        c.id as consultant_id,
        c.user_id,
        c.name as consultant_name,
        c.company_name,
        c.city as consultant_city,
        c.state as consultant_state,
        c.address,
        c.email as consultant_email,
        c.phone as consultant_phone,
        c.average_rating,
        c.rating_count,
        c.consultation_fee,
        c.response_time,
        c.digital_only,
        c.target_countries,
        c.services,
        c.languages,
        c.specialization,
        c.availability,
        c.active,
        c.comment,
        c.designation,
        c.employeeno,
        c.nationality,
        c.created_at as consultant_created_at,
        c.updated_at as consultant_updated_at
      FROM users u
      LEFT JOIN consultants c ON u.id = c.user_id
      ${whereClause}
      ORDER BY 
        CASE 
          WHEN c.premium_featured IS NULL OR c.premium_featured = '' OR c.premium_featured = '0' THEN 999999
          ELSE CAST(c.premium_featured AS UNSIGNED)
        END,
        u.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    console.log('📝 Data Query:', dataQuery);
    
    const queryParams = [...params, limit, skip];
    console.log('🔢 Query Parameters:', queryParams);

    const [users] = await pool.execute<RowDataPacket[]>(dataQuery, queryParams);
    
    console.log('✅ Raw users fetched:', users.length);

    // Parse and format the response
    const formattedUsers: User[] = users.map((row) => {
      // Build consultant object if consultant data exists
      let consultant: Consultant | undefined = undefined;
      
      if (row.consultant_id) {
        try {
          consultant = {
            id: row.consultant_id,
            user_id: row.user_id,
            name: row.consultant_name || '',
            company_name: row.company_name || '',
            state: row.consultant_state || '',
            city: row.consultant_city || row.user_city || '',
            address: row.address || '',
            email: row.consultant_email || row.email || '',
            phone: row.consultant_phone || row.phone || '',
            average_rating: parseFloat(row.average_rating) || 0,
            rating_count: parseInt(row.rating_count) || 0,
            consultation_fee: parseFloat(row.consultation_fee) || 0,
            response_time: row.response_time || 'normal',
            digital_only: Boolean(row.digital_only),
            target_countries: safeParseJSON(row.target_countries),
            services: safeParseJSON(row.services),
            languages: safeParseJSON(row.languages),
            specialization: safeParseJSON(row.specialization),
            availability: row.availability || 'normal',
            active: row.active || '1',
            comment: row.comment || '',
            designation: row.designation || '',
            employeeno: row.employeeno || '',
            nationality: row.nationality || row.user_country || '',
            created_at: row.consultant_created_at || null,
            updated_at: row.consultant_updated_at || null,
          };
        } catch (error) {
          console.error('❌ Error parsing consultant data for user', row.id, error);
        }
      }

      return {
        id: row.id,
        first_name: row.first_name || '',
        last_name: row.last_name || '',
        name: `${row.first_name || ''} ${row.last_name || ''}`.trim(),
        phone: row.phone || '',
        email: row.email || '',
        user_type: row.user_type || 'consultant',
        is_active: Boolean(row.is_active),
        featured: row.featured || '0',
        premium_featured: row.premium_featured || null, // NEW: Include premium_featured
        country: row.user_country || '',
        state: row.consultant_state || '',
        city: row.user_city || '',
        created_at: row.created_at || null,
        updated_at: row.updated_at || null,
        consultant
      };
    });

    console.log('✅ Formatted users:', formattedUsers.length);

    const totalPages = Math.max(1, Math.ceil(totalItems / limit));

    const response = {
      success: true,
      users: formattedUsers,
      meta: {
        page,
        limit,
        totalItems,
        totalPages,
        startIndex: skip + 1,
        endIndex: skip + formattedUsers.length,
      },
    };

    console.log('✅ API Response ready, total users:', formattedUsers.length);
    
    return NextResponse.json(response);

  } catch (error: any) {
    console.error('❌ Error fetching consultant users:', error);
    console.error('Error stack:', error.stack);
    
    // Check for specific database errors
    if (error.code === 'ER_NO_SUCH_TABLE') {
      return NextResponse.json(
        { 
          success: false, 
          message: 'Database table does not exist. Please check database schema.' 
        },
        { status: 500 }
      );
    }
    
    if (error.code === 'ER_BAD_FIELD_ERROR') {
      console.error('🔍 Field error details:', error.sqlMessage);
      return NextResponse.json(
        { 
          success: false, 
          message: `Database field error: ${error.sqlMessage}` 
        },
        { status: 500 }
      );
    }
    
    return NextResponse.json(
      { 
        success: false, 
        message: `Server error: ${error.message || 'Unknown error'}` 
      },
      { status: 500 }
    );
  }
}

// -------------------- PUT --------------------
export async function PUT(req: NextRequest) {
  try {
    const body = await req.json();
    const { id, consultant_id, is_active, featured, premium_featured } = body;

    console.log('🔄 API: Updating record', { id, consultant_id, is_active, featured, premium_featured });

    // Validate input
    if (!id && !consultant_id) {
      return NextResponse.json(
        { success: false, message: 'id or consultant_id is required' },
        { status: 400 }
      );
    }

    let updateQuery = '';
    let params: any[] = [];
    let targetId = id;
    let targetTable = 'users';
    
    // Check which field we're updating
    if (typeof is_active !== 'undefined') {
      updateQuery = `UPDATE users SET is_active = ? WHERE id = ?`;
      params = [is_active ? 1 : 0, targetId];
      targetTable = 'users';
    } else if (typeof featured !== 'undefined') {
      // For featured, we need to update the consultants table
      // Use consultant_id if provided, otherwise we need to find it from user_id
      if (consultant_id) {
        targetId = consultant_id;
        updateQuery = `UPDATE consultants SET featured = ? WHERE id = ?`;
        params = [featured, targetId];
        targetTable = 'consultants';
      } else if (id) {
        // If only user id is provided, find the consultant_id first
        const [consultantRows] = await pool.execute<RowDataPacket[]>(
          `SELECT id FROM consultants WHERE user_id = ?`,
          [id]
        );
        
        if (consultantRows.length === 0) {
          return NextResponse.json(
            { success: false, message: 'Consultant profile not found for this user' },
            { status: 404 }
          );
        }
        
        targetId = consultantRows[0].id;
        updateQuery = `UPDATE consultants SET featured = ? WHERE id = ?`;
        params = [featured, targetId];
        targetTable = 'consultants';
      }
    } else if (typeof premium_featured !== 'undefined') {
      // NEW: Handle premium_featured updates
      let premiumValue = premium_featured;
      
      // Convert empty string or null to NULL
      if (premium_featured === '' || premium_featured === null || premium_featured === '0') {
        premiumValue = null;
      }
      
      if (consultant_id) {
        targetId = consultant_id;
        updateQuery = `UPDATE consultants SET premium_featured = ? WHERE id = ?`;
        params = [premiumValue, targetId];
        targetTable = 'consultants';
      } else if (id) {
        // If only user id is provided, find the consultant_id first
        const [consultantRows] = await pool.execute<RowDataPacket[]>(
          `SELECT id FROM consultants WHERE user_id = ?`,
          [id]
        );
        
        if (consultantRows.length === 0) {
          return NextResponse.json(
            { success: false, message: 'Consultant profile not found for this user' },
            { status: 404 }
          );
        }
        
        targetId = consultantRows[0].id;
        updateQuery = `UPDATE consultants SET premium_featured = ? WHERE id = ?`;
        params = [premiumValue, targetId];
        targetTable = 'consultants';
      }
    } else {
      return NextResponse.json(
        { success: false, message: 'Either is_active, featured, or premium_featured is required' },
        { status: 400 }
      );
    }

    const [result] = await pool.execute<ResultSetHeader>(updateQuery, params);

    console.log('✅ Update result:', result);

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, message: `${targetTable === 'users' ? 'User' : 'Consultant'} not found or not updated` },
        { status: 404 }
      );
    }

    // Return appropriate success message
    let successMessage = '';
    if (typeof is_active !== 'undefined') {
      successMessage = `User ${is_active ? 'activated' : 'deactivated'} successfully`;
    } else if (typeof featured !== 'undefined') {
      successMessage = `Consultant ${featured === '1' ? 'marked as featured' : 'removed from featured'} successfully`;
    } else if (typeof premium_featured !== 'undefined') {
      if (!premium_featured || premium_featured === '0') {
        successMessage = 'Premium featured position removed successfully';
      } else {
        successMessage = `Premium featured position set to ${premium_featured} successfully`;
      }
    }

    return NextResponse.json({
      success: true,
      message: successMessage,
      data: { 
        id: targetId, 
        is_active, 
        featured, 
        premium_featured,
        updated_table: targetTable 
      },
    });
  } catch (error: any) {
    console.error('❌ Error updating record:', error);
    console.error('Error stack:', error.stack);
    return NextResponse.json(
      { 
        success: false, 
        message: `Server error: ${error.message || 'Unknown error'}` 
      },
      { status: 500 }
    );
  }
}