(고건) 리스크 관리 테이블 내 등급 정보 추가

1 files changed, 165 insertions, 4 deletions

diff --git a/db/schema/risks/risks.ts b/db/schema/risks/risks.ts
index 7be18776..0a4366d6 100644
--- a/db/schema/risks/risks.ts
+++ b/db/schema/risks/risks.ts
@@ -8,7 +8,7 @@ import {
   timestamp,
   varchar,
 } from 'drizzle-orm/pg-core';
-import { eq, relations } from 'drizzle-orm';
+import { eq, relations, sql } from 'drizzle-orm';
 import { users } from '../users';
 import { vendors } from '../vendors';
 
@@ -40,8 +40,9 @@ const riskEvents = risksSchema.table('risk_events', {
 // ----------------------------------------------------------------------------------------------------
 
 /* VIEWS */
-const risksView = risksSchema.view('risks_view').as((qb) =>
-  qb
+const risksView = risksSchema.view('risks_view').as((qb) => {
+
+  return qb
     .select({
       id: riskEvents.id,
       eventType: riskEvents.eventType,
@@ -51,6 +52,166 @@ const risksView = risksSchema.view('risks_view').as((qb) =>
       businessNumber: vendors.taxId,
       provider: riskEvents.provider,
       content: riskEvents.content,
+      prevRatingTotal: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '종합등급' THEN 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '종합등급' 
+                AND re2.occurred_at < ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '종합등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('prev_rating_total'),
+      curRatingTotal: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '종합등급' THEN 
+            ${riskEvents.content}
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '종합등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('cur_rating_total'),
+      prevRatingCredit: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '신용등급' THEN 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '신용등급' 
+                AND re2.occurred_at < ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '신용등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('prev_rating_credit'),
+      curRatingCredit: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '신용등급' THEN 
+            ${riskEvents.content}
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '신용등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('cur_rating_credit'),
+      prevRatingCashflow: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '현금흐름등급' THEN 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '현금흐름등급' 
+                AND re2.occurred_at < ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '현금흐름등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('prev_rating_cashflow'),
+      curRatingCashflow: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = '현금흐름등급' THEN 
+            ${riskEvents.content}
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = '현금흐름등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('cur_rating_cashflow'),
+      prevRatingWatch: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = 'WATCH등급' THEN 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = 'WATCH등급' 
+                AND re2.occurred_at < ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = 'WATCH등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('prev_rating_watch'),
+      curRatingWatch: sql`
+        CASE 
+          WHEN ${riskEvents.eventType} = 'WATCH등급' THEN 
+            ${riskEvents.content}
+          ELSE 
+            COALESCE((
+              SELECT content 
+              FROM ${risksSchema}.risk_events re2 
+              WHERE re2.vendor_id = ${riskEvents.vendorId} 
+                AND re2.event_type = 'WATCH등급' 
+                AND re2.occurred_at <= ${riskEvents.occuredAt}
+              ORDER BY re2.occurred_at DESC 
+              LIMIT 1
+            ), '-')
+        END
+      `.as('cur_rating_watch'),
       eventStatus: riskEvents.eventStatus,
       managerId: riskEvents.managerId,
       managerName: users.name,
@@ -60,7 +221,7 @@ const risksView = risksSchema.view('risks_view').as((qb) =>
     .from(riskEvents)
     .leftJoin(vendors, eq(vendors.id, riskEvents.vendorId))
     .leftJoin(users, eq(users.id, riskEvents.managerId))
-);
+});
 
 // ----------------------------------------------------------------------------------------------------