ALTER TABLE "evaluation_target_reviewers" ADD COLUMN "department_name_from" varchar(50);--> statement-breakpoint
CREATE VIEW "public"."evaluation_targets_with_departments" AS (select "evaluation_targets"."id", "evaluation_targets"."evaluation_year", "evaluation_targets"."division", "evaluation_targets"."vendor_code", "evaluation_targets"."vendor_name", "evaluation_targets"."domestic_foreign", "evaluation_targets"."material_type", "evaluation_targets"."status", "evaluation_targets"."consensus_status", "evaluation_targets"."admin_comment", "evaluation_targets"."consolidated_comment", "evaluation_targets"."confirmed_at", "evaluation_targets"."confirmed_by", "evaluation_targets"."ld_claim_count", "evaluation_targets"."ld_claim_amount", "evaluation_targets"."ld_claim_currency", "evaluation_targets"."created_at", "evaluation_targets"."updated_at", order_reviewer.name as "order_reviewer_name", order_reviewer.email as "order_reviewer_email", order_etr.department_name_from as "order_department_name", order_review.is_approved as "order_is_approved", order_review.reviewed_at as "order_reviewed_at", procurement_reviewer.name as "procurement_reviewer_name", procurement_reviewer.email as "procurement_reviewer_email", procurement_etr.department_name_from as "procurement_department_name", procurement_review.is_approved as "procurement_is_approved", procurement_review.reviewed_at as "procurement_reviewed_at", quality_reviewer.name as "quality_reviewer_name", quality_reviewer.email as "quality_reviewer_email", quality_etr.department_name_from as "quality_department_name", quality_review.is_approved as "quality_is_approved", quality_review.reviewed_at as "quality_reviewed_at", design_reviewer.name as "design_reviewer_name", design_reviewer.email as "design_reviewer_email", design_etr.department_name_from as "design_department_name", design_review.is_approved as "design_is_approved", design_review.reviewed_at as "design_reviewed_at", cs_reviewer.name as "cs_reviewer_name", cs_reviewer.email as "cs_reviewer_email", cs_etr.department_name_from as "cs_department_name", cs_review.is_approved as "cs_is_approved", cs_review.reviewed_at as "cs_reviewed_at" from "evaluation_targets" left join evaluation_target_reviewers order_etr on "evaluation_targets"."id" = order_etr.evaluation_target_id AND order_etr.department_code = 'ORDER_EVAL' left join users order_reviewer on order_etr.reviewer_user_id = order_reviewer.id left join evaluation_target_reviews order_review on "evaluation_targets"."id" = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id left join evaluation_target_reviewers procurement_etr on "evaluation_targets"."id" = procurement_etr.evaluation_target_id AND procurement_etr.department_code = 'PROCUREMENT_EVAL' left join users procurement_reviewer on procurement_etr.reviewer_user_id = procurement_reviewer.id left join evaluation_target_reviews procurement_review on "evaluation_targets"."id" = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id left join evaluation_target_reviewers quality_etr on "evaluation_targets"."id" = quality_etr.evaluation_target_id AND quality_etr.department_code = 'QUALITY_EVAL' left join users quality_reviewer on quality_etr.reviewer_user_id = quality_reviewer.id left join evaluation_target_reviews quality_review on "evaluation_targets"."id" = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id left join evaluation_target_reviewers design_etr on "evaluation_targets"."id" = design_etr.evaluation_target_id AND design_etr.department_code = 'DESIGN_EVAL' left join users design_reviewer on design_etr.reviewer_user_id = design_reviewer.id left join evaluation_target_reviews design_review on "evaluation_targets"."id" = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id left join evaluation_target_reviewers cs_etr on "evaluation_targets"."id" = cs_etr.evaluation_target_id AND cs_etr.department_code = 'CS_EVAL' left join users cs_reviewer on cs_etr.reviewer_user_id = cs_reviewer.id left join evaluation_target_reviews cs_review on "evaluation_targets"."id" = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id);