import { NextRequest, NextResponse } from "next/server";
import { queryRows, queryOne, pool } from "@/lib/db";
import { requireMomoUser } from "@/lib/momo-guard";

// 마이그레이션 016/011 등이 운영 DB에 아직 안 들어간 환경에서도 안전하게 동작하도록
// 첫 호출 시 필요한 컬럼이 없으면 ADD COLUMN IF NOT EXISTS 로 보장한다.
let columnsEnsured = false;
async function ensureColumns() {
  if (columnsEnsured) return;
  try {
    await pool.query(`
      ALTER TABLE momo_items
        ADD COLUMN IF NOT EXISTS vendor_objid       TEXT,
        ADD COLUMN IF NOT EXISTS max_order_qty      INTEGER,
        ADD COLUMN IF NOT EXISTS is_hidden          CHAR(1) DEFAULT 'N',
        ADD COLUMN IF NOT EXISTS requires_delivery  CHAR(1) DEFAULT 'N';
    `);
    columnsEnsured = true;
  } catch (err) {
    console.error("[items/list ensureColumns]", err);
  }
}

// 품목 목록 — USER 는 재고 있는 ACTIVE 만, ADMIN 은 전체 표시
// 숨김(is_hidden='Y') 품목: view_hidden='Y' 권한 회원과 관리자에게만 노출
export async function POST(req: NextRequest) {
  const r = await requireMomoUser();
  if (r instanceof NextResponse) return r;
  await ensureColumns();

  const body = await req.json().catch(() => ({}));
  const { keyword, isTaxFree, makerObjid, status, onlyAvailable } = body as {
    keyword?: string;
    isTaxFree?: "Y" | "N";
    makerObjid?: string;
    status?: string;
    onlyAvailable?: boolean;
  };

  // admin 판정 — role/isAdmin/userType='A' 중 하나라도 해당하면 관리자
  const isAdmin =
    r.user.role === "ADMIN" ||
    r.user.isAdmin === true ||
    r.user.userType === "A";
  const isUser = !isAdmin;

  // view_hidden 권한 조회 (일반 사용자만 의미 있음)
  let canViewHidden = isAdmin;
  if (isUser) {
    const row = await queryOne<{ view_hidden: string }>(
      `SELECT COALESCE(view_hidden, 'N') AS view_hidden FROM user_info WHERE user_id = $1`,
      [r.user.userId]
    );
    canViewHidden = row?.view_hidden === "Y";
  }

  const conditions = ["COALESCE(I.is_del, 'N') != 'Y'"];
  const params: unknown[] = [];
  let i = 1;

  // 일반 사용자: 활성 품목만 (DB 값이 'active'/'ACTIVE' 혼재해도 안전하도록 UPPER)
  if (isUser) conditions.push("UPPER(COALESCE(I.status, '')) = 'ACTIVE'");
  else if (status) {
    conditions.push(`I.status = $${i++}`);
    params.push(status);
  }

  if (!canViewHidden) conditions.push("COALESCE(I.is_hidden, 'N') != 'Y'");

  if (keyword) {
    conditions.push(`(I.item_name ILIKE '%' || $${i} || '%' OR I.item_code ILIKE '%' || $${i} || '%' OR I.item_detail ILIKE '%' || $${i} || '%')`);
    params.push(keyword);
    i++;
  }
  if (isTaxFree === "Y" || isTaxFree === "N") {
    conditions.push(`I.is_tax_free = $${i++}`);
    params.push(isTaxFree);
  }
  if (makerObjid) {
    conditions.push(`I.maker_objid = $${i++}`);
    params.push(makerObjid);
  }
  const { vendorObjid } = body as { vendorObjid?: string };
  if (vendorObjid) {
    conditions.push(`I.vendor_objid = $${i++}`);
    params.push(vendorObjid);
  }
  if (onlyAvailable) {
    conditions.push(
      `COALESCE((SELECT SUM(S.qty) FROM momo_stocks S JOIN momo_warehouses W ON S.wh_objid = W.objid WHERE S.item_objid = I.objid AND COALESCE(W.is_del,'N') != 'Y'), 0) > 0`
    );
  }

  const sql = `
    SELECT
      I.objid          AS "OBJID",
      I.item_code      AS "ITEM_CODE",
      I.item_name      AS "ITEM_NAME",
      I.item_detail    AS "ITEM_DETAIL",
      I.maker_objid    AS "MAKER_OBJID",
      M.maker_name     AS "MAKER_NAME",
      I.unit           AS "UNIT",
      I.unit_price     AS "UNIT_PRICE",
      I.cost_price     AS "COST_PRICE",
      I.is_tax_free    AS "IS_TAX_FREE",
      I.image_url      AS "IMAGE_URL",
      I.status         AS "STATUS",
      I.attributes     AS "ATTRIBUTES",
      I.max_order_qty  AS "MAX_ORDER_QTY",
      COALESCE(I.is_hidden, 'N') AS "IS_HIDDEN",
      COALESCE(I.requires_delivery, 'N') AS "REQUIRES_DELIVERY",
      I.vendor_objid   AS "VENDOR_OBJID",
      V.supply_name    AS "VENDOR_NAME",
      COALESCE((
        SELECT SUM(S.qty) FROM momo_stocks S
        JOIN momo_warehouses W ON S.wh_objid = W.objid
        WHERE S.item_objid = I.objid AND COALESCE(W.is_del,'N') != 'Y'
      ), 0)            AS "STOCK_QTY",
      TO_CHAR(I.regdate, 'YYYY-MM-DD') AS "REGDATE"
    FROM momo_items I
    LEFT JOIN momo_makers M ON I.maker_objid = M.objid
    LEFT JOIN supply_mng V ON I.vendor_objid = V.objid::text
    WHERE ${conditions.join(" AND ")}
    ORDER BY I.item_name ASC
  `;

  const rows = await queryRows(sql, params);
  return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
}
