/**
 * ERP → 내부 DB 동기화 서비스
 *
 * 외부 ERP(Wehago/RPS)에서 사원/부서/창고/거래처/계정과목 정보를 가져와
 * 내부 PostgreSQL 테이블(user_info / dept_info / warehouse_info / customer_mng / account_code_info)에
 * UPSERT(있으면 갱신, 없으면 삽입) 합니다.
 *
 * 배치 관리 화면(자동화 관리 > 배치 관리)에서 호출됩니다.
 */
import {
  fetchEmployees,
  fetchDepartments,
  fetchWarehouses,
  fetchCustomers,
  fetchAccountCodes,
  extractRecords,
} from "./erpApiClient";
import { transaction, query, queryOne } from "../database/db";
import { logger } from "../utils/logger";

async function writeSyncLog(
  resource: string,
  coCd: string,
  companyCode: string,
  result: SyncResult,
  executedBy?: string
): Promise<void> {
  try {
    await query(
      `INSERT INTO erp_sync_log (
         resource, co_cd, company_code,
         fetched_count, inserted_count, updated_count, skipped_count,
         duration_ms, success, error_message, executed_by
       ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)`,
      [
        resource,
        coCd,
        companyCode,
        result.fetched,
        result.inserted,
        result.updated,
        result.skipped,
        result.durationMs,
        result.errors.length === 0,
        result.errors.join("\n") || null,
        executedBy || "system",
      ]
    );
  } catch (e: any) {
    // 로그 테이블이 마이그레이션 전이면 INSERT 실패할 수 있음 — 동기화 결과 자체에 영향 없도록 무시
    logger.warn("[erpSync] erp_sync_log INSERT 실패 (무시)", { error: e?.message });
  }
}

async function withSyncLog(
  resource: string,
  coCd: string,
  companyCode: string,
  executedBy: string | undefined,
  fn: () => Promise<SyncResult>
): Promise<SyncResult> {
  const r = await fn();
  await writeSyncLog(resource, coCd, companyCode, r, executedBy);
  return r;
}

export interface SyncResult {
  resource: string;
  fetched: number;
  inserted: number;
  updated: number;
  skipped: number;
  errors: string[];
  durationMs: number;
}

function pick<T extends Record<string, any>>(obj: T, keys: string[]): any {
  for (const k of keys) {
    const v = obj?.[k];
    if (v !== undefined && v !== null && String(v) !== "") return v;
  }
  return null;
}

function asString(v: any): string | null {
  if (v === undefined || v === null) return null;
  const s = String(v).trim();
  return s === "" ? null : s;
}

function asDate(v: any): string | null {
  const s = asString(v);
  if (!s) return null;
  // "20231201" 형태 → "2023-12-01"
  const ymd = /^(\d{4})(\d{2})(\d{2})$/.exec(s);
  if (ymd) return `${ymd[1]}-${ymd[2]}-${ymd[3]}`;
  return s;
}

/**
 * 사원(user_info) 동기화
 *  - 외부 응답 필드(empSeq, empNm, deptCd, deptNm, posCd, posNm, emalAdd, tel, sabun ...) → 내부 컬럼 매핑
 */
export async function syncEmployees(
  coCd: string,
  companyCode: string,
  executedBy?: string
): Promise<SyncResult> {
  return withSyncLog("employees", coCd, companyCode, executedBy, () => doSyncEmployees(coCd, companyCode));
}

