'use server'

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, gtcDocumentsView, 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 문서 개수 조회
 */
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: gtcDocumentsView,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

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


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

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

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

          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"],
    }
  )()
}

// 성공한 ID들을 반환하는 버전
export async function deleteGtcDocuments(
  ids: number[],
  updatedById: number
): Promise<number[]> {
  if (ids.length === 0) {
    return [];
  }

  const updated = await db
    .update(gtcDocuments)
    .set({
      isActive: false,
      updatedById,
      updatedAt: new Date(),
    })
    .where(inArray(gtcDocuments.id, ids))
    .returning({ id: gtcDocuments.id });

  return updated.map(doc => doc.id);
}
/**
 * 다음 리비전 번호 조회
 */
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)
}