// app/api/appapis/consultant_dashboard_stats/route.ts
import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../lib/db/db';
import { verifyAuthentication } from '../../../utils/verifyAuthentication';
import { RowDataPacket } from 'mysql2';

interface TokenInterface {
  id: number;
  userId?: number;
  email?: string;
  role?: string;
}

export async function OPTIONS() {
  return NextResponse.json({}, { status: 200 });
}

export async function GET(request: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    // Get token from Authorization header
    const authHeader = request.headers.get('authorization');
    
    if (!authHeader || !authHeader.startsWith('Bearer ')) {
      return NextResponse.json({
        success: false,
        message: "No valid authorization header found"
      }, { status: 401 });
    }

    const token = authHeader.substring(7);
    
    // Verify token
    const authResult = await verifyAuthentication(token);
    
    if (authResult instanceof NextResponse) {
      return authResult;
    }
    
    const decoded = authResult as TokenInterface;
    
    if (!decoded || !decoded.id) {
      return NextResponse.json({
        success: false,
        message: "Invalid token - no user ID found"
      }, { status: 401 });
    }

    // Check if user is a consultant
    if (decoded.role !== "consultant") {
      return NextResponse.json({
        success: false,
        message: "You are not authorized to perform this action. Consultant access required."
      }, { status: 401 });
    }

    // Get consultant profile information from users and consultants tables
    const [userInfo] = await connection.query<RowDataPacket[]>(
      `SELECT 
        u.id,
        u.first_name,
        u.last_name,
        u.email,
        u.phone,
        u.image as profile_picture,
        c.company_name,
        c.designation,
        c.availability as experience,
        c.specialization,
        c.profile_completed_date,
        c.approval_requested_date,
        c.approved_date,
        c.active_profile,
        c.profile_status,
        c.profile_picture as consultant_profile_picture,
        c.address,
        c.employeeno as number_of_employees,
        c.nationality,
        c.state,
        c.city,
        c.target_countries,
        c.services,
        c.languages,
        c.consultation_fee,
        c.response_time,
        c.digital_only,
        c.comment
      FROM users u
      LEFT JOIN consultants c ON u.id = c.user_id
      WHERE u.id = ?`,
      [decoded.id]
    );

    // Get students count for this consultant
    const [studentsResult] = await connection.query<RowDataPacket[]>(
      "SELECT COUNT(*) as count FROM cstudents WHERE consultant_id = ?",
      [decoded.id]
    );
    const studentCount = studentsResult[0]?.count || 0;

    // Get wishlist count
    const [wishlistResult] = await connection.query<RowDataPacket[]>(
      "SELECT COUNT(*) as count FROM wishlist WHERE user_id = ?",
      [decoded.id]
    );
    const wishlistCount = wishlistResult[0]?.count || 0;

    // Get UNREAD notifications count
    const [notificationsResult] = await connection.query<RowDataPacket[]>(
      "SELECT COUNT(*) as count FROM notifications WHERE user_id = ? AND is_read = 0",
      [decoded.id]
    );
    const unreadNotificationsCount = notificationsResult[0]?.count || 0;

    // Get recent activity (students added recently)
    const [recentActivity] = await connection.query<RowDataPacket[]>(
      `SELECT 
        'student_added' as type,
        created_at as date,
        CONCAT('New student: ', name) as description,
        name as student_name
      FROM cstudents 
      WHERE consultant_id = ?
      ORDER BY created_at DESC
      LIMIT 5`,
      [decoded.id]
    );

    // Get profile picture - prioritize consultant's profile picture
    let profilePicture = null;
    if (userInfo.length > 0) {
      profilePicture = userInfo[0].consultant_profile_picture || userInfo[0].profile_picture || null;
    }

    // Format consultant profile data
    const profile = userInfo.length > 0 ? {
      first_name: userInfo[0].first_name || '',
      last_name: userInfo[0].last_name || '',
      email: userInfo[0].email || '',
      phone: userInfo[0].phone || '',
      profile_picture: profilePicture,
      company_name: userInfo[0].company_name || '',
      profile_status: userInfo[0].profile_status || 'incomplete',
      active_profile: userInfo[0].active_profile || '0',
      designation: userInfo[0].designation || '',
      experience: userInfo[0].experience || '',
      specialization: userInfo[0].specialization || '',
      address: userInfo[0].address || '',
      number_of_employees: userInfo[0].number_of_employees || '',
      nationality: userInfo[0].nationality || '',
      state: userInfo[0].state || '',
      city: userInfo[0].city || '',
      consultation_fee: userInfo[0].consultation_fee || 0,
      response_time: userInfo[0].response_time || '',
      digital_only: userInfo[0].digital_only || 0,
      comment: userInfo[0].comment || '',
      profile_completion: calculateConsultantProfileCompletion(userInfo[0])+5,
      rejection_reason: userInfo[0].rejection_reason || null,
    } : null;

    return NextResponse.json({
      success: true,
      data: {
        profile,
        counts: {
          students: studentCount,
          wishlist: wishlistCount,
          notifications: unreadNotificationsCount,
        },
        recentActivity: recentActivity || []
      }
    }, { status: 200 });

  } catch (error) {
    console.error('Consultant dashboard stats error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal server error"
    }, { status: 500 });
  } finally {
    connection.release();
  }
}

// Helper function to calculate consultant profile completion
function calculateConsultantProfileCompletion(profile: any): number {
  let filledFields = 0;
  let totalFields = 0;

  // Required fields with weights (total weight = 100)
  const requiredFields = [
    // Personal Info (15%)
    { field: 'first_name', weight: 5 },
    { field: 'last_name', weight: 5 },
    { field: 'email', weight: 5 },
    
    // Phone (5%)
    { field: 'phone', weight: 5 },
    
    // Company Info (15%)
    { field: 'company_name', weight: 5 },
    { field: 'designation', weight: 5 },
    { field: 'address', weight: 5 },
    
    // Number of Employees (5%)
    { field: 'number_of_employees', weight: 5 },
    
    // Location (15%)
    { field: 'nationality', weight: 5 },
    { field: 'state', weight: 5 },
    { field: 'city', weight: 5 },
    
    // AI Optimization Fields (40%)
    { 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 profile[field] === 'string' ? JSON.parse(profile[field]) : profile[field];
      } catch {
        arrayData = [];
      }
      
      if (Array.isArray(arrayData) && arrayData.length > 0) {
        filledFields += weight;
      }
    } else {
      if (profile[field] && profile[field].toString().trim() !== '') {
        filledFields += weight;
      }
    }
  });

  // Bonus fields (5% total)
  const bonusFields = [
    { field: 'consultation_fee', weight: 1.25 },
    { field: 'response_time', weight: 1.25 },
    { field: 'digital_only', weight: 1.25, isBoolean: true },
    { field: 'comment', weight: 1.25 },
  ];

  bonusFields.forEach(({ field, weight, isBoolean }) => {
    totalFields += weight;
    
    if (isBoolean) {
      // For boolean fields, check if it exists (true/false both count)
      if (profile[field] !== undefined && profile[field] !== null) {
        filledFields += weight;
      }
    } else if (profile[field]) {
      if (typeof profile[field] === 'number' && profile[field] > 0) {
        filledFields += weight;
      } else if (typeof profile[field] === 'string' && profile[field].trim() !== '') {
        filledFields += weight;
      }
    }
  });

  // Profile picture (5%)
  totalFields += 5;
  if (profile.profile_picture) {
    filledFields += 5;
  }

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