async function doSyncEmployees(coCd: string, companyCode: string): Promise<SyncResult> {
  const start = Date.now();
  const result: SyncResult = {
    resource: "employees",
    fetched: 0,
    inserted: 0,
    updated: 0,
    skipped: 0,
    errors: [],
    durationMs: 0,
  };

  try {
    const apiResponse = await fetchEmployees(coCd);
    const records = extractRecords(apiResponse);
    result.fetched = records.length;

    if (records.length === 0) {
      logger.warn("[erpSync] employees 응답 데이터 없음", { coCd, sample: JSON.stringify(apiResponse).slice(0, 300) });
      result.durationMs = Date.now() - start;
      return result;
    }

    await transaction(async (client) => {
      for (const r of records) {
        const userId = asString(pick(r, ["empSeq", "userId", "user_id", "loginId"]));
        const userName = asString(pick(r, ["empNm", "userName", "user_name", "name"]));
        if (!userId || !userName) {
          result.skipped++;
          continue;
        }

        const deptCode = asString(pick(r, ["deptCd", "deptCode", "dept_code"]));
        const deptName = asString(pick(r, ["deptNm", "deptName", "dept_name"]));
        const positionCode = asString(pick(r, ["posCd", "positionCode", "position_code", "rspofcCd"]));
        const positionName = asString(pick(r, ["posNm", "positionName", "position_name", "rspofcNm"]));
        const email = asString(pick(r, ["emalAdd", "outemalAdd", "email"]));
        const tel = asString(pick(r, ["tel", "phone", "telNo"]));
        const cellPhone = asString(pick(r, ["emgcTel", "cellPhone", "cell_phone", "hpNo", "mobile"]));
        const sabun = asString(pick(r, ["sabun", "empNo", "emp_no"]));

        const existing = await client.query(
          "SELECT user_id FROM user_info WHERE user_id = $1",
          [userId]
        );

        if (existing.rowCount && existing.rowCount > 0) {
          await client.query(
            `UPDATE user_info SET
              user_name = COALESCE($2, user_name),
              dept_code = COALESCE($3, dept_code),
              dept_name = COALESCE($4, dept_name),
              position_code = COALESCE($5, position_code),
              position_name = COALESCE($6, position_name),
              email = COALESCE($7, email),
              tel = COALESCE($8, tel),
              cell_phone = COALESCE($9, cell_phone),
              sabun = COALESCE($10, sabun),
              company_code = COALESCE($11, company_code)
             WHERE user_id = $1`,
            [userId, userName, deptCode, deptName, positionCode, positionName, email, tel, cellPhone, sabun, companyCode]
          );
          result.updated++;
        } else {
          await client.query(
            `INSERT INTO user_info (
               user_id, user_name, user_password,
               dept_code, dept_name, position_code, position_name,
               email, tel, cell_phone, sabun,
               company_code, status, regdate
             ) VALUES ($1, $2, '', $3, $4, $5, $6, $7, $8, $9, $10, $11, 'active', NOW())`,
            [userId, userName, deptCode, deptName, positionCode, positionName, email, tel, cellPhone, sabun, companyCode]
          );
          result.inserted++;
        }
      }
    });
  } catch (err: any) {
    logger.error("[erpSync] employees 실패", { error: err?.message });
    result.errors.push(err?.message || String(err));
  }

  result.durationMs = Date.now() - start;
  return result;
}

/**
 * 부서(dept_info) 동기화
 */
export async function syncDepartments(
  coCd: string,
  companyCode: string,
  searchText?: string,
  executedBy?: string
): Promise<SyncResult> {
  return withSyncLog("departments", coCd, companyCode, executedBy, () =>
    doSyncDepartments(coCd, companyCode, searchText)
  );
}

async function doSyncDepartments(
  coCd: string,
  companyCode: string,
  searchText?: string
): Promise<SyncResult> {
  const start = Date.now();
  const result: SyncResult = {
    resource: "departments",
    fetched: 0,
    inserted: 0,
    updated: 0,
    skipped: 0,
    errors: [],
    durationMs: 0,
  };

  try {
    const apiResponse = await fetchDepartments(coCd, searchText);
    const records = extractRecords(apiResponse);
    result.fetched = records.length;

    const company = await queryOne<any>(
      "SELECT company_name FROM company_mng WHERE company_code = $1",
      [companyCode]
    );
    const companyName = company?.company_name || companyCode;

    await transaction(async (client) => {
      for (const r of records) {
        const deptCode = asString(pick(r, ["deptCd", "deptCode", "dept_code"]));
        const deptName = asString(pick(r, ["deptNm", "deptName", "dept_name"]));
        if (!deptCode || !deptName) {
          result.skipped++;
          continue;
        }
        const parentDeptCode = asString(pick(r, ["upDeptCd", "parentDeptCode", "parent_dept_code"]));

        const existing = await client.query(
          "SELECT dept_code FROM dept_info WHERE dept_code = $1 AND company_code = $2",
          [deptCode, companyCode]
        );

        if (existing.rowCount && existing.rowCount > 0) {
          await client.query(
            `UPDATE dept_info SET
              dept_name = $1,
              parent_dept_code = $2,
              company_name = $3
             WHERE dept_code = $4 AND company_code = $5`,
            [deptName, parentDeptCode, companyName, deptCode, companyCode]
          );
          result.updated++;
        } else {
          await client.query(
            `INSERT INTO dept_info (
               dept_code, dept_name, company_code, company_name,
               parent_dept_code, status, regdate
             ) VALUES ($1, $2, $3, $4, $5, 'active', NOW())`,
            [deptCode, deptName, companyCode, companyName, parentDeptCode]
          );
          result.inserted++;
        }
      }
    });
  } catch (err: any) {
    logger.error("[erpSync] departments 실패", { error: err?.message });
    result.errors.push(err?.message || String(err));
  }

  result.durationMs = Date.now() - start;
  return result;
}

