/**
 * wace_plm 운영 DB → 우리 DB 데이터 import 서비스
 *
 *  - source: 211.115.91.141:11132/ilshin (postgres / ilshin0909!!)
 *  - target: 우리 시스템 PostgreSQL 풀 (vexplor_rps)
 *
 *  대상 도메인:
 *    - ECR_MNG       → ecr_mng
 *    - CUSTOMER_MNG  → customer_mng
 *    - APPROVAL / ROUTE / INBOXTASK / APPROVAL_TARGET / APPROVAL_KIND  (옵션)
 *
 *  Idempotent: ON CONFLICT (objid) DO NOTHING — 이미 같은 OBJID 가 있으면 건너뜀
 *  운영 환경에서 직접 호출하지 말고 관리자 명시 액션(API 또는 일회성 boot 옵션)으로만 사용
 */
import { Client as PgClient } from "pg";
import { getPool } from "../database/db";
import { logger } from "../utils/logger";

interface SourceConfig {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
}

// 운영 비밀번호는 코드에 박지 않음 — 환경변수 WACE_PLM_DB_* 로 주입
const DEFAULT_SOURCE: SourceConfig = {
  host:     process.env.WACE_PLM_DB_HOST     || "",
  port:     Number(process.env.WACE_PLM_DB_PORT || 0),
  database: process.env.WACE_PLM_DB_NAME     || "",
  user:     process.env.WACE_PLM_DB_USER     || "",
  password: process.env.WACE_PLM_DB_PASSWORD || "",
};

/** 새 PG 클라이언트로 source 연결 (풀 X). 환경변수 미설정 시 명시적 에러. */
async function openSource(cfg: Partial<SourceConfig> = {}): Promise<PgClient> {
  const merged = { ...DEFAULT_SOURCE, ...cfg };
  if (!merged.host || !merged.port || !merged.database || !merged.user) {
    throw new Error(
      "wace_plm 소스 DB 정보가 비어 있습니다. 환경변수 WACE_PLM_DB_HOST / PORT / NAME / USER / PASSWORD 를 설정하거나 호출 시 cfg 인자로 전달하세요.",
    );
  }
  const c = new PgClient({ ...merged, statement_timeout: 60_000 });
  await c.connect();
  return c;
}

