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

type PageProps = {
  params: Promise<{
    id: string;
  }>;
};

// 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}`;
};

// PUT update course by ID
export async function PUT(req: NextRequest, { params }: PageProps) {
  const putConnection = await pool.getConnection()

  const { id } = await params;

  try {
    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
        }
      );
    }

    const data = await req.json();

    await putConnection.beginTransaction();

    // Check if course name is being changed
    const [existingCourse] = await putConnection.query<RowDataPacket[]>(
      "SELECT name FROM courses WHERE id = ?",
      [id]
    );

    if (existingCourse.length === 0) {
      await putConnection.rollback();
      return NextResponse.json({ success: false, message: 'Course not found' }, { status: 404 });
    }

    const existingName = existingCourse[0].name;
    const isNameChanged = data.name && data.name !== existingName;
    
    let slug = null;
    if (isNameChanged) {
      // Generate new slug if course name changed
      slug = generateSlug(data.name, parseInt(id));
    }

    // Build the SQL UPDATE query - include slug if name changed
    let updateQuery = `
      UPDATE courses 
      SET 
        name = ?, 
        university_id = ?, 
        subject_id = ?, 
        review_detail = ?, 
        qualification = ?, 
        duration = ?, 
        yearly_fee = ?, 
        application_fee = ?, 
        languages = ?, 
        starting_date = ?, 
        deadline = ?, 
        about = ?, 
        entry_requirments = ?, 
        scholarship = ?, 
        sort_order = ?, 
        active = ?, 
        display = ?,
        updated_at = ?
    `;
    
    let queryParams = [
      data.name,
      data.university_id,
      data.subject_id,
      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,
      data.active,
      data.display,
      new Date(), // updated_at timestamp
    ];

    // Add slug to query if name changed
    if (isNameChanged) {
      updateQuery = updateQuery.replace('updated_at = ?', 'slug = ?, updated_at = ?');
      queryParams.splice(queryParams.length - 1, 0, slug); // Insert slug before updated_at
    }

    // Add WHERE clause
    updateQuery += ' WHERE id = ?';
    queryParams.push(id);

    // Execute the query using the pool
    const [result] = await putConnection.query<ResultSetHeader>(updateQuery, queryParams);

    // Check if any rows were affected
    if (result.affectedRows === 0) {
      await putConnection.rollback();
      return NextResponse.json({ success: false, message: 'Course not found or no changes made' }, { status: 404 });
    }

    // ✅ SYNC TO PYTHON API (POSTGRESQL) - PART OF TRANSACTION
    try {
      // Fetch the updated course to get all data for sync
      const [updatedCourseRows] = await putConnection.query(`SELECT * FROM courses WHERE id = ?`, [id]);
      const updatedCourse = updatedCourseRows[0];

      // Prepare data for Python API
      const sendingData = {
        id: parseInt(updatedCourse.id),
        name: updatedCourse.name,
        university_id: updatedCourse.university_id,
        subject_id: updatedCourse.subject_id,
        qualification: updatedCourse.qualification,
        sm_question: updatedCourse.sm_question,
        sm_answer: updatedCourse.sm_answer,
        review_detail: updatedCourse.review_detail,
        duration: updatedCourse.duration,
        yearly_fee: updatedCourse.yearly_fee,
        application_fee: updatedCourse.application_fee,
        languages: updatedCourse.languages,
        starting_date: updatedCourse.starting_date,
        deadline: updatedCourse.deadline,
        about: updatedCourse.about,
        entry_requirements: updatedCourse.entry_requirments,
        scholarship: updatedCourse.scholarship === 1,
        slug: updatedCourse.slug, // Include slug in sync
        sort_order: updatedCourse.sort_order,
        active: Boolean(updatedCourse.active),
        display: Boolean(updatedCourse.display),
        popular: Boolean(updatedCourse.popular),
        created_at: updatedCourse.created_at,
        updated_at: updatedCourse.updated_at
      };

      const syncResponse = await fetch(`${process.env.PYTHON_APP_URL}/api/sync-course`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'X-API-Key': process.env.PYTHON_API_KEY || 'your-api-key-here'
        },
        body: JSON.stringify(sendingData)
      });

      if (!syncResponse.ok) {
        const errorData = await syncResponse.json();
        throw new Error(`PostgreSQL sync failed: ${errorData.detail || syncResponse.statusText}`);
      }

      const syncResult = await syncResponse.json();
      
      if (!syncResult.success) {
        throw new Error(`PostgreSQL sync failed: ${syncResult.error || 'Unknown error'}`);
      }

      console.log('✅ Successfully synced updated course to PostgreSQL:', syncResult);

    } catch (syncError) {
      await putConnection.rollback();
      console.error('❌ Failed to sync to PostgreSQL:', syncError);
      return NextResponse.json(
        { 
          success: false, 
          error: (syncError as Error).message,
          message: "Transaction rolled back - no data was saved in either database"
        },
        { status: 500 }
      );
    }

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

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

    return NextResponse.json({ 
      success: true, 
      message: "Course updated successfully and synced to PostgreSQL",
      data: {
        slugUpdated: isNameChanged,
        newSlug: slug
      }
    });

  } catch (error) {
    console.error('Error updating course:', error);
    
    // Rollback transaction if any error occurs
    if (putConnection) {
      await putConnection.rollback();
    }
    
    return NextResponse.json({ success: false, error: error.message }, { status: 500 });
  } finally {
    // Always release connection back to pool
    if (putConnection) {
      putConnection.release();
    }
  }
}

// DELETE course by ID
export async function DELETE(req: NextRequest, { params }: PageProps) {

  const {id} = await params // Access query parameter

  const deleteConnection = await pool.getConnection();
  
  try {

    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 deleteConnection.beginTransaction();

    const getIds = process.env.ALLOWED_DELETE_IDS;
    const allowedDeleteIds = getIds.split(',').map(id => id.trim());

    function canUserDelete(currentUserId) {
        return allowedDeleteIds.includes(currentUserId.toString());
    }

    if (!canUserDelete(decoded.id)) {
      return NextResponse.json(
        {
          success: false,
          message: "You are not authorized to do this action"
        },
        {
          status: 401
        }
      );
    }

    const [checkID] = await deleteConnection.query<RowDataPacket[]>("SELECT * FROM courses WHERE id = ?", [parseInt(id)]);

    if(checkID.length === 0)
    {
      return NextResponse.json({ error: 'Course not found' }, { status: 404 });
    }

    // Delete the course by id
    const [result] = await pool.query<ResultSetHeader>(
      'DELETE FROM courses WHERE id = ?',
      [id]
    );

    // Check if any row was affected
    if (result.affectedRows === 0) {
      return NextResponse.json({ success: false, message: 'Course not found' }, { status: 404 });
    }

    // make activity log
    await deleteConnection.query("INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", [decoded.id, `Course Name ${checkID[0].name} deleted by user ${decoded.name} and email is ${decoded.email}`]);

    await deleteConnection.commit();

    return NextResponse.json({ success: true, message: "Course deleted successfully" });

  } catch (error) {
    console.error('Error deleting course:', error);
    if (deleteConnection) {
      await deleteConnection.rollback();
      console.log("Transaction rolled back due to error:", error);
    }
    return NextResponse.json({ success: false, error: error.message }, { status: 500 });
  } finally {
    // Always release connection back to pool
    if (deleteConnection) {
      deleteConnection.release();
    }
  }
}