DROP VIEW "public"."periodic_evaluations_aggregated_view";--> statement-breakpoint
CREATE VIEW "public"."periodic_evaluations_aggregated_view" AS (select CONCAT("evaluation_year", '_', "vendor_id") as "id", "evaluation_year", "vendor_id", "vendor_code", "vendor_name", "domestic_foreign", "material_type", ROUND(AVG(NULLIF("process_score", 0)), 1) as "process_score", ROUND(AVG(NULLIF("price_score", 0)), 1) as "price_score", ROUND(AVG(NULLIF("delivery_score", 0)), 1) as "delivery_score", ROUND(AVG(NULLIF("self_evaluation_score", 0)), 1) as "self_evaluation_score", ROUND(AVG(NULLIF("participation_bonus", 0)), 1) as "participation_bonus", ROUND(AVG(NULLIF("quality_deduction", 0)), 1) as "quality_deduction", ROUND(AVG(NULLIF("final_score", 0)), 1) as "final_score", 
        CASE 
          WHEN (
            AVG(NULLIF("process_score", 0)) + 
            AVG(NULLIF("price_score", 0)) + 
            AVG(NULLIF("delivery_score", 0)) + 
            AVG(NULLIF("self_evaluation_score", 0)) +
            AVG(NULLIF("participation_bonus", 0)) - 
            AVG(NULLIF("quality_deduction", 0))
          ) >= 90 THEN 'S'
          WHEN (
            AVG(NULLIF("process_score", 0)) + 
            AVG(NULLIF("price_score", 0)) + 
            AVG(NULLIF("delivery_score", 0)) + 
            AVG(NULLIF("self_evaluation_score", 0)) +
            AVG(NULLIF("participation_bonus", 0)) - 
            AVG(NULLIF("quality_deduction", 0))
          ) >= 80 THEN 'A'
          WHEN (
            AVG(NULLIF("process_score", 0)) + 
            AVG(NULLIF("price_score", 0)) + 
            AVG(NULLIF("delivery_score", 0)) + 
            AVG(NULLIF("self_evaluation_score", 0)) +
            AVG(NULLIF("participation_bonus", 0)) - 
            AVG(NULLIF("quality_deduction", 0))
          ) >= 70 THEN 'B'
          WHEN (
            AVG(NULLIF("process_score", 0)) + 
            AVG(NULLIF("price_score", 0)) + 
            AVG(NULLIF("delivery_score", 0)) + 
            AVG(NULLIF("self_evaluation_score", 0)) +
            AVG(NULLIF("participation_bonus", 0)) - 
            AVG(NULLIF("quality_deduction", 0))
          ) >= 60 THEN 'C'
          ELSE 'D'
        END
       as "evaluation_grade", 
        CASE 
          WHEN AVG(NULLIF("final_score", 0)) >= 90 THEN 'S'
          WHEN AVG(NULLIF("final_score", 0)) >= 80 THEN 'A'
          WHEN AVG(NULLIF("final_score", 0)) >= 70 THEN 'B'
          WHEN AVG(NULLIF("final_score", 0)) >= 60 THEN 'C'
          ELSE 'D'
        END
       as "final_grade", 
        CASE 
          WHEN COUNT(CASE WHEN "status" = 'FINALIZED' THEN 1 END) = COUNT(*) THEN 'FINALIZED'
          WHEN COUNT(CASE WHEN "status" IN ('REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) = COUNT(*) THEN 'REVIEW_COMPLETED'
          WHEN COUNT(CASE WHEN "status" IN ('IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'IN_REVIEW'
          WHEN COUNT(CASE WHEN "status" IN ('SUBMITTED', 'IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'SUBMITTED'
          ELSE 'PENDING_SUBMISSION'
        END
       as "status", 
        CASE 
          WHEN COUNT(CASE WHEN "order_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "order_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "order_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "order_eval_status", 
        CASE 
          WHEN COUNT(CASE WHEN "procurement_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "procurement_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "procurement_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "procurement_eval_status", 
        CASE 
          WHEN COUNT(CASE WHEN "quality_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "quality_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "quality_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "quality_eval_status", 
        CASE 
          WHEN COUNT(CASE WHEN "design_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "design_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "design_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "design_eval_status", 
        CASE 
          WHEN COUNT(CASE WHEN "cs_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "cs_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "cs_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "cs_eval_status", 
        CASE 
          WHEN COUNT(CASE WHEN "admin_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
          WHEN COUNT(CASE WHEN "admin_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
          WHEN COUNT(CASE WHEN "admin_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
          ELSE 'NOT_ASSIGNED'
        END
       as "admin_eval_status", 
        BOOL_AND("documents_submitted")
       as "documents_submitted", MAX("submission_date") as "submission_date", MAX("submission_deadline") as "submission_deadline", MAX("review_completed_at") as "review_completed_at", MAX("finalized_at") as "finalized_at", 
        CASE 
          WHEN COUNT(DISTINCT "division") > 1 THEN 'BOTH'
          ELSE MAX("division")
        END
       as "division", COUNT(*)::int as "evaluation_count", STRING_AGG(DISTINCT "division", ',') as "divisions", SUM("total_reviewers")::int as "total_reviewers", SUM("completed_reviewers")::int as "completed_reviewers", SUM("pending_reviewers")::int as "pending_reviewers", MAX("evaluation_period") as "evaluation_period", STRING_AGG("evaluation_note", ' | ') as "evaluation_note", (ARRAY_AGG("periodic_evaluations_view"."finalized_by" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by", (ARRAY_AGG("periodic_evaluations_view"."name" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by_user_name", (ARRAY_AGG("periodic_evaluations_view"."email" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by_user_email", MIN("created_at") as "created_at", MAX("updated_at") as "updated_at", (ARRAY_AGG("periodic_evaluations_view"."evaluation_target_id"))[1] as "evaluation_target_id", 
        STRING_AGG(DISTINCT "admin_comment", ' | ')
       as "evaluation_target_admin_comment", 
        STRING_AGG(DISTINCT "consolidated_comment", ' | ')
       as "evaluation_target_consolidated_comment", (ARRAY_AGG("periodic_evaluations_view"."consensus_status" ORDER BY "periodic_evaluations_view"."updated_at" DESC NULLS LAST))[1] as "evaluation_target_consensus_status", 
        MAX("confirmed_at")
       as "evaluation_target_confirmed_at" from "periodic_evaluations_view" group by "periodic_evaluations_view"."evaluation_year", "periodic_evaluations_view"."vendor_id", "periodic_evaluations_view"."vendor_code", "periodic_evaluations_view"."vendor_name", "periodic_evaluations_view"."domestic_foreign", "periodic_evaluations_view"."material_type");