// lib/rfq/service.ts
'use server'

import { unstable_cache, unstable_noStore } from "next/cache";
import db from "@/db/db";
import { RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView } from "@/db/schema";
import {sql, and, desc, asc, like, ilike, or, eq, SQL, count, gte, lte, isNotNull, ne, inArray } from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
import { GetRfqLastAttachmentsSchema, GetRfqsSchema } from "./validations";

export async function getRfqs(input: GetRfqsSchema) {
  unstable_noStore();

  try {
    const offset = (input.page - 1) * input.perPage;

    // 1. RFQ 타입별 필터링
    let typeFilter: SQL<unknown> | undefined = undefined;
    if (input.rfqCategory) {
      switch (input.rfqCategory) {
        case "general":
          // 일반견적: rfqType이 있는 경우
          typeFilter = and(
            isNotNull(rfqsLastView.rfqType),
            ne(rfqsLastView.rfqType, '')
          );
          break;
        case "itb":
          // ITB: projectCompany가 있는 경우
          typeFilter = and(
            isNotNull(rfqsLastView.projectCompany),
            ne(rfqsLastView.projectCompany, '')
          );
          break;
        case "rfq":
          // RFQ: prNumber가 있는 경우  
          typeFilter = and(
            isNotNull(rfqsLastView.prNumber),
            ne(rfqsLastView.prNumber, '')
          );
          break;
      }
    }

    // 2. 고급 필터 처리
    let advancedWhere: SQL<unknown> | undefined = undefined;

    if (input.filters && Array.isArray(input.filters) && input.filters.length > 0) {
      console.log("필터 적용:", input.filters.map(f => `${f.id} ${f.operator} ${f.value}`));

      try {
        advancedWhere = filterColumns({
          table: rfqsLastView,
          filters: input.filters,
          joinOperator: input.joinOperator || 'and',
        });

        console.log("필터 조건 생성 완료");
      } catch (error) {
        console.error("필터 조건 생성 오류:", error);
        advancedWhere = undefined;
      }
    }

    // 3. 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined;
    if (input.search) {
      const s = `%${input.search}%`;

      const searchConditions: SQL<unknown>[] = [
        ilike(rfqsLastView.rfqCode, s),
        ilike(rfqsLastView.itemCode, s),
        ilike(rfqsLastView.itemName, s),
        ilike(rfqsLastView.packageNo, s),
        ilike(rfqsLastView.packageName, s),
        ilike(rfqsLastView.picName, s),
        ilike(rfqsLastView.engPicName, s),
        ilike(rfqsLastView.projectCode, s),
        ilike(rfqsLastView.projectName, s),
        ilike(rfqsLastView.rfqTitle, s),
        ilike(rfqsLastView.prNumber, s),
      ].filter(Boolean);

      if (searchConditions.length > 0) {
        globalWhere = or(...searchConditions);
      }
    }

    // 4. 최종 WHERE 조건 결합
    const whereConditions: SQL<unknown>[] = [];
    if (typeFilter) whereConditions.push(typeFilter);
    if (advancedWhere) whereConditions.push(advancedWhere);
    if (globalWhere) whereConditions.push(globalWhere);

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // 5. 전체 데이터 수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(rfqsLastView)
      .where(finalWhere);

    const total = totalResult[0]?.count || 0;

    if (total === 0) {
      return { data: [], pageCount: 0, total: 0 };
    }

    console.log("총 데이터 수:", total);

    // 6. 정렬 및 페이징 처리
    const orderByColumns = input.sort.map((sort) => {
      const column = sort.id as keyof typeof rfqsLastView.$inferSelect;
      return sort.desc
        ? desc(rfqsLastView[column])
        : asc(rfqsLastView[column]);
    });

    if (orderByColumns.length === 0) {
      orderByColumns.push(desc(rfqsLastView.createdAt));
    }

    const rfqData = await db
      .select()
      .from(rfqsLastView)
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset);

    const pageCount = Math.ceil(total / input.perPage);

    console.log("반환 데이터 수:", rfqData.length);

    return { data: rfqData, pageCount, total };
  } catch (err) {
    console.error("getRfqs 오류:", err);
    return { data: [], pageCount: 0, total: 0 };
  }
}

