/**
 * Customer CS(고객 사후관리) 서비스
 *  - wace_plm 의 CustomerMngController + customerMng.xml 을 Node 로 포팅
 *  - mng_number 자동 채번: YYYY-NNN (해당 년도 max 번호 + 1)
 */
import { query, queryOne, transaction } from "../database/db";
import { CS_CODE_CATEGORIES } from "./customerCsTableMigration";

export interface CsListParams {
  searchYear?: string;
  mngType?: string;
  productDivision?: string;
  customerName?: string;
  startReceptionDate?: string;
  endReceptionDate?: string;
  eventLocation?: string;
  performer?: string;
  startActionDate?: string;
  endActionDate?: string;
  measureType?: string;
  status?: string;
  page?: number;
  pageSize?: number;
}

export interface CsUpsertParams {
  objId?: string | number;
  mng_type?: string;
  product_division?: string;
  project_objid?: string | number | null;
  reception_date?: string | null;
  event_location?: string;
  action_date?: string | null;
  customer_name?: string;
  title?: string;
  performer?: string;
  analysis?: string;
  measure?: string;
  measure_type?: string;
  measure_amount?: string;
  status?: string;
  writer: string;
}

const CAT = CS_CODE_CATEGORIES;

/** 메인 SELECT (목록/상세 공용) — 코드 카테고리 별로 LATERAL 조인하여 라벨 부여 */
const SELECT_BASE = `
  SELECT
      T.objid::text                                    AS objid,
      T.mng_number,
      T.mng_type,
      (SELECT code_name FROM comm_code WHERE code_id = T.mng_type AND parent_code_id = '${CAT.MNG_TYPE}')         AS mng_type_title,
      T.product_division,
      (SELECT code_name FROM comm_code WHERE code_id = T.product_division AND parent_code_id = '${CAT.PRODUCT_DIVISION}') AS product_division_title,
      T.project_objid::text AS project_objid,
      T.reception_date,
      TO_CHAR(T.reception_date, 'YYYY-MM-DD')          AS reception_date_title,
      T.event_location,
      T.action_date,
      TO_CHAR(T.action_date, 'YYYY-MM-DD')             AS action_date_title,
      T.customer_name,
      T.title,
      T.performer,
      (SELECT code_name FROM comm_code WHERE code_id = T.performer AND parent_code_id = '${CAT.PERFORMER}')      AS performer_title,
      T.writer,
      (SELECT dept_name || ' ' || user_name FROM user_info WHERE user_id = T.writer) AS writer_title,
      T.analysis,
      T.measure,
      T.measure_type,
      (SELECT code_name FROM comm_code WHERE code_id = T.measure_type AND parent_code_id = '${CAT.MEASURE_TYPE}') AS measure_type_title,
      T.measure_amount,
      T.status,
      (SELECT code_name FROM comm_code WHERE code_id = T.status AND parent_code_id = '${CAT.STATUS}')             AS status_title,
      TO_CHAR(T.regdate,  'YYYY-MM-DD')                AS regdate_title,
      TO_CHAR(T.regdate,  'YYYY')                      AS regdate_year_title,
      TO_CHAR(T.editdate, 'YYYY-MM-DD')                AS editdate_title
  FROM customer_cs_mng T
`;

