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) {
  try {
    const { searchParams } = new URL(req.url);

    const search = searchParams.get("search") || "";
    const category = searchParams.get("category") || "";
    const page = Number(searchParams.get("page") || 1);
    const limit = Number(searchParams.get("limit") || 10);

    const offset = (page - 1) * limit;

    /**
     * ===========================
     * WHERE BUILDER (SAFE)
     * ===========================
     */
    let where = "WHERE 1=1";
    const params: any[] = [];

    if (search) {
      where += " AND (i.name LIKE ? OR i.sku LIKE ?)";
      params.push(`%${search}%`, `%${search}%`);
    }

    if (category && category !== "ALL") {
      where += " AND c.name = ?";
      params.push(category);
    }

    /**
     * ===========================
     * TOTAL COUNT
     * ===========================
     */
    const [countRows]: any = await db.query(
      `
      SELECT COUNT(*) as total
      FROM items i
      LEFT JOIN categories c ON c.id = i.category_id
      ${where}
      `,
      params
    );

    const total = countRows[0].total;

    /**
     * ===========================
     * ITEMS QUERY
     * ===========================
     */
  const [itemsRows]: any = await db.query(
      `
      SELECT 
        i.id,
        i.sku,
        i.name,
        c.name AS category,

        u.name AS unit_name,
        u.code AS unit_code,
        u.symbol AS unit_symbol,

        i.is_active,
        i.description
      FROM items i
      LEFT JOIN categories c ON c.id = i.category_id
      LEFT JOIN units u ON u.code = i.units
      ${where}
      ORDER BY i.id DESC
      LIMIT ? OFFSET ?
      `,
      [...params, limit, offset]
    );

    /**
     * ===========================
     * BARCODE
     * ===========================
     */
    const [barcodeRows]: any = await db.query(`
      SELECT item_id, barcode
      FROM item_barcodes
      WHERE is_primary = 1
    `);

    const barcodeMap = new Map<number, string>();
    for (const b of barcodeRows) {
      barcodeMap.set(b.item_id, b.barcode);
    }

    /**
     * ===========================
     * FINAL ITEMS
     * ===========================
     */
    const items = itemsRows.map((i: any) => ({
      id: i.id,
      sku: i.sku,
      name: i.name,
      category: i.category || "-",
      unit: i.unit_name ? `${i.unit_name} (${i.unit_code})` : "-",
      is_active: Boolean(i.is_active),
      description: i.description,
      barcode: barcodeMap.get(i.id) || null,
    }));

    /**
     * ===========================
     * CATEGORIES (MASTER TABLE)
     * ===========================
     */
    const [catRows]: any = await db.query(`
      SELECT name FROM categories
      WHERE is_active = 1
      ORDER BY name ASC
    `);

    const categories = catRows.map((c: any) => c.name);

    return NextResponse.json({
      items,
      categories,
      total,
      page,
      limit,
    });
  } catch (err: any) {
    console.error("ITEMS API ERROR:", err);

    return NextResponse.json(
      { message: "Failed load items", error: err?.message },
      { status: 500 }
    );
  }
}


/* =========================
   POST CREATE ITEM
========================= */
export async function POST(req: Request) {
  try {
    const body = await req.json();

    const {
      sku,
      name,
      description,
      category_id,
      supplier_id, // ✅ ADD INI
      unit,
      is_active = true,
    } = body;

    const normalizedSku = sku.trim().toUpperCase();

    /* ================= SESSION USER (ADDED) ================= */
    const session = await getServerSession(authOptions);
    const created_by = session?.user?.id ? Number(session.user.id) : null;

    /* ================= VALIDATION ================= */
    if (!normalizedSku || !name || !unit) {
      return NextResponse.json(
        { message: "SKU, Name, Unit wajib diisi" },
        { status: 400 }
      );
    }

    /* ================= VALIDATE UNIT ================= */
    const [unitRows]: any = await db.query(
      `SELECT code, name FROM units WHERE code = ? AND is_active = 1 LIMIT 1`,
      [unit]
    );

    if (!unitRows.length) {
      return NextResponse.json(
        { message: "Unit tidak ditemukan atau tidak aktif" },
        { status: 400 }
      );
    }

    const unitName = unitRows[0].name;
    const unitCode = unitRows[0].code;

    /* ================= VALIDATE SUPPLIER (OPTIONAL) ================= */
    let supplierName = null;

    if (supplier_id) {
      const [supplierRows]: any = await db.query(
        `SELECT id, name FROM suppliers WHERE id = ? LIMIT 1`,
        [supplier_id]
      );

      if (!supplierRows.length) {
        return NextResponse.json(
          { message: "Supplier tidak ditemukan" },
          { status: 400 }
        );
      }

      supplierName = supplierRows[0].name;
    }

    /* ================= CHECK DUPLICATE SKU ================= */
    const [exist]: any = await db.query(
      `SELECT id FROM items WHERE sku = ? LIMIT 1`,
      [normalizedSku]
    );

    if (exist.length > 0) {
      return NextResponse.json(
        { message: "SKU sudah digunakan, silakan pakai SKU lain" },
        { status: 400 }
      );
    }

    /* ================= INSERT ITEM ================= */
    const [result]: any = await db.query(
      `
      INSERT INTO items
      (sku, name, description, category_id, supplier_id, units, is_active)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      `,
      [
        normalizedSku,
        name,
        description,
        category_id || null,
        supplier_id || null,
        unitCode,
        is_active,
      ]
    );

    const itemId = result.insertId;

    /* ================= FORMAT BARCODE ================= */
    const padded = String(itemId).padStart(9, "0");
    const formattedNumber = padded.replace(/(\d{3})(?=\d)/g, "$1-");
    const finalBarcode = `${normalizedSku}-${formattedNumber}`;

    /* ================= INSERT BARCODE ================= */
    await db.query(
      `
      INSERT INTO item_barcodes
      (item_id, barcode, is_primary)
      VALUES (?, ?, 1)
      `,
      [itemId, finalBarcode]
    );

    /* ================= ACTIVITY LOG (FIXED) ================= */
    await db.query(
      `
      INSERT INTO activity_logs (
        entity,
        entity_id,
        action,
        description,
        created_by
      ) VALUES (?, ?, ?, ?, ?)
      `,
      [
        "ITEM",
        itemId,
        "CREATE",
        `Item dibuat: ${normalizedSku} - ${name}
        | Unit: ${unitCode} (${unitName})
        ${supplierName ? `| Supplier: ${supplierName}` : ""}`,
        created_by,
      ]
    );

    return NextResponse.json({
      message: "Item berhasil ditambahkan",
      item_id: itemId,
      barcode: finalBarcode,
    });

  } catch (err: any) {
    console.error("CREATE ITEM ERROR:", err);

    if (err.code === "ER_DUP_ENTRY") {
      return NextResponse.json(
        { message: "SKU sudah digunakan (duplicate)" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { message: "Gagal tambah item", error: err?.message },
      { status: 500 }
    );
  }
}