import { type Request, type Response, Router } from "express";
import { getPool } from "../database/db";
import { authenticateToken } from "../middleware/authMiddleware";

const router = Router();

router.use(authenticateToken);

// ---- 검사 기준 조회 (item_inspection_info) ----
// GET /api/pop/inspection-result/info?itemCode=ITEM-001&inspectionType=입고검사
router.get("/info", async (req: Request, res: Response) => {
	const pool = getPool();
	const companyCode = (req as any).user?.companyCode;
	const { itemCode, itemId, inspectionType } = req.query;

	if (!companyCode) {
		return res.status(401).json({ success: false, message: "인증 정보 없음" });
	}

	const conditions: string[] = ["company_code = $1", "is_active = 'Y'"];
	const params: unknown[] = [companyCode];
	let idx = 2;

	if (itemCode) {
		conditions.push(`item_code = $${idx++}`);
		params.push(itemCode);
	}
	if (itemId) {
		conditions.push(`item_id = $${idx++}`);
		params.push(itemId);
	}
	if (inspectionType) {
		conditions.push(`inspection_type = $${idx++}`);
		params.push(inspectionType);
	}

	const sql = `
    SELECT id, item_id, item_code, item_name,
           inspection_type, inspection_item_name, inspection_standard,
           inspection_method, pass_criteria, is_required, sort_order, memo
    FROM item_inspection_info
    WHERE ${conditions.join(" AND ")}
    ORDER BY sort_order, inspection_item_name
  `;

	try {
		const result = await pool.query(sql, params);
		return res.json({ success: true, data: result.rows });
	} catch (err: any) {
		return res.status(500).json({ success: false, message: err.message });
	}
});

// ---- 검사 결과 조회 ----
// GET /api/pop/inspection-result?referenceId=xxx&referenceTable=yyy&screenId=zzz
router.get("/", async (req: Request, res: Response) => {
	const pool = getPool();
	const companyCode = (req as any).user?.companyCode;
	const { referenceId, referenceTable, screenId } = req.query;

	if (!companyCode) {
		return res.status(401).json({ success: false, message: "인증 정보 없음" });
	}

	const conditions: string[] = ["company_code = $1"];
	const params: unknown[] = [companyCode];
	let idx = 2;

	if (referenceId) {
		conditions.push(`reference_id = $${idx++}`);
		params.push(referenceId);
	}
	if (referenceTable) {
		conditions.push(`reference_table = $${idx++}`);
		params.push(referenceTable);
	}
	if (screenId) {
		conditions.push(`screen_id = $${idx++}`);
		params.push(screenId);
	}

	const sql = `
    SELECT *
    FROM inspection_result
    WHERE ${conditions.join(" AND ")}
    ORDER BY created_date DESC
  `;

	try {
		const result = await pool.query(sql, params);
		return res.json({ success: true, data: result.rows });
	} catch (err: any) {
		return res.status(500).json({ success: false, message: err.message });
	}
});

// ---- 검사번호 채번 (PC numberingRuleService 활용) ----
async function generateInspectionNumber(companyCode: string): Promise<string> {
	// PC 채번 서비스 동적 import (순환 참조 방지)
	const { numberingRuleService } = await import(
		"../services/numberingRuleService"
	);

	// 1) inspection_result_mng / inspection_number 채번 규칙 조회
	const rule = await numberingRuleService.getNumberingRuleByColumn(
		companyCode,
		"inspection_result_mng",
		"inspection_number",
	);

	if (rule && rule.ruleId) {
		// 2) PC API와 동일한 allocateCode 호출 → 실제 시퀀스 +1
		return await numberingRuleService.allocateCode(rule.ruleId, companyCode);
	}

	// fallback: 채번 규칙 없으면 단순 SELECT MAX
	const { getPool } = await import("../database/db");
	const pool = getPool();
	const year = new Date().getFullYear();
	const prefix = `QI-${year}-`;
	const result = await pool.query(
		`SELECT inspection_number FROM inspection_result_mng
     WHERE company_code = $1 AND inspection_number LIKE $2
     ORDER BY inspection_number DESC LIMIT 1`,
		[companyCode, `${prefix}%`],
	);
	let nextSeq = 1;
	if (result.rows.length > 0) {
		const lastNumber = result.rows[0].inspection_number;
		const match = lastNumber.match(/(\d+)$/);
		if (match) nextSeq = parseInt(match[1], 10) + 1;
	}
	return `${prefix}${String(nextSeq).padStart(4, "0")}`;
}

// ---- 검사번호 채번 전용 엔드포인트 (검사 모달에서 검사 완료 시) ----
// POST /api/pop/inspection-result/allocate-number
router.post("/allocate-number", async (req: Request, res: Response) => {
	const companyCode = (req as any).user?.companyCode;
	if (!companyCode) {
		return res.status(401).json({ success: false, message: "인증 정보 없음" });
	}
	try {
		const inspectionNumber = await generateInspectionNumber(companyCode);
		return res.json({ success: true, data: { inspectionNumber } });
	} catch (err: any) {
		return res.status(500).json({ success: false, message: err.message });
	}
});