export class CustomerCsService {
  static async list(p: CsListParams) {
    const page = Math.max(1, Number(p.page) || 1);
    const pageSize = Math.max(1, Math.min(200, Number(p.pageSize) || 20));
    const offset = (page - 1) * pageSize;

    const where: string[] = ["1=1"];
    const args: any[] = [];
    let i = 1;

    if (p.searchYear)        { where.push(`TO_CHAR(T.regdate, 'YYYY') = $${i++}`); args.push(p.searchYear); }
    if (p.mngType)           { where.push(`T.mng_type = $${i++}`); args.push(p.mngType); }
    if (p.productDivision)   { where.push(`T.product_division = $${i++}`); args.push(p.productDivision); }
    if (p.customerName)      { where.push(`T.customer_name ILIKE '%' || $${i++} || '%'`); args.push(p.customerName); }
    if (p.startReceptionDate){ where.push(`T.reception_date >= $${i++}::timestamp`); args.push(p.startReceptionDate); }
    if (p.endReceptionDate)  { where.push(`T.reception_date <= $${i++}::timestamp`); args.push(p.endReceptionDate + " 23:59:59"); }
    if (p.eventLocation)     { where.push(`T.event_location ILIKE '%' || $${i++} || '%'`); args.push(p.eventLocation); }
    if (p.performer)         { where.push(`T.performer = $${i++}`); args.push(p.performer); }
    if (p.startActionDate)   { where.push(`T.action_date >= $${i++}::timestamp`); args.push(p.startActionDate); }
    if (p.endActionDate)     { where.push(`T.action_date <= $${i++}::timestamp`); args.push(p.endActionDate + " 23:59:59"); }
    if (p.measureType)       { where.push(`T.measure_type = $${i++}`); args.push(p.measureType); }
    if (p.status)            { where.push(`T.status = $${i++}`); args.push(p.status); }
    const whereSql = where.join(" AND ");

    const cnt = await queryOne<{ total: string; sum_measure: string }>(
      `SELECT
         COUNT(1)::int AS total,
         COALESCE(SUM(
           CASE
             WHEN T.measure_amount ~ '^[0-9]+$' THEN T.measure_amount::numeric
             ELSE 0
           END
         ), 0)::int AS sum_measure
       FROM customer_cs_mng T WHERE ${whereSql}`,
      args,
    );

    const rows = await query<any>(
      `${SELECT_BASE} WHERE ${whereSql} ORDER BY T.regdate DESC, T.objid DESC LIMIT $${i++} OFFSET $${i++}`,
      [...args, pageSize, offset],
    );

    return {
      list: rows,
      pagination: {
        page, pageSize,
        total: Number(cnt?.total || 0),
        totalPages: Math.ceil(Number(cnt?.total || 0) / pageSize),
      },
      summary: {
        sumMeasureAmount: Number(cnt?.sum_measure || 0),
      },
    };
  }

  static async detail(objId: string | number) {
    return await queryOne<any>(`${SELECT_BASE} WHERE T.objid = $1::bigint`, [objId]);
  }

