/**
 * 생산계획 서비스
 * - 수주 데이터 조회 (품목별 그룹핑)
 * - 안전재고 부족분 조회
 * - 자동 스케줄 생성
 * - 스케줄 병합
 * - 반제품 계획 자동 생성
 * - 스케줄 분할
 */

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

// ─── 수주 데이터 조회 (품목별 그룹핑) ───

export async function getOrderSummary(
  companyCode: string,
  options?: { excludePlanned?: boolean; itemCode?: string; itemName?: string }
) {
  const pool = getPool();
  const conditions: string[] = ["so.company_code = $1"];
  const params: any[] = [companyCode];
  let paramIdx = 2;

  if (options?.itemCode) {
    conditions.push(`so.part_code ILIKE $${paramIdx}`);
    params.push(`%${options.itemCode}%`);
    paramIdx++;
  }
  if (options?.itemName) {
    conditions.push(`so.part_name ILIKE $${paramIdx}`);
    params.push(`%${options.itemName}%`);
    paramIdx++;
  }

  const whereClause = conditions.join(" AND ");

  const query = `
    WITH order_summary AS (
      SELECT
        so.part_code AS item_code,
        COALESCE(so.part_name, so.part_code) AS item_name,
        SUM(COALESCE(so.order_qty::numeric, 0)) AS total_order_qty,
        SUM(COALESCE(so.ship_qty::numeric, 0)) AS total_ship_qty,
        SUM(COALESCE(so.balance_qty::numeric, 0)) AS total_balance_qty,
        COUNT(*) AS order_count,
        MIN(so.due_date) AS earliest_due_date
      FROM sales_order_mng so
      WHERE ${whereClause}
      GROUP BY so.part_code, so.part_name
    ),
    stock_info AS (
      SELECT
        item_code,
        SUM(COALESCE(current_qty::numeric, 0)) AS current_stock,
        MAX(COALESCE(safety_qty::numeric, 0)) AS safety_stock
      FROM inventory_stock
      WHERE company_code = $1
      GROUP BY item_code
    ),
    plan_info AS (
      SELECT
        item_code,
        SUM(CASE WHEN status = 'planned' THEN COALESCE(plan_qty, 0) ELSE 0 END) AS existing_plan_qty,
        SUM(CASE WHEN status = 'in_progress' THEN COALESCE(plan_qty, 0) ELSE 0 END) AS in_progress_qty
      FROM production_plan_mng
      WHERE company_code = $1
        AND COALESCE(product_type, '완제품') = '완제품'
        AND status NOT IN ('completed', 'cancelled')
      GROUP BY item_code
    )
    SELECT
      os.item_code,
      os.item_name,
      os.total_order_qty,
      os.total_ship_qty,
      os.total_balance_qty,
      os.order_count,
      os.earliest_due_date,
      COALESCE(si.current_stock, 0) AS current_stock,
      COALESCE(si.safety_stock, 0) AS safety_stock,
      COALESCE(pi.existing_plan_qty, 0) AS existing_plan_qty,
      COALESCE(pi.in_progress_qty, 0) AS in_progress_qty,
      GREATEST(
        os.total_balance_qty + COALESCE(si.safety_stock, 0) - COALESCE(si.current_stock, 0)
        - COALESCE(pi.existing_plan_qty, 0) - COALESCE(pi.in_progress_qty, 0),
        0
      ) AS required_plan_qty
    FROM order_summary os
    LEFT JOIN stock_info si ON os.item_code = si.item_code
    LEFT JOIN plan_info pi ON os.item_code = pi.item_code
    ${options?.excludePlanned ? "WHERE COALESCE(pi.existing_plan_qty, 0) = 0" : ""}
    ORDER BY os.item_code;
  `;

  const result = await pool.query(query, params);

  // 그룹별 상세 수주 데이터도 함께 조회
  const detailWhere = conditions.map(c => c.replace(/so\./g, "")).join(" AND ");
  const detailQuery = `
    SELECT
      id, order_no, part_code, part_name,
      COALESCE(order_qty::numeric, 0) AS order_qty,
      COALESCE(ship_qty::numeric, 0) AS ship_qty,
      COALESCE(balance_qty::numeric, 0) AS balance_qty,
      due_date, status, partner_id, manager_name
    FROM sales_order_mng
    WHERE ${detailWhere}
    ORDER BY part_code, due_date;
  `;
  const detailResult = await pool.query(detailQuery, params);

  // 그룹별로 상세 데이터 매핑
  const ordersByItem: Record<string, any[]> = {};
  for (const row of detailResult.rows) {
    const key = row.part_code || "__null__";
    if (!ordersByItem[key]) ordersByItem[key] = [];
    ordersByItem[key].push(row);
  }

  const data = result.rows.map((group: any) => ({
    ...group,
    orders: ordersByItem[group.item_code || "__null__"] || [],
  }));

  logger.info("수주 데이터 조회", { companyCode, groupCount: data.length });
  return data;
}

