import pool from "@/lib/db";
import { handleFileUploads } from "@/lib/fileUpload";
import { cleanData } from "@/lib/utils";
import { removeImage } from "@/lib/removeImage";

// ✅ ADD Scheme
export async function POST(req) {
  let connection;
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();

    const formData = await req.formData();
    const body = Object.fromEntries(formData.entries());
    const cleanedData = cleanData(body);
    const uploadedFiles = await handleFileUploads(formData);

    const normalized = cleanedData.alias.replace(/\s+/g, "").toLowerCase();

    // ✅ Check if alias already exists
    const [existing] = await connection.query(
      `SELECT id FROM schemes WHERE REPLACE(LOWER(alias), ' ', '') = ?`,
      [normalized]
    );
    if (existing.length > 0) {
      return Response.json(
        { error: "Alias already exists. Please use a unique alias." },
        { status: 400 }
      );
    }

    const [result] = await connection.query(
      `INSERT INTO schemes (name, alias, image) VALUES (?, ?, ?)`,
      [cleanedData.name, cleanedData.alias, uploadedFiles.image || null]
    );

    await connection.commit();
    return Response.json(
      { message: "Scheme added successfully", id: result.insertId },
      { status: 201 }
    );
  } catch (error) {
    if (connection) await connection.rollback();

    console.error("❌ Scheme Add Error:", error);

    if (error.code === "ER_DUP_ENTRY") {
      return Response.json(
        { error: "Alias must be unique. This alias already exists." },
        { status: 400 }
      );
    }

    return Response.json(
      { error: "Failed to add scheme", details: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

// ✅ GET Schemes (with pagination + search)
export async function GET(req) {
  const { searchParams } = new URL(req.url);
  let pageIndex = parseInt(searchParams.get("pageIndex")) || 0;
  let pageSize = parseInt(searchParams.get("pageSize")) || 10;
  const filters = searchParams.get("filters");

  let name = "";
  if (filters) {
    try {
      const parsedFilters = globalJsonParse(filters);
      name = parsedFilters.name || "";
    } catch {
      return Response.json(
        { error: "Invalid filters format." },
        { status: 400 }
      );
    }
  }

  const offset = pageIndex * pageSize;

  let connection;
  try {
    connection = await pool.getConnection();

    let baseQuery = `
      SELECT 
        id, name, alias, image,
        DATE_FORMAT(created_at, '%Y-%m-%d') as created_at
      FROM schemes
      WHERE 1 = 1
    `;
    let countQuery = `SELECT COUNT(*) as count FROM schemes WHERE 1 = 1`;

    const queryParams = [];
    const countParams = [];

    if (name) {
      baseQuery += ` AND name LIKE ?`;
      countQuery += ` AND name LIKE ?`;
      queryParams.push(`%${name}%`);
      countParams.push(`%${name}%`);
    }

    baseQuery += ` ORDER BY id ASC LIMIT ? OFFSET ?`;
    queryParams.push(pageSize, offset);

    const [rows] = await connection.query(baseQuery, queryParams);
    const [countResult] = await connection.query(countQuery, countParams);
    const rowCount = countResult[0]?.count || 0;

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

// ✅ DELETE Scheme
export async function DELETE(req) {
  const { id } = await req.json();
  if (!id)
    return Response.json({ error: "Missing scheme ID" }, { status: 400 });

  let connection;
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();

    // Fetch the image path before deletion
    const [scheme] = await connection.query(
      `SELECT image FROM schemes WHERE id = ?`,
      [id]
    );

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

    // Remove the uploaded image if it exists
    if (scheme.length > 0 && scheme[0].image) {
      removeImage(scheme[0].image);
    }

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

// ✅ EDIT Scheme
export async function PATCH(req) {
  let connection;
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();

    const formData = await req.formData();
    const body = Object.fromEntries(formData.entries());
    const cleanedData = cleanData(body);
    const uploadedFiles = await handleFileUploads(formData);

    if (!cleanedData.id) {
      return Response.json({ error: "Missing scheme ID" }, { status: 400 });
    }

    const normalized = cleanedData.alias.replace(/\s+/g, "").toLowerCase();

    // ✅ Check if alias already exists
    // const [existing] = await connection.query(
    //   `SELECT id FROM schemes WHERE REPLACE(LOWER(alias), ' ', '') = ?`,
    //   [normalized]
    // );
    // if (existing.length > 0) {
    //   return Response.json(
    //     { error: "Alias already exists. Please use a unique alias." },
    //     { status: 400 }
    //   );
    // }

    // Fetch old image if a new one is being uploaded
    if (uploadedFiles.image) {
      const [oldScheme] = await connection.query(
        `SELECT image FROM schemes WHERE id = ?`,
        [cleanedData.id]
      );
      if (oldScheme.length > 0 && oldScheme[0].image) {
        removeImage(oldScheme[0].image);
      }
    }

    const [update] = await connection.query(
      `UPDATE schemes SET name = ?, alias = ?, image = ? WHERE id = ?`,
      [
        cleanedData.name,
        cleanedData.alias,
        uploadedFiles.image || cleanedData.image || null,
        cleanedData.id,
      ]
    );

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