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

export async function POST(request: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    const authHeader = request.headers.get('authorization');
    if (authHeader !== process.env.APP_API_KEY) {
      return NextResponse.json({ success: false, message: 'Unauthorized Access' }, { status: 401 });
    }

    const body = await request.json();
    const { qualification_id, subjects_selected, cgpa, budget, english_test_type, english_test_score } = body;

    let subjects = subjects_selected;
    if (typeof subjects_selected === 'string') {
      subjects = JSON.parse(subjects_selected);
    }

    const budgetNumber = budget ? parseFloat(budget) : null;
    const cgpaNumber = cgpa ? parseFloat(cgpa) : null;
    const englishScoreNumber = english_test_score ? parseFloat(english_test_score) : null;

    // Fetch universities with match scores
    const [universities] = await connection.query<RowDataPacket[]>(`
      SELECT 
        u.id,
        u.name,
        u.slug,
        u.logo as image,
        CONCAT(u.city, ', ', u.country) as location,
        u.ranking,
        u.scholarship,
        u.total_students,
        CASE 
          WHEN u.ranking IS NOT NULL AND u.ranking < 100 THEN 95
          WHEN u.ranking IS NOT NULL AND u.ranking < 500 THEN 80
          WHEN u.ranking IS NOT NULL THEN 65
          ELSE 50
        END as ranking_score,
        CASE WHEN u.scholarship = true THEN 25 ELSE 0 END as scholarship_score,
        CASE 
          WHEN u.total_students > 20000 THEN 15
          WHEN u.total_students > 10000 THEN 10
          ELSE 5
        END as size_score,
        ROUND(
          (
            CASE 
              WHEN u.ranking IS NOT NULL AND u.ranking < 100 THEN 95
              WHEN u.ranking IS NOT NULL AND u.ranking < 500 THEN 80
              WHEN u.ranking IS NOT NULL THEN 65
              ELSE 50
            END +
            CASE WHEN u.scholarship = true THEN 25 ELSE 0 END +
            CASE 
              WHEN u.total_students > 20000 THEN 15
              WHEN u.total_students > 10000 THEN 10
              ELSE 5
            END
          ) / 1.35
        ) as match_score
      FROM university_details u
      WHERE u.display = true AND u.active = true
      ORDER BY match_score DESC, u.ranking ASC
      LIMIT 20
    `);

    // Build course query with dynamic filters
    let courseQuery = `
      SELECT 
        c.id,
        c.name,
        c.slug,
        c.duration,
        c.yearly_fee as fee,
        c.scholarship,
        u.name as university,
        u.id as university_id,
        u.logo as university_logo,
        u.ranking as university_ranking,
        p.title as qualification,
        cn.currency_symbol,
        CONCAT(u.city, ', ', u.country) as location,
        CASE 
          WHEN c.yearly_fee IS NOT NULL AND c.yearly_fee <= ? THEN 40
          WHEN c.yearly_fee IS NOT NULL AND c.yearly_fee <= ? THEN 30
          WHEN c.yearly_fee IS NOT NULL THEN 20
          ELSE 10
        END as budget_score,
        CASE WHEN c.scholarship = true THEN 30 ELSE 0 END as scholarship_score,
        CASE 
          WHEN u.ranking IS NOT NULL AND u.ranking < 100 THEN 35
          WHEN u.ranking IS NOT NULL AND u.ranking < 500 THEN 25
          WHEN u.ranking IS NOT NULL THEN 15
          ELSE 10
        END as ranking_score,
        ROUND(
          (
            CASE 
              WHEN c.yearly_fee IS NOT NULL AND c.yearly_fee <= ? THEN 40
              WHEN c.yearly_fee IS NOT NULL AND c.yearly_fee <= ? THEN 30
              WHEN c.yearly_fee IS NOT NULL THEN 20
              ELSE 10
            END +
            CASE WHEN c.scholarship = true THEN 30 ELSE 0 END +
            CASE 
              WHEN u.ranking IS NOT NULL AND u.ranking < 100 THEN 35
              WHEN u.ranking IS NOT NULL AND u.ranking < 500 THEN 25
              WHEN u.ranking IS NOT NULL THEN 15
              ELSE 10
            END
          ) / 1.05
        ) as match_score
      FROM courses c
      LEFT JOIN university_details u ON c.university_id = u.id
      LEFT JOIN posts p ON c.qualification = p.id
      LEFT JOIN countries cn ON u.country = cn.country
      WHERE c.active = '1' AND u.display = true
    `;

    const queryParams: any[] = [];
    const lowBudget = budgetNumber ? budgetNumber * 0.7 : 10000;
    const highBudget = budgetNumber ? budgetNumber * 1.3 : 30000;
    queryParams.push(highBudget, lowBudget, highBudget, lowBudget);
    
    if (qualification_id) {
      courseQuery += ` AND c.qualification = ?`;
      queryParams.push(qualification_id);
    }
    
    if (subjects && subjects.length > 0) {
      const placeholders = subjects.map(() => '?').join(',');
      courseQuery += ` AND c.subject_id IN (${placeholders})`;
      queryParams.push(...subjects);
    }
    
    courseQuery += ` ORDER BY match_score DESC, c.yearly_fee ASC LIMIT 20`;
    
    const [courses] = await connection.query<RowDataPacket[]>(courseQuery, queryParams);

    // Fetch scholarships based on preferences
    let scholarshipQuery = `
      SELECT 
        s.id,
        s.title,
        s.slug,
        s.image,
        s.deadline,
        s.amount_min,
        s.amount_max,
        s.cgpa,
        s.percentage_cover,
        s.ielts,
        c.country AS country_name,
        c.currency_symbol,
        CASE 
          WHEN s.amount_max IS NOT NULL AND ? IS NOT NULL AND s.amount_max <= ? THEN 50
          WHEN s.amount_min IS NOT NULL AND ? IS NOT NULL AND s.amount_min <= ? THEN 40
          WHEN s.percentage_cover IS NOT NULL AND s.percentage_cover >= 50 THEN 35
          WHEN s.percentage_cover IS NOT NULL THEN 25
          ELSE 15
        END as budget_score,
        CASE 
          WHEN s.cgpa IS NOT NULL AND ? IS NOT NULL AND CAST(s.cgpa AS DECIMAL) <= ? THEN 35
          WHEN s.cgpa IS NOT NULL THEN 20
          ELSE 10
        END as cgpa_score,
        CASE 
          WHEN s.ielts = true AND ? IS NOT NULL AND ? >= 6.5 THEN 15
          WHEN s.ielts = true THEN 5
          ELSE 10
        END as ielts_score,
        ROUND(
          (
            CASE 
              WHEN s.amount_max IS NOT NULL AND ? IS NOT NULL AND s.amount_max <= ? THEN 50
              WHEN s.amount_min IS NOT NULL AND ? IS NOT NULL AND s.amount_min <= ? THEN 40
              WHEN s.percentage_cover IS NOT NULL AND s.percentage_cover >= 50 THEN 35
              WHEN s.percentage_cover IS NOT NULL THEN 25
              ELSE 15
            END +
            CASE 
              WHEN s.cgpa IS NOT NULL AND ? IS NOT NULL AND CAST(s.cgpa AS DECIMAL) <= ? THEN 35
              WHEN s.cgpa IS NOT NULL THEN 20
              ELSE 10
            END +
            CASE 
              WHEN s.ielts = true AND ? IS NOT NULL AND ? >= 6.5 THEN 15
              WHEN s.ielts = true THEN 5
              ELSE 10
            END
          ) / 1.0
        ) as match_score
      FROM scholarships s
      LEFT JOIN countries c ON s.country_id = c.id
      WHERE s.is_active = 1
        AND (s.deadline IS NULL OR s.deadline >= CURDATE())
    `;

    const scholarshipParams: any[] = [
      budgetNumber, budgetNumber, budgetNumber, budgetNumber,
      cgpaNumber, cgpaNumber,
      englishScoreNumber, englishScoreNumber,
      budgetNumber, budgetNumber, budgetNumber, budgetNumber,
      cgpaNumber, cgpaNumber,
      englishScoreNumber, englishScoreNumber
    ];

    // Add qualification filter for scholarships
    if (qualification_id) {
      scholarshipQuery += ` AND EXISTS (SELECT 1 FROM scholarship_qualifications sq WHERE sq.scholarship_id = s.id AND sq.qualification_id = ?)`;
      scholarshipParams.push(qualification_id);
    }

    scholarshipQuery += ` ORDER BY match_score DESC LIMIT 20`;

    const [scholarships] = await connection.query<RowDataPacket[]>(scholarshipQuery, scholarshipParams);

    // Add personalized reasons
    const universitiesWithReasons = universities.map(u => ({
      ...u,
      match_score: Math.min(100, Math.round(u.match_score || 50)),
      reason: getUniversityReason(u, budgetNumber, cgpa, english_test_score)
    }));

    const coursesWithReasons = courses.map(c => ({
      ...c,
      fee_with_symbol: c.fee ? `${c.currency_symbol || '$'}${parseInt(c.fee).toLocaleString()}` : null,
      match_score: Math.min(100, Math.round(c.match_score || 50)),
      reason: getCourseReason(c, budgetNumber, cgpa, english_test_score)
    }));

    const scholarshipsWithReasons = scholarships.map(s => ({
      ...s,
      match_score: Math.min(100, Math.round(s.match_score || 50)),
      reason: getScholarshipReason(s, cgpaNumber, englishScoreNumber)
    }));

    return NextResponse.json({
      success: true,
      universities: universitiesWithReasons,
      courses: coursesWithReasons,
      scholarships: scholarshipsWithReasons,
    });

  } catch (error) {
    console.error('Recommendations error:', error);
    return NextResponse.json({ 
      success: false, 
      message: error instanceof Error ? error.message : 'Internal server error' 
    }, { status: 500 });
  } finally {
    connection.release();
  }
}

