DROP VIEW "public"."vendor_response_cbe_view";--> 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"."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");