/**
 * Amaranth 동기화 대상 테이블 보장 (idempotent migration).
 *  - dept_info / user_info / warehouse_info / customer_mng / account_code_info
 *  - 부팅 시 1회 실행 — 누락 컬럼은 ADD COLUMN IF NOT EXISTS, 신규 테이블은 CREATE TABLE IF NOT EXISTS
 */
import { getPool } from "../database/db";
import { logger } from "../utils/logger";

const STATEMENTS: string[] = [
  // ── customer_mng 누락 컬럼 보충 (Wehago api16S11 응답 필드 전체 커버) ──
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS business_number VARCHAR(32)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS corp_number     VARCHAR(32)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS ceo_name        VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS short_name      VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS biz_condition   VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS biz_item        VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS zip_code        VARCHAR(20)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS tel             VARCHAR(40)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS fax_no          VARCHAR(40)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS hp_no           VARCHAR(40)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS email           VARCHAR(200)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS address         VARCHAR(500)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS address_detail  VARCHAR(500)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS charge_name     VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS charge_tel      VARCHAR(40)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS charge_email    VARCHAR(200)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS customer_type   VARCHAR(20)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS bank_name       VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS bank_account    VARCHAR(80)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS account_owner   VARCHAR(120)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS nation_code     VARCHAR(20)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS currency_code   VARCHAR(20)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS use_yn          VARCHAR(1) DEFAULT 'Y'`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS reg_date        VARCHAR(20)`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS status          VARCHAR(20) DEFAULT 'active'`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS created_at      TIMESTAMP DEFAULT NOW()`,
  `ALTER TABLE customer_mng ADD COLUMN IF NOT EXISTS updated_at      TIMESTAMP DEFAULT NOW()`,

  // ── warehouse_info 누락 컬럼 보충 (Wehago api20A00S00801) ──────
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS warehouse_type      VARCHAR(20)`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS warehouse_type_name VARCHAR(60)`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS parent_loc_code     VARCHAR(40)`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS baseloc_fg          VARCHAR(2)`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS sort_seq            INTEGER`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS use_yn              VARCHAR(1) DEFAULT 'Y'`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS status              VARCHAR(20) DEFAULT 'active'`,
  `ALTER TABLE warehouse_info ADD COLUMN IF NOT EXISTS created_at          TIMESTAMP DEFAULT NOW()`,

  // ── dept_info 누락 컬럼 보충 (Wehago api16S10) ──────────────
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS company_name VARCHAR(200)`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS dept_short_name VARCHAR(120)`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS dept_eng_name VARCHAR(200)`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS dept_level INTEGER`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS sort_seq INTEGER`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS use_yn VARCHAR(1) DEFAULT 'Y'`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS status       VARCHAR(20) DEFAULT 'active'`,
  `ALTER TABLE dept_info ADD COLUMN IF NOT EXISTS regdate      TIMESTAMP DEFAULT NOW()`,

  // ── user_info 누락 컬럼 보충 (Wehago api16S05) ─────────────
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS sabun         VARCHAR(40)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS emp_seq       VARCHAR(40)`, // Wehago 사원 시퀀스 (api16S05/api99u01A11 매칭 키)
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS dept_name     VARCHAR(200)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS position_code VARCHAR(40)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS position_name VARCHAR(120)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS rank_code     VARCHAR(40)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS rank_name     VARCHAR(120)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS join_date     VARCHAR(20)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS retire_date   VARCHAR(20)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS work_status   VARCHAR(20)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS user_name_eng VARCHAR(200)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS out_email     VARCHAR(200)`,
  `ALTER TABLE user_info ADD COLUMN IF NOT EXISTS gender_fg     VARCHAR(2)`,
  // user_info 의 sabun, emp_seq 에 UNIQUE 인덱스 — UPSERT(ON CONFLICT) 동작 보장
  //  - 부분 인덱스(WHERE)는 ON CONFLICT 에서 매칭이 안 되므로 NON-PARTIAL UNIQUE 사용
  //  - PostgreSQL UNIQUE 는 다중 NULL 허용 (기존 NULL 행들은 충돌하지 않음)
  `DROP INDEX IF EXISTS uniq_user_info_sabun`,
  `DROP INDEX IF EXISTS uniq_user_info_emp_seq`,
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_user_info_sabun   ON user_info (sabun)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_user_info_emp_seq ON user_info (emp_seq)`,

  // ── batch_mappings: 조건부 매핑(conditional) 설정 저장용 ──────
  //  mapping_config(JSON 문자열) — { rules:[{when, then}], default }
  `ALTER TABLE batch_mappings ADD COLUMN IF NOT EXISTS mapping_config TEXT`,

  // ── 단일 컬럼 UNIQUE 인덱스 (ON CONFLICT 단일 키 매칭용) ─────────
  //  - 기존 PK 가 (col, company_code) 복합키여서 단일 col 만으론 ON CONFLICT 매칭이 안 됨
  //  - 단일 회사 운영(COMPANY_16) 가정 + Amaranth 마스터 코드는 회사 간 교차되지 않음
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_dept_info_dept_code           ON dept_info         (dept_code)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_warehouse_info_warehouse_code ON warehouse_info    (warehouse_code)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_customer_mng_customer_code    ON customer_mng      (customer_code)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS uniq_account_code_info_account_code ON account_code_info (account_code)`,

  // ── 계정과목 (신규 테이블) — Wehago api11A02 응답 필드 모두 커버 ──
  `CREATE TABLE IF NOT EXISTS account_code_info (
     id            BIGSERIAL PRIMARY KEY,
     account_code  VARCHAR(40)  NOT NULL,
     account_name  VARCHAR(200) NOT NULL,
     account_type  VARCHAR(20),
     use_yn        VARCHAR(1)   DEFAULT 'Y',
     company_code  VARCHAR(40)  NOT NULL,
     created_at    TIMESTAMP    DEFAULT NOW(),
     updated_at    TIMESTAMP    DEFAULT NOW(),
     UNIQUE (account_code, company_code)
   )`,
  // 누락 컬럼 보충 (재실행 안전)
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS group_seq      VARCHAR(40)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS group_code     VARCHAR(40)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS group_name     VARCHAR(200)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS dr_cr_fg       VARCHAR(2)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS sub_disp       VARCHAR(20)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS sub_disp_name  VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS ch_fg          VARCHAR(2)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS ch_fg_name     VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS account_short  VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS bud_fg         VARCHAR(2)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS bud_fg_name    VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS attr_fg        VARCHAR(2)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS attr_fg_name   VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS racct_code     VARCHAR(40)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS racct_name     VARCHAR(200)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS fill_yn        VARCHAR(1)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS ctrl_cds       VARCHAR(120)`,
  `ALTER TABLE account_code_info ADD COLUMN IF NOT EXISTS ext_input_cd   VARCHAR(40)`,
  `CREATE INDEX IF NOT EXISTS idx_account_code_info_company ON account_code_info (company_code)`,

  // ── ERP 동기화 실행 이력 (선택) ──────────────────────────────
  `CREATE TABLE IF NOT EXISTS erp_sync_log (
     id              BIGSERIAL PRIMARY KEY,
     resource        VARCHAR(40)  NOT NULL,
     co_cd           VARCHAR(20),
     company_code    VARCHAR(40),
     fetched_count   INTEGER      DEFAULT 0,
     inserted_count  INTEGER      DEFAULT 0,
     updated_count   INTEGER      DEFAULT 0,
     skipped_count   INTEGER      DEFAULT 0,
     duration_ms     INTEGER      DEFAULT 0,
     success         BOOLEAN      DEFAULT TRUE,
     error_message   TEXT,
     executed_by     VARCHAR(80),
     executed_at     TIMESTAMP    DEFAULT NOW()
   )`,
  `CREATE INDEX IF NOT EXISTS idx_erp_sync_log_resource ON erp_sync_log (resource, executed_at DESC)`,

  // ── batch_configs.row_filter_config: 행 단위 제외 필터 (JSON) ─────
  //   예) {"exclude":[{"column":"loginId","op":"eq","value":"wace"}]}
  //   배치 실행 시 fromData 에서 매칭되는 행을 동기화에서 제외합니다.
  `ALTER TABLE batch_configs ADD COLUMN IF NOT EXISTS row_filter_config TEXT`,
];

export async function ensureErpTables(): 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) {
      // ALTER TABLE 대상이 아예 없는 경우엔 무시
      const msg = e?.message || "";
      if (msg.includes("does not exist")) {
        logger.warn(`[erpTableMigration] 스킵 (대상 미존재): ${stmt.split("\n")[0].trim()}`);
        continue;
      }
      failed++;
      logger.error(`[erpTableMigration] 실패: ${msg}`);
    }
  }

  logger.info(`🔧 ERP 동기화 대상 테이블 점검 완료: 성공 ${success}건 / 실패 ${failed}건`);
}