  /**
   * 등록/수정 (mergeCustomerMng)
   *  - 신규 시 mng_number 자동 채번: YYYY-NNN
   *    (원본: to_char(now(),'YYYY')||'-'||LPAD((max(MNG_NUMBER) substring(6,3)+1)::text, 3, '0'))
   */
  static async merge(p: CsUpsertParams) {
    const objId = p.objId ? String(p.objId) : `${Date.now()}`;
    return await transaction(async (client) => {
      const r = await client.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)
        VALUES
          ($1::bigint,
           TO_CHAR(NOW(), 'YYYY') || '-' || LPAD(
             ((SELECT COALESCE(MAX(SUBSTRING(mng_number, 6, 3)::numeric), 0)
                 FROM customer_cs_mng
                WHERE TO_CHAR(regdate, 'YYYY') = TO_CHAR(NOW(), 'YYYY')) + 1)::text,
             3, '0'),
           $2, $3, NULLIF($4,'')::bigint,
           NULLIF($5,'')::timestamp, $6, NULLIF($7,'')::timestamp,
           $8, $9, $10, $11, $12, $13,
           $14, $15, $16, NOW())
        ON CONFLICT (objid) DO UPDATE SET
            mng_type         = EXCLUDED.mng_type,
            product_division = EXCLUDED.product_division,
            project_objid    = EXCLUDED.project_objid,
            reception_date   = EXCLUDED.reception_date,
            event_location   = EXCLUDED.event_location,
            action_date      = EXCLUDED.action_date,
            customer_name    = EXCLUDED.customer_name,
            title            = EXCLUDED.title,
            performer        = EXCLUDED.performer,
            analysis         = EXCLUDED.analysis,
            measure          = EXCLUDED.measure,
            measure_type     = EXCLUDED.measure_type,
            measure_amount   = EXCLUDED.measure_amount,
            status           = EXCLUDED.status,
            editdate         = NOW()
        RETURNING objid::text AS objid, mng_number, status
        `,
        [
          objId,
          p.mng_type || "",
          p.product_division || "",
          p.project_objid ? String(p.project_objid) : "",
          p.reception_date || "",
          p.event_location || "",
          p.action_date || "",
          p.customer_name || "",
          p.title || "",
          p.performer || "",
          p.writer,
          p.analysis || "",
          p.measure || "",
          p.measure_type || "",
          p.measure_amount || "",
          p.status || "",
        ],
      );
      return r.rows[0];
    });
  }

  static async deleteByIds(ids: Array<string | number>) {
    if (!ids?.length) return { deleted: 0 };
    const r = await query<any>(
      `DELETE FROM customer_cs_mng WHERE objid = ANY($1::bigint[]) RETURNING objid`,
      [ids.map(String)],
    );
    return { deleted: r.length };
  }

  /**
   * 대시보드 집계 — 원본 customerMngDashBoard 의 요약 카드용
   *  - 총건수, 유상/무상 합계, 상태별 카운트, 관리유형별 카운트, 제품구분별 카운트
   */
  static async dashboard(searchYear?: string) {
    const args: any[] = [];
    let yearFilter = "";
    if (searchYear) {
      yearFilter = `AND TO_CHAR(T.regdate, 'YYYY') = $1`;
      args.push(searchYear);
    }

    // 핵심 요약: 총건수/유상합/무상합
    const summary = await queryOne<any>(
      `
      SELECT
        COUNT(1)::int AS total_count,
        COALESCE(SUM(CASE WHEN T.measure_amount ~ '^[0-9]+$' THEN T.measure_amount::numeric ELSE 0 END), 0)::int AS sum_amount,
        COALESCE(SUM(CASE
          WHEN T.measure_type = '0203001' AND T.measure_amount ~ '^[0-9]+$' THEN T.measure_amount::numeric
          ELSE 0 END), 0)::int AS sum_paid_amount,
        COALESCE(SUM(CASE
          WHEN T.measure_type = '0203002' AND T.measure_amount ~ '^[0-9]+$' THEN T.measure_amount::numeric
          ELSE 0 END), 0)::int AS sum_free_amount
      FROM customer_cs_mng T WHERE 1=1 ${yearFilter}
      `,
      args,
    );

    // 상태별
    const byStatus = await query<any>(
      `
      SELECT
        T.status AS code_id,
        (SELECT code_name FROM comm_code WHERE code_id = T.status AND parent_code_id = '${CAT.STATUS}') AS code_name,
        COUNT(1)::int AS cnt
      FROM customer_cs_mng T
      WHERE 1=1 ${yearFilter}
      GROUP BY T.status
      ORDER BY cnt DESC
      `,
      args,
    );

    // 관리유형별
    const byMngType = await query<any>(
      `
      SELECT
        T.mng_type AS code_id,
        (SELECT code_name FROM comm_code WHERE code_id = T.mng_type AND parent_code_id = '${CAT.MNG_TYPE}') AS code_name,
        COUNT(1)::int AS cnt
      FROM customer_cs_mng T
      WHERE 1=1 ${yearFilter}
      GROUP BY T.mng_type
      ORDER BY cnt DESC
      `,
      args,
    );

    // 제품구분별
    const byProductDivision = await query<any>(
      `
      SELECT
        T.product_division AS code_id,
        (SELECT code_name FROM comm_code WHERE code_id = T.product_division AND parent_code_id = '${CAT.PRODUCT_DIVISION}') AS code_name,
        COUNT(1)::int AS cnt
      FROM customer_cs_mng T
      WHERE 1=1 ${yearFilter}
      GROUP BY T.product_division
      ORDER BY cnt DESC
      `,
      args,
    );

    return { summary, byStatus, byMngType, byProductDivision };
  }

  /** parent_code_id 기준 옵션 조회 */
  static async commCodeOptions(parentCodeId: string) {
    return await query<any>(
      `SELECT code_id AS value, code_name AS label
         FROM comm_code
        WHERE parent_code_id = $1 AND COALESCE(status,'active') = 'active'
        ORDER BY sort_order, code_name`,
      [parentCodeId],
    );
  }

  /** 카테고리 ID 매핑 노출 — 프론트에서 어떤 parent_code_id 를 호출해야 할지 알기 위함 */
  static categories() {
    return CAT;
  }
}
