import { NextResponse } from "next/server";
import { db } from "../../lib/db";

export async function GET() {
  try {
    /**
     * =========================================
     * 1. ITEMS + TOTAL STOCK
     * =========================================
     */
    const [itemsRows]: any = await db.query(`
      SELECT 
        i.id,
        i.sku,
        i.name,
        i.category,
        i.unit,
        i.is_active,
        i.description,
        COALESCE(SUM(inv.quantity), 0) AS total_stock
      FROM items i
      LEFT JOIN inventories inv ON inv.item_id = i.id
      GROUP BY i.id
      ORDER BY i.name ASC
    `);

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

    /**
     * =========================================
     * 3. INVENTORY PER LOCATION
     * =========================================
     */
    const [locationRows]: any = await db.query(`
      SELECT 
        inv.item_id,
        l.name AS location,
        inv.quantity
      FROM inventories inv
      JOIN locations l ON l.id = inv.location_id
    `);

    /**
     * =========================================
     * 4. MAP BARCODE
     * =========================================
     */
    const barcodeMap = new Map<number, string>();

    for (const row of barcodeRows) {
      barcodeMap.set(row.item_id, row.barcode);
    }

    /**
     * =========================================
     * 5. MAP LOCATION
     * =========================================
     */
    const locationMap = new Map<number, any[]>();

    for (const row of locationRows) {
      if (!locationMap.has(row.item_id)) {
        locationMap.set(row.item_id, []);
      }

      locationMap.get(row.item_id)!.push({
        location: row.location,
        qty: Number(row.quantity),
      });
    }

    /**
     * =========================================
     * 6. BUILD FINAL ITEMS
     * =========================================
     */
    const items = itemsRows.map((item: any) => ({
      id: item.id,
      sku: item.sku,
      name: item.name,
      category: item.category,
      unit: item.unit,
      is_active: Boolean(item.is_active),
      total_stock: Number(item.total_stock),

      // FIXED: from item_barcodes
      barcode: barcodeMap.get(item.id) || null,

      locations: locationMap.get(item.id) || [],
    }));

    /**
     * =========================================
     * 7. CATEGORIES
     * =========================================
     */
    const categories = [...new Set(items.map((i: any) => i.category))];

    /**
     * =========================================
     * 8. SUMMARY
     * =========================================
     */
    const summary = {
      total_items: items.length,
      active_items: items.filter((i: any) => i.is_active).length,
      total_stock: items.reduce(
        (acc: number, i: any) => acc + i.total_stock,
        0
      ),
    };

    /**
     * =========================================
     * RESPONSE
     * =========================================
     */
    return NextResponse.json({
      items,
      categories,
      summary,
    });
  } catch (error: any) {
    console.error("BARANG API ERROR:", error);

    return NextResponse.json(
      {
        message: "Failed to load barang from database",
        error: error?.message,
      },
      { status: 500 }
    );
  }
}