// app/api/frontend/foreign-student/profile/route.js
import pool from '../../../../../lib/db/db';
import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket } from 'mysql2';
import jwt from 'jsonwebtoken';
import { z } from 'zod';

interface updatedFields {
  [key: string]: string | number | null;
}

const profileSchema = z.object({
  name: z.string()
    .min(2, 'Name must be at least 2 characters')
    .max(255, 'Name cannot exceed 255 characters')
    .optional(),

  phone: z.string()
    .regex(/^[\+]?[1-9][\d]{0,15}$/, 'Invalid phone number')
    .optional()
    .or(z.literal('')),

  country_id: z.coerce.number()
    .int('Country must be a valid ID')
    .positive('Country is required')
    .optional(),

  about_me: z.string()
    .max(2000, 'About me cannot exceed 2000 characters')
    .optional()
    .or(z.literal('')),

  profile_picture: z.string()
    .optional()
    .or(z.literal('')),

  hour_rate: z.coerce.number()
    .min(0, 'Hour rate cannot be negative')
    .max(1000, 'Hour rate cannot exceed $1000')
    .optional()
    .default(0),

  gender: z.enum(['male', 'female', 'other'], {
    errorMap: () => ({ message: 'Gender must be male, female, or other' })
  }).optional(),

  experience: z.string()
    .max(5000, 'Experience cannot exceed 5000 characters')
    .optional()
    .or(z.literal('')),

  last_education: z.enum([
    'high_school',
    'bachelors', 
    'masters',
    'phd',
    'diploma',
    'certificate'
  ], {
    errorMap: () => ({ message: 'Invalid education level' })
  }).optional(),
});

export async function GET(req: NextRequest) {
  try {
    // Get token from cookie
    const token = req.cookies.get('university-token')?.value;
    
    if (!token) {
      return NextResponse.json(
        { 
          success: false, 
          message: 'Authentication required' 
        },
        { status: 401 }
      );
    }

    // Verify token
    let decoded;
    try {
      decoded = jwt.verify(token, process.env.JWT_SECRET as string);
    } catch (err) {
      return NextResponse.json(
        { 
          success: false, 
          message: 'Invalid or expired token' 
        },
        { status: 401 }
      );
    }

    // Check if user is a foreign student
    if (decoded.role !== 'foreign-student') {
      return NextResponse.json(
        { 
          success: false, 
          message: 'Access denied' 
        },
        { status: 403 }
      );
    }

    // Fetch foreign student data
    const [studentData] = await pool.query<RowDataPacket[]>(
      `SELECT 
        id, name, email, phone, country_id, about_me, profile_picture, 
        hour_rate, gender, experience, last_education, 
        is_active, email_verified, created_at, updated_at
       FROM foreign_students 
       WHERE id = ? AND is_active = 1 
       LIMIT 1`, 
      [decoded.id]
    );

    if (studentData.length === 0) {
      return NextResponse.json(
        { 
          success: false, 
          message: 'Student not found or account inactive' 
        },
        { status: 404 }
      );
    }

    // Remove sensitive data
    const { ...userData } = studentData[0];

    return NextResponse.json({
      success: true,
      message: 'Profile fetched successfully',
      data: userData
    }, { status: 200 });

  } catch (err: any) {
    console.error('Get profile error:', err);
    return NextResponse.json(
      { 
        success: false, 
        message: 'Internal server error' 
      },
      { status: 500 }
    );
  }
}

