"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

import { revalidateTag, unstable_noStore } from "next/cache";
import db from "@/db/db";
import { techVendorAttachments, techVendorContacts, techVendorPossibleItems, techVendors, techVendorItemsView, type TechVendor } from "@/db/schema/techVendors";
import { items, itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";

import { filterColumns } from "@/lib/filter-columns";
import { unstable_cache } from "@/lib/unstable-cache";
import { getErrorMessage } from "@/lib/handle-error";

import {
  insertTechVendor,
  updateTechVendor,
  groupByTechVendorStatus,
  selectTechVendorContacts,
  countTechVendorContacts,
  insertTechVendorContact,
  selectTechVendorItems,
  countTechVendorItems,
  insertTechVendorItem,
  selectTechVendorsWithAttachments,
  countTechVendorsWithAttachments,
  updateTechVendors,
} from "./repository";

import type {
  CreateTechVendorSchema,
  UpdateTechVendorSchema,
  GetTechVendorsSchema,
  GetTechVendorContactsSchema,
  CreateTechVendorContactSchema,
  GetTechVendorItemsSchema,
  CreateTechVendorItemSchema,
} from "./validations";

import { asc, desc, ilike, inArray, and, or, eq, isNull } from "drizzle-orm";
import path from "path";
import fs from "fs/promises";
import { randomUUID } from "crypto";
import { sql } from "drizzle-orm";
import { users } from "@/db/schema/users";

/* -----------------------------------------------------
   1) 조회 관련 
----------------------------------------------------- */

/**
 * 복잡한 조건으로 기술영업 Vendor 목록을 조회 (+ pagination) 하고,
 * 총 개수에 따라 pageCount를 계산해서 리턴.
 * Next.js의 unstable_cache를 사용해 일정 시간 캐시.
 */
export async function getTechVendors(input: GetTechVendorsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;
        
        // 1) 고급 필터
        const advancedWhere = filterColumns({
          table: techVendors,
          filters: input.filters,
          joinOperator: input.joinOperator,
        });
        
        // 2) 글로벌 검색
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(techVendors.vendorName, s),
            ilike(techVendors.vendorCode, s),
            ilike(techVendors.email, s),
            ilike(techVendors.status, s)
          );
        }
        
        // 최종 where 결합
        const finalWhere = and(advancedWhere, globalWhere);
        
        // 간단 검색 (advancedTable=false) 시 예시
        const simpleWhere = and(
          input.vendorName
            ? ilike(techVendors.vendorName, `%${input.vendorName}%`)
            : undefined,
          input.status ? ilike(techVendors.status, input.status) : undefined,
          input.country
            ? ilike(techVendors.country, `%${input.country}%`)
            : undefined
        );
        
        // 실제 사용될 where
        const where = finalWhere;
        
        // 정렬
        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc ? desc(techVendors[item.id]) : asc(techVendors[item.id])
            )
            : [asc(techVendors.createdAt)];
        
        // 트랜잭션 내에서 데이터 조회
        const { data, total } = await db.transaction(async (tx) => {
          // 1) vendor 목록 조회 (with attachments)
          const vendorsData = await selectTechVendorsWithAttachments(tx, {
            where,
            orderBy,
            offset,
            limit: input.perPage,
          });
          
          // 2) 전체 개수
          const total = await countTechVendorsWithAttachments(tx, where);
          return { data: vendorsData, total };
        });
        
        // 페이지 수
        const pageCount = Math.ceil(total / input.perPage);
        
        return { data, pageCount };
      } catch (err) {
        console.error("Error fetching tech vendors:", err);
        // 에러 발생 시
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)], // 캐싱 키
    {
      revalidate: 3600,
      tags: ["tech-vendors"], // revalidateTag("tech-vendors") 호출 시 무효화
    }
  )();
}

/**
 * 기술영업 벤더 상태별 카운트 조회
 */
