/**
 * 품질관리 — wace_plm 의 QualityController + quality.xml 을 1:1 이식.
 *
 *   GET /api/quality/incoming-request        → 수입검사 요청 (incomingInspectionGridList)
 *   GET /api/quality/incoming-mgmt           → 수입검사 진행 (incomingInspectionProgressGridList)
 *   GET /api/quality/process-inspection      → 공정검사 관리 (processInspectionGridList)
 *   GET /api/quality/semi-product-inspection → 반제품검사 관리 (semiProductInspectionGridList)
 *
 * 컬럼/필터/조인은 wace_plm 의 JSP + MyBatis 와 동일하게 구성한다.
 * 일부 조인 테이블(arrival_plan, inventory_mgmt_in 등)이 vexplor_rps 에 없을 수 있어
 * LEFT JOIN 으로만 묶고, 미존재 시 SQL 실패해도 빈 응답으로 fallback.
 */

import { Router, Request, Response } from "express";
import { authenticateToken } from "../middleware/authMiddleware";
import { getPool } from "../database/db";

const router = Router();
router.use(authenticateToken);

function emptyList(res: Response) {
  return res.json({
    success: true,
    list: [],
    pagination: { page: 1, pageSize: 100, total: 0, totalPages: 0 },
  });
}

// ─── 1. 수입검사 요청 ──────────────────────────────────────────
// wace quality.xml getIncomingInspectionList — 발주서 기반.
router.get("/incoming-request", async (req: Request, res: Response) => {
  try {
    const pool = getPool();
    const q = req.query as Record<string, string>;

    const where: string[] = ["1=1"];
    const params: any[] = [];
    const add = (sql: string, ...v: any[]) => { v.forEach(x => params.push(x)); where.push(sql.replace(/\?/g, () => "$" + params.length)); };

    if (q.search_proposal_no)         add("COALESCE(srm.request_mng_no, '') ILIKE ?", `%${q.search_proposal_no}%`);
    if (q.search_purchase_order_no)   add("pom.purchase_order_no ILIKE ?", `%${q.search_purchase_order_no}%`);
    if (q.project_no)                 add("COALESCE(cm.project_no, '') ILIKE ?", `%${q.project_no}%`);
    if (q.search_part_no)             add("COALESCE(pm.part_no, '') ILIKE ?", `%${q.search_part_no}%`);
    if (q.search_part_name)           add("COALESCE(pm.part_name, '') ILIKE ?", `%${q.search_part_name}%`);
    if (q.search_partner)             add("pom.partner_objid::text = ?", q.search_partner);
    if (q.search_product_cd)          add("pom.product_code::text = ?", q.search_product_cd);
    if (q.search_inspection_yn)       add("COALESCE(iid.inspection_yn, '미요청') = ?", q.search_inspection_yn);
    if (q.request_user_id)            add("COALESCE(iid.request_user_id, pom.writer) = ?", q.request_user_id);
    if (q.request_start_date)         add("COALESCE(NULLIF(iid.request_date, ''), TO_CHAR(pom.regdate, 'YYYY-MM-DD')) >= ?", q.request_start_date);
    if (q.request_end_date)           add("COALESCE(NULLIF(iid.request_date, ''), TO_CHAR(pom.regdate, 'YYYY-MM-DD')) <= ?", q.request_end_date);
    if (q.search_request_status) {
      const s = q.search_request_status;
      if (s === "미요청") where.push("iid.objid IS NULL");
      else if (s === "요청중") where.push("iid.objid IS NOT NULL AND iid.inspection_date IS NULL");
      else if (s === "요청완료") where.push("iid.inspection_date IS NOT NULL");
    }

    const sql = `
      SELECT
        pom.objid::text                       AS objid,
        COALESCE(srm.request_mng_no, '')         AS proposal_no,
        pom.purchase_order_no                 AS purchase_order_no,
        COALESCE(cm.project_no, '')           AS project_no,
        COALESCE(ci.code_name, '')            AS product_name,
        COALESCE(pm.part_no, '')              AS part_no,
        COALESCE(pm.part_name, '')            AS part_name,
        COALESCE(client.client_nm, '')     AS partner_name,
        '-'                                    AS delivery_status,
        COALESCE(NULLIF(iid.request_date, ''), TO_CHAR(pom.regdate, 'YYYY-MM-DD')) AS request_date,
        COALESCE(ui.user_name, '')            AS request_user_name,
        COALESCE(iid.inspection_yn, '-')      AS inspection_yn,
        CASE
          WHEN iid.objid IS NULL THEN '미요청'
          WHEN iid.inspection_date IS NOT NULL THEN '요청완료'
          ELSE '요청중'
        END                                    AS request_status
      FROM purchase_order_master pom
      LEFT JOIN sales_request_master srm  ON srm.objid = pom.sales_request_objid
      LEFT JOIN contract_mgmt cm          ON cm.objid  = pom.contract_mgmt_objid
      LEFT JOIN code_info ci              ON ci.code_value = pom.product_code
      LEFT JOIN client_mng client         ON client.objid = pom.partner_objid
      -- purchase_order_master 에 part_objid 가 없어 part_mng 직접 조인 불가. part_no/name 은 빈값으로 표시.
      LEFT JOIN part_mng pm               ON FALSE
      LEFT JOIN incoming_inspection_detail iid ON iid.purchase_order_master_objid = pom.objid::text
      LEFT JOIN user_info ui              ON ui.user_id = COALESCE(iid.request_user_id, pom.writer)
      WHERE ${where.join(" AND ")}
      ORDER BY pom.regdate DESC NULLS LAST, pom.objid DESC
      LIMIT 500
    `;
    const r = await pool.query(sql, params);
    res.json({ success: true, list: r.rows, pagination: { page: 1, pageSize: 500, total: r.rows.length, totalPages: 1 } });
  } catch (err: any) {
    console.warn("[quality/incoming-request] fallback empty:", err?.message);
    emptyList(res);
  }
});

