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

export async function GET(request: NextRequest) {
  let connection;
  
  try {
    connection = await pool.getConnection();

    const { searchParams } = new URL(request.url);
    const page = Number.parseInt(searchParams.get("page") || "1", 10) || 1;
    const limit = Number.parseInt(searchParams.get("limit") || "15", 10) || 15;
    const search = (searchParams.get("search") || "").trim();
    const skip = (page - 1) * limit;

    console.log('🔍 API Request Params:', { page, limit, search, skip });

    // WHERE + params for safe, index-friendly filtering
    let whereSql = "";
    const whereParams = [];
    if (search) {
      whereSql = "WHERE name LIKE ?";
      whereParams.push(`%${search}%`);
    }

    // Count query using the same connection
    const countQuery = `SELECT COUNT(*) AS totalCount FROM subjects ${whereSql}`;
    console.log('🔍 Count Query:', countQuery, whereParams);
    
    const [countRows] = await connection.execute(countQuery, whereParams);
    const totalCount = countRows[0]?.totalCount ?? 0;

    console.log('🔍 Total Count:', totalCount);

    // Page data query
    const dataQuery = `
      SELECT *
      FROM subjects
      ${whereSql}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    console.log('🔍 Data Query:', dataQuery, dataParams);

    const [subjects] = await connection.execute(dataQuery, dataParams);

    const totalPages = Math.ceil(totalCount / limit);

    console.log('📦 API Response:', {
      dataCount: subjects.length,
      totalCount,
      totalPages,
      currentPage: page
    });

    return NextResponse.json(
      {
        success: true,
        data: subjects,
        meta: {
          totalItems: totalCount,
          totalPages,
          currentPage: page,
          itemsPerPage: limit,
          hasNextPage: page < totalPages,
          hasPreviousPage: page > 1,
        },
      },
      {
        status: 200,
      }
    );
  } catch (error) {
    console.error("GET /subjects error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch subjects", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function POST(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();
    
    const body = await req.json();
    
    if (!body.name) {
      return NextResponse.json(
        { success: false, error: "Subject name is required" },
        { status: 400 }
      );
    }

    const [result] = await connection.execute(
      `INSERT INTO subjects (name, icon, created_at, updated_at) VALUES (?, ?, ?, ?)`,
      [
        body.name,
        body.icon || null,
        new Date(),
        new Date(),
      ]
    );

    const insertId = result.insertId;
    const [rows] = await connection.query(`SELECT * FROM subjects WHERE id = ?`, [insertId]);
    const newSubject = rows[0];

    // ✅ NEW: Sync to Python API (PostgreSQL) - PART OF TRANSACTION
    try {
      const syncResponse = await fetch(`${process.env.PYTHON_APP_URL}/api/sync-subject`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'X-API-Key': process.env.PYTHON_API_KEY
        },
        body: JSON.stringify({
          id: newSubject.id,
          name: newSubject.name,
          icon: newSubject.icon,
          created_at: newSubject.created_at,
          updated_at: newSubject.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'}`);
      }

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

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

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

// PUT - Update subject
export async function PUT(request: NextRequest) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();
    
    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Subject ID is required" },
        { status: 400 }
      );
    }

    const body = await request.json();
    
    if (!body.name) {
      return NextResponse.json(
        { success: false, error: "Subject name is required" },
        { status: 400 }
      );
    }

    const [result] = await connection.execute(
      `UPDATE subjects SET name = ?, icon = ?, updated_at = ? WHERE id = ?`,
      [
        body.name,
        body.icon || null,
        new Date(),
        id
      ]
    );

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

    const [rows] = await connection.query(`SELECT * FROM subjects WHERE id = ?`, [id]);
    const updatedSubject = rows[0];

    // ✅ NEW: 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
        },
        body: JSON.stringify({
          id: 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'}`);
      }

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

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

    return NextResponse.json({ 
      success: true, 
      data: updatedSubject 
    });
  } catch (error) {
    // 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 - Delete subject
export async function DELETE(request: NextRequest) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Subject ID is required" },
        { status: 400 }
      );
    }

    const [result] = await connection.execute(
      `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();
    }
  }
}