(대표님) RFQ-last, tbe-last, 기본계약 템플릿 내 견적,입찰,계약 추가, env.dev NAS_PATH 수정

1 files changed, 272 insertions, 0 deletions

diff --git a/db/migrations/0355_outgoing_stepford_cuckoos.sql b/db/migrations/0355_outgoing_stepford_cuckoos.sql
new file mode 100644
index 00000000..5ceb0ca6
--- /dev/null
+++ b/db/migrations/0355_outgoing_stepford_cuckoos.sql
@@ -0,0 +1,272 @@
+CREATE TABLE "rfq_last_tbe_communications" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"tbe_session_id" integer NOT NULL,
+	"parent_id" integer,
+	"thread_id" varchar(50),
+	"message_type" varchar(30) NOT NULL,
+	"sender_type" varchar(20) NOT NULL,
+	"sender_user_id" integer NOT NULL,
+	"related_document_review_id" integer,
+	"subject" varchar(255),
+	"content" text NOT NULL,
+	"priority" varchar(20) DEFAULT 'medium',
+	"requires_response" boolean DEFAULT false,
+	"response_deadline" timestamp with time zone,
+	"response_status" varchar(30) DEFAULT '대기중',
+	"responded_at" timestamp with time zone,
+	"has_attachments" boolean DEFAULT false,
+	"attachment_paths" jsonb,
+	"read_by" jsonb,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_document_reviews" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"tbe_session_id" integer NOT NULL,
+	"document_source" varchar(20) NOT NULL,
+	"buyer_attachment_id" integer,
+	"buyer_attachment_revision_id" integer,
+	"vendor_attachment_id" integer,
+	"document_type" varchar(50),
+	"document_name" varchar(255),
+	"review_status" varchar(30) DEFAULT '미검토' NOT NULL,
+	"technical_compliance" boolean,
+	"quality_acceptable" boolean,
+	"requires_revision" boolean DEFAULT false,
+	"review_comments" text,
+	"revision_requirements" text,
+	"has_pdftron_comments" boolean DEFAULT false,
+	"pdftron_document_id" varchar(255),
+	"pdftron_annotation_count" integer DEFAULT 0,
+	"reviewed_by" integer,
+	"reviewed_at" timestamp with time zone,
+	"additional_reviewers" jsonb,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_evaluation_items" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"tbe_session_id" integer NOT NULL,
+	"category" varchar(50) NOT NULL,
+	"subcategory" varchar(100),
+	"item_code" varchar(50),
+	"item_name" varchar(255) NOT NULL,
+	"item_description" text,
+	"weight" numeric(5, 2) DEFAULT 1,
+	"max_score" numeric(5, 2) DEFAULT 10,
+	"evaluation_result" varchar(20),
+	"score" numeric(5, 2),
+	"is_mandatory" boolean DEFAULT false,
+	"mandatory_passed" boolean,
+	"evaluator_comments" text,
+	"evidence" text,
+	"attachment_refs" jsonb,
+	"improvement_required" boolean DEFAULT false,
+	"improvement_description" text,
+	"evaluated_by" integer,
+	"evaluated_at" timestamp with time zone,
+	"verified_by" integer,
+	"verified_at" timestamp with time zone,
+	"verification_comments" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_history" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"tbe_session_id" integer NOT NULL,
+	"action_type" varchar(50) NOT NULL,
+	"previous_status" varchar(30),
+	"new_status" varchar(30),
+	"change_description" text,
+	"change_details" jsonb,
+	"performed_by" integer NOT NULL,
+	"performed_by_type" varchar(20) NOT NULL,
+	"performed_at" timestamp DEFAULT now() NOT NULL,
+	"related_entity_type" varchar(50),
+	"related_entity_id" integer,
+	"remarks" text
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_pdftron_comments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"document_review_id" integer NOT NULL,
+	"pdftron_document_id" varchar(255) NOT NULL,
+	"pdftron_annotation_id" varchar(255) NOT NULL,
+	"annotation_type" varchar(50),
+	"page_number" integer,
+	"x_position" numeric(10, 4),
+	"y_position" numeric(10, 4),
+	"coordinates" jsonb,
+	"comment_text" text,
+	"comment_category" varchar(50),
+	"severity" varchar(20) DEFAULT 'minor',
+	"status" varchar(30) DEFAULT 'open',
+	"resolved_by" integer,
+	"resolved_at" timestamp with time zone,
+	"resolution_note" text,
+	"replies" jsonb,
+	"created_by" integer NOT NULL,
+	"created_by_type" varchar(20) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_sessions" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfqs_last_id" integer NOT NULL,
+	"rfq_last_details_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"session_code" varchar(50),
+	"session_title" varchar(255),
+	"session_type" varchar(50) DEFAULT 'initial',
+	"status" varchar(30) DEFAULT '준비중' NOT NULL,
+	"evaluation_result" varchar(30),
+	"evaluation_score" numeric(5, 2),
+	"evaluation_grade" varchar(10),
+	"conditional_requirements" text,
+	"conditions_fulfilled" boolean DEFAULT false,
+	"vendor_remarks" text,
+	"vendor_questions_log" jsonb,
+	"planned_start_date" date,
+	"actual_start_date" timestamp with time zone,
+	"planned_end_date" date,
+	"actual_end_date" timestamp with time zone,
+	"lead_evaluator_id" integer,
+	"technical_evaluator_id" integer,
+	"commercial_evaluator_id" integer,
+	"technical_summary" text,
+	"commercial_summary" text,
+	"overall_remarks" text,
+	"created_by" integer NOT NULL,
+	"updated_by" integer NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	"approved_by" integer,
+	"approved_at" timestamp with time zone,
+	"approval_remarks" text,
+	CONSTRAINT "rfq_last_tbe_sessions_session_code_unique" UNIQUE("session_code")
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_last_tbe_vendor_documents" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"tbe_session_id" integer NOT NULL,
+	"document_type" varchar(50) NOT NULL,
+	"is_response_to" integer,
+	"file_name" varchar(255) NOT NULL,
+	"original_file_name" varchar(255) NOT NULL,
+	"file_path" varchar(512) NOT NULL,
+	"file_size" integer,
+	"file_type" varchar(100),
+	"document_no" varchar(100),
+	"revision_no" varchar(20),
+	"issue_date" date,
+	"description" text,
+	"submittal_remarks" text,
+	"review_required" boolean DEFAULT true,
+	"review_status" varchar(30) DEFAULT 'pending',
+	"submitted_by" integer NOT NULL,
+	"submitted_at" timestamp DEFAULT now() NOT NULL,
+	"reviewed_by" integer,
+	"reviewed_at" timestamp with time zone,
+	"review_comments" text
+);
+--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_communications" ADD CONSTRAINT "rfq_last_tbe_communications_tbe_session_id_rfq_last_tbe_sessions_id_fk" FOREIGN KEY ("tbe_session_id") REFERENCES "public"."rfq_last_tbe_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_communications" ADD CONSTRAINT "rfq_last_tbe_communications_parent_id_rfq_last_tbe_communications_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."rfq_last_tbe_communications"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_communications" ADD CONSTRAINT "rfq_last_tbe_communications_sender_user_id_users_id_fk" FOREIGN KEY ("sender_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_communications" ADD CONSTRAINT "rfq_last_tbe_communications_related_document_review_id_rfq_last_tbe_document_reviews_id_fk" FOREIGN KEY ("related_document_review_id") REFERENCES "public"."rfq_last_tbe_document_reviews"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_document_reviews" ADD CONSTRAINT "rfq_last_tbe_document_reviews_tbe_session_id_rfq_last_tbe_sessions_id_fk" FOREIGN KEY ("tbe_session_id") REFERENCES "public"."rfq_last_tbe_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_document_reviews" ADD CONSTRAINT "rfq_last_tbe_document_reviews_buyer_attachment_id_rfq_last_attachments_id_fk" FOREIGN KEY ("buyer_attachment_id") REFERENCES "public"."rfq_last_attachments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_document_reviews" ADD CONSTRAINT "rfq_last_tbe_document_reviews_buyer_attachment_revision_id_rfq_last_attachment_revisions_id_fk" FOREIGN KEY ("buyer_attachment_revision_id") REFERENCES "public"."rfq_last_attachment_revisions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_document_reviews" ADD CONSTRAINT "rfq_last_tbe_document_reviews_vendor_attachment_id_rfq_last_vendor_attachments_id_fk" FOREIGN KEY ("vendor_attachment_id") REFERENCES "public"."rfq_last_vendor_attachments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_document_reviews" ADD CONSTRAINT "rfq_last_tbe_document_reviews_reviewed_by_users_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_evaluation_items" ADD CONSTRAINT "rfq_last_tbe_evaluation_items_tbe_session_id_rfq_last_tbe_sessions_id_fk" FOREIGN KEY ("tbe_session_id") REFERENCES "public"."rfq_last_tbe_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_evaluation_items" ADD CONSTRAINT "rfq_last_tbe_evaluation_items_evaluated_by_users_id_fk" FOREIGN KEY ("evaluated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_evaluation_items" ADD CONSTRAINT "rfq_last_tbe_evaluation_items_verified_by_users_id_fk" FOREIGN KEY ("verified_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_history" ADD CONSTRAINT "rfq_last_tbe_history_tbe_session_id_rfq_last_tbe_sessions_id_fk" FOREIGN KEY ("tbe_session_id") REFERENCES "public"."rfq_last_tbe_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_history" ADD CONSTRAINT "rfq_last_tbe_history_performed_by_users_id_fk" FOREIGN KEY ("performed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_pdftron_comments" ADD CONSTRAINT "rfq_last_tbe_pdftron_comments_document_review_id_rfq_last_tbe_document_reviews_id_fk" FOREIGN KEY ("document_review_id") REFERENCES "public"."rfq_last_tbe_document_reviews"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_pdftron_comments" ADD CONSTRAINT "rfq_last_tbe_pdftron_comments_resolved_by_users_id_fk" FOREIGN KEY ("resolved_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_pdftron_comments" ADD CONSTRAINT "rfq_last_tbe_pdftron_comments_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_rfqs_last_id_rfqs_last_id_fk" FOREIGN KEY ("rfqs_last_id") REFERENCES "public"."rfqs_last"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_rfq_last_details_id_rfq_last_details_id_fk" FOREIGN KEY ("rfq_last_details_id") REFERENCES "public"."rfq_last_details"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_lead_evaluator_id_users_id_fk" FOREIGN KEY ("lead_evaluator_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_technical_evaluator_id_users_id_fk" FOREIGN KEY ("technical_evaluator_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_commercial_evaluator_id_users_id_fk" FOREIGN KEY ("commercial_evaluator_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_sessions" ADD CONSTRAINT "rfq_last_tbe_sessions_approved_by_users_id_fk" FOREIGN KEY ("approved_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_vendor_documents" ADD CONSTRAINT "rfq_last_tbe_vendor_documents_tbe_session_id_rfq_last_tbe_sessions_id_fk" FOREIGN KEY ("tbe_session_id") REFERENCES "public"."rfq_last_tbe_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_vendor_documents" ADD CONSTRAINT "rfq_last_tbe_vendor_documents_is_response_to_rfq_last_tbe_communications_id_fk" FOREIGN KEY ("is_response_to") REFERENCES "public"."rfq_last_tbe_communications"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_vendor_documents" ADD CONSTRAINT "rfq_last_tbe_vendor_documents_submitted_by_users_id_fk" FOREIGN KEY ("submitted_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_last_tbe_vendor_documents" ADD CONSTRAINT "rfq_last_tbe_vendor_documents_reviewed_by_users_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+CREATE INDEX "idx_comm_tbe_session" ON "rfq_last_tbe_communications" USING btree ("tbe_session_id");--> statement-breakpoint
+CREATE INDEX "idx_comm_parent" ON "rfq_last_tbe_communications" USING btree ("parent_id");--> statement-breakpoint
+CREATE INDEX "idx_comm_thread" ON "rfq_last_tbe_communications" USING btree ("thread_id");--> statement-breakpoint
+CREATE INDEX "idx_comm_sender_type" ON "rfq_last_tbe_communications" USING btree ("sender_type");--> statement-breakpoint
+CREATE INDEX "idx_tbe_session" ON "rfq_last_tbe_document_reviews" USING btree ("tbe_session_id");--> statement-breakpoint
+CREATE INDEX "idx_document_source" ON "rfq_last_tbe_document_reviews" USING btree ("document_source");--> statement-breakpoint
+CREATE INDEX "idx_review_status" ON "rfq_last_tbe_document_reviews" USING btree ("review_status");--> statement-breakpoint
+CREATE INDEX "idx_eval_tbe_session" ON "rfq_last_tbe_evaluation_items" USING btree ("tbe_session_id");--> statement-breakpoint
+CREATE INDEX "idx_eval_category" ON "rfq_last_tbe_evaluation_items" USING btree ("category");--> statement-breakpoint
+CREATE INDEX "idx_eval_mandatory" ON "rfq_last_tbe_evaluation_items" USING btree ("is_mandatory");--> statement-breakpoint
+CREATE INDEX "idx_history_tbe_session" ON "rfq_last_tbe_history" USING btree ("tbe_session_id");--> statement-breakpoint
+CREATE INDEX "idx_history_action_type" ON "rfq_last_tbe_history" USING btree ("action_type");--> statement-breakpoint
+CREATE INDEX "idx_history_performed_at" ON "rfq_last_tbe_history" USING btree ("performed_at");--> statement-breakpoint
+CREATE INDEX "idx_pdftron_doc_review" ON "rfq_last_tbe_pdftron_comments" USING btree ("document_review_id");--> statement-breakpoint
+CREATE INDEX "idx_pdftron_status" ON "rfq_last_tbe_pdftron_comments" USING btree ("status");--> statement-breakpoint
+CREATE UNIQUE INDEX "unique_pdftron_annotation" ON "rfq_last_tbe_pdftron_comments" USING btree ("pdftron_document_id","pdftron_annotation_id");--> statement-breakpoint
+CREATE UNIQUE INDEX "unique_active_tbe" ON "rfq_last_tbe_sessions" USING btree ("rfqs_last_id","vendor_id","status") WHERE "rfq_last_tbe_sessions"."status" IN ('준비중', '진행중', '검토중', '보류');--> statement-breakpoint
+CREATE INDEX "idx_tbe_vendor_doc_session" ON "rfq_last_tbe_vendor_documents" USING btree ("tbe_session_id");--> statement-breakpoint
+CREATE INDEX "idx_tbe_vendor_doc_status" ON "rfq_last_tbe_vendor_documents" USING btree ("review_status");--> statement-breakpoint
+CREATE VIEW "public"."tbe_session_summary_view" AS (select "tbe"."id" as "tbe_id", "tbe"."session_code" as "session_code", "tbe"."session_title" as "session_title", "rfq"."rfq_code" as "rfq_code", "vendor"."vendor_name" as "vendor_name", "vendor"."vendor_code" as "vendor_code", "tbe"."status" as "status", "tbe"."evaluation_result" as "evaluation_result", "tbe"."evaluation_score" as "evaluation_score", "lead_evaluator"."name" as "lead_evaluator_name", (
+                SELECT COUNT(*)
+                FROM rfq_last_tbe_document_reviews
+                WHERE tbe_session_id = "tbe"."id"
+            ) as "total_documents", (
+                SELECT COUNT(*)
+                FROM rfq_last_tbe_document_reviews
+                WHERE tbe_session_id = "tbe"."id"
+                AND review_status IN ('검토완료', '승인')
+            ) as "reviewed_documents", (
+                SELECT COUNT(*)
+                FROM rfq_last_tbe_communications
+                WHERE tbe_session_id = "tbe"."id"
+                AND message_type = 'question'
+                AND response_status = '대기중'
+            ) as "open_questions", (
+                SELECT COUNT(*)
+                FROM rfq_last_tbe_pdftron_comments pc
+                JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
+                WHERE dr.tbe_session_id = "tbe"."id"
+            ) as "total_comments", (
+                SELECT COUNT(*)
+                FROM rfq_last_tbe_pdftron_comments pc
+                JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
+                WHERE dr.tbe_session_id = "tbe"."id"
+                AND pc.status = 'open'
+            ) as "unresolved_comments", (
+                SELECT COALESCE(
+                    BOOL_AND(
+                        CASE 
+                            WHEN is_mandatory THEN mandatory_passed
+                            ELSE true
+                        END
+                    ), 
+                    true
+                )
+                FROM rfq_last_tbe_evaluation_items
+                WHERE tbe_session_id = "tbe"."id"
+            ) as "mandatory_items_passed", (
+                SELECT COALESCE(
+                    COUNT(CASE WHEN evaluation_result IS NOT NULL THEN 1 END) * 100.0 / 
+                    NULLIF(COUNT(*), 0),
+                    0
+                )
+                FROM rfq_last_tbe_evaluation_items
+                WHERE tbe_session_id = "tbe"."id"
+            ) as "evaluation_progress", "tbe"."actual_start_date" as "actual_start_date", "tbe"."actual_end_date" as "actual_end_date", "tbe"."created_at" as "created_at", "tbe"."updated_at" as "updated_at" from "rfq_last_tbe_sessions" "tbe" left join "rfqs_last" "rfq" on "tbe"."rfqs_last_id" = "rfq"."id" left join "vendors" "vendor" on "tbe"."vendor_id" = "vendor"."id" left join "users" "lead_evaluator" on "tbe"."lead_evaluator_id" = "lead_evaluator"."id");
\ No newline at end of file