import pool from "@/lib/db";

export async function POST(req) {
  let connection;
  try {
    const formData = await req.formData();
    const metal_name = formData.get("metal_name");
    const unit = formData.get("unit");
    const purity = formData.get("purity") || null;
    const status = formData.get("status") || "1";

    if (!metal_name || !unit) {
      return Response.json(
        { error: "Metal name and unit are required" },
        { status: 400 }
      );
    }

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

    const [result] = await connection.query(
      `INSERT INTO metals (metal_name, unit, purity, status) VALUES (?, ?, ?, ?)`,
      [metal_name, unit, purity, status]
    );

    await connection.commit();
    return Response.json(
      { message: "Metal created successfully", id: result.insertId },
      { status: 201 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to create metal", details: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

export async function GET() {
  let connection;
  try {
    connection = await pool.getConnection();
    
    const [rows] = await connection.query(
      `SELECT id, metal_name, unit, purity, status FROM metals ORDER BY metal_name ASC`
    );

    return Response.json({ rows }, { status: 200 });
  } catch (error) {
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to fetch metals" },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

export async function PUT(req) {
  let connection;
  try {
    const formData = await req.formData();
    const id = formData.get("id");
    const metal_name = formData.get("metal_name");
    const unit = formData.get("unit");
    const purity = formData.get("purity") || null;
    const status = formData.get("status") || "1";

    if (!id || !metal_name || !unit) {
      return Response.json(
        { error: "ID, metal name, and unit are required" },
        { status: 400 }
      );
    }

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

    await connection.query(
      `UPDATE metals SET metal_name = ?, unit = ?, purity = ?, status = ? WHERE id = ?`,
      [metal_name, unit, purity, status, id]
    );

    await connection.commit();
    return Response.json(
      { message: "Metal updated successfully" },
      { status: 200 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to update metal", details: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

export async function DELETE(req) {
  let connection;
  try {
    const { searchParams } = new URL(req.url);
    const id = searchParams.get("id");

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

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

    // Check if metal is used in metal_rates
    const [ratesUsing] = await connection.query(
      `SELECT COUNT(*) as count FROM metal_rates WHERE metal_id = ?`,
      [id]
    );

    if (ratesUsing[0].count > 0) {
      await connection.rollback();
      return Response.json(
        { error: "Cannot delete metal that has assigned rates" },
        { status: 400 }
      );
    }

    await connection.query(`DELETE FROM metals WHERE id = ?`, [id]);

    await connection.commit();
    return Response.json(
      { message: "Metal deleted successfully" },
      { status: 200 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to delete metal", details: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}