export async function getTechVendorStatusCounts() {
  return unstable_cache(
    async () => {
      try {
        const initial: Record<TechVendor["status"], number> = {
          "ACTIVE": 0,
          "INACTIVE": 0,
          "BLACKLISTED": 0,
          "PENDING_REVIEW": 0,
          "IN_REVIEW": 0,
          "REJECTED": 0
        };

        const result = await db.transaction(async (tx) => {
          const rows = await groupByTechVendorStatus(tx);
          type StatusCountRow = { status: TechVendor["status"]; count: number };
          return (rows as StatusCountRow[]).reduce<Record<TechVendor["status"], number>>((acc, { status, count }) => {
            acc[status] = count;
            return acc;
          }, initial);
        });

        return result;
      } catch (err) {
        return {} as Record<TechVendor["status"], number>;
      }
    },
    ["tech-vendor-status-counts"], // 캐싱 키
    {
      revalidate: 3600,
    }
  )();
}

/**
 * 벤더 상세 정보 조회
 */
export async function getTechVendorById(id: number) {
  return unstable_cache(
    async () => {
      try {
        const result = await getTechVendorDetailById(id);
        return { data: result };
      } catch (err) {
        console.error("기술영업 벤더 상세 조회 오류:", err);
        return { data: null };
      }
    },
    [`tech-vendor-${id}`],
    {
      revalidate: 3600,
      tags: ["tech-vendors", `tech-vendor-${id}`],
    }
  )();
}

/* -----------------------------------------------------
   2) 생성(Create) 
----------------------------------------------------- */

/**
 * 첨부파일 저장 헬퍼 함수
 */
async function storeTechVendorFiles(
  tx: any,
  vendorId: number,
  files: File[],
  attachmentType: string
) {
  const vendorDir = path.join(
    process.cwd(),
    "public",
    "tech-vendors",
    String(vendorId)
  );
  await fs.mkdir(vendorDir, { recursive: true });

  for (const file of files) {
    // Convert file to buffer
    const ab = await file.arrayBuffer();
    const buffer = Buffer.from(ab);

    // Generate a unique filename
    const uniqueName = `${randomUUID()}-${file.name}`;
    const relativePath = path.join("tech-vendors", String(vendorId), uniqueName);
    const absolutePath = path.join(process.cwd(), "public", relativePath);

    // Write to disk
    await fs.writeFile(absolutePath, buffer);

    // Insert attachment record
    await tx.insert(techVendorAttachments).values({
      vendorId,
      fileName: file.name,
      filePath: "/" + relativePath.replace(/\\/g, "/"),
      attachmentType,
    });
  }
}

/**
 * 신규 기술영업 벤더 생성
 */
export async function createTechVendor(input: CreateTechVendorSchema) {
  unstable_noStore();
  
  try {
    // taxId 중복 검사
    const existingVendor = await db
      .select({ id: techVendors.id })
      .from(techVendors)
      .where(eq(techVendors.taxId, input.taxId))
      .limit(1);
    
    // 이미 동일한 taxId를 가진 업체가 존재하면 에러 반환
    if (existingVendor.length > 0) {
      return {
        success: false,
        data: null,
        error: `이미 등록된 사업자등록번호입니다. (Tax ID ${input.taxId} already exists in the system)`
      };
    }
    
    const result = await db.transaction(async (tx) => {
      // 1. 벤더 생성
      const [newVendor] = await insertTechVendor(tx, {
        vendorName: input.vendorName,
        vendorCode: input.vendorCode || null,
        taxId: input.taxId,
        address: input.address || null,
        country: input.country,
        phone: input.phone || null,
        email: input.email,
        website: input.website || null,
        techVendorType: input.techVendorType as "조선" | "해양TOP" | "해양HULL",
        representativeName: input.representativeName || null,
        representativeBirth: input.representativeBirth || null,
        representativeEmail: input.representativeEmail || null,
        representativePhone: input.representativePhone || null,
        corporateRegistrationNumber: input.corporateRegistrationNumber || null,
        items: input.items || null,
        status: "PENDING_REVIEW"
      });
      
      // 2. 연락처 정보 등록
      for (const contact of input.contacts) {
        await insertTechVendorContact(tx, {
          vendorId: newVendor.id,
          contactName: contact.contactName,
          contactPosition: contact.contactPosition || null,
          contactEmail: contact.contactEmail,
          contactPhone: contact.contactPhone || null,
          isPrimary: contact.isPrimary ?? false,
        });
      }
      
      // 3. 첨부파일 저장
      if (input.files && input.files.length > 0) {
        await storeTechVendorFiles(tx, newVendor.id, input.files, "GENERAL");
      }
      
      return newVendor;
    });
    
    revalidateTag("tech-vendors");
    
    return {
      success: true,
      data: result,
      error: null
    };
  } catch (err) {
    console.error("기술영업 벤더 생성 오류:", err);
    
    return {
      success: false,
      data: null,
      error: getErrorMessage(err)
    };
  }
}

