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");