/**
 * 제일그라스(COMPANY_9) 수주 엑셀 일괄 업로드 서비스
 * - 품목 자동 등록 (item_info에 없는 품명/규격 조합은 신규 생성)
 * - 마스터 UPSERT (sales_order_mng)
 * - 디테일 INSERT (sales_order_detail)
 * - 트랜잭션 보장
 */

import { getPool } from "../database/db";
import { numberingRuleService } from "./numberingRuleService";
import logger from "../utils/logger";

// 업로드 요청 페이로드 — 프론트 매핑 결과
export interface BulkRow {
  // 마스터 후보 필드
  order_no?: string;
  partner_code?: string;
  partner_name?: string;
  order_date?: string;
  due_date?: string;
  status?: string;

  // 디테일 필드
  part_code?: string;
  part_name?: string;
  spec?: string;
  width?: number | string;
  height?: number | string;
  thickness?: number | string;
  area?: number | string;
  unit?: string;
  qty?: number | string;
  unit_price?: number | string;
  amount?: number | string;
  memo?: string;

  // 품목 자동등록 옵션 필드
  division?: string;
}

export interface BulkUploadPayload {
  companyCode: string;
  userId: string;
  rows: BulkRow[];
  autoCreateItems?: boolean;
  defaultItemDivision?: string; // e.g. 'CAT_DIV_RAW_MAT'
}

export interface BulkUploadResult {
  itemsCreated: number;
  mastersCreated: number;
  detailsCreated: number;
  warnings: string[];
  errors: string[];
}

const ITEM_NUMBER_PREFIX = "R"; // 자동 생성 품번 접두어

function toNum(v: any): number {
  if (v === null || v === undefined || v === "") return 0;
  const n = Number(String(v).replace(/,/g, ""));
  return isNaN(n) ? 0 : n;
}

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

/**
 * 엑셀 한 줄 → item_info에서 기존 품목 조회, 없으면 INSERT
 * 반환: { itemNumber: string, created: boolean }
 */
async function resolveOrCreateItem(
  client: any,
  companyCode: string,
  row: BulkRow,
  defaultDivision: string,
  userId: string
): Promise<{ itemNumber: string; created: boolean } | null> {
  const partName = normStr(row.part_name);
  const partCode = normStr(row.part_code);
  if (!partName && !partCode) return null;

  // 1) part_code 직접 매칭 우선
  if (partCode) {
    const r = await client.query(
      `SELECT item_number FROM item_info
        WHERE company_code = $1 AND item_number = $2
        LIMIT 1`,
      [companyCode, partCode]
    );
    if (r.rows.length > 0) return { itemNumber: r.rows[0].item_number, created: false };
  }

  // 2) part_name + 규격 매칭
  if (partName) {
    const w = toNum(row.width);
    const h = toNum(row.height);
    const t = toNum(row.thickness);
    const r2 = await client.query(
      `SELECT item_number FROM item_info
        WHERE company_code = $1
          AND item_name = $2
          AND COALESCE(width::numeric,0)     = $3
          AND COALESCE(height::numeric,0)    = $4
          AND COALESCE(thickness::numeric,0) = $5
        LIMIT 1`,
      [companyCode, partName, w, h, t]
    );
    if (r2.rows.length > 0) return { itemNumber: r2.rows[0].item_number, created: false };
  }

  // 3) 자동 생성
  if (!partName) return null; // 품명 없으면 생성 불가

  // 간단 채번: R_YYYYMMDD_NNNN (company 내 해당 prefix 최대값+1)
  const today = new Date().toISOString().slice(0, 10).replace(/-/g, "");
  const prefix = `${ITEM_NUMBER_PREFIX}_${today}_`;
  const seqR = await client.query(
    `SELECT COUNT(*)::int AS c FROM item_info
      WHERE company_code = $1 AND item_number LIKE $2`,
    [companyCode, `${prefix}%`]
  );
  const seq = (Number(seqR.rows[0]?.c) || 0) + 1;
  const itemNumber = `${prefix}${String(seq).padStart(4, "0")}`;

  const division = normStr(row.division) || defaultDivision;
  const unit = normStr(row.unit) || "EA";

  await client.query(
    `INSERT INTO item_info (
        id, company_code, item_number, item_name,
        size, width, height, thickness,
        unit, division, status, writer, created_date
     ) VALUES (
        gen_random_uuid()::text, $1, $2, $3,
        $4, $5, $6, $7,
        $8, $9, 'active', $10, NOW()
     )`,
    [
      companyCode,
      itemNumber,
      partName,
      normStr(row.spec),
      toNum(row.width) || null,
      toNum(row.height) || null,
      toNum(row.thickness) || null,
      unit,
      division,
      userId,
    ]
  );

  return { itemNumber, created: true };
}

