CREATE TABLE "incoterms" (
	"code" varchar(20) PRIMARY KEY NOT NULL,
	"description" varchar(255) NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_by" integer,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "payment_terms" (
	"code" varchar(50) PRIMARY KEY NOT NULL,
	"description" varchar(255) NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_by" integer,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "pr_items" (
	"id" serial PRIMARY KEY NOT NULL,
	"procurement_rfqs_id" integer,
	"rfq_item" varchar(50),
	"pr_item" varchar(50),
	"item_id" integer,
	"material_code" varchar(255),
	"material_category" varchar(255),
	"acc" varchar(255),
	"material_description" varchar(255),
	"size" varchar(255),
	"delivery_date" date,
	"quantity" numeric(12, 2) DEFAULT 1,
	"uom" varchar(50),
	"gw_uom" varchar(50),
	"spec_no" varchar(255),
	"spec_url" varchar(255),
	"tracking_no" varchar(255),
	"major_yn" boolean DEFAULT false,
	"project_def" varchar(255),
	"prject_sc" varchar(255),
	"prject_kl" varchar(255),
	"prject_lc" varchar(255),
	"prject_dl" varchar(255),
	"remark" text
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_details" (
	"id" serial PRIMARY KEY NOT NULL,
	"procurement_rfqs_id" integer,
	"vendors_id" integer,
	"currency" varchar(10) DEFAULT 'USD',
	"payment_terms_code" varchar(50),
	"incoterms_code" varchar(20),
	"incoterms_detail" varchar(255),
	"delivery_date" date NOT NULL,
	"tax_code" varchar(255) DEFAULT 'VV',
	"place_of_shipping" varchar(255),
	"place_of_destination" varchar(255),
	"remark" text,
	"cancel_reason" text,
	"updated_by" integer NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	"material_price_related_yn" boolean DEFAULT false
);
--> statement-breakpoint
CREATE TABLE "procurement_rfqs" (
	"id" serial PRIMARY KEY NOT NULL,
	"rfq_code" varchar(50),
	"project_id" integer,
	"series" varchar(50),
	"item_code" varchar(100) NOT NULL,
	"due_date" date NOT NULL,
	"rfq_send_date" date NOT NULL,
	"status" varchar(30) DEFAULT 'RFQ Created' NOT NULL,
	"rfq_sealed_yn" boolean DEFAULT false,
	"remark" 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,
	CONSTRAINT "procurement_rfqs_rfq_code_unique" UNIQUE("rfq_code")
);
--> statement-breakpoint
ALTER TABLE "incoterms" ADD CONSTRAINT "incoterms_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 "payment_terms" ADD CONSTRAINT "payment_terms_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 "pr_items" ADD CONSTRAINT "pr_items_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pr_items" ADD CONSTRAINT "pr_items_item_id_items_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."items"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_vendors_id_vendors_id_fk" FOREIGN KEY ("vendors_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_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_rfq_details" ADD CONSTRAINT "procurement_rfq_details_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_details" ADD CONSTRAINT "procurement_rfq_details_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 "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_item_code_items_id_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD CONSTRAINT "procurement_rfqs_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_rfqs" ADD CONSTRAINT "procurement_rfqs_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."normalized_rfq_details_view" AS (select "procurement_rfq_details"."id", "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "items"."item_code", "items"."item_name", "vendors"."vendor_name", "vendors"."vendor_code", "procurement_rfq_details"."currency", "payment_terms"."code", "payment_terms"."description", "incoterms"."code", "incoterms"."description", "procurement_rfq_details"."incoterms_detail", "procurement_rfq_details"."delivery_date", "procurement_rfq_details"."tax_code", "procurement_rfq_details"."place_of_shipping", "procurement_rfq_details"."place_of_destination", "procurement_rfq_details"."material_price_related_yn", "updated_by_user"."name", "procurement_rfq_details"."updated_at" from "procurement_rfq_details" left join "procurement_rfqs" on "procurement_rfq_details"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "vendors" on "procurement_rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "procurement_rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "procurement_rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "procurement_rfq_details"."updated_by" = "updated_by_user"."id");--> statement-breakpoint
CREATE VIEW "public"."pr_items_view" AS (select "pr_items"."id", "pr_items"."procurement_rfqs_id", "pr_items"."rfq_item", "pr_items"."pr_item", "pr_items"."item_id", "pr_items"."material_code", "pr_items"."material_category", "pr_items"."acc", "pr_items"."material_description", "pr_items"."size", "pr_items"."delivery_date", "pr_items"."quantity", "pr_items"."uom", "pr_items"."quantity", "pr_items"."gw_uom", "pr_items"."spec_no", "pr_items"."spec_url", "pr_items"."tracking_no", "pr_items"."major_yn", "pr_items"."project_def", "pr_items"."prject_sc", "pr_items"."prject_kl", "pr_items"."prject_lc", "pr_items"."prject_dl", "pr_items"."remark", "procurement_rfqs"."rfq_code", "items"."item_code", "items"."item_name" from "pr_items" left join "procurement_rfqs" on "pr_items"."procurement_rfqs_id" = "procurement_rfqs"."id" left join "items" on "pr_items"."item_id" = "items"."id");--> statement-breakpoint
CREATE VIEW "public"."procurement_rfqs_view" AS (select "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "procurement_rfqs"."series", "procurement_rfqs"."rfq_sealed_yn", "items"."item_code", "items"."item_name", "procurement_rfqs"."rfq_send_date", "procurement_rfqs"."due_date", "created_by_user"."name", "procurement_rfqs"."updated_at", "updated_by_user"."name", "procurement_rfqs"."remark", (
                SELECT material_code 
                FROM pr_items 
                WHERE procurement_rfqs_id = "procurement_rfqs"."id" 
                AND major_yn = true 
                LIMIT 1
            ) as "major_item_material_code", (
                SELECT COUNT(*) 
                FROM pr_items 
                WHERE procurement_rfqs_id = "procurement_rfqs"."id"
            ) as "pr_items_count" from "procurement_rfqs" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_code" = "items"."id" left join "users" "created_by_user" on "procurement_rfqs"."created_by" = "created_by_user"."id" left join "users" "updated_by_user" on "procurement_rfqs"."updated_by" = "updated_by_user"."id");