import { NextResponse } from 'next/server';
import pool from '../../../../../../lib/db/db';
import { verifyAuthentication } from '../../../../../utils/verifyAuthentication';
import { S3Client, PutObjectCommand, DeleteObjectCommand } from '@aws-sdk/client-s3';
import { RowDataPacket, OkPacket, ResultSetHeader } from 'mysql2/promise';

// ---------- S3 CLIENT ----------
const s3 = new S3Client({
  region: process.env.AWS_DEFAULT_REGION,
  credentials: {
    accessKeyId: process.env.AWS_ACCESS_KEY_ID!,
    secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY!,
  },
  forcePathStyle: process.env.AWS_USE_PATH_STYLE_ENDPOINT === 'true',
});

// ---------- LOCAL generateS3Key FUNCTION ----------
function generateS3Key(folder: string, fileName: string): string {
  const timestamp = Date.now();
  const sanitizedFileName = fileName.replace(/\s+/g, '_');
  return `${folder}/${timestamp}_${sanitizedFileName}`;
}

// ---------- Helper function to delete file from S3 ----------
async function deleteFromS3(url: string): Promise<boolean> {
  try {
    const bucket = process.env.AWS_BUCKET;
    if (!bucket || !url || !url.includes(bucket)) return false;
    
    const key = url.split(`${bucket}/`)[1];
    if (!key) return false;
    
    await s3.send(
      new DeleteObjectCommand({
        Bucket: bucket,
        Key: key,
      })
    );
    console.log('Deleted old S3 file:', key);
    return true;
  } catch (error) {
    console.error('Error deleting from S3:', error);
    return false;
  }
}