function getUniversityReason(university: any, budget: number | null, cgpa: string, englishScore: string): string {
  const reasons = [];
  if (university.ranking && university.ranking < 100) reasons.push(`Top ${university.ranking} ranked globally`);
  else if (university.ranking && university.ranking < 500) reasons.push(`Ranked #${university.ranking} worldwide`);
  if (university.scholarship) reasons.push(`Scholarship available`);
  if (university.total_students) reasons.push(`${university.total_students.toLocaleString()}+ students`);
  return reasons.length ? reasons.join(' • ') : `Excellent match for your profile`;
}

function getCourseReason(course: any, budget: number | null, cgpa: string, englishScore: string): string {
  const reasons = [];
  if (course.fee && budget) {
    const fee = parseFloat(course.fee);
    if (fee <= budget) reasons.push(`Within your budget`);
    else if (fee <= budget * 1.2) reasons.push(`Slightly above budget, great value`);
  }
  if (course.scholarship) reasons.push(`Scholarship available`);
  if (course.duration) reasons.push(`${course.duration}`);
  return reasons.length ? reasons.join(' • ') : `Highly recommended based on your interests`;
}

function getScholarshipReason(scholarship: any, cgpa: number | null, englishScore: number | null): string {
  const reasons = [];
  if (scholarship.percentage_cover && scholarship.percentage_cover >= 50) {
    reasons.push(`${scholarship.percentage_cover}% coverage`);
  } else if (scholarship.amount_max) {
    reasons.push(`Up to ${scholarship.currency_symbol || '$'}${scholarship.amount_max.toLocaleString()}`);
  }
  if (scholarship.cgpa && cgpa && parseFloat(scholarship.cgpa) <= cgpa) {
    reasons.push(`CGPA ${scholarship.cgpa}+ eligible`);
  }
  if (scholarship.ielts) reasons.push(`IELTS accepted`);
  return reasons.length ? reasons.join(' • ') : `Great funding opportunity`;
}