DROP VIEW "public"."periodic_evaluations_view";--> statement-breakpoint
ALTER TABLE "tags" ADD COLUMN "tag_class_id" integer;--> statement-breakpoint
ALTER TABLE "tags" ADD CONSTRAINT "tags_tag_class_id_tag_classes_id_fk" FOREIGN KEY ("tag_class_id") REFERENCES "public"."tag_classes"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."periodic_evaluations_view" AS (select "periodic_evaluations"."id", "periodic_evaluations"."evaluation_target_id", "evaluation_targets"."evaluation_year", "evaluation_targets"."division", "evaluation_targets"."vendor_id", "evaluation_targets"."vendor_code", "evaluation_targets"."vendor_name", "evaluation_targets"."domestic_foreign", "evaluation_targets"."material_type", "periodic_evaluations"."evaluation_period", "periodic_evaluations"."documents_submitted", "periodic_evaluations"."submission_date", "periodic_evaluations"."submission_deadline", "periodic_evaluations"."final_score", "periodic_evaluations"."final_grade", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'processScore'
        AND re.is_completed = true
      ) as "process_score", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'priceScore'
        AND re.is_completed = true
      ) as "price_score", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'deliveryScore'
        AND re.is_completed = true
      ) as "delivery_score", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'selfEvaluationScore'
        AND re.is_completed = true
      ) as "self_evaluation_score", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'bonus'
        AND re.is_completed = true
      ) as "participation_bonus", (
        SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
        FROM "reviewer_evaluation_details" red
        JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id
        JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id
        JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND rec.category2 = 'penalty'
        AND re.is_completed = true
      ) as "quality_deduction", "periodic_evaluations"."status", "periodic_evaluations"."review_completed_at", "periodic_evaluations"."finalized_at", "periodic_evaluations"."finalized_by", "periodic_evaluations"."evaluation_note", "periodic_evaluations"."created_at", "periodic_evaluations"."updated_at", "evaluation_targets"."admin_comment", "evaluation_targets"."consolidated_comment", "evaluation_targets"."consensus_status", "evaluation_targets"."confirmed_at", (
        SELECT CASE
          WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
          WHEN re.is_completed = true THEN 'COMPLETED'
          WHEN (
            SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
            FROM "reviewer_evaluation_details" red
            WHERE red.reviewer_evaluation_id = re.id
          ) > 0 THEN 'IN_PROGRESS'
          ELSE 'NOT_STARTED'
        END
        FROM "reviewer_evaluations" re
        JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND etr.department_code = 'ORDER_EVAL'
        LIMIT 1
      ) as "order_eval_status", (
        SELECT CASE
          WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
          WHEN re.is_completed = true THEN 'COMPLETED'
          WHEN (
            SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
            FROM "reviewer_evaluation_details" red
            WHERE red.reviewer_evaluation_id = re.id
          ) > 0 THEN 'IN_PROGRESS'
          ELSE 'NOT_STARTED'
        END
        FROM "reviewer_evaluations" re
        JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND etr.department_code = 'PROCUREMENT_EVAL'
        LIMIT 1
      ) as "procurement_eval_status", (
        SELECT CASE
          WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
          WHEN re.is_completed = true THEN 'COMPLETED'
          WHEN (
            SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
            FROM "reviewer_evaluation_details" red
            WHERE red.reviewer_evaluation_id = re.id
          ) > 0 THEN 'IN_PROGRESS'
          ELSE 'NOT_STARTED'
        END
        FROM "reviewer_evaluations" re
        JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND etr.department_code = 'QUALITY_EVAL'
        LIMIT 1
      ) as "quality_eval_status", (
        SELECT CASE
          WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
          WHEN re.is_completed = true THEN 'COMPLETED'
          WHEN (
            SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
            FROM "reviewer_evaluation_details" red
            WHERE red.reviewer_evaluation_id = re.id
          ) > 0 THEN 'IN_PROGRESS'
          ELSE 'NOT_STARTED'
        END
        FROM "reviewer_evaluations" re
        JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND etr.department_code = 'DESIGN_EVAL'
        LIMIT 1
      ) as "design_eval_status", (
        SELECT CASE
          WHEN re.id IS NULL THEN 'NOT_ASSIGNED'
          WHEN re.is_completed = true THEN 'COMPLETED'
          WHEN (
            SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0)
            FROM "reviewer_evaluation_details" red
            WHERE red.reviewer_evaluation_id = re.id
          ) > 0 THEN 'IN_PROGRESS'
          ELSE 'NOT_STARTED'
        END
        FROM "reviewer_evaluations" re
        JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND etr.department_code = 'CS_EVAL'
        LIMIT 1
      ) as "cs_eval_status", (
        SELECT COUNT(*)::int
        FROM "reviewer_evaluations" re
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
      ) as "total_reviewers", (
        SELECT COUNT(*)::int
        FROM "reviewer_evaluations" re
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND re.is_completed = true
      ) as "completed_reviewers", (
        SELECT COUNT(*)::int
        FROM "reviewer_evaluations" re
        WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
        AND re.is_completed = false
      ) as "pending_reviewers", "users"."name", "users"."email" from "periodic_evaluations" left join "evaluation_targets" on "periodic_evaluations"."evaluation_target_id" = "evaluation_targets"."id" left join "users" on "periodic_evaluations"."finalized_by" = "users"."id" order by "periodic_evaluations"."created_at");