import { NextResponse } from 'next/server';
import pool from '../../../../lib/db/db';
import { RowDataPacket } from 'mysql2';

// GET - Fetch countries with pagination and search
export async function GET(request) {
  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 15;
    const search = searchParams.get('search') || '';
    const showInMenu = searchParams.get('showInMenu') || ''; // NEW: Menu visibility filter
    
    console.log('🔍 Countries API called with params:', { page, limit, search, showInMenu });
    
    // Validate parameters
    if (page < 1) {
      return NextResponse.json({ 
        success: false, 
        error: "Page number must be greater than 0" 
      }, { status: 400 });
    }
    
    if (limit < 1 || limit > 100) {
      return NextResponse.json({ 
        success: false, 
        error: "Limit must be between 1 and 100" 
      }, { status: 400 });
    }
    
    // Calculate skip value for pagination
    const skip = (page - 1) * limit;
    
    // Build WHERE clause for search
    let whereClause = 'WHERE 1';
    const queryParams = [];
    
    if (search) {
      // Convert search to lowercase for case-insensitive search
      const searchLower = `%${search.toLowerCase()}%`;
      whereClause = `WHERE (LOWER(country) LIKE ? OR LOWER(code) LIKE ? OR LOWER(currency) LIKE ? OR LOWER(currency_symbol) LIKE ?)`;
      queryParams.push(searchLower, searchLower, searchLower, searchLower);
      console.log('🔍 Search WHERE clause:', whereClause);
    }

    // NEW: Add menu visibility filter
    if (showInMenu) {
      if (whereClause === 'WHERE 1') {
        whereClause = `WHERE show_in_menu = ?`;
      } else {
        whereClause += ` AND show_in_menu = ?`;
      }
      queryParams.push(showInMenu === '1' ? 1 : 0);
    }
    
    // Get total count for pagination
    const countQuery = `SELECT COUNT(*) AS totalCount FROM countries ${whereClause}`;
    const [countRows] = await pool.query(countQuery, queryParams);
    const totalCount = countRows[0].totalCount;
    console.log('🔍 Total count result:', totalCount);
    
    // Get paginated data - ADD show_in_menu to SELECT
    const countriesQuery = `
      SELECT id, code, country, selected, before_description, after_description, image, 
             currency, currency_symbol, consultation_fee, consultation_fee_discount, 
             show_in_menu, created_at, updated
      FROM countries ${whereClause} 
      ORDER BY created_at DESC 
      LIMIT ? OFFSET ?
    `;
    
    const paginatedQueryParams = [...queryParams, limit, skip];
    
    console.log('🔍 Executing countries query with:', { skip, take: limit, whereClause });
    const [countriesRows] = await pool.query<RowDataPacket[]>(countriesQuery, paginatedQueryParams);
    console.log('🔍 Found countries:', countriesRows.length);
    
    // Calculate pagination metadata
    const totalPages = Math.ceil(totalCount / limit);
    
    const response = { 
      success: true,
      data: countriesRows,
      meta: {
        totalItems: totalCount,
        totalPages: totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1
      }
    };
    
    console.log('🔍 Returning response with:', { 
      success: response.success, 
      dataCount: response.data.length, 
      meta: response.meta 
    });
    
    return NextResponse.json(response);
  } catch (err) {
    console.error("❌ GET API Error:", err);
    
    // Handle specific MySQL errors
    if (err.code === 'ECONNREFUSED') {
      return NextResponse.json({ 
        success: false,
        error: "Database connection failed",
        details: "Cannot connect to database server"
      }, { status: 500 });
    }
    
    return NextResponse.json({ 
      success: false,
      error: "Failed to fetch countries",
      details: err.message
    }, { status: 500 });
  }
}