/**
 * 창고(warehouse_info) 동기화
 */
export async function syncWarehouses(
  coCd: string,
  companyCode: string,
  baselocFg?: string,
  executedBy?: string
): Promise<SyncResult> {
  return withSyncLog("warehouses", coCd, companyCode, executedBy, () =>
    doSyncWarehouses(coCd, companyCode, baselocFg)
  );
}

async function doSyncWarehouses(
  coCd: string,
  companyCode: string,
  baselocFg?: string
): Promise<SyncResult> {
  const start = Date.now();
  const result: SyncResult = {
    resource: "warehouses",
    fetched: 0,
    inserted: 0,
    updated: 0,
    skipped: 0,
    errors: [],
    durationMs: 0,
  };

  try {
    const apiResponse = await fetchWarehouses(coCd, baselocFg);
    const records = extractRecords(apiResponse);
    result.fetched = records.length;

    await transaction(async (client) => {
      for (const r of records) {
        const warehouseCode = asString(pick(r, ["lctnCd", "warehouseCode", "warehouse_code", "whCd"]));
        const warehouseName = asString(pick(r, ["lctnNm", "warehouseName", "warehouse_name", "whNm"]));
        if (!warehouseCode || !warehouseName) {
          result.skipped++;
          continue;
        }
        const warehouseType = asString(pick(r, ["lctnFg", "warehouseType", "warehouse_type", "whFg"]));

        const existing = await client.query(
          "SELECT warehouse_code FROM warehouse_info WHERE warehouse_code = $1 AND company_code = $2",
          [warehouseCode, companyCode]
        );

        if (existing.rowCount && existing.rowCount > 0) {
          await client.query(
            `UPDATE warehouse_info SET
              warehouse_name = $1,
              warehouse_type = COALESCE($2, warehouse_type)
             WHERE warehouse_code = $3 AND company_code = $4`,
            [warehouseName, warehouseType, warehouseCode, companyCode]
          );
          result.updated++;
        } else {
          await client.query(
            `INSERT INTO warehouse_info (
               warehouse_code, warehouse_name, warehouse_type,
               company_code, status, created_at
             ) VALUES ($1, $2, $3, $4, 'active', NOW())`,
            [warehouseCode, warehouseName, warehouseType, companyCode]
          );
          result.inserted++;
        }
      }
    });
  } catch (err: any) {
    logger.error("[erpSync] warehouses 실패", { error: err?.message });
    result.errors.push(err?.message || String(err));
  }

  result.durationMs = Date.now() - start;
  return result;
}

/**
 * 거래처(customer_mng) 동기화
 */
export async function syncCustomers(
  coCd: string,
  companyCode: string,
  executedBy?: string
): Promise<SyncResult> {
  return withSyncLog("customers", coCd, companyCode, executedBy, () => doSyncCustomers(coCd, companyCode));
}