// ---- 검사 결과 저장 (마스터 + 디테일 트랜잭션) ----
// POST /api/pop/inspection-result
router.post("/", async (req: Request, res: Response) => {
	const pool = getPool();
	const companyCode = (req as any).user?.companyCode;
	const writer = (req as any).user?.userId;

	if (!companyCode) {
		return res.status(401).json({ success: false, message: "인증 정보 없음" });
	}

	const {
		inspectionNumber: providedNumber, // 프론트에서 미리 채번한 번호 (있으면 재사용)
		referenceTable,
		referenceId,
		screenId,
		itemId,
		itemCode,
		itemName,
		inspectionType,
		items, // 검사 항목별 결과 배열
		overallJudgment,
		totalQty,
		goodQty,
		badQty,
		defectDescription,
		memo,
		inspector,
		supplierCode,
		supplierName,
		isCompleted,
	} = req.body;

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

	const client = await pool.connect();
	try {
		await client.query("BEGIN");

		// 1. 동일 referenceId + referenceTable 기존 마스터/디테일 삭제 (덮어쓰기)
		if (referenceId && referenceTable) {
			await client.query(
				`DELETE FROM inspection_result WHERE master_id IN (
          SELECT id FROM inspection_result_mng
          WHERE company_code = $1 AND reference_id = $2 AND reference_table = $3
        )`,
				[companyCode, referenceId, referenceTable],
			);
			await client.query(
				`DELETE FROM inspection_result_mng
         WHERE company_code = $1 AND reference_id = $2 AND reference_table = $3`,
				[companyCode, referenceId, referenceTable],
			);
		}

		// 2. 검사번호 (프론트에서 미리 받았으면 재사용, 없으면 새로 채번)
		const inspectionNumber =
			providedNumber || (await generateInspectionNumber(companyCode));

		// 3. 마스터 INSERT
		const completedFlag = isCompleted ? "Y" : "N";
		const completedDate = isCompleted ? new Date() : null;
		const masterResult = await client.query(
			`INSERT INTO inspection_result_mng (
        company_code, writer, inspection_number,
        reference_table, reference_id, screen_id,
        item_id, item_code, item_name,
        inspection_type, total_qty, good_qty, bad_qty,
        overall_judgment, defect_description, memo,
        inspector, inspection_date,
        supplier_code, supplier_name,
        is_completed, completed_date
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, NOW(), $18, $19, $20, $21
      ) RETURNING id, inspection_number`,
			[
				companyCode,
				writer,
				inspectionNumber,
				referenceTable || null,
				referenceId || null,
				screenId || null,
				itemId || null,
				itemCode || null,
				itemName || null,
				inspectionType || null,
				totalQty != null ? Number(totalQty) : null,
				goodQty != null ? Number(goodQty) : null,
				badQty != null ? Number(badQty) : null,
				overallJudgment || null,
				defectDescription || null,
				memo || null,
				inspector || writer,
				supplierCode || null,
				supplierName || null,
				completedFlag,
				completedDate,
			],
		);
		const masterId = masterResult.rows[0].id;

		// 4. 디테일 N건 INSERT
		const insertedDetailIds: string[] = [];
		for (const item of items) {
			const detailResult = await client.query(
				`INSERT INTO inspection_result (
          company_code, writer, master_id,
          reference_table, reference_id, screen_id,
          inspection_info_id, item_id, item_code, item_name,
          inspection_type, inspection_item_name, inspection_standard, pass_criteria, is_required,
          measured_value, judgment, overall_judgment, memo,
          is_completed, completed_date
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21
        ) RETURNING id`,
				[
					companyCode,
					writer,
					masterId,
					referenceTable || null,
					referenceId || null,
					screenId || null,
					item.inspectionInfoId || null,
					itemId || item.itemId || null,
					itemCode || item.itemCode || null,
					itemName || item.itemName || null,
					inspectionType || item.inspectionType || null,
					item.inspectionItemName || null,
					item.inspectionStandard || null,
					item.passCriteria || null,
					item.isRequired || "Y",
					item.measuredValue || null,
					item.judgment || null,
					overallJudgment || null,
					memo || null,
					completedFlag,
					completedDate,
				],
			);
			insertedDetailIds.push(detailResult.rows[0].id);
		}

		await client.query("COMMIT");
		return res.json({
			success: true,
			data: {
				masterId,
				inspectionNumber,
				detailIds: insertedDetailIds,
			},
		});
	} catch (err: any) {
		await client.query("ROLLBACK");
		return res.status(500).json({ success: false, message: err.message });
	} finally {
		client.release();
	}
});

export default router;