export class WacePlmDataImportService {
  /** ECR_MNG → ecr_mng */
  static async importEcr(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0;
    let skipped = 0;
    try {
      const rows = await src.query(`
        SELECT objid::bigint AS objid,
               ecr_no, product_objid::bigint, part_objid::bigint, upg_no,
               request_cd, title, writer, status_cd, before_contents, after_contents,
               check_user_id, check_date, reg_date
          FROM ecr_mng
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO ecr_mng
             (objid, ecr_no, product_objid, part_objid, upg_no, request_cd, title, writer,
              status_cd, before_contents, after_contents, check_user_id, check_date, reg_date)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
           ON CONFLICT (objid) DO NOTHING`,
          [
            r.objid, r.ecr_no, r.product_objid, r.part_objid, r.upg_no,
            r.request_cd, r.title, r.writer, r.status_cd,
            r.before_contents, r.after_contents,
            r.check_user_id, r.check_date, r.reg_date,
          ],
        );
        if (ins.rowCount === 1) inserted++;
        else skipped++;
      }
      logger.info(`📦 ECR import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** CUSTOMER_MNG → customer_mng */
  static async importCustomerMng(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0;
    let skipped = 0;
    try {
      const rows = await src.query(`
        SELECT objid::bigint, mng_number, mng_type, product_division, project_objid::bigint,
               reception_date, event_location, action_date, customer_name, title,
               performer, writer, analysis, measure, measure_type, measure_amount,
               status, regdate, editdate
          FROM customer_mng
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO customer_cs_mng
             (objid, mng_number, mng_type, product_division, project_objid,
              reception_date, event_location, action_date, customer_name, title,
              performer, writer, analysis, measure, measure_type, measure_amount,
              status, regdate, editdate)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19)
           ON CONFLICT (objid) DO NOTHING`,
          [
            r.objid, r.mng_number, r.mng_type, r.product_division, r.project_objid,
            r.reception_date, r.event_location, r.action_date, r.customer_name, r.title,
            r.performer, r.writer, r.analysis, r.measure, r.measure_type, r.measure_amount,
            r.status, r.regdate, r.editdate,
          ],
        );
        if (ins.rowCount === 1) inserted++;
        else skipped++;
      }
      logger.info(`📦 CUSTOMER_MNG import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** PRODUCT_MGMT → product_mgmt (wace_plm 풀스키마 16 컬럼) */
  static async importProduct(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0, skipped = 0;
    try {
      const rows = await src.query(`
        SELECT objid::bigint, product_category, product_type, product_grade, product_ton, product_boom,
               product_vehicle, product_code, production_flag, regdate, writer, contents, price,
               product_name, product_name_code, note
          FROM product_mgmt
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO product_mgmt
             (objid, product_category, product_type, product_grade, product_ton, product_boom,
              product_vehicle, product_code, production_flag, regdate, writer, contents, price,
              product_name, product_name_code, note)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
           ON CONFLICT (objid) DO NOTHING`,
          [
            r.objid, r.product_category, r.product_type, r.product_grade, r.product_ton, r.product_boom,
            r.product_vehicle, r.product_code, r.production_flag, r.regdate, r.writer, r.contents, r.price,
            r.product_name, r.product_name_code, r.note,
          ],
        );
        if (ins.rowCount === 1) inserted++; else skipped++;
      }
      logger.info(`📦 PRODUCT_MGMT import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** PART_MNG → part_mng (wace_plm 풀스키마 52 컬럼) */
  static async importPart(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0, skipped = 0;
    try {
      const rows = await src.query(`
        SELECT objid::bigint AS objid, product_mgmt_objid, upg_no, part_no, part_name, unit, qty, spec,
               material, weight, part_type, remark, es_spec, ms_spec, change_option, design_apply_point,
               management_flag, revision, status, reg_date, edit_date, writer, is_last, eo_no, eo_temp,
               excel_upload_seq, sourcing_code, sub_material, parent_part_no, design_date, eo_date,
               deploy_date, thickness, width, height, out_diameter, in_diameter, length, supply_code,
               change_type, contract_objid, maker, post_processing, material_code, code1, code2, code3,
               code4, code5, major_category, sub_category, is_new, is_longd
          FROM part_mng
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO part_mng
             (objid, product_mgmt_objid, upg_no, part_no, part_name, unit, qty, spec, material, weight,
              part_type, remark, es_spec, ms_spec, change_option, design_apply_point, management_flag,
              revision, status, reg_date, edit_date, writer, is_last, eo_no, eo_temp, excel_upload_seq,
              sourcing_code, sub_material, parent_part_no, design_date, eo_date, deploy_date, thickness,
              width, height, out_diameter, in_diameter, length, supply_code, change_type, contract_objid,
              maker, post_processing, material_code, code1, code2, code3, code4, code5, major_category,
              sub_category, is_new, is_longd)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,
                   $21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,
                   $41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53)
           ON CONFLICT (objid) DO NOTHING`,
          [
            r.objid, r.product_mgmt_objid, r.upg_no, r.part_no, r.part_name, r.unit, r.qty, r.spec,
            r.material, r.weight, r.part_type, r.remark, r.es_spec, r.ms_spec, r.change_option,
            r.design_apply_point, r.management_flag, r.revision, r.status, r.reg_date, r.edit_date,
            r.writer, r.is_last, r.eo_no, r.eo_temp, r.excel_upload_seq, r.sourcing_code, r.sub_material,
            r.parent_part_no, r.design_date, r.eo_date, r.deploy_date, r.thickness, r.width, r.height,
            r.out_diameter, r.in_diameter, r.length, r.supply_code, r.change_type, r.contract_objid,
            r.maker, r.post_processing, r.material_code, r.code1, r.code2, r.code3, r.code4, r.code5,
            r.major_category, r.sub_category, r.is_new, r.is_longd,
          ],
        );
        if (ins.rowCount === 1) inserted++; else skipped++;
      }
      logger.info(`📦 PART_MNG import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** USER_INFO → user_info (wace_plm 의 ECR mapper 가 직접 참조) */
  static async importUserInfo(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0, skipped = 0;
    try {
      const rows = await src.query(`
        SELECT sabun, user_id, user_password, user_name, user_name_eng, user_name_cn, dept_code,
               dept_name, position_code, position_name, email, tel, cell_phone, user_type, user_type_name,
               regdate, data_type, status, end_date, fax_no, partner_objid, rank
          FROM user_info
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO user_info
             (sabun, user_id, user_password, user_name, user_name_eng, user_name_cn, dept_code,
              dept_name, position_code, position_name, email, tel, cell_phone, user_type, user_type_name,
              regdate, data_type, status, end_date, fax_no, partner_objid, rank)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22)
           ON CONFLICT (user_id) DO NOTHING`,
          [
            r.sabun, r.user_id, r.user_password, r.user_name, r.user_name_eng, r.user_name_cn, r.dept_code,
            r.dept_name, r.position_code, r.position_name, r.email, r.tel, r.cell_phone, r.user_type,
            r.user_type_name, r.regdate, r.data_type, r.status, r.end_date, r.fax_no, r.partner_objid, r.rank,
          ],
        );
        if (ins.rowCount === 1) inserted++; else skipped++;
      }
      logger.info(`📦 USER_INFO import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** COMM_CODE → comm_code (wace_plm 의 0000090/0000099 등 ECR 가 의존하는 공통코드) */
  static async importCommCode(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    let inserted = 0, skipped = 0;
    try {
      const rows = await src.query(`
        SELECT objid::bigint, code_id, parent_code_id, code_name, id, code_cd, ext_val, writer, regdate, status
          FROM comm_code
      `);
      for (const r of rows.rows) {
        const ins = await tgt.query(
          `INSERT INTO comm_code
             (objid, code_id, parent_code_id, code_name, id, code_cd, ext_val, writer, regdate, status)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
           ON CONFLICT (objid) DO NOTHING`,
          [r.objid, r.code_id, r.parent_code_id, r.code_name, r.id, r.code_cd, r.ext_val, r.writer, r.regdate, r.status],
        );
        if (ins.rowCount === 1) inserted++; else skipped++;
      }
      logger.info(`📦 COMM_CODE import 완료: 신규 ${inserted}건 / 기존 ${skipped}건`);
      return { inserted, skipped, total: rows.rows.length };
    } finally {
      await src.end();
    }
  }

  /** APPROVAL/ROUTE/INBOXTASK/APPROVAL_TARGET 일괄 import */
  static async importApprovalAll(cfg?: Partial<SourceConfig>) {
    const src = await openSource(cfg);
    const tgt = getPool();
    const counters: Record<string, { inserted: number; skipped: number; total: number }> = {};
    try {
      // approval
      {
        const rows = await src.query(`SELECT objid::bigint, target_objid::bigint, target_type, approval_seq::bigint, system_type, regdate, status FROM approval`);
        let ins = 0, sk = 0;
        for (const r of rows.rows) {
          const x = await tgt.query(
            `INSERT INTO approval (objid, target_objid, target_type, approval_seq, system_type, regdate, status)
             VALUES ($1,$2,$3,$4,$5,$6,$7) ON CONFLICT (objid) DO NOTHING`,
            [r.objid, r.target_objid, r.target_type, r.approval_seq, r.system_type, r.regdate, r.status],
          );
          x.rowCount === 1 ? ins++ : sk++;
        }
        counters.approval = { inserted: ins, skipped: sk, total: rows.rows.length };
      }
      // route
      {
        const rows = await src.query(`SELECT objid::bigint, target_objid::bigint, approval_objid::bigint, route_seq::bigint, approval_title, approval_desc, system_type, writer, regdate, status FROM route`);
        let ins = 0, sk = 0;
        for (const r of rows.rows) {
          const x = await tgt.query(
            `INSERT INTO route (objid, target_objid, approval_objid, route_seq, approval_title, approval_desc, system_type, writer, regdate, status)
             VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) ON CONFLICT (objid) DO NOTHING`,
            [r.objid, r.target_objid, r.approval_objid, r.route_seq, r.approval_title, r.approval_desc, r.system_type, r.writer, r.regdate, r.status],
          );
          x.rowCount === 1 ? ins++ : sk++;
        }
        counters.route = { inserted: ins, skipped: sk, total: rows.rows.length };
      }
      // approval_target
      {
        const rows = await src.query(`SELECT objid::bigint, master_target_objid::bigint, target_objid::bigint, approval_objid::bigint, writer, regdate FROM approval_target`);
        let ins = 0, sk = 0;
        for (const r of rows.rows) {
          const x = await tgt.query(
            `INSERT INTO approval_target (objid, master_target_objid, target_objid, approval_objid, writer, regdate)
             VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (objid) DO NOTHING`,
            [r.objid, r.master_target_objid, r.target_objid, r.approval_objid, r.writer, r.regdate],
          );
          x.rowCount === 1 ? ins++ : sk++;
        }
        counters.approval_target = { inserted: ins, skipped: sk, total: rows.rows.length };
      }
      // inboxtask
      {
        const rows = await src.query(`SELECT objid::bigint, seq::bigint, approval_type, target_objid::bigint, approval_objid::bigint, route_objid::bigint, target_user_id, regdate, status FROM inboxtask`);
        let ins = 0, sk = 0;
        for (const r of rows.rows) {
          const x = await tgt.query(
            `INSERT INTO inboxtask (objid, seq, approval_type, target_objid, approval_objid, route_objid, target_user_id, regdate, status)
             VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT (objid) DO NOTHING`,
            [r.objid, r.seq, r.approval_type, r.target_objid, r.approval_objid, r.route_objid, r.target_user_id, r.regdate, r.status],
          );
          x.rowCount === 1 ? ins++ : sk++;
        }
        counters.inboxtask = { inserted: ins, skipped: sk, total: rows.rows.length };
      }
      logger.info(`📦 APPROVAL 일괄 import 완료: ${JSON.stringify(counters)}`);
      return counters;
    } finally {
      await src.end();
    }
  }

  /** 모든 import 한 번에 — 마스터(공통코드/사용자/제품/부품) → 트랜잭션(ECR/CS/결재) 순 */
  static async importAll(cfg?: Partial<SourceConfig>) {
    const result: Record<string, any> = {};
    try { result.commCode = await this.importCommCode(cfg); } catch (e: any) { result.commCode = { error: e?.message }; }
    try { result.userInfo = await this.importUserInfo(cfg); } catch (e: any) { result.userInfo = { error: e?.message }; }
    try { result.product  = await this.importProduct(cfg);  } catch (e: any) { result.product  = { error: e?.message }; }
    try { result.part     = await this.importPart(cfg);     } catch (e: any) { result.part     = { error: e?.message }; }
    try { result.ecr      = await this.importEcr(cfg);      } catch (e: any) { result.ecr      = { error: e?.message }; }
    try { result.cs       = await this.importCustomerMng(cfg); } catch (e: any) { result.cs       = { error: e?.message }; }
    try { result.approval = await this.importApprovalAll(cfg); } catch (e: any) { result.approval = { error: e?.message }; }
    return result;
  }
}