const getRfqById = async (id: number): Promise<RfqsLastView | null> => {
  // 1) RFQ 단건 조회
  const rfqsRes = await db
    .select()
    .from(rfqsLastView)
    .where(eq(rfqsLastView.id, id))
    .limit(1);

  if (rfqsRes.length === 0) return null;
  const rfqRow = rfqsRes[0];

  // 3) RfqWithItems 형태로 반환
  const result: RfqsLastView = {
    ...rfqRow,

  };

  return result;
};


export const findRfqLastById = async (id: number): Promise<RfqsLastView | null> => {
  try {

    const rfq = await getRfqById(id);

    return rfq;
  } catch (error) {
    throw new Error('Failed to fetch user');
  }
};


export async function getRfqLastAttachments(
  input: GetRfqLastAttachmentsSchema,
  rfqId: number
) {
  try {
    const offset = (input.page - 1) * input.perPage

    // Advanced Filter 처리 (메인 테이블 기준)
    const advancedWhere = filterColumns({
      table: rfqLastAttachments,
      filters: input.filters,
      joinOperator: input.joinOperator,
    })

    // 전역 검색 (첨부파일 + 리비전 파일명 검색)
    let globalWhere
    if (input.search) {
      const s = `%${input.search}%`
      globalWhere = or(
        ilike(rfqLastAttachments.serialNo, s),
        ilike(rfqLastAttachments.description, s),
        ilike(rfqLastAttachments.currentRevision, s),
        ilike(rfqLastAttachmentRevisions.fileName, s),
        ilike(rfqLastAttachmentRevisions.originalFileName, s)
      )
    }

    // 기본 필터
    let basicWhere
    if (input.attachmentType.length > 0 || input.fileType.length > 0) {
      basicWhere = and(
        input.attachmentType.length > 0
          ? inArray(rfqLastAttachments.attachmentType, input.attachmentType)
          : undefined,
        input.fileType.length > 0
          ? inArray(rfqLastAttachmentRevisions.fileType, input.fileType)
          : undefined
      )
    }

    // 최종 WHERE 절
    const finalWhere = and(
      eq(rfqLastAttachments.rfqId, rfqId), // RFQ ID 필수 조건
      advancedWhere,
      globalWhere,
      basicWhere
    )

    // 정렬 (메인 테이블 기준)
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) =>
        item.desc ? desc(rfqLastAttachments[item.id as keyof typeof rfqLastAttachments]) : asc(rfqLastAttachments[item.id as keyof typeof rfqLastAttachments])
      )
      : [desc(rfqLastAttachments.createdAt)]

    // 트랜잭션으로 데이터 조회
    const { data, total } = await db.transaction(async (tx) => {
      // 메인 데이터 조회 (첨부파일 + 최신 리비전 조인)
      const data = await tx
        .select({
          // 첨부파일 메인 정보
          id: rfqLastAttachments.id,
          attachmentType: rfqLastAttachments.attachmentType,
          serialNo: rfqLastAttachments.serialNo,
          rfqId: rfqLastAttachments.rfqId,
          currentRevision: rfqLastAttachments.currentRevision,
          latestRevisionId: rfqLastAttachments.latestRevisionId,
          description: rfqLastAttachments.description,
          createdBy: rfqLastAttachments.createdBy,
          createdAt: rfqLastAttachments.createdAt,
          updatedAt: rfqLastAttachments.updatedAt,

          // 최신 리비전 파일 정보
          fileName: rfqLastAttachmentRevisions.fileName,
          originalFileName: rfqLastAttachmentRevisions.originalFileName,
          filePath: rfqLastAttachmentRevisions.filePath,
          fileSize: rfqLastAttachmentRevisions.fileSize,
          fileType: rfqLastAttachmentRevisions.fileType,
          revisionComment: rfqLastAttachmentRevisions.revisionComment,

          // 생성자 정보
          createdByName: users.name,
        })
        .from(rfqLastAttachments)
        .leftJoin(
          rfqLastAttachmentRevisions,
          and(
            eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
            eq(rfqLastAttachmentRevisions.isLatest, true)
          )
        )
        .leftJoin(users, eq(rfqLastAttachments.createdBy, users.id))
        .where(finalWhere)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset)

      // 전체 개수 조회
      const totalResult = await tx
        .select({ count: count() })
        .from(rfqLastAttachments)
        .leftJoin(
          rfqLastAttachmentRevisions,
          eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id)
        )
        .where(finalWhere)

      const total = totalResult[0]?.count ?? 0

      return { data, total }
    })

    const pageCount = Math.ceil(total / input.perPage)

    return { data, pageCount }
  } catch (err) {
    console.error("getRfqAttachments error:", err)
    return { data: [], pageCount: 0 }
  }

}
// 사용자 목록 조회 (필터용)
export async function getPUsersForFilter() {

  try {
    return await db
      .select({
        id: users.id,
        name: users.name,
        userCode: users.userCode,
      })
      .from(users)
      .where(and(eq(users.isActive, true), isNotNull(users.userCode,)))
      .orderBy(asc(users.name))
  } catch (err) {
    console.error("Error fetching users for filter:", err)
    return []
  }
}



