// 거래명세표 다운로드 (xlsx)
import { NextRequest, NextResponse } from "next/server";
import { queryOne, queryRows } from "@/lib/db";
import { requireMomoUser } from "@/lib/momo-guard";
import { buildStatementXlsx } from "@/lib/excel-statement";
import { getSupplierByBranch } from "@/lib/momo-branches";

export async function GET(req: NextRequest, ctx: { params: Promise<{ id: string }> }) {
  const r = await requireMomoUser();
  if (r instanceof NextResponse) return r;

  const { id } = await ctx.params;
  const order = await queryOne<Record<string, unknown>>(
    `SELECT O.objid, O.order_no, TO_CHAR(O.order_date,'YYYY-MM-DD') AS order_date,
            O.customer_objid,
            U.user_name        AS company_name,
            U.ceo_name         AS ceo_name,
            U.biz_no           AS biz_no,
            U.cell_phone       AS phone,
            U.address          AS address,
            U.email            AS email,
            U.statement_branch AS statement_branch,
            O.supplier_branch       AS supplier_branch,
            O.supplier_name         AS supplier_name,
            O.supplier_ceo          AS supplier_ceo,
            O.supplier_bank_account AS supplier_bank_account,
            O.supplier_phone        AS supplier_phone,
            O.supplier_email        AS supplier_email,
            O.supplier_biz_no       AS supplier_biz_no,
            O.supplier_address      AS supplier_address,
            O.total_supply, O.total_vat, O.total_amount,
            O.total_taxfree, O.total_taxable,
            COALESCE(O.paid_amount, 0) AS paid_amount
     FROM momo_orders O LEFT JOIN user_info U ON U.user_id = O.customer_objid
     WHERE O.objid = $1`,
    [id]
  );
  if (!order) return NextResponse.json({ success: false, message: "찾을 수 없습니다." }, { status: 404 });
  const userOwn = r.user.objid ?? r.user.userId;
  if (r.user.role === "USER" && order.customer_objid !== userOwn && order.customer_objid !== r.user.userId) {
    return NextResponse.json({ success: false, message: "권한 없음" }, { status: 403 });
  }

  const items = await queryRows<Record<string, unknown>>(
    `SELECT seq, item_name_snap, unit_price, qty, is_tax_free, supply_amount, vat_amount, total_amount, remark,
            COALESCE(kind, 'ITEM') AS kind, extra_label,
            (SELECT unit FROM momo_items WHERE objid = OI.item_objid) AS unit
     FROM momo_order_items OI WHERE order_objid = $1
     ORDER BY
       CASE COALESCE(kind,'ITEM')
         WHEN 'DELIVERY' THEN 0
         WHEN 'CHARTER'  THEN 1
         ELSE 2
       END,
       seq ASC`,
    [id]
  );

  const buf = buildStatementXlsx({
    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, idx) => {
      const isExtra = it.kind === "DELIVERY" || it.kind === "CHARTER";
      return {
        seq: idx + 1,
        itemName: isExtra
          ? (`[${it.kind === "DELIVERY" ? "택배" : "용차"}] ${String(it.extra_label || it.item_name_snap || "")}`)
          : String(it.item_name_snap || ""),
        unit: isExtra ? "" : 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",
        remark: (it.remark as string) ?? "",
      };
    }),
    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),
      unpaid: Math.max(0, Number(order.total_amount) - Number(order.paid_amount)),
    },
  });

  return new NextResponse(new Uint8Array(buf), {
    headers: {
      "Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      "Content-Disposition": `attachment; filename="거래명세표_${order.order_no}.xlsx"`,
    },
  });
}
