path: root/lib/gtc-contract/service.ts

import { unstable_cache } from "next/cache"
import { and, desc, asc, eq, or, ilike, count, max } from "drizzle-orm"
import  db  from "@/db/db"
import { gtcDocuments, type GtcDocument, type GtcDocumentWithRelations } from "@/db/schema/gtc"
import { projects } from "@/db/schema/projects"
import { users } from "@/db/schema/users"
import { filterColumns } from "@/lib/filter-columns"
import type { GetGtcDocumentsSchema, CreateGtcDocumentSchema, UpdateGtcDocumentSchema, CreateNewRevisionSchema } from "./validations"

/**
 * 프로젝트 존재 여부 확인
 */
export async function checkProjectExists(projectId: number): Promise<boolean> {
  const result = await db
    .select({ id: projects.id })
    .from(projects)
    .where(eq(projects.id, projectId))
    .limit(1)

  return result.length > 0
}

/**
 * GTC 문서 관련 뷰/조인 쿼리를 위한 기본 select
 */
function selectGtcDocumentsWithRelations() {
  return db
    .select({
      id: gtcDocuments.id,
      type: gtcDocuments.type,
      projectId: gtcDocuments.projectId,
      revision: gtcDocuments.revision,
      createdAt: gtcDocuments.createdAt,
      createdById: gtcDocuments.createdById,
      updatedAt: gtcDocuments.updatedAt,
      updatedById: gtcDocuments.updatedById,
      editReason: gtcDocuments.editReason,
      isActive: gtcDocuments.isActive,
      // 관계 데이터
      project: {
        id: projects.id,
        code: projects.code,
        name: projects.name,
      },
      createdBy: {
        id: users.id,
        name: users.name,
        email: users.email,
      },
      updatedBy: {
        id: users.id,
        name: users.name,
        email: users.email,
      },
    })
    .from(gtcDocuments)
    .leftJoin(projects, eq(gtcDocuments.projectId, projects.id))
    .leftJoin(users, eq(gtcDocuments.createdById, users.id))
    .leftJoin(users, eq(gtcDocuments.updatedById, users.id))
}

/**
 * GTC 문서 개수 조회
 */
async function countGtcDocuments(tx: any, where: any) {
  const result = await tx
    .select({ count: count() })
    .from(gtcDocuments)
    .where(where)

  return result[0]?.count ?? 0
}

/**
 * GTC 문서 목록 조회 (필터링, 정렬, 페이징 지원)
 */
export async function getGtcDocuments(input: GetGtcDocumentsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage

        // (1) advancedWhere - 고급 필터
        const advancedWhere = filterColumns({
          table: gtcDocuments,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // (2) globalWhere - 전역 검색
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            ilike(gtcDocuments.editReason, s),
            ilike(projects.name, s),
            ilike(projects.code, s)
          )
        }

        // (3) 기본 필터들
        const basicFilters = []
        
        if (input.type && input.type !== "") {
          basicFilters.push(eq(gtcDocuments.type, input.type))
        }
        
        if (input.projectId && input.projectId > 0) {
          basicFilters.push(eq(gtcDocuments.projectId, input.projectId))
        }

        // 활성 문서만 조회 (기본값)
        basicFilters.push(eq(gtcDocuments.isActive, true))

        // (4) 최종 where 조건
        const finalWhere = and(
          advancedWhere,
          globalWhere,
          ...basicFilters
        )

        // (5) 정렬
        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) => {
                const column = gtcDocuments[item.id as keyof typeof gtcDocuments]
                return item.desc ? desc(column) : asc(column)
              })
            : [desc(gtcDocuments.updatedAt)]

        // (6) 데이터 조회
        const { data, total } = await db.transaction(async (tx) => {
          const data = await selectGtcDocumentsWithRelations()
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage)

          const total = await countGtcDocuments(tx, finalWhere)
          return { data, total }
        })

        const pageCount = Math.ceil(total / input.perPage)
        return { data, pageCount }
      } catch (err) {
        console.error("Error fetching GTC documents:", err)
        return { data: [], pageCount: 0 }
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["gtc-documents"],
    }
  )()
}

/**
 * 특정 GTC 문서 조회
 */
export async function getGtcDocumentById(id: number): Promise<GtcDocumentWithRelations | null> {
  const result = await selectGtcDocumentsWithRelations()
    .where(eq(gtcDocuments.id, id))
    .limit(1)

  return result[0] || null
}

/**
 * 다음 리비전 번호 조회
 */
