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

// export async function GET() {
//   const connection = await pool.getConnection();

//   try {
//     const [rows] = await connection.query(`
//       SELECT id, name, parent_id, category_url, category_status, category_image
//       FROM categories
//     `);

//     const map = {};
//     const roots = [];

//     rows.forEach((row) => {
//       map[row.id] = { ...row, children: [] };
//     });

//     rows.forEach((row) => {
//       if (row.parent_id && map[row.parent_id]) {
//         map[row.parent_id].children.push(map[row.id]);
//       } else {
//         roots.push(map[row.id]);
//       }
//     });

//     return NextResponse.json(roots);
//   } catch (error) {
//     console.error("Error fetching categories:", error);
//     return NextResponse.json(
//       { error: "Failed to fetch categories" },
//       { status: 500 }
//     );
//   } finally {
//     connection.release();
//   }
// }

// export async function GET(request) {
//   const connection = await pool.getConnection();

//   try {
//     const { searchParams } = new URL(request.url);

//     const tree = searchParams.get("tree") === "true";
//     const all = searchParams.get("all") === "true";
//     const pageIndex = parseInt(searchParams.get("pageIndex") || "0");
//     const pageSize = parseInt(searchParams.get("pageSize") || "10");
//     const filters = globalJsonParse(searchParams.get("filters") || "{}");

//     let baseQuery = `
//       SELECT id, name, parent_id, category_url, category_status, category_image
//       FROM categories
//       WHERE 1 = 1
//     `;

//     // Optional filtering
//     if (filters.name) {
//       baseQuery += ` AND name LIKE '%${filters.name}%'`;
//     }

//     const [allRows] = await connection.query(baseQuery);

//     if (tree) {
//       const map = {};
//       const roots = [];

//       allRows.forEach((row) => {
//         map[row.id] = { ...row, children: [] };
//       });

//       allRows.forEach((row) => {
//         if (row.parent_id && map[row.parent_id]) {
//           map[row.parent_id].children.push(map[row.id]);
//         } else {
//           roots.push(map[row.id]);
//         }
//       });

//       return NextResponse.json(roots);
//     }

//     if (all) {
//       return NextResponse.json({
//         rows: allRows,
//         rowsCount: allRows.length,
//         pageIndex: 0,
//         pageSize: allRows.length,
//       });
//     }

//     // Flat with pagination
//     const start = pageIndex * pageSize;
//     const paginatedRows = allRows.slice(start, start + pageSize);

//     return NextResponse.json({
//       rows: paginatedRows,
//       rowsCount: allRows.length,
//       pageIndex,
//       pageSize,
//     });
//   } catch (error) {
//     console.error("Error fetching categories:", error);
//     return NextResponse.json(
//       { error: "Failed to fetch categories" },
//       { status: 500 }
//     );
//   } finally {
//     connection.release();
//   }
// }

// export async function GET(request) {
//   const connection = await pool.getConnection();

//   try {
//     const { searchParams } = new URL(request.url);

//     const tree = searchParams.get("tree") === "true";
//     const all = searchParams.get("all") === "true";
//     const pageIndex = parseInt(searchParams.get("pageIndex") || "0");
//     const pageSize = parseInt(searchParams.get("pageSize") || "10");
//     const filters = globalJsonParse(searchParams.get("filters") || "{}");

//     let baseQuery = `
//       SELECT id, name, parent_id, category_url, category_status, category_image, description
//       FROM categories
//       WHERE 1 = 1
//     `;

//     if (filters.name) {
//       baseQuery += ` AND name LIKE '%${filters.name}%'`;
//     }

//     const [allRows] = await connection.query(baseQuery);

//     // 🧠 Map category ID → category
//     const idToCategory = Object.fromEntries(
//       allRows.map((row) => [row.id, row])
//     );

//     // 🔁 Build full parent chain name: "Gold → Rings → Red Stone"
//     const buildCategoryPath = (row) => {
//       const path = [row.name];
//       let current = row;
//       while (current.parent_id && idToCategory[current.parent_id]) {
//         current = idToCategory[current.parent_id];
//         path.unshift(current.name);
//       }
//       return path.join(" → ");
//     };

//     const rowsWithName = allRows.map((row) => ({
//       ...row,
//       category_name: buildCategoryPath(row),
//     }));

//     if (tree) {
//       const map = {};
//       const roots = [];

//       rowsWithName.forEach((row) => {
//         map[row.id] = { ...row, children: [] };
//       });