// ─── 안전재고 부족분 조회 ───

export async function getStockShortage(companyCode: string) {
  const pool = getPool();

  const query = `
    SELECT
      ist.item_code,
      ii.item_name,
      COALESCE(ist.current_qty::numeric, 0) AS current_qty,
      COALESCE(ist.safety_qty::numeric, 0) AS safety_qty,
      (COALESCE(ist.current_qty::numeric, 0) - COALESCE(ist.safety_qty::numeric, 0)) AS shortage_qty,
      GREATEST(
        COALESCE(ist.safety_qty::numeric, 0) * 2 - COALESCE(ist.current_qty::numeric, 0), 0
      ) AS recommended_qty,
      ist.last_in_date
    FROM inventory_stock ist
    LEFT JOIN item_info ii ON ist.item_code = ii.id AND ist.company_code = ii.company_code
    WHERE ist.company_code = $1
      AND COALESCE(ist.current_qty::numeric, 0) < COALESCE(ist.safety_qty::numeric, 0)
    ORDER BY shortage_qty ASC;
  `;

  const result = await pool.query(query, [companyCode]);
  logger.info("안전재고 부족분 조회", { companyCode, count: result.rowCount });
  return result.rows;
}

// ─── 생산계획 CRUD ───

export async function getPlanById(companyCode: string, planId: number) {
  const pool = getPool();
  const result = await pool.query(
    `SELECT * FROM production_plan_mng WHERE id = $1 AND company_code = $2`,
    [planId, companyCode]
  );
  return result.rows[0] || null;
}

export async function updatePlan(
  companyCode: string,
  planId: number,
  data: Record<string, any>,
  updatedBy: string
) {
  const pool = getPool();

  const allowedFields = [
    "plan_qty", "start_date", "end_date", "due_date",
    "equipment_id", "equipment_code", "equipment_name",
    "manager_name", "work_shift", "priority", "remarks", "status",
    "item_code", "item_name", "product_type", "order_no",
  ];

  const setClauses: string[] = [];
  const params: any[] = [];
  let paramIdx = 1;

  for (const field of allowedFields) {
    if (data[field] !== undefined) {
      setClauses.push(`${field} = $${paramIdx}`);
      params.push(data[field]);
      paramIdx++;
    }
  }

  if (setClauses.length === 0) {
    throw new Error("수정할 필드가 없습니다");
  }

  setClauses.push(`updated_date = NOW()`);
  setClauses.push(`updated_by = $${paramIdx}`);
  params.push(updatedBy);
  paramIdx++;

  params.push(planId);
  params.push(companyCode);

  const query = `
    UPDATE production_plan_mng
    SET ${setClauses.join(", ")}
    WHERE id = $${paramIdx - 1} AND company_code = $${paramIdx}
    RETURNING *
  `;

  const result = await pool.query(query, params);
  if (result.rowCount === 0) {
    throw new Error("생산계획을 찾을 수 없거나 권한이 없습니다");
  }
  logger.info("생산계획 수정", { companyCode, planId });
  return result.rows[0];
}

