DROP VIEW "risks"."risks_view";--> statement-breakpoint
CREATE VIEW "risks"."risks_view" AS (select "risks"."risk_events"."id", "risks"."risk_events"."event_type", "risks"."risk_events"."vendor_id", "vendors"."vendor_code", "vendors"."vendor_name", "vendors"."tax_id", "risks"."risk_events"."provider", "risks"."risk_events"."content", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '종합등급' THEN 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at < "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "prev_rating_total", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '종합등급' THEN 
            "risks"."risk_events"."content"
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "cur_rating_total", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '신용등급' THEN 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at < "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "prev_rating_credit", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '신용등급' THEN 
            "risks"."risk_events"."content"
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "cur_rating_credit", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '현금흐름등급' THEN 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at < "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "prev_rating_cashflow", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = '현금흐름등급' THEN 
            "risks"."risk_events"."content"
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "cur_rating_cashflow", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = 'WATCH등급' THEN 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at < "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "prev_rating_watch", 
        CASE 
          WHEN "risks"."risk_events"."event_type" = 'WATCH등급' THEN 
            "risks"."risk_events"."content"
          ELSE 
            COALESCE((
              SELECT content 
              FROM "risks".risk_events re2 
              WHERE re2.vendor_id = "risks"."risk_events"."vendor_id" 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at <= "risks"."risk_events"."occurred_at"
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
       as "cur_rating_watch", "risks"."risk_events"."event_status", "risks"."risk_events"."manager_id", "users"."name", "risks"."risk_events"."admin_comment", "risks"."risk_events"."occurred_at" from "risks"."risk_events" left join "vendors" on "vendors"."id" = "risks"."risk_events"."vendor_id" left join "users" on "users"."id" = "risks"."risk_events"."manager_id");