summaryrefslogtreecommitdiff
path: root/db/migrations/0355_outgoing_stepford_cuckoos.sql
diff options
context:
space:
mode:
Diffstat (limited to 'db/migrations/0355_outgoing_stepford_cuckoos.sql')
-rw-r--r--db/migrations/0355_outgoing_stepford_cuckoos.sql272
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
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 04:57:48 +0000