CREATE TABLE "purchase_request_attachments" (
	"id" serial PRIMARY KEY NOT NULL,
	"request_id" integer NOT NULL,
	"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),
	"category" varchar(50) DEFAULT '설계문서' NOT NULL,
	"description" varchar(500),
	"created_by" integer NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "purchase_requests" (
	"id" serial PRIMARY KEY NOT NULL,
	"request_code" varchar(50) NOT NULL,
	"project_id" integer,
	"project_code" varchar(100),
	"project_name" varchar(255),
	"project_company" varchar(255),
	"project_site" varchar(255),
	"class_no" varchar(50),
	"package_no" varchar(50),
	"package_name" varchar(255),
	"major_item_material_category" varchar(100),
	"major_item_material_description" varchar(255),
	"sm_code" varchar(255),
	"request_title" varchar(255) NOT NULL,
	"request_description" text,
	"estimated_budget" varchar(100),
	"requested_delivery_date" timestamp with time zone,
	"items" jsonb DEFAULT '[]'::jsonb,
	"status" varchar(30) DEFAULT '작성중' NOT NULL,
	"reject_reason" text,
	"confirmed_at" timestamp with time zone,
	"confirmed_by" integer,
	"rfq_id" integer,
	"rfq_code" varchar(50),
	"rfq_created_at" timestamp with time zone,
	"eng_pic_id" integer,
	"eng_pic_name" varchar(50),
	"purchase_pic_id" integer,
	"purchase_pic_name" varchar(50),
	"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 "purchase_requests_request_code_unique" UNIQUE("request_code")
);
--> statement-breakpoint
ALTER TABLE "purchase_request_attachments" ADD CONSTRAINT "purchase_request_attachments_request_id_purchase_requests_id_fk" FOREIGN KEY ("request_id") REFERENCES "public"."purchase_requests"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_request_attachments" ADD CONSTRAINT "purchase_request_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 "purchase_requests" ADD CONSTRAINT "purchase_requests_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 "purchase_requests" ADD CONSTRAINT "purchase_requests_confirmed_by_users_id_fk" FOREIGN KEY ("confirmed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_eng_pic_id_users_id_fk" FOREIGN KEY ("eng_pic_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_purchase_pic_id_users_id_fk" FOREIGN KEY ("purchase_pic_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchase_requests" ADD CONSTRAINT "purchase_requests_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 "purchase_requests" ADD CONSTRAINT "purchase_requests_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "request_file_idx" ON "purchase_request_attachments" USING btree ("request_id","file_name");--> statement-breakpoint
CREATE VIEW "public"."purchase_requests_view" AS (
    SELECT 
      pr.id,
      pr.request_code,
      pr.request_title,
      pr.request_description,
      
      -- 프로젝트 정보
      pr.project_id,
      pr.project_code,
      pr.project_name,
      pr.project_company,
      pr.project_site,
      pr.class_no,
      
      -- 패키지 정보
      pr.package_no,
      pr.package_name,
      
      -- 자재 정보
      pr.major_item_material_category,
      pr.major_item_material_description,
      pr.sm_code,
      
      -- 예산 및 납기
      pr.estimated_budget,
      pr.requested_delivery_date,
      
      -- 아이템 정보
      pr.items,
      jsonb_array_length(pr.items) as item_count,
      (
        SELECT SUM((item->>'quantity')::numeric)
        FROM jsonb_array_elements(pr.items) as item
      ) as total_quantity,
      (
        SELECT SUM(
          (item->>'quantity')::numeric * 
          COALESCE((item->>'estimatedUnitPrice')::numeric, 0)
        )
        FROM jsonb_array_elements(pr.items) as item
      ) as total_estimated_amount,
      
      -- 상태 정보
      pr.status,
      pr.reject_reason,
      
      -- 확정 정보
      pr.confirmed_at,
      pr.confirmed_by,
      cb.name as confirmed_by_name,
      
      -- RFQ 정보
      pr.rfq_id,
      pr.rfq_code,
      pr.rfq_created_at,
      
      -- 담당자 정보
      pr.eng_pic_id,
      pr.eng_pic_name,
      ep.email as eng_pic_email,
      
      pr.purchase_pic_id,
      pr.purchase_pic_name,
      pp.email as purchase_pic_email,
      
      -- 첨부파일 수
      (
        SELECT COUNT(*)
        FROM purchase_request_attachments pra
        WHERE pra.request_id = pr.id
      ) as attachment_count,
      
      -- 생성/수정 정보
      pr.created_by,
      cr.name as created_by_name,
      cr.email as created_by_email,
      pr.updated_by,
      up.name as updated_by_name,
      up.email as updated_by_email,
      pr.created_at,
      pr.updated_at
      
    FROM purchase_requests pr
    LEFT JOIN users cb ON cb.id = pr.confirmed_by
    LEFT JOIN users ep ON ep.id = pr.eng_pic_id
    LEFT JOIN users pp ON pp.id = pr.purchase_pic_id
    LEFT JOIN users cr ON cr.id = pr.created_by
    LEFT JOIN users up ON up.id = pr.updated_by
  );