DROP VIEW "public"."procurement_rfqs_view";--> statement-breakpoint
ALTER TABLE "procurement_rfqs" ADD COLUMN "pic_code" varchar(50);--> 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"."status", "procurement_rfqs"."pic_code", "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 pr_no 
                FROM pr_items 
                WHERE procurement_rfqs_id = "procurement_rfqs"."id" 
                AND major_yn = true 
                LIMIT 1
            ) as "po_no", (
                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_id" = "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");


CREATE VIEW "public"."procurement_rfqs_view" AS (
  SELECT 
    "procurement_rfqs"."id" AS "id", 
    "procurement_rfqs"."rfq_code", 
    "projects"."code" AS "project_code", 
    "projects"."name" AS "project_name", 
    "procurement_rfqs"."series", 
    "procurement_rfqs"."pic_code", 
    "procurement_rfqs"."rfq_sealed_yn", 
    "items"."item_code", 
    "items"."item_name", 
    "procurement_rfqs"."status", 
    "procurement_rfqs"."rfq_send_date", 
    "procurement_rfqs"."due_date", 
    "created_by_user"."name" AS "created_by_user_name", 
    "procurement_rfqs"."updated_at", 
    "updated_by_user"."name" AS "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 pr_no 
      FROM pr_items 
      WHERE procurement_rfqs_id = "procurement_rfqs"."id" 
      AND major_yn = true 
      LIMIT 1
    ) AS "po_no", 
    (
      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_id" = "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"
);