async function doSyncCustomers(coCd: string, companyCode: string): Promise<SyncResult> {
  const start = Date.now();
  const result: SyncResult = {
    resource: "customers",
    fetched: 0,
    inserted: 0,
    updated: 0,
    skipped: 0,
    errors: [],
    durationMs: 0,
  };

  try {
    const apiResponse = await fetchCustomers(coCd);
    const records = extractRecords(apiResponse);
    result.fetched = records.length;

    await transaction(async (client) => {
      for (const r of records) {
        const customerCode = asString(pick(r, ["trCd", "customerCode", "customer_code", "cstmrCd"]));
        const customerName = asString(pick(r, ["trNm", "customerName", "customer_name", "cstmrNm"]));
        if (!customerCode || !customerName) {
          result.skipped++;
          continue;
        }
        const bizNo = asString(pick(r, ["bizNo", "businessNumber", "business_number", "bisnsRgstNo"]));
        const ceoName = asString(pick(r, ["repNm", "ceoName", "ceo_name", "rprsvNm"]));
        const tel = asString(pick(r, ["tel", "phone"]));
        const address = asString(pick(r, ["addr", "address"]));
        const customerType = asString(pick(r, ["trFg", "customerType", "customer_type"]));

        const existing = await client.query(
          "SELECT customer_code FROM customer_mng WHERE customer_code = $1 AND company_code = $2",
          [customerCode, companyCode]
        );

        if (existing.rowCount && existing.rowCount > 0) {
          await client.query(
            `UPDATE customer_mng SET
              customer_name = $1,
              business_number = COALESCE($2, business_number),
              ceo_name = COALESCE($3, ceo_name),
              tel = COALESCE($4, tel),
              address = COALESCE($5, address),
              customer_type = COALESCE($6, customer_type)
             WHERE customer_code = $7 AND company_code = $8`,
            [customerName, bizNo, ceoName, tel, address, customerType, customerCode, companyCode]
          );
          result.updated++;
        } else {
          await client.query(
            `INSERT INTO customer_mng (
               customer_code, customer_name, business_number, ceo_name,
               tel, address, customer_type, company_code, status, created_at
             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'active', NOW())`,
            [customerCode, customerName, bizNo, ceoName, tel, address, customerType, companyCode]
          );
          result.inserted++;
        }
      }
    });
  } catch (err: any) {
    logger.error("[erpSync] customers 실패", { error: err?.message });
    result.errors.push(err?.message || String(err));
  }

  result.durationMs = Date.now() - start;
  return result;
}

/**
 * 계정과목(account_code_info) 동기화
 */
export async function syncAccountCodes(
  coCd: string,
  companyCode: string,
  executedBy?: string
): Promise<SyncResult> {
  return withSyncLog("account_codes", coCd, companyCode, executedBy, () =>
    doSyncAccountCodes(coCd, companyCode)
  );
}

async function doSyncAccountCodes(coCd: string, companyCode: string): Promise<SyncResult> {
  const start = Date.now();
  const result: SyncResult = {
    resource: "accountCodes",
    fetched: 0,
    inserted: 0,
    updated: 0,
    skipped: 0,
    errors: [],
    durationMs: 0,
  };

  try {
    const apiResponse = await fetchAccountCodes(coCd);
    const records = extractRecords(apiResponse);
    result.fetched = records.length;

    await transaction(async (client) => {
      for (const r of records) {
        const acctCd = asString(pick(r, ["acctCd", "accountCode", "account_code"]));
        const acctNm = asString(pick(r, ["acctNm", "accountName", "account_name"]));
        if (!acctCd || !acctNm) {
          result.skipped++;
          continue;
        }
        const acctTy = asString(pick(r, ["acctTy", "accountType", "account_type"]));
        const useYn = asString(pick(r, ["useYn", "use_yn"])) || "Y";

        const existing = await client.query(
          "SELECT account_code FROM account_code_info WHERE account_code = $1 AND company_code = $2",
          [acctCd, companyCode]
        );

        if (existing.rowCount && existing.rowCount > 0) {
          await client.query(
            `UPDATE account_code_info SET
              account_name = $1,
              account_type = COALESCE($2, account_type),
              use_yn = $3
             WHERE account_code = $4 AND company_code = $5`,
            [acctNm, acctTy, useYn, acctCd, companyCode]
          );
          result.updated++;
        } else {
          await client.query(
            `INSERT INTO account_code_info (
               account_code, account_name, account_type, use_yn,
               company_code, created_at
             ) VALUES ($1, $2, $3, $4, $5, NOW())`,
            [acctCd, acctNm, acctTy, useYn, companyCode]
          );
          result.inserted++;
        }
      }
    });
  } catch (err: any) {
    logger.error("[erpSync] accountCodes 실패", { error: err?.message });
    result.errors.push(err?.message || String(err));
  }

  result.durationMs = Date.now() - start;
  return result;
}

/**
 * 전체 마스터 일괄 동기화 (사원/부서/창고/거래처/계정과목)
 *  - 부서를 가장 먼저 동기화 (사원의 dept_code FK 가정).
 */
export async function syncAll(
  coCd: string,
  companyCode: string,
  executedBy?: string
): Promise<SyncResult[]> {
  return [
    await syncDepartments(coCd, companyCode, undefined, executedBy),
    await syncEmployees(coCd, companyCode, executedBy),
    await syncWarehouses(coCd, companyCode, undefined, executedBy),
    await syncCustomers(coCd, companyCode, executedBy),
    await syncAccountCodes(coCd, companyCode, executedBy),
  ];
}