/**
 * 수주번호 채번 — numbering_rules에 설정 있으면 사용, 없으면 ORD-YYYYMMDD-NNNN 폴백
 */
async function allocateOrderNo(companyCode: string, client: any): Promise<string> {
  // 기존 규칙 조회
  const ruleRes = await client.query(
    `SELECT rule_id FROM numbering_rules
      WHERE company_code = $1 AND table_name = 'sales_order_mng' AND column_name = 'order_no'
      LIMIT 1`,
    [companyCode]
  );
  if (ruleRes.rows.length > 0) {
    try {
      const code = await numberingRuleService.allocateCode(
        ruleRes.rows[0].rule_id,
        companyCode
      );
      if (code) return code;
    } catch (e: any) {
      logger.warn(`allocateCode 실패 → 폴백 사용: ${e?.message}`);
    }
  }

  // 폴백
  const today = new Date().toISOString().slice(0, 10).replace(/-/g, "");
  const prefix = `ORD-${today}-`;
  const r = await client.query(
    `SELECT COUNT(*)::int AS c FROM sales_order_mng
      WHERE company_code = $1 AND order_no LIKE $2`,
    [companyCode, `${prefix}%`]
  );
  const seq = (Number(r.rows[0]?.c) || 0) + 1;
  return `${prefix}${String(seq).padStart(4, "0")}`;
}

