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

/*
  GET /api/location-bins

  Compatible with table:
  location_bins
  locations
*/

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

    const keyword = searchParams.get("keyword") || "";
    const type = searchParams.get("type") || "";
    const status = searchParams.get("status") || "";
    const warehouse = searchParams.get("warehouse") || "";

    let sql = `
      SELECT
        lb.id,
        lb.location_id,
        lb.code,
        lb.name,
        lb.type,
        lb.is_active,
        lb.created_at,
        lb.updated_at,

        l.code AS warehouse_code,
        l.name AS warehouse_name,
        l.type AS warehouse_type

      FROM location_bins lb
      INNER JOIN locations l
        ON l.id = lb.location_id

      WHERE 1 = 1
    `;

    const params: any[] = [];

    if (keyword) {
      sql += `
        AND (
          lb.code LIKE ?
          OR lb.name LIKE ?
          OR l.name LIKE ?
          OR l.code LIKE ?
        )
      `;

      const q = `%${keyword}%`;
      params.push(q, q, q, q);
    }

    if (type && type !== "ALL") {
      sql += ` AND lb.type = ? `;
      params.push(type);
    }

    if (warehouse && warehouse !== "ALL") {
      sql += ` AND lb.location_id = ? `;
      params.push(Number(warehouse));
    }

    if (status === "ACTIVE") {
      sql += ` AND lb.is_active = 1 `;
    }

    if (status === "INACTIVE") {
      sql += ` AND lb.is_active = 0 `;
    }

    sql += ` ORDER BY l.name ASC, lb.code ASC `;

    const [rows]: any = await db.query(sql, params);

    return NextResponse.json({
      success: true,
      data: rows,
    });
  } catch (error: any) {
    console.error("GET LOCATION BINS ERROR:", error);

    return NextResponse.json(
      {
        success: false,
        message: "Failed load location bins",
        error: error.message,
      },
      { status: 500 }
    );
  }
}

/*
  POST /api/location-bins
*/

export async function POST(req: NextRequest) {
  try {
    const body = await req.json();

    const location_id = Number(body.location_id);
    const code = String(body.code || "").trim().toUpperCase();
    const name = String(body.name || "").trim();
    const type = String(body.type || "RACK");
    const is_active =
      body.is_active === false || body.is_active === 0 ? 0 : 1;

    if (!location_id) {
      return NextResponse.json(
        {
          success: false,
          message: "Location required",
        },
        { status: 400 }
      );
    }

    if (!code) {
      return NextResponse.json(
        {
          success: false,
          message: "Code required",
        },
        { status: 400 }
      );
    }

    if (!name) {
      return NextResponse.json(
        {
          success: false,
          message: "Name required",
        },
        { status: 400 }
      );
    }

    const [exist]: any = await db.query(
      `
      SELECT id
      FROM location_bins
      WHERE location_id = ?
      AND code = ?
      LIMIT 1
      `,
      [location_id, code]
    );

    if (exist.length > 0) {
      return NextResponse.json(
        {
          success: false,
          message: "Bin code already exists in this warehouse",
        },
        { status: 409 }
      );
    }

    const [result]: any = await db.query(
      `
      INSERT INTO location_bins
      (
        location_id,
        code,
        name,
        type,
        is_active
      )
      VALUES (?, ?, ?, ?, ?)
      `,
      [location_id, code, name, type, is_active]
    );

    return NextResponse.json({
      success: true,
      message: "Location bin created",
      id: result.insertId,
    });
  } catch (error: any) {
    console.error("POST LOCATION BIN ERROR:", error);

    return NextResponse.json(
      {
        success: false,
        message: "Failed create location bin",
        error: error.message,
      },
      { status: 500 }
    );
  }
}