(대표님) 20250620 작업사항

1 files changed, 45 insertions, 0 deletions

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