import pool from "@/lib/db";

export async function POST(req) {
  let connection;
  try {
    const formData = await req.formData();
    const metal_id = formData.get("metal_id");
    const rate = formData.get("rate");
    const status = formData.get("status") || "1";

    if (!metal_id || !rate) {
      return Response.json(
        { error: "Metal ID and rate are required" },
        { status: 400 }
      );
    }

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

    // Check if rate already exists for this metal
    const [existing] = await connection.query(
      `SELECT id FROM metal_rates WHERE metal_id = ?`,
      [metal_id]
    );

    if (existing.length > 0) {
      // Update existing rate
      await connection.query(
        `UPDATE metal_rates SET rate = ?, status = ?, updated_at = NOW() WHERE metal_id = ?`,
        [rate, status, metal_id]
      );
    } else {
      // Insert new rate
      await connection.query(
        `INSERT INTO metal_rates (metal_id, rate, status) VALUES (?, ?, ?)`,
        [metal_id, rate, status]
      );
    }

    await connection.commit();
    return Response.json(
      { message: "Metal rate saved successfully" },
      { status: 200 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to save metal rate", 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 
        mr.id,
        mr.metal_id,
        mr.rate,
        mr.status,
        m.metal_name,
        m.unit,
        m.purity,
        mr.created_at,
        mr.updated_at
      FROM metal_rates mr
      INNER JOIN metals m ON mr.metal_id = m.id
      WHERE m.status = 1
      ORDER BY m.metal_name ASC`
    );

    return Response.json({ rows }, { status: 200 });
  } catch (error) {
    console.error("❌ API Error:", error);
    return Response.json(
      { error: "Failed to fetch metal rates" },
      { 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();

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

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