export async function deletePlan(companyCode: string, planId: number) {
  const pool = getPool();
  const result = await pool.query(
    `DELETE FROM production_plan_mng WHERE id = $1 AND company_code = $2 RETURNING id`,
    [planId, companyCode]
  );
  if (result.rowCount === 0) {
    throw new Error("생산계획을 찾을 수 없거나 권한이 없습니다");
  }
  logger.info("생산계획 삭제", { companyCode, planId });
  return { id: planId };
}

// ─── 자동 스케줄 생성 ───

interface GenerateScheduleItem {
  item_code: string;
  item_name: string;
  required_qty: number;
  earliest_due_date: string;
  hourly_capacity?: number;
  daily_capacity?: number;
  lead_time?: number;
}

interface GenerateScheduleOptions {
  safety_lead_time?: number;
  recalculate_unstarted?: boolean;
  product_type?: string;
}

/**
 * 자동 스케줄 미리보기 (DB 변경 없이 예상 결과만 반환)
 */
export async function previewSchedule(
  companyCode: string,
  items: GenerateScheduleItem[],
  options: GenerateScheduleOptions
) {
  const pool = getPool();
  const productType = options.product_type || "완제품";
  const safetyLeadTime = options.safety_lead_time || 1;

  const previews: any[] = [];
  const deletedSchedules: any[] = [];
  const keptSchedules: any[] = [];

  for (const item of items) {
    if (options.recalculate_unstarted) {
      // 삭제 대상(planned) 상세 조회
      const deleteResult = await pool.query(
        `SELECT id, plan_no, item_code, item_name, plan_qty, start_date, end_date, status
         FROM production_plan_mng
         WHERE company_code = $1 AND item_code = $2
           AND COALESCE(product_type, '완제품') = $3
           AND status = 'planned'`,
        [companyCode, item.item_code, productType]
      );
      deletedSchedules.push(...deleteResult.rows);

      // 유지 대상(진행중 등) 상세 조회
      const keptResult = await pool.query(
        `SELECT id, plan_no, item_code, item_name, plan_qty, start_date, end_date, status, completed_qty
         FROM production_plan_mng
         WHERE company_code = $1 AND item_code = $2
           AND COALESCE(product_type, '완제품') = $3
           AND status NOT IN ('planned', 'completed', 'cancelled')`,
        [companyCode, item.item_code, productType]
      );
      keptSchedules.push(...keptResult.rows);
    }

    const dailyCapacity = item.daily_capacity || 800;
    const requiredQty = item.required_qty;
    if (requiredQty <= 0) continue;

    const productionDays = Math.ceil(requiredQty / dailyCapacity);

    const dueDate = new Date(item.earliest_due_date);
    const endDate = new Date(dueDate);
    endDate.setDate(endDate.getDate() - safetyLeadTime);
    const startDate = new Date(endDate);
    startDate.setDate(startDate.getDate() - productionDays);

    const today = new Date();
    today.setHours(0, 0, 0, 0);
    if (startDate < today) {
      startDate.setTime(today.getTime());
      endDate.setTime(startDate.getTime());
      endDate.setDate(endDate.getDate() + productionDays);
    }

    // 해당 품목의 수주 건수 확인
    const orderCountResult = await pool.query(
      `SELECT COUNT(*) AS cnt FROM sales_order_mng
       WHERE company_code = $1 AND part_code = $2 AND part_code IS NOT NULL`,
      [companyCode, item.item_code]
    );
    const orderCount = parseInt(orderCountResult.rows[0].cnt, 10);

    previews.push({
      item_code: item.item_code,
      item_name: item.item_name,
      required_qty: requiredQty,
      daily_capacity: dailyCapacity,
      hourly_capacity: item.hourly_capacity || 100,
      production_days: productionDays,
      start_date: startDate.toISOString().split("T")[0],
      end_date: endDate.toISOString().split("T")[0],
      due_date: item.earliest_due_date,
      order_count: orderCount,
      status: "planned",
    });
  }

  const summary = {
    total: previews.length + keptSchedules.length,
    new_count: previews.length,
    kept_count: keptSchedules.length,
    deleted_count: deletedSchedules.length,
  };

  logger.info("자동 스케줄 미리보기", { companyCode, summary });
  return { summary, previews, deletedSchedules, keptSchedules };
}

