-- 운영 DB에 Phase 2(화면이 코드테이블만 보게 되는 단계)를 배포하기 전에 실행한다.
--
-- 결과가 한 줄이라도 나오면, 그 값을 active=false 코드로 code 테이블에 추가한 뒤
-- 배포해야 한다. 값을 지우거나 다른 값으로 바꾸지 않는 이유는 이미 그 값으로 저장된
-- 데이터가 있어서, 지우면 기존 화면에서 값이 조용히 사라지기 때문이다.
--
-- 실행 예:
--   psql -h 192.168.0.60 -U itnhub -d itnhub -f check-orphan-codes.sql
--
-- 고아값을 발견했을 때의 보존 예:
--   insert into code (group_id, code, label, sort_order, active)
--   values ('CONTACT_METHOD', '문자', '문자', 90, false);

select 'review_item.review_major' as source, review_major as value, count(*) as cnt
  from review_item
 where review_major is not null and review_major <> ''
   and review_major not in (select code from code where group_id = 'REVIEW_MAJOR')
 group by review_major

union all
select 'review_item.review_minor', review_minor, count(*)
  from review_item
 where review_minor is not null and review_minor <> ''
   and review_minor not in (select code from code where group_id = 'REVIEW_MINOR')
 group by review_minor

union all
select 'review_item.review_result', review_result, count(*)
  from review_item
 where review_result is not null and review_result <> ''
   and review_result not in (select code from code where group_id = 'REVIEW_RESULT')
 group by review_result

union all
select 'review_item.judged_kogl_type', judged_kogl_type, count(*)
  from review_item
 where judged_kogl_type is not null and judged_kogl_type <> ''
   and judged_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
 group by judged_kogl_type

union all
select 'review_item.kogl_type', kogl_type, count(*)
  from review_item
 where kogl_type is not null and kogl_type <> ''
   and kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
 group by kogl_type

union all
select 'process_item.process_status', process_status, count(*)
  from process_item
 where process_status is not null and process_status <> ''
   and process_status not in (select code from code where group_id = 'PROCESS_STATUS')
 group by process_status

union all
select 'process_item.judged_kogl_type', judged_kogl_type, count(*)
  from process_item
 where judged_kogl_type is not null and judged_kogl_type <> ''
   and judged_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
 group by judged_kogl_type

union all
select 'process_item.prior_kogl_type', prior_kogl_type, count(*)
  from process_item
 where prior_kogl_type is not null and prior_kogl_type <> ''
   and prior_kogl_type not in (select code from code where group_id = 'KOGL_TYPE')
 group by prior_kogl_type

union all
select 'contact_log.method', method, count(*)
  from contact_log
 where method is not null and method <> ''
   and method not in (select code from code where group_id = 'CONTACT_METHOD')
 group by method

order by 1, 2;