// /app/api/internal/subjects/[id]/route.js
import { NextRequest, NextResponse } from "next/server";
import pool from "../../../../../lib/db/db";
import { ResultSetHeader, RowDataPacket } from "mysql2";

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

// ------------------ PUT update subject ------------------
export async function PUT(req: Request, { params }: PageProps) {
  let connection;

  try {
    const body = await req.json();
    const { id } = await params;

    if (!id) {
      return NextResponse.json(
        { success: false, error: "Subject ID is required" },
        { status: 400 }
      );
    }

    connection = await pool.getConnection();
    await connection.beginTransaction();

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

    if (body.name !== undefined) {
      updateFields.push("name = ?");
      updateValues.push(body.name);
    }

    if (body.icon !== undefined) {
      updateFields.push("icon = ?");
      updateValues.push(body.icon || null);
    }

    updateFields.push("updated_at = ?");
    updateValues.push(new Date());

    if (updateFields.length === 0) {
      return NextResponse.json(
        { success: false, error: "No fields to update" },
        { status: 400 }
      );
    }

    updateValues.push(id);

    const [result] = await connection.execute(
      `UPDATE subjects SET ${updateFields.join(", ")} WHERE id = ?`,
      updateValues
    );

    if ((result as any).affectedRows === 0) {
      await connection.rollback();
      return NextResponse.json(
        { success: false, error: "Subject not found" },
        { status: 404 }
      );
    }

    // Fetch updated subject
    const [rows] = await connection.execute(`SELECT * FROM subjects WHERE id = ?`, [id]);
    const updatedSubject = (rows as any[])[0];

    // ✅ SYNC TO PYTHON API (POSTGRESQL) - PART OF TRANSACTION
    try {
      const syncResponse = await fetch(`${process.env.PYTHON_APP_URL}/api/sync-subject`, {
        method: 'PUT',
        headers: {
          'Content-Type': 'application/json',
          'X-API-Key': process.env.PYTHON_API_KEY || 'your-api-key'
        },
        body: JSON.stringify({
          id: parseInt(updatedSubject.id),
          name: updatedSubject.name,
          icon: updatedSubject.icon,
          created_at: updatedSubject.created_at,
          updated_at: updatedSubject.updated_at
        })
      });

      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 subject to PostgreSQL:', syncResult);

    } catch (syncError) {
      await connection.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 }
      );
    }

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

    return NextResponse.json({ 
      success: true, 
      data: updatedSubject,
      message: "Subject updated successfully and synced to PostgreSQL"
    });

  } catch (error: any) {
    // Rollback transaction if any error occurs
    if (connection) {
      await connection.rollback();
    }
    
    console.error("Error updating subject:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

// ------------------ DELETE subject ------------------
export async function DELETE(req, { params }: PageProps) {
  
  const connection = await pool.getConnection();

  try {

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

    if (!id) {
      return NextResponse.json(
        { success: false, error: "Subject ID is required" },
        { status: 400 }
      );
    }

    const [result] = await connection.execute<ResultSetHeader>(
      `DELETE FROM subjects WHERE id = ?`,
      [id]
    );

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

    return NextResponse.json({ 
      success: true, 
      message: "Subject deleted successfully" 
    });
  } catch (error) {
    console.error("Error deleting subject:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}