export async function generateSchedule(
  companyCode: string,
  items: GenerateScheduleItem[],
  options: GenerateScheduleOptions,
  createdBy: string
) {
  const pool = getPool();
  const client = await pool.connect();
  const productType = options.product_type || "완제품";
  const safetyLeadTime = options.safety_lead_time || 1;

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

    let deletedCount = 0;
    let keptCount = 0;
    const newSchedules: any[] = [];

    for (const item of items) {
      // 기존 미진행(planned) 스케줄 처리
      if (options.recalculate_unstarted) {
        const deleteResult = await client.query(
          `DELETE FROM production_plan_mng
           WHERE company_code = $1
             AND item_code = $2
             AND COALESCE(product_type, '완제품') = $3
             AND status = 'planned'
           RETURNING id`,
          [companyCode, item.item_code, productType]
        );
        deletedCount += deleteResult.rowCount || 0;

        const keptResult = await client.query(
          `SELECT COUNT(*) AS cnt FROM production_plan_mng
           WHERE company_code = $1
             AND item_code = $2
             AND COALESCE(product_type, '완제품') = $3
             AND status NOT IN ('planned', 'completed', 'cancelled')`,
          [companyCode, item.item_code, productType]
        );
        keptCount += parseInt(keptResult.rows[0].cnt, 10);
      }

      // 생산일수 계산
      const dailyCapacity = item.daily_capacity || 800;
      const requiredQty = item.required_qty;
      if (requiredQty <= 0) continue;

      const productionDays = Math.ceil(requiredQty / dailyCapacity);

      // 시작일 = 납기일 - 생산일수 - 안전리드타임
      const dueDate = new Date(item.earliest_due_date);
      const endDate = new Date(dueDate);
      endDate.setDate(endDate.getDate() - safetyLeadTime);
      const startDate = new Date(endDate);
      startDate.setDate(startDate.getDate() - productionDays);

      // 시작일이 오늘보다 이전이면 오늘로 조정
      const today = new Date();
      today.setHours(0, 0, 0, 0);
      if (startDate < today) {
        startDate.setTime(today.getTime());
        endDate.setTime(startDate.getTime());
        endDate.setDate(endDate.getDate() + productionDays);
      }

      // 계획번호 생성 (YYYYMMDD-NNNN 형식)
      const todayStr = new Date().toISOString().split("T")[0].replace(/-/g, "");
      const planNoResult = await client.query(
        `SELECT COUNT(*) + 1 AS next_no
         FROM production_plan_mng
         WHERE company_code = $1 AND plan_no LIKE $2`,
        [companyCode, `PP-${todayStr}-%`]
      );
      const nextNo = parseInt(planNoResult.rows[0].next_no, 10) || 1;
      const planNo = `PP-${todayStr}-${String(nextNo).padStart(4, "0")}`;

      const insertResult = await client.query(
        `INSERT INTO production_plan_mng (
          company_code, plan_no, plan_date, item_code, item_name,
          product_type, plan_qty, start_date, end_date, due_date,
          status, priority, hourly_capacity, daily_capacity, lead_time,
          created_by, created_date, updated_date
        ) VALUES (
          $1, $2, CURRENT_DATE, $3, $4,
          $5, $6, $7, $8, $9,
          'planned', 'normal', $10, $11, $12,
          $13, NOW(), NOW()
        ) RETURNING *`,
        [
          companyCode, planNo, item.item_code, item.item_name,
          productType, requiredQty,
          startDate.toISOString().split("T")[0],
          endDate.toISOString().split("T")[0],
          item.earliest_due_date,
          item.hourly_capacity || 100,
          dailyCapacity,
          item.lead_time || 1,
          createdBy,
        ]
      );
      newSchedules.push(insertResult.rows[0]);
    }

    await client.query("COMMIT");

    const summary = {
      total: newSchedules.length + keptCount,
      new_count: newSchedules.length,
      kept_count: keptCount,
      deleted_count: deletedCount,
    };

    logger.info("자동 스케줄 생성 완료", { companyCode, summary });
    return { summary, schedules: newSchedules };
  } catch (error) {
    await client.query("ROLLBACK");
    logger.error("자동 스케줄 생성 실패", { companyCode, error });
    throw error;
  } finally {
    client.release();
  }
}