// ─── 2. 수입검사 진행 ──────────────────────────────────────────
// wace quality.xml getIncomingInspectionProgressList — INCOMING_INSPECTION_DETAIL 기반,
// 검사된 행만 (INSPECTION_YN = '검사'). DEFECT 합계로 불량률 계산.
router.get("/incoming-mgmt", async (req: Request, res: Response) => {
  try {
    const pool = getPool();
    const q = req.query as Record<string, string>;

    // wace_plm 원본은 inspection_yn='검사' 만 노출하지만, '스킵' 행도 진행 화면에 보이도록 완화.
    // 검사 완료만 좁히려면 search_inspection_status='완료' 사용.
    const where: string[] = ["1=1"];
    const params: any[] = [];
    const add = (sql: string, ...v: any[]) => { v.forEach(x => params.push(x)); where.push(sql.replace(/\?/g, () => "$" + params.length)); };

    if (q.search_proposal_no)         add("COALESCE(srm.request_mng_no, '') ILIKE ?", `%${q.search_proposal_no}%`);
    if (q.search_purchase_order_no)   add("pom.purchase_order_no ILIKE ?", `%${q.search_purchase_order_no}%`);
    if (q.project_no)                 add("COALESCE(cm.project_no, '') ILIKE ?", `%${q.project_no}%`);
    if (q.search_part_no)             add("COALESCE(pm.part_no, '') ILIKE ?", `%${q.search_part_no}%`);
    if (q.search_part_name)           add("COALESCE(pm.part_name, '') ILIKE ?", `%${q.search_part_name}%`);
    if (q.search_partner)             add("pom.partner_objid::text = ?", q.search_partner);
    if (q.search_product_cd)          add("pom.product_code::text = ?", q.search_product_cd);
    if (q.inspector_id)               add("iid.inspector_id = ?", q.inspector_id);
    if (q.inspection_start_date)      add("COALESCE(NULLIF(iid.inspection_date, ''), TO_CHAR(iid.reg_date, 'YYYY-MM-DD')) >= ?", q.inspection_start_date);
    if (q.inspection_end_date)        add("COALESCE(NULLIF(iid.inspection_date, ''), TO_CHAR(iid.reg_date, 'YYYY-MM-DD')) <= ?", q.inspection_end_date);
    if (q.search_inspection_status) {
      const s = q.search_inspection_status;
      if (s === "완료") where.push("iid.inspection_date IS NOT NULL AND COALESCE(iid.inspection_result, '') <> ''");
      else if (s === "진행중") where.push("iid.inspection_date IS NULL");
    }

    const sql = `
      SELECT
        iid.objid::text                      AS objid,
        COALESCE(NULLIF(iid.inspection_date, ''), TO_CHAR(iid.reg_date, 'YYYY-MM-DD')) AS inspection_date,
        COALESCE(ui_ins.user_name, '')       AS inspector_name,
        COALESCE(srm.request_mng_no, '')        AS proposal_no,
        pom.purchase_order_no                AS purchase_order_no,
        COALESCE(cm.project_no, '')          AS project_no,
        COALESCE(ci.code_name, '')           AS product_name,
        COALESCE(pm.product_name, '')        AS model_name,
        COALESCE(pm.part_no, '')             AS part_no,
        COALESCE(pm.part_name, '')           AS part_name,
        COALESCE(client.client_nm, '')    AS partner_name,
        TO_CHAR(iid.reg_date, 'YYYY-MM-DD')  AS delivery_date,
        iid.inspection_qty                   AS delivery_qty,
        '-'                                   AS delivery_status,
        iid.inspection_qty                   AS inspection_qty,
        COALESCE((SELECT SUM(d.defect_qty) FROM incoming_inspection_defect d WHERE d.inspection_detail_objid = iid.objid), 0) AS defect_qty_sum,
        CASE
          WHEN iid.inspection_qty IS NULL OR iid.inspection_qty = 0 THEN 0
          ELSE ROUND(COALESCE((SELECT SUM(d.defect_qty) FROM incoming_inspection_defect d WHERE d.inspection_detail_objid = iid.objid), 0) / iid.inspection_qty * 100, 2)
        END                                   AS defect_rate,
        CASE
          WHEN iid.inspection_date IS NULL THEN '미검사'
          WHEN COALESCE(iid.inspection_result, '') = '' THEN '진행중'
          ELSE '완료'
        END                                   AS inspection_result,
        0                                     AS inspection_file_cnt
      FROM incoming_inspection_detail iid
      LEFT JOIN purchase_order_master pom  ON pom.objid::text = iid.purchase_order_master_objid
      LEFT JOIN sales_request_master srm   ON srm.objid = pom.sales_request_objid
      LEFT JOIN contract_mgmt cm           ON cm.objid  = pom.contract_mgmt_objid
      LEFT JOIN code_info ci               ON ci.code_value = pom.product_code
      LEFT JOIN client_mng client          ON client.objid = pom.partner_objid
      LEFT JOIN part_mng pm                ON pm.objid = pom.part_objid
      LEFT JOIN user_info ui_ins           ON ui_ins.user_id = iid.inspector_id
      WHERE ${where.join(" AND ")}
      ORDER BY COALESCE(NULLIF(iid.inspection_date, ''), TO_CHAR(iid.reg_date, 'YYYY-MM-DD')) DESC NULLS LAST, iid.objid DESC
      LIMIT 500
    `;
    const r = await pool.query(sql, params);
    res.json({ success: true, list: r.rows, pagination: { page: 1, pageSize: 500, total: r.rows.length, totalPages: 1 } });
  } catch (err: any) {
    console.warn("[quality/incoming-mgmt] fallback empty:", err?.message);
    emptyList(res);
  }
});