/* -----------------------------------------------------
   3) 업데이트 (단건/복수)
----------------------------------------------------- */

/** 단건 업데이트 */
export async function modifyTechVendor(
  input: UpdateTechVendorSchema & { id: string; }
) {
  unstable_noStore();
  try {
    const updated = await db.transaction(async (tx) => {
      // 벤더 정보 업데이트
      const [res] = await updateTechVendor(tx, input.id, {
        vendorName: input.vendorName,
        vendorCode: input.vendorCode,
        address: input.address,
        country: input.country,
        phone: input.phone,
        email: input.email,
        website: input.website,
        status: input.status,
      });

      return res;
    });

    // 캐시 무효화
    revalidateTag("tech-vendors");
    revalidateTag(`tech-vendor-${input.id}`);

    return { data: updated, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/** 복수 업데이트 */
export async function modifyTechVendors(input: {
  ids: string[];
  status?: TechVendor["status"];
}) {
  unstable_noStore();
  try {
    const data = await db.transaction(async (tx) => {
      // 여러 협력업체 일괄 업데이트
      const [updated] = await updateTechVendors(tx, input.ids, {
        // 예: 상태만 일괄 변경
        status: input.status,
      });
      return updated;
    });

    // 캐시 무효화
    revalidateTag("tech-vendors");
    revalidateTag("tech-vendor-status-counts");
    
    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/* -----------------------------------------------------
   4) 연락처 관리
----------------------------------------------------- */

export async function getTechVendorContacts(input: GetTechVendorContactsSchema, id: number) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 필터링 설정
        const advancedWhere = filterColumns({
          table: techVendorContacts,
          filters: input.filters,
          joinOperator: input.joinOperator,
        });

        // 검색 조건
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(techVendorContacts.contactName, s), 
            ilike(techVendorContacts.contactPosition, s),
            ilike(techVendorContacts.contactEmail, s), 
            ilike(techVendorContacts.contactPhone, s)
          );
        }

        // 해당 벤더 조건
        const vendorWhere = eq(techVendorContacts.vendorId, id);

        // 최종 조건 결합
        const finalWhere = and(advancedWhere, globalWhere, vendorWhere);

        // 정렬 조건
        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc ? desc(techVendorContacts[item.id]) : asc(techVendorContacts[item.id])
            )
            : [asc(techVendorContacts.createdAt)];

        // 트랜잭션 내부에서 Repository 호출
        const { data, total } = await db.transaction(async (tx) => {
          const data = await selectTechVendorContacts(tx, {
            where: finalWhere,
            orderBy,
            offset,
            limit: input.perPage,
          });
          const total = await countTechVendorContacts(tx, finalWhere);
          return { data, total };
        });

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

        return { data, pageCount };
      } catch (err) {
        // 에러 발생 시 디폴트
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input), String(id)], // 캐싱 키
    {
      revalidate: 3600,
      tags: [`tech-vendor-contacts-${id}`],
    }
  )();
}

