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

export async function GET() {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.query(
      `SELECT p.*, c.name AS category_name , c.id AS category_id ,c.category_status
       FROM products p 
       LEFT JOIN categories c ON p.category_id = c.id
      WHERE p.status = 1
      ORDER BY p.id DESC`
    );

    return NextResponse.json({ rows, rowCount: rows.length });
  } catch (error) {
    console.error("❌ Product GET error:", error);
    return NextResponse.json(
      { error: "Failed to fetch products" },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function POST(req) {
  const connection = await pool.getConnection();
  try {
    const formData = await req.formData();

    const body = Object.fromEntries(formData.entries());
    const cleaned = cleanData(body);
    const uploaded = await handleFileUploads(formData);

    // Validate name uniqueness
    const [existing] = await connection.query(
      `SELECT id FROM products WHERE name = ?`,
      [cleaned.name]
    );
    if (existing.length > 0) {
      return NextResponse.json(
        { error: "Product name already exists." },
        { status: 409 }
      );
    }

    await connection.beginTransaction();
    // 🔗 Get category_url for product_url prefix
    let fullProductUrl = cleaned.product_url || null;
    if (cleaned.category_id) {
      const [[category]] = await connection.query(
        `SELECT category_url FROM categories WHERE id = ?`,
        [cleaned.category_id]
      );
      if (category?.category_url) {
        fullProductUrl = `${category.category_url}/${cleaned.product_url}`;
      }
    }

    const [result] = await connection.query(
      `INSERT INTO products (name, description, category_id, product_image, video_url, product_url, status, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [
        cleaned.name,
        cleaned.description,
        cleaned.category_id || null,
        uploaded.product_image?.join(",") || null,
        cleaned.video_url || null,
        fullProductUrl,
        +cleaned.status ? 1 : 0,
      ]
    );

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

    if (hasMetas) {
      await saveProductMetaFromForm(
        connection,
        formData,
        "product",
        fullProductUrl,
        uploaded // pass uploaded file map from handleFileUploads
      );
    }
    await connection.commit();
    return NextResponse.json(
      { message: "Product created", id: result.insertId },
      { status: 201 }
    );
  } catch (error) {
    await connection.rollback();
    console.error("❌ Product POST error:", error);
    return NextResponse.json(
      { error: "Failed to create product" },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function PATCH(req) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const formData = await req.formData();

    const body = Object.fromEntries(formData.entries());
    const cleaned = cleanData(body);
    const uploaded = await handleFileUploads(formData);

    const existingImages = formData.getAll("existing_images") || [];

    if (!cleaned.id) {
      return NextResponse.json(
        { error: "Missing product ID" },
        { status: 400 }
      );
    }

    const [[current]] = await connection.query(
      `SELECT * FROM products WHERE id = ?`,
      [cleaned.id]
    );
    if (!current) {
      return NextResponse.json({ error: "Product not found" }, { status: 404 });
    }

    if (cleaned.name && cleaned.name !== current.name) {
      const [existing] = await connection.query(
        `SELECT id FROM products WHERE name = ? AND id != ?`,
        [cleaned.name, cleaned.id]
      );
      if (existing.length > 0) {
        return NextResponse.json(
          { error: "Product name already exists" },
          { status: 409 }
        );
      }
    }

    let fullProductUrl = cleaned.product_url || null;
    if (cleaned.category_id) {
      const [[category]] = await connection.query(
        `SELECT category_url FROM categories WHERE id = ?`,
        [cleaned.category_id]
      );

      const prefix = category?.category_url;
      if (prefix) {
        const lastSegment = cleaned.product_url.split("/").pop();
        fullProductUrl = `${prefix}/${lastSegment}`;
      }
    }

    // Merge existing + newly uploaded images
    const finalImages = [...existingImages, ...(uploaded.product_image || [])];

    await connection.query(
      `UPDATE products SET
        name = ?,
        description = ?,
        category_id = ?,
        product_image = ?,
        video_url = ?,
        product_url = ?,
        status = ?,
        updated_at = NOW()
      WHERE id = ?`,
      [
        cleaned.name,
        cleaned.description || null,
        cleaned.category_id,
        finalImages.join(","),
        cleaned.video_url || null,
        fullProductUrl,
        +cleaned.status ? 1 : 0,
        cleaned.id,
      ]
    );

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

    if (hasMetas) {
      if (current.product_url !== fullProductUrl) {
        // Update reference_id in metas table if product_url changed
        await connection.query(
          `UPDATE metas SET reference_id = ? WHERE reference_type = 'product' AND reference_id = ?`,
          [fullProductUrl, current.product_url]
        );
      }

      await saveProductMetaFromForm(
        connection,
        formData,
        "product",
        fullProductUrl,
        uploaded // pass uploaded file map from handleFileUploads
      );
    }

    await connection.commit();
    return NextResponse.json({ message: "Product updated" });
  } catch (error) {
    await connection.rollback();
    console.error("❌ Product PATCH error:", error);
    return NextResponse.json(
      { error: "Failed to update product" },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function DELETE(req) {
  const connection = await pool.getConnection();

  try {
    const { id } = await req.json();
    if (!id) {
      return NextResponse.json(
        { error: "Missing product ID" },
        { status: 400 }
      );
    }

    await connection.beginTransaction();

    const [productRows] = await connection.query(
      `SELECT product_url FROM products WHERE id = ?`,
      [id]
    );

    if (productRows.length === 0) {
      await connection.rollback();
      return NextResponse.json({ error: "Product not found" }, { status: 404 });
    }

    const product_url = productRows[0].product_url;

    // Find collections that reference this product
    const [collections] = await connection.query(
      `SELECT id, refids FROM collections 
       WHERE ref_table = 'products' AND JSON_CONTAINS(refids, JSON_ARRAY(?))`,
      [id]
    );

    for (const collection of collections) {
      let refidsArray = collection.refids;

      let updatedRefids;

      if (Array.isArray(refidsArray)) {
        updatedRefids = refidsArray.filter((item) => item != id);
      } else {
        updatedRefids = refidsArray;
      }

      await connection.query(`UPDATE collections SET refids = ? WHERE id = ?`, [
        JSON.stringify(updatedRefids),
        collection.id,
      ]);
    }

    // Now proceed with product deletion
    const [result] = await connection.query(
      `DELETE FROM products WHERE id = ?`,
      [id]
    );

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

    await deleteMetaAndAttributes(connection, "product", product_url);
    await connection.commit();

    return NextResponse.json({
      message: "Product deleted successfully and removed from collections",
      collectionsUpdated: collections.length,
    });
  } catch (error) {
    await connection.rollback();
    console.error("❌ Product DELETE error:", error);
    return NextResponse.json(
      { error: "Failed to delete product", details: error.message },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}
