import { NextRequest, NextResponse } from "next/server";
import sql from "mssql";
import { getPool } from "@/lib/db";
import type { QueryParams, QueryResult, WarehouseRow } from "@/types/warehouse";

const WORKSHOP_MAP: Record<string, string> = {
  "1": "一车间",
  "2": "二车间",
  "3": "温度计车间",
  "4": "四车间",
  "5": "五车间",
};

// Map frontend sort keys to SQL column aliases in the subquery
const SORT_MAP: Record<string, string> = {
  paichan: "车间号",
  zongpai: "总排号",
  workshop: "车间",
  model: "产品型号",
  range: "量程",
  qty: "数量",
  boxNo: "box_no",
  boxQty: "item_quantity",
  workOrder: "工令号",
  shelf: "location_code",
  handler: "经办人",
  orderNo: "订单号",
  inbound: "入库日期",
};

function formatRows(rows: sql.IRecordSet<any>): WarehouseRow[] {
  return rows.map((r) => {
    const inboundDate = r.入库日期;
    const inbound = inboundDate
      ? new Date(inboundDate).toISOString().slice(0, 10).replace(/-/g, "/")
      : "";
    return {
      paichan: r.车间号 || "",
      zongpai: r.总排号 || "",
      workshop: WORKSHOP_MAP[(r.车间 || "").trim()] || (r.车间 || "").trim(),
      model: r.产品型号 || "",
      range: r.量程 || "",
      qty: r.数量 ?? null,
      boxNo: r.box_no != null ? String(r.box_no) : "",
      boxQty: r.item_quantity ?? null,
      workOrder: r.工令号 || "",
      shelf: r.location_code || "",
      handler: r.经办人 || "",
      orderNo: r.订单号 || "",
      inbound,
      status: inboundDate ? ("IN" as const) : ("WAIT" as const),
    };
  });
}