// ─── 스케줄 병합 ───

export async function mergeSchedules(
  companyCode: string,
  scheduleIds: number[],
  productType: string,
  mergedBy: string
) {
  const pool = getPool();
  const client = await pool.connect();

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

    // 대상 스케줄 조회
    const placeholders = scheduleIds.map((_, i) => `$${i + 2}`).join(", ");
    const targetResult = await client.query(
      `SELECT * FROM production_plan_mng
       WHERE company_code = $1 AND id IN (${placeholders})
       ORDER BY start_date`,
      [companyCode, ...scheduleIds]
    );

    if (targetResult.rowCount !== scheduleIds.length) {
      throw new Error("일부 스케줄을 찾을 수 없습니다");
    }

    const rows = targetResult.rows;

    // 동일 품목 검증
    const itemCodes = [...new Set(rows.map((r: any) => r.item_code))];
    if (itemCodes.length > 1) {
      throw new Error("동일 품목의 스케줄만 병합할 수 있습니다");
    }

    // 병합 값 계산
    const totalQty = rows.reduce((sum: number, r: any) => sum + (parseFloat(r.plan_qty) || 0), 0);
    const earliestStart = rows.reduce(
      (min: string, r: any) => (!min || r.start_date < min ? r.start_date : min),
      ""
    );
    const latestEnd = rows.reduce(
      (max: string, r: any) => (!max || r.end_date > max ? r.end_date : max),
      ""
    );
    const earliestDue = rows.reduce(
      (min: string, r: any) => (!min || (r.due_date && r.due_date < min) ? r.due_date : min),
      ""
    );
    const orderNos = [...new Set(rows.map((r: any) => r.order_no).filter(Boolean))].join(", ");

    // 기존 삭제
    await client.query(
      `DELETE FROM production_plan_mng WHERE company_code = $1 AND id IN (${placeholders})`,
      [companyCode, ...scheduleIds]
    );

    // 병합된 스케줄 생성
    const planNoResult = await client.query(
      `SELECT COALESCE(MAX(CAST(REPLACE(plan_no, 'PP-', '') AS INTEGER)), 0) + 1 AS next_no
       FROM production_plan_mng WHERE company_code = $1`,
      [companyCode]
    );
    const planNo = `PP-${String(planNoResult.rows[0].next_no || 1).padStart(6, "0")}`;

    const insertResult = await client.query(
      `INSERT INTO production_plan_mng (
        company_code, plan_no, plan_date, item_code, item_name,
        product_type, plan_qty, start_date, end_date, due_date,
        status, order_no, created_by, created_date, updated_date
      ) VALUES (
        $1, $2, CURRENT_DATE, $3, $4,
        $5, $6, $7, $8, $9,
        'planned', $10, $11, NOW(), NOW()
      ) RETURNING *`,
      [
        companyCode, planNo, rows[0].item_code, rows[0].item_name,
        productType, totalQty,
        earliestStart, latestEnd, earliestDue || null,
        orderNos || null, mergedBy,
      ]
    );

    await client.query("COMMIT");
    logger.info("스케줄 병합 완료", {
      companyCode,
      mergedFrom: scheduleIds,
      mergedTo: insertResult.rows[0].id,
    });
    return insertResult.rows[0];
  } catch (error) {
    await client.query("ROLLBACK");
    logger.error("스케줄 병합 실패", { companyCode, error });
    throw error;
  } finally {
    client.release();
  }
}

