// src/lib/items-ship/service.ts
"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

import { revalidateTag, unstable_noStore } from "next/cache";
import db from "@/db/db";

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

import { asc, desc, ilike, and, or, eq, count, inArray, sql } from "drizzle-orm";
import { GetShipbuildingSchema, GetOffshoreTopSchema, GetOffshoreHullSchema, ShipbuildingItemCreateData, TypedItemCreateData, OffshoreTopItemCreateData, OffshoreHullItemCreateData } from "./validations";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";

// 타입 정의 추가
export type ShipbuildingWorkType = '기장' | '전장' | '선실' | '배관' | '철의';
export type OffshoreTopWorkType = 'TM' | 'TS' | 'TE' | 'TP';
export type OffshoreHullWorkType = 'HA' | 'HE' | 'HH' | 'HM' | 'NC';

export interface ShipbuildingItem {
  id: number;
  itemCode: string;
  workType: ShipbuildingWorkType;
  itemList: string;
  shipTypes: string;
  createdAt: Date;
  updatedAt: Date;
}

export interface OffshoreTopTechItem {
  id: number;
  itemCode: string;
  workType: OffshoreTopWorkType;
  itemList: string;
  subItemList?: string;
  createdAt: Date;
  updatedAt: Date;
}

export interface OffshoreHullTechItem {
  id: number;
  itemCode: string;
  workType: OffshoreHullWorkType;
  itemList: string;
  subItemList?: string;
  createdAt: Date;
  updatedAt: Date;
}

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

/**
 * 복잡한 조건으로 조선 아이템 목록을 조회 (+ pagination) 하고,
 * 총 개수에 따라 pageCount를 계산해서 리턴.
 * Next.js의 unstable_cache를 사용해 일정 시간 캐시.
 */
export async function getShipbuildingItems(input: GetShipbuildingSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // advancedTable 모드면 filterColumns()로 where 절 구성
        const advancedWhere = filterColumns({
          table: itemShipbuilding,
          filters: input.filters.filter(filter => {
            // enum 필드에 대한 isEmpty/isNotEmpty는 제외
            return !((filter.id === 'workType' || filter.id === 'shipTypes') && 
                    (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          }),
          joinOperator: input.joinOperator,
        });

        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(itemShipbuilding.itemCode, s), 
            ilike(itemShipbuilding.itemList, s),
          );
        }

        // enum 필드에 대한 isEmpty/isNotEmpty 처리
        const enumConditions = input.filters
          .filter(filter => (filter.id === 'workType' || filter.id === 'shipTypes') && 
                           (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          .map(filter => {
            const column = itemShipbuilding[filter.id];
            return filter.operator === 'isEmpty' ? sql`${column} is null` : sql`${column} is not null`;
          });

        const finalWhere = and(
          advancedWhere,
          globalWhere,
          ...enumConditions
        );

        const where = finalWhere;

        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) => {
                const column = itemShipbuilding[item.id];
                return item.desc ? desc(column) : asc(column);
              })
            : [desc(itemShipbuilding.createdAt)];

        // 조선 아이템 테이블만 조회 (독립적)
        const result = 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(where)
        .orderBy(...orderBy)
        .offset(offset)
        .limit(input.perPage);
        
        // 전체 데이터 개수 조회
        const [{ count: total }] = await db.select({
          count: count()
        })
        .from(itemShipbuilding)
        .where(where);
        
        const pageCount = Math.ceil(Number(total) / input.perPage);

        return { data: result, pageCount };
      } catch (err) {
        console.error("Error fetching shipbuilding items:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["items"],
    }
  )();
}

