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

// 년도별 영업목표 + 실적 조회
// 원본: contractMgmt.xml getYearGoalInfo
// body.Year 기준으로 Year, Year-1, Year-2 3개 반환
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 baseYear = parseInt(String(body.Year || body.year || new Date().getFullYear()), 10) || new Date().getFullYear();

  const fetchYear = async (yr: string) => {
    const row = await queryOne<Record<string, unknown>>(
      `
      WITH W_CM AS (
        SELECT
          CM.area_cd,
          COALESCE(NULLIF(
            CASE
              WHEN COALESCE(NULLIF(CM.contract_price,''), '0') != '0' THEN COALESCE(NULLIF(CM.contract_price,''), '0')
              WHEN CM.contract_currency = '0001566' AND COALESCE(NULLIF(CM.contract_price_currency,''), '0') != '0' THEN COALESCE(NULLIF(CM.contract_price_currency,''), '0')
              ELSE COALESCE(NULLIF(CM.contract_price,''), '0')
            END, ''
          ), '0') AS contract_price
        FROM contract_mgmt CM
        WHERE CM.contract_result = '0000964'
          AND TO_CHAR(TO_DATE(CM.contract_date, 'YYYY-MM-DD'), 'YYYY') = $1
      )
      SELECT
        $1 AS "YEAR",
        (SELECT COALESCE(MAX(price::integer), 0) FROM pms_pjt_year_goal WHERE year = $1) AS "PRICE",
        (SELECT objid FROM pms_pjt_year_goal WHERE year = $1 LIMIT 1) AS "YEAR_GOAL_OBJID",
        (SELECT COUNT(1) FROM contract_mgmt WHERE TO_CHAR(TO_DATE(contract_date,'YYYY-MM-DD'),'YYYY') = $1) AS "CONTRACT_CNT_YEAR_ALL",
        (SELECT COUNT(1) FROM W_CM) AS "CONTRACT_CNT_YEAR",
        (SELECT COUNT(1) FROM W_CM WHERE area_cd = '0001220') AS "CONTRACT_CNT_YEAR_IN",
        (SELECT COUNT(1) FROM W_CM WHERE area_cd = '0001221') AS "CONTRACT_CNT_YEAR_OUT",
        COALESCE((SELECT (SUM(COALESCE(contract_price::float, 0)))/100000000 FROM W_CM), 0) AS "CONTRACT_COST_YEAR",
        CASE
          WHEN (SELECT COALESCE(MAX(price::integer), 0) FROM pms_pjt_year_goal WHERE year = $1) = 0 THEN 0
          ELSE COALESCE(
            (
              (SELECT (SUM(COALESCE(contract_price::float, 0)))/100000000 FROM W_CM)
              / NULLIF((SELECT MAX(price::integer) FROM pms_pjt_year_goal WHERE year = $1), 0)
            ) * 100,
            0
          )
        END AS "GOAL_RATE"
      `,
      [yr]
    );
    return row || { YEAR: yr, PRICE: 0, CONTRACT_CNT_YEAR: 0, CONTRACT_COST_YEAR: 0, GOAL_RATE: 0 };
  };

  const years = await Promise.all([
    fetchYear(String(baseYear - 2)),
    fetchYear(String(baseYear - 1)),
    fetchYear(String(baseYear)),
  ]);

  return NextResponse.json({ success: true, years });
}