// ─── 3. 공정검사 관리 ──────────────────────────────────────────
// wace quality.xml getProcessInspectionList — 마스터별 디테일 SUM.
router.get("/process-inspection", async (req: Request, res: Response) => {
  try {
    const pool = getPool();
    const q = req.query as Record<string, string>;

    const where: string[] = ["1=1"];
    const params: any[] = [];
    const add = (sql: string, ...v: any[]) => { v.forEach(x => params.push(x)); where.push(sql.replace(/\?/g, () => "$" + params.length)); };

    if (q.search_project_no)               add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 LEFT JOIN project_mgmt pj ON pj.objid::text = NULLIF(pid2.project_objid, '') WHERE pid2.master_objid = pim.objid AND COALESCE(pj.project_no, '') ILIKE ?)", `%${q.search_project_no}%`);
    if (q.productType)                     add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 LEFT JOIN project_mgmt pj ON pj.objid::text = NULLIF(pid2.project_objid, '') WHERE pid2.master_objid = pim.objid AND pj.product::text = ?)", q.productType);
    if (q.search_part_no)                  add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.part_no ILIKE ?)", `%${q.search_part_no}%`);
    if (q.search_part_name)                add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.part_name ILIKE ?)", `%${q.search_part_name}%`);
    if (q.search_work_env_status)          add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.work_env_status = ?)", q.search_work_env_status);
    if (q.search_measuring_device)         add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.measuring_device = ?)", q.search_measuring_device);
    if (q.search_inspector)                add("pim.inspector_id = ?", q.search_inspector);
    if (q.search_inspection_date_from)     add("pim.inspection_date >= ?", q.search_inspection_date_from);
    if (q.search_inspection_date_to)       add("pim.inspection_date <= ?", q.search_inspection_date_to);
    if (q.search_inspection_result)        add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.inspection_result = ?)", q.search_inspection_result);
    if (q.search_process_cd)               add("EXISTS (SELECT 1 FROM process_inspection_detail pid2 WHERE pid2.master_objid = pim.objid AND pid2.process_cd = ?)", q.search_process_cd);

    // pim.inspection_date 가 varchar 이고 빈 문자열인 경우가 많아 reg_date 로 fallback.
    // pid.project_objid 도 varchar 빈 문자열이 다수라 NULLIF + 안전 캐스트 필요.
    const sql = `
      SELECT
        pim.objid::text                      AS objid,
        COALESCE(NULLIF(pim.inspection_date, ''), TO_CHAR(pim.reg_date, 'YYYY-MM-DD')) AS inspection_date,
        COALESCE(ui_ins.user_name, ui_w.user_name, pim.writer, '') AS inspector_name,
        COALESCE(MIN(pj.project_no), '')     AS project_no,
        COALESCE(MIN(ci.code_name), '')      AS product_name,
        COALESCE(MIN(pid.part_no), '')       AS part_no,
        COALESCE(MIN(pid.part_name), '')     AS part_name,
        COALESCE(SUM(pid.inspection_qty), 0) AS inspection_qty,
        COALESCE(SUM(pid.defect_qty), 0)     AS defect_qty,
        COALESCE(MAX(pid.work_env_status), '')  AS work_env_status,
        COALESCE(MAX(pid.measuring_device), '') AS measuring_device,
        CASE
          WHEN SUM(pid.defect_qty) > 0 THEN 'NG'
          WHEN COUNT(pid.objid) > 0 THEN 'OK'
          ELSE ''
        END                                  AS inspection_result,
        0                                    AS process_inspection_file_cnt
      FROM process_inspection_master pim
      LEFT JOIN process_inspection_detail pid ON pid.master_objid = pim.objid
      LEFT JOIN project_mgmt pj               ON pj.objid::text = NULLIF(pid.project_objid, '')
      LEFT JOIN code_info ci                  ON ci.objid::text = pj.product::text
      LEFT JOIN user_info ui_ins              ON ui_ins.user_id = NULLIF(pim.inspector_id, '')
      LEFT JOIN user_info ui_w                ON ui_w.user_id = pim.writer
      WHERE ${where.join(" AND ")}
      GROUP BY pim.objid, pim.inspection_date, pim.reg_date, pim.writer, ui_ins.user_name, ui_w.user_name
      ORDER BY COALESCE(NULLIF(pim.inspection_date, ''), TO_CHAR(pim.reg_date, 'YYYY-MM-DD')) DESC NULLS LAST, pim.objid DESC
      LIMIT 500
    `;
    const r = await pool.query(sql, params);
    res.json({ success: true, list: r.rows, pagination: { page: 1, pageSize: 500, total: r.rows.length, totalPages: 1 } });
  } catch (err: any) {
    console.warn("[quality/process-inspection] fallback empty:", err?.message);
    emptyList(res);
  }
});