export async function getOffshoreTopItems(input: GetOffshoreTopSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // advancedTable 모드면 filterColumns()로 where 절 구성
        const advancedWhere = filterColumns({
          table: itemOffshoreTop,
          filters: input.filters.filter(filter => {
            // enum 필드에 대한 isEmpty/isNotEmpty는 제외
            return !((filter.id === 'workType') && 
                    (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          }),
          joinOperator: input.joinOperator,
        });

        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(itemOffshoreTop.itemCode, s), 
            ilike(itemOffshoreTop.itemList, s),
            ilike(itemOffshoreTop.subItemList, s)
          );
        }

        // enum 필드에 대한 isEmpty/isNotEmpty 처리
        const enumConditions = input.filters
          .filter(filter => (filter.id === 'workType') && 
                           (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          .map(filter => {
            const column = itemOffshoreTop.workType;
            return filter.operator === 'isEmpty' ? sql`${column} is null` : sql`${column} is not null`;
          });

        const finalWhere = and(
          advancedWhere,
          globalWhere,
          ...enumConditions
        );

        const where = finalWhere;

        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) => {
                const column = itemOffshoreTop[item.id];
                return item.desc ? desc(column) : asc(column);
              })
            : [desc(itemOffshoreTop.createdAt)];

        // 해양 TOP 아이템 테이블만 조회 (독립적)
        const result = 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(where)
        .orderBy(...orderBy)
        .offset(offset)
        .limit(input.perPage);
        
        // 전체 데이터 개수 조회
        const [{ count: total }] = await db.select({
          count: count()
        })
        .from(itemOffshoreTop)
        .where(where);
        
        const pageCount = Math.ceil(Number(total) / input.perPage);

        return { data: result, pageCount };
      } catch (err) {
        console.error("Error fetching offshore top items:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["items"],
    }
  )();
}

export async function getOffshoreHullItems(input: GetOffshoreHullSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // advancedTable 모드면 filterColumns()로 where 절 구성
        const advancedWhere = filterColumns({
          table: itemOffshoreHull,
          filters: input.filters.filter(filter => {
            // enum 필드에 대한 isEmpty/isNotEmpty는 제외
            return !((filter.id === 'workType') && 
                    (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          }),
          joinOperator: input.joinOperator,
        });

        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(itemOffshoreHull.itemCode, s), 
            ilike(itemOffshoreHull.itemList, s),
            ilike(itemOffshoreHull.subItemList, s)
          );
        }

        // enum 필드에 대한 isEmpty/isNotEmpty 처리
        const enumConditions = input.filters
          .filter(filter => (filter.id === 'workType') && 
                           (filter.operator === 'isEmpty' || filter.operator === 'isNotEmpty'))
          .map(filter => {
            const column = itemOffshoreHull.workType;
            return filter.operator === 'isEmpty' ? sql`${column} is null` : sql`${column} is not null`;
          });

        const finalWhere = and(
          advancedWhere,
          globalWhere,
          ...enumConditions
        );

        const where = finalWhere;

        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) => {
                const column = itemOffshoreHull[item.id];
                return item.desc ? desc(column) : asc(column);
              })
            : [desc(itemOffshoreHull.createdAt)];

        // 해양 HULL 아이템 테이블만 조회 (독립적)
        const result = 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(where)
        .orderBy(...orderBy)
        .offset(offset)
        .limit(input.perPage);
        
        // 전체 데이터 개수 조회
        const [{ count: total }] = await db.select({
          count: count()
        })
        .from(itemOffshoreHull)
        .where(where);
        
        const pageCount = Math.ceil(Number(total) / input.perPage);

        return { data: result, pageCount };
      } catch (err) {
        console.error("Error fetching offshore hull items:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["items"],
    }
  )();
}

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

/**
 * 조선 아이템 생성 - 독립적으로 생성
 */