export async function createTechVendorContact(input: CreateTechVendorContactSchema) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      // DB Insert
      const [newContact] = await insertTechVendorContact(tx, {
        vendorId: input.vendorId,
        contactName: input.contactName,
        contactPosition: input.contactPosition || "",
        contactEmail: input.contactEmail,
        contactPhone: input.contactPhone || "",
        isPrimary: input.isPrimary || false,
      });
      return newContact;
    });

    // 캐시 무효화
    revalidateTag(`tech-vendor-contacts-${input.vendorId}`);

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/* -----------------------------------------------------
   5) 아이템 관리
----------------------------------------------------- */

export async function getTechVendorItems(input: GetTechVendorItemsSchema, id: number) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 필터링 설정
        const advancedWhere = filterColumns({
          table: techVendorItemsView,
          filters: input.filters,
          joinOperator: input.joinOperator,
        });

        // 검색 조건
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(techVendorItemsView.itemCode, s),
            ilike(techVendorItemsView.itemName, s)
          );
        }

        // 해당 벤더 조건
        const vendorWhere = eq(techVendorItemsView.vendorId, id);

        // 최종 조건 결합
        const finalWhere = and(advancedWhere, globalWhere, vendorWhere);

        // 정렬 조건
        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc ? desc(techVendorItemsView[item.id]) : asc(techVendorItemsView[item.id])
            )
            : [asc(techVendorItemsView.createdAt)];

        // 트랜잭션 내부에서 Repository 호출
        const { data, total } = await db.transaction(async (tx) => {
          const data = await selectTechVendorItems(tx, {
            where: finalWhere,
            orderBy,
            offset,
            limit: input.perPage,
          });
          const total = await countTechVendorItems(tx, finalWhere);
          return { data, total };
        });

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

        return { data, pageCount };
      } catch (err) {
        // 에러 발생 시 디폴트
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input), String(id)], // 캐싱 키
    {
      revalidate: 3600,
      tags: [`tech-vendor-items-${id}`],
    }
  )();
}

export interface ItemDropdownOption {
  itemCode: string;
  itemName: string;
  description: string | null;
}

/**
 * Vendor Item 추가 시 사용할 아이템 목록 조회 (전체 목록 반환)
 * 아이템 코드, 이름, 설명만 간소화해서 반환
 */
export async function getItemsForTechVendor(vendorId: number) {
  return unstable_cache(
    async () => {
      try {
        // 해당 vendorId가 이미 가지고 있는 itemCode 목록을 서브쿼리로 구함
        // 그 아이템코드를 제외(notIn)하여 모든 items 테이블에서 조회
        const itemsData = await db
          .select({
            itemCode: items.itemCode,
            itemName: items.itemName,
            description: items.description,
          })
          .from(items)
          .leftJoin(
            techVendorPossibleItems,
            eq(items.itemCode, techVendorPossibleItems.itemCode)
          )
          // vendorPossibleItems.vendorId가 이 vendorId인 행이 없는(즉 아직 등록되지 않은) 아이템만
          .where(
            isNull(techVendorPossibleItems.id)
          )
          .orderBy(asc(items.itemName));

        return {
          data: itemsData.map((item) => ({
            itemCode: item.itemCode ?? "",     // null이라면 ""로 치환
            itemName: item.itemName,
            description: item.description ?? ""  // null이라면 ""로 치환
          })),
          error: null
        };
      } catch (err) {
        console.error("Failed to fetch items for tech vendor dropdown:", err);
        return {
          data: [],
          error: "아이템 목록을 불러오는데 실패했습니다.",
        };
      }
    },
    // 캐시 키를 vendorId 별로 달리 해야 한다.
    ["items-for-tech-vendor", String(vendorId)],
    {
      revalidate: 3600, // 1시간 캐싱
      tags: ["items"],   // revalidateTag("items") 호출 시 무효화
    }
  )();
}

/**
 * 벤더 타입과 아이템 코드에 따른 아이템 조회
 */
