import { NextResponse } from "next/server";
import { db } from "../../lib/db";
import { getServerSession } from "next-auth";
import { authOptions } from "../auth/[...nextauth]/route";

export async function GET(req: Request) {
  const { searchParams } = new URL(req.url);

  let search = searchParams.get("search") || "";
  const locationId = searchParams.get("location_id") || "";
  const itemId = searchParams.get("item_id") || ""; // ✅ untuk edit page

  const page = Number(searchParams.get("page") || 1);
  const limit = Number(searchParams.get("limit") || 10);
  const offset = (page - 1) * limit;

  const normalizedSearch = search
    .toLowerCase()
    .replace(/\s+/g, "");

  let where = "WHERE 1=1";
  let params: any[] = [];

  /* ================= FILTER ================= */
  if (search) {
    where += `
      AND (
        LOWER(REPLACE(it.name, ' ', '')) LIKE ?
        OR LOWER(REPLACE(it.sku, ' ', '')) LIKE ?
      )
    `;
    params.push(`%${normalizedSearch}%`, `%${normalizedSearch}%`);
  }

  if (locationId) {
    where += " AND l.id = ?";
    params.push(locationId);
  }

  if (itemId) {
    where += " AND it.id = ?";
    params.push(itemId);
  }

  /* ================= TOTAL DATA ================= */
  const [count]: any = await db.query(
    `
    SELECT COUNT(*) as total
    FROM inventories inv
    JOIN items it ON it.id = inv.item_id
    JOIN locations l ON l.id = inv.location_id
    ${where}
    `,
    params
  );

  /* ================= TOTAL QTY ================= */
  const [qty]: any = await db.query(
    `
    SELECT COALESCE(SUM(inv.quantity), 0) as total_quantity
    FROM inventories inv
    JOIN items it ON it.id = inv.item_id
    JOIN locations l ON l.id = inv.location_id
    ${where}
    `,
    params
  );

  /* ================= DATA ================= */
  const [rows]: any = await db.query(
    `
    SELECT
      inv.id,

      it.id as item_id,
      it.sku,
      it.name,
      it.category,
      it.unit,

      ib.barcode, -- ✅ barcode primary

      l.id as location_id,
      l.name as location_name,

      COALESCE(inv.quantity, 0) as quantity,
      COALESCE(inv.reserved_quantity, 0) as reserved_quantity

    FROM inventories inv

    JOIN items it
      ON it.id = inv.item_id

    LEFT JOIN item_barcodes ib
      ON ib.item_id = it.id
      AND ib.is_primary = 1

    JOIN locations l
      ON l.id = inv.location_id

    ${where}

    ORDER BY inv.created_at DESC, it.name ASC

    LIMIT ? OFFSET ?
    `,
    [...params, limit, offset]
  );

  return NextResponse.json({
    data: rows,
    total: count[0]?.total || 0,
    total_quantity: qty[0]?.total_quantity || 0,
  });
}




/**
 * CREATE / ADD INVENTORY
 * - Jika belum ada → insert
 * - Jika sudah ada → tambah quantity
 */

export async function POST(req: Request) {
  const conn = await db.getConnection();

  try {
    await conn.beginTransaction();

    /* ================= GET SESSION USER ================= */
    const session = await getServerSession(authOptions);

    const created_by = session?.user?.id || null;

    if (!created_by) {
      await conn.rollback();
      conn.release();

      return NextResponse.json(
        { message: "Unauthorized" },
        { status: 401 }
      );
    }

    const body = await req.json();

    let {
      item_id,
      location_id,
      quantity,
      reserved_quantity = 0,
    } = body;

    /* ================= VALIDATION ================= */
    if (!item_id || !location_id || quantity == null) {
      await conn.rollback();
      conn.release();

      return NextResponse.json(
        { message: "Field wajib belum lengkap" },
        { status: 400 }
      );
    }

    /* ================= NORMALIZE ================= */
    item_id = Number(item_id);
    location_id = Number(location_id);
    quantity = Number(quantity);
    reserved_quantity = Number(reserved_quantity || 0);

    if (quantity <= 0) {
      await conn.rollback();
      conn.release();

      return NextResponse.json(
        { message: "Quantity harus lebih dari 0" },
        { status: 400 }
      );
    }

    /* ================= GET EXISTING ================= */
    const [existing]: any = await conn.query(
      `
      SELECT quantity
      FROM inventories
      WHERE item_id = ? AND location_id = ?
      `,
      [item_id, location_id]
    );

    const oldQty = Number(existing?.[0]?.quantity || 0);
    const newQty = oldQty + quantity;

    /* ================= GET ITEM + LOCATION INFO ================= */
    const [metaRows]: any = await conn.query(
      `
      SELECT
        i.name AS item_name,
        i.sku,
        l.name AS location_name
      FROM items i
      CROSS JOIN locations l
      WHERE i.id = ? AND l.id = ?
      LIMIT 1
      `,
      [item_id, location_id]
    );

    const meta = metaRows?.[0] || {
      item_name: "-",
      sku: "-",
      location_name: "-",
    };

    /* ================= UPSERT INVENTORY ================= */
    await conn.query(
      `
      INSERT INTO inventories
        (item_id, location_id, quantity, reserved_quantity)
      VALUES (?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        quantity = quantity + VALUES(quantity)
      `,
      [item_id, location_id, quantity, reserved_quantity]
    );

    /* ================= FORMAT ================= */
    const formatQty = (val: number) =>
      Number(val).toLocaleString("en-US", {
        minimumFractionDigits: 0,
        maximumFractionDigits: 4,
      });

    const oldFormatted = formatQty(oldQty);
    const newFormatted = formatQty(newQty);

    const notes = `Tambah stok: ${oldFormatted} → ${newFormatted}`;

    /* ================= STOCK MOVEMENT LOG ================= */
    await conn.query(
      `
      INSERT INTO stock_movements (
        item_id,
        location_id,
        movement_type,
        quantity,
        reference_type,
        notes,
        created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?)
      `,
      [
        item_id,
        location_id,
        "IN",
        quantity,
        "INVENTORY_ADD",
        notes,
        created_by,
      ]
    );

    /* ================= ACTIVITY LOG ================= */
    await conn.query(
      `
      INSERT INTO activity_logs (
        entity,
        entity_id,
        action,
        description,
        created_by
      ) VALUES (?, ?, ?, ?, ?)
      `,
      [
        "INVENTORY",
        item_id,
        "ADD_STOCK",
        `Tambah stok inventory:
Item: ${meta.item_name} (${meta.sku})
Gudang: ${meta.location_name}
Qty Tambah: ${formatQty(quantity)}
Stok Lama: ${oldFormatted}
Stok Baru: ${newFormatted}`,
        created_by,
      ]
    );

    await conn.commit();
    conn.release();

    return NextResponse.json({
      message: "Inventory berhasil disimpan",
    });
  } catch (err: any) {
    await conn.rollback();
    conn.release();

    console.error("ERROR INVENTORY POST:", err);

    return NextResponse.json(
      {
        message: "Server error",
        error: err.message,
      },
      { status: 500 }
    );
  }
}