DROP VIEW "public"."vendors_with_types";--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" DROP CONSTRAINT "vendor_possible_materials_item_code_materials_item_code_fk";
--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ALTER COLUMN "item_code" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "AS_GRNT_PRD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "BIZCLS" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "BIZLOC_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "BIZ_DMN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "BP_DL_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CHN_PROJ_TP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CLS_1" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CLS_2" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CNRT_CNTN_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CNRT_DL_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CNRT_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CNRT_RESV_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CO_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "CSTM_PO_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DEL_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DIGT_PDT_GRP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DIST_PATH" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DL_BF_PROJ_NM" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DL_CSTM_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DOCK_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "DSN_CHRGR" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "FIN_GRNT_FN_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "GENT_CNT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "GOV" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "GRNT_STDT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "IF_STAT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "IMO_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "INQY_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "INQY_SEQ" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "IO_GB" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "MNG_ACOT_DMN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "MN_ENGN_TP_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "MSHIP_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "NEW_MC_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "NTTP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "ORDR_GRNT_FN_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "ORDR_GRNT_PRD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "OWN_1" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "OWN_AB" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "OWN_NM" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PDT_LVL_4" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PLNT_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PRCTR" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PRGS_STAT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_CRTE_REQ_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_CRTE_REQ_EMPNO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_DL_PLN_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_DL_RT_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_DTL_TP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_ETC_TP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_GB" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_PRGS_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_PROF" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_SCP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PROJ_WBS_TP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "PRO_PROJ_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "QM_CLS" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "REF_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "RLTD_PROJ" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "RL_DL_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SALE_GRP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SALE_ORG_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SA_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SERS_NO" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SERS_YN" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SHTYPE" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SHTYPE_CD" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SHTYPE_GRP" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SHTYPE_UOM" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SKND" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SRC_SYS_ID" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "STDT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "SYS_ACOT_CLSD_DT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "TOT_CNRT_CNT" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "TYPE" varchar(1000);--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "WP_PROJ_TP" varchar(1000);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "item_name" varchar(255);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "vendor_type" varchar(255);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "register_user_id" integer;--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "register_user_name" varchar(255);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "is_confirmed" boolean DEFAULT false;--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "recent_po_no" varchar(100);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "recent_po_date" timestamp;--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "recent_delivery_date" timestamp;--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "recent_order_date" timestamp;--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "recent_order_user_name" varchar(255);--> statement-breakpoint
ALTER TABLE "vendor_possible_materials" ADD COLUMN "purchase_group_code" varchar(10);--> statement-breakpoint
CREATE VIEW "public"."vendors_with_types" AS (select "vendors"."id" as "id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."tax_id" as "tax_id", "vendors"."address" as "address", "vendors"."address_detail" as "address_detail", "vendors"."postal_code" as "postal_code", "vendors"."country" as "country", "vendors"."phone" as "phone", "vendors"."email" as "email", "vendors"."business_size" as "business_size", "vendors"."website" as "website", "vendors"."status" as "status", "vendors"."vendor_type_id" as "vendor_type_id", "vendors"."representative_name" as "representative_name", "vendors"."representative_birth" as "representative_birth", "vendors"."representative_email" as "representative_email", "vendors"."representative_phone" as "representative_phone", "vendors"."corporate_registration_number" as "corporate_registration_number", "vendors"."credit_agency" as "credit_agency", "vendors"."credit_rating" as "credit_rating", "vendors"."cash_flow_rating" as "cash_flow_rating", "vendors"."created_at" as "created_at", "vendors"."updated_at" as "updated_at", "vendor_types"."name_ko" as "vendor_type_name", "vendor_types"."name_en" as "vendor_type_name_en", "vendor_types"."code" as "vendor_type_code", 
        CASE
          WHEN "vendors"."status" = 'ACTIVE' THEN '정규업체'
          WHEN "vendors"."status" IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN ''
          ELSE '잠재업체'
        END
       as "vendor_category" from "vendors" left join "vendor_types" on "vendors"."vendor_type_id" = "vendor_types"."id");