// ─── 반제품 BOM 소요량 조회 (공통) ───

async function getBomChildItems(
  client: any,
  companyCode: string,
  itemCode: string
) {
  const bomQuery = `
    SELECT
      bd.child_item_id,
      ii.item_name AS child_item_name,
      ii.item_number AS child_item_code,
      bd.quantity AS bom_qty,
      bd.unit
    FROM bom b
    JOIN bom_detail bd ON b.id = bd.bom_id AND b.company_code = bd.company_code
    LEFT JOIN item_info ii ON bd.child_item_id = ii.id AND bd.company_code = ii.company_code
    WHERE b.company_code = $1
      AND b.item_code = $2
      AND COALESCE(b.status, 'active') = 'active'
  `;
  const result = await client.query(bomQuery, [companyCode, itemCode]);
  return result.rows;
}

// ─── 반제품 계획 미리보기 (실제 DB 변경 없음) ───

export async function previewSemiSchedule(
  companyCode: string,
  planIds: number[],
  options: { considerStock?: boolean; excludeUsed?: boolean }
) {
  const pool = getPool();

  const placeholders = planIds.map((_, i) => `$${i + 2}`).join(", ");
  const plansResult = await pool.query(
    `SELECT * FROM production_plan_mng
     WHERE company_code = $1 AND id IN (${placeholders})
       AND product_type = '완제품'`,
    [companyCode, ...planIds]
  );

  const previews: any[] = [];
  const existingSemiPlans: any[] = [];

  for (const plan of plansResult.rows) {
    // 이미 존재하는 반제품 계획 조회
    const existingResult = await pool.query(
      `SELECT * FROM production_plan_mng
       WHERE company_code = $1 AND parent_plan_id = $2 AND product_type = '반제품'`,
      [companyCode, plan.id]
    );
    existingSemiPlans.push(...existingResult.rows);

    const bomItems = await getBomChildItems(pool, companyCode, plan.item_code);

    for (const bomItem of bomItems) {
      let requiredQty = (parseFloat(plan.plan_qty) || 0) * (parseFloat(bomItem.bom_qty) || 1);

      if (options.considerStock) {
        const stockResult = await pool.query(
          `SELECT COALESCE(SUM(CAST(current_qty AS numeric)), 0) AS stock
           FROM inventory_stock
           WHERE company_code = $1 AND item_code = $2`,
          [companyCode, bomItem.child_item_code || bomItem.child_item_id]
        );
        const stock = parseFloat(stockResult.rows[0].stock) || 0;
        requiredQty = Math.max(requiredQty - stock, 0);
      }

      if (requiredQty <= 0) continue;

      const semiDueDate = plan.start_date;
      const semiStartDate = new Date(plan.start_date);
      semiStartDate.setDate(semiStartDate.getDate() - (parseInt(plan.lead_time) || 1));

      previews.push({
        parent_plan_id: plan.id,
        parent_plan_no: plan.plan_no,
        parent_item_name: plan.item_name,
        item_code: bomItem.child_item_code || bomItem.child_item_id,
        item_name: bomItem.child_item_name || bomItem.child_item_id,
        plan_qty: requiredQty,
        bom_qty: parseFloat(bomItem.bom_qty) || 1,
        start_date: semiStartDate.toISOString().split("T")[0],
        end_date: typeof semiDueDate === "string"
          ? semiDueDate.split("T")[0]
          : new Date(semiDueDate).toISOString().split("T")[0],
        due_date: typeof semiDueDate === "string"
          ? semiDueDate.split("T")[0]
          : new Date(semiDueDate).toISOString().split("T")[0],
        product_type: "반제품",
        status: "planned",
      });
    }
  }

  // 기존 반제품 중 삭제 대상 (status = planned)
  const deletedSchedules = existingSemiPlans.filter(
    (s) => s.status === "planned"
  );
  // 기존 반제품 중 유지 대상 (진행중 등)
  const keptSchedules = existingSemiPlans.filter(
    (s) => s.status !== "planned" && s.status !== "completed"
  );

  const summary = {
    total: previews.length + keptSchedules.length,
    new_count: previews.length,
    deleted_count: deletedSchedules.length,
    kept_count: keptSchedules.length,
    parent_count: plansResult.rowCount,
  };

  return { summary, previews, deletedSchedules, keptSchedules };
}