//       rowsWithName.forEach((row) => {
//         if (row.parent_id && map[row.parent_id]) {
//           map[row.parent_id].children.push(map[row.id]);
//         } else {
//           roots.push(map[row.id]);
//         }
//       });

//       return NextResponse.json(roots);
//     }

//     if (all) {
//       return NextResponse.json({
//         rows: rowsWithName,
//         rowsCount: rowsWithName.length,
//         pageIndex: 0,
//         pageSize: rowsWithName.length,
//       });
//     }

//     const start = pageIndex * pageSize;
//     const paginatedRows = rowsWithName.slice(start, start + pageSize);

//     return NextResponse.json({
//       rows: paginatedRows,
//       rowsCount: rowsWithName.length,
//       pageIndex,
//       pageSize,
//     });
//   } catch (error) {
//     if (connection) await connection.rollback();
//     console.error("Error fetching categories:", error);
//     return NextResponse.json(
//       { error: "Failed to fetch categories" },
//       { status: 500 }
//     );
//   } finally {
//     if (connection) connection.release();
//   }
// }

export async function GET(request) {
  try {
    const { searchParams } = new URL(request.url);

    const tree = searchParams.get("tree") === "true";
    const all = searchParams.get("all") === "true";
    const pageIndex = parseInt(searchParams.get("pageIndex") || "0");
    const pageSize = parseInt(searchParams.get("pageSize") || "10");
    const filters = globalJsonParse(searchParams.get("filters") || "{}");

    let baseQuery = `
      SELECT id, name, parent_id, category_url, category_status, category_image, description
      FROM categories
      WHERE 1 = 1
    `;

    if (filters.name) {
      baseQuery += ` AND name LIKE '%${filters.name}%'`;
    }

    const [allRows] = await pool.query(baseQuery);

    // 🧠 Map category ID → category
    const idToCategory = Object.fromEntries(
      allRows.map((row) => [row.id, row])
    );

    // 🔁 Build full parent chain name: "Gold → Rings → Red Stone"
    const buildCategoryPath = (row) => {
      const path = [row.name];
      let current = row;
      while (current.parent_id && idToCategory[current.parent_id]) {
        current = idToCategory[current.parent_id];
        path.unshift(current.name);
      }
      return path.join(" → ");
    };

    const rowsWithName = allRows.map((row) => ({
      ...row,
      category_name: buildCategoryPath(row),
    }));

    if (tree) {
      const map = {};
      const roots = [];

      rowsWithName.forEach((row) => {
        map[row.id] = { ...row, children: [] };
      });

      rowsWithName.forEach((row) => {
        if (row.parent_id && map[row.parent_id]) {
          map[row.parent_id].children.push(map[row.id]);
        } else {
          roots.push(map[row.id]);
        }
      });

      return NextResponse.json(roots);
    }

    if (all) {
      return NextResponse.json({
        rows: rowsWithName,
        rowsCount: rowsWithName.length,
        pageIndex: 0,
        pageSize: rowsWithName.length,
      });
    }

    const start = pageIndex * pageSize;
    const paginatedRows = rowsWithName.slice(start, start + pageSize);

    return NextResponse.json({
      rows: paginatedRows,
      rowsCount: rowsWithName.length,
      pageIndex,
      pageSize,
    });
  } catch (error) {
    console.error("Error fetching categories:", error);
    return NextResponse.json(
      { error: "Failed to fetch categories" },
      { status: 500 }
    );
  }
}

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 uploadedFiles = await handleFileUploads(formData);
    const imagePath = uploadedFiles?.category_image || null;

    const [existing] = await pool.query(
      `SELECT id FROM categories WHERE name = ? AND parent_id ${cleaned.parent_id ? "= ?" : "IS NULL"}`,
      cleaned.parent_id ? [cleaned.name, cleaned.parent_id] : [cleaned.name]
    );

    if (existing.length > 0) {
      return Response.json(
        {
          error:
            "A category with this name already exists under the selected parent.",
        },
        { status: 409 }
      );
    }

    // Before insert
    let fullCategoryUrl = cleaned.category_url || null;

    if (cleaned.parent_id) {
      const [parent] = await connection.query(
        `SELECT category_url FROM categories WHERE id = ?`,
        [cleaned.parent_id]
      );
      if (parent.length > 0 && parent[0].category_url) {
        fullCategoryUrl = `${parent[0].category_url}/${fullCategoryUrl}`;
      }
    }

    // Insert into categories
    const [result] = await connection.query(
      `INSERT INTO categories (name, parent_id, category_url, category_image, category_status, description, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [
        cleaned.name,
        cleaned.parent_id || null,
        fullCategoryUrl,
        imagePath || null,
        cleaned.category_status,
        cleaned.description,
      ]
    );

    const newId = result.insertId;

    // Closure Table: Self-reference
    await connection.query(
      `INSERT INTO category_closure (ancestor_id, descendant_id, depth)
       VALUES (?, ?, 0)`,
      [newId, newId]
    );

    // Closure Table: Inherit ancestors from parent
    if (cleaned.parent_id) {
      await connection.query(
        `INSERT INTO category_closure (ancestor_id, descendant_id, depth)
         SELECT ancestor_id, ?, depth + 1
         FROM category_closure
         WHERE descendant_id = ?`,
        [newId, cleaned.parent_id]
      );
    }

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

    if (hasMetas) {
      await saveProductMetaFromForm(
        connection,
        formData,
        "category",
        fullCategoryUrl,
        uploadedFiles
      );
    }

    connection.release();
    return NextResponse.json(
      { message: "Category created successfully", id: newId },
      { status: 201 }
    );
  } catch (error) {
    connection.release();
    console.error("❌ Category creation error:", error);
    return NextResponse.json(
      { error: "Failed to create category", details: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) 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 categoryId = cleaned.id;

    const uploadedFiles = await handleFileUploads(formData);
    const imagePath = uploadedFiles?.category_image;

    // Get current category data
    const [currentCategory] = await connection.query(
      "SELECT parent_id, category_url, category_image FROM categories WHERE id = ?",
      [categoryId]
    );

    const oldParentId = currentCategory[0]?.parent_id;
    const oldCategoryUrl = currentCategory[0]?.category_url;
    const oldCategoryImage = currentCategory[0]?.category_image;
    const newParentId = cleaned.parent_id || null;

    const [existing] = await connection.query(
      `SELECT id FROM categories 
       WHERE name = ? 
       AND parent_id ${newParentId ? "= ?" : "IS NULL"}
       AND id != ?`,
      newParentId
        ? [cleaned.name, newParentId, categoryId]
        : [cleaned.name, categoryId]
    );

    if (existing.length > 0) {
      await connection.rollback();
      return NextResponse.json(
        {
          error:
            "A category with this name already exists under the selected parent.",
        },
        { status: 409 }
      );
    }

    // Before insert
    let fullCategoryUrl = cleaned.category_url || null;

    if (cleaned.parent_id) {
      const [parent] = await connection.query(
        `SELECT category_url FROM categories WHERE id = ?`,
        [cleaned.parent_id]
      );

      if (parent.length > 0 && parent[0].category_url) {
        const parentUrl = parent[0].category_url;
        // Avoid duplicating parent prefix
        if (!fullCategoryUrl.startsWith(`${parentUrl}/`)) {
          fullCategoryUrl = `${parentUrl}/${fullCategoryUrl}`;
        }
      }
    }

    // Remove old image if a new one is being uploaded
    if (imagePath && oldCategoryImage) {
      removeImage(oldCategoryImage);
    }

    // ✅ Update category
    const updateQuery = `
      UPDATE categories 
      SET 
        name = ?,
        parent_id = ?,
        category_url = ?,
        ${imagePath ? "category_image = ?," : ""}
        category_status = ?,
        description = ?,
        updated_at = NOW()
      WHERE id = ?
    `;

    const updateParams = [
      cleaned.name,
      newParentId,
      fullCategoryUrl,
      ...(imagePath ? [imagePath] : []),
      +cleaned.category_status ? 1 : 0,
      cleaned.description,
      categoryId,
    ];

    await connection.query(updateQuery, updateParams);

    // ✅ Update closure table if parent has changed
    if (oldParentId !== newParentId) {
      // Remove all ancestor relations except self
      await connection.query(
        `DELETE FROM category_closure 
         WHERE descendant_id = ? 
         AND ancestor_id != descendant_id`,
        [categoryId]
      );

      // Insert new ancestor relationships
      if (newParentId) {
        await connection.query(
          `INSERT IGNORE INTO category_closure (ancestor_id, descendant_id, depth)
           SELECT cc.ancestor_id, ?, cc.depth + 1
           FROM category_closure cc
           WHERE cc.descendant_id = ?
           UNION ALL
           SELECT ?, ?, 1`,
          [categoryId, newParentId, newParentId, categoryId]
        );
      }
    }

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

    if (hasMetas) {
      if (oldCategoryUrl !== fullCategoryUrl) {
        await connection.query(
          `UPDATE metas SET reference_id = ? WHERE reference_type = 'category' AND reference_id = ?`,
          [fullCategoryUrl, oldCategoryUrl]
        );
      }

      await saveProductMetaFromForm(
        connection,
        formData,
        "category",
        fullCategoryUrl,
        uploadedFiles
      );
    }

    await connection.commit();

    return NextResponse.json(
      { message: "Category updated successfully" },
      { status: 200 }
    );
  } catch (error) {
    await connection.rollback();
    console.error("❌ Category update error:", error);
    return NextResponse.json(
      { error: "Failed to update category", details: error.message },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

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

  try {
    const { id: categoryId } = await req.json();
    await connection.beginTransaction();

    // 1. Get parent_id of category
    const [[categoryData] = []] = await connection.query(
      `SELECT parent_id, category_url, category_image FROM categories WHERE id = ?`,
      [categoryId]
    );

    if (!categoryData) {
      throw new Error("Category not found");
    }

    const parent_id = categoryData.parent_id;
    const category_url = categoryData.category_url;
    const category_image = categoryData.category_image;

    const [[productRef]] = await connection.query(
      `SELECT COUNT(*) AS count FROM products WHERE category_id = ?`,
      [categoryId]
    );

    if (productRef.count > 0) {
      await connection.rollback();
      return NextResponse.json(
        {
          error:
            "This category is assigned to one or more products. Please reassign or remove those products before deleting this category.",
        },
        { status: 400 }
      );
    }

    // 2. Get all direct children of this category
    const [children] = await connection.query(
      `SELECT c.id, c.name
       FROM categories c
       JOIN category_closure cc ON c.id = cc.descendant_id
       WHERE cc.ancestor_id = ? AND cc.depth = 1`,
      [categoryId]
    );

    // ✅ 3. Check for name conflicts under the new parent
    if (children.length > 0) {
      for (const child of children) {
        const [[conflict]] = await connection.query(
          `SELECT id FROM categories 
           WHERE name = ? 
           AND parent_id ${parent_id ? "= ?" : "IS NULL"} 
           AND id != ?`,
          parent_id ? [child.name, parent_id, child.id] : [child.name, child.id]
        );

        if (conflict) {
          await connection.rollback();
          return NextResponse.json(
            {
              error: `Cannot delete category. A sibling with the name "${child.name}" already exists under the new parent.`,
            },
            { status: 409 }
          );
        }
      }
    }

    // 4. Reassign children to parent and rebuild closure table
    if (children.length > 0) {
      const childIds = children.map((c) => c.id);

      // Update categories table
      await connection.query(
        `UPDATE categories SET parent_id = ? WHERE id IN (?)`,
        [parent_id, childIds]
      );

      // Remove old closure paths (except self)
      await connection.query(
        `DELETE FROM category_closure 
         WHERE descendant_id IN (?) 
         AND ancestor_id != descendant_id`,
        [childIds]
      );

      if (parent_id) {
        const [ancestors] = await connection.query(
          `SELECT ancestor_id, depth FROM category_closure WHERE descendant_id = ?`,
          [parent_id]
        );

        const closureInserts = [];

        for (const childId of childIds) {
          for (const ancestor of ancestors) {
            closureInserts.push([
              ancestor.ancestor_id,
              childId,
              ancestor.depth + 1,
            ]);
          }
          closureInserts.push([childId, childId, 0]); // self
        }

        await connection.query(
          `INSERT IGNORE INTO category_closure (ancestor_id, descendant_id, depth) VALUES ?`,
          [closureInserts]
        );
      } else {
        const selfOnly = childIds.map((id) => [id, id, 0]);
        await connection.query(
          `INSERT IGNORE INTO category_closure (ancestor_id, descendant_id, depth) VALUES ?`,
          [selfOnly]
        );
      }
    }

    // 5. Delete the category's closure and record
    await connection.query(
      `DELETE FROM category_closure WHERE ancestor_id = ? OR descendant_id = ?`,
      [categoryId, categoryId]
    );
    await deleteMetaAndAttributes(connection, "category", category_url);
    await connection.query(`DELETE FROM categories WHERE id = ?`, [categoryId]);

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

    await connection.commit();
    return NextResponse.json({ message: "Category deleted successfully" });
  } catch (error) {
    await connection.rollback();
    console.error("❌ Delete error:", error);
    return NextResponse.json(
      { error: "Failed to delete category", details: error.message },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}