export async function getItemsByVendorType(vendorType: string, itemCode: string) {
  try {
    let items: (typeof itemShipbuilding.$inferSelect | typeof itemOffshoreTop.$inferSelect | typeof itemOffshoreHull.$inferSelect)[] = [];
    
    switch (vendorType) {
      case "조선":
        const shipbuildingResults = await db
          .select({
            id: itemShipbuilding.id,
            itemCode: itemShipbuilding.itemCode,
            workType: itemShipbuilding.workType,
            shipTypes: itemShipbuilding.shipTypes,
            itemList: itemShipbuilding.itemList,
            createdAt: itemShipbuilding.createdAt,
            updatedAt: itemShipbuilding.updatedAt,
          })
          .from(itemShipbuilding)
          .where(itemCode ? eq(itemShipbuilding.itemCode, itemCode) : undefined);
        items = shipbuildingResults;
        break;

      case "해양TOP":
        const offshoreTopResults = await db
          .select({
            id: itemOffshoreTop.id,
            itemCode: itemOffshoreTop.itemCode,
            workType: itemOffshoreTop.workType,
            itemList: itemOffshoreTop.itemList,
            subItemList: itemOffshoreTop.subItemList,
            createdAt: itemOffshoreTop.createdAt,
            updatedAt: itemOffshoreTop.updatedAt,
          })
          .from(itemOffshoreTop)
          .where(itemCode ? eq(itemOffshoreTop.itemCode, itemCode) : undefined);
        items = offshoreTopResults;
        break;

      case "해양HULL":
        const offshoreHullResults = await db
          .select({
            id: itemOffshoreHull.id,
            itemCode: itemOffshoreHull.itemCode,
            workType: itemOffshoreHull.workType,
            itemList: itemOffshoreHull.itemList,
            subItemList: itemOffshoreHull.subItemList,
            createdAt: itemOffshoreHull.createdAt,
            updatedAt: itemOffshoreHull.updatedAt,
          })
          .from(itemOffshoreHull)
          .where(itemCode ? eq(itemOffshoreHull.itemCode, itemCode) : undefined);
        items = offshoreHullResults;
        break;

      default:
        items = [];
    }

    const result = items.map(item => ({
      ...item,
      techVendorType: vendorType
    }));

    return { data: result, error: null };
  } catch (error) {
    return { data: [], error: "Failed to fetch items" };
  }
}

/**
 * 벤더의 possible_items를 조회하고 해당 아이템 코드로 각 타입별 테이블을 조회
 */
export async function getVendorItemsByType(vendorId: number, vendorType: string) {
  try {
    // 벤더의 possible_items 조회
    const possibleItems = await db.query.techVendorPossibleItems.findMany({
      where: eq(techVendorPossibleItems.vendorId, vendorId),
      columns: {
        itemCode: true
      }
    })

    const itemCodes = possibleItems.map(item => item.itemCode)

    // 벤더 타입에 따라 해당하는 테이블에서 아이템 조회
    switch (vendorType) {
      case "조선":
        const shipbuildingItems = await db.query.itemShipbuilding.findMany({
          where: inArray(itemShipbuilding.itemCode, itemCodes)
        })
        return {
          data: shipbuildingItems.map(item => ({
            ...item,
            techVendorType: "조선"
          }))
        }

      case "해양TOP":
        const offshoreTopItems = await db.query.itemOffshoreTop.findMany({
          where: inArray(itemOffshoreTop.itemCode, itemCodes)
        })
        return {
          data: offshoreTopItems.map(item => ({
            ...item,
            techVendorType: "해양TOP"
          }))
        }

      case "해양HULL":
        const offshoreHullItems = await db.query.itemOffshoreHull.findMany({
          where: inArray(itemOffshoreHull.itemCode, itemCodes)
        })
        return {
          data: offshoreHullItems.map(item => ({
            ...item,
            techVendorType: "해양HULL"
          }))
        }

      default:
        throw new Error(`Unsupported vendor type: ${vendorType}`)
    }
  } catch (error) {
    throw error
  }
}

