import pool from "@/lib/db";
import { cleanData } from "@/lib/utils";
import { handleFileUploads } from "@/lib/fileUpload";
import { removeImage } from "@/lib/removeImage";
import {
  deleteMetaAndAttributes,
  saveProductMetaFromForm,
} from "../meta/utilMeta";

// export async function GET(req) {
//   const { searchParams } = new URL(req.url);
//   const pageIndexRaw = searchParams.get("pageIndex");
//   const pageSizeRaw = searchParams.get("pageSize");

//   const pageIndex = Number.isNaN(Number(pageIndexRaw))
//     ? 0
//     : parseInt(pageIndexRaw, 10);
//   const pageSize = Number.isNaN(Number(pageSizeRaw))
//     ? 10
//     : parseInt(pageSizeRaw, 10);

//   const filters = searchParams.get("filters");

//   let name = "";
//   if (filters) {
//     try {
//       const parsed = globalJsonParse(filters);
//       name = parsed.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, url, url_target, description, image, offers_percentage,
//              visibility, refids, ref_table,
//              DATE_FORMAT(created_at, '%Y-%m-%d') as created_at
//       FROM collections
//       WHERE 1 = 1
//     `;
//     let countQuery = `SELECT COUNT(*) as count FROM collections WHERE 1 = 1`;
//     const params = [];
//     const countParams = [];

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

//     baseQuery += ` ORDER BY id DESC LIMIT ? OFFSET ?`;
//     params.push(pageSize, offset);

//     const [rows] = await connection.query(baseQuery, params);
//     const [count] = await connection.query(countQuery, countParams);

//     return Response.json(
//       { rows, rowCount: count[0]?.count || 0 },
//       { status: 200 }
//     );
//   } catch (error) {
//     console.error("❌ Get Collection Error:", error);
//     return Response.json(
//       { error: "Failed to fetch collections" },
//       { status: 500 }
//     );
//   } finally {
//     if (connection) connection.release();
//   }
// }

