1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
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
);
|
