// 발주 승인: 트랜잭션으로 재고 차감 + 상태 변경, 그 후 메일 발송
import { NextRequest, NextResponse } from "next/server";
import { pool, queryOne, queryRows } from "@/lib/db";
import { createObjectId } from "@/lib/utils";
import { requireMomoAdmin } from "@/lib/momo-guard";
import { buildStatementHtml, buildStatementXlsx } from "@/lib/excel-statement";
import { sendMail } from "@/lib/mailer";
import { getSupplierByBranch } from "@/lib/momo-branches";

export async function POST(req: NextRequest) {
  const g = await requireMomoAdmin();
  if (g instanceof NextResponse) return g;
  const adminId = g.user.objid || g.user.userId;

  const { objid } = await req.json();
  if (!objid) return NextResponse.json({ success: false, message: "objid 누락" }, { status: 400 });

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

    const orderRow = await client.query(
      `SELECT objid, order_no, customer_objid, status FROM momo_orders WHERE objid = $1 FOR UPDATE`,
      [objid]
    );
    if (orderRow.rowCount === 0) {
      await client.query("ROLLBACK");
      return NextResponse.json({ success: false, message: "발주를 찾을 수 없습니다." }, { status: 404 });
    }
    const order = orderRow.rows[0];
    if (order.status !== "REQUESTED") {
      await client.query("ROLLBACK");
      return NextResponse.json({ success: false, message: `현재 상태(${order.status})에서는 승인할 수 없습니다.` }, { status: 400 });
    }

    const itemsRes = await client.query(
      `SELECT objid, item_objid, qty FROM momo_order_items WHERE order_objid = $1 ORDER BY seq`,
      [objid]
    );

    // 출고 창고 결정 우선순위:
    //  1) 발주 거래처(user_info)의 default_wh_objid — 본사팀/김포팀 등 거래처별 기준 창고
    //  2) STOCK 타입 첫 번째 창고 (fallback)
    let whObjid: string | null = null;
    if (order.customer_objid) {
      const userWh = await client.query(
        `SELECT default_wh_objid FROM user_info WHERE user_id = $1 AND default_wh_objid IS NOT NULL`,
        [order.customer_objid]
      );
      if (userWh.rowCount && userWh.rows[0].default_wh_objid) {
        whObjid = String(userWh.rows[0].default_wh_objid);
      }
    }
    if (!whObjid) {
      const whRes = await client.query(
        `SELECT objid FROM momo_warehouses
           WHERE wh_type IN ('STOCK','HQ_STOCK','KIMPO_STOCK')
             AND COALESCE(is_del,'N') != 'Y'
         ORDER BY wh_code LIMIT 1`
      );
      if (whRes.rowCount === 0) {
        await client.query("ROLLBACK");
        return NextResponse.json({ success: false, message: "출고 가능한 창고가 없습니다." }, { status: 400 });
      }
      whObjid = whRes.rows[0].objid;
    }

    for (const ln of itemsRes.rows) {
      const stk = await client.query(
        `SELECT qty FROM momo_stocks WHERE wh_objid = $1 AND item_objid = $2 FOR UPDATE`,
        [whObjid, ln.item_objid]
      );
      const currentQty = stk.rowCount === 0 ? 0 : Number(stk.rows[0].qty);
      if (currentQty < Number(ln.qty)) {
        await client.query("ROLLBACK");
        return NextResponse.json(
          { success: false, message: `재고 부족: 품목 ${ln.item_objid} (현재고 ${currentQty}, 요청 ${ln.qty})` },
          { status: 400 }
        );
      }
      if (stk.rowCount === 0) {
        // 안전장치: 재고 row가 없으면 만들지 않고 에러 처리
        await client.query("ROLLBACK");
        return NextResponse.json({ success: false, message: "재고 정보가 없습니다." }, { status: 400 });
      }
      await client.query(
        `UPDATE momo_stocks SET qty = qty - $1, update_date = NOW()
         WHERE wh_objid = $2 AND item_objid = $3`,
        [ln.qty, whObjid, ln.item_objid]
      );
      await client.query(
        `INSERT INTO momo_stock_moves (objid, wh_objid, item_objid, move_type, qty, ref_type, ref_objid, regdate, regid)
         VALUES ($1, $2, $3, 'OUT', $4, 'ORDER', $5, NOW(), $6)`,
        [createObjectId(), whObjid, ln.item_objid, -Number(ln.qty), objid, adminId]
      );
    }

    await client.query(
      `UPDATE momo_orders
         SET status = 'APPROVED', approve_user = $2, approve_date = NOW(),
             update_date = NOW(), update_id = $2
       WHERE objid = $1`,
      [objid, adminId]
    );

    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("[order/approve]", err);
    return NextResponse.json({ success: false, message: "승인 중 오류가 발생했습니다." }, { status: 500 });
  } finally {
    client.release();
  }

  // ===== 메일 발송 (트랜잭션 외부) =====
  try {
    const order = await queryOne<Record<string, unknown>>(
      `SELECT O.objid, O.order_no, TO_CHAR(O.order_date,'YYYY-MM-DD') AS order_date,
              U.user_name AS company_name, U.email,
              U.ceo_name, U.biz_no, U.cell_phone AS phone, U.address,
              U.statement_branch,
              O.supplier_branch, O.supplier_name, O.supplier_ceo,
              O.supplier_bank_account, O.supplier_phone, O.supplier_email,
              O.supplier_biz_no, O.supplier_address,
              O.total_supply, O.total_vat, O.total_amount,
              O.total_taxfree, O.total_taxable
       FROM momo_orders O
       LEFT JOIN user_info U ON U.user_id = O.customer_objid
       WHERE O.objid = $1`,
      [objid]
    );
    const items = await queryRows<Record<string, unknown>>(
      `SELECT seq, item_name_snap, unit_price, qty, is_tax_free, supply_amount, vat_amount, total_amount,
              (SELECT unit FROM momo_items WHERE objid = OI.item_objid) AS unit
       FROM momo_order_items OI WHERE order_objid = $1 ORDER BY seq`,
      [objid]
    );
    if (order && order.email) {
      const stmt = {
        orderNo: String(order.order_no),
        orderDate: String(order.order_date),
        customer: {
          companyName: String(order.company_name),
          ceoName: order.ceo_name as string | undefined,
          bizNo: order.biz_no as string | undefined,
          phone: order.phone as string | undefined,
        },
        supplier: await (async () => {
          // snapshot 우선 (출고요청 시점), 없으면 사용자 기준 명세표 폴백
          if (order.supplier_bank_account) {
            return {
              companyName: String(order.supplier_name ?? "모모유통"),
              bankAccount: String(order.supplier_bank_account),
              phone:       String(order.supplier_phone ?? ""),
              email:       String(order.supplier_email ?? ""),
            };
          }
          const b = await getSupplierByBranch(order.statement_branch as string | undefined);
          return { companyName: b.NAME, bankAccount: b.BANK_ACCOUNT, phone: b.PHONE, email: b.EMAIL };
        })(),
        items: items.map((it) => ({
          seq: Number(it.seq),
          itemName: String(it.item_name_snap),
          unit: String(it.unit ?? "EA"),
          qty: Number(it.qty),
          unitPrice: Number(it.unit_price),
          supplyAmount: Number(it.supply_amount),
          vatAmount: Number(it.vat_amount),
          totalAmount: Number(it.total_amount),
          isTaxFree: it.is_tax_free === "Y",
        })),
        totals: {
          supply: Number(order.total_supply),
          vat: Number(order.total_vat),
          total: Number(order.total_amount),
          taxFree: Number(order.total_taxfree),
          taxable: Number(order.total_taxable),
        },
      };

      const html = buildStatementHtml(stmt);
      const xlsx = buildStatementXlsx(stmt);
      const mailRes = await sendMail({
        to: String(order.email),
        subject: `[모모유통] 발주 ${stmt.orderNo} 승인되었습니다`,
        html,
        attachments: [{
          filename: `거래명세표_${stmt.orderNo}.xlsx`,
          content: xlsx,
          contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        }],
        refType: "ORDER",
        refObjid: objid,
      });
      return NextResponse.json({ success: true, mailSent: mailRes.ok, mailError: mailRes.error });
    }
  } catch (err) {
    console.error("[order/approve mail]", err);
    return NextResponse.json({ success: true, mailSent: false, mailError: "메일 발송 실패 (승인은 완료)" });
  }

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