CREATE TABLE "procurement_attachments" (
	"id" serial PRIMARY KEY NOT NULL,
	"attachment_type" varchar(50) NOT NULL,
	"procurement_rfqs_id" integer,
	"procurement_rfq_details_id" 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),
	"description" varchar(500),
	"created_by" integer NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "attachment_type_check" CHECK ("procurement_attachments"."procurement_rfqs_id" IS NOT NULL OR "procurement_attachments"."procurement_rfq_details_id" IS NOT NULL)
);
--> statement-breakpoint
CREATE TABLE "procurement_quotation_items" (
	"id" serial PRIMARY KEY NOT NULL,
	"quotation_id" integer NOT NULL,
	"pr_item_id" integer NOT NULL,
	"material_code" varchar(50),
	"material_description" varchar(255),
	"quantity" numeric NOT NULL,
	"uom" varchar(20),
	"unit_price" numeric NOT NULL,
	"total_price" numeric NOT NULL,
	"currency" varchar(10) DEFAULT 'USD',
	"vendor_material_code" varchar(50),
	"vendor_material_description" varchar(255),
	"delivery_date" date,
	"lead_time_in_days" integer,
	"tax_rate" numeric,
	"tax_amount" numeric,
	"discount_rate" numeric,
	"discount_amount" numeric,
	"remark" text,
	"is_alternative" boolean DEFAULT false,
	"is_recommended" boolean DEFAULT false,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_attachments" (
	"id" serial PRIMARY KEY NOT NULL,
	"rfq_id" integer NOT NULL,
	"comment_id" integer,
	"quotation_id" integer,
	"file_name" varchar(255) NOT NULL,
	"file_size" integer NOT NULL,
	"file_type" varchar(100),
	"file_path" varchar(500) NOT NULL,
	"is_vendor_upload" boolean DEFAULT false,
	"uploaded_by" integer,
	"vendor_id" integer,
	"uploaded_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_comments" (
	"id" serial PRIMARY KEY NOT NULL,
	"rfq_id" integer NOT NULL,
	"vendor_id" integer,
	"user_id" integer,
	"content" text NOT NULL,
	"is_vendor_comment" boolean DEFAULT false,
	"parent_comment_id" integer,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_vendor_quotations" (
	"id" serial PRIMARY KEY NOT NULL,
	"rfq_id" integer NOT NULL,
	"vendor_id" integer NOT NULL,
	"quotation_code" varchar(50),
	"quotation_version" integer DEFAULT 1,
	"total_items_count" integer DEFAULT 0,
	"sub_total" numeric DEFAULT '0',
	"tax_total" numeric DEFAULT '0',
	"discount_total" numeric DEFAULT '0',
	"total_price" numeric DEFAULT '0',
	"currency" varchar(10) DEFAULT 'USD',
	"valid_until" date,
	"estimated_delivery_date" date,
	"payment_terms_code" varchar(50),
	"incoterms_code" varchar(20),
	"incoterms_detail" varchar(255),
	"status" varchar(30) DEFAULT 'Draft' NOT NULL,
	"remark" text,
	"rejection_reason" text,
	"submitted_at" timestamp,
	"accepted_at" timestamp,
	"created_by" integer,
	"updated_by" integer,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
DROP VIEW "public"."procurement_rfq_details_view";--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfq_details_id_procurement_rfq_details_id_fk" FOREIGN KEY ("procurement_rfq_details_id") REFERENCES "public"."procurement_rfq_details"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_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 "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_pr_item_id_pr_items_id_fk" FOREIGN KEY ("pr_item_id") REFERENCES "public"."pr_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_parent_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("parent_comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_payment_terms_code_payment_terms_code_fk" FOREIGN KEY ("payment_terms_code") REFERENCES "public"."payment_terms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."procurement_rfq_details_view" AS (select "rfq_details"."id" as "detail_id", "rfqs"."id" as "rfq_id", "rfqs"."rfq_code" as "rfq_code", (
                SELECT COUNT(*) 
                FROM procurement_rfq_comments 
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
            ) as "comment_count", (
                SELECT created_at 
                FROM procurement_rfq_comments 
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
                ORDER BY created_at DESC LIMIT 1
            ) as "last_comment_date", (
                SELECT created_at 
                FROM procurement_rfq_comments 
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id" AND is_vendor_comment = true
                ORDER BY created_at DESC LIMIT 1
            ) as "last_vendor_comment_date", (
                SELECT COUNT(*) 
                FROM procurement_rfq_attachments 
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
            ) as "attachment_count", (
                SELECT COUNT(*) > 0
                FROM procurement_vendor_quotations
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
            ) as "has_quotation", (
                SELECT status
                FROM procurement_vendor_quotations
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
                ORDER BY created_at DESC LIMIT 1
            ) as "quotation_status", (
                SELECT total_price
                FROM procurement_vendor_quotations
                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
                ORDER BY created_at DESC LIMIT 1
            ) as "quotation_total_price" from "procurement_rfq_details" "rfq_details" left join "procurement_rfqs" "rfqs" on "rfq_details"."procurement_rfqs_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "items" on "rfqs"."item_id" = "items"."id" left join "vendors" on "rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "rfq_details"."updated_by" = "updated_by_user"."id");