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

// 대시보드 (project.dashBoardMain.do + dashboard.getmainDash_* 대응)
// 데이터 소스: project_mgmt + contract_mgmt + planning_issue + pms_wbs_task + release_mgmt
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 || new Date().getFullYear());

  try {
    // 1) 프로젝트 상태별 카운트 (원본 contractMgmt.projectCount 5상태 이식)
    // 전체 = 계획미수립 + 진행중 + 지연 + 종료 (상호 배타적)
    // 계획미수립: PLAN_START/END 자체가 없음 (act이 아니라 plan 기준!)
    const projectStats = await queryOne<Record<string, unknown>>(
      `WITH pm_base AS (
         SELECT PM.objid::text AS objid,
           -- 셋업 완료율 (100%면 종료)
           (SELECT CASE
                     WHEN COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) = 0 THEN 0
                     ELSE ROUND((COUNT(CASE WHEN COALESCE(setup_act_end,'') != '' AND COALESCE(parent_objid,'') != '' THEN 1 END)::float
                                / COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) * 100)::numeric)::int
                   END
             FROM setup_wbs_task WHERE contract_objid = PM.objid::text) AS setup_rate,
           -- WBS 계획(PLAN_START/END) 존재 여부
           (SELECT COUNT(*) FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND (
             (COALESCE(design_plan_start,'') != '' AND COALESCE(design_plan_end,'') != '')
             OR (COALESCE(purchase_plan_start,'') != '' AND COALESCE(purchase_plan_end,'') != '')
             OR (COALESCE(produce_plan_start,'') != '' AND COALESCE(produce_plan_end,'') != '')
           )) AS wbs_plan_cnt,
           -- 셋업 계획 존재 여부
           (SELECT COUNT(*) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
            AND COALESCE(setup_plan_start,'') != '' AND COALESCE(setup_plan_end,'') != '') AS setup_plan_cnt,
           -- 지연 task 존재 여부
           (EXISTS (SELECT 1 FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND (
             (COALESCE(design_plan_end,'') != '' AND TO_DATE(design_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(design_act_end,'') = '')
             OR (COALESCE(purchase_plan_end,'') != '' AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(purchase_act_end,'') = '')
             OR (COALESCE(produce_plan_end,'') != '' AND TO_DATE(produce_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(produce_act_end,'') = '')
           )) OR EXISTS (SELECT 1 FROM setup_wbs_task WHERE contract_objid = PM.objid::text
             AND COALESCE(setup_plan_end,'') != '' AND TO_DATE(setup_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(setup_act_end,'') = '')
           ) AS has_delay
         FROM project_mgmt PM
         INNER JOIN contract_mgmt CM ON CM.objid = PM.contract_objid
         WHERE CM.contract_result = '0000964'
           -- 원본 Year 필터: 해당 연도가 WBS PLAN 범위 내에 포함되는 프로젝트
           AND $1 BETWEEN (
               SELECT LEAST(
                 MIN(TO_CHAR(TO_DATE(NULLIF(purchase_plan_start,''),'YYYY-MM-DD'),'YYYY')),
                 MIN(TO_CHAR(TO_DATE(NULLIF(produce_plan_start,''),'YYYY-MM-DD'),'YYYY')),
                 MIN(TO_CHAR(TO_DATE(NULLIF(design_plan_start,''),'YYYY-MM-DD'),'YYYY')),
                 (SELECT MIN(TO_CHAR(TO_DATE(NULLIF(setup_plan_start,''),'YYYY-MM-DD'),'YYYY'))
                    FROM setup_wbs_task S WHERE S.contract_objid = PM.objid::text)
               )
               FROM pms_wbs_task O WHERE O.contract_objid = PM.objid::text)
           AND (
               SELECT GREATEST(
                 MAX(TO_CHAR(TO_DATE(NULLIF(purchase_plan_end,''),'YYYY-MM-DD'),'YYYY')),
                 MAX(TO_CHAR(TO_DATE(NULLIF(produce_plan_end,''),'YYYY-MM-DD'),'YYYY')),
                 MAX(TO_CHAR(TO_DATE(NULLIF(design_plan_end,''),'YYYY-MM-DD'),'YYYY')),
                 (SELECT MAX(TO_CHAR(TO_DATE(NULLIF(setup_plan_end,''),'YYYY-MM-DD'),'YYYY'))
                    FROM setup_wbs_task S WHERE S.contract_objid = PM.objid::text)
               )
               FROM pms_wbs_task O WHERE O.contract_objid = PM.objid::text)
       ),
       pm_cat AS (
         SELECT objid,
           CASE
             WHEN setup_rate >= 100 THEN '종료'
             WHEN wbs_plan_cnt = 0 AND setup_plan_cnt = 0 THEN '계획미수립'
             WHEN has_delay THEN '지연'
             ELSE '진행중'
           END AS cat
         FROM pm_base
       )
       SELECT
         (SELECT COUNT(*) FROM pm_cat)::int AS "CNT_TOTAL",
         (SELECT COUNT(*) FROM pm_cat WHERE cat = '계획미수립')::int AS "CNT_NOPLAN",
         (SELECT COUNT(*) FROM pm_cat WHERE cat = '진행중')::int AS "CNT_ING",
         (SELECT COUNT(*) FROM pm_cat WHERE cat = '지연')::int AS "CNT_DELAY",
         (SELECT COUNT(*) FROM pm_cat WHERE cat = '종료')::int AS "CNT_END",
         -- 이슈/비용 집계는 전체 기준
         COALESCE((SELECT COUNT(*) FROM planning_issue WHERE status = 'release' AND project_objid IN (SELECT objid FROM pm_cat)), 0)::int AS "ISSUE_TOTAL",
         COALESCE((SELECT COUNT(*) FROM planning_issue WHERE status = 'release' AND project_objid IN (SELECT objid FROM pm_cat)
                    AND (COALESCE(design_result,'') = '' OR COALESCE(design_date,'') = '')), 0)::int AS "ISSUE_MISS"
      `,
      [year],
    ) || {};

    // 2) 제품별 수주현황 - pie chart (contractMgmt.getContractCNTByProduct 대응)
    const productDist = await queryRows(
      `SELECT
         CM.product AS "CODE",
         (SELECT code_name FROM comm_code WHERE code_id = CM.product LIMIT 1) AS "NAME",
         COUNT(*)::int AS "CNT"
       FROM contract_mgmt CM
       WHERE SUBSTR(COALESCE(CM.contract_date,''), 1, 4) = $1
         AND CM.contract_result = '0000964'
         AND CM.product IS NOT NULL AND CM.product != ''
       GROUP BY CM.product
       ORDER BY COUNT(*) DESC`,
      [year],
    );

    // 3) 고객사별 수주현황 - pie chart (contractMgmt.getContractCNTBySupply 대응)
    const supplyDist = await queryRows(
      `SELECT
         CM.customer_objid AS "CODE",
         (SELECT supply_name FROM supply_mng WHERE objid::text = CM.customer_objid LIMIT 1) AS "NAME",
         COUNT(*)::int AS "CNT"
       FROM contract_mgmt CM
       WHERE SUBSTR(COALESCE(CM.contract_date,''), 1, 4) = $1
         AND CM.contract_result = '0000964'
         AND CM.customer_objid IS NOT NULL AND CM.customer_objid != ''
       GROUP BY CM.customer_objid
       ORDER BY COUNT(*) DESC`,
      [year],
    );

    // 3) 월별 계약금액 (년도별 매출현황)
    const monthlyContract = await queryRows(
      `SELECT
         SUBSTR(contract_date, 6, 2)::int AS "MONTH",
         COALESCE(SUM(COALESCE(NULLIF(contract_price,'')::numeric, 0)), 0) AS "AMOUNT"
       FROM contract_mgmt
       WHERE SUBSTR(COALESCE(contract_date,''), 1, 4) = $1
         AND contract_result = '0000964'
         AND SUBSTR(COALESCE(contract_date,''), 6, 2) ~ '^[0-9]{2}$'
       GROUP BY SUBSTR(contract_date, 6, 2)
       ORDER BY SUBSTR(contract_date, 6, 2)`,
      [year],
    );

    // 4) 주요 프로젝트 리스트 + 진척율
    const projectList = await queryRows(
      `SELECT
         PM.objid::text AS "OBJID",
         PM.project_no AS "PROJECT_NO",
         PM.project_name AS "PROJECT_NAME",
         (SELECT code_name FROM comm_code WHERE code_id = PM.category_cd LIMIT 1) AS "CATEGORY_NAME",
         (SELECT supply_name FROM supply_mng WHERE objid::text = PM.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
         (SELECT code_name FROM comm_code WHERE code_id = PM.product LIMIT 1) AS "PRODUCT_NAME",
         (SELECT code_name FROM comm_code WHERE code_id = PM.manufacture_plant LIMIT 1) AS "MANUFACTURE_PLANT_NAME",
         PM.contract_del_date AS "CONTRACT_DEL_DATE",
         PM.req_del_date AS "REQ_DEL_DATE",
         COALESCE(NULLIF(PM.contract_price_currency,'')::numeric, 0) AS "CONTRACT_PRICE",
         CASE WHEN (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) = 0 THEN 0
              ELSE ROUND(
                ((SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND COALESCE(design_act_end,'') != '')
                +(SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND COALESCE(purchase_act_end,'') != '')
                +(SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND COALESCE(produce_act_end,'') != ''))::numeric
                / ((SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) * 3)::numeric * 100, 1)
         END AS "TOTAL_RATE",
         -- 이슈: 발생/조치/미결
         (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release') AS "ISSUE_CNT",
         (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release'
          AND COALESCE(design_result,'') != '' AND COALESCE(design_date,'') != '') AS "ISSUE_DONE_CNT",
         (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release')
           - (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release'
              AND COALESCE(design_result,'') != '' AND COALESCE(design_date,'') != '') AS "ISSUE_MISS_CNT",
         -- 투입원가 항목별 (원본 costMgmt.costTotaltGridList 이식, 노무비 실적은 WORK_DIARY 복잡도로 0 처리)
         COALESCE((SELECT material_cost_goal::numeric FROM input_cost_goal WHERE contract_objid::text = PM.objid::text LIMIT 1), 0) AS "MATERIAL_COST_GOAL",
         COALESCE((SELECT labor_cost_goal::numeric    FROM input_cost_goal WHERE contract_objid::text = PM.objid::text LIMIT 1), 0) AS "LABOR_COST_GOAL",
         COALESCE((SELECT expense_cost_goal::numeric  FROM input_cost_goal WHERE contract_objid::text = PM.objid::text LIMIT 1), 0) AS "EXPENSE_COST_GOAL",
         -- 재료비 실적 = purchase_order_master(approvalComplete) total_supply_unit_price 합
         COALESCE((SELECT SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0))
                   FROM purchase_order_master
                   WHERE contract_mgmt_objid = PM.objid AND status = 'approvalComplete'), 0) AS "ACCRUAL_MATERIAL_COST",
         -- 노무비 실적 (WORK_DIARY 기반 계산 복잡 — 추후 보강)
         0 AS "LABOR_COST_ACTUAL",
         -- 경비 실적 = expense_master + expense_detail SETTLE_AMOUNT 합
         COALESCE((SELECT SUM(settle)
                   FROM (SELECT (SUM(COALESCE(ED.card_used,'0')::numeric) + SUM(COALESCE(ED.cash_used,'0')::numeric) - SUM(COALESCE(ED.payment,'0')::numeric)) AS settle
                         FROM expense_master EM
                         LEFT OUTER JOIN expense_detail ED ON ED.expense_master_objid = EM.expense_master_objid
                         WHERE EM.project_mgmt_objid = PM.objid::text
                         GROUP BY EM.expense_master_objid) s), 0) AS "ACCRUAL_EXPENSE",
         RM.release_date AS "RELEASE_DATE",
         -- 셋업 완료일 (setup_wbs_task 중 마지막 act_end)
         (SELECT MAX(setup_act_end) FROM setup_wbs_task
          WHERE contract_objid = PM.objid::text AND COALESCE(parent_objid,'') != ''
            AND COALESCE(setup_act_end,'') != '') AS "SETUP_DONE_DATE",
         -- 셋업 진척율
         COALESCE((SELECT CASE
                     WHEN COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) = 0 THEN 0
                     ELSE ROUND((COUNT(CASE WHEN COALESCE(setup_act_end,'') != '' AND COALESCE(parent_objid,'') != '' THEN 1 END)::float
                                / COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) * 100)::numeric, 1) END
             FROM setup_wbs_task WHERE contract_objid = PM.objid::text), 0) AS "SETUP_RATE",
         -- 5상태 분류 (원본 projectCount 로직, 계획미수립 = PLAN_START/END 없음)
         CASE
           WHEN COALESCE((SELECT CASE
                     WHEN COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) = 0 THEN 0
                     ELSE ROUND((COUNT(CASE WHEN COALESCE(setup_act_end,'') != '' AND COALESCE(parent_objid,'') != '' THEN 1 END)::float
                                / COUNT(CASE WHEN COALESCE(parent_objid,'') != '' THEN 1 END) * 100)::numeric)::int END
             FROM setup_wbs_task WHERE contract_objid = PM.objid::text), 0) >= 100 THEN '종료'
           WHEN (SELECT COUNT(*) FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND (
                   (COALESCE(design_plan_start,'') != '' AND COALESCE(design_plan_end,'') != '')
                   OR (COALESCE(purchase_plan_start,'') != '' AND COALESCE(purchase_plan_end,'') != '')
                   OR (COALESCE(produce_plan_start,'') != '' AND COALESCE(produce_plan_end,'') != '')
                 )) = 0
            AND (SELECT COUNT(*) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
                 AND COALESCE(setup_plan_start,'') != '' AND COALESCE(setup_plan_end,'') != '') = 0 THEN '계획미수립'
           WHEN (EXISTS (SELECT 1 FROM pms_wbs_task WHERE contract_objid = PM.objid::text AND (
             (COALESCE(design_plan_end,'') != '' AND TO_DATE(design_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(design_act_end,'') = '')
             OR (COALESCE(purchase_plan_end,'') != '' AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(purchase_act_end,'') = '')
             OR (COALESCE(produce_plan_end,'') != '' AND TO_DATE(produce_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(produce_act_end,'') = '')
           )) OR EXISTS (SELECT 1 FROM setup_wbs_task WHERE contract_objid = PM.objid::text
             AND COALESCE(setup_plan_end,'') != '' AND TO_DATE(setup_plan_end,'YYYY-MM-DD') < CURRENT_DATE AND COALESCE(setup_act_end,'') = ''))
             THEN '지연'
           ELSE '진행중'
         END AS "STATUS_TITLE"
       FROM project_mgmt PM
       INNER JOIN contract_mgmt CM ON CM.objid = PM.contract_objid
       LEFT OUTER JOIN release_mgmt RM ON RM.parent_objid = PM.objid::text
       WHERE CM.contract_result = '0000964'
         AND $1 BETWEEN (
             SELECT LEAST(
               MIN(TO_CHAR(TO_DATE(NULLIF(purchase_plan_start,''),'YYYY-MM-DD'),'YYYY')),
               MIN(TO_CHAR(TO_DATE(NULLIF(produce_plan_start,''),'YYYY-MM-DD'),'YYYY')),
               MIN(TO_CHAR(TO_DATE(NULLIF(design_plan_start,''),'YYYY-MM-DD'),'YYYY')),
               (SELECT MIN(TO_CHAR(TO_DATE(NULLIF(setup_plan_start,''),'YYYY-MM-DD'),'YYYY'))
                  FROM setup_wbs_task S WHERE S.contract_objid = PM.objid::text)
             )
             FROM pms_wbs_task O WHERE O.contract_objid = PM.objid::text)
         AND (
             SELECT GREATEST(
               MAX(TO_CHAR(TO_DATE(NULLIF(purchase_plan_end,''),'YYYY-MM-DD'),'YYYY')),
               MAX(TO_CHAR(TO_DATE(NULLIF(produce_plan_end,''),'YYYY-MM-DD'),'YYYY')),
               MAX(TO_CHAR(TO_DATE(NULLIF(design_plan_end,''),'YYYY-MM-DD'),'YYYY')),
               (SELECT MAX(TO_CHAR(TO_DATE(NULLIF(setup_plan_end,''),'YYYY-MM-DD'),'YYYY'))
                  FROM setup_wbs_task S WHERE S.contract_objid = PM.objid::text)
             )
             FROM pms_wbs_task O WHERE O.contract_objid = PM.objid::text)
       ORDER BY PM.regdate DESC
`,
      [year],
    );

    // 5) 3년치 영업현황 (getYearGoalInfo 이식): 수주건수 국내/해외/비율, 계약금액(억원), 영업목표, 달성율
    const years = [Number(year), Number(year) - 1, Number(year) - 2].map(String);
    const yearGoalInfo = await Promise.all(
      years.map(async (y) => {
        const row = await queryOne<Record<string, unknown>>(
          `WITH W_CM AS (
             SELECT area_cd,
               CASE WHEN COALESCE(NULLIF(contract_price,''),'0') != '0' THEN contract_price
                    WHEN contract_currency = '0001566' AND COALESCE(NULLIF(contract_price_currency,''),'0') != '0' THEN contract_price_currency
                    ELSE COALESCE(NULLIF(contract_price,''),'0')
               END AS price
             FROM contract_mgmt
             WHERE contract_result = '0000964'
               AND TO_CHAR(TO_DATE(contract_date, 'YYYY-MM-DD'), 'YYYY') = $1
           )
           SELECT
             $1 AS "YEAR",
             (SELECT COUNT(*) FROM contract_mgmt WHERE TO_CHAR(TO_DATE(contract_date,'YYYY-MM-DD'),'YYYY') = $1) AS "CONTRACT_CNT_YEAR_ALL",
             (SELECT COUNT(*) FROM W_CM)::int AS "CONTRACT_CNT_YEAR",
             (SELECT COUNT(*) FROM W_CM WHERE area_cd = '0001220')::int AS "CONTRACT_CNT_YEAR_IN",
             (SELECT COUNT(*) FROM W_CM WHERE area_cd = '0001221')::int AS "CONTRACT_CNT_YEAR_OUT",
             COALESCE((SELECT (SUM(COALESCE(NULLIF(price,'')::numeric, 0))) / 100000000 FROM W_CM)::numeric(18,1), 0) AS "CONTRACT_COST_YEAR",
             COALESCE((SELECT price::numeric FROM pms_pjt_year_goal WHERE year = $1 LIMIT 1), 0) AS "PRICE",
             (SELECT objid::text FROM pms_pjt_year_goal WHERE year = $1 LIMIT 1) AS "YEAR_GOAL_OBJID"`,
          [y],
        );
        if (!row) return { YEAR: y };
        const total = Number(row.CONTRACT_CNT_YEAR_ALL || 0);
        const ok = Number(row.CONTRACT_CNT_YEAR || 0);
        const cost = Number(row.CONTRACT_COST_YEAR || 0);
        const goal = Number(row.PRICE || 0);
        return {
          ...row,
          CONTRACT_CNT_YEAR_RATE: total > 0 ? Math.round((ok / total) * 1000) / 10 : 0,
          GOAL_RATE: goal > 0 ? Math.round((cost / goal) * 1000) / 10 : 0,
        };
      })
    );

    return NextResponse.json({
      projectStats: projectStats || {},
      productDist,
      supplyDist,
      monthlyContract,
      projectList,
      yearGoalInfo,
    });
  } catch (error) {
    console.error("Dashboard error:", error);
    return NextResponse.json({
      projectStats: {},
      productDist: [],
      supplyDist: [],
      monthlyContract: [],
      projectList: [],
      yearGoalInfo: [],
    });
  }
}
