CREATE TABLE "nonsap"."nonsap_user" (
	"USR_ID" varchar(255) PRIMARY KEY NOT NULL,
	"USR_NM" varchar(255),
	"USR_ENM" varchar(255),
	"EMPNO" varchar(255),
	"CO_CD" varchar(255),
	"CO_NM" varchar(255),
	"DEPTCD" varchar(255),
	"DEPTNM" varchar(255),
	"MAST_DEPTCD" varchar(255),
	"MAST_DEPTNM" varchar(255),
	"VNDRCD" varchar(255),
	"VNDRNM" varchar(255),
	"REGL_ORORD_GB" varchar(255),
	"JG_CD" varchar(255),
	"JG_NM" varchar(255),
	"JK_CD" varchar(255),
	"JK_NM" varchar(255),
	"EMAIL_ADR" varchar(255),
	"TELNO" varchar(255),
	"HP_NO" varchar(255),
	"ADR" varchar(255),
	"MYSNG_ID" varchar(255),
	"MYSNG_USR_ID" varchar(255),
	"MYSNG_USE_YN" varchar(255),
	"CHRG_BIZ_NM" varchar(255),
	"FIN_PWD_CHG_DTM" varchar(255),
	"FIN_LGN_DTM" varchar(255),
	"FIN_LOGOUT_DTM" varchar(255),
	"FIN_LGN_FAIL_TMS" varchar(255),
	"FIN_USEIP" varchar(255),
	"UNLOCK_DTM" varchar(255),
	"LOCK_YN" varchar(255),
	"AGR_YN" varchar(255),
	"DEL_YN" varchar(255),
	"BIZLOC_GB_CD" varchar(255),
	"BIZLOC_GB_NM" varchar(255),
	"GRD_NM" varchar(255),
	"CH_DEPTCD" varchar(255),
	"CH_DEPTNM" varchar(255),
	"ORG_OTHER_NAME" varchar(255),
	"GRADE_OTHER_NAME" varchar(255),
	"FAX_NO" varchar(255),
	"FS_INPR_ID" varchar(255),
	"FS_INP_DTM" varchar(255),
	"FIN_CHGR_ID" varchar(255),
	"FIN_CHG_DTM" varchar(255),
	"LOFF_GB" varchar(255),
	"DEL_DTM" varchar(255)
);
--> statement-breakpoint
DROP VIEW "public"."user_view";--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employee_number" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "knox_id" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "nonsap_user_id" varchar(50);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_absent" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_deleted_on_non_sap" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_regular_employee" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD CONSTRAINT "users_nonsap_user_id_unique" UNIQUE("nonsap_user_id");--> statement-breakpoint
CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."phone" as "user_phone", "users"."email" as "user_email", "users"."domain" as "user_domain", "users"."image_url" as "user_image", "users"."employee_number" as "employee_number", "users"."deptName" as "dept_name", "users"."knox_id" as "knox_id", "users"."is_locked" as "is_locked", "users"."is_absent" as "is_absent", "users"."is_deleted_on_non_sap" as "is_deleted_on_non_sap", "users"."is_regular_employee" as "is_regular_employee", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", 
      array_agg("roles"."name")
     as "roles", "users"."created_at" as "created_at", "users"."updated_at" as "updated_at", "users"."deactivated_at" as "deactivated_at" from "users" left join "vendors" on "users"."company_id" = "vendors"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "vendors"."id");