export async function createShipbuildingItem(input: TypedItemCreateData) {
  unstable_noStore()
  
  try {
    if (!input.itemCode) {
      return { 
        success: false, 
        message: "아이템 코드는 필수입니다",
        data: null, 
        error: "필수 필드 누락" 
      }
    }
    
    const shipData = input as ShipbuildingItemCreateData;
    const result = await db.insert(itemShipbuilding).values({
      itemCode: input.itemCode,
      workType: shipData.workType ? (shipData.workType as '기장' | '전장' | '선실' | '배관' | '철의') : '기장',
      shipTypes: shipData.shipTypes || '',
      itemList: shipData.itemList || null,
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();

    revalidateTag("items")

    return {
      success: true,
      data: result[0] || null,
      error: null
    }
  } catch (err) {
    console.error("아이템 생성 오류:", err)
    
    if (err instanceof Error && err.message.includes("unique constraint")) {
      return {
        success: false,
        message: "이미 존재하는 아이템 코드입니다",
        data: null,
        error: "중복 키 오류"
      }
    }

    return {
      success: false,
      message: getErrorMessage(err),
      data: null,
      error: getErrorMessage(err)
    }
  }
}

/**
 * Excel import를 위한 조선 아이템 생성 함수
 * 하나의 아이템 코드에 대해 여러 선종을 처리 (1:N 관계)
 */
export async function createShipbuildingImportItem(input: {
  itemCode: string;
  workType: '기장' | '전장' | '선실' | '배관' | '철의';
  itemList?: string | null;
  subItemList?: string | null;
  shipTypes?: string | null;
}) {
  unstable_noStore();

  try {
    if (!input.itemCode) {
      return {
        success: false,
        message: "아이템 코드는 필수입니다",
        data: null,
        error: "필수 필드 누락"
      }
    }
    
    // 기존 아이템 및 선종 확인
    const existingItem = await db.select().from(itemShipbuilding)
      .where(
        and(
          eq(itemShipbuilding.itemCode, input.itemCode),
          eq(itemShipbuilding.shipTypes, input.shipTypes || '')
        )
      );
      
    if (existingItem.length > 0) {
      return {
        success: false,
        message: "이미 존재하는 아이템 코드 및 선종입니다",
        data: null,
        error: "중복 키 오류"
      }
    }

    const result = await db.insert(itemShipbuilding).values({
      itemCode: input.itemCode,
      workType: input.workType,
      shipTypes: input.shipTypes || '',
      itemList: input.itemList || '',
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();

    revalidateTag("items");

    return {
      success: true,
      data: result[0],
      error: null
    }
  } catch (err) {
    console.error("조선 아이템 생성 오류:", err);
    
    if (err instanceof Error && err.message.includes("unique constraint")) {
      return {
        success: false,
        message: "이미 존재하는 아이템 코드 및 선종입니다",
        data: null,
        error: "중복 키 오류"
      }
    }

    return {
      success: false,
      message: getErrorMessage(err),
      data: null,
      error: getErrorMessage(err)
    }
  }
}

export async function createOffshoreTopItem(data: OffshoreTopItemCreateData) {
  unstable_noStore()
  
  try {
    if (!data.itemCode) {
      return { 
        success: false, 
        message: "아이템 코드는 필수입니다",
        data: null, 
        error: "필수 필드 누락" 
      }
    }
    
    const result = await db.insert(itemOffshoreTop).values({
      itemCode: data.itemCode,
      workType: data.workType,
      itemList: data.itemList,
      subItemList: data.subItemList,
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();

    revalidateTag("items")

    return {
      success: true,
      data: result[0],
      error: null
    }
  } catch (err) {
    console.error("아이템 생성 오류:", err)
    
    if (err instanceof Error && err.message.includes("unique constraint")) {
      return {
        success: false,
        message: "이미 존재하는 아이템 코드입니다",
        data: null,
        error: "중복 키 오류"
      }
    }

    return {
      success: false,
      message: getErrorMessage(err),
      data: null,
      error: getErrorMessage(err)
    }
  }
}

export async function createOffshoreHullItem(data: OffshoreHullItemCreateData) {
  unstable_noStore()
  
  try {
    if (!data.itemCode) {
      return { 
        success: false, 
        message: "아이템 코드는 필수입니다",
        data: null, 
        error: "필수 필드 누락" 
      }
    }
    
    const result = await db.insert(itemOffshoreHull).values({
      itemCode: data.itemCode,
      workType: data.workType,
      itemList: data.itemList,
      subItemList: data.subItemList,
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();

    revalidateTag("items")

    return {
      success: true,
      data: result[0],
      error: null
    }
  } catch (err) {
    console.error("아이템 생성 오류:", err)
    
    if (err instanceof Error && err.message.includes("unique constraint")) {
      return {
        success: false,
        message: "이미 존재하는 아이템 코드입니다",
        data: null,
        error: "중복 키 오류"
      }
    }

    return {
      success: false,
      message: getErrorMessage(err),
      data: null,
      error: getErrorMessage(err)
    }
  }
}

/* -----------------------------------------------------
   3) 업데이트
----------------------------------------------------- */

// 업데이트 타입 정의 인터페이스
interface UpdateShipbuildingItemInput {
  id: number;
  workType?: string;
  shipTypes?: string;
  itemList?: string;
  itemCode?: string;
}

/** 단건 업데이트 */
export async function modifyShipbuildingItem(input: UpdateShipbuildingItemInput) {
  unstable_noStore();
  try {
    const updateData: Record<string, unknown> = {};
    
    if (input.workType) updateData.workType = input.workType as '기장' | '전장' | '선실' | '배관' | '철의';
    if (input.shipTypes) updateData.shipTypes = input.shipTypes;
    if (input.itemList !== undefined) updateData.itemList = input.itemList;
    if (input.itemCode) updateData.itemCode = input.itemCode;
    
    if (Object.keys(updateData).length > 0) {
      updateData.updatedAt = new Date();
      await db.update(itemShipbuilding)
        .set(updateData)
        .where(eq(itemShipbuilding.id, input.id));
    }

    // 캐시 무효화
    revalidateTag("items");
    
    return { 
      data: { id: input.id }, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 업데이트되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err),
      success: false,
      message: "아이템 업데이트 중 오류가 발생했습니다." 
    };
  }
}

// Offshore TOP 업데이트 타입 정의 인터페이스
interface UpdateOffshoreTopItemInput {
  id: number;
  workType?: string;
  itemList?: string;
  subItemList?: string;
  itemCode?: string;
}

/** Offshore TOP 단건 업데이트 */
export async function modifyOffshoreTopItem(input: UpdateOffshoreTopItemInput) {
  unstable_noStore();
  try {
    const updateData: Record<string, unknown> = {};
    
    if (input.workType) updateData.workType = input.workType as 'TM' | 'TS' | 'TE' | 'TP';
    if (input.itemList !== undefined) updateData.itemList = input.itemList;
    if (input.subItemList !== undefined) updateData.subItemList = input.subItemList;
    if (input.itemCode) updateData.itemCode = input.itemCode;
    
    if (Object.keys(updateData).length > 0) {
      updateData.updatedAt = new Date();
      await db.update(itemOffshoreTop)
        .set(updateData)
        .where(eq(itemOffshoreTop.id, input.id));
    }
    
    // 캐시 무효화
    revalidateTag("items");
    
    return { 
      data: { id: input.id }, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 업데이트되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err),
      success: false,
      message: "아이템 업데이트 중 오류가 발생했습니다." 
    };
  }
}

// Offshore HULL 업데이트 타입 정의 인터페이스
interface UpdateOffshoreHullItemInput {
  id: number;
  workType?: string;
  itemList?: string;
  subItemList?: string;
  itemCode?: string;
}

/** Offshore HULL 단건 업데이트 */
export async function modifyOffshoreHullItem(input: UpdateOffshoreHullItemInput) {
  unstable_noStore();
  try {
    const updateData: Record<string, unknown> = {};
    
    if (input.workType) updateData.workType = input.workType as 'HA' | 'HE' | 'HH' | 'HM' | 'NC';
    if (input.itemList !== undefined) updateData.itemList = input.itemList;
    if (input.subItemList !== undefined) updateData.subItemList = input.subItemList;
    if (input.itemCode) updateData.itemCode = input.itemCode;
    
    if (Object.keys(updateData).length > 0) {
      updateData.updatedAt = new Date();
      await db.update(itemOffshoreHull)
        .set(updateData)
        .where(eq(itemOffshoreHull.id, input.id));
    }
    
    // 캐시 무효화
    revalidateTag("items");
    
    return { 
      data: { id: input.id }, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 업데이트되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err),
      success: false,
      message: "아이템 업데이트 중 오류가 발생했습니다." 
    };
  }
}

/* -----------------------------------------------------
   4) 삭제
----------------------------------------------------- */

// 삭제 타입 정의 인터페이스
interface DeleteItemInput {
  id: number;
}

interface DeleteItemsInput {
  ids: number[];
}

/** 단건 삭제 */
export async function removeShipbuildingItem(input: DeleteItemInput) {
  unstable_noStore();
  try {
    await db.delete(itemShipbuilding)
      .where(eq(itemShipbuilding.id, input.id));

    revalidateTag("items");

    return { 
      data: null, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 삭제되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err), 
      success: false, 
      message: "아이템 삭제 중 오류가 발생했습니다." 
    };
  }
}

/** 복수 삭제 */
export async function removeShipbuildingItems(input: DeleteItemsInput) {
  unstable_noStore();
  try {
    if (input.ids.length > 0) {
      await db.delete(itemShipbuilding)
        .where(inArray(itemShipbuilding.id, input.ids));
    }

    revalidateTag("items");

    return { data: null, error: null, success: true, message: "아이템이 성공적으로 삭제되었습니다." };
  } catch (err) {
    return { data: null, error: getErrorMessage(err), success: false, message: "아이템 삭제 중 오류가 발생했습니다." };
  }
}

/** Offshore TOP 단건 삭제 */
export async function removeOffshoreTopItem(input: DeleteItemInput) {
  unstable_noStore();
  try {
    await db.delete(itemOffshoreTop)
      .where(eq(itemOffshoreTop.id, input.id));

    revalidateTag("items");

    return { 
      data: null, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 삭제되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err), 
      success: false, 
      message: "아이템 삭제 중 오류가 발생했습니다." 
    };
  }
}

/** Offshore TOP 복수 삭제 */
export async function removeOffshoreTopItems(input: DeleteItemsInput) {
  unstable_noStore();
  try {
    if (input.ids.length > 0) {
      await db.delete(itemOffshoreTop)
        .where(inArray(itemOffshoreTop.id, input.ids));
    }

    revalidateTag("items");

    return { data: null, error: null, success: true, message: "아이템이 성공적으로 삭제되었습니다." };
  } catch (err) {
    return { data: null, error: getErrorMessage(err), success: false, message: "아이템 삭제 중 오류가 발생했습니다." };
  }
}

/** Offshore HULL 단건 삭제 */
export async function removeOffshoreHullItem(input: DeleteItemInput) {
  unstable_noStore();
  try {
    await db.delete(itemOffshoreHull)
      .where(eq(itemOffshoreHull.id, input.id));

    revalidateTag("items");

    return { 
      data: null, 
      error: null, 
      success: true, 
      message: "아이템이 성공적으로 삭제되었습니다." 
    };
  } catch (err) {
    return { 
      data: null, 
      error: getErrorMessage(err), 
      success: false, 
      message: "아이템 삭제 중 오류가 발생했습니다." 
    };
  }
}

/** Offshore HULL 복수 삭제 */
export async function removeOffshoreHullItems(input: DeleteItemsInput) {
  unstable_noStore();
  try {
    if (input.ids.length > 0) {
      await db.delete(itemOffshoreHull)
        .where(inArray(itemOffshoreHull.id, input.ids));
    }

    revalidateTag("items");

    return { data: null, error: null, success: true, message: "아이템이 성공적으로 삭제되었습니다." };
  } catch (err) {
    return { data: null, error: getErrorMessage(err), success: false, message: "아이템 삭제 중 오류가 발생했습니다." };
  }
}

/* -----------------------------------------------------
   5) 조회 관련 추가 함수들
----------------------------------------------------- */

// 조선 공종별 아이템 조회
export async function getShipbuildingItemsByWorkType(workType?: ShipbuildingWorkType, shipType?: string) {
  try {
    const query = db
      .select({
        id: itemShipbuilding.id,
        itemCode: itemShipbuilding.itemCode,
        workType: itemShipbuilding.workType,
        itemList: itemShipbuilding.itemList,
        shipTypes: itemShipbuilding.shipTypes,
        createdAt: itemShipbuilding.createdAt,
        updatedAt: itemShipbuilding.updatedAt,
      })
      .from(itemShipbuilding)

    const conditions = []
    if (workType) {
      conditions.push(eq(itemShipbuilding.workType, workType))
    }
    if (shipType) {
      conditions.push(eq(itemShipbuilding.shipTypes, shipType))
    }

    if (conditions.length > 0) {
      query.where(and(...conditions))
    }

    const result = await query

    return {
      data: result as ShipbuildingItem[],
      error: null
    }
  } catch (error) {
    console.error("조선 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 TOP 공종별 아이템 조회
export async function getOffshoreTopItemsByWorkType(workType?: OffshoreTopWorkType) {
  try {
    const query = 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)

    if (workType) {
      query.where(eq(itemOffshoreTop.workType, workType))
    }

    const result = await query

    return {
      data: result as OffshoreTopTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 TOP 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 HULL 공종별 아이템 조회
export async function getOffshoreHullItemsByWorkType(workType?: OffshoreHullWorkType) {
  try {
    const query = 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)

    if (workType) {
      query.where(eq(itemOffshoreHull.workType, workType))
    }

    const result = await query

    return {
      data: result as OffshoreHullTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 HULL 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 아이템 검색
export async function searchShipbuildingItems(searchQuery: string, workType?: ShipbuildingWorkType, shipType?: string) {
  try {
    const searchConditions = [
      ilike(itemShipbuilding.itemCode, `%${searchQuery}%`),
      ilike(itemShipbuilding.itemList, `%${searchQuery}%`),
    ]

    let whereCondition = or(...searchConditions)

    const filterConditions = []
    if (workType) {
      filterConditions.push(eq(itemShipbuilding.workType, workType))
    }
    if (shipType) {
      filterConditions.push(eq(itemShipbuilding.shipTypes, shipType))
    }

    if (filterConditions.length > 0) {
      whereCondition = and(
        and(...filterConditions),
        or(...searchConditions)
      )
    }

    const result = await db
      .select({
        id: itemShipbuilding.id,
        itemCode: itemShipbuilding.itemCode,
        workType: itemShipbuilding.workType,
        itemList: itemShipbuilding.itemList,
        shipTypes: itemShipbuilding.shipTypes,
        createdAt: itemShipbuilding.createdAt,
        updatedAt: itemShipbuilding.updatedAt,
      })
      .from(itemShipbuilding)
      .where(whereCondition)

    return {
      data: result as ShipbuildingItem[],
      error: null
    }
  } catch (error) {
    console.error("조선 아이템 검색 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 TOP 아이템 검색
export async function searchOffshoreTopItems(searchQuery: string, workType?: OffshoreTopWorkType) {
  try {
    const searchConditions = [
      ilike(itemOffshoreTop.itemCode, `%${searchQuery}%`),
      ilike(itemOffshoreTop.itemList, `%${searchQuery}%`),
      ilike(itemOffshoreTop.subItemList, `%${searchQuery}%`)
    ]

    let whereCondition = or(...searchConditions)

    if (workType) {
      whereCondition = and(
        eq(itemOffshoreTop.workType, workType),
        or(...searchConditions)
      )
    }

    const result = 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(whereCondition)

    return {
      data: result as OffshoreTopTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 TOP 아이템 검색 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 HULL 아이템 검색  
export async function searchOffshoreHullItems(searchQuery: string, workType?: OffshoreHullWorkType) {
  try {
    const searchConditions = [
      ilike(itemOffshoreHull.itemCode, `%${searchQuery}%`),
      ilike(itemOffshoreHull.itemList, `%${searchQuery}%`),
      ilike(itemOffshoreHull.subItemList, `%${searchQuery}%`)
    ]

    let whereCondition = or(...searchConditions)

    if (workType) {
      whereCondition = and(
        eq(itemOffshoreHull.workType, workType),
        or(...searchConditions)
      )
    }

    const result = 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(whereCondition)

    return {
      data: result as OffshoreHullTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 HULL 아이템 검색 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 모든 공종 목록 조회
export async function getWorkTypes() {
  return [
    { code: '기장' as ShipbuildingWorkType, name: '기장'},
    { code: '전장' as ShipbuildingWorkType, name: '전장'},
    { code: '선실' as ShipbuildingWorkType, name: '선실'},
    { code: '배관' as ShipbuildingWorkType, name: '배관'},
    { code: '철의' as ShipbuildingWorkType, name: '철의'},
  ]
}

// 해양 TOP 공종 목록 조회
export async function getOffshoreTopWorkTypes() {
  return [
    { code: 'TM' as OffshoreTopWorkType, name: 'TM'},
    { code: 'TS' as OffshoreTopWorkType, name: 'TS'},
    { code: 'TE' as OffshoreTopWorkType, name: 'TE'},
    { code: 'TP' as OffshoreTopWorkType, name: 'TP'},
  ]
}

// 해양 HULL 공종 목록 조회
export async function getOffshoreHullWorkTypes() {
  return [
    { code: 'HA' as OffshoreHullWorkType, name: 'HA'},
    { code: 'HE' as OffshoreHullWorkType, name: 'HE'},
    { code: 'HH' as OffshoreHullWorkType, name: 'HH'},
    { code: 'HM' as OffshoreHullWorkType, name: 'HM'},
    { code: 'NC' as OffshoreHullWorkType, name: 'NC'},
  ]
}

// 특정 아이템 코드들로 아이템 조회
export async function getShipbuildingItemsByCodes(itemCodes: string[]) {
  try {
    const result = await db
      .select({
        id: itemShipbuilding.id,
        itemCode: itemShipbuilding.itemCode,
        workType: itemShipbuilding.workType,
        itemList: itemShipbuilding.itemList,
        shipTypes: itemShipbuilding.shipTypes,
        createdAt: itemShipbuilding.createdAt,
        updatedAt: itemShipbuilding.updatedAt,
      })
      .from(itemShipbuilding)
      .where(
        or(...itemCodes.map(code => eq(itemShipbuilding.itemCode, code)))
      )

    return {
      data: result as ShipbuildingItem[],
      error: null
    }
  } catch (error) {
    console.error("조선 아이템 코드별 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 TOP 아이템 코드별 조회
export async function getOffshoreTopItemsByCodes(itemCodes: string[]) {
  try {
    const result = 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(
        or(...itemCodes.map(code => eq(itemOffshoreTop.itemCode, code)))
      )

    return {
      data: result as OffshoreTopTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 TOP 아이템 코드별 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 해양 HULL 아이템 코드별 조회
export async function getOffshoreHullItemsByCodes(itemCodes: string[]) {
  try {
    const result = 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(
        or(...itemCodes.map(code => eq(itemOffshoreHull.itemCode, code)))
      )

    return {
      data: result as OffshoreHullTechItem[],
      error: null
    }
  } catch (error) {
    console.error("해양 HULL 아이템 코드별 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 전체 조선 아이템 조회 (캐싱용)
export async function getAllShipbuildingItemsForCache() {
  try {
    const result = await db
      .select({
        id: itemShipbuilding.id,
        itemCode: itemShipbuilding.itemCode,
        workType: itemShipbuilding.workType,
        itemList: itemShipbuilding.itemList,
        shipTypes: itemShipbuilding.shipTypes,
        createdAt: itemShipbuilding.createdAt,
        updatedAt: itemShipbuilding.updatedAt,
      })
      .from(itemShipbuilding)

    return {
      data: result as ShipbuildingItem[],
      error: null
    }
  } catch (error) {
    console.error("전체 조선 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 전체 해양 TOP 아이템 조회 (캐싱용)
export async function getAllOffshoreTopItemsForCache() {
  try {
    const result = 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)

    return {
      data: result as OffshoreTopTechItem[],
      error: null
    }
  } catch (error) {
    console.error("전체 해양 TOP 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 전체 해양 HULL 아이템 조회 (캐싱용)
export async function getAllOffshoreHullItemsForCache() {
  try {
    const result = 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)

    return {
      data: result as OffshoreHullTechItem[],
      error: null
    }
  } catch (error) {
    console.error("전체 해양 HULL 아이템 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// 선종 목록 가져오기
export async function getShipTypes() {
  try {
    const result = await db
      .selectDistinct({
        shipTypes: itemShipbuilding.shipTypes
      })
      .from(itemShipbuilding)
      .orderBy(itemShipbuilding.shipTypes)

    return {
      data: result.map(item => item.shipTypes),
      error: null
    }
  } catch (error) {
    console.error("선종 목록 조회 오류:", error)
    return {
      data: null,
      error: error instanceof Error ? error.message : "알 수 없는 오류"
    }
  }
}

// -----------------------------------------------------------
//                    기술영업을 위한 로직 끝
// -----------------------------------------------------------