export async function PUT(req: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    // Get token from cookie
    const token = req.cookies.get('university-token')?.value;
    
    if (!token) {
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Authentication required' 
        },
        { status: 401 }
      );
    }

    // Verify token
    let decoded;
    try {
      decoded = jwt.verify(token, process.env.JWT_SECRET as string);
    } catch (err) {
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Invalid or expired token' 
        },
        { status: 401 }
      );
    }

    // Check if user is a foreign student
    if (decoded.role !== 'foreign-student') {
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Access denied' 
        },
        { status: 403 }
      );
    }

    const body = await req.json();
    
    // Validate input
    const validatedData = profileSchema.parse(body);

    // Start transaction
    await connection.beginTransaction();

    // Check if foreign student exists and get current data
    const [existingStudent] = await connection.query<RowDataPacket[]>(
      `SELECT 
        id, name, email, phone, country_id, about_me, profile_picture, 
        hour_rate, gender, experience, last_education
       FROM foreign_students 
       WHERE id = ? AND is_active = 1 
       LIMIT 1`,
      [decoded.id]
    );

    if (existingStudent.length === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Student not found or account inactive' 
        },
        { status: 404 }
      );
    }

    const currentData = existingStudent[0];

    // Build update query dynamically
    const updateFields = [];
    const updateValues = [];

    // Track which fields are being updated for CRM
    const updatedFields: updatedFields = {};

    // Add fields to update
    if (validatedData.name !== undefined && validatedData.name !== currentData.name) {
      updateFields.push('name = ?');
      updateValues.push(validatedData.name);
      updatedFields.name = validatedData.name;
    }

    if (validatedData.phone !== undefined && validatedData.phone !== currentData.phone) {
      updateFields.push('phone = ?');
      updateValues.push(validatedData.phone || null);
      updatedFields.phone = validatedData.phone || null;
    }

    if (validatedData.country_id !== undefined && validatedData.country_id !== currentData.country_id) {
      updateFields.push('country_id = ?');
      updateValues.push(validatedData.country_id || null);
      updatedFields.country_id = validatedData.country_id || null;
    }

    if (validatedData.about_me !== undefined && validatedData.about_me !== currentData.about_me) {
      updateFields.push('about_me = ?');
      updateValues.push(validatedData.about_me || null);
      updatedFields.about_me = validatedData.about_me || null;
    }

    if (validatedData.profile_picture !== undefined && validatedData.profile_picture !== currentData.profile_picture) {
      updateFields.push('profile_picture = ?');
      updateValues.push(validatedData.profile_picture || null);
      updatedFields.profile_picture = validatedData.profile_picture || null;
    }

    if (validatedData.hour_rate !== undefined && validatedData.hour_rate !== currentData.hour_rate) {
      updateFields.push('hour_rate = ?');
      updateValues.push(validatedData.hour_rate);
      updatedFields.hour_rate = validatedData.hour_rate;
    }

    if (validatedData.gender !== undefined && validatedData.gender !== currentData.gender) {
      updateFields.push('gender = ?');
      updateValues.push(validatedData.gender || null);
      updatedFields.gender = validatedData.gender || null;
    }

    if (validatedData.experience !== undefined && validatedData.experience !== currentData.experience) {
      updateFields.push('experience = ?');
      updateValues.push(validatedData.experience || null);
      updatedFields.experience = validatedData.experience || null;
    }

    if (validatedData.last_education !== undefined && validatedData.last_education !== currentData.last_education) {
      updateFields.push('last_education = ?');
      updateValues.push(validatedData.last_education || null);
      updatedFields.last_education = validatedData.last_education || null;
    }

    // Only proceed if there are fields to update
    if (updateFields.length === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json({
        success: true,
        message: 'No changes detected',
        data: currentData
      }, { status: 200 });
    }

    // Always update the updated_at timestamp
    updateFields.push('updated_at = NOW()');

    // Add the WHERE condition value
    updateValues.push(decoded.id);

    // Execute update
    const query = `UPDATE foreign_students SET ${updateFields.join(', ')} WHERE id = ?`;
    
    await connection.query(query, updateValues);

    // Fetch updated data for CRM payload
    const [updatedStudent] = await connection.query<RowDataPacket[]>(
      `SELECT 
        fs.*, 
        c.name as country_name
       FROM foreign_students fs
       LEFT JOIN countries_db c ON fs.country_id = c.id
       WHERE fs.id = ? 
       LIMIT 1`, 
      [decoded.id]
    );

    if (updatedStudent.length === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Failed to fetch updated data' 
        },
        { status: 500 }
      );
    }

    const updatedData = updatedStudent[0];

    // Prepare CRM payload
    const crmPayload = {
      user_id: updatedData.id,
      name: updatedData.name,
      email: updatedData.email,
      phone: updatedData.phone,
      country: updatedData.country_name || '',
      about_me: updatedData.about_me,
      profile_picture: updatedData.profile_picture,
      hour_rate: updatedData.hour_rate,
      gender: updatedData.gender,
      experience: updatedData.experience,
      last_education: updatedData.last_education,
      updated_at: new Date().toISOString()
    };

    // Call CRM API
    let crmResponse;
    try {
      crmResponse = await fetch(
        "https://crm-universitiespage.com/reactapis/api/website_foreign_student/update_record",
        {
          method: "PUT",
          body: JSON.stringify(crmPayload),
          headers: {
            'Content-Type': 'application/json',
            'Authorization': `${process.env.CRM_API_KEY}`,
          },
        }
      );

      if (!crmResponse.ok) {
        throw new Error(`CRM API responded with status: ${crmResponse.status}`);
      }

      const crmData = await crmResponse.json();

      // Check CRM response (adjust based on actual CRM response structure)
      if (crmData?.status_code !== 200 && crmData?.status !== 'success') {
        throw new Error(`CRM API returned error: ${crmData.message || 'Unknown error'}`);
      }

      // Commit transaction only if CRM API succeeds
      await connection.commit();

    } catch (crmError) {
      // Rollback transaction if CRM API fails
      await connection.rollback();
      console.error('CRM API Error:', crmError);
      
      connection.release();
      return NextResponse.json(
        {
          success: false,
          message: "Profile update failed to sync with CRM. Changes were not saved. Please try again later."
        },
        { status: 502 }
      );
    }

    // Remove sensitive data before sending response
    const { password_hash, ...userResponse } = updatedData;

    connection.release();
    
    return NextResponse.json({
      success: true,
      message: 'Profile updated successfully',
      data: userResponse,
      crm_synced: true
    }, { status: 200 });

  } catch (err: any) {
    // Rollback on any error
    if (connection) {
      await connection.rollback();
    }
    
    console.error('Update profile error:', err);
    
    // Zod validation errors
    if (err instanceof z.ZodError) {
      const errors = {};
      err.errors.forEach(error => {
        const path = error.path.join('.');
        errors[path] = error.message;
      });
      
      connection.release();
      return NextResponse.json(
        { 
          success: false, 
          message: 'Validation failed', 
          errors 
        },
        { status: 400 }
      );
    }
    
    // Database or other errors
    connection.release();
    return NextResponse.json(
      { 
        success: false, 
        message: 'Failed to update profile' 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}