import { NextRequest, NextResponse } from "next/server";
import { queryRows } from "@/lib/db";
import { getSession } from "@/lib/session";

// 영업관리 > 계약현황 대시보드 메인 테이블
// 원본: contractMgmt.xml getContractDashBoard
// - 12개월 × 제품구분(0000001 자식) 매트릭스
// - 각 셀은 해당 월 × 제품의 수주 건수
// - 계 row (연간 합계), 월별 row 반환
// - 각 row에 연간 매출액(원화 환산), 출고 수량 포함
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json();
  const year = String(body.Year || body.year || new Date().getFullYear());

  // 제품구분 종류 (CODE list)
  const products = await queryRows<{ CODE: string; NAME: string }>(
    `SELECT code_id AS "CODE", code_name AS "NAME"
     FROM comm_code
     WHERE parent_code_id = '0000001'
       AND COALESCE(status,'active') = 'active'
     ORDER BY code_id`
  );

  // 동적 필터
  const extraConditions: string[] = [];
  const extraParams: unknown[] = [];
  let idx = 2; // $1 은 year
  if (body.category_cd) {
    extraConditions.push(`CM.category_cd = $${idx++}`);
    extraParams.push(body.category_cd);
  }
  if (body.customer_objid) {
    extraConditions.push(`CM.customer_objid = $${idx++}`);
    extraParams.push(body.customer_objid);
  }
  if (body.product) {
    extraConditions.push(`CM.product = $${idx++}`);
    extraParams.push(body.product);
  }
  const extraWhere = extraConditions.length > 0 ? " AND " + extraConditions.join(" AND ") : "";

  // 월별 × 제품 cross-tab 데이터 집계
  const monthly = await queryRows<Record<string, unknown>>(
    `
    WITH base AS (
      SELECT
        TO_CHAR(TO_DATE(CM.contract_date, 'YYYY-MM-DD'), 'MM') AS mm,
        CM.product,
        COALESCE(NULLIF(
          CASE
            WHEN COALESCE(NULLIF(CM.contract_price,''), '0')::float != 0 THEN CM.contract_price
            WHEN CM.contract_currency = '0001566' AND COALESCE(NULLIF(CM.contract_price_currency,''), '0')::float != 0 THEN CM.contract_price_currency
            ELSE '0'
          END, ''
        )::float, 0) AS contract_price,
        (SELECT COUNT(*) FROM project_mgmt PM RIGHT JOIN release_mgmt RM ON PM.objid = RM.parent_objid WHERE CM.objid = PM.contract_objid) AS release_qty
      FROM contract_mgmt CM
      WHERE CM.contract_date IS NOT NULL
        AND CM.contract_result = '0000964'
        AND TO_CHAR(TO_DATE(CM.contract_date, 'YYYY-MM-DD'), 'YYYY') = $1
        ${extraWhere}
    )
    SELECT mm AS "MM",
           product AS "PRODUCT",
           COUNT(*) AS "CNT",
           SUM(contract_price) AS "COST",
           SUM(release_qty) AS "RELEASE_QTY"
    FROM base
    GROUP BY mm, product
    ORDER BY mm
    `,
    [year, ...extraParams]
  );

  // row-per-month 로 재구성: { MM: '01', total_cnt, total_cost, release, byProduct: { <code>: cnt } }
  type MonthRow = {
    MM: string;
    CONTRACT_CNT_YEAR: number;
    CONTRACT_COST_YEAR_ORG: number;
    CONTRACT_COST_YEAR: number; // /100000000
    RELEASE_CNT_YEAR: number;
    [key: string]: number | string; // CONTRACT_CNT_MONTH_<code>
  };

  const months: MonthRow[] = [];
  for (let i = 1; i <= 12; i++) {
    const mm = String(i).padStart(2, "0");
    const row: MonthRow = {
      MM: mm,
      CONTRACT_CNT_YEAR: 0,
      CONTRACT_COST_YEAR_ORG: 0,
      CONTRACT_COST_YEAR: 0,
      RELEASE_CNT_YEAR: 0,
    };
    for (const p of products) row[`CONTRACT_CNT_MONTH_${p.CODE}`] = 0;
    months.push(row);
  }

  for (const r of monthly) {
    const mm = String(r.MM || "").padStart(2, "0");
    const m = months.find((x) => x.MM === mm);
    if (!m) continue;
    const cnt = Number(r.CNT || 0);
    const cost = Number(r.COST || 0);
    const rel = Number(r.RELEASE_QTY || 0);
    const pcode = String(r.PRODUCT || "");
    m.CONTRACT_CNT_YEAR += cnt;
    m.CONTRACT_COST_YEAR_ORG += cost;
    m.CONTRACT_COST_YEAR = m.CONTRACT_COST_YEAR_ORG / 100000000;
    m.RELEASE_CNT_YEAR += rel;
    if (pcode) {
      const key = `CONTRACT_CNT_MONTH_${pcode}`;
      m[key] = (Number(m[key] || 0) + cnt);
    }
  }

  // 계(합계) row
  const total: MonthRow = {
    MM: "",
    CONTRACT_CNT_YEAR: 0,
    CONTRACT_COST_YEAR_ORG: 0,
    CONTRACT_COST_YEAR: 0,
    RELEASE_CNT_YEAR: 0,
  };
  for (const p of products) total[`CONTRACT_CNT_MONTH_${p.CODE}`] = 0;
  for (const m of months) {
    total.CONTRACT_CNT_YEAR += m.CONTRACT_CNT_YEAR;
    total.CONTRACT_COST_YEAR_ORG += m.CONTRACT_COST_YEAR_ORG;
    total.RELEASE_CNT_YEAR += m.RELEASE_CNT_YEAR;
    for (const p of products) {
      const key = `CONTRACT_CNT_MONTH_${p.CODE}`;
      total[key] = Number(total[key] || 0) + Number(m[key] || 0);
    }
  }
  total.CONTRACT_COST_YEAR = total.CONTRACT_COST_YEAR_ORG / 100000000;

  return NextResponse.json({
    products,
    total,
    months,
    year,
  });
}