export async function GET(request: NextRequest) {
  try {
    const params: QueryParams = {
      search: request.nextUrl.searchParams.get("search") || undefined,
      workshop: request.nextUrl.searchParams.get("workshop") || undefined,
      handler: request.nextUrl.searchParams.get("handler") || undefined,
      status:
        (request.nextUrl.searchParams.get("status") as QueryParams["status"]) ||
        undefined,
      dateFrom: request.nextUrl.searchParams.get("dateFrom") || undefined,
      dateTo: request.nextUrl.searchParams.get("dateTo") || undefined,
      sortKey: request.nextUrl.searchParams.get("sortKey") || undefined,
      sortDir:
        (request.nextUrl.searchParams.get("sortDir") as QueryParams["sortDir"]) ||
        undefined,
      page: Number(request.nextUrl.searchParams.get("page")) || 1,
      pageSize: Number(request.nextUrl.searchParams.get("pageSize")) || 20,
    };

    const pool = await getPool();

    // --- Base CTE: UNION ALL of both contract tables ---
    const baseCTE = `
      WITH contract AS (
        SELECT 总排号, 车间号, 车间, 产品型号, 量程, 数量, 工令号, 经办人, 订单号
        FROM productionContractData.[26年压力表合同数据]
        UNION ALL
        SELECT 总排号, 车间号, 车间, 产品型号, 量程, 数量, 工令号, 经办人, 订单号
        FROM productionContractData.[26年温度计合同数据]
      )
    `;

    // --- Main FROM with LEFT JOINs ---
    const mainFrom = `
      FROM contract c
      LEFT JOIN CargoTrace.finished_goods_box_item i ON c.总排号 = i.zongpai_no
      LEFT JOIN CargoTrace.finished_goods_box b ON i.box_id = b.id
      LEFT JOIN CargoTrace.finished_goods_location l ON c.总排号 = l.zongpai_no
      LEFT JOIN productWarehousing.成品入库记录_YEAR2026 wh ON c.总排号 = wh.总排号
    `;

    // --- Build WHERE clause ---
    const conditions: string[] = [];

    if (params.workshop && params.workshop !== "ALL") {
      conditions.push("c.车间 = @workshop");
    }
    if (params.handler && params.handler !== "ALL") {
      conditions.push("c.经办人 = @handler");
    }
    if (params.status === "IN") {
      conditions.push("wh.日期 IS NOT NULL");
    } else if (params.status === "WAIT") {
      conditions.push("wh.日期 IS NULL");
    }
    if (params.dateFrom) {
      conditions.push("wh.日期 >= @dateFrom");
    }
    if (params.dateTo) {
      conditions.push("wh.日期 < DATEADD(DAY, 1, @dateTo)");
    }
    if (params.search) {
      conditions.push(`(
        c.总排号 LIKE @kw OR c.车间号 LIKE @kw OR c.产品型号 LIKE @kw
        OR c.量程 LIKE @kw OR ISNULL(CAST(b.box_no AS NVARCHAR(50)), '') LIKE @kw
        OR c.工令号 LIKE @kw OR ISNULL(l.location_code, '') LIKE @kw
        OR c.经办人 LIKE @kw OR c.订单号 LIKE @kw
      )`);
    }

    const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

    // --- Helper to add params to a request ---
    function addParams(req: sql.Request) {
      if (params.workshop && params.workshop !== "ALL")
        req.input("workshop", sql.NVarChar, params.workshop);
      if (params.handler && params.handler !== "ALL")
        req.input("handler", sql.NVarChar, params.handler);
      if (params.dateFrom)
        req.input("dateFrom", sql.DateTime2, new Date(params.dateFrom));
      if (params.dateTo)
        req.input("dateTo", sql.DateTime2, new Date(params.dateTo));
      if (params.search)
        req.input("kw", sql.NVarChar, `%${params.search}%`);
    }

    // --- Count query ---
    const countReq = pool.request();
    addParams(countReq);
    const countSQL = `${baseCTE} SELECT COUNT(*) AS total FROM (SELECT c.* ${mainFrom} ${where}) sub`;
    const countResult = await countReq.query(countSQL);
    const total = countResult.recordset[0].total;

    // --- Data query with sorting & pagination ---
    const userSortCol = SORT_MAP[params.sortKey || ""];
    const userSortDir = params.sortDir === "desc" ? "DESC" : "ASC";
    // Default multi-column sort: 排产号(车间号) ASC, 总排号 ASC, 箱号 ASC (FR-3)
    const orderBy = userSortCol
      ? `${userSortCol} ${userSortDir}`
      : "c.车间号 ASC, c.总排号 ASC, b.box_no ASC";
    const page = Math.max(1, params.page || 1);
    const pageSize = params.pageSize || 20;
    const offset = (page - 1) * pageSize;

    const dataReq = pool.request();
    addParams(dataReq);
    dataReq.input("offset", sql.Int, offset);
    dataReq.input("pageSize", sql.Int, pageSize);

    const dataSQL = `
      ${baseCTE}
      SELECT * FROM (
        SELECT
          c.车间号, c.总排号, c.车间, c.产品型号, c.量程, c.数量,
          ISNULL(CAST(b.box_no AS NVARCHAR(50)), '') AS box_no,
          ISNULL(CAST(i.quantity AS NVARCHAR(50)), '') AS item_quantity,
          ISNULL(c.工令号, '') AS 工令号,
          ISNULL(l.location_code, '') AS location_code,
          ISNULL(c.经办人, '') AS 经办人,
          ISNULL(c.订单号, '') AS 订单号,
          wh.日期 AS 入库日期,
          ROW_NUMBER() OVER (ORDER BY ${orderBy}) AS _rownum
        ${mainFrom}
        ${where}
      ) numbered
      WHERE _rownum > @offset AND _rownum <= @offset + @pageSize
      ORDER BY _rownum
    `;

    const dataResult = await dataReq.query(dataSQL);
    const rows = formatRows(dataResult.recordset);

    const result: QueryResult = {
      rows,
      total,
      page,
      pageSize,
      totalPages: Math.max(1, Math.ceil(total / pageSize)),
    };

    return NextResponse.json(result);
  } catch (error) {
    console.error("Warehouse query failed:", error);
    return NextResponse.json(
      { error: "Query failed", detail: String(error) },
      { status: 500 }
    );
  }
}