// 일반견적 RFQ 코드 생성 (F+userCode(3자리)+일련번호5자리 형식)
async function generateGeneralRfqCode(userCode: string): Promise<string> {
  try {
    // 동일한 userCode를 가진 마지막 일반견적 번호 조회
    const lastRfq = await db
      .select({ rfqCode: rfqsLast.rfqCode })
      .from(rfqsLast)
      .where(
        and(
          eq(rfqsLast.rfqType, "일반견적"),
          like(rfqsLast.rfqCode, `F${userCode}%`) // 같은 userCode로 시작하는 RFQ만 조회
        )
      )
      .orderBy(desc(rfqsLast.createdAt))
      .limit(1);

    let nextNumber = 1;

    if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
      // F+userCode(3자리)+일련번호(5자리) 형식에서 마지막 5자리 숫자 추출
      const rfqCode = lastRfq[0].rfqCode;
      const serialNumber = rfqCode.slice(-5); // 마지막 5자리 추출

      // 숫자인지 확인하고 다음 번호 생성
      if (/^\d{5}$/.test(serialNumber)) {
        nextNumber = parseInt(serialNumber) + 1;
      }
    }

    // 5자리 숫자로 패딩
    const paddedNumber = String(nextNumber).padStart(5, '0');
    return `F${userCode}${paddedNumber}`;
  } catch (error) {
    console.error("Error generating General RFQ code:", error);
    // 에러 발생 시 타임스탬프 기반 코드 생성
    const timestamp = Date.now().toString().slice(-5);
    return `F${userCode}${timestamp}`;
  }
}

// 일반견적 생성 액션
interface CreateGeneralRfqInput {
  rfqType: string;
  rfqTitle: string;
  dueDate: Date;
  picUserId: number;
  remark?: string;
  items: Array<{
    itemCode: string;
    itemName: string;
    quantity: number;
    uom: string;
    remark?: string;
  }>;
  createdBy: number;
  updatedBy: number;
}