export async function excelBulkUpload(
  payload: BulkUploadPayload
): Promise<BulkUploadResult> {
  const pool = getPool();
  const client = await pool.connect();
  const result: BulkUploadResult = {
    itemsCreated: 0,
    mastersCreated: 0,
    detailsCreated: 0,
    warnings: [],
    errors: [],
  };

  const autoCreate = payload.autoCreateItems !== false; // 기본 true
  const defaultDivision = payload.defaultItemDivision || "CAT_DIV_RAW_MAT";

  try {
    await client.query("BEGIN");

    // 1) 각 행의 품목 확정 (part_code를 확정값으로 채움)
    const resolved: Array<{ row: BulkRow; partCode: string }> = [];
    for (let i = 0; i < payload.rows.length; i++) {
      const row = payload.rows[i];
      if (!autoCreate && !normStr(row.part_code)) {
        // 자동생성 비활성 + part_code 비어있음: 매칭만 시도
        const pr = await resolveOrCreateItem(
          client, payload.companyCode, row, defaultDivision, payload.userId
        );
        if (!pr) {
          result.warnings.push(`행 ${i + 1}: 품목 매칭 실패 (품명=${row.part_name})`);
          continue;
        }
        resolved.push({ row, partCode: pr.itemNumber });
      } else {
        const pr = await resolveOrCreateItem(
          client, payload.companyCode, row, defaultDivision, payload.userId
        );
        if (!pr) {
          result.warnings.push(`행 ${i + 1}: 품목 정보 부족 (품명/품번 모두 없음)`);
          continue;
        }
        if (pr.created) result.itemsCreated++;
        resolved.push({ row, partCode: pr.itemNumber });
      }
    }

    if (resolved.length === 0) {
      await client.query("ROLLBACK");
      result.errors.push("유효한 행이 없습니다.");
      return result;
    }

    // 2) order_no 기준 그룹핑
    // - 엑셀에 order_no 있는 행은 동일 번호끼리 묶음
    // - 비어있는 행들은 하나의 그룹 "(auto)" 으로 묶어 자동 채번 1건
    const groups = new Map<string, Array<{ row: BulkRow; partCode: string }>>();
    for (const item of resolved) {
      const key = normStr(item.row.order_no) || "__AUTO__";
      if (!groups.has(key)) groups.set(key, []);
      groups.get(key)!.push(item);
    }

    // 3) 그룹별 마스터 UPSERT + 디테일 INSERT
    let autoOrderNo: string | null = null;

    for (const [key, items] of Array.from(groups.entries())) {
      let orderNo = key;
      if (key === "__AUTO__") {
        if (!autoOrderNo) autoOrderNo = await allocateOrderNo(payload.companyCode, client);
        orderNo = autoOrderNo;
      }

      // 마스터 존재 확인
      const mRes = await client.query(
        `SELECT id FROM sales_order_mng
          WHERE company_code = $1 AND order_no = $2 LIMIT 1`,
        [payload.companyCode, orderNo]
      );

      // 대표값 (첫 행 기준)
      const first = items[0].row;

      // partner_id 조회 (name으로) — 없어도 무방
      let partnerId = normStr(first.partner_code);
      if (!partnerId && normStr(first.partner_name)) {
        const pRes = await client.query(
          `SELECT id FROM customer_mng
            WHERE company_code = $1 AND customer_name = $2 LIMIT 1`,
          [payload.companyCode, normStr(first.partner_name)]
        );
        if (pRes.rows.length > 0) partnerId = pRes.rows[0].id;
      }

      if (mRes.rows.length === 0) {
        // INSERT
        await client.query(
          `INSERT INTO sales_order_mng (
              company_code, order_no, order_date, due_date, partner_id,
              status, manager_id, created_date, created_by
           ) VALUES (
              $1, $2, $3, $4, $5,
              $6, $7, NOW(), $8
           )`,
          [
            payload.companyCode,
            orderNo,
            normStr(first.order_date) || new Date().toISOString().slice(0, 10),
            normStr(first.due_date) || null,
            partnerId || null,
            normStr(first.status) || "수주",
            payload.userId,
            payload.userId,
          ]
        );
        result.mastersCreated++;
      }

      // 디테일 INSERT
      // 기존 디테일의 최대 seq_no 조회
      const sRes = await client.query(
        `SELECT COALESCE(MAX(NULLIF(seq_no,'')::int), 0)::int AS max_seq
           FROM sales_order_detail
          WHERE company_code = $1 AND order_no = $2`,
        [payload.companyCode, orderNo]
      );
      let seqStart = (Number(sRes.rows[0]?.max_seq) || 0) + 1;

      for (const it of items) {
        const r = it.row;
        const w = toNum(r.width);
        const h = toNum(r.height);
        const qty = toNum(r.qty);
        const price = toNum(r.unit_price);
        const area = normStr(r.area) || (w > 0 && h > 0 ? (w * h / 91808).toFixed(4) : "");
        const amount = normStr(r.amount) || (qty && price ? String(qty * price) : "");

        await client.query(
          `INSERT INTO sales_order_detail (
              id, company_code, order_no, seq_no,
              part_code, part_name, spec,
              width, height, thickness, area,
              unit, qty, unit_price, amount,
              delivery_partner_code, due_date, memo,
              writer, created_date
           ) VALUES (
              gen_random_uuid()::text, $1, $2, $3,
              $4, $5, $6,
              $7, $8, $9, $10,
              $11, $12, $13, $14,
              $15, $16, $17,
              $18, NOW()
           )`,
          [
            payload.companyCode,
            orderNo,
            String(seqStart++),
            it.partCode,
            normStr(r.part_name),
            normStr(r.spec),
            w || null, h || null, toNum(r.thickness) || null, area || null,
            normStr(r.unit) || null,
            qty || null,
            price || null,
            amount || null,
            normStr(r.partner_code) || null,
            normStr(r.due_date) || null,
            normStr(r.memo) || null,
            payload.userId,
          ]
        );
        result.detailsCreated++;
      }
    }

    await client.query("COMMIT");
    return result;
  } catch (err: any) {
    await client.query("ROLLBACK");
    logger.error(`excelBulkUpload 실패: ${err?.message}`, err);
    result.errors.push(err?.message || "알 수 없는 오류");
    return result;
  } finally {
    client.release();
  }
}
