1 files changed, 113 insertions, 1 deletions
diff --git a/db/schema/avl/avl.ts b/db/schema/avl/avl.ts
index d2aac795..0b983168 100644
--- a/db/schema/avl/avl.ts
+++ b/db/schema/avl/avl.ts
@@ -1,5 +1,6 @@
-import { pgTable, boolean, integer, timestamp, varchar, decimal, json } from "drizzle-orm/pg-core";
+import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView } from "drizzle-orm/pg-core";
 import { createInsertSchema, createSelectSchema } from "drizzle-zod";
+import { sql } from "drizzle-orm";
 
 // AVL 리스트 테이블 (프로젝트 AVL 및 선종별 표준 AVL 리스트)
 export const avlList = pgTable("avl_list", {
@@ -125,8 +126,119 @@ export const selectAvlListSchema = createSelectSchema(avlList);
 export const insertAvlVendorInfoSchema = createInsertSchema(avlVendorInfo);
 export const selectAvlVendorInfoSchema = createSelectSchema(avlVendorInfo);
 
+// AVL 리스트 집계 뷰 - 가장 마지막 revision별로 집계
+export const avlListSummaryView = pgView("avl_list_summary_view", {
+  id: integer("id"),
+  isTemplate: boolean("is_template"),
+  constructionSector: varchar("construction_sector", { length: 10 }),
+  projectCode: varchar("project_code", { length: 50 }),
+  shipType: varchar("ship_type", { length: 50 }),
+  avlKind: varchar("avl_kind", { length: 50 }),
+  htDivision: varchar("ht_division", { length: 10 }),
+  rev: integer("rev"),
+  createdAt: timestamp("created_at"),
+  createdBy: varchar("created_by", { length: 50 }),
+  updatedAt: timestamp("updated_at"),
+  updatedBy: varchar("updated_by", { length: 50 }),
+  pkgCount: integer("pkg_count"),
+  materialGroupCount: integer("material_group_count"),
+  vendorCount: integer("vendor_count"),
+  tierCount: integer("tier_count"),
+  ownerSuggestionCount: integer("owner_suggestion_count"),
+  shiSuggestionCount: integer("shi_suggestion_count"),
+}).as(sql`
+  WITH latest_revisions AS (
+    -- 표준 AVL의 경우: 공사부문, 선종, AVL종류, H/T 구분별 최신 revision 찾기
+    SELECT 
+      construction_sector,
+      ship_type,
+      avl_kind,
+      ht_division,
+      MAX(rev) as max_rev,
+      'standard' as avl_type,
+      NULL::varchar as project_code
+    FROM avl_list 
+    WHERE is_template = true
+    GROUP BY construction_sector, ship_type, avl_kind, ht_division
+    
+    UNION ALL
+    
+    -- 프로젝트 AVL의 경우: 프로젝트 코드별 최신 revision 찾기
+    SELECT 
+      construction_sector,
+      ship_type,
+      avl_kind,
+      ht_division,
+      MAX(rev) as max_rev,
+      'project' as avl_type,
+      project_code
+    FROM avl_list 
+    WHERE is_template = false
+    GROUP BY project_code, construction_sector, ship_type, avl_kind, ht_division
+  ),
+  latest_avl_lists AS (
+    -- 최신 revision의 실제 AVL 리스트 가져오기
+    SELECT 
+      al.*,
+      CASE 
+        WHEN al.is_template = true THEN 'standard'
+        ELSE 'project'
+      END as avl_type
+    FROM avl_list al
+    INNER JOIN latest_revisions lr ON (
+      (al.is_template = true AND lr.avl_type = 'standard' AND
+       al.construction_sector = lr.construction_sector AND
+       al.ship_type = lr.ship_type AND
+       al.avl_kind = lr.avl_kind AND
+       al.ht_division = lr.ht_division AND
+       al.rev = lr.max_rev)
+      OR
+      (al.is_template = false AND lr.avl_type = 'project' AND
+       al.project_code = lr.project_code AND
+       al.rev = lr.max_rev)
+    )
+  )
+  SELECT 
+    al.id,
+    al.is_template,
+    al.construction_sector,
+    al.project_code,
+    al.ship_type,
+    al.avl_kind,
+    al.ht_division,
+    al.rev,
+    al.created_at,
+    al.created_by,
+    al.updated_at,
+    al.updated_by,
+    
+    -- 집계 필드들
+    COALESCE(COUNT(DISTINCT CASE WHEN avi.package_code IS NOT NULL THEN avi.package_code END), 0)::integer as pkg_count,
+    COALESCE(COUNT(DISTINCT CASE WHEN avi.material_group_code IS NOT NULL THEN avi.material_group_code END), 0)::integer as material_group_count,
+    COALESCE(COUNT(DISTINCT CASE WHEN avi.vendor_name IS NOT NULL THEN avi.vendor_name END), 0)::integer as vendor_count,
+    COALESCE(COUNT(DISTINCT CASE WHEN avi.tier IS NOT NULL THEN avi.tier END), 0)::integer as tier_count,
+    COALESCE(SUM(CASE WHEN avi.owner_suggestion = true THEN 1 ELSE 0 END), 0)::integer as owner_suggestion_count,
+    COALESCE(SUM(CASE WHEN avi.shi_suggestion = true THEN 1 ELSE 0 END), 0)::integer as shi_suggestion_count
+    
+  FROM latest_avl_lists al
+  LEFT JOIN avl_vendor_info avi ON al.id = avi.avl_list_id
+  GROUP BY 
+    al.id, al.is_template, al.construction_sector, al.project_code, 
+    al.ship_type, al.avl_kind, al.ht_division, al.rev,
+    al.created_at, al.created_by, al.updated_at, al.updated_by
+  ORDER BY 
+    al.is_template ASC, 
+    al.construction_sector, 
+    al.project_code, 
+    al.ship_type, 
+    al.avl_kind, 
+    al.ht_division,
+    al.rev DESC
+`);
+
 // 타입 추론
 export type AvlList = typeof avlList.$inferSelect;
 export type NewAvlList = typeof avlList.$inferInsert;
 export type AvlVendorInfo = typeof avlVendorInfo.$inferSelect;
 export type NewAvlVendorInfo = typeof avlVendorInfo.$inferInsert;
+export type AvlListSummary = typeof avlListSummaryView.$inferSelect;