export async function createGeneralRfqAction(input: CreateGeneralRfqInput) {
  try {
    // 트랜잭션으로 처리
    const result = await db.transaction(async (tx) => {
      // 1. 구매 담당자 정보 조회
      const picUser = await tx
        .select({
          name: users.name,
          email: users.email,
          userCode: users.userCode
        })
        .from(users)
        .where(eq(users.id, input.picUserId))
        .limit(1);

      if (!picUser || picUser.length === 0) {
        throw new Error("구매 담당자를 찾을 수 없습니다");
      }

      // 2. userCode 확인 (3자리)
      const userCode = picUser[0].userCode;
      if (!userCode || userCode.length !== 3) {
        throw new Error("구매 담당자의 userCode가 올바르지 않습니다 (3자리 필요)");
      }

      // 3. RFQ 코드 생성 (userCode 사용)
      const rfqCode = await generateGeneralRfqCode(userCode);

      // 4. 대표 아이템 정보 추출 (첫 번째 아이템)
      const representativeItem = input.items[0];

      // 5. rfqsLast 테이블에 기본 정보 삽입
      const [newRfq] = await tx
        .insert(rfqsLast)
        .values({
          rfqCode,
          rfqType: input.rfqType,
          rfqTitle: input.rfqTitle,
          status: "RFQ 생성",
          dueDate: input.dueDate,

          // 대표 아이템 정보
          itemCode: representativeItem.itemCode,
          itemName: representativeItem.itemName,

          // 담당자 정보
          pic: input.picUserId,
          picCode: userCode, // userCode를 picCode로 사용
          picName: picUser[0].name || '',

          // 기타 정보
          remark: input.remark || null,
          createdBy: input.createdBy,
          updatedBy: input.updatedBy,
          createdAt: new Date(),
          updatedAt: new Date(),
        })
        .returning();

      // 6. rfqPrItems 테이블에 아이템들 삽입
      const prItemsData = input.items.map((item, index) => ({
        rfqsLastId: newRfq.id,
        rfqItem: `${index + 1}`.padStart(3, '0'), // 001, 002, ...
        prItem: `${index + 1}`.padStart(3, '0'),
        prNo: rfqCode, // RFQ 코드를 PR 번호로 사용

        materialCode: item.itemCode,
        materialDescription: item.itemName,
        quantity: item.quantity,
        uom: item.uom,

        majorYn: index === 0, // 첫 번째 아이템을 주요 아이템으로 설정
        remark: item.remark || null,
      }));

      await tx.insert(rfqPrItems).values(prItemsData);

      return newRfq;
    });

    return {
      success: true,
      message: "일반견적이 성공적으로 생성되었습니다",
      data: {
        id: result.id,
        rfqCode: result.rfqCode,
      },
    };

  } catch (error) {
    console.error("일반견적 생성 오류:", error);

    if (error instanceof Error) {
      return {
        success: false,
        error: error.message,
      };
    }

    return {
      success: false,
      error: "일반견적 생성 중 오류가 발생했습니다",
    };
  }
}

// 일반견적 미리보기 (선택적 기능)
export async function previewGeneralRfqCode(picUserId: number): Promise<string> {
  try {
    // 구매 담당자 정보 조회
    const picUser = await db
      .select({
        userCode: users.userCode
      })
      .from(users)
      .where(eq(users.id, picUserId))
      .limit(1);

    if (!picUser || picUser.length === 0 || !picUser[0].userCode) {
      return `F???00001`;
    }

    const userCode = picUser[0].userCode;
    if (userCode.length !== 3) {
      return `F???00001`;
    }

    // 동일한 userCode를 가진 마지막 일반견적 번호 조회
    const lastRfq = await db
      .select({ rfqCode: rfqsLast.rfqCode })
      .from(rfqsLast)
      .where(
        and(
          eq(rfqsLast.rfqType, "일반견적"),
          like(rfqsLast.rfqCode, `F${userCode}%`)
        )
      )
      .orderBy(desc(rfqsLast.createdAt))
      .limit(1);

    let nextNumber = 1;

    if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
      const rfqCode = lastRfq[0].rfqCode;
      const serialNumber = rfqCode.slice(-5);

      if (/^\d{5}$/.test(serialNumber)) {
        nextNumber = parseInt(serialNumber) + 1;
      }
    }

    const paddedNumber = String(nextNumber).padStart(5, '0');
    return `F${userCode}${paddedNumber}`;
  } catch (error) {
    return `F???XXXXX`;
  }
}


/**
 * RFQ 첨부파일 목록 조회
 */
export async function getRfqAttachmentsAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: []
      }
    }

    // rfpAttachmentsWithLatestRevisionView 뷰 조회
    const attachments = await db.execute(sql`
      SELECT 
        attachment_id,
        attachment_type,
        serial_no,
        rfq_id,
        description,
        current_revision,
        revision_id,
        file_name,
        original_file_name,
        file_path,
        file_size,
        file_type,
        revision_comment,
        created_by,
        created_by_name,
        created_at,
        updated_at
      FROM rfq_attachments_with_latest_revision
      WHERE rfq_id = ${rfqId}
      ORDER BY attachment_type, serial_no, created_at DESC
    `)

    const formattedAttachments = attachments.rows.map((row: any) => ({
      attachmentId: row.attachment_id,
      attachmentType: row.attachment_type,
      serialNo: row.serial_no,
      rfqId: row.rfq_id,
      description: row.description,
      currentRevision: row.current_revision,
      revisionId: row.revision_id,
      fileName: row.file_name,
      originalFileName: row.original_file_name,
      filePath: row.file_path,
      fileSize: row.file_size,
      fileType: row.file_type,
      revisionComment: row.revision_comment,
      createdBy: row.created_by,
      createdByName: row.created_by_name,
      createdAt: row.created_at ? new Date(row.created_at) : null,
      updatedAt: row.updated_at ? new Date(row.updated_at) : null,
    }))

    return {
      success: true,
      data: formattedAttachments,
      count: formattedAttachments.length
    }

  } catch (error) {
    console.error("RFQ 첨부파일 조회 오류:", error)
    return {
      success: false,
      error: "첨부파일 목록을 불러오는데 실패했습니다",
      data: []
    }
  }
}

