import { Response } from "express";
import { getPool } from "../database/db";
import { logger } from "../utils/logger";

interface AuthenticatedRequest extends Request {
  user?: { userId: string; companyCode: string; userName?: string };
  body: any;
  query: any;
}

/**
 * GET /api/pop/inventory/stock-detail
 * 재고 목록 + 품목상세(item_info) + 창고명(warehouse_info) JOIN 조회
 */
export const getStockDetail = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { warehouse_code, keyword } = req.query;
    const conditions = ["ist.company_code = $1"];
    const params: any[] = [companyCode];
    let idx = 2;

    // 수량 > 0 인 재고만
    conditions.push("COALESCE(CAST(NULLIF(ist.current_qty, '') AS numeric), 0) > 0");

    if (warehouse_code && warehouse_code !== "all") {
      conditions.push(`ist.warehouse_code = $${idx++}`);
      params.push(warehouse_code);
    }

    if (keyword) {
      conditions.push(`(
        ist.item_code ILIKE $${idx}
        OR COALESCE(ii.item_name, '') ILIKE $${idx}
        OR COALESCE(ii.item_number, '') ILIKE $${idx}
      )`);
      params.push(`%${keyword}%`);
      idx++;
    }

    const result = await pool.query(
      `SELECT
         ist.id,
         ist.item_code,
         COALESCE(ii.item_name, ist.item_code) AS item_name,
         COALESCE(ii.item_number, ist.item_code) AS item_number,
         COALESCE(ii.size, '') AS spec,
         COALESCE(ii.unit, 'EA') AS unit,
         COALESCE(ii.material, '') AS material,
         ist.warehouse_code,
         COALESCE(wi.warehouse_name, ist.warehouse_code) AS warehouse_name,
         COALESCE(ist.location_code, '') AS location_code,
         COALESCE(wl.location_name, '') AS location_name,
         COALESCE(wl.floor, '') AS floor,
         ist.current_qty
       FROM inventory_stock ist
       LEFT JOIN (
         SELECT DISTINCT ON (item_number, company_code)
           item_number, item_name, size, unit, material, company_code
         FROM item_info
         ORDER BY item_number, company_code, created_date DESC
       ) ii ON ist.item_code = ii.item_number AND ist.company_code = ii.company_code
       LEFT JOIN warehouse_info wi ON ist.warehouse_code = wi.warehouse_code
         AND ist.company_code = wi.company_code
       LEFT JOIN warehouse_location wl ON ist.location_code = wl.location_code
         AND ist.company_code = wl.company_code
       WHERE ${conditions.join(" AND ")}
       ORDER BY COALESCE(ii.item_name, ist.item_code)
       LIMIT 500`,
      params
    );

    return res.json({ success: true, data: result.rows });

  } catch (error: any) {
    logger.error("[pop/inventory] stock-detail 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/temp-load
 * 임시저장 cart_items 불러오기 (cart_type + status='saved' 기준)
 */
export const loadTempCart = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const cartType = req.query.cart_type || "inventory-adjust";

    const result = await pool.query(
      `SELECT id, row_data, row_key, cart_type, status, created_date
       FROM cart_items
       WHERE company_code = $1 AND cart_type = $2 AND status = 'saved'
       ORDER BY created_date`,
      [companyCode, cartType]
    );

    return res.json({ success: true, data: result.rows });

  } catch (error: any) {
    logger.error("[pop/inventory] temp-load 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * DELETE /api/pop/inventory/temp-clear
 * 임시저장 cart_items 삭제 (cart_type 기준)
 */
export const clearTempCart = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const cartType = req.query.cart_type || "inventory-adjust";

    await pool.query(
      `DELETE FROM cart_items WHERE company_code = $1 AND cart_type = $2`,
      [companyCode, cartType]
    );

    return res.json({ success: true, message: "임시저장 삭제 완료" });

  } catch (error: any) {
    logger.error("[pop/inventory] temp-clear 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

interface AdjustItem {
  stock_id: string;
  item_code: string;
  warehouse_code: string;
  location_code?: string;
  system_qty: number;
  type: "confirm" | "adjust";
  actual_qty?: number;
  reason?: string;
  new_warehouse?: string;
  new_location?: string;
  memo?: string;
}

/**
 * POST /api/pop/inventory/adjust-batch
 * 재고 조정 일괄 확정
 * - confirm: 이상없음 (이력만 기록)
 * - adjust: 수량 조정 (inventory_stock UPDATE + inventory_history INSERT)
 * - 위치불일치: 기존 위치 차감 + 새 위치 추가
 */
export const adjustBatch = async (req: any, res: Response) => {
  const pool = getPool();
  const client = await pool.connect();

  try {
    const companyCode = req.user?.companyCode;
    const userId = req.user?.userId;
    const userName = req.user?.userName || userId;

    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { items } = req.body as { items: AdjustItem[] };
    if (!items || !Array.isArray(items) || items.length === 0) {
      return res.status(400).json({ success: false, message: "조정 항목이 없습니다" });
    }

    await client.query("BEGIN");

    let confirmCount = 0;
    let adjustCount = 0;
    const results: Array<{ item_code: string; type: string; status: string }> = [];

    for (const item of items) {
      // company_code 필터: 멀티테넌시 필수
      const stockCheck = await client.query(
        `SELECT id, item_code, warehouse_code, location_code,
                COALESCE(CAST(NULLIF(current_qty, '') AS numeric), 0) as current_qty
         FROM inventory_stock
         WHERE id = $1 AND company_code = $2`,
        [item.stock_id, companyCode]
      );

      if (stockCheck.rowCount === 0) {
        results.push({ item_code: item.item_code, type: item.type, status: "not_found" });
        continue;
      }

      const stock = stockCheck.rows[0];
      const systemQty = parseFloat(stock.current_qty) || 0;

      if (item.type === "confirm") {
        // 확인 (이상없음): 이력만 기록, 재고 변경 없음
        await client.query(
          `INSERT INTO inventory_history (
             id, company_code, item_code, warehouse_code, location_code,
             transaction_type, transaction_date, quantity, balance_qty,
             remark, writer, manager_name, created_date
           ) VALUES (
             gen_random_uuid()::text, $1, $2, $3, $4,
             '조정확인', NOW(), '0', $5,
             $6, $7, $8, NOW()
           )`,
          [
            companyCode, stock.item_code, stock.warehouse_code, stock.location_code || "",
            String(systemQty),
            JSON.stringify({ reason: "이상없음", type: "confirm", system_qty: systemQty }),
            userId, userName
          ]
        );
        confirmCount++;
        results.push({ item_code: item.item_code, type: "confirm", status: "ok" });

      } else if (item.type === "adjust") {
        const actualQty = item.actual_qty ?? systemQty;
        const diff = actualQty - systemQty;

        if (item.reason === "위치불일치" && item.new_warehouse) {
          // 위치불일치: 기존 위치에서 차감 + 새 위치에 추가
          // 1. 기존 위치 차감
          await client.query(
            `UPDATE inventory_stock
             SET current_qty = CAST(GREATEST(COALESCE(CAST(NULLIF(current_qty,'') AS numeric),0) - $1, 0) AS text),
                 updated_date = NOW(), writer = $2
             WHERE id = $3 AND company_code = $4`,
            [String(actualQty), userId, item.stock_id, companyCode]
          );

          // 2. 새 위치에 UPSERT
          const newLoc = item.new_location || item.new_warehouse;
          const existingNew = await client.query(
            `SELECT id FROM inventory_stock
             WHERE company_code = $1 AND item_code = $2
               AND COALESCE(warehouse_code,'') = COALESCE($3,'')
               AND COALESCE(location_code,'') = COALESCE($4,'')
             LIMIT 1`,
            [companyCode, stock.item_code, item.new_warehouse, newLoc]
          );

          if (existingNew.rows.length > 0) {
            await client.query(
              `UPDATE inventory_stock
               SET current_qty = CAST(COALESCE(CAST(NULLIF(current_qty,'') AS numeric),0) + $1 AS text),
                   updated_date = NOW(), writer = $2
               WHERE id = $3 AND company_code = $4`,
              [String(actualQty), userId, existingNew.rows[0].id, companyCode]
            );
          } else {
            await client.query(
              `INSERT INTO inventory_stock (id, company_code, item_code, warehouse_code, location_code, current_qty, created_date, writer)
               VALUES (gen_random_uuid()::text, $1, $2, $3, $4, $5, NOW(), $6)`,
              [companyCode, stock.item_code, item.new_warehouse, newLoc, String(actualQty), userId]
            );
          }

          // 3. 이력 2건 (출발 + 도착)
          const remarkJson = JSON.stringify({
            reason: item.reason, type: "adjust",
            system_qty: systemQty, actual_qty: actualQty,
            from_warehouse: stock.warehouse_code, from_location: stock.location_code,
            to_warehouse: item.new_warehouse, to_location: newLoc,
            memo: item.memo || ""
          });

          await client.query(
            `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code, transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
             VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '조정', NOW(), $5, '0', $6, $7, $8, NOW())`,
            [companyCode, stock.item_code, stock.warehouse_code, stock.location_code || "",
             String(-actualQty), remarkJson, userId, userName]
          );
          await client.query(
            `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code, transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
             VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '조정', NOW(), $5, $6, $7, $8, $9, NOW())`,
            [companyCode, stock.item_code, item.new_warehouse, newLoc,
             String(actualQty), String(actualQty), remarkJson, userId, userName]
          );

        } else {
          // 일반 수량 조정
          await client.query(
            `UPDATE inventory_stock
             SET current_qty = $1, updated_date = NOW(), writer = $2
             WHERE id = $3 AND company_code = $4`,
            [String(actualQty), userId, item.stock_id, companyCode]
          );

          // 이력 1건
          const remarkJson = JSON.stringify({
            reason: item.reason || "실사차이", type: "adjust",
            system_qty: systemQty, actual_qty: actualQty, diff,
            memo: item.memo || ""
          });

          await client.query(
            `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code, transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
             VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '조정', NOW(), $5, $6, $7, $8, $9, NOW())`,
            [companyCode, stock.item_code, stock.warehouse_code, stock.location_code || "",
             String(diff), String(actualQty), remarkJson, userId, userName]
          );
        }

        adjustCount++;
        results.push({ item_code: item.item_code, type: "adjust", status: "ok" });
      }
    }

    await client.query("COMMIT");

    logger.info("[pop/inventory] adjust-batch 완료", {
      companyCode, userId, confirmCount, adjustCount, total: items.length
    });

    return res.json({
      success: true,
      message: `확인 ${confirmCount}건, 조정 ${adjustCount}건 처리 완료`,
      data: { confirmCount, adjustCount, results }
    });

  } catch (error: any) {
    await client.query("ROLLBACK").catch(() => {});
    logger.error("[pop/inventory] adjust-batch 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

/**
 * GET /api/pop/inventory/adjust-history
 * 재고 조정 이력 조회
 */
export const getAdjustHistory = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { date_from, date_to, item_code } = req.query;
    const conditions = ["company_code = $1", "transaction_type IN ('조정', '조정확인')"];
    const params: any[] = [companyCode];
    let idx = 2;

    if (date_from) {
      conditions.push(`transaction_date >= $${idx++}`);
      params.push(date_from);
    }
    if (date_to) {
      conditions.push(`transaction_date <= $${idx++}::date + interval '1 day'`);
      params.push(date_to);
    }
    if (item_code) {
      conditions.push(`item_code ILIKE $${idx++}`);
      params.push(`%${item_code}%`);
    }

    const result = await pool.query(
      `SELECT id, item_code, warehouse_code, location_code,
              transaction_type, transaction_date, quantity, balance_qty,
              remark, writer, manager_name, created_date
       FROM inventory_history
       WHERE ${conditions.join(" AND ")}
       ORDER BY transaction_date DESC
       LIMIT 100`,
      params
    );

    // remark JSON 파싱
    const data = result.rows.map((row: any) => {
      let detail: any = {};
      try { detail = JSON.parse(row.remark || "{}"); } catch { /* */ }
      return {
        ...row,
        reason: detail.reason || "",
        adjust_type: detail.type || "",
        system_qty: detail.system_qty,
        actual_qty: detail.actual_qty,
        diff: detail.diff,
        memo: detail.memo || "",
        from_warehouse: detail.from_warehouse,
        to_warehouse: detail.to_warehouse,
      };
    });

    return res.json({ success: true, data });

  } catch (error: any) {
    logger.error("[pop/inventory] adjust-history 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/locations?warehouse_code=XXX
 * 특정 창고의 위치 목록 조회 (warehouse_location 테이블)
 */
export const getLocations = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { warehouse_code } = req.query;
    if (!warehouse_code) {
      return res.status(400).json({ success: false, message: "warehouse_code 필수" });
    }

    const result = await pool.query(
      `SELECT id, location_code, location_name, floor, zone, row_num, level_num,
              warehouse_code, warehouse_name
       FROM warehouse_location
       WHERE company_code = $1 AND warehouse_code = $2
       ORDER BY floor, zone, row_num, level_num`,
      [companyCode, warehouse_code]
    );

    return res.json({ success: true, data: result.rows });

  } catch (error: any) {
    logger.error("[pop/inventory] locations 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/location-lookup?code=XXX
 * 위치코드로 창고+위치 정보 조회 (QR 스캔 결과 처리)
 */
export const locationLookup = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { code } = req.query;
    if (!code) {
      return res.status(400).json({ success: false, message: "code 필수" });
    }

    const result = await pool.query(
      `SELECT id, location_code, location_name, floor, zone, row_num, level_num,
              warehouse_code, warehouse_name
       FROM warehouse_location
       WHERE company_code = $1 AND location_code = $2
       LIMIT 1`,
      [companyCode, code]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "해당 위치코드를 찾을 수 없습니다" });
    }

    return res.json({ success: true, data: result.rows[0] });

  } catch (error: any) {
    logger.error("[pop/inventory] location-lookup 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * POST /api/pop/inventory/temp-status
 * cart_items 상태 일괄 변경 (cart_type + fromStatus → toStatus)
 * 개별 건: ids 배열 전달 시 해당 건만 변경
 */
export const updateCartStatus = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { cart_type, from_status, to_status, ids } = req.body;
    if (!cart_type || !to_status) {
      return res.status(400).json({ success: false, message: "cart_type, to_status 필수" });
    }

    let result;
    if (ids && Array.isArray(ids) && ids.length > 0) {
      // 개별 건 상태 변경
      const placeholders = ids.map((_: string, i: number) => `$${i + 4}`).join(", ");
      result = await pool.query(
        `UPDATE cart_items
         SET status = $1, updated_date = NOW()
         WHERE company_code = $2 AND cart_type = $3
           AND id IN (${placeholders})
           ${from_status ? "AND status = $" + (ids.length + 4) : ""}`,
        from_status
          ? [to_status, companyCode, cart_type, ...ids, from_status]
          : [to_status, companyCode, cart_type, ...ids]
      );
    } else {
      // 전체 건 상태 변경 (from_status 기준)
      if (!from_status) {
        return res.status(400).json({ success: false, message: "전체 변경 시 from_status 필수" });
      }
      result = await pool.query(
        `UPDATE cart_items
         SET status = $1, updated_date = NOW()
         WHERE company_code = $2 AND cart_type = $3 AND status = $4`,
        [to_status, companyCode, cart_type, from_status]
      );
    }

    logger.info("[pop/inventory] temp-status 변경", {
      companyCode, cart_type, from_status, to_status,
      ids: ids?.length ?? "all",
      affected: result.rowCount,
    });

    return res.json({
      success: true,
      message: `${result.rowCount}건 상태 변경 완료`,
      data: { affected: result.rowCount },
    });

  } catch (error: any) {
    logger.error("[pop/inventory] temp-status 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/process-stock
 * 공정 진행 중인 항목의 수량 표시 (양품수량 > 0)
 */
export const getProcessStock = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const result = await pool.query(
      `SELECT wop.id, wop.wo_id, wi.work_instruction_no, wop.process_name, wop.seq_no,
              COALESCE(wop.input_qty, '0') as input_qty,
              COALESCE(wop.good_qty, '0') as good_qty,
              wop.item_code, ii.item_name
       FROM work_order_process wop
       JOIN work_instruction wi ON wop.wo_id = wi.id AND wop.company_code = wi.company_code
       LEFT JOIN (
         SELECT DISTINCT ON (item_number, company_code)
           item_number, item_name, company_code
         FROM item_info
         ORDER BY item_number, company_code, created_date DESC
       ) ii ON wop.item_code = ii.item_number AND wop.company_code = ii.company_code
       WHERE wop.company_code = $1
         AND wop.parent_process_id IS NULL
         AND COALESCE(CAST(NULLIF(wop.good_qty,'') AS numeric), 0) > 0
       ORDER BY wi.work_instruction_no, wop.seq_no`,
      [companyCode]
    );

    return res.json({ success: true, data: result.rows });

  } catch (error: any) {
    logger.error("[pop/inventory] process-stock 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/item-history?item_code=XXX
 * 특정 품목의 재고 이력 조회
 */
export const getItemHistory = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { item_code } = req.query;
    if (!item_code) {
      return res.status(400).json({ success: false, message: "item_code 필수" });
    }

    const result = await pool.query(
      `SELECT id, item_code, warehouse_code, location_code, transaction_type,
              transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date
       FROM inventory_history
       WHERE company_code = $1 AND item_code = $2
       ORDER BY created_date DESC
       LIMIT 50`,
      [companyCode, item_code]
    );

    return res.json({ success: true, data: result.rows });

  } catch (error: any) {
    logger.error("[pop/inventory] item-history 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

/**
 * GET /api/pop/inventory/process-stock-v2
 * 공정명+설비 필터 기반 품목 리스트 (재고이동 화면용)
 * - 필터: process_name, equipment_code (선택)
 * - 응답: processNames[], equipments[], processes[] (품목 기준)
 * - 대기수량 = N공정 양품 - (N+1)공정 투입합계
 * - 미입고 = 마지막 공정 AND good_qty > 0 AND target_warehouse_id IS NULL
 * - 리워크 제외 (is_rework = 'Y' 제외)
 */
export const getProcessStockV2 = async (req: any, res: Response) => {
  const pool = getPool();

  try {
    const companyCode = req.user?.companyCode;
    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { process_name, equipment_code } = req.query;

    // 1) DISTINCT 공정명 목록 (양품 > 0인 것만)
    const processNamesResult = await pool.query(
      `SELECT DISTINCT wop.process_name
       FROM work_order_process wop
       WHERE wop.company_code = $1
         AND wop.parent_process_id IS NULL
         AND (wop.is_rework IS NULL OR wop.is_rework != 'Y')
         AND COALESCE(CAST(NULLIF(wop.good_qty, '') AS numeric), 0) > 0
       ORDER BY wop.process_name`,
      [companyCode]
    );

    // 2) DISTINCT 설비 목록 (양품 > 0인 것만)
    const equipmentsResult = await pool.query(
      `SELECT DISTINCT COALESCE(wop.equipment_code, '') AS equipment_code
       FROM work_order_process wop
       WHERE wop.company_code = $1
         AND wop.parent_process_id IS NULL
         AND (wop.is_rework IS NULL OR wop.is_rework != 'Y')
         AND COALESCE(CAST(NULLIF(wop.good_qty, '') AS numeric), 0) > 0
       ORDER BY equipment_code`,
      [companyCode]
    );

    // 3) 전체 공정 목록 (양품 > 0, 마스터 행, 리워크 제외) + 필터 적용
    const conditions = [
      "wop.company_code = $1",
      "wop.parent_process_id IS NULL",
      "(wop.is_rework IS NULL OR wop.is_rework != 'Y')",
      "COALESCE(CAST(NULLIF(wop.good_qty, '') AS numeric), 0) > 0",
    ];
    const params: any[] = [companyCode];
    let idx = 2;

    if (process_name) {
      conditions.push(`wop.process_name = $${idx++}`);
      params.push(process_name);
    }
    if (equipment_code) {
      conditions.push(`COALESCE(wop.equipment_code, '') = $${idx++}`);
      params.push(equipment_code);
    }

    const processResult = await pool.query(
      `SELECT wop.id, wop.wo_id, wop.seq_no, wop.process_name,
              COALESCE(wop.equipment_code, '') AS equipment_code,
              COALESCE(wop.input_qty, '0') AS input_qty,
              COALESCE(wop.good_qty, '0') AS good_qty,
              COALESCE(wop.concession_qty, '0') AS concession_qty,
              wop.target_warehouse_id,
              wop.target_location_code,
              wop.status,
              wi.work_instruction_no,
              COALESCE(ii.item_number, wi.item_id) AS item_number,
              COALESCE(ii.item_name, ii.item_number, wi.item_id) AS item_name
       FROM work_order_process wop
       JOIN work_instruction wi ON wop.wo_id = wi.id AND wop.company_code = wi.company_code
       LEFT JOIN (
         SELECT DISTINCT ON (id, company_code)
           id, item_number, item_name, company_code
         FROM item_info
         ORDER BY id, company_code, created_date DESC
       ) ii ON wi.item_id = ii.id AND wi.company_code = ii.company_code
       WHERE ${conditions.join(" AND ")}
       ORDER BY wop.process_name, wi.work_instruction_no, CAST(wop.seq_no AS int)`,
      params
    );

    const rows = processResult.rows;

    // 작업지시별 공정 그룹핑하여 마지막 공정 판별 + 대기수량 계산
    // wo_id 별로 그룹핑하여 각 공정의 순서 내 마지막인지 확인
    const woProcessMap: Record<string, any[]> = {};
    for (const row of rows) {
      if (!woProcessMap[row.wo_id]) woProcessMap[row.wo_id] = [];
      woProcessMap[row.wo_id].push(row);
    }

    // 각 wo_id의 전체 공정 목록 (양품 없는 것 포함) 가져와서 마지막 공정/대기수량 정확히 계산
    const woIds = Object.keys(woProcessMap);
    const allProcessMap: Record<string, any[]> = {};

    if (woIds.length > 0) {
      const allProcessResult = await pool.query(
        `SELECT wop.id, wop.wo_id, wop.seq_no, wop.process_name,
                COALESCE(wop.input_qty, '0') AS input_qty,
                COALESCE(wop.good_qty, '0') AS good_qty,
                COALESCE(wop.concession_qty, '0') AS concession_qty,
                wop.target_warehouse_id
         FROM work_order_process wop
         WHERE wop.wo_id = ANY($1) AND wop.company_code = $2
           AND wop.parent_process_id IS NULL
           AND (wop.is_rework IS NULL OR wop.is_rework != 'Y')
         ORDER BY wop.wo_id, CAST(wop.seq_no AS int)`,
        [woIds, companyCode]
      );
      for (const r of allProcessResult.rows) {
        if (!allProcessMap[r.wo_id]) allProcessMap[r.wo_id] = [];
        allProcessMap[r.wo_id].push(r);
      }
    }

    // enriched 결과 생성
    const enriched = [];
    for (const row of rows) {
      const allProcs = allProcessMap[row.wo_id] || [];
      const procIdx = allProcs.findIndex((p: any) => p.id === row.id);
      const isLastProcess = procIdx === allProcs.length - 1;

      const goodQty = (parseFloat(row.good_qty) || 0) + (parseFloat(row.concession_qty) || 0);

      // 분할 행 양품 합계
      const splitGoodResult = await pool.query(
        `SELECT COALESCE(SUM(COALESCE(CAST(NULLIF(good_qty,'') AS numeric),0) + COALESCE(CAST(NULLIF(concession_qty,'') AS numeric),0)), 0) AS total_good
         FROM work_order_process
         WHERE wo_id = $1 AND seq_no = $2 AND company_code = $3
           AND parent_process_id IS NOT NULL
           AND (is_rework IS NULL OR is_rework != 'Y')`,
        [row.wo_id, row.seq_no, companyCode]
      );
      const totalGood = parseFloat(splitGoodResult.rows[0]?.total_good) || goodQty;
      const effectiveGood = totalGood > 0 ? totalGood : goodQty;

      // 다음 공정 투입합계
      let nextInputTotal = 0;
      if (!isLastProcess && procIdx >= 0 && procIdx < allProcs.length - 1) {
        const nextProc = allProcs[procIdx + 1];
        const nextInputResult = await pool.query(
          `SELECT COALESCE(SUM(CAST(NULLIF(input_qty,'') AS numeric)), 0) AS total_input
           FROM work_order_process
           WHERE wo_id = $1 AND seq_no = $2 AND company_code = $3
             AND parent_process_id IS NOT NULL
             AND (is_rework IS NULL OR is_rework != 'Y')`,
          [row.wo_id, nextProc.seq_no, companyCode]
        );
        nextInputTotal = parseFloat(nextInputResult.rows[0]?.total_input) || 0;
      }

      const waitingQty = effectiveGood - nextInputTotal;
      const isUnstored = isLastProcess && effectiveGood > 0 && !row.target_warehouse_id;

      enriched.push({
        id: row.id,
        wo_id: row.wo_id,
        seq_no: row.seq_no,
        process_name: row.process_name,
        equipment_code: row.equipment_code,
        good_qty: String(effectiveGood),
        input_qty: row.input_qty,
        status: row.status,
        target_warehouse_id: row.target_warehouse_id || null,
        target_location_code: row.target_location_code || null,
        is_last_process: isLastProcess,
        is_unstored: isUnstored,
        waiting_qty: Math.max(waitingQty, 0),
        next_input_total: nextInputTotal,
        item_code: row.item_number,
        item_name: row.item_name,
        work_instruction_no: row.work_instruction_no,
      });
    }

    return res.json({
      success: true,
      data: {
        processNames: processNamesResult.rows.map((r: any) => r.process_name),
        equipments: equipmentsResult.rows.map((r: any) => r.equipment_code).filter((e: string) => e !== ''),
        processes: enriched,
      },
    });

  } catch (error: any) {
    logger.error("[pop/inventory] process-stock-v2 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  }
};

interface MoveItem {
  item_code: string;
  from_warehouse: string;
  from_location?: string;
  to_warehouse: string;
  to_location?: string;
  quantity: number;
  stock_id?: string;
  source_type?: "warehouse" | "process";
  work_order_process_id?: string;
}

/**
 * POST /api/pop/inventory/move-batch
 * 재고 이동 일괄 실행
 * - 출발 창고 inventory_stock 차감
 * - 도착 창고 inventory_stock UPSERT (증가)
 * - inventory_history 2건 (출발 -수량, 도착 +수량, transaction_type='이동')
 */
export const moveBatch = async (req: any, res: Response) => {
  const pool = getPool();
  const client = await pool.connect();

  try {
    const companyCode = req.user?.companyCode;
    const userId = req.user?.userId;
    const userName = req.user?.userName || userId;

    if (!companyCode) {
      return res.status(401).json({ success: false, message: "인증 정보 없음" });
    }

    const { items } = req.body as { items: MoveItem[] };
    if (!items || !Array.isArray(items) || items.length === 0) {
      return res.status(400).json({ success: false, message: "이동 항목이 없습니다" });
    }

    await client.query("BEGIN");

    let moveCount = 0;
    const results: Array<{ item_code: string; status: string; message?: string }> = [];

    for (const item of items) {
      const sourceType = item.source_type || "warehouse";

      if (sourceType === "process") {
        // ===== 공정 → 창고 입고 =====
        if (!item.work_order_process_id || !item.to_warehouse || !item.item_code) {
          results.push({ item_code: item.item_code || "", status: "invalid", message: "공정 입고: 필수값 누락" });
          continue;
        }

        // 1. work_order_process 조회 (이중 입고 방지)
        const procResult = await client.query(
          `SELECT id, wo_id, good_qty, concession_qty, target_warehouse_id, seq_no, parent_process_id, is_rework
           FROM work_order_process
           WHERE id = $1 AND company_code = $2`,
          [item.work_order_process_id, companyCode]
        );

        if (procResult.rowCount === 0) {
          results.push({ item_code: item.item_code, status: "not_found", message: "공정을 찾을 수 없음" });
          continue;
        }

        const proc = procResult.rows[0];
        if (proc.target_warehouse_id) {
          results.push({ item_code: item.item_code, status: "already_stored", message: "이미 입고 완료" });
          continue;
        }

        const goodQty = (parseFloat(proc.good_qty || "0")) + (parseFloat(proc.concession_qty || "0"));
        if (goodQty <= 0) {
          results.push({ item_code: item.item_code, status: "no_qty", message: "양품 수량 없음" });
          continue;
        }

        const moveQty = item.quantity > 0 ? Math.min(item.quantity, goodQty) : goodQty;
        const toLocation = item.to_location || "";

        // 2. inventory_stock UPSERT (도착 창고)
        const existingTo = await client.query(
          `SELECT id, current_qty FROM inventory_stock
           WHERE company_code = $1 AND item_code = $2
             AND warehouse_code = $3
             AND COALESCE(location_code, '') = COALESCE($4, '')
           LIMIT 1`,
          [companyCode, item.item_code, item.to_warehouse, toLocation]
        );

        if (existingTo.rows.length > 0) {
          const toQty = parseFloat(existingTo.rows[0].current_qty) || 0;
          await client.query(
            `UPDATE inventory_stock
             SET current_qty = $1, last_in_date = NOW(), updated_date = NOW(), writer = $2
             WHERE id = $3 AND company_code = $4`,
            [String(toQty + moveQty), userId, existingTo.rows[0].id, companyCode]
          );
        } else {
          await client.query(
            `INSERT INTO inventory_stock (id, company_code, item_code, warehouse_code, location_code, current_qty, safety_qty, last_in_date, created_date, updated_date, writer)
             VALUES (gen_random_uuid()::text, $1, $2, $3, $4, $5, '0', NOW(), NOW(), NOW(), $6)`,
            [companyCode, item.item_code, item.to_warehouse, toLocation, String(moveQty), userId]
          );
        }

        // 3. work_order_process에 target_warehouse_id 저장 (현재 행 + 마스터 행)
        const idsToUpdate = [item.work_order_process_id];
        if (proc.parent_process_id) {
          idsToUpdate.push(proc.parent_process_id);
        }

        for (const id of idsToUpdate) {
          await client.query(
            `UPDATE work_order_process
             SET target_warehouse_id = $3,
                 target_location_code = $4,
                 writer = $5,
                 updated_date = NOW()
             WHERE id = $1 AND company_code = $2`,
            [id, companyCode, item.to_warehouse, toLocation || null, userId]
          );
        }

        // 4. 리워크 마크 해제
        if (proc.is_rework === "Y") {
          await client.query(
            `UPDATE work_order_process SET is_rework = NULL, updated_date = NOW()
             WHERE id = $1 AND company_code = $2`,
            [item.work_order_process_id, companyCode]
          );
        }

        // 5. inventory_history 1건 (공정 입고)
        const toBalance = existingTo.rows.length > 0
          ? (parseFloat(existingTo.rows[0].current_qty) || 0) + moveQty
          : moveQty;

        const remarkJson = JSON.stringify({
          type: "process_inbound",
          source: "process",
          work_order_process_id: item.work_order_process_id,
          to_warehouse: item.to_warehouse,
          quantity: moveQty,
        });

        await client.query(
          `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code,
             transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
           VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '공정입고', NOW(), $5, $6, $7, $8, $9, NOW())`,
          [companyCode, item.item_code, item.to_warehouse, toLocation,
           String(moveQty), String(toBalance), remarkJson, userId, userName]
        );

        moveCount++;
        results.push({ item_code: item.item_code, status: "ok" });

      } else {
        // ===== 창고 → 창고 이동 (기존 로직) =====
        if (!item.item_code || !item.from_warehouse || !item.to_warehouse || !item.quantity || item.quantity <= 0) {
          results.push({ item_code: item.item_code, status: "invalid", message: "필수값 누락 또는 수량 오류" });
          continue;
        }

        // 1. 출발 창고 재고 확인 + 차감
        const fromConditions = [
          "company_code = $1",
          "item_code = $2",
          "warehouse_code = $3",
        ];
        const fromParams: any[] = [companyCode, item.item_code, item.from_warehouse];

        if (item.stock_id) {
          fromConditions.push(`id = $${fromParams.length + 1}`);
          fromParams.push(item.stock_id);
        }

        const fromStock = await client.query(
          `SELECT id, current_qty, location_code
           FROM inventory_stock
           WHERE ${fromConditions.join(" AND ")}
           LIMIT 1`,
          fromParams
        );

        if (fromStock.rowCount === 0) {
          results.push({ item_code: item.item_code, status: "not_found", message: "출발 재고 없음" });
          continue;
        }

        const fromRow = fromStock.rows[0];
        const currentQty = parseFloat(fromRow.current_qty) || 0;

        if (currentQty < item.quantity) {
          results.push({ item_code: item.item_code, status: "insufficient", message: `재고 부족 (현재: ${currentQty})` });
          continue;
        }

        // 출발 재고 차감
        const newFromQty = currentQty - item.quantity;
        await client.query(
          `UPDATE inventory_stock
           SET current_qty = $1, updated_date = NOW(), writer = $2
           WHERE id = $3 AND company_code = $4`,
          [String(newFromQty), userId, fromRow.id, companyCode]
        );

        // 2. 도착 창고 UPSERT
        const toLocation = item.to_location || "";
        const existingTo = await client.query(
          `SELECT id, current_qty FROM inventory_stock
           WHERE company_code = $1 AND item_code = $2
             AND warehouse_code = $3
             AND COALESCE(location_code, '') = COALESCE($4, '')
           LIMIT 1`,
          [companyCode, item.item_code, item.to_warehouse, toLocation]
        );

        if (existingTo.rows.length > 0) {
          const toQty = parseFloat(existingTo.rows[0].current_qty) || 0;
          await client.query(
            `UPDATE inventory_stock
             SET current_qty = $1, updated_date = NOW(), writer = $2
             WHERE id = $3 AND company_code = $4`,
            [String(toQty + item.quantity), userId, existingTo.rows[0].id, companyCode]
          );
        } else {
          await client.query(
            `INSERT INTO inventory_stock (id, company_code, item_code, warehouse_code, location_code, current_qty, created_date, writer)
             VALUES (gen_random_uuid()::text, $1, $2, $3, $4, $5, NOW(), $6)`,
            [companyCode, item.item_code, item.to_warehouse, toLocation, String(item.quantity), userId]
          );
        }

        // 3. inventory_history 2건 (출발 -수량, 도착 +수량)
        const remarkJson = JSON.stringify({
          type: "move",
          from_warehouse: item.from_warehouse,
          to_warehouse: item.to_warehouse,
          quantity: item.quantity,
        });

        // 출발 이력 (마이너스)
        await client.query(
          `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code,
             transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
           VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '이동', NOW(), $5, $6, $7, $8, $9, NOW())`,
          [companyCode, item.item_code, item.from_warehouse, item.from_location || "",
           String(-item.quantity), String(newFromQty), remarkJson, userId, userName]
        );

        // 도착 이력 (플러스)
        const toBalance = existingTo.rows.length > 0
          ? (parseFloat(existingTo.rows[0].current_qty) || 0) + item.quantity
          : item.quantity;

        await client.query(
          `INSERT INTO inventory_history (id, company_code, item_code, warehouse_code, location_code,
             transaction_type, transaction_date, quantity, balance_qty, remark, writer, manager_name, created_date)
           VALUES (gen_random_uuid()::text, $1, $2, $3, $4, '이동', NOW(), $5, $6, $7, $8, $9, NOW())`,
          [companyCode, item.item_code, item.to_warehouse, toLocation,
           String(item.quantity), String(toBalance), remarkJson, userId, userName]
        );

        moveCount++;
        results.push({ item_code: item.item_code, status: "ok" });
      }
    }

    await client.query("COMMIT");

    logger.info("[pop/inventory] move-batch 완료", {
      companyCode, userId, moveCount, total: items.length
    });

    return res.json({
      success: true,
      message: `${moveCount}건 이동 완료`,
      data: { moveCount, results }
    });

  } catch (error: any) {
    await client.query("ROLLBACK").catch(() => {});
    logger.error("[pop/inventory] move-batch 오류:", error);
    return res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};