export async function createTechVendorItem(input: CreateTechVendorItemSchema & { itemName: string }) {
  unstable_noStore();
  try {
    // DB에 이미 존재하는지 확인
    const existingItem = await db
      .select({ id: techVendorPossibleItems.id })
      .from(techVendorPossibleItems)
      .where(
        and(
          eq(techVendorPossibleItems.vendorId, input.vendorId),
          eq(techVendorPossibleItems.itemCode, input.itemCode)
        )
      )
      .limit(1);

    if (existingItem.length > 0) {
      return { data: null, error: "이미 추가된 아이템입니다." };
    }

    await db.transaction(async (tx) => {
      // DB Insert
      const [newItem] = await tx
        .insert(techVendorPossibleItems)
        .values({
          vendorId: input.vendorId,
          itemCode: input.itemCode,
          itemName: input.itemName || "기술영업",
        })
        .returning();
      return newItem;
    });

    // 캐시 무효화
    revalidateTag(`tech-vendor-items-${input.vendorId}`);

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/* -----------------------------------------------------
   6) 기술영업 벤더 승인/거부
----------------------------------------------------- */

interface ApproveTechVendorsInput {
  ids: string[];
}

/**
 * 기술영업 벤더 승인 (상태를 ACTIVE로 변경)
 */
export async function approveTechVendors(input: ApproveTechVendorsInput) {
  unstable_noStore();
  
  try {
    // 트랜잭션 내에서 협력업체 상태 업데이트
    const result = await db.transaction(async (tx) => {
      // 협력업체 상태 업데이트
      const [updated] = await tx
        .update(techVendors)
        .set({
          status: "ACTIVE",
          updatedAt: new Date()
        })
        .where(inArray(techVendors.id, input.ids.map(id => parseInt(id))))
        .returning();
        
      return updated;
    });
    
    // 캐시 무효화
    revalidateTag("tech-vendors");
    revalidateTag("tech-vendor-status-counts");
    
    return { data: result, error: null };
  } catch (err) {
    console.error("Error approving tech vendors:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 벤더 거부 (상태를 REJECTED로 변경)
 */
export async function rejectTechVendors(input: ApproveTechVendorsInput) {
  unstable_noStore();
  
  try {
    // 트랜잭션 내에서 협력업체 상태 업데이트
    const result = await db.transaction(async (tx) => {
      // 협력업체 상태 업데이트
      const [updated] = await tx
        .update(techVendors)
        .set({
          status: "REJECTED",
          updatedAt: new Date()
        })
        .where(inArray(techVendors.id, input.ids.map(id => parseInt(id))))
        .returning();
        
      return updated;
    });
    
    // 캐시 무효화
    revalidateTag("tech-vendors");
    revalidateTag("tech-vendor-status-counts");
    
    return { data: result, error: null };
  } catch (err) {
    console.error("Error rejecting tech vendors:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/* -----------------------------------------------------
   7) 엑셀 내보내기
----------------------------------------------------- */

/**
 * 벤더 연락처 목록 엑셀 내보내기
 */
export async function exportTechVendorContacts(vendorId: number) {
  try {
    const contacts = await db
      .select()
      .from(techVendorContacts)
      .where(eq(techVendorContacts.vendorId, vendorId))
      .orderBy(techVendorContacts.isPrimary, techVendorContacts.contactName);
    
    return contacts;
  } catch (err) {
    console.error("기술영업 벤더 연락처 내보내기 오류:", err);
    return [];
  }
}

/**
 * 벤더 아이템 목록 엑셀 내보내기
 */
export async function exportTechVendorItems(vendorId: number) {
  try {
    const items = await db
      .select({
        id: techVendorItemsView.vendorItemId,
        vendorId: techVendorItemsView.vendorId,
        itemName: techVendorItemsView.itemName,
        itemCode: techVendorItemsView.itemCode,
        createdAt: techVendorItemsView.createdAt,
        updatedAt: techVendorItemsView.updatedAt,
      })
      .from(techVendorItemsView)
      .where(eq(techVendorItemsView.vendorId, vendorId))
      .orderBy(techVendorItemsView.itemName);
    
    return items;
  } catch (err) {
    console.error("기술영업 벤더 아이템 내보내기 오류:", err);
    return [];
  }
}

/**
 * 벤더 정보 엑셀 내보내기
 */
export async function exportTechVendorDetails(vendorIds: number[]) {
  try {
    if (!vendorIds.length) return [];
    
    // 벤더 기본 정보 조회
    const vendorsData = await db
      .select({
        id: techVendors.id,
        vendorName: techVendors.vendorName,
        vendorCode: techVendors.vendorCode,
        taxId: techVendors.taxId,
        address: techVendors.address,
        country: techVendors.country,
        phone: techVendors.phone,
        email: techVendors.email,
        website: techVendors.website,
        status: techVendors.status,
        representativeName: techVendors.representativeName,
        representativeEmail: techVendors.representativeEmail,
        representativePhone: techVendors.representativePhone,
        representativeBirth: techVendors.representativeBirth,
        corporateRegistrationNumber: techVendors.corporateRegistrationNumber,
        items: techVendors.items,
        createdAt: techVendors.createdAt,
        updatedAt: techVendors.updatedAt,
      })
      .from(techVendors)
      .where(
        vendorIds.length === 1 
          ? eq(techVendors.id, vendorIds[0]) 
          : inArray(techVendors.id, vendorIds)
      );

    // 벤더별 상세 정보를 포함하여 반환
    const vendorsWithDetails = await Promise.all(
      vendorsData.map(async (vendor) => {
        // 연락처 조회
        const contacts = await exportTechVendorContacts(vendor.id);
        
        // 아이템 조회
        const items = await exportTechVendorItems(vendor.id);
        
        return {
          ...vendor,
          vendorContacts: contacts,
          vendorItems: items,
        };
      })
    );
    
    return vendorsWithDetails;
  } catch (err) {
    console.error("기술영업 벤더 상세 내보내기 오류:", err);
    return [];
  }
}

/**
 * 기술영업 벤더 상세 정보 조회 
 */
async function getTechVendorDetailById(id: number) {
  try {
    const vendor = await db.select().from(techVendors).where(eq(techVendors.id, id)).limit(1);
    
    if (!vendor || vendor.length === 0) {
      console.error(`Vendor not found with id: ${id}`);
      return null;
    }

    const contacts = await db.select().from(techVendorContacts).where(eq(techVendorContacts.vendorId, id));
    const attachments = await db.select().from(techVendorAttachments).where(eq(techVendorAttachments.vendorId, id));
    const possibleItems = await db.select().from(techVendorPossibleItems).where(eq(techVendorPossibleItems.vendorId, id));

    return {
      ...vendor[0],
      contacts,
      attachments,
      possibleItems
    };
  } catch (error) {
    console.error("Error fetching tech vendor detail:", error);
    return null;
  }
}

/**
 * 기술영업 벤더 첨부파일 다운로드를 위한 서버 액션
 * @param vendorId 기술영업 벤더 ID
 * @param fileId 특정 파일 ID (단일 파일 다운로드시)
 * @returns 다운로드할 수 있는 임시 URL
 */
export async function downloadTechVendorAttachments(vendorId:number, fileId?:number) {
  try {
    // API 경로 생성 (단일 파일 또는 모든 파일)
    const url = fileId
      ? `/api/tech-vendors/attachments/download?id=${fileId}&vendorId=${vendorId}`
      : `/api/tech-vendors/attachments/download-all?vendorId=${vendorId}`;
    
    // fetch 요청 (기본적으로 Blob으로 응답 받기)
    const response = await fetch(url, {
      method: 'GET',
      headers: {
        'Content-Type': 'application/json',
      },
    });
    
    if (!response.ok) {
      throw new Error(`Server responded with ${response.status}: ${response.statusText}`);
    }
    
    // 파일명 가져오기 (Content-Disposition 헤더에서)
    const contentDisposition = response.headers.get('content-disposition');
    let fileName = fileId ? `file-${fileId}.zip` : `tech-vendor-${vendorId}-files.zip`;
    
    if (contentDisposition) {
      const matches = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/.exec(contentDisposition);
      if (matches && matches[1]) {
        fileName = matches[1].replace(/['"]/g, '');
      }
    }
    
    // Blob으로 응답 변환
    const blob = await response.blob();
    
    // Blob URL 생성
    const blobUrl = window.URL.createObjectURL(blob);
    
    return { 
      url: blobUrl,
      fileName,
      blob
    };
  } catch (error) {
    console.error('Download API error:', error);
    throw error;
  }
}

/**
 * 임시 ZIP 파일 정리를 위한 서버 액션
 * @param fileName 정리할 파일명
 */
export async function cleanupTechTempFiles(fileName: string) {
  'use server';

  try {
    const tempDir = path.join(process.cwd(), 'tmp');
    const filePath = path.join(tempDir, fileName);

    try {
      // 파일 존재 확인
      await fs.access(filePath, fs.constants.F_OK);
      // 파일 삭제
      await fs.unlink(filePath);
    } catch {
      // 파일이 없으면 무시
    }

    return { success: true };
  } catch (error) {
    console.error('임시 파일 정리 오류:', error);
    return { success: false, error: '임시 파일 정리 중 오류가 발생했습니다.' };
  }
}

export const findVendorById = async (id: number): Promise<TechVendor | null> => {
  try {
    // 직접 DB에서 조회
    const vendor = await db
      .select()
      .from(techVendors)
      .where(eq(techVendors.id, id))
      .limit(1)
      .then(rows => rows[0] || null);

    if (!vendor) {
      console.error(`Vendor not found with id: ${id}`);
      return null;
    }

    return vendor;
  } catch (error) {
    console.error('Error fetching vendor:', error);
    return null;
  }
};

/**
 * 기술영업 벤더 엑셀 import 시 유저 생성 및 아이템 등록
 */
export async function importTechVendorsFromExcel(
  vendors: Array<{
    vendorName: string;
    email: string;
    taxId: string;
    address?: string;
    country?: string;
    phone?: string;
    website?: string;
    techVendorType: string;
    items: string; // 쉼표로 구분된 아이템 코드들
  }>,
) {
  unstable_noStore();

  try {
    const result = await db.transaction(async (tx) => {
      const createdVendors = [];

      for (const vendor of vendors) {
        // 1. 벤더 생성
        const [newVendor] = await tx.insert(techVendors).values({
          vendorName: vendor.vendorName,
          vendorCode: null, // 자동 생성
          taxId: vendor.taxId,
          address: vendor.address || null,
          country: vendor.country || null,
          phone: vendor.phone || null,
          email: vendor.email,
          website: vendor.website || null,
          techVendorType: vendor.techVendorType as "조선" | "해양TOP" | "해양HULL",
          status: "PENDING_REVIEW"
        }).returning();

        // 2. 유저 생성 (이메일이 있는 경우)
        if (vendor.email) {
          // 이미 존재하는 유저인지 확인
          const existingUser = await tx.query.users.findFirst({
            where: eq(users.email, vendor.email),
            columns: { id: true }
          });

          // 유저가 존재하지 않는 경우에만 생성
          if (!existingUser) {
            await tx.insert(users).values({
              name: vendor.vendorName,
              email: vendor.email,
              companyId: newVendor.id,
              domain: "partners",
            });
          }
        }

        // 3. 아이템 등록
        if (vendor.items) {
          const itemCodes = vendor.items.split(',').map(code => code.trim());
          for (const itemCode of itemCodes) {
            // 아이템 정보 조회
            const [item] = await tx.select().from(items).where(eq(items.itemCode, itemCode));
            if (item && item.itemCode && item.itemName) {
              await tx.insert(techVendorPossibleItems).values({
                vendorId: newVendor.id,
                itemCode: item.itemCode,
                itemName: item.itemName,
              });
            }
          }
        }

        createdVendors.push(newVendor);
      }

      return createdVendors;
    });

    // 캐시 무효화
    revalidateTag("tech-vendors");
    revalidateTag("users");

    return { success: true, data: result };
  } catch (error) {
    console.error("Failed to import tech vendors:", error);
    return { success: false, error: getErrorMessage(error) };
  }
}