diff options
Diffstat (limited to 'db/schema/gtc.ts')
| -rw-r--r-- | db/schema/gtc.ts | 174 |
1 files changed, 171 insertions, 3 deletions
diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts index a4052d61..281b6af9 100644 --- a/db/schema/gtc.ts +++ b/db/schema/gtc.ts @@ -8,9 +8,9 @@ import { pgEnum, boolean, index, - uniqueIndex + uniqueIndex, pgView } from "drizzle-orm/pg-core" - import { relations } from "drizzle-orm" + import { relations ,sql, eq} from "drizzle-orm" import { projects } from "./projects" import { users } from "./users" @@ -107,4 +107,172 @@ import { users } from "./users" id: number name: string } - }
\ No newline at end of file + } + + + export const gtcDocumentsView = pgView('gtc_documents_view').as((qb) => + qb + .select({ + // GTC 문서 기본 필드 + id: gtcDocuments.id, + type: gtcDocuments.type, + projectId: gtcDocuments.projectId, + revision: gtcDocuments.revision, + fileName: gtcDocuments.fileName, + filePath: gtcDocuments.filePath, + fileSize: gtcDocuments.fileSize, + createdAt: gtcDocuments.createdAt, + createdById: gtcDocuments.createdById, + updatedAt: gtcDocuments.updatedAt, + updatedById: gtcDocuments.updatedById, + editReason: gtcDocuments.editReason, + isActive: gtcDocuments.isActive, + + // 프로젝트 정보 + projectCode: projects.code, + projectName: projects.name, + + // 생성자 정보 + createdByName: sql<string>`created_by_user.name`.as('created_by_name'), + createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'), + + // 수정자 정보 + updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'), + updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'), + + // 집계 필드들 + // 같은 타입/프로젝트의 총 문서 수 + totalDocumentsInGroup: sql<number>` + ( + SELECT count(*) + FROM gtc_documents gd2 + WHERE gd2.type = ${gtcDocuments.type} + AND gd2.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd2.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd2.project_id IS NULL) + ) + ) + `.as('total_documents_in_group'), + + // 최신 리비전 번호 + latestRevision: sql<number>` + ( + SELECT max(revision) + FROM gtc_documents gd3 + WHERE gd3.type = ${gtcDocuments.type} + AND gd3.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd3.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd3.project_id IS NULL) + ) + ) + `.as('latest_revision'), + + // 현재 문서가 최신인지 여부 + isLatestRevision: sql<boolean>` + ${gtcDocuments.revision} = ( + SELECT max(revision) + FROM gtc_documents gd4 + WHERE gd4.type = ${gtcDocuments.type} + AND gd4.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd4.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd4.project_id IS NULL) + ) + ) + `.as('is_latest_revision'), + + // 이전 리비전 ID (있다면) + previousRevisionId: sql<number>` + ( + SELECT id + FROM gtc_documents gd5 + WHERE gd5.type = ${gtcDocuments.type} + AND gd5.is_active = true + AND gd5.revision < ${gtcDocuments.revision} + AND ( + (${gtcDocuments.type} = 'project' AND gd5.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd5.project_id IS NULL) + ) + ORDER BY gd5.revision DESC + LIMIT 1 + ) + `.as('previous_revision_id'), + + // 다음 리비전 ID (있다면) + nextRevisionId: sql<number>` + ( + SELECT id + FROM gtc_documents gd6 + WHERE gd6.type = ${gtcDocuments.type} + AND gd6.is_active = true + AND gd6.revision > ${gtcDocuments.revision} + AND ( + (${gtcDocuments.type} = 'project' AND gd6.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd6.project_id IS NULL) + ) + ORDER BY gd6.revision ASC + LIMIT 1 + ) + `.as('next_revision_id'), + + // 파일 크기 (사람이 읽기 쉬운 형태) + fileSizeFormatted: sql<string>` + CASE + WHEN ${gtcDocuments.fileSize} IS NULL THEN NULL + WHEN ${gtcDocuments.fileSize} < 1024 THEN ${gtcDocuments.fileSize} || ' B' + WHEN ${gtcDocuments.fileSize} < 1024 * 1024 THEN round(${gtcDocuments.fileSize} / 1024.0, 1) || ' KB' + WHEN ${gtcDocuments.fileSize} < 1024 * 1024 * 1024 THEN round(${gtcDocuments.fileSize} / (1024.0 * 1024), 1) || ' MB' + ELSE round(${gtcDocuments.fileSize} / (1024.0 * 1024 * 1024), 1) || ' GB' + END + `.as('file_size_formatted'), + + // 프로젝트가 있는 경우, 해당 프로젝트의 총 GTC 문서 수 + projectTotalDocuments: sql<number>` + CASE + WHEN ${gtcDocuments.projectId} IS NOT NULL THEN ( + SELECT count(*) + FROM gtc_documents gd7 + WHERE gd7.project_id = ${gtcDocuments.projectId} + AND gd7.is_active = true + ) + ELSE NULL + END + `.as('project_total_documents'), + + // 리비전 히스토리 (배열로 반환) + revisionHistory: sql<number[]>` + ( + SELECT array_agg(revision ORDER BY revision) + FROM gtc_documents gd8 + WHERE gd8.type = ${gtcDocuments.type} + AND gd8.is_active = true + AND ( + (${gtcDocuments.type} = 'project' AND gd8.project_id = ${gtcDocuments.projectId}) OR + (${gtcDocuments.type} = 'standard' AND gd8.project_id IS NULL) + ) + ) + `.as('revision_history'), + + // 수정 이력이 있는지 여부 + hasEditHistory: sql<boolean>` + ${gtcDocuments.createdById} != ${gtcDocuments.updatedById} OR + ${gtcDocuments.createdAt} != ${gtcDocuments.updatedAt} + `.as('has_edit_history'), + }) + .from(gtcDocuments) + .leftJoin(projects, sql`${gtcDocuments.projectId} = ${projects.id}`) + .leftJoin( + sql`users created_by_user`, + sql`${gtcDocuments.createdById} = created_by_user.id` + ) + .leftJoin( + sql`users updated_by_user`, + sql`${gtcDocuments.updatedById} = updated_by_user.id` + ) + ); + + export type GtcDocumentView = typeof gtcDocumentsView.$inferSelect; + +
\ No newline at end of file |