/**
 * RFQ 품목 목록 조회
 */
export async function getRfqItemsAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: []
      }
    }

    // prItemsLastView 조회
    const items = await db
      .select()
      .from(prItemsLastView)
      .where(eq(prItemsLastView.rfqsLastId, rfqId))
      .orderBy(prItemsLastView.majorYn, prItemsLastView.rfqItem, prItemsLastView.materialCode)

    const formattedItems = items.map(item => ({
      id: item.id,
      rfqsLastId: item.rfqsLastId,
      rfqItem: item.rfqItem,
      prItem: item.prItem,
      prNo: item.prNo,
      materialCode: item.materialCode,
      materialCategory: item.materialCategory,
      acc: item.acc,
      materialDescription: item.materialDescription,
      size: item.size,
      deliveryDate: item.deliveryDate,
      quantity: item.quantity,
      uom: item.uom,
      grossWeight: item.grossWeight,
      gwUom: item.gwUom,
      specNo: item.specNo,
      specUrl: item.specUrl,
      trackingNo: item.trackingNo,
      majorYn: item.majorYn,
      remark: item.remark,
      projectDef: item.projectDef,
      projectSc: item.projectSc,
      projectKl: item.projectKl,
      projectLc: item.projectLc,
      projectDl: item.projectDl,
      // RFQ 관련 정보
      rfqCode: item.rfqCode,
      rfqType: item.rfqType,
      rfqTitle: item.rfqTitle,
      itemCode: item.itemCode,
      itemName: item.itemName,
      projectCode: item.projectCode,
      projectName: item.projectName,
    }))

    // 주요 품목과 일반 품목 분리 및 통계
    const majorItems = formattedItems.filter(item => item.majorYn)
    const regularItems = formattedItems.filter(item => !item.majorYn)

    return {
      success: true,
      data: formattedItems,
      statistics: {
        total: formattedItems.length,
        major: majorItems.length,
        regular: regularItems.length,
        totalQuantity: formattedItems.reduce((sum, item) => sum + (item.quantity || 0), 0),
        totalWeight: formattedItems.reduce((sum, item) => sum + (item.grossWeight || 0), 0),
      }
    }

  } catch (error) {
    console.error("RFQ 품목 조회 오류:", error)
    return {
      success: false,
      error: "품목 목록을 불러오는데 실패했습니다",
      data: [],
      statistics: {
        total: 0,
        major: 0,
        regular: 0,
        totalQuantity: 0,
        totalWeight: 0,
      }
    }
  }
}

/**
 * RFQ 기본 정보 조회 (첨부파일/품목 다이얼로그용)
 */
export async function getRfqBasicInfoAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: null
      }
    }

    const rfqInfo = await db
      .select({
        id: rfqsLast.id,
        rfqCode: rfqsLast.rfqCode,
        rfqType: rfqsLast.rfqType,
        rfqTitle: rfqsLast.rfqTitle,
        status: rfqsLast.status,
        itemCode: rfqsLast.itemCode,
        itemName: rfqsLast.itemName,
        dueDate: rfqsLast.dueDate,
        createdAt: rfqsLast.createdAt,
      })
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId))
      .limit(1)

    if (!rfqInfo.length) {
      return {
        success: false,
        error: "RFQ를 찾을 수 없습니다",
        data: null
      }
    }

    return {
      success: true,
      data: rfqInfo[0]
    }

  } catch (error) {
    console.error("RFQ 기본정보 조회 오류:", error)
    return {
      success: false,
      error: "RFQ 정보를 불러오는데 실패했습니다",
      data: null
    }
  }
}