DROP VIEW "public"."vendor_response_cbe_view";--> statement-breakpoint
DROP VIEW "public"."vendor_tbe_view";--> statement-breakpoint
ALTER TABLE "vendor_commercial_responses" ADD COLUMN "response_status" varchar(30) DEFAULT 'PENDING' NOT NULL;--> statement-breakpoint
ALTER TABLE "vendor_technical_responses" ADD COLUMN "response_status" varchar(30) DEFAULT 'PENDING' NOT NULL;--> statement-breakpoint
CREATE VIEW "public"."vendor_response_cbe_view" AS (select "vendor_responses"."id" as "response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."vendor_id" as "vendor_id", "vendor_responses"."response_status" as "response_status", "vendor_responses"."notes" as "response_notes", "vendor_responses"."responded_by" as "responded_by", "vendor_responses"."responded_at" as "responded_at", "vendor_responses"."updated_at" as "response_updated_at", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "rfqs"."due_date" as "rfq_due_date", "rfqs"."status" as "rfq_status", "rfqs"."rfq_type" as "rfq_type", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."status" as "vendor_status", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "vendor_commercial_responses"."id" as "commercial_response_id", "vendor_commercial_responses"."response_status" as "commercial_response_status", "vendor_commercial_responses"."total_price" as "total_price", "vendor_commercial_responses"."currency" as "currency", "vendor_commercial_responses"."payment_terms" as "payment_terms", "vendor_commercial_responses"."incoterms" as "incoterms", "vendor_commercial_responses"."delivery_period" as "delivery_period", "vendor_commercial_responses"."warranty_period" as "warranty_period", "vendor_commercial_responses"."validity_period" as "validity_period", "vendor_commercial_responses"."price_breakdown" as "price_breakdown", "vendor_commercial_responses"."commercial_notes" as "commercial_notes", "vendor_commercial_responses"."created_at" as "commercial_created_at", "vendor_commercial_responses"."updated_at" as "commercial_updated_at", (
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id"
            ) as "attachment_count", (
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."commercial_response_id" = "vendor_commercial_responses"."id"
            ) as "commercial_attachment_count", (
                SELECT COUNT(*) 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id"
                AND "vendor_response_attachments"."attachment_type" = 'TECHNICAL_SPEC'
            ) as "technical_attachment_count", (
                SELECT MAX("uploaded_at") 
                FROM "vendor_response_attachments" 
                WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id"
            ) as "latest_attachment_date" 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 "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_commercial_responses" on "vendor_commercial_responses"."response_id" = "vendor_responses"."id");--> statement-breakpoint
CREATE VIEW "public"."vendor_tbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "vendor_technical_responses"."id" as "technical_response_id", "vendor_technical_responses"."response_status" as "technical_response_status", "vendor_technical_responses"."summary" as "technical_summary", "vendor_technical_responses"."notes" as "technical_notes", "vendor_technical_responses"."updated_at" as "technical_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "rfq_evaluations"."notes" as "tbe_note", "rfq_evaluations"."updated_at" as "tbe_updated" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_technical_responses" on "vendor_technical_responses"."response_id" = "vendor_responses"."id" left join "rfq_evaluations" on ("rfq_evaluations"."vendor_id" = "vendors"."id" and "rfq_evaluations"."eval_type" = 'TBE' and "rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));