(대표님) 파일관리변경, 클라IP추적, 실시간알림, 미들웨어변경, 알림API

3 files changed, 104 insertions, 2 deletions

diff --git a/db/schema/history.ts b/db/schema/history.ts
index 13b00196..ad5ac858 100644
--- a/db/schema/history.ts
+++ b/db/schema/history.ts
@@ -6,7 +6,7 @@ import {
     text,
     boolean,
     integer,
-    inet
+    inet, serial
 } from 'drizzle-orm/pg-core';
 import { relations } from 'drizzle-orm';
 import { users } from './users';
@@ -110,4 +110,40 @@ export type PageVisit = typeof pageVisits.$inferSelect;
 export type NewPageVisit = typeof pageVisits.$inferInsert;
 
 export type DailyAccessStats = typeof dailyAccessStats.$inferSelect;
-export type NewDailyAccessStats = typeof dailyAccessStats.$inferInsert;
\ No newline at end of file
+export type NewDailyAccessStats = typeof dailyAccessStats.$inferInsert;
+
+
+export const fileDownloadLogs = pgTable('file_download_logs', {
+    id: serial('id').primaryKey(),
+    fileId: integer('file_id').notNull(),
+    userId: varchar('user_id', { length: 50 }).notNull(),
+    userEmail: varchar('user_email', { length: 255 }),
+    userName: varchar('user_name', { length: 100 }),
+    userRole: varchar('user_role', { length: 50 }),
+    userIP: inet('user_ip'), // PostgreSQL의 inet 타입 사용 (IPv4/IPv6 지원)
+    userAgent: text('user_agent'),
+    fileName: varchar('file_name', { length: 255 }),
+    filePath: varchar('file_path', { length: 500 }),
+    fileSize: integer('file_size'),
+    downloadedAt: timestamp('downloaded_at', { withTimezone: true }).defaultNow().notNull(),
+    success: boolean('success').notNull(),
+    errorMessage: text('error_message'),
+    sessionId: varchar('session_id', { length: 100 }),
+    requestId: varchar('request_id', { length: 50 }), // 요청 추적용
+    referer: text('referer'),
+    downloadDurationMs: integer('download_duration_ms'), // 다운로드 소요 시간
+  });
+  
+  // 사용자별 다운로드 통계 테이블 (선택사항)
+  export const userDownloadStats = pgTable('user_download_stats', {
+    id: serial('id').primaryKey(),
+    userId: varchar('user_id', { length: 50 }).notNull(),
+    date: timestamp('date', { withTimezone: true }).notNull(),
+    totalDownloads: integer('total_downloads').default(0).notNull(),
+    totalBytes: integer('total_bytes').default(0).notNull(),
+    uniqueFiles: integer('unique_files').default(0).notNull(),
+    lastDownloadAt: timestamp('last_download_at', { withTimezone: true }),
+  });
+  
+ 
+  
\ No newline at end of file
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 387bba8c..b1d9ee89 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -27,6 +27,7 @@ export * from './information';
 export * from './qna';
 export * from './notice';
 export * from './history';
+export * from './notification';
 
 // MDG SOAP 수신용
 export * from './MDG/mdg'
diff --git a/db/schema/notification.ts b/db/schema/notification.ts
new file mode 100644
index 00000000..8b745ac2
--- /dev/null
+++ b/db/schema/notification.ts
@@ -0,0 +1,65 @@
+// db/schema/notifications.ts
+import { 
+    pgTable, 
+    uuid, 
+    varchar, 
+    text, 
+    boolean, 
+    timestamp, 
+    index 
+  } from 'drizzle-orm/pg-core';
+  import { users } from './users'; // 기존 users 테이블
+  import { relations } from 'drizzle-orm';
+  
+  export const notifications = pgTable('notifications', {
+    id: uuid('id').primaryKey().defaultRandom(),
+    userId: varchar('user_id', { length: 255 }).notNull(),
+    title: varchar('title', { length: 255 }).notNull(),
+    message: text('message').notNull(),
+    type: varchar('type', { length: 50 }).notNull(), // 'assignment', 'update', 'reminder' 등
+    relatedRecordId: varchar('related_record_id', { length: 255 }),
+    relatedRecordType: varchar('related_record_type', { length: 100 }), // 'project', 'task', 'order' 등
+    isRead: boolean('is_read').default(false).notNull(),
+    createdAt: timestamp('created_at').defaultNow().notNull(),
+    readAt: timestamp('read_at'),
+  }, (table) => ({
+    userIdIdx: index('idx_notifications_user_id').on(table.userId),
+    createdAtIdx: index('idx_notifications_created_at').on(table.createdAt.desc()),
+    isReadIdx: index('idx_notifications_is_read').on(table.isRead),
+    userReadIdx: index('idx_notifications_user_read').on(table.userId, table.isRead),
+  }));
+  
+  // 관계 정의
+  export const notificationsRelations = relations(notifications, ({ one }) => ({
+    user: one(users, {
+      fields: [notifications.userId],
+      references: [users.id],
+    }),
+  }));
+  
+  // 타입 정의
+  export type Notification = typeof notifications.$inferSelect;
+  export type NewNotification = typeof notifications.$inferInsert;
+  
+  // 알림 타입 enum
+  export const NotificationType = {
+    ASSIGNMENT: 'assignment',
+    UPDATE: 'update', 
+    REMINDER: 'reminder',
+    APPROVAL: 'approval',
+    DEADLINE: 'deadline',
+    STATUS_CHANGE: 'status_change'
+  } as const;
+  
+  export type NotificationTypeValue = typeof NotificationType[keyof typeof NotificationType];
+  
+  // 관련 레코드 타입 enum
+  export const RelatedRecordType = {
+    PROJECT: 'project',
+    TASK: 'task', 
+    ORDER: 'order',
+    DOCUMENT: 'document',
+    USER: 'user'
+  } as const;
+  
+  export type RelatedRecordTypeValue = typeof RelatedRecordType[keyof typeof RelatedRecordType];
\ No newline at end of file