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

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

    const body = await req.json();
    const cleanedData = cleanData(body);

    if (
      !cleanedData.page ||
      !cleanedData.section_type ||
      !cleanedData.collection_ids
    ) {
      return Response.json(
        { error: "Missing required fields" },
        { status: 400 }
      );
    }

    const [result] = await connection.query(
      `INSERT INTO assign_collections (page, section_type, collection_ids)
       VALUES (?, ?, ?)`,
      [
        cleanedData.page,
        cleanedData.section_type,
        cleanedData.collection_ids, // should already be a JSON string
      ]
    );

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

    if (error.code === "ER_DUP_ENTRY") {
      return Response.json(
        { error: "Section Type must be unique" },
        { status: 400 }
      );
    }

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

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 offset = pageIndex * pageSize;

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

    const [rows] = await connection.query(
      `
      SELECT 
        ac.id, 
        ac.page, 
        ac.section_type, 
        ac.collection_ids,
        GROUP_CONCAT(c.name ORDER BY jt.ordering SEPARATOR ' — ') AS collection_names,
        DATE_FORMAT(ac.created_at, '%Y-%m-%d') AS created_at,
        DATE_FORMAT(ac.updated_at, '%Y-%m-%d') AS updated_at
      FROM assign_collections ac
      LEFT JOIN JSON_TABLE(
        ac.collection_ids,
        '$[*]' COLUMNS (
          collection_id INT PATH '$',
          ordering FOR ORDINALITY
        )
      ) AS jt ON TRUE
      LEFT JOIN collections c ON c.id = jt.collection_id
      GROUP BY ac.id
      ORDER BY ac.id DESC
      LIMIT ? OFFSET ?

    `,
      [pageSize, offset]
    );

    const [countRows] = await connection.query(
      `SELECT COUNT(*) as count FROM assign_collections`
    );
    
    const normalized = rows.map((r) => ({
      ...r,
      collection_ids:
        typeof r.collection_ids === "string"
          ? globalJsonParse(r.collection_ids)
          : r.collection_ids,
    }));

    return Response.json(
      { rows : normalized, rowCount: countRows[0]?.count || 0 },
      { status: 200 }
    );
  } catch (error) {
    console.error("❌ Get AssignCollection Error:", error);
    return Response.json(
      { error: "Failed to fetch assigned collections", 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 body = await req.json();
    const cleanedData = cleanData(body);

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

    await connection.query(
      `UPDATE assign_collections 
       SET page = ?, section_type = ?, collection_ids = ?, updated_at = CURRENT_TIMESTAMP
       WHERE id = ?`,
      [
        cleanedData.page,
        cleanedData.section_type,
        cleanedData.collection_ids,
        cleanedData.id,
      ]
    );

    await connection.commit();
    return Response.json(
      { message: "Assigned collection updated" },
      { status: 200 }
    );
  } catch (error) {
    if (connection) await connection.rollback();
    console.error("❌ Update AssignCollection Error:", error);

    if (error.code === "ER_DUP_ENTRY") {
      return Response.json(
        { error: "Section Type must be unique" },
        { status: 400 }
      );
    }

    return Response.json(
      { error: "Failed to update assigned 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 ID" }, { status: 400 });

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

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

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