export async function GET(req) {
  const { searchParams } = new URL(req.url);
  const all = searchParams.get("all") === "true";

  const pageIndexRaw = searchParams.get("pageIndex");
  const pageSizeRaw = searchParams.get("pageSize");

  const pageIndex = Number.isNaN(Number(pageIndexRaw))
    ? 0
    : parseInt(pageIndexRaw, 10);
  const pageSize = Number.isNaN(Number(pageSizeRaw))
    ? 10
    : parseInt(pageSizeRaw, 10);

  const filters = searchParams.get("filters");

  let name = "";
  if (filters) {
    try {
      const parsed = globalJsonParse(filters);
      name = parsed.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, url, url_target, description, image, offers_percentage,
             visibility, refids, ref_table,status,
             DATE_FORMAT(created_at, '%Y-%m-%d') as created_at
      FROM collections
      WHERE 1 = 1
    `;

    let countQuery = `SELECT COUNT(*) as count FROM collections WHERE 1 = 1`;

    const params = [];
    const countParams = [];

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

    if (!all) {
      baseQuery += ` ORDER BY id DESC LIMIT ? OFFSET ?`;
      params.push(pageSize, offset);
    } else {
      baseQuery += ` ORDER BY id DESC`;
    }

    const [rows] = await connection.query(baseQuery, params);
    const [count] = await connection.query(countQuery, countParams);

    return Response.json(
      { rows, rowCount: count[0]?.count || 0 },
      { status: 200 }
    );
  } catch (error) {
    console.error("❌ Get Collection Error:", error);
    return Response.json(
      { error: "Failed to fetch collections" },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

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 visibility = {
      show_offers: cleanedData.show_offers == "false" ? 0 : 1,
      show_description: cleanedData.show_description == "false" ? 0 : 1,
      show_button: cleanedData.show_button == "false" ? 0 : 1,
    };

    const [result] = await connection.query(
      `INSERT INTO collections 
        (name, url, url_target, description, image, offers_percentage, visibility, refids, status, ref_table) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        cleanedData.name,
        cleanedData.url,
        cleanedData.url_target || "button",
        cleanedData.description || null,
        uploadedFiles.image || null,
        cleanedData.offers_percentage || null,
        JSON.stringify(visibility),
        cleanedData.refids || null,
        cleanedData.status == "true" ? 1 : 0,
        cleanedData.ref_table,
      ]
    );

    const hasMetas = Array.from(formData.keys()).some((key) =>
      key.startsWith("metas[")
    );

    if (hasMetas) {
      await saveProductMetaFromForm(
        connection,
        formData,
        "collection",
        cleanedData.url,
        uploadedFiles
      );
    }

    await connection.commit();

    return Response.json(
      { message: "Product collection added successfully", id: result.insertId },
      { status: 201 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ Add Collection Error:", error);

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

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

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 folder = body.folder || "default";
    const uploadedFiles = await handleFileUploads(formData);

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

    // Get old image path from DB
    const [existingRows] = await connection.query(
      `SELECT image,url FROM collections WHERE id = ?`,
      [cleanedData.id]
    );
    const existingImage = existingRows?.[0]?.image;

    const newImage = uploadedFiles.image || null;
    let finalImage = newImage || cleanedData.image || null;

    // ✅ Parse cleanedData.image safely
    let parsedImage;
    try {
      parsedImage =
        typeof cleanedData.image === "string"
          ? globalJsonParse(cleanedData.image)
          : cleanedData.image;
    } catch (e) {
      console.warn("⚠️ Could not parse image:", cleanedData.image);
      parsedImage = {};
    }

    const isImageRemoval = parsedImage?.image?.length === 0;

    // ✅ 1. If clearing image (`{ image: [] }`) – delete old file and set null
    if (isImageRemoval && existingImage) {
      removeImage(existingImage);
      finalImage = null;
    }

    // ✅ 2. If uploading new image – delete old one if different
    if (newImage && existingImage && newImage !== existingImage) {
      removeImage(existingImage);
    }

    const visibility = {
      show_offers: cleanedData.show_offers == "false" ? 0 : 1,
      show_description: cleanedData.show_description == "false" ? 0 : 1,
      show_button: cleanedData.show_button == "false" ? 0 : 1,
    };

    // ✅ 3. Perform the update
    await connection.query(
      `UPDATE collections 
       SET name = ?, url = ?, url_target = ?, description = ?, image = ?, 
           offers_percentage = ?, visibility = ?, refids = ?, status = ?, ref_table = ?
       WHERE id = ?`,
      [
        cleanedData.name,
        cleanedData.url,
        cleanedData.url_target || "button",
        cleanedData.description === "null"
          ? null
          : cleanedData.description || null,
        finalImage,
        cleanedData.offers_percentage === "null"
          ? null
          : cleanedData.offers_percentage || null,
        JSON.stringify(visibility),
        cleanedData.refids || null,
        cleanedData.status == "true" ? 1 : 0,
        cleanedData.ref_table || null,
        cleanedData.id,
      ]
    );

    const hasMetas = Array.from(formData.keys()).some((key) =>
      key.startsWith("metas[")
    );

    if (hasMetas) {
      const oldUrl = existingRows?.[0]?.url;
      if (oldUrl && oldUrl !== cleanedData.url) {
        await connection.query(
          `UPDATE metas SET reference_id = ? WHERE reference_type = 'collection' AND reference_id = ?`,
          [cleanedData.url, oldUrl]
        );
      }

      await saveProductMetaFromForm(
        connection,
        formData,
        "collection",
        cleanedData.url,
        uploadedFiles
      );
    }

    await connection.commit();

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

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

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

    const [collections] = await connection.query(
      `SELECT url, image FROM collections WHERE id = ?`,
      [id]
    );

    if (collections.length === 0) {
      await connection.rollback();
      return Response.json({ error: "Collection not found" }, { status: 404 });
    }

    // ✅ 1. Check if this collection ID is used in assign_collections
    const [usedInAssignments] = await connection.query(
      `
      SELECT id FROM assign_collections 
      WHERE JSON_CONTAINS(collection_ids, JSON_ARRAY(?))
      `,
      [id]
    );

    if (usedInAssignments.length > 0) {
      await connection.rollback();
      return Response.json(
        {
          error:
            "Cannot delete collection. It is used in one or more page section assignments.",
        },
        { status: 409 }
      );
    }

    const collectionUrl = collections[0].url;
    const collectionImage = collections[0].image;

    await deleteMetaAndAttributes(connection, "collection", collectionUrl);
    await connection.query(`DELETE FROM collections WHERE id = ?`, [id]);

    // Remove the uploaded image if it exists
    if (collectionImage) {
      removeImage(collectionImage);
    }

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