/**
 * 외주출고 컨트롤러
 *
 * 이전 공정이 완료되고 다음 공정이 외주 공정이면
 * 자동으로 외주출고 대상 목록에 표시 → 출고 처리
 *
 * 출고 데이터는 기존 outbound_mng 테이블 재사용
 * (outbound_type='외주출고', source_type='work_order_process')
 */

import type { Response } from "express";
import { getPool } from "../database/db";
import type { AuthenticatedRequest } from "../types/auth";
import { adjustInventory } from "../utils/inventoryUtils";
import { logger } from "../utils/logger";

/**
 * 외주출고 대상 자동 조회
 * GET /api/outsourcing-outbound/candidates
 *
 * 이전 공정 완료 + 다음 공정이 외주 공정인 건 자동 표시
 */
export async function getCandidates(req: AuthenticatedRequest, res: Response) {
	try {
		const companyCode = req.user!.companyCode;
		const { keyword } = req.query;

		const pool = getPool();

		let keywordCondition = "";
		const params: any[] = [];
		let paramIdx = 1;

		if (companyCode !== "*") {
			params.push(companyCode);
			paramIdx++;
		}

		if (keyword) {
			keywordCondition = `AND (
				wi.instruction_no ILIKE $${paramIdx}
				OR wi.item_name ILIKE $${paramIdx}
				OR wi.item_code ILIKE $${paramIdx}
				OR sm.subcontractor_name ILIKE $${paramIdx}
			)`;
			params.push(`%${keyword}%`);
			paramIdx++;
		}

		const companyFilter = companyCode !== "*"
			? `wop_done.company_code = $1`
			: `1=1`;

		const query = `
			SELECT
				wop_done.id AS completed_process_id,
				wop_done.wo_id,
				wop_done.seq_no AS completed_seq_no,
				wop_done.process_code AS completed_process_code,
				COALESCE(pm_done.process_name, wop_done.process_name, wop_done.process_code) AS completed_process_name,
				COALESCE(CAST(NULLIF(wop_done.good_qty, '') AS numeric), 0) AS good_qty,
				wop_next.id AS next_process_id,
				wop_next.seq_no AS next_seq_no,
				wop_next.process_code AS next_process_code,
				COALESCE(pm_next.process_name, wop_next.process_name, wop_next.process_code) AS next_process_name,
				wop_next.status AS next_status,
				wi.instruction_no,
				wi.item_code,
				wi.item_name,
				ii.size AS spec,
				ii.material,
				ii.inventory_unit AS unit,
				sm.id AS subcontractor_id,
				sm.subcontractor_code,
				sm.subcontractor_name
			FROM work_order_process wop_done
			INNER JOIN work_instruction wi
				ON wop_done.wo_id = wi.id
				AND wop_done.company_code = wi.company_code
			-- 다음 공정 (바로 다음 seq_no)
			INNER JOIN LATERAL (
				SELECT wop2.*
				FROM work_order_process wop2
				WHERE wop2.wo_id = wop_done.wo_id
					AND wop2.company_code = wop_done.company_code
					AND wop2.parent_process_id IS NULL
					AND CAST(wop2.seq_no AS int) > CAST(wop_done.seq_no AS int)
				ORDER BY CAST(wop2.seq_no AS int)
				LIMIT 1
			) wop_next ON TRUE
			-- 다음 공정이 외주인지 확인
			INNER JOIN item_routing_subcontractor irs
				ON irs.routing_detail_id = wop_next.routing_detail_id
			INNER JOIN subcontractor_mng sm
				ON irs.subcontractor_id = sm.id
			LEFT JOIN item_info ii
				ON wi.item_code = ii.item_number AND wi.company_code = ii.company_code
			LEFT JOIN process_mng pm_done
				ON wop_done.process_code = pm_done.process_code AND wop_done.company_code = pm_done.company_code
			LEFT JOIN process_mng pm_next
				ON wop_next.process_code = pm_next.process_code AND wop_next.company_code = pm_next.company_code
			WHERE ${companyFilter}
				AND wop_done.parent_process_id IS NULL
				AND wop_done.status IN ('completed', 'acceptable')
				AND COALESCE(CAST(NULLIF(wop_done.good_qty, '') AS numeric), 0) > 0
				-- 아직 외주출고 등록 안 된 건만
				AND NOT EXISTS (
					SELECT 1 FROM outbound_mng om
					WHERE om.outbound_type = '외주출고'
						AND om.source_type = 'work_order_process'
						AND om.source_id = wop_done.id
						${companyCode !== "*" ? "AND om.company_code = $1" : ""}
				)
				${keywordCondition}
			ORDER BY wi.instruction_no, CAST(wop_done.seq_no AS int)
		`;

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

		logger.info("외주출고 대상 조회", { companyCode, count: result.rowCount });
		return res.json({ success: true, data: result.rows });
	} catch (error: any) {
		logger.error("외주출고 대상 조회 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	}
}

/**
 * 외주출고 목록 조회
 * GET /api/outsourcing-outbound/list
 */
export async function getList(req: AuthenticatedRequest, res: Response) {
	try {
		const companyCode = req.user!.companyCode;
		const { outbound_status, search_keyword, date_from, date_to } = req.query;

		const conditions: string[] = ["om.outbound_type = '외주출고'"];
		const params: any[] = [];
		let paramIdx = 1;

		if (companyCode !== "*") {
			conditions.push(`om.company_code = $${paramIdx}`);
			params.push(companyCode);
			paramIdx++;
		}

		if (outbound_status && outbound_status !== "all") {
			conditions.push(`om.outbound_status = $${paramIdx}`);
			params.push(outbound_status);
			paramIdx++;
		}

		if (search_keyword) {
			conditions.push(`(
				om.outbound_number ILIKE $${paramIdx}
				OR om.item_name ILIKE $${paramIdx}
				OR om.item_code ILIKE $${paramIdx}
				OR om.customer_name ILIKE $${paramIdx}
				OR om.reference_number ILIKE $${paramIdx}
			)`);
			params.push(`%${search_keyword}%`);
			paramIdx++;
		}

		if (date_from) {
			conditions.push(`om.outbound_date >= $${paramIdx}`);
			params.push(date_from);
			paramIdx++;
		}
		if (date_to) {
			conditions.push(`om.outbound_date <= $${paramIdx}`);
			params.push(date_to);
			paramIdx++;
		}

		const whereClause = `WHERE ${conditions.join(" AND ")}`;

		const pool = getPool();
		const result = await pool.query(
			`SELECT om.*, wh.warehouse_name
			 FROM outbound_mng om
			 LEFT JOIN warehouse_info wh ON om.warehouse_code = wh.warehouse_code AND om.company_code = wh.company_code
			 ${whereClause}
			 ORDER BY om.created_date DESC`,
			params,
		);

		logger.info("외주출고 목록 조회", { companyCode, count: result.rowCount });
		return res.json({ success: true, data: result.rows });
	} catch (error: any) {
		logger.error("외주출고 목록 조회 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	}
}

/**
 * 외주출고 등록
 * POST /api/outsourcing-outbound
 */
export async function create(req: AuthenticatedRequest, res: Response) {
	const pool = getPool();
	const client = await pool.connect();

	try {
		const companyCode = req.user!.companyCode;
		const userId = req.user!.userId;
		const {
			items,
			outbound_number,
			outbound_date,
			warehouse_code,
			location_code,
			manager_id,
			memo,
		} = req.body;

		if (!items || !Array.isArray(items) || items.length === 0) {
			return res.status(400).json({ success: false, message: "출고 품목이 없습니다." });
		}

		await client.query("BEGIN");

		const insertedRows: any[] = [];

		for (const item of items) {
			const result = await client.query(
				`INSERT INTO outbound_mng (
					id, company_code, outbound_number, outbound_type, outbound_date,
					reference_number, customer_code, customer_name,
					item_code, item_name, specification, material, unit,
					outbound_qty, unit_price, total_amount,
					warehouse_code, location_code,
					outbound_status, manager_id, memo,
					source_type, source_id,
					created_date, created_by, writer, status
				) VALUES (
					gen_random_uuid()::text, $1, $2, '외주출고', $3,
					$4, $5, $6,
					$7, $8, $9, $10, $11,
					$12, 0, 0,
					$13, $14,
					'출고완료', $15, $16,
					'work_order_process', $17,
					NOW(), $18, $18, '출고'
				) RETURNING *`,
				[
					companyCode,
					outbound_number || item.outbound_number,
					outbound_date || item.outbound_date,
					item.reference_number || null,       // 작업지시번호
					item.subcontractor_code || null,      // 외주사코드 → customer_code
					item.subcontractor_name || null,      // 외주사명 → customer_name
					item.item_code || null,
					item.item_name || null,
					item.spec || null,
					item.material || null,
					item.unit || null,
					item.outbound_qty || 0,
					warehouse_code || item.warehouse_code || null,
					location_code || item.location_code || null,
					manager_id || item.manager_id || null,
					memo || item.memo || null,
					item.completed_process_id || null,    // source_id = 완료된 공정 ID
					userId,
				],
			);

			insertedRows.push(result.rows[0]);

			// 재고 차감
			const itemCode = item.item_code || null;
			const whCode = warehouse_code || item.warehouse_code || null;
			const locCode = location_code || item.location_code || null;
			const outQty = Number(item.outbound_qty) || 0;

			if (itemCode && outQty > 0 && whCode) {
				await adjustInventory(client, {
					companyCode,
					userId,
					itemCode,
					whCode,
					locCode,
					delta: -outQty,
					transactionType: "외주출고",
					remark: `외주출고 (${outbound_number || ""}) → ${item.subcontractor_name || ""}`,
				});
			}
		}

		await client.query("COMMIT");

		logger.info("외주출고 등록 완료", {
			companyCode,
			userId,
			count: insertedRows.length,
			outbound_number,
		});

		return res.json({
			success: true,
			data: insertedRows,
			message: `${insertedRows.length}건 외주출고 등록 완료`,
		});
	} catch (error: any) {
		await client.query("ROLLBACK");
		logger.error("외주출고 등록 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	} finally {
		client.release();
	}
}

/**
 * 외주출고 수정
 * PUT /api/outsourcing-outbound/:id
 */
export async function update(req: AuthenticatedRequest, res: Response) {
	try {
		const companyCode = req.user!.companyCode;
		const userId = req.user!.userId;
		const { id } = req.params;
		const { outbound_date, outbound_qty, warehouse_code, location_code, memo } = req.body;

		const pool = getPool();
		const companyCondition = companyCode === "*" ? "" : `AND company_code = '${companyCode}'`;

		const result = await pool.query(
			`UPDATE outbound_mng SET
				outbound_date = COALESCE($1::date, outbound_date),
				outbound_qty = COALESCE($2::numeric, outbound_qty),
				warehouse_code = COALESCE($3, warehouse_code),
				location_code = COALESCE($4, location_code),
				memo = COALESCE($5, memo),
				updated_date = NOW(),
				updated_by = $6
			WHERE id = $7 ${companyCondition}
			RETURNING *`,
			[outbound_date, outbound_qty, warehouse_code, location_code, memo, userId, id],
		);

		if (result.rowCount === 0) {
			return res.status(404).json({ success: false, message: "데이터를 찾을 수 없습니다." });
		}

		return res.json({ success: true, data: result.rows[0] });
	} catch (error: any) {
		logger.error("외주출고 수정 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	}
}

/**
 * 외주출고 삭제
 * DELETE /api/outsourcing-outbound/:id
 */
export async function deleteOutbound(req: AuthenticatedRequest, res: Response) {
	const pool = getPool();
	const client = await pool.connect();

	try {
		const companyCode = req.user!.companyCode;
		const userId = req.user!.userId;
		const { id } = req.params;

		await client.query("BEGIN");

		// 삭제 전 데이터 조회 (재고 복구용)
		const companyCondition = companyCode === "*" ? "" : `AND company_code = $2`;
		const queryParams = companyCode === "*" ? [id] : [id, companyCode];

		const oldRes = await client.query(
			`SELECT * FROM outbound_mng WHERE id = $1 ${companyCondition}`,
			queryParams,
		);

		if (oldRes.rowCount === 0) {
			await client.query("ROLLBACK");
			return res.status(404).json({ success: false, message: "데이터를 찾을 수 없습니다." });
		}

		const old = oldRes.rows[0];
		const itemCode = old.item_code || null;
		const whCode = old.warehouse_code || null;
		const locCode = old.location_code || null;
		const oldQty = Number(old.outbound_qty) || 0;

		// 재고 복구
		if (itemCode && oldQty > 0 && whCode) {
			await adjustInventory(client, {
				companyCode: old.company_code,
				userId,
				itemCode,
				whCode,
				locCode,
				delta: +oldQty,
				transactionType: "외주출고취소",
				remark: `외주출고 삭제 (${old.outbound_number || ""})`,
			});
		}

		// 삭제
		await client.query(
			`DELETE FROM outbound_mng WHERE id = $1 ${companyCondition}`,
			queryParams,
		);

		await client.query("COMMIT");

		logger.info("외주출고 삭제", { companyCode, id });
		return res.json({ success: true, message: "삭제되었습니다." });
	} catch (error: any) {
		await client.query("ROLLBACK");
		logger.error("외주출고 삭제 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	} finally {
		client.release();
	}
}

/**
 * 외주출고번호 자동생성
 * GET /api/outsourcing-outbound/generate-number
 */
export async function generateNumber(req: AuthenticatedRequest, res: Response) {
	try {
		const companyCode = req.user!.companyCode;
		const pool = getPool();
		const yyyy = new Date().getFullYear();
		const prefix = `OSOUT-${yyyy}-`;

		const result = await pool.query(
			`SELECT outbound_number FROM outbound_mng
			 WHERE company_code = $1 AND outbound_number LIKE $2
			 ORDER BY outbound_number DESC LIMIT 1`,
			[companyCode, `${prefix}%`],
		);

		let seq = 1;
		if (result.rows.length > 0) {
			const lastNo = result.rows[0].outbound_number;
			const lastSeq = parseInt(lastNo.replace(prefix, ""), 10);
			if (!isNaN(lastSeq)) seq = lastSeq + 1;
		}

		const newNumber = `${prefix}${String(seq).padStart(4, "0")}`;
		return res.json({ success: true, data: newNumber });
	} catch (error: any) {
		logger.error("외주출고번호 생성 실패", { error: error.message });
		return res.status(500).json({ success: false, message: error.message });
	}
}

/**
 * 창고 목록 (outbound 컨트롤러와 공유)
 */
export async function getWarehouses(req: AuthenticatedRequest, res: Response) {
	try {
		const companyCode = req.user!.companyCode;
		const pool = getPool();

		const condition = companyCode === "*" ? "" : `WHERE company_code = $1`;
		const params = companyCode === "*" ? [] : [companyCode];

		const result = await pool.query(
			`SELECT warehouse_code, warehouse_name, warehouse_type FROM warehouse_info ${condition} ORDER BY warehouse_name`,
			params,
		);

		return res.json({ success: true, data: result.rows });
	} catch (error: any) {
		return res.status(500).json({ success: false, message: error.message });
	}
}
