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
);