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

// Helper function to validate JSON fields
const validateJsonField = (field, fieldName) => {
  if (!field || field === '') return { valid: true, value: null };

  // If it's already an object (might happen if sent as FormData)
  if (typeof field === 'object') return { valid: true, value: field };

  try {
    const parsed = JSON.parse(field);
    return { valid: true, value: parsed };
  } catch (e) {
    console.log(`JSON validation failed for ${fieldName}:`, field, e.message);
    return {
      valid: false,
      error: `Invalid ${fieldName} format. Must be valid JSON. Received: ${field}`
    };
  }
};

// Custom JSON stringifier that handles BigInt
const safeJsonStringify = (obj) => {
  return JSON.stringify(obj, (key, value) => 
    typeof value === 'bigint' ? value.toString() : value
  );
};

export async function POST(request) {
  const postConnection = await pool.getConnection();

  try {
    const token = request.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 formData = await request.formData();
    const data = Object.fromEntries(formData.entries());

    // Collect all validation errors
    const errors = [];
    if (typeof data.name === 'string' && !data.name.trim()) errors.push('University name is required');
    if (typeof data.country === 'string' && !data.country.trim()) errors.push('Country is required');
    if (typeof data.university_type === 'string' && !data.university_type.trim()) errors.push('University type is required');

    // Validate JSON fields
    const reviewDetailValidation = validateJsonField(data.review_detail, 'review_detail');
    const englishTestValidation = validateJsonField(data.english_test_requirements, 'english_test_requirements');
    const keywordsValidation = validateJsonField(data.keywords, 'keywords');
    const featuredImagesValidation = validateJsonField(data.featured_images, 'featured_images');
    const faqsValidation = validateJsonField(data.faqs, 'faqs');

    if (!reviewDetailValidation.valid) errors.push(reviewDetailValidation.error);
    if (!englishTestValidation.valid) errors.push(englishTestValidation.error);
    if (!keywordsValidation.valid) errors.push(keywordsValidation.error);
    if (!featuredImagesValidation.valid) errors.push(featuredImagesValidation.error);
    if (!faqsValidation.valid) errors.push(faqsValidation.error);

    // Validate meta fields
    if (typeof data.meta_title === 'string' && data.meta_title.trim() !== '') {
      if (data.meta_title.length > 255) {
        errors.push('Meta title must be 255 characters or less');
      }
    }

    if (typeof data.meta_description === 'string' && data.meta_description.trim() !== '') {
      if (data.meta_description.length > 500) {
        errors.push('Meta description must be 500 characters or less');
      }
    }

    // Validate ranking - must be a number if provided
    if (typeof data.ranking === 'string' && data.ranking.trim() !== '') {
      const rankingNum = parseFloat(data.ranking);
      if (isNaN(rankingNum)) {
        errors.push('Ranking must be a valid number');
      }
    }

    // Validate CGPA - must be a valid number between 0 and 4 if provided
    if (typeof data.cgpa === 'string' && data.cgpa.trim() !== '') {
      const cgpaNum = parseFloat(data.cgpa);
      if (isNaN(cgpaNum) || cgpaNum < 0 || cgpaNum > 4.0) {
        errors.push('CGPA must be a valid number between 0.0 and 4.0');
      }
    }

    // Validate application fee - must be a valid number if provided
    if (typeof data.application_fee === 'string' && data.application_fee.trim() !== '') {
      const appFeeNum = parseFloat(data.application_fee);
      if (isNaN(appFeeNum) || appFeeNum < 0) {
        errors.push('Application fee must be a valid positive number');
      }
    }

    // Validate English test requirements
    if (englishTestValidation.valid && englishTestValidation.value) {
      const englishTests = englishTestValidation.value;
      if (Array.isArray(englishTests)) {
        for (let i = 0; i < englishTests.length; i++) {
          const test = englishTests[i];
          if (!test.testType) {
            errors.push(`English test #${i + 1}: Test type is required`);
          }
          if (test.testType && test.testType !== 'none' && !test.minScore) {
            errors.push(`English test #${i + 1}: Minimum score is required for ${test.testType}`);
          }
        }
      }
    }

    // Validate FAQs
    if (faqsValidation.valid && faqsValidation.value) {
      const faqs = faqsValidation.value;
      if (Array.isArray(faqs)) {
        for (let i = 0; i < faqs.length; i++) {
          const faq = faqs[i];
          if (!faq.question || !faq.question.trim()) {
            errors.push(`FAQ #${i + 1}: Question is required`);
          }
          if (!faq.answer || !faq.answer.trim()) {
            errors.push(`FAQ #${i + 1}: Answer is required`);
          }
        }
      }
    }

    if (errors.length > 0) {
      return Response.json(
        { error: 'Validation failed', details: errors },
        { status: 400 }
      );
    }

    // Generate slug (make sure data.name is a string)
    if (typeof data.name === 'string') {
      const slug = slugify(data.name, { lower: true, strict: true });

      // Check for existing university
      const [existingUniversityRows] = await pool.query<RowDataPacket[]>(
        "SELECT * FROM university_details WHERE slug = ? AND display = true LIMIT 1",
        [slug]
      );

      if (existingUniversityRows.length > 0) {
        return Response.json(
          { error: 'University with this name already exists' },
          { status: 400 }
        );
      }

      // Handle numeric fields
      const totalStudents = typeof data.total_students === 'string' ? parseInt(data.total_students) : null;
      const internationalStudent = typeof data.international_student === 'string' ? parseInt(data.international_student) : null;
      
      // Handle CGPA field
      const cgpaValue = typeof data.cgpa === 'string' && data.cgpa.trim() !== ''
        ? parseFloat(data.cgpa)
        : null;

      // Handle application fee
      const applicationFeeValue = typeof data.application_fee === 'string' && data.application_fee.trim() !== ''
        ? parseFloat(data.application_fee)
        : null;

      // Handle ranking - only set if valid and ensure it's a string before parsing
      const rankingValue = typeof data.ranking === 'string' && data.ranking.trim() !== ''
        ? parseFloat(data.ranking)
        : null;

      // Handle acceptance rate
      const acceptanceRate = typeof data.acceptance_rate === 'string' && data.acceptance_rate.trim() !== ''
        ? data.acceptance_rate.trim()
        : null;

      // Handle meta fields
      const metaTitle = typeof data.meta_title === 'string' && data.meta_title.trim() !== ''
        ? data.meta_title.trim()
        : null;
      
      const metaDescription = typeof data.meta_description === 'string' && data.meta_description.trim() !== ''
        ? data.meta_description.trim()
        : null;

      // Handle menu toggle - NEW
      const showInMenu = data.show_in_menu === 'true';

      // Handle boolean fields
      const isPopular = data.popular === 'true';
      const isCountryPopular = data.country_popular === 'true';
      const hasScholarship = data.scholarship === '1';
      const isActive = data.is_active === undefined ? true : data.is_active === 'true';

      // Handle phone number - store as string
      const phoneNo = typeof data.phone_no === 'string' ? data.phone_no.trim() : null;

      // Handle image URLs
      const featureImage = data.feature_image_url || data.feature_image || null;
      const logo = data.logo_url || data.logo || null;

      // Process English test requirements
      let processedEnglishTests = null;
      if (englishTestValidation.valid && englishTestValidation.value) {
        processedEnglishTests = englishTestValidation.value.map(test => ({
          testType: test.testType,
          minScore: test.minScore ? parseFloat(test.minScore) : null,
          createdAt: new Date().toISOString()
        }));
      }

      // Process keywords
      const processedKeywords = keywordsValidation.valid ? keywordsValidation.value : [];

      // Process featured images
      const processedFeaturedImages = featuredImagesValidation.valid ? featuredImagesValidation.value : [];

      // Process FAQs
      const processedFaqs = faqsValidation.valid ? faqsValidation.value : [];

      const userId = decoded.id;

      // UPDATED SQL query with menu toggle field
      const [result] = await postConnection.query<ResultSetHeader>(
        `INSERT INTO university_details 
          (name, slug, university_type, founded_in, country, city, address, postcode, phone_no, 
          total_students, international_student, scholarship, cgpa, overview, academic_programs, 
          scholarships_financial_support, campus_life_student_experience, why_choose, 
          short_description, keywords,
          languages, accommodation, intake, ranking, 
          alternate_email, website, popular, country_popular, average_course_fee, application_fee, 
          acceptance_rate, meta_title, meta_description, is_active,
          show_in_menu,
          review_detail, english_test_requirements,
          user_id, feature_image, logo, featured_images) 
        VALUES 
          (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          data.name, 
          slug, 
          data.university_type,
          data.founded_in || null, 
          data.country || null, 
          data.city || null, 
          data.address || null,
          data.postcode || null, 
          phoneNo, 
          totalStudents,
          internationalStudent, 
          hasScholarship, 
          cgpaValue, 
          data.overview || '',
          data.academic_programs || '',
          data.scholarships_financial_support || '',
          data.campus_life_student_experience || '',
          data.why_choose || '',
          data.short_description || '',
          JSON.stringify(processedKeywords),
          data.languages || null, 
          data.accommodation || '', 
          data.intake || null,
          rankingValue, 
          data.alternate_email || null, 
          data.website || null, 
          isPopular,
          isCountryPopular,
          data.average_course_fee || null,
          applicationFeeValue,
          acceptanceRate,
          metaTitle,
          metaDescription,
          isActive,
          showInMenu,
          JSON.stringify(reviewDetailValidation.value), 
          processedEnglishTests ? JSON.stringify(processedEnglishTests) : null,
          userId, 
          featureImage, 
          logo,
          JSON.stringify(processedFeaturedImages)
        ]
      );

      // Insert FAQs into university_faqs table
      if (Array.isArray(processedFaqs) && processedFaqs.length > 0) {
        for (const faq of processedFaqs) {
          if (faq.question && faq.answer) {
            await postConnection.query(
              "INSERT INTO university_faqs (university_id, question, answer) VALUES (?, ?, ?)",
              [result.insertId, faq.question.trim(), faq.answer.trim()]
            );
          }
        }
      }

      
      // STEP 2: Get the inserted university
      console.log("🔄 Step 2: Getting inserted university...");
      const [universityRows] = await postConnection.query(
        "SELECT * FROM university_details WHERE id = ?",
        [result.insertId]
      );

      const university = universityRows[0];

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

      await postConnection.commit();

      console.log("✅ University successfully added to database");

      // Convert the university object to a plain object
      const responseData = {
        ...university,
        english_test_requirements: processedEnglishTests,
        keywords: processedKeywords,
        featured_images: processedFeaturedImages,
        faqs: processedFaqs,
      };

      return new Response(safeJsonStringify(responseData), {
        status: 201,
        headers: {
          'Content-Type': 'application/json'
        }
      });
    } else {
      return Response.json(
        { error: 'University name must be a string' },
        { status: 400 }
      );
    }

  } catch (error) {
    console.error('Error creating university:', error);
    // Rollback transaction if any error occurs
    if (postConnection) {
      await postConnection.rollback();
      console.log("Transaction rolled back due to error:", error);
    }
    return Response.json(
      {
        error: 'Internal server error',
        details: error.message
      },
      { status: 500 }
    );
  } finally {
    // Always release connection back to pool
    if (postConnection) {
      postConnection.release();
    }
  }
}

export async function GET(request: NextRequest) {
  try {

    const getIds = process.env.ALLOWED_DELETE_IDS;

    console.log("IDs", getIds);

    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    const slug = searchParams.get('slug');
    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 scholarship = searchParams.get('scholarship') || '';
    const qualifications = searchParams.get('qualification') || '';
    const popular = searchParams.get('popular');
    const year = searchParams.get('year');
    const active = searchParams.get('active');
    const showInMenu = searchParams.get('showInMenu'); // NEW: Filter by menu visibility
    const sortOrder = searchParams.get('sortOrder') || 'asc';

    console.log("Coming Limit", limit)

    console.log('🔍 University API called with params:', { page, limit, search, country, scholarship, qualifications, popular, year, active, showInMenu, sortOrder });

    // Query to count total universities
    const [rows] = await pool.query<RowDataPacket[]>("SELECT COUNT(*) AS total FROM university_details");
    const totalUniversities = rows[0].total;

    if (totalUniversities === 0) {
      return Response.json({
        success: true,
        data: [],
        message: 'No universities found'
      });
    }

    // If ID is provided, return a single university with its FAQs
    if (id) {
      const [universityRows] = await pool.query<RowDataPacket[]>("SELECT * FROM university_details WHERE id = ?", [id]);
      if (universityRows.length === 0) {
        return Response.json({ error: 'University not found' }, { status: 404 });
      }

      const university = universityRows[0];
      // Convert BigInt fields to string
      university.phone_no = university.phone_no ? university.phone_no.toString() : null;

      // Parse the new text editor fields
      university.overview = university.overview || '';
      university.academic_programs = university.academic_programs || '';
      university.scholarships_financial_support = university.scholarships_financial_support || '';
      university.campus_life_student_experience = university.campus_life_student_experience || '';
      university.why_choose = university.why_choose || '';

      // Get FAQs for this university
      const [faqRows] = await pool.query<RowDataPacket[]>(
        "SELECT id, question, answer FROM university_faqs WHERE university_id = ? ORDER BY id",
        [id]
      );
      university.faqs = faqRows;

      return Response.json(university);
    }

    // If slug is provided, return university by slug with its FAQs
    if (slug) {
      const [universityRows] = await pool.query<RowDataPacket[]>(
        "SELECT * FROM university_details WHERE slug = ? AND display = true AND is_active = true",
        [slug]
      );
      if (universityRows.length === 0) {
        return Response.json({ error: 'University not found' }, { status: 404 });
      }
      const university = universityRows[0];
      // Convert BigInt fields to string
      university.phone_no = university.phone_no ? university.phone_no.toString() : null;

      // Parse the new text editor fields
      university.overview = university.overview || '';
      university.academic_programs = university.academic_programs || '';
      university.scholarships_financial_support = university.scholarships_financial_support || '';
      university.campus_life_student_experience = university.campus_life_student_experience || '';
      university.why_choose = university.why_choose || '';

      // Get FAQs for this university
      const [faqRows] = await pool.query<RowDataPacket[]>(
        "SELECT id, question, answer FROM university_faqs WHERE university_id = ? ORDER BY id",
        [university.id]
      );
      university.faqs = faqRows;

      return Response.json(university);
    }

    // Build where clause for filtering and pagination
    let whereClause = "WHERE display = true";
    const queryParams: any[] = [];

    // Add search filter
    if (search) {
      whereClause += " AND (name LIKE ? OR country LIKE ? OR city LIKE ? OR short_description LIKE ?)";
      queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    // Add country filter
    if (country && country !== 'Select Country') {
      whereClause += " AND country LIKE ?";
      queryParams.push(`%${country}%`);
    }

    // Add scholarship filter
    if (scholarship && scholarship !== '') {
      if (scholarship === 'With Scholarship') {
        whereClause += " AND scholarship = true";
      } else if (scholarship === 'Without Scholarship') {
        whereClause += " AND scholarship = false";
      } else {
        whereClause += " AND scholarship = ?";
        queryParams.push(scholarship === 'true' || scholarship === '1');
      }
    }

    // Add popular filter
    if (popular !== null && popular !== '') {
      whereClause += " AND popular = ?";
      queryParams.push(popular === '1' || popular === 'true');
    }

    // Add active filter
    if (active !== null && active !== '') {
      whereClause += " AND is_active = ?";
      queryParams.push(active === '1' || active === 'true');
    }

    // NEW: Add showInMenu filter
    if (showInMenu !== null && showInMenu !== '') {
      whereClause += " AND show_in_menu = ?";
      queryParams.push(showInMenu === '1' || showInMenu === 'true');
    }

    // Add year filter
    if (year) {
      whereClause += " AND founded_in LIKE ?";
      queryParams.push(`%${year}%`);
    }

    // Add qualifications filter
    if (qualifications && qualifications !== '') {
      const qualificationList = qualifications.split(',').map(q => `%${q.trim()}%`);
      if (qualificationList.length > 0) {
        // Find universities with matching qualifications
        const [qualificationRows] = await pool.query<RowDataPacket[]>(
          `SELECT DISTINCT university_id FROM courses WHERE qualification LIKE ?`,
          [qualificationList]
        );
        if (qualificationRows.length === 0) {
          whereClause += " AND id IN (SELECT 0)";  // This will return no results
        } else {
          const validUniversityIds = qualificationRows.map(row => row.university_id);
          whereClause += " AND id IN (?)";
          queryParams.push(validUniversityIds);
        }
      }
    }

    // Build ORDER BY clause for sorting
    let orderByClause = "ORDER BY ";
    if (sortOrder === 'desc') {
      orderByClause += "name DESC";
    } else {
      orderByClause += "name ASC";
    }

    console.log('🔍 Final WHERE clause:', whereClause);
    console.log('🔍 Query parameters:', queryParams);
    console.log('🔍 ORDER BY clause:', orderByClause);

    // Calculate pagination
    const skip = (page - 1) * limit;

    // Get total count for pagination (after all filters)
    const [countRows] = await pool.query(`SELECT COUNT(*) AS totalCount FROM university_details ${whereClause}`, queryParams);
    const totalCount = countRows[0].totalCount;

    const maxPages = Math.ceil(totalCount / limit);
    if (page > maxPages && maxPages > 0) {
      return Response.json({
        success: false,
        error: 'Page number exceeds available results',
        redirectTo: maxPages,
        totalPages: maxPages,
        totalItems: totalCount
      }, { status: 400 });
    }

    // Get paginated data with the necessary fields
    const [universityRows] = await pool.query<RowDataPacket[]>(
      `SELECT *
      FROM university_details ${whereClause} ${orderByClause} LIMIT ? OFFSET ?`,
      [...queryParams, limit, skip]
    );

    if (universityRows.length === 0) {
      return Response.json({
        success: true,
        data: [],
        message: 'No results found with current filters'
      });
    }

    // Get all university IDs to fetch FAQs in bulk
    const universityIds = universityRows.map(uni => uni.id);
    
    // Fetch FAQs for all universities in one query
    let allFaqs = [];
    if (universityIds.length > 0) {
      const [faqRows] = await pool.query<RowDataPacket[]>(
        `SELECT university_id, question, answer FROM university_faqs WHERE university_id IN (?) ORDER BY university_id, id`,
        [universityIds]
      );
      allFaqs = faqRows;
    }

    // Group FAQs by university_id for easy lookup
    const faqsByUniversityId = {};
    allFaqs.forEach(faq => {
      if (!faqsByUniversityId[faq.university_id]) {
        faqsByUniversityId[faq.university_id] = [];
      }
      faqsByUniversityId[faq.university_id].push({
        id: faq.id,
        question: faq.question,
        answer: faq.answer
      });
    });

    // Process universities to convert BigInt to string and parse JSON fields
    const processedUniversities = universityRows.map(university => ({
      ...university,
      phone_no: university.phone_no ? university.phone_no.toString() : null,
      overview: university.overview || '',
      academic_programs: university.academic_programs || '',
      scholarships_financial_support: university.scholarships_financial_support || '',
      campus_life_student_experience: university.campus_life_student_experience || '',
      why_choose: university.why_choose || '',
      keywords: university.keywords ? JSON.parse(university.keywords) : [],
      featured_images: university.featured_images ? JSON.parse(university.featured_images) : [],
      faqs: faqsByUniversityId[university.id] || []
    }));

    // Calculate pagination metadata
    const totalPages = Math.ceil(totalCount / limit);
    const hasNextPage = page < totalPages;
    const hasPrevPage = page > 1;
    const startItem = skip + 1;
    const endItem = Math.min(skip + processedUniversities.length, totalCount);

    return Response.json({
      success: true,
      data: processedUniversities,
      pagination: {
        currentPage: page,
        totalPages,
        totalItems: totalCount,
        itemsPerPage: limit,
        hasNextPage,
        hasPrevPage,
        startItem,
        endItem
      }
    });

  } catch (error) {
    console.error('🔍 Error in database queries:', error);
    return Response.json({
      success: false,
      error: 'Database query failed',
      details: error.message
    }, { status: 500 });
  }
}