import pool from "../db/db";

export async function getPopularCourses(country = null) {

    const connection = await pool.getConnection();

  try {
    let sqlQuery;
    let queryParams;

    if (country) {
      // Filter courses by university country
      sqlQuery = `
        SELECT 
          c.id, 
          c.slug as slug,
          c.name, 
          c.popular, 
          c.qualification,
          u.name AS university,
          u.logo AS university_logo,
          u.country AS university_country,
          u.country AS location,
          u.slug as university_slug
        FROM courses c
        RIGHT JOIN university_details u ON c.university_id = u.id
          AND u.country = $1
        GROUP BY c.id, u.name, u.logo, u.country  
        ORDER BY c.created_at DESC 
        LIMIT 8`;
      queryParams = [country];
    } else {
      // Original query - no country filter (popular courses)
      sqlQuery = `
        SELECT 
          c.id, 
          c.name, 
          c.slug as slug,
          c.popular, 
          c.qualification,
          u.name AS university,
          u.logo AS university_logo,
          u.country AS location,
          u.slug as university_slug
        FROM courses c
        LEFT JOIN university_details u ON c.university_id = u.id
        WHERE c.popular = 1 
        ORDER BY c.created_at DESC 
        LIMIT 5`;
      queryParams = [];
    }

    const [courses] = await connection.query(sqlQuery, queryParams);

    if(courses.length === 0) {
      return {
        success: false,
        message: 'No courses found',
        data: []
      };
    }

    return {
      success: true,
      data: courses
    };

  } catch (error) {
    console.error('Database error fetching popular courses:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedConsultants() {
  const connection = await pool.getConnection();
  
  try {
    const [consultants] = await connection.query(`
      SELECT 
        c.user_id as id,
        c.name,
        c.designation,
        c.company_name,
        c.profile_picture,
        c.nationality as country,
        c.state,
        c.city,
        c.target_countries,
        c.services,
        c.seo_slug,
        c.specialization,
        c.languages,
        c.consultation_fee,
        c.consultation_currency,
        c.response_time,
        c.digital_only,
        c.availability,
        c.average_rating,
        c.rating_count,
        c.profile_status,
        c.email,
        c.phone,
        c.created_at as registration_date
      FROM consultants c 
      WHERE 
        c.profile_status = 'approved'
        AND c.active_profile = '1' 
        AND c.active = '1' 
        AND c.seo_slug != ''
        AND c.seo_slug IS NOT NULL
        AND c.featured = '1' 
      LIMIT 8;
    `);

    if (!consultants || consultants.length === 0) {
      return {
        success: true,
        data: []
      };
    }

    // Process the consultants data
    const processedConsultants = consultants.map(consultant => {
      // Safely parse JSON fields
      const parseJSON = (str) => {
        try {
          return JSON.parse(str || '[]');
        } catch {
          return [];
        }
      };

      const target_countries = parseJSON(consultant.target_countries);
      const services = parseJSON(consultant.services);
      const specialization = parseJSON(consultant.specialization);
      const languages = parseJSON(consultant.languages);
      
      // Calculate expertise score based on various factors
      const expertiseScore = Math.min(
        Math.round(
          (target_countries.length * 5) + 
          (services.length * 3) + 
          (specialization.length * 4) + 
          (languages.length * 2) + 
          (consultant.rating_count || 0)
        ),
        100
      );

      // Determine badge based on rating
      let badge = '';
      const avgRating = consultant.average_rating || 0;
      if (avgRating >= 4.5) badge = 'Top Rated';
      else if (avgRating >= 4.0) badge = 'Highly Rated';
      else if (consultant.rating_count >= 10) badge = 'Experienced';
      else if (consultant.response_time === 'urgent') badge = 'Fast Responder';

      return {
        ...consultant,
        target_countries,
        services,
        specialization,
        languages,
        expertise_score: expertiseScore,
        badge,
        // Format consultation fee with currency
        formatted_fee: `${consultant.consultation_currency || 'USD'} ${(consultant.consultation_fee || 0).toLocaleString()}`,
        // Get first 2-3 services for display
        display_services: services.slice(0, 3),
        // Get first 2-3 specializations for display
        display_specializations: specialization.slice(0, 3),
        // Format response time
        response_time_formatted: consultant.response_time === 'urgent' ? 'Within 24h' : 
                               consultant.response_time === 'fast' ? '1-2 days' : '3-5 days'
      };
    });

    return {
      success: true,
      data: processedConsultants
    };

  } catch (error) {
    console.error('Database error fetching featured consultants:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getPopularUniversities(country = null) {

    const connection = await pool.getConnection();

  try {
    let sqlQuery;
    let queryParams;

    if (country) {
      // Filter universities by country
      sqlQuery = `
        SELECT 
          id, 
          name, 
          slug, 
          logo,
          country,
          city,
          country as location
        FROM university_details 
        WHERE country = $1
        ORDER BY created_at DESC 
        LIMIT 8`;
      queryParams = [country];
    } else {
      // Original query - no country filter (popular universities)
      sqlQuery = `
        SELECT 
          id, 
          name, 
          slug, 
          logo,
          country,
          city,
          country as location
        FROM university_details 
        WHERE popular = 1 AND is_active = 1
        ORDER BY created_at DESC 
        LIMIT 4`;
      queryParams = [];
    }

    const [universities] = await connection.query(sqlQuery, queryParams);

    if(universities.length === 0) {
      return {
        success: false,
        message: 'No universities found',
        data: []
      };
    }

    return {
      success: true,
      data: universities
    };

  } catch (error) {
    console.error('Database error fetching popular universities:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getBrowseCoursesData() {

  const connection = await pool.getConnection();

  try {
    // Run all three queries in parallel
    const [levelsData, universitiesData, subjectsData] = await Promise.all([
      // Query 1: Get Qualification Levels
      connection.query(`
        SELECT id, title, image 
        FROM posts 
        WHERE is_active = 1
        ORDER BY created_at DESC
      `),
      
      // Query 2: Get Featured Universities
      connection.query(`
        SELECT id, name, logo, slug, country
        FROM university_details 
        WHERE popular = 1 
        ORDER BY created_at DESC
        LIMIT 10
      `),
      
      // Query 3: Get Subjects with Course Counts
      connection.query(`
        SELECT 
          s.id, s.name, s.icon,
          COUNT(DISTINCT c.id) AS course_count,
          COUNT(DISTINCT ud.id) AS university_count
        FROM subjects s
        LEFT JOIN courses c ON s.id = c.subject_id
        LEFT JOIN university_details ud ON c.university_id = ud.id
        GROUP BY s.id, s.name, s.icon
        HAVING course_count > 0 AND university_count > 0
        ORDER BY course_count DESC
        LIMIT 12
      `)
    ]);

    if (levelsData.length === 0 || universitiesData.length === 0 || subjectsData.length === 0) {
      return {
        success: false,
        message: 'No data found',
        data: {
          levels: [],
          universities: [],
          subjects: []
        }
      };
    }

    return {
      success: true,
      data: {
        levels: levelsData[0],
        universities: universitiesData[0],
        subjects: subjectsData[0]
      }
    };

  } catch (error) {
    console.error('Database error fetching browse courses data:', error);
    return {
      success: false,
      message: error.message,
      data: {
        levels: [],
        universities: [],
        subjects: []
      }
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedScholarshipsWithQualifications() {
  
  const connection = await pool.getConnection();

  try {
    // First get scholarships with basic info
    const [scholarships] = await connection.query(
      `
        SELECT DISTINCT
          s.id,
          s.slug,
          s.title,
          s.image,
          s.amount_min,
          s.amount_max,
          s.cgpa,
          s.description,
          s.created_at,
          c.country AS country_name,
          c.currency_symbol as currency_symbol
        FROM scholarships s
        LEFT JOIN countries c ON s.country_id = c.id
        LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
        WHERE s.is_active = 1 
        ORDER BY s.created_at DESC
        LIMIT 12
      `
    );

    if (scholarships.length === 0) {
      return {
        success: true,
        data: []
      };
    }

    // Fix: Ensure we have a proper array and fetch qualifications correctly
    const scholarshipsWithQualifications = await Promise.all(
      scholarships.map(async (scholarship) => {
        // Make sure scholarship is a proper object, not nested
        const cleanScholarship = { ...scholarship };
        
        const [qualifications] = await connection.query(
          `SELECT 
            sq.qualification_id,
            p.title as qualification_name
           FROM scholarship_qualifications sq
           LEFT JOIN posts p ON sq.qualification_id = p.id
           WHERE sq.scholarship_id = ?
           ORDER BY p.title`,
          [cleanScholarship.id]
        );
        
        return {
          ...cleanScholarship,
          qualifications: qualifications || []
        };
      })
    );

    return {
      success: true,
      data: scholarshipsWithQualifications
    };

  } catch (error) {
    console.error('Database error fetching scholarships:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedYouTubeVideos() {

  const connection = await pool.getConnection();

  try {
    const [videos] = await connection.query(`
      SELECT 
        id,
        title,
        description,
        thumbnail,
        video_id,
        url,
        published_at,
        view_count,
        duration
      FROM youtube_videos 
      WHERE is_active = 1 AND featured = 1
      ORDER BY published_at DESC, created_at DESC
      LIMIT 4
    `);

    if(videos.length === 0) {
      return {
        success: false,
        message: 'No featured YouTube videos found',
        data: []
      };
    }

    return {
      success: true,
      data: videos
    };

  } catch (error) {
    console.error('Database error fetching YouTube videos:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedStudentReviews() {

  const connection = await pool.getConnection();

  try {
    const [reviews] = await connection.query(`
      SELECT 
        id,
        review_name,
        profile_pic,
        review,
        created_at,
        updated_at
      FROM tbl_review 
      WHERE 1=1  -- Simple condition to get all active reviews
      ORDER BY created_at DESC 
      LIMIT 10
    `);

    if(reviews.length === 0) {
      return {
        success: false,
        message: 'No reviews found',
        data: []
      };
    }

    return {
      success: true,
      data: reviews
    };

  } catch (error) {
    console.error('Database error fetching student reviews:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedSuccessStories() {
  
  const connection = await pool.getConnection();

  try {
    const [stories] = await connection.query(`
      SELECT * 
      FROM success_stories 
      WHERE is_featured = 1 
      ORDER BY created_at DESC 
      LIMIT 6
    `);

    if(stories.length === 0) {
      return {
        success: false,
        message: 'No featured success stories found',
        data: []
      };
    }

    return {
      success: true,
      data: stories
    };

  } catch (error) {
    console.error('Database error fetching success stories:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedBlogs() {

  const connection = await pool.getConnection();

  try {
    const [blogs] = await connection.query(`
      SELECT 
        id, 
        title, 
        slug, 
        image, 
        created_at 
      FROM blogs 
      WHERE is_active = 1 AND is_featured = 1 
      ORDER BY created_at DESC 
      LIMIT 8
    `);

    if(blogs.length === 0) {
      return {
        success: false,
        message: 'No featured blogs found',
        data: []
      };
    }

    return {
      success: true,
      data: blogs
    };

  } catch (error) {
    console.error('Database error fetching featured blogs:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  } finally {
    connection.release();
  }
}

export async function getFeaturedPartners() {
  
  const connection = await pool.getConnection();

  try {
    const [partners] = await connection.query(`
      SELECT * 
      FROM tbl_partners 
      WHERE 1=1  -- Simple condition to get all partners
      ORDER BY created_at DESC 
      LIMIT 20
    `);

    if(partners.length === 0) {
      return {
        success: false,
        message: 'No partners found',
        data: []
      };
    }

    return {
      success: true,
      data: partners
    };

  } catch (error) {
    console.error('Database error fetching partners:', error);
    return {
      success: false,
      message: error.message,
      data: []
    };
  }
}

export async function fetchSingleCountry(newCountry) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const country = newCountry;

    // Fetch universities for China
    const [universities] = await connection.query(`
      SELECT 
        ud.id, 
        ud.name, 
        ud.slug, 
        ud.logo,
        ud.country,
        ud.city,
        ud.ranking,
        ud.scholarship,
        COUNT(DISTINCT c.id) as courses_count
      FROM university_details ud
      LEFT JOIN courses c ON c.university_id = ud.id
      WHERE ud.country = ? AND ud.country_popular = 1
      GROUP BY ud.id, ud.name, ud.slug, ud.logo, ud.country, ud.city, ud.ranking, ud.scholarship
      ORDER BY ud.created_at DESC 
      LIMIT 8
    `, [country]);

    // Fetch limited courses for China
    const [courses] = await connection.query(`
      SELECT 
        c.id,
        c.name,
        c.slug as slug,
        c.duration,
        c.yearly_fee,
        c.currency_symbol,
        c.scholarship,
        ud.name as university,
        ud.slug as university_slug,
        ud.logo as university_logo
      FROM courses c
      INNER JOIN university_details ud ON c.university_id = ud.id
      WHERE ud.country = ? AND c.country_popular = 1
      ORDER BY c.created_at DESC
      LIMIT 8
    `, [country]);

    // Fetch country descriptions
    const [countryDescriptions] = await connection.query(`
      SELECT before_description, after_description 
      FROM countries 
      WHERE LOWER(country) = LOWER(?) OR LOWER(country) LIKE LOWER(?)
    `, [country.trim(), `%${country.trim()}%`]);

    return {
      success: true,
      data: {
        universities: universities || [],
        courses: courses || [],
        countryDescriptions: countryDescriptions[0] || {
          before_description: "",
          after_description: ""
        }
      }
    };

  } catch (error) {
    console.error('Error fetching China data:', error);
    return {
      success: false,
      error: error.message,
      data: {
        universities: [],
        courses: [],
        countryDescriptions: {
          before_description: "",
          after_description: ""
        }
      }
    };
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function getCountries() {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.query(
      `SELECT DISTINCT c.id, c.country, c.currency, c.currency_symbol 
       FROM countries c
       INNER JOIN university_details u ON c.country = u.country
       INNER JOIN courses co ON u.id = co.university_id
       ORDER BY c.country ASC`
    );
    return rows;
  } catch (err) {
    console.error('Error fetching countries:', err);
    return [];
  } finally {
    connection.release();
  }
}

export async function getLevels() {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.query(
      `SELECT id, title FROM posts ORDER BY display_order ASC`
    );
    return rows;
  } catch (err) {
    console.error('Error fetching levels:', err);
    return [];
  } finally {
    connection.release();
  }
}

export async function getSubjects() 
{
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.query(
      `SELECT id, name FROM subjects ORDER BY name ASC`
    );
    return rows;
  } catch (err) {
    console.error('Error fetching subjects:', err);
    return [];
  } finally {
    connection.release();
  }
}
export async function getMenuItems() {
  const connection = await pool.getConnection();

  try {
    
    // Fetch universities
    const [universities] = await connection.query(`
      SELECT 
        name as title,
        slug
      FROM university_details 
      WHERE show_in_menu = 1 
        AND display = 1 
        AND is_active = 1
      ORDER BY name ASC
    `);

    // Fetch courses
    const [courses] = await connection.query(`
      SELECT 
        c.name as title,
        c.slug,
        p.title as qualification,
        ud.slug as university_slug
      FROM courses c
      INNER JOIN posts p ON c.qualification = p.id
      INNER JOIN university_details ud ON c.university_id = ud.id
      WHERE c.show_in_menu = 1 
        AND c.active = 1 
        AND c.display = 1
      ORDER BY c.name ASC
    `);

    // Fetch scholarships
    const [scholarships] = await connection.query(`
      SELECT 
        s.title,
        s.slug,
        s.ielts
      FROM scholarships s
      WHERE s.show_in_menu = 1 
        AND s.is_active = 1
      ORDER BY s.title ASC
    `);

    // Fetch countries
    const [countries] = await connection.query(`
      SELECT 
        country as title,
        LOWER(code) as slug
      FROM countries 
      WHERE show_in_menu = 1
      ORDER BY country ASC
    `);

    const [qualifications] = await connection.query(`
      SELECT
        id,
        title,
        slug,
        display_order
      FROM posts
      WHERE is_active = 1 ORDER BY display_order ASC
    `);

    return {
      success: true,
      data: {
        universities,
        courses,
        scholarships,
        countries,
        qualifications
      }
    };

  } catch (error) {
    console.error('Error fetching menu items:', error);
    return {
      success: false,
      error: error.message,
      data: {
        universities: [],
        courses: [],
        scholarships: [],
        countries: []
      }
    };
  } finally {
    connection.release();
  }
}

export async function getStaticPage(slug) {
  const baseUrl = process.env.APP_URL || 'http://localhost:3000';

  try {
    const res = await fetch(`${baseUrl}/api/frontend/static-page/${slug}`, {
      // This makes Next.js cache it per slug at build time
      next: { revalidate: 3600 } // or use { cache: 'force-cache' } for full static
    });

    if (!res.ok) {
      throw new Error(`HTTP error! status: ${res.status}`);
    }

    return await res.json();

  } catch (error) {
    console.error(`Error fetching static page [${slug}]:`, error);
    return {
      success: false,
      error: error.message,
      data: null
    };
  } 
}

export async function getSiloRoute(slug) {
  const baseUrl = process.env.APP_URL || 'http://localhost:3000';

  try {
    const res = await fetch(`${baseUrl}/api/frontend/silo-routes/${slug}`, {
      // This makes Next.js cache it per slug at build time
      next: { revalidate: 3600 } // or use { cache: 'force-cache' } for full static
    });

    if (!res.ok) {
      throw new Error(`HTTP error! status: ${res.status}`);
    }

    return await res.json();

  } catch (error) {
    console.error(`Error fetching Silo Routes page [${slug}]:`, error);
    return {
      success: false,
      error: error.message,
      data: null
    };
  }
}