-- ============================================================
-- part_mng 분리 검증 — wace 도메인 메뉴가 part_mng를 참조하는지 정합성 확인
-- ============================================================

\echo ''
\echo '[1] 카운트'
SELECT 'item_info numeric id (wace)' AS kind, COUNT(*) FROM item_info WHERE id ~ '^-?[0-9]+$'
UNION ALL SELECT 'part_mng total',                COUNT(*) FROM part_mng
UNION ALL SELECT 'part_mng status active/release/활성', COUNT(*) FROM part_mng WHERE LOWER(COALESCE(status,'')) IN ('active','release','활성');

\echo ''
\echo '[2] 견적관리 라인 JOIN — 26C-0801'
SELECT CI.seq, CI.part_objid,
       COALESCE(PM.part_no,   CI.part_no)   AS master_part_no,
       COALESCE(PM.part_name, CI.part_name) AS master_part_name
  FROM contract_item CI
  LEFT JOIN part_mng PM ON PM.objid::varchar = CI.part_objid
 WHERE CI.contract_objid='-1778190592' AND CI.status='ACTIVE'
 ORDER BY CI.seq;

\echo ''
\echo '[3] /sales/parts 호출 결과 — 8,173건 기대'
SELECT COUNT(*) AS cnt FROM part_mng
 WHERE LOWER(COALESCE(status, '')) IN ('active', 'release', '활성')
   AND part_no   IS NOT NULL AND part_no   <> ''
   AND part_name IS NOT NULL AND part_name <> '';

\echo ''
\echo '[4] 영업관리 4개 메뉴 그리드 SQL 정합성 (라인 JOIN)'
\echo '  - 견적관리 26C-0801 ITEM_SUMMARY'
SELECT T.contract_no,
       CASE WHEN COUNT(*) = 1 THEN MIN(COALESCE(PM.part_name, CI.part_name))
            WHEN COUNT(*) > 1 THEN MIN(COALESCE(PM.part_name, CI.part_name)) || ' 외 ' || (COUNT(*) - 1) || '건'
            ELSE '' END AS item_summary
  FROM contract_mgmt T
  JOIN contract_item CI ON CI.contract_objid = T.objid AND CI.status='ACTIVE'
  LEFT JOIN part_mng PM ON PM.objid::varchar = CI.part_objid
 WHERE T.contract_no IN ('26C-0801','26C-0797','26C-0796')
 GROUP BY T.contract_no
 ORDER BY T.contract_no DESC;

\echo ''
\echo '[5] 잡 데이터 제거 확인 (item_info에 있는 -20260126-, 0 등은 part_mng에 없어야)'
SELECT 'item_info garbage'                          AS kind, COUNT(*) FROM item_info WHERE item_number IN ('-20260126-', '-20260126-____')
UNION ALL SELECT 'part_mng garbage (0건 기대)',           COUNT(*) FROM part_mng WHERE part_no    IN ('-20260126-', '-20260126-____');