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

// Helper function to generate slug
const generateSlug = (courseName: string, courseId: number): string => {
  // Remove special characters, keep only letters, numbers and spaces
  let slug = courseName
    .toLowerCase()
    .replace(/[^\w\s]/gi, '') // Remove special characters
    .replace(/\s+/g, '-')     // Replace spaces with hyphens
    .trim();
  
  // Remove any leading/trailing hyphens
  slug = slug.replace(/^-+|-+$/g, '');
  
  // Add course ID at the end
  return `${slug}-${courseId}`;
};

export async function GET(request: NextRequest) {
  try {
    // Verify database pool
    await pool.execute('SELECT 1');
    console.log('🔍 Course API - Database pool successful');

    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 10;
    const search = searchParams.get('search') || '';
    const country = searchParams.get('country') || '';
    const qualification = searchParams.get('qualification') || '';
    const scholarship = searchParams.get('scholarship') || '';
    const startdate = searchParams.get('start_date') || '';
    const enddate = searchParams.get('end_date') || '';
    const popular = searchParams.get('popular') || '';
    const subject = searchParams.get('subject') || '';
    const universityIdFilter = searchParams.get('university_id') || '';
    const showInMenu = searchParams.get('showInMenu') || ''; // NEW: Menu visibility filter

    console.log('🔍 Course API called with params:', { page, limit, search, country, qualification, scholarship, showInMenu });

    // Build the SQL WHERE clause for filtering
    let whereClause = '1=1';
    let params = [];

    // Search filter
    if (search.trim() !== '') {
      whereClause += ` AND (c.name LIKE ? OR c.about LIKE ? OR c.slug LIKE ?)`;
      params.push(`%${search.trim()}%`, `%${search.trim()}%`, `%${search.trim()}%`);
    }

    // Scholarship filter
    if (scholarship && scholarship !== 'Select Scholarship') {
      if (scholarship === 'With Scholarship') {
        whereClause += ` AND (c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ?)`;
        params.push('%Yes%', '%Available%', '%True%', '%1%', '%Scholarship%');
      } else if (scholarship === 'Without Scholarship') {
        whereClause += ` AND (c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ? OR c.scholarship LIKE ?)`;
        params.push('%No%', '%Not Available%', '%False%', '%0%', '%None%');
      } else {
        whereClause += ` AND c.scholarship LIKE ?`;
        params.push(`%${scholarship}%`);
      }
    }

    // Qualification filter
    if (qualification && qualification !== '') {
      const qualificationList = qualification.split(',').map(q => q.trim()).filter(q => q);
      console.log("Check qualification split", qualification.split(',').map(q => q.trim()).filter(q => q))
      if (qualificationList.length > 0) {
        const qualificationFilters = qualificationList.map(qual => `(c.qualification LIKE ?)`).join(' OR ');
        whereClause += ` AND (${qualificationFilters})`;
        qualificationList.forEach(qual => params.push(`%${qual}%`));
      }
    }

    // Date filters (startdate and enddate)
    if (startdate) {
      whereClause += ` AND c.starting_date >= ?`;
      params.push(new Date(startdate));
    }

    if (enddate) {
      whereClause += ` AND c.deadline <= ?`;
      params.push(new Date(enddate));
    }

    if (popular !== '') {
      whereClause += ` AND c.popular = ?`;
      params.push(popular === 'true' ? 1 : 0);
    }

    if (subject !== '') {
      whereClause += ` AND c.subject_id = ?`;
      params.push(subject);
    }

    if (universityIdFilter && universityIdFilter !== '') {
      whereClause += ` AND c.university_id = ?`;
      params.push(universityIdFilter);
    }

    // NEW: Menu visibility filter
    if (showInMenu !== '') {
      whereClause += ` AND c.show_in_menu = ?`;
      params.push(showInMenu === '1' ? 1 : 0);
    }

    // Country filter - handled separately as it requires university lookup
    let universityIdsForCountry = [];
    if (country && country !== 'Select Country' && country !== '') {
      console.log('🔍 Course API - Applying country filter for:', country);
      
      const [matchingUniversities] = await pool.execute<RowDataPacket[]>(
        'SELECT id FROM university_details WHERE country LIKE ?',
        [`%${country}%`]
      );

      universityIdsForCountry = matchingUniversities.map(u => u.id);
      console.log('🔍 Course API - Universities matching country:', universityIdsForCountry.length);

      if (universityIdsForCountry.length === 0) {
        // No universities in this country, return empty results
        return NextResponse.json({
          success: true,
          data: [],
          pagination: {
            currentPage: page,
            totalPages: 0,
            totalItems: 0,
            itemsPerPage: limit,
            hasNextPage: false,
            hasPrevPage: false
          },
          filters: { country, qualification, scholarship, search },
          message: `No courses found for country: ${country}`
        });
      }
    }

    // Add country filter if applicable
    if (universityIdsForCountry.length > 0) {
      whereClause += ` AND c.university_id IN (${universityIdsForCountry.map(() => '?').join(',')})`;
      params.push(...universityIdsForCountry);
    }

    console.log('🔍 Course API - Final where clause:', whereClause);

    // Get total count with filters applied
    const [totalCountResult] = await pool.execute(
      `SELECT COUNT(*) AS count FROM courses c WHERE ${whereClause}`,
      params
    );
    const totalCount = totalCountResult[0].count;

    console.log('🔍 Course API - Total courses matching filters:', totalCount);

    if (totalCount === 0) {
      return NextResponse.json({
        success: true,
        data: [],
        pagination: {
          currentPage: page,
          totalPages: 0,
          totalItems: 0,
          itemsPerPage: limit,
          hasNextPage: false,
          hasPrevPage: false
        },
        filters: { country, qualification, scholarship, search },
        message: 'No courses found with current filters'
      });
    }

    // Calculate pagination
    const skip = (page - 1) * limit;
    const totalPages = Math.ceil(totalCount / limit);

    if (page > totalPages && totalPages > 0) {
      return NextResponse.json({
        success: false,
        error: 'Page number exceeds available results',
        redirectTo: totalPages,
        totalPages,
        totalItems: totalCount
      }, { status: 400 });
    }

    // Fetch courses with pagination and filters
    const [courses] = await pool.execute<RowDataPacket[]>(
      `SELECT 
        c.*,
        c.show_in_menu,
        p.title AS qualification,
        p.id AS qualification_id,
        ud.country as university_country,
        ud.name as university_name,
        ud.city as university_city,
        ud.logo as university_logo
      FROM courses c
      LEFT JOIN posts p ON c.qualification = p.id
      LEFT JOIN university_details ud ON c.university_id = ud.id
      WHERE ${whereClause} ORDER BY c.created_at DESC LIMIT ? OFFSET ?`,
      [...params, limit, skip]
    );

    console.log('🔍 Course API - Courses fetched:', courses.length);

    // Enrich with subject data
    const subjectIds = [...new Set(courses.map(c => c.subject_id).filter(Boolean))];
    const subject_placeholders = subjectIds.map(() => '?').join(', ');

    const subject_query = `
      SELECT id, name 
      FROM subjects 
      WHERE id IN (${subject_placeholders})`;

    const [subjects] = subjectIds.length > 0
      ? await pool.execute<RowDataPacket[]>(subject_query, subjectIds)
      : [];

    const subjectById = Object.fromEntries(subjects.map(s => [s.id, s]));

    // Enrich courses with additional data
    const enriched = courses.map(course => {
      const subject = subjectById[course.subject_id] || null;
      
      return {
        ...course,
        location: course.university_city && course.university_country ? 
          `${course.university_city}, ${course.university_country}` : null,
        subject_name: subject?.name || null,
      };
    });

    return NextResponse.json({
      success: true,
      data: enriched,
      pagination: {
        currentPage: page,
        totalPages,
        totalItems: totalCount,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      },
      filters: { country, qualification, scholarship, search, showInMenu }
    });
  } catch (error) {
    console.error('🔍 Course API - Error:', error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  }
}

// POST create new course
export async function POST(req: NextRequest) {
  const postConnection = await pool.getConnection();

  try {
    const data = await req.json();
    
    if (!data.name || !data.university_id || !data.subject_id) {
      return NextResponse.json(
        { success: false, message: "Missing required fields: name, university_id, subject_id" },
        { status: 400 }
      );
    }

    const token = req.cookies.get("university-token")?.value || null;
    
    if (!token) {
      return NextResponse.json(
        {
          message: "Please Login to continue"
        },
        {
          status: 400
        }
      );
    }

    const decoded = await verifyAuthentication(token)

    if (decoded instanceof NextResponse) {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

    
    await postConnection.beginTransaction();

    const [checkUniversity] = await postConnection.query<RowDataPacket[]>("SELECT * FROM university_details WHERE id = ?", [data.university_id])

    if(checkUniversity.length === 0){
      return NextResponse.json(
        {
          success: false,
          message: "You select an invalid University. Please select the correct one",
        },
        {
          status: 400
        }
      )
    }

    const [checkSubject] = await postConnection.query<RowDataPacket[]>("SELECT * FROM subjects WHERE id = ?", [data.subject_id])

    if(checkSubject.length === 0){
      return NextResponse.json(
        {
          success: false,
          message: "You select an invalid Subject. Please select the correct one",
        },
        {
          status: 400
        }
      )
    }

    const [checkLevel] = await postConnection.query<RowDataPacket[]>("SELECT * FROM posts WHERE id = ?", [data.qualification])

    if(checkLevel.length === 0){
      return NextResponse.json(
        {
          success: false,
          message: "You select an invalid Level of Qualification. Please select the correct one",
        },
        {
          status: 400
        }
      )
    }

    // First insert without slug (will be generated after we get the ID)
    const [result] = await postConnection.query<ResultSetHeader>(
      `INSERT INTO courses (
        name, university_id, subject_id, sm_question, sm_answer, review_detail,
        qualification, duration, yearly_fee, application_fee, languages,
        starting_date, deadline, about, entry_requirments, scholarship, 
        sort_order, active, display, popular, show_in_menu
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        data.name,
        data.university_id,
        data.subject_id,
        data.sm_question,
        data.sm_answer,
        data.review_detail,
        data.qualification,
        data.duration,
        data.yearly_fee,
        data.application_fee,
        data.languages,
        data.starting_date ? new Date(data.starting_date) : null,
        data.deadline ? new Date(data.deadline) : null,
        data.about,
        data.entry_requirments,
        data.scholarship,
        data.sort_order ?? 0,
        data.active ?? true,
        data.display ?? true,
        data.popular ?? false,
        data.show_in_menu ?? true, // NEW: Add menu visibility field
      ]
    );

    if(result.affectedRows === 0){
      return NextResponse.json(
        {
          success: false,
          message: "Something went wrong. Please try again later",
        },
        {
          status: 400
        }
      )
    }

    const insertId = result.insertId;

    // Generate slug based on course name and ID
    const slug = generateSlug(data.name, insertId);
    
    // Update the course with generated slug
    await postConnection.query<ResultSetHeader>(
      `UPDATE courses SET slug = ? WHERE id = ?`,
      [slug, insertId]
    );

    // Fetch the newly created course to get all data for sync
    const [newCourseRows] = await postConnection.query<RowDataPacket[]>("SELECT * FROM courses WHERE id = ?", [insertId]);
    const newCourse = newCourseRows[0];

    // Add activity log
    await postConnection.query(
      "INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", 
      [decoded.id, `Course Name ${data.name} added by user ${decoded.name} and email is ${decoded.email}`]
    );

    // Commit transaction if both operations succeed
    await postConnection.commit();

    return NextResponse.json({ 
      success: true, 
      data: newCourse,
      message: "Course created successfully and synced to PostgreSQL"
    });

  } catch (error: any) {
    console.error('Error creating course:', error);
    
    // Rollback transaction if any error occurs
    if (postConnection) {
      await postConnection.rollback();
      console.log("Transaction rolled back due to error:", error);
    }
    
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (postConnection) {
      postConnection.release();
    }
  }
}