(공통) 0919 DB Migrations

1 files changed, 89 insertions, 0 deletions

diff --git a/db/migrations/0373_fuzzy_quasimodo.sql b/db/migrations/0373_fuzzy_quasimodo.sql
new file mode 100644
index 00000000..9db89eb2
--- /dev/null
+++ b/db/migrations/0373_fuzzy_quasimodo.sql
@@ -0,0 +1,89 @@
+CREATE VIEW "public"."avl_list_summary_view" AS (
+  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
+);
\ No newline at end of file