CREATE TABLE "vendor_commercial_responses" (
	"id" serial PRIMARY KEY NOT NULL,
	"response_id" integer NOT NULL,
	"total_price" numeric(18, 2),
	"currency" varchar(10) DEFAULT 'USD',
	"payment_terms" varchar(255),
	"incoterms" varchar(50),
	"delivery_period" varchar(100),
	"warranty_period" varchar(100),
	"validity_period" varchar(100),
	"price_breakdown" text,
	"commercial_notes" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_response_attachments" (
	"id" serial PRIMARY KEY NOT NULL,
	"response_id" integer,
	"technical_response_id" integer,
	"commercial_response_id" integer,
	"file_name" varchar(255) NOT NULL,
	"file_path" varchar(1024) NOT NULL,
	"file_type" varchar(50),
	"attachment_type" varchar(50),
	"description" varchar(255),
	"uploaded_at" timestamp DEFAULT now() NOT NULL,
	"uploaded_by" varchar(255)
);
--> statement-breakpoint
CREATE TABLE "vendor_responses" (
	"id" serial PRIMARY KEY NOT NULL,
	"rfq_id" integer NOT NULL,
	"vendor_id" integer NOT NULL,
	"response_status" varchar(30) DEFAULT 'REVIEWING' NOT NULL,
	"notes" text,
	"responded_by" varchar(255),
	"responded_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_technical_responses" (
	"id" serial PRIMARY KEY NOT NULL,
	"response_id" integer NOT NULL,
	"summary" text,
	"notes" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "vendor_commercial_responses" ADD CONSTRAINT "vendor_commercial_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_technical_response_id_vendor_technical_responses_id_fk" FOREIGN KEY ("technical_response_id") REFERENCES "public"."vendor_technical_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_commercial_response_id_vendor_commercial_responses_id_fk" FOREIGN KEY ("commercial_response_id") REFERENCES "public"."vendor_commercial_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_technical_responses" ADD CONSTRAINT "vendor_technical_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "vendor_response_unique" ON "vendor_responses" USING btree ("rfq_id","vendor_id");--> statement-breakpoint
CREATE VIEW "public"."vendor_responses_view" AS (select "vendor_responses"."id" as "response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."vendor_id" as "vendor_id", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "rfqs"."due_date" as "rfq_due_date", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendor_responses"."response_status" as "response_status", "vendor_responses"."responded_at" as "responded_at", CASE WHEN "vendor_technical_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_technical_response", "vendor_technical_responses"."id" as "technical_response_id", CASE WHEN "vendor_commercial_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_commercial_response", "vendor_commercial_responses"."id" as "commercial_response_id", "vendor_commercial_responses"."total_price" as "total_price", "vendor_commercial_responses"."currency" as "currency", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."result" as "cbe_result" from "vendor_responses" inner join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" inner join "vendors" on "vendor_responses"."vendor_id" = "vendors"."id" left join "vendor_technical_responses" on "vendor_technical_responses"."response_id" = "vendor_responses"."id" left join "vendor_commercial_responses" on "vendor_commercial_responses"."response_id" = "vendor_responses"."id" left join "rfq_evaluations" on ("rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "rfq_evaluations"."vendor_id" = "vendor_responses"."vendor_id" and "rfq_evaluations"."eval_type" = 'TBE') left join "cbe_evaluations" on ("cbe_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "cbe_evaluations"."vendor_id" = "vendor_responses"."vendor_id"));