/**
 * Customer CS(고객 사후관리) 테이블 idempotent 마이그레이션
 *  - wace_plm 의 CUSTOMER_MNG 테이블 + 공통코드 카테고리 시드 포팅
 *  - 부팅 시 1회 실행
 */
import { getPool } from "../database/db";
import { logger } from "../utils/logger";

/** 공통코드 카테고리 — Customer CS 화면 dropdown 원본 */
export const CS_CODE_CATEGORIES = {
  MNG_TYPE: "0000200",          // 관리유형
  PRODUCT_DIVISION: "0000201",  // 제품구분
  PERFORMER: "0000202",         // 담당자/수행자
  MEASURE_TYPE: "0000203",      // 조치유형 (유상/무상)
  STATUS: "0000204",            // 상태
} as const;

const STATEMENTS: string[] = [
  // ── customer_cs_mng ─────────────────────────────────────────────
  `CREATE TABLE IF NOT EXISTS customer_cs_mng (
     objid             BIGINT PRIMARY KEY,
     mng_number        VARCHAR(40),
     mng_type          VARCHAR(20),
     product_division  VARCHAR(20),
     project_objid     BIGINT,
     reception_date    TIMESTAMP,
     event_location    VARCHAR(200),
     action_date       TIMESTAMP,
     customer_name     VARCHAR(200),
     title             VARCHAR(500),
     performer         VARCHAR(20),
     writer            VARCHAR(80),
     analysis          TEXT,
     measure           TEXT,
     measure_type      VARCHAR(20),
     measure_amount    VARCHAR(40),
     status            VARCHAR(20),
     regdate           TIMESTAMP DEFAULT NOW(),
     editdate          TIMESTAMP,
     company_code      VARCHAR(40)
   )`,
  `CREATE INDEX IF NOT EXISTS idx_customer_cs_mng_status   ON customer_cs_mng (status)`,
  `CREATE INDEX IF NOT EXISTS idx_customer_cs_mng_writer   ON customer_cs_mng (writer)`,
  `CREATE INDEX IF NOT EXISTS idx_customer_cs_mng_reg      ON customer_cs_mng (regdate DESC)`,
  `CREATE INDEX IF NOT EXISTS idx_customer_cs_mng_recept   ON customer_cs_mng (reception_date DESC)`,

  // ── 공통코드 카테고리 시드 (NOT EXISTS — 기존 DB 보존) ────────
  `DO $$
   DECLARE
     base BIGINT := extract(epoch FROM now())::BIGINT;
   BEGIN
     -- 0000200: 관리유형
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE code_id='0000200') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer)
       VALUES (base, '0000200', NULL, '관리유형', 0, 'active', NOW(), 'system-seed');
     END IF;
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE parent_code_id='0000200') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer) VALUES
         (base+1,  '0200001', '0000200', '클레임',     1, 'active', NOW(), 'system-seed'),
         (base+2,  '0200002', '0000200', 'A/S',        2, 'active', NOW(), 'system-seed'),
         (base+3,  '0200003', '0000200', '문의',       3, 'active', NOW(), 'system-seed'),
         (base+4,  '0200004', '0000200', '품질이슈',   4, 'active', NOW(), 'system-seed'),
         (base+5,  '0200005', '0000200', '교환/환불',  5, 'active', NOW(), 'system-seed');
     END IF;

     -- 0000201: 제품구분
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE code_id='0000201') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer)
       VALUES (base+10, '0000201', NULL, '제품구분', 0, 'active', NOW(), 'system-seed');
     END IF;
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE parent_code_id='0000201') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer) VALUES
         (base+11, '0201001', '0000201', '완제품',     1, 'active', NOW(), 'system-seed'),
         (base+12, '0201002', '0000201', '반제품',     2, 'active', NOW(), 'system-seed'),
         (base+13, '0201003', '0000201', '부품',       3, 'active', NOW(), 'system-seed'),
         (base+14, '0201004', '0000201', '서비스',     4, 'active', NOW(), 'system-seed');
     END IF;

     -- 0000202: 담당자(수행자)
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE code_id='0000202') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer)
       VALUES (base+20, '0000202', NULL, '담당자', 0, 'active', NOW(), 'system-seed');
     END IF;
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE parent_code_id='0000202') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer) VALUES
         (base+21, '0202001', '0000202', '영업팀',     1, 'active', NOW(), 'system-seed'),
         (base+22, '0202002', '0000202', '품질팀',     2, 'active', NOW(), 'system-seed'),
         (base+23, '0202003', '0000202', 'CS팀',       3, 'active', NOW(), 'system-seed'),
         (base+24, '0202004', '0000202', '기술팀',     4, 'active', NOW(), 'system-seed');
     END IF;

     -- 0000203: 조치유형
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE code_id='0000203') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer)
       VALUES (base+30, '0000203', NULL, '조치유형', 0, 'active', NOW(), 'system-seed');
     END IF;
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE parent_code_id='0000203') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer) VALUES
         (base+31, '0203001', '0000203', '유상',       1, 'active', NOW(), 'system-seed'),
         (base+32, '0203002', '0000203', '무상',       2, 'active', NOW(), 'system-seed');
     END IF;

     -- 0000204: 상태
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE code_id='0000204') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer)
       VALUES (base+40, '0000204', NULL, 'CS상태', 0, 'active', NOW(), 'system-seed');
     END IF;
     IF NOT EXISTS (SELECT 1 FROM comm_code WHERE parent_code_id='0000204') THEN
       INSERT INTO comm_code (objid, code_id, parent_code_id, code_name, sort_order, status, regdate, writer) VALUES
         (base+41, '0204001', '0000204', '접수',       1, 'active', NOW(), 'system-seed'),
         (base+42, '0204002', '0000204', '진행중',     2, 'active', NOW(), 'system-seed'),
         (base+43, '0204003', '0000204', '조치완료',   3, 'active', NOW(), 'system-seed'),
         (base+44, '0204004', '0000204', '종결',       4, 'active', NOW(), 'system-seed');
     END IF;
   END $$`,

  // ── 메뉴 URL 정규화 (이미 menu_info 의 '고객CS관리' objid=100043 있음) ──
  `UPDATE menu_info SET menu_url = '/COMPANY_16/customer-cs/cs', status = 'active'
     WHERE objid = '100043' AND (menu_url IS NULL OR menu_url = '')`,
];

export async function ensureCustomerCsTables(): Promise<void> {
  const pool = getPool();
  let success = 0;
  let failed = 0;

  for (const stmt of STATEMENTS) {
    try {
      await pool.query(stmt);
      success++;
    } catch (e: any) {
      failed++;
      logger.warn(`[customerCsMigration] 실패 (계속 진행): ${e?.message?.slice(0, 200)}`);
    }
  }

  logger.info(`🌱 Customer CS 마이그레이션 완료: 성공 ${success}건 / 실패 ${failed}건`);
}
