From aa86729f9a2ab95346a2851e3837de1c367aae17 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Fri, 20 Jun 2025 11:37:31 +0000
Subject: (대표님) 20250620 작업사항
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/schema/projectGtc.ts | 45 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 45 insertions(+)
 create mode 100644 db/schema/projectGtc.ts

(limited to 'db/schema/projectGtc.ts')

diff --git a/db/schema/projectGtc.ts b/db/schema/projectGtc.ts
new file mode 100644
index 00000000..220e42df
--- /dev/null
+++ b/db/schema/projectGtc.ts
@@ -0,0 +1,45 @@
+import { pgTable, pgView, timestamp, integer, varchar, serial } from 'drizzle-orm/pg-core';
+import { projects } from './projects';
+import { eq, sql } from "drizzle-orm";
+
+export const projectGtcFiles = pgTable('project_gtc_files', {
+  id: serial("id").primaryKey(),
+  projectId: integer('project_id').references(() => projects.id).notNull(),
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+  fileSize: integer('file_size'),
+  mimeType: varchar("mime_type", { length: 100 }),
+  createdAt: timestamp('created_at').defaultNow().notNull(),
+  updatedAt: timestamp('updated_at').defaultNow().notNull(),
+});
+
+// Project GTC 뷰 (프로젝트 정보와 파일 정보를 조인)
+export const projectGtcView = pgView('project_gtc_view').as((qb) => {
+  return qb
+    .select({
+      // 프로젝트 정보
+      id: sql<number>`${projects.id}`.as('id'),
+      code: sql<string>`${projects.code}`.as('code'),
+      name: sql<string>`${projects.name}`.as('name'),
+      type: sql<string>`${projects.type}`.as('type'),
+      projectCreatedAt: sql<Date>`${projects.createdAt}`.as('project_created_at'),
+      projectUpdatedAt: sql<Date>`${projects.updatedAt}`.as('project_updated_at'),
+      
+      // GTC 파일 정보
+      gtcFileId: sql<number | null>`${projectGtcFiles.id}`.as('gtc_file_id'),
+      fileName: sql<string | null>`${projectGtcFiles.fileName}`.as('fileName'),
+      filePath: sql<string | null>`${projectGtcFiles.filePath}`.as('filePath'),
+      originalFileName: sql<string | null>`${projectGtcFiles.originalFileName}`.as('originalFileName'),
+      fileSize: sql<number | null>`${projectGtcFiles.fileSize}`.as('fileSize'),
+      mimeType: sql<string | null>`${projectGtcFiles.mimeType}`.as('mimeType'),
+      gtcCreatedAt: sql<Date | null>`${projectGtcFiles.createdAt}`.as('gtcCreatedAt'),
+      gtcUpdatedAt: sql<Date | null>`${projectGtcFiles.updatedAt}`.as('gtcUpdatedAt'),
+    })
+    .from(projects)
+    .leftJoin(projectGtcFiles, eq(projects.id, projectGtcFiles.projectId));
+});
+
+// 타입 정의
+export type ProjectGtcFile = typeof projectGtcFiles.$inferSelect;
+export type ProjectGtcView = typeof projectGtcView.$inferSelect; 
\ No newline at end of file
-- 
cgit v1.2.3