// ─── 4. 반제품검사 관리 ────────────────────────────────────────
// wace quality.xml getSemiProductInspectionList — DATA_TYPE='GOOD' 마스터,
// 동일 INSPECTION_GROUP_ID 의 'DEFECT' 행 SUM 으로 불량/재생/최종양품 산정.
router.get("/semi-product-inspection", async (req: Request, res: Response) => {
  try {
    const pool = getPool();
    const q = req.query as Record<string, string>;

    const where: string[] = ["g.data_type = 'GOOD'"];
    const params: any[] = [];
    const add = (sql: string, ...v: any[]) => { v.forEach(x => params.push(x)); where.push(sql.replace(/\?/g, () => "$" + params.length)); };

    if (q.search_model_name)        add("COALESCE(g.model_name, '') ILIKE ?", `%${q.search_model_name}%`);
    if (q.search_work_order_no)     add("COALESCE(g.work_order_no, '') ILIKE ?", `%${q.search_work_order_no}%`);
    if (q.search_part_no)           add("COALESCE(g.part_no, '') ILIKE ?", `%${q.search_part_no}%`);
    if (q.search_part_name)         add("COALESCE(g.part_name, '') ILIKE ?", `%${q.search_part_name}%`);
    // 반제품 inspection_date 는 date 타입 → reg_date 와 동일 도메인.
    if (q.inspection_start_date)    add("COALESCE(g.inspection_date, g.reg_date::date) >= ?::date", q.inspection_start_date);
    if (q.inspection_end_date)      add("COALESCE(g.inspection_date, g.reg_date::date) <= ?::date", q.inspection_end_date);
    if (q.search_writer)            add("g.writer = ?", q.search_writer);
    if (q.search_defect_type)       add("EXISTS (SELECT 1 FROM pms_quality_semi_product_inspection d2 WHERE d2.inspection_group_id = g.inspection_group_id AND d2.data_type = 'DEFECT' AND d2.defect_type = ?)", q.search_defect_type);
    if (q.search_responsible_dept)  add("EXISTS (SELECT 1 FROM pms_quality_semi_product_inspection d2 WHERE d2.inspection_group_id = g.inspection_group_id AND d2.data_type = 'DEFECT' AND d2.responsible_dept = ?)", q.search_responsible_dept);

    const sql = `
      SELECT
        g.objid::text                              AS objid,
        TO_CHAR(COALESCE(g.inspection_date, g.reg_date::date), 'YYYY-MM-DD') AS inspection_date,
        COALESCE(ui.user_name, g.writer, '')       AS writer_name,
        COALESCE(g.product_type, '')               AS product_type,
        COALESCE(g.model_name, '')                 AS model_name,
        COALESCE(g.work_order_no, '')              AS work_order_no,
        COALESCE(g.part_no, '')                    AS part_no,
        COALESCE(g.part_name, '')                  AS part_name,
        COALESCE(g.receipt_qty, 0)                 AS receipt_qty,
        COALESCE(g.good_qty, 0)                    AS good_qty,
        COALESCE(d.defect_qty_sum, 0)              AS defective_qty,
        CASE
          WHEN COALESCE(g.receipt_qty, 0) = 0 THEN 0
          ELSE ROUND(COALESCE(d.defect_qty_sum, 0) / g.receipt_qty * 100, 2)
        END                                         AS defect_rate,
        COALESCE(d.regen_qty_sum, 0)               AS regeneration_qty,
        COALESCE(g.good_qty, 0) + COALESCE(d.regen_qty_sum, 0) AS final_good_qty
      FROM pms_quality_semi_product_inspection g
      LEFT JOIN LATERAL (
        SELECT
          SUM(defect_qty) AS defect_qty_sum,
          SUM(CASE WHEN disposition_type = '수정완료' THEN defect_qty ELSE 0 END) AS regen_qty_sum
        FROM pms_quality_semi_product_inspection d
        WHERE d.inspection_group_id = g.inspection_group_id AND d.data_type = 'DEFECT'
      ) d ON true
      LEFT JOIN user_info ui ON ui.user_id = g.writer
      WHERE ${where.join(" AND ")}
      ORDER BY COALESCE(g.inspection_date, g.reg_date::date) DESC NULLS LAST, g.objid DESC
      LIMIT 500
    `;
    const r = await pool.query(sql, params);
    res.json({ success: true, list: r.rows, pagination: { page: 1, pageSize: 500, total: r.rows.length, totalPages: 1 } });
  } catch (err: any) {
    console.warn("[quality/semi-product-inspection] fallback empty:", err?.message);
    emptyList(res);
  }
});

export default router;