export async function POST(req) {
  let connection;
  
  try {
    const body = await req.json();
    console.log("📥 Received data:", body);
    
    const {
      name, // Frontend sends 'name' but DB expects 'country'
      code,
      currency,
      currencySymbol,
      consultationFee,
      consultationDiscountFee,
      featureImage,
      before_description,
      after_description,
      show_in_menu // NEW: Menu visibility field
    } = body;

    // Validate required fields
    if (!name || !code || !currency || !currencySymbol) {
      console.log("❌ Missing required fields:", { name, code, currency, currencySymbol });
      return NextResponse.json({ 
        error: "Country name, code, currency code, and currency symbol are required" 
      }, { status: 400 });
    }

    // Handle image data - store the uploaded image URL
    let imageData = null;
    if (featureImage) {
      // If it's a URL (from upload), store it
      if (featureImage.startsWith('/uploads/') || featureImage.startsWith('http')) {
        imageData = featureImage;
        console.log("✅ Image URL stored:", imageData);
      } else if (featureImage.startsWith('data:image')) {
        // For backward compatibility with base64 (not recommended)
        const base64Size = Math.ceil((featureImage.length * 3) / 4);
        const maxSize = 500 * 1024; // 500KB limit
        
        if (base64Size > maxSize) {
          console.log("⚠️ Base64 image too large:", base64Size, "bytes");
          return NextResponse.json({ 
            error: "Image is too large. Please use the file upload instead." 
          }, { status: 400 });
        }
        
        imageData = featureImage;
        console.log("⚠️ Using base64 image (not recommended):", base64Size, "bytes");
      } else {
        // If it's a URL, store it
        imageData = featureImage;
      }
    }

    // Get database connection
    connection = await pool.getConnection();

    // Check if country code or name already exists
    const [existingCountries] = await connection.execute(
      'SELECT id FROM countries WHERE code = ? OR LOWER(country) = LOWER(?)',
      [code.toUpperCase().trim(), name.trim()]
    );

    if (existingCountries.length > 0) {
      console.log("❌ Country code or name already exists:", { code, name });
      return NextResponse.json({ 
        error: "Country code or name already exists" 
      }, { status: 409 });
    }

    const countryData = {
      country: name.trim(),
      code: code.toUpperCase().trim(),
      currency: currency.trim(),
      currency_symbol: currencySymbol.trim(),
      consultation_fee: parseFloat(consultationFee || 0),
      consultation_fee_discount: parseFloat(consultationDiscountFee || 0),
      before_description: before_description || null,
      after_description: after_description || null,
      image: imageData,
      selected: false,
      show_in_menu: show_in_menu !== undefined ? (show_in_menu ? 1 : 0) : 1, // NEW: Default to visible
    };

    console.log("📝 Creating country with data:", {
      ...countryData,
      image: imageData ? "Image provided" : "No image",
      before_description: before_description ? "Before description provided" : "No before description",
      after_description: after_description ? "After description provided" : "No after description"
    });

    // Insert new country - ADD show_in_menu to INSERT
    const [result] = await connection.execute(
      `INSERT INTO countries (
        country, 
        code, 
        currency, 
        currency_symbol,
        consultation_fee, 
        consultation_fee_discount, 
        before_description,
        after_description,
        image, 
        selected,
        show_in_menu,
        created_at,
        updated
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [
        countryData.country,
        countryData.code,
        countryData.currency,
        countryData.currency_symbol,
        countryData.consultation_fee,
        countryData.consultation_fee_discount,
        countryData.before_description,
        countryData.after_description,
        countryData.image,
        countryData.selected ? 1 : 0,
        countryData.show_in_menu, // NEW
      ]
    );

    // Get the newly created country
    const [newCountries] = await connection.execute(
      'SELECT * FROM countries WHERE id = ?',
      [result.insertId]
    );

    const newCountry = newCountries[0];

    // Convert MySQL tinyint back to boolean for response
    if (newCountry) {
      newCountry.selected = Boolean(newCountry.selected);
      newCountry.show_in_menu = Boolean(newCountry.show_in_menu); // NEW
    }

    console.log("✅ Country created successfully:", newCountry);
    return NextResponse.json(newCountry, { status: 201 });

  } catch (err) {
    console.error("❌ POST API Error:", err);
    console.error("❌ Error details:", {
      code: err.code,
      message: err.message,
      sqlMessage: err.sqlMessage,
      sqlState: err.sqlState
    });
    
    // Handle specific MySQL errors
    if (err.code === 'ER_DUP_ENTRY') {
      return NextResponse.json({ 
        error: "Country code or name already exists" 
      }, { status: 409 });
    }
    
    if (err.code === 'ER_DATA_TOO_LONG') {
      return NextResponse.json({ 
        error: "Image data too large. Please use a smaller image or upload to cloud storage." 
      }, { status: 400 });
    }

    if (err.code === 'ER_NO_REFERENCED_ROW_2' || err.code === 'ER_NO_REFERENCED_ROW') {
      return NextResponse.json({ 
        error: "Invalid data provided" 
      }, { status: 400 });
    }

    if (err.code === 'ER_TABLE_EXISTS_ERROR' || err.code === 'ER_BAD_TABLE_ERROR') {
      return NextResponse.json({ 
        error: "Database schema mismatch. Please check your database structure." 
      }, { status: 500 });
    }

    // Handle connection errors
    if (err.code === 'ECONNREFUSED' || err.code === 'ENOTFOUND') {
      return NextResponse.json({ 
        error: "Database connection failed. Please try again later." 
      }, { status: 503 });
    }

    return NextResponse.json({ 
      error: "Failed to create country. Please check your data and try again." 
    }, { status: 500 });

  } finally {
    // Always release the connection back to the pool
    if (connection) {
      connection.release();
    }
  }
}

// DELETE - Delete country by ID
export async function DELETE(req) {
  let connection;
  
  try {
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json({ error: "Country ID is required" }, { status: 400 });
    }

    // Get database connection
    connection = await pool.getConnection();

    // Check if country exists
    const [existingCountries] = await connection.execute(
      'SELECT id FROM countries WHERE id = ?',
      [parseInt(id)]
    );

    if (existingCountries.length === 0) {
      return NextResponse.json({ 
        error: "Country not found" 
      }, { status: 404 });
    }

    // Delete the country
    const [result] = await connection.execute(
      'DELETE FROM countries WHERE id = ?',
      [parseInt(id)]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json({ 
        error: "Country not found" 
      }, { status: 404 });
    }

    console.log("✅ Country deleted successfully, ID:", id);
    return NextResponse.json({ message: "Country deleted successfully" });

  } catch (err) {
    console.error("❌ DELETE API Error:", err);
    console.error("❌ Error details:", {
      code: err.code,
      message: err.message,
      sqlMessage: err.sqlMessage
    });
    
    // Handle specific MySQL errors
    if (err.code === 'ER_ROW_IS_REFERENCED_2' || err.code === 'ER_ROW_IS_REFERENCED') {
      return NextResponse.json({ 
        error: "Cannot delete country because it is referenced by other records" 
      }, { status: 409 });
    }

    if (err.code === 'ER_NO_REFERENCED_ROW_2' || err.code === 'ER_NO_REFERENCED_ROW') {
      return NextResponse.json({ 
        error: "Country not found" 
      }, { status: 404 });
    }

    // Handle connection errors
    if (err.code === 'ECONNREFUSED' || err.code === 'ENOTFOUND') {
      return NextResponse.json({ 
        error: "Database connection failed. Please try again later." 
      }, { status: 503 });
    }

    return NextResponse.json({ 
      error: "Failed to delete country" 
    }, { status: 500 });

  } finally {
    // Always release the connection back to the pool
    if (connection) {
      connection.release();
    }
  }
}

// PUT - Update country by ID
export async function PUT(req) {
  let connection;
  
  try {
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    console.log("🔍 Update request received for country ID:", id);
    
    if (!id) {
      console.log("❌ Missing country ID in request");
      return NextResponse.json({ 
        error: "Country ID is required",
        details: "Please provide a valid country ID in the URL parameters"
      }, { status: 400 });
    }

    // Validate ID format
    const countryId = parseInt(id);
    if (isNaN(countryId) || countryId <= 0) {
      console.log("❌ Invalid country ID format:", id);
      return NextResponse.json({ 
        error: "Invalid country ID format",
        details: "Country ID must be a positive number"
      }, { status: 400 });
    }

    let body;
    try {
      body = await req.json();
      console.log("📥 Request body received:", JSON.stringify(body, null, 2));
    } catch (parseError) {
      console.log("❌ JSON parse error:", parseError);
      return NextResponse.json({ 
        error: "Invalid JSON in request body",
        details: "Please check the format of your request data"
      }, { status: 400 });
    }
    
    const {
      name,
      code,
      currency,
      currencySymbol,
      consultationFee,
      consultationDiscountFee,
      featureImage,
      before_description,
      after_description
    } = body;

    // Enhanced validation with detailed error messages
    const missingFields = [];
    if (!name) missingFields.push('name');
    if (!code) missingFields.push('code');
    if (!currency) missingFields.push('currency');
    if (!currencySymbol) missingFields.push('currencySymbol');

    if (missingFields.length > 0) {
      console.log("❌ Missing required fields:", missingFields);
      return NextResponse.json({ 
        error: "Missing required fields",
        details: `The following fields are required: ${missingFields.join(', ')}`,
        missingFields: missingFields
      }, { status: 400 });
    }

    // Validate field formats
    const validationErrors = [];
    
    if (typeof name !== 'string' || name.trim().length === 0) {
      validationErrors.push('Name must be a non-empty string');
    }
    
    if (typeof code !== 'string' || code.trim().length !== 2) {
      validationErrors.push('Country code must be a 2-letter string');
    }
    
    if (typeof currency !== 'string' || currency.trim().length === 0) {
      validationErrors.push('Currency must be a non-empty string');
    }
    
    if (typeof currencySymbol !== 'string' || currencySymbol.trim().length === 0) {
      validationErrors.push('Currency symbol must be a non-empty string');
    }

    if (consultationFee !== undefined && isNaN(parseFloat(consultationFee))) {
      validationErrors.push('Consultation fee must be a valid number');
    }

    if (consultationDiscountFee !== undefined && isNaN(parseFloat(consultationDiscountFee))) {
      validationErrors.push('Consultation discount fee must be a valid number');
    }

    if (validationErrors.length > 0) {
      console.log("❌ Validation errors:", validationErrors);
      return NextResponse.json({ 
        error: "Validation failed",
        details: "One or more fields have invalid format",
        validationErrors: validationErrors
      }, { status: 400 });
    }

    // Get database connection
    console.log("🔌 Acquiring database connection...");
    connection = await pool.getConnection();
    console.log("✅ Database connection acquired");

    // Check if country exists
    console.log(`🔍 Checking if country exists with ID: ${countryId}`);
    const [existingCountries] = await connection.execute(
      'SELECT id, country, code, image FROM countries WHERE id = ?',
      [countryId]
    );

    if (existingCountries.length === 0) {
      console.log(`❌ Country not found with ID: ${countryId}`);
      return NextResponse.json({ 
        error: "Country not found",
        details: `No country found with ID: ${countryId}`,
        countryId: countryId
      }, { status: 404 });
    }

    const currentCountry = existingCountries[0];
    console.log(`✅ Found existing country: ${currentCountry.country} (${currentCountry.code})`);

    // Check for duplicate country code or name
    console.log(`🔍 Checking for duplicates with code: ${code}, name: ${name}`);
    const [duplicateCountries] = await connection.execute(
      'SELECT id, country, code FROM countries WHERE (code = ? OR LOWER(country) = LOWER(?)) AND id != ?',
      [code.toUpperCase().trim(), name.trim(), countryId]
    );

    if (duplicateCountries.length > 0) {
      const duplicates = duplicateCountries.map(c => `${c.country} (${c.code})`);
      console.log("❌ Duplicate countries found:", duplicates);
      return NextResponse.json({ 
        error: "Country code or name already exists",
        details: "Another country with the same code or name already exists",
        conflictingCountries: duplicates
      }, { status: 409 });
    }

    // Prepare update data
    const updateData = {
      country: name.trim(),
      code: code.toUpperCase().trim(),
      currency: currency.trim(),
      currency_symbol: currencySymbol.trim(),
      consultation_fee: consultationFee !== undefined ? parseFloat(consultationFee) : currentCountry.consultation_fee,
      consultation_fee_discount: consultationDiscountFee !== undefined ? parseFloat(consultationDiscountFee) : currentCountry.consultation_fee_discount,
      before_description: before_description !== undefined ? before_description : currentCountry.before_description,
      after_description: after_description !== undefined ? after_description : currentCountry.after_description,
      image: featureImage !== undefined ? featureImage : currentCountry.image,
    };

    console.log("🔄 Preparing to update country with data:", updateData);

    // Update the country
    const [result] = await connection.execute(
      `UPDATE countries SET 
        country = ?, 
        code = ?, 
        currency = ?, 
        currency_symbol = ?,
        consultation_fee = ?, 
        consultation_fee_discount = ?, 
        before_description = ?,
        after_description = ?,
        image = ?,
        updated = NOW()
      WHERE id = ?`,
      [
        updateData.country,
        updateData.code,
        updateData.currency,
        updateData.currency_symbol,
        updateData.consultation_fee,
        updateData.consultation_fee_discount,
        updateData.before_description,
        updateData.after_description,
        updateData.image,
        countryId
      ]
    );

    console.log(`✅ Database update result:`, {
      affectedRows: result.affectedRows,
      changedRows: result.changedRows,
      warningCount: result.warningCount
    });

    if (result.affectedRows === 0) {
      console.log(`❌ No rows affected - country may have been deleted: ${countryId}`);
      return NextResponse.json({ 
        error: "Update failed",
        details: "No changes were made to the country. It may have been deleted.",
        countryId: countryId
      }, { status: 404 });
    }

    if (result.changedRows === 0) {
      console.log(`ℹ️  No changes detected - data may be identical: ${countryId}`);
      // This is not necessarily an error, but worth logging
    }

    // Get the updated country
    const [updatedCountries] = await connection.execute(
      'SELECT * FROM countries WHERE id = ?',
      [countryId]
    );

    const updatedCountry = updatedCountries[0];

    if (!updatedCountry) {
      console.log(`❌ Failed to retrieve updated country: ${countryId}`);
      return NextResponse.json({ 
        error: "Update verification failed",
        details: "Country was updated but could not be retrieved",
        countryId: countryId
      }, { status: 500 });
    }

    // Convert MySQL tinyint back to boolean for response
    if (updatedCountry.selected !== undefined) {
      updatedCountry.selected = Boolean(updatedCountry.selected);
    }

    console.log("✅ Country updated successfully:", {
      id: updatedCountry.id,
      country: updatedCountry.country,
      code: updatedCountry.code
    });

    return NextResponse.json({
      success: true,
      message: "Country updated successfully",
      data: updatedCountry
    });

  } catch (err) {
    console.error("❌ PUT API Error:", err);
    
    // Enhanced error logging
    const errorDetails = {
      name: err.name,
      message: err.message,
      code: err.code,
      sqlMessage: err.sqlMessage,
      sqlState: err.sqlState,
      stack: err.stack
    };
    
    console.error("🔍 Detailed error analysis:", errorDetails);
    
    // Handle specific MySQL errors with better messages
    if (err.code === 'ER_DUP_ENTRY') {
      return NextResponse.json({ 
        error: "Duplicate entry",
        details: "A country with this code or name already exists",
        mysqlError: err.sqlMessage
      }, { status: 409 });
    }
    
    if (err.code === 'ER_DATA_TOO_LONG') {
      return NextResponse.json({ 
        error: "Data too long",
        details: "One of the fields exceeds the maximum length. The image data might be too large.",
        suggestion: "Try using a smaller image or upload to cloud storage and provide a URL instead"
      }, { status: 400 });
    }

    if (err.code === 'ER_NO_REFERENCED_ROW_2' || err.code === 'ER_NO_REFERENCED_ROW') {
      return NextResponse.json({ 
        error: "Referenced data not found",
        details: "The update references data that doesn't exist",
        mysqlError: err.sqlMessage
      }, { status: 400 });
    }

    if (err.code === 'ER_BAD_FIELD_ERROR' || err.code === 'ER_BAD_TABLE_ERROR') {
      return NextResponse.json({ 
        error: "Database schema error",
        details: "There's a mismatch between the code and database structure",
        suggestion: "Check if the database table and columns exist with the expected names",
        mysqlError: err.sqlMessage
      }, { status: 500 });
    }

    // Handle connection errors
    if (err.code === 'ECONNREFUSED' || err.code === 'ENOTFOUND') {
      return NextResponse.json({ 
        error: "Database connection failed",
        details: "Unable to connect to the database server",
        suggestion: "Check if the database is running and connection settings are correct"
      }, { status: 503 });
    }

    if (err.code === 'ER_ACCESS_DENIED_ERROR') {
      return NextResponse.json({ 
        error: "Database access denied",
        details: "Invalid database credentials or insufficient permissions",
        suggestion: "Verify database username and password"
      }, { status: 500 });
    }

    // Generic error with detailed information for debugging
    return NextResponse.json({ 
      error: "Failed to update country",
      details: "An unexpected error occurred while updating the country",
      errorType: err.name,
      debugInfo: process.env.NODE_ENV === 'development' ? errorDetails : errorDetails,
      suggestion: "Please check the request data and try again. If the problem persists, contact support."
    }, { status: 500 });

  } finally {
    // Always release the connection back to the pool
    if (connection) {
      console.log("🔌 Releasing database connection");
      connection.release();
    }
  }
}