diff options
Diffstat (limited to 'db/migrations/0279_solid_stone_men.sql')
| -rw-r--r-- | db/migrations/0279_solid_stone_men.sql | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/db/migrations/0279_solid_stone_men.sql b/db/migrations/0279_solid_stone_men.sql new file mode 100644 index 00000000..8fefb9f3 --- /dev/null +++ b/db/migrations/0279_solid_stone_men.sql @@ -0,0 +1,163 @@ +CREATE TABLE "stage_documents" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "stage_documents_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "doc_number" varchar(100) NOT NULL, + "title" varchar(255) NOT NULL, + "status" varchar(50) DEFAULT 'ACTIVE' NOT NULL, + "vendor_doc_number" varchar(100), + "issued_date" date, + "project_id" integer NOT NULL, + "vendor_id" varchar(50) NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "stage_issue_stages" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "stage_issue_stages_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "document_id" integer NOT NULL, + "stage_name" varchar(100) NOT NULL, + "plan_date" date, + "actual_date" date, + "stage_status" varchar(50) DEFAULT 'PLANNED' NOT NULL, + "stage_order" integer DEFAULT 0, + "priority" varchar(20) DEFAULT 'MEDIUM', + "assignee_id" integer, + "assignee_name" varchar(100), + "reminder_days" integer DEFAULT 3, + "description" varchar(500), + "notes" varchar(1000), + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "stage_documents" ADD CONSTRAINT "stage_documents_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "stage_issue_stages" ADD CONSTRAINT "stage_issue_stages_document_id_stage_documents_id_fk" FOREIGN KEY ("document_id") REFERENCES "public"."stage_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +CREATE UNIQUE INDEX "unique_project_doc" ON "stage_documents" USING btree ("project_id","doc_number","status");--> statement-breakpoint +CREATE UNIQUE INDEX "unique_project_vendor_doc" ON "stage_documents" USING btree ("project_id","vendor_doc_number") WHERE "stage_documents"."vendor_doc_number" IS NOT NULL;--> statement-breakpoint +CREATE INDEX "stage_doc_vendor_id_idx" ON "stage_documents" USING btree ("vendor_id");--> statement-breakpoint +CREATE INDEX "stage_doc_status_idx" ON "stage_documents" USING btree ("status");--> statement-breakpoint +CREATE UNIQUE INDEX "unique_stage_document_stage" ON "stage_issue_stages" USING btree ("document_id","stage_name");--> statement-breakpoint +CREATE UNIQUE INDEX "stage_document_stage_order" ON "stage_issue_stages" USING btree ("document_id","stage_order");--> statement-breakpoint +CREATE VIEW "public"."stage_documents_view" AS ( + WITH document_stats AS ( + SELECT + sd.id as document_id, + COUNT(ist.id) as total_stages, + COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages, + CASE + WHEN COUNT(ist.id) > 0 + THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id)) + ELSE 0 + END as progress_percentage + FROM stage_documents sd + LEFT JOIN issue_stages ist ON sd.id = ist.document_id + GROUP BY sd.id + ), + current_stage_info AS ( + SELECT DISTINCT ON (document_id) + document_id, + id as current_stage_id, + stage_name as current_stage_name, + stage_status as current_stage_status, + stage_order as current_stage_order, + plan_date as current_stage_plan_date, + actual_date as current_stage_actual_date, + assignee_name as current_stage_assignee_name, + priority as current_stage_priority, + CASE + WHEN actual_date IS NULL AND plan_date IS NOT NULL + THEN plan_date - CURRENT_DATE + ELSE NULL + END as days_until_due, + CASE + WHEN actual_date IS NULL AND plan_date < CURRENT_DATE + THEN true + WHEN actual_date IS NOT NULL AND actual_date > plan_date + THEN true + ELSE false + END as is_overdue, + CASE + WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL + THEN actual_date - plan_date + ELSE NULL + END as days_difference + FROM issue_stages + WHERE stage_status NOT IN ('COMPLETED', 'APPROVED') + ORDER BY document_id, stage_order ASC, priority DESC + ), + -- 문서별 스테이지 집계 (리비전 제외) + stage_aggregation AS ( + SELECT + ist.document_id, + json_agg( + json_build_object( + 'id', ist.id, + 'stageName', ist.stage_name, + 'stageStatus', ist.stage_status, + 'stageOrder', ist.stage_order, + 'planDate', ist.plan_date, + 'actualDate', ist.actual_date, + 'assigneeName', ist.assignee_name, + 'priority', ist.priority, + 'description', ist.description, + 'notes', ist.notes, + 'reminderDays', ist.reminder_days + ) ORDER BY ist.stage_order + ) as all_stages + FROM issue_stages ist + GROUP BY ist.document_id + ) + + SELECT + sd.id as document_id, + sd.doc_number, + sd.vendor_doc_number, + sd.title, + sd.status, + sd.issued_date, + + -- 프로젝트 및 벤더 정보 (직접 참조로 간소화) + sd.project_id, + p.code as project_code, + sd.vendor_id, + v.vendor_name, + v.vendor_code, + + -- 현재 스테이지 정보 + csi.current_stage_id, + csi.current_stage_name, + csi.current_stage_status, + csi.current_stage_order, + csi.current_stage_plan_date, + csi.current_stage_actual_date, + csi.current_stage_assignee_name, + csi.current_stage_priority, + + -- 계산 필드 + csi.days_until_due, + csi.is_overdue, + csi.days_difference, + + -- 진행률 정보 + ds.total_stages, + ds.completed_stages, + ds.progress_percentage, + + -- 전체 스테이지 (리비전 제외) + COALESCE(sa.all_stages, '[]'::json) as all_stages, + + -- 메타 정보 + sd.created_at, + sd.updated_at + + FROM stage_documents sd + -- 간소화된 JOIN (vendors는 vendor_id로 직접 조인) + LEFT JOIN projects p ON sd.project_id = p.id + LEFT JOIN vendors v ON sd.vendor_id = v.vendor_id + + -- 스테이지 관련 정보 JOIN + LEFT JOIN document_stats ds ON sd.id = ds.document_id + LEFT JOIN current_stage_info csi ON sd.id = csi.document_id + LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id + + ORDER BY sd.created_at DESC +);
\ No newline at end of file |