export async function POST(request: Request) {
  const connection = await pool.getConnection();
  
  try {
    console.log('Profile picture upload endpoint called');
    
    const token = request.headers.get('cookie')?.split('university-token=')[1]?.split(';')[0] || null;
    const decoded = await verifyAuthentication(token);

    if (decoded instanceof NextResponse || !decoded || decoded.role !== "consultant") {
      console.log('Unauthorized access attempt');
      return NextResponse.json(
        {
          success: false,
          message: "You are not authorized to perform this action"
        },
        { status: 401 }
      );
    }

    console.log('Consultant ID:', decoded.id);
    
    const formData = await request.formData();
    const file = formData.get('profile_picture') as File;
    const deleteOld = formData.get('delete_old_picture') as string;

    console.log('File received:', file ? `Yes, size: ${file.size}, type: ${file.type}` : 'No');
    console.log('Delete old flag:', deleteOld);

    if (!file && deleteOld !== 'true') {
      return NextResponse.json(
        {
          success: false,
          message: "No file provided or delete action specified"
        },
        { status: 400 }
      );
    }

    await connection.beginTransaction();

    // Get old picture URL
    const [oldData] = await connection.query<RowDataPacket[]>(
      "SELECT profile_picture FROM consultants WHERE user_id = ?",
      [decoded.id]
    );

    console.log('Old data found:', Array.isArray(oldData) && oldData.length > 0 ? 'Yes' : 'No');
    
    let profilePictureUrl: string | null = null;
    let oldPictureUrl: string | null = null;

    if (Array.isArray(oldData) && oldData.length > 0) {
      oldPictureUrl = oldData[0].profile_picture;
      console.log('Old picture URL:', oldPictureUrl);
    }

    if (file && file.size > 0) {
      // Validate file type
      const allowedTypes = ['image/jpeg', 'image/jpg', 'image/png', 'image/gif', 'image/webp'];
      if (!allowedTypes.includes(file.type)) {
        await connection.rollback();
        return NextResponse.json(
          {
            success: false,
            message: "Invalid file type. Please upload JPEG, PNG, GIF, or WebP image."
          },
          { status: 400 }
        );
      }

      // Validate file size (max 5MB)
      const maxSize = 5 * 1024 * 1024; // 5MB
      if (file.size > maxSize) {
        await connection.rollback();
        return NextResponse.json(
          {
            success: false,
            message: "File size too large. Maximum size is 5MB."
          },
          { status: 400 }
        );
      }

      try {
        console.log('Starting S3 upload...');
        console.log('AWS Bucket:', process.env.AWS_BUCKET);
        console.log('AWS Region:', process.env.AWS_DEFAULT_REGION);
        
        const fileName = file.name || 'profile.jpg';
        const s3Key = generateS3Key('consultant-profiles', fileName);
        const buffer = Buffer.from(await file.arrayBuffer());
        const bucket = process.env.AWS_BUCKET;

        if (!bucket) {
          throw new Error('AWS bucket not configured');
        }

        console.log('S3 Key:', s3Key);
        console.log('Buffer size:', buffer.length);
        console.log('Content type:', file.type || 'image/jpeg');

        await s3.send(
          new PutObjectCommand({
            Bucket: bucket,
            Key: s3Key,
            Body: buffer,
            ContentType: file.type || 'image/jpeg',
            // Removed ACL: 'public-read' to match working code
          })
        );

        console.log('S3 upload successful');
        
        // Use the same URL format as working code
        const region = process.env.AWS_DEFAULT_REGION || 'us-east-1';
        profilePictureUrl = `https://${bucket}.s3.${region}.amazonaws.com/${s3Key}`;
        console.log('Generated URL:', profilePictureUrl);
        
        // Delete old picture if exists
        if (oldPictureUrl) {
          console.log('Attempting to delete old picture:', oldPictureUrl);
          await deleteFromS3(oldPictureUrl);
        }
      } catch (uploadError: any) {
        console.error('File upload error details:', uploadError);
        console.error('Error stack:', uploadError.stack);
        await connection.rollback();
        return NextResponse.json(
          { 
            success: false, 
            message: 'Failed to upload profile picture',
            error: uploadError.message 
          },
          { status: 500 }
        );
      }
    } else if (deleteOld === 'true') {
      // Delete existing picture
      if (oldPictureUrl) {
        console.log('Deleting existing picture only:', oldPictureUrl);
        await deleteFromS3(oldPictureUrl);
      }
      profilePictureUrl = null;
      console.log('Profile picture set to null');
    }

    // Update database
    const [result] = await connection.query<ResultSetHeader>(
      `UPDATE consultants 
       SET profile_picture = ?, updated_at = NOW()
       WHERE user_id = ?`,
      [profilePictureUrl, decoded.id]
    );

    console.log('Database update result:', result.affectedRows, 'rows affected');

    if (result.affectedRows === 0) {
      console.log('Consultant record not found, creating new one...');
      // Create consultant record if it doesn't exist
      const [userData] = await connection.query<RowDataPacket[]>(
        "SELECT first_name, last_name FROM users WHERE id = ?",
        [decoded.id]
      );
      
      const fullName = userData.length > 0 
        ? `${userData[0].first_name} ${userData[0].last_name}`
        : 'Consultant';
      
      const [createResult] = await connection.query<ResultSetHeader>(
        `INSERT INTO consultants (
          user_id, name, profile_picture, created_at, updated_at
        ) VALUES (?, ?, ?, NOW(), NOW())`,
        [decoded.id, fullName, profilePictureUrl]
      );

      if (createResult.affectedRows === 0) {
        throw new Error("Failed to create consultant record");
      }
      
      console.log('Created new consultant record');
    }

    await connection.commit();
    console.log('Transaction committed successfully');

    return NextResponse.json(
      { 
        success: true, 
        message: deleteOld === 'true' 
          ? "Profile picture removed successfully" 
          : "Profile picture uploaded successfully",
        data: {
          profile_picture: profilePictureUrl
        }
      },
      { status: 200 }
    );

  } catch (err: any) {
    try {
      await connection.rollback();
      console.log('Transaction rolled back due to error');
    } catch (rollbackErr) {
      console.error("Rollback failed:", rollbackErr);
    }

    console.error('Profile picture upload error:', err);
    console.error('Error stack:', err.stack);
    
    return NextResponse.json(
      { 
        success: false, 
        message: err.message || "Internal server error" 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
    console.log('Database connection released');
  }
}