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;
}

// Helper function to calculate profile completion percentage
function calculateProfileCompletion(profile: any, preferences: any): number {
  let completedFields = 0;
  let totalFields = 13; // Total fields to check (from both profile and preferences)

  // Profile fields (from users and students tables) - 7 fields
  const profileFields = [
    profile?.first_name,
    profile?.last_name,
    profile?.phone,
    profile?.nationality,
    profile?.city,
    profile?.gender,
    profile?.prefered_program,
  ];

  profileFields.forEach(field => {
    if (field && field.toString().trim() !== '') {
      completedFields++;
    }
  });

  // Preferences fields (from app_preferences table) - 6 fields
  if (preferences) {
    // Qualification selected
    if (preferences.qualification_id) completedFields++;
    
    // At least one subject selected
    const subjects = preferences.subjects_selected ? 
      (typeof preferences.subjects_selected === 'string' ? 
        JSON.parse(preferences.subjects_selected) : preferences.subjects_selected) : [];
    if (subjects.length > 0) completedFields++;
    
    // CGPA provided
    if (preferences.cgpa && preferences.cgpa.trim() !== '') completedFields++;
    
    // Budget provided
    if (preferences.budget && preferences.budget.trim() !== '') completedFields++;
    
    // English test type selected
    if (preferences.english_test_type) completedFields++;
    
    // English score provided (only if test type is selected)
    if (preferences.english_test_score && preferences.english_test_score.trim() !== '') completedFields++;
  }

  // Calculate percentage
  const percentage = Math.round((completedFields / totalFields) * 100);
  return Math.min(100, percentage);
}

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 student
    if (decoded.role !== "student" && decoded.role !== "consultant") {
      return NextResponse.json({
        success: false,
        message: "You are not authorized to perform this action"
      }, { status: 401 });
    }

    // Get user profile information
    const [userInfo] = await connection.query<RowDataPacket[]>(
      `SELECT 
        u.id,
        u.first_name,
        u.last_name,
        u.email,
        u.phone,
        u.image as profile_picture,
        s.nationality,
        s.gender,
        s.city,
        s.state,
        s.prefered_program,
        s.city as country_name,
        s.state as state_name,
        s.nationality as city_name
      FROM users u
      LEFT JOIN students s ON u.id = s.user_id
      WHERE u.id = ?`,
      [decoded.id]
    );

    // Get user preferences from app_preferences table
    const [preferencesResult] = await connection.query<RowDataPacket[]>(
      `SELECT 
        qualification_id,
        subjects_selected,
        cgpa,
        budget,
        english_test_type,
        english_test_score
      FROM app_preferences 
      WHERE user_id = ?`,
      [decoded.id]
    );

    const preferences = preferencesResult.length > 0 ? preferencesResult[0] : null;

    // 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 applications count
    const [applicationsResult] = await connection.query<RowDataPacket[]>(
      "SELECT COUNT(*) as count FROM search_apply_now WHERE user_id = ?",
      [decoded.id]
    );
    const applicationsCount = applicationsResult[0]?.count || 0;

        // Get applications count
    const [studentsResult] = await connection.query<RowDataPacket[]>(
      "SELECT COUNT(*) as count FROM cstudents WHERE consultant_id = ?",
      [decoded.id]
    );
    const studentCount = studentsResult[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
    const [recentActivity] = await connection.query<RowDataPacket[]>(
      `(SELECT 
        'application' as type,
        created_at as date,
        CONCAT('Applied to ', item_name) as description
      FROM search_apply_now 
      WHERE user_id = ?)
      UNION ALL
      (SELECT 
        'wishlist' as type,
        created_at as date,
        'Added to wishlist' as description
      FROM wishlist 
      WHERE user_id = ?)
      UNION ALL
      (SELECT 
        'notification' as type,
        created_at as date,
        message as description
      FROM notifications 
      WHERE user_id = ? AND is_read = 0)
      ORDER BY date DESC
      LIMIT 5`,
      [decoded.id, decoded.id, decoded.id]
    );

    // Format user 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: userInfo[0].profile_picture || null,
      nationality: userInfo[0].nationality?.toString() || '',
      nationality_name: userInfo[0].nationality_name || '',
      gender: userInfo[0].gender || '',
      prefered_program: userInfo[0].prefered_program || '',
      city: userInfo[0].city?.toString() || '',
      city_name: userInfo[0].city_name || '',
      state: userInfo[0].state?.toString() || '',
      state_name: userInfo[0].state_name || '',
    } : null;

    // Calculate profile completion percentage
    const profileCompletion = calculateProfileCompletion(profile, preferences);

    // Parse subjects_selected if it exists
    let subjectsSelected = [];
    if (preferences?.subjects_selected) {
      try {
        subjectsSelected = typeof preferences.subjects_selected === 'string' 
          ? JSON.parse(preferences.subjects_selected) 
          : preferences.subjects_selected;
      } catch (e) {
        console.error('Error parsing subjects_selected:', e);
      }
    }

    return NextResponse.json({
      success: true,
      data: {
        profile: {
          ...profile,
          profile_completion: profileCompletion,
        },
        preferences: preferences ? {
          qualification_id: preferences.qualification_id,
          subjects_selected: subjectsSelected,
          cgpa: preferences.cgpa,
          budget: preferences.budget,
          english_test_type: preferences.english_test_type,
          english_test_score: preferences.english_test_score,
        } : null,
        counts: {
          wishlist: wishlistCount,
          applications: applicationsCount,
          students: studentCount,
          notifications: unreadNotificationsCount,
        },
        recentActivity: recentActivity || []
      }
    }, { status: 200 });

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