export async function getNextRevision(type: "standard" | "project", projectId?: number): Promise<number> {
  const where = projectId 
    ? and(eq(gtcDocuments.type, type), eq(gtcDocuments.projectId, projectId))
    : and(eq(gtcDocuments.type, type), eq(gtcDocuments.projectId, null))

  const result = await db
    .select({ maxRevision: max(gtcDocuments.revision) })
    .from(gtcDocuments)
    .where(where)

  return (result[0]?.maxRevision ?? -1) + 1
}

/**
 * GTC 문서 생성
 */
export async function createGtcDocument(
  data: CreateGtcDocumentSchema & { createdById: number }
): Promise<GtcDocument> {
  // 리비전 번호가 없는 경우 자동 생성
  if (!data.revision && data.revision !== 0) {
    data.revision = await getNextRevision(data.type, data.projectId || undefined)
  }

  const [newDocument] = await db
    .insert(gtcDocuments)
    .values({
      ...data,
      updatedById: data.createdById, // 생성시에는 생성자와 수정자가 동일
    })
    .returning()

  return newDocument
}

/**
 * GTC 문서 업데이트
 */
export async function updateGtcDocument(
  id: number,
  data: UpdateGtcDocumentSchema & { updatedById: number }
): Promise<GtcDocument | null> {
  const [updatedDocument] = await db
    .update(gtcDocuments)
    .set({
      ...data,
      updatedAt: new Date(),
    })
    .where(eq(gtcDocuments.id, id))
    .returning()

  return updatedDocument || null
}

/**
 * 새 리비전 생성
 */
export async function createNewRevision(
  originalId: number,
  data: CreateNewRevisionSchema & { createdById: number }
): Promise<GtcDocument | null> {
  // 원본 문서 조회
  const original = await getGtcDocumentById(originalId)
  if (!original) {
    throw new Error("Original document not found")
  }

  // 다음 리비전 번호 계산
  const nextRevision = await getNextRevision(
    original.type,
    original.projectId || undefined
  )

  // 새 리비전 생성
  const [newRevision] = await db
    .insert(gtcDocuments)
    .values({
      type: original.type,
      projectId: original.projectId,
      revision: nextRevision,
      editReason: data.editReason,
      createdById: data.createdById,
      updatedById: data.createdById,
    })
    .returning()

  return newRevision
}

/**
 * GTC 문서 삭제 (소프트 삭제)
 */
export async function deleteGtcDocument(
  id: number,
  updatedById: number
): Promise<boolean> {
  const [updated] = await db
    .update(gtcDocuments)
    .set({
      isActive: false,
      updatedById,
      updatedAt: new Date(),
    })
    .where(eq(gtcDocuments.id, id))
    .returning()

  return !!updated
}

/**
 * 프로젝트별 GTC 문서 목록 조회
 */
export async function getGtcDocumentsByProject(projectId: number): Promise<GtcDocumentWithRelations[]> {
  return await selectGtcDocumentsWithRelations()
    .where(
      and(
        eq(gtcDocuments.projectId, projectId),
        eq(gtcDocuments.isActive, true)
      )
    )
    .orderBy(desc(gtcDocuments.revision))
}

/**
 * 표준 GTC 문서 목록 조회
 */
export async function getStandardGtcDocuments(): Promise<GtcDocumentWithRelations[]> {
  return await selectGtcDocumentsWithRelations()
    .where(
      and(
        eq(gtcDocuments.type, "standard"),
        eq(gtcDocuments.isActive, true)
      )
    )
    .orderBy(desc(gtcDocuments.revision))
}

// 타입 정의
export type ProjectForFilter = {
  id: number
  code: string
  name: string
}

export type UserForFilter = {
  id: number
  name: string
  email: string
}

/**
 * 프로젝트 목록 조회 (필터용)
 */
export async function getProjectsForFilter(): Promise<ProjectForFilter[]> {
  return await db
    .select({
      id: projects.id,
      code: projects.code,
      name: projects.name,
    })
    .from(projects)
    .orderBy(projects.name)
}

/**
 * 프로젝트 목록 조회 (선택용)
 */
export async function getProjectsForSelect(): Promise<ProjectForFilter[]> {
  return await db
    .select({
      id: projects.id,
      code: projects.code,
      name: projects.name,
    })
    .from(projects)
    .orderBy(projects.name)
}

/**
 * 사용자 목록 조회 (필터용)
 */
export async function getUsersForFilter(): Promise<UserForFilter[]> {
  return await db
    .select({
      id: users.id,
      name: users.name,
      email: users.email,
    })
    .from(users)
    .where(eq(users.isActive, true)) // 활성 사용자만
    .orderBy(users.name)
}