// ─── 반제품 계획 자동 생성 ───

export async function generateSemiSchedule(
  companyCode: string,
  planIds: number[],
  options: { considerStock?: boolean; excludeUsed?: boolean },
  createdBy: string
) {
  const pool = getPool();
  const client = await pool.connect();

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

    const placeholders = planIds.map((_, i) => `$${i + 2}`).join(", ");
    const plansResult = await client.query(
      `SELECT * FROM production_plan_mng
       WHERE company_code = $1 AND id IN (${placeholders})
         AND product_type = '완제품'`,
      [companyCode, ...planIds]
    );

    // 기존 planned 상태 반제품 삭제
    for (const plan of plansResult.rows) {
      await client.query(
        `DELETE FROM production_plan_mng
         WHERE company_code = $1 AND parent_plan_id = $2
           AND product_type = '반제품' AND status = 'planned'`,
        [companyCode, plan.id]
      );
    }

    const newSemiPlans: any[] = [];
    const todayStr = new Date().toISOString().split("T")[0].replace(/-/g, "");

    for (const plan of plansResult.rows) {
      const bomItems = await getBomChildItems(client, companyCode, plan.item_code);

      for (const bomItem of bomItems) {
        let requiredQty = (parseFloat(plan.plan_qty) || 0) * (parseFloat(bomItem.bom_qty) || 1);

        if (options.considerStock) {
          const stockResult = await client.query(
            `SELECT COALESCE(SUM(CAST(current_qty AS numeric)), 0) AS stock
             FROM inventory_stock
             WHERE company_code = $1 AND item_code = $2`,
            [companyCode, bomItem.child_item_code || bomItem.child_item_id]
          );
          const stock = parseFloat(stockResult.rows[0].stock) || 0;
          requiredQty = Math.max(requiredQty - stock, 0);
        }

        if (requiredQty <= 0) continue;

        const semiDueDate = plan.start_date;
        const semiEndDate = plan.start_date;
        const semiStartDate = new Date(plan.start_date);
        semiStartDate.setDate(semiStartDate.getDate() - (parseInt(plan.lead_time) || 1));

        // plan_no 생성 (PP-YYYYMMDD-SXXX 형식, S = 반제품)
        const planNoResult = await client.query(
          `SELECT COUNT(*) + 1 AS next_no
           FROM production_plan_mng
           WHERE company_code = $1 AND plan_no LIKE $2`,
          [companyCode, `PP-${todayStr}-S%`]
        );
        const nextNo = parseInt(planNoResult.rows[0].next_no, 10) || 1;
        const planNo = `PP-${todayStr}-S${String(nextNo).padStart(3, "0")}`;

        const insertResult = await client.query(
          `INSERT INTO production_plan_mng (
            company_code, plan_no, plan_date, item_code, item_name,
            product_type, plan_qty, start_date, end_date, due_date,
            status, parent_plan_id, created_by, created_date, updated_date
          ) VALUES (
            $1, $2, CURRENT_DATE, $3, $4,
            '반제품', $5, $6, $7, $8,
            'planned', $9, $10, NOW(), NOW()
          ) RETURNING *`,
          [
            companyCode, planNo,
            bomItem.child_item_code || bomItem.child_item_id,
            bomItem.child_item_name || bomItem.child_item_id,
            requiredQty,
            semiStartDate.toISOString().split("T")[0],
            typeof semiEndDate === "string" ? semiEndDate.split("T")[0] : new Date(semiEndDate).toISOString().split("T")[0],
            typeof semiDueDate === "string" ? semiDueDate.split("T")[0] : new Date(semiDueDate).toISOString().split("T")[0],
            plan.id,
            createdBy,
          ]
        );
        newSemiPlans.push(insertResult.rows[0]);
      }
    }

    await client.query("COMMIT");
    logger.info("반제품 계획 생성 완료", {
      companyCode,
      parentPlanIds: planIds,
      semiPlanCount: newSemiPlans.length,
    });
    return { count: newSemiPlans.length, schedules: newSemiPlans };
  } catch (error) {
    await client.query("ROLLBACK");
    logger.error("반제품 계획 생성 실패", { companyCode, error });
    throw error;
  } finally {
    client.release();
  }
}

// ─── 스케줄 분할 ───

export async function splitSchedule(
  companyCode: string,
  planId: number,
  splitQty: number,
  splitBy: string
) {
  const pool = getPool();
  const client = await pool.connect();

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

    const planResult = await client.query(
      `SELECT * FROM production_plan_mng WHERE id = $1 AND company_code = $2`,
      [planId, companyCode]
    );
    if (planResult.rowCount === 0) {
      throw new Error("생산계획을 찾을 수 없습니다");
    }

    const plan = planResult.rows[0];
    const originalQty = parseFloat(plan.plan_qty) || 0;

    if (splitQty >= originalQty || splitQty <= 0) {
      throw new Error("분할 수량은 0보다 크고 원래 수량보다 작아야 합니다");
    }

    // 원본 수량 감소
    await client.query(
      `UPDATE production_plan_mng SET plan_qty = $1, updated_date = NOW(), updated_by = $2
       WHERE id = $3 AND company_code = $4`,
      [originalQty - splitQty, splitBy, planId, companyCode]
    );

    // 분할된 새 계획 생성
    const planNoResult = await client.query(
      `SELECT COALESCE(MAX(CAST(REPLACE(plan_no, 'PP-', '') AS INTEGER)), 0) + 1 AS next_no
       FROM production_plan_mng WHERE company_code = $1`,
      [companyCode]
    );
    const planNo = `PP-${String(planNoResult.rows[0].next_no || 1).padStart(6, "0")}`;

    const insertResult = await client.query(
      `INSERT INTO production_plan_mng (
        company_code, plan_no, plan_date, item_code, item_name,
        product_type, plan_qty, start_date, end_date, due_date,
        status, priority, equipment_id, equipment_code, equipment_name,
        order_no, parent_plan_id, created_by, created_date, updated_date
      ) VALUES (
        $1, $2, CURRENT_DATE, $3, $4,
        $5, $6, $7, $8, $9,
        $10, $11, $12, $13, $14,
        $15, $16, $17, NOW(), NOW()
      ) RETURNING *`,
      [
        companyCode, planNo, plan.item_code, plan.item_name,
        plan.product_type, splitQty,
        plan.start_date, plan.end_date, plan.due_date,
        plan.status, plan.priority, plan.equipment_id, plan.equipment_code, plan.equipment_name,
        plan.order_no, plan.parent_plan_id,
        splitBy,
      ]
    );

    await client.query("COMMIT");
    logger.info("스케줄 분할 완료", { companyCode, planId, splitQty });
    return {
      original: { id: planId, plan_qty: originalQty - splitQty },
      split: insertResult.rows[0],
    };
  } catch (error) {
    await client.query("ROLLBACK");
    logger.error("스케줄 분할 실패", { companyCode, error });
    throw error;
  } finally {
    client.release();
  }
}
