-- 품질관리 — ilshin DB 운영 스키마와 동일하게 vexplor_rps 에 6 개 테이블 정합.
-- 원본: 211.115.91.141:11132/ilshin (wace_plm 운영 DB)
-- 컬럼 정의는 information_schema.columns 에서 그대로 추출.

-- 1) 고객 CS 마스터 (wace_plm 의 실제 운영 테이블)
CREATE TABLE IF NOT EXISTS public.customer_service_mgmt (
  objid              varchar PRIMARY KEY,
  service_no         varchar,
  product            varchar,
  contract_objid     varchar,
  cs_category        varchar,
  warranty           varchar,
  manager_id         varchar,
  act_date           varchar,
  category_h         varchar,
  category_m         varchar,
  category_l         varchar,
  title              varchar,
  before_contents    text,
  after_contents     text,
  writer             varchar,
  regdate            timestamp,
  status             varchar,
  total_sup_price    varchar,
  total_work_day     varchar,
  total_work_person  varchar,
  total_work_day_m   varchar,
  total_labor_cost   varchar,
  total_expenses     varchar
);

-- 2) 고객 CS 부품 (CS 마스터 자식)
CREATE TABLE IF NOT EXISTS public.customer_service_part (
  objid         varchar PRIMARY KEY,
  parent_objid  varchar,
  part_no       varchar,
  part_name     varchar,
  spec          varchar,
  qty           varchar,
  cur_qty       varchar,
  price         varchar,
  sup_price     varchar
);

-- 3) 고객 CS 작업시간 (CS 부품 자식 — 인건/경비 산정)
CREATE TABLE IF NOT EXISTS public.customer_service_workingtime (
  objid         varchar PRIMARY KEY,
  parent_objid  varchar,
  supply_objid  varchar,
  form_date     varchar,
  to_date       varchar,
  work_day      varchar,
  work_person   varchar,
  work_day_m    varchar,
  labor_cost    varchar,
  expenses      varchar
);

-- 4) 검사관리 — 내부검사 + 입고검사를 단일 테이블로 통합 보관
CREATE TABLE IF NOT EXISTS public.inspection_mgmt (
  objid                        varchar PRIMARY KEY,
  parent_objid                 varchar,
  unit_code                    varchar,
  internal_inspection_date     varchar,
  internal_inspection_result   varchar,
  internal_inspection_id       varchar,
  admission_inspection_date    varchar,
  admission_inspection_result  varchar,
  admission_inspection_id      varchar,
  regdate                      timestamp,
  writer                       varchar
);

-- 5) 출하 불량 이력
CREATE TABLE IF NOT EXISTS public.delivery_history_defect (
  objid                      varchar PRIMARY KEY,
  purchase_order_part_objid  varchar,
  defect_qty                 varchar,
  defect_reason_cd           varchar,
  writer                     varchar,
  regdate                    timestamp
);

-- 6) ecr_mng 컬럼 동기화 (ilshin 에는 있지만 vexplor_rps 에는 없는 컬럼)
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS project_no   varchar;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS customer_cd  varchar;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS equip_name   varchar;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS design_dept  varchar;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS unit_cd      varchar;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS memo         text;
ALTER TABLE public.ecr_mng ADD COLUMN IF NOT EXISTS check_result varchar;