diff options
Diffstat (limited to 'db/migrations/0093_futuristic_firestar.sql')
| -rw-r--r-- | db/migrations/0093_futuristic_firestar.sql | 203 |
1 files changed, 203 insertions, 0 deletions
diff --git a/db/migrations/0093_futuristic_firestar.sql b/db/migrations/0093_futuristic_firestar.sql new file mode 100644 index 00000000..0a7a2d57 --- /dev/null +++ b/db/migrations/0093_futuristic_firestar.sql @@ -0,0 +1,203 @@ +DROP VIEW "public"."document_stages_view";--> statement-breakpoint +DROP VIEW "public"."vendor_documents_view";--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "stage_status" varchar(50) DEFAULT 'PLANNED' NOT NULL;--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "stage_order" integer DEFAULT 0;--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "priority" varchar(20) DEFAULT 'MEDIUM';--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "assignee_id" integer;--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "assignee_name" varchar(100);--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "reminder_days" integer DEFAULT 3;--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "description" varchar(500);--> statement-breakpoint +ALTER TABLE "issue_stages" ADD COLUMN "notes" varchar(1000);--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "revision_status" varchar(50) DEFAULT 'SUBMITTED' NOT NULL;--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "submitted_date" date;--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "review_start_date" date;--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "rejected_date" date;--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "reviewer_id" integer;--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "reviewer_name" varchar(100);--> statement-breakpoint +ALTER TABLE "revisions" ADD COLUMN "review_comments" varchar(1000);--> statement-breakpoint +CREATE UNIQUE INDEX "document_stage_order" ON "issue_stages" USING btree ("document_id","stage_order");--> statement-breakpoint +ALTER TABLE "revisions" DROP COLUMN "status";--> statement-breakpoint +CREATE VIEW "public"."enhanced_documents_view" AS ( + WITH document_stats AS ( + SELECT + d.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 documents d + LEFT JOIN issue_stages ist ON d.id = ist.document_id + GROUP BY d.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 + ), + latest_revision_info AS ( + SELECT DISTINCT ON (ist.document_id) + ist.document_id, + r.id as latest_revision_id, + r.revision as latest_revision, + r.revision_status as latest_revision_status, + r.uploader_name as latest_revision_uploader_name, + r.submitted_date as latest_submitted_date + FROM revisions r + JOIN issue_stages ist ON r.issue_stage_id = ist.id + ORDER BY ist.document_id, r.created_at DESC + ), + stage_aggregation AS ( + SELECT + document_id, + json_agg( + json_build_object( + 'id', id, + 'stageName', stage_name, + 'stageStatus', stage_status, + 'stageOrder', stage_order, + 'planDate', plan_date, + 'actualDate', actual_date, + 'assigneeName', assignee_name, + 'priority', priority + ) ORDER BY stage_order + ) as all_stages + FROM issue_stages + GROUP BY document_id + ), + attachment_counts AS ( + SELECT + ist.document_id, + COUNT(da.id) as attachment_count + FROM issue_stages ist + LEFT JOIN revisions r ON ist.id = r.issue_stage_id + LEFT JOIN document_attachments da ON r.id = da.revision_id + GROUP BY ist.document_id + ) + + SELECT + d.id as document_id, + d.doc_number, + d.title, + d.pic, + d.status, + d.issued_date, + d.contract_id, + + -- 현재 스테이지 정보 + 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, + + -- 최신 리비전 정보 + lri.latest_revision_id, + lri.latest_revision, + lri.latest_revision_status, + lri.latest_revision_uploader_name, + lri.latest_submitted_date, + + -- 전체 스테이지 + COALESCE(sa.all_stages, '[]'::json) as all_stages, + + -- 기타 + COALESCE(ac.attachment_count, 0) as attachment_count, + d.created_at, + d.updated_at + + FROM documents d + LEFT JOIN document_stats ds ON d.id = ds.document_id + LEFT JOIN current_stage_info csi ON d.id = csi.document_id + LEFT JOIN latest_revision_info lri ON d.id = lri.document_id + LEFT JOIN stage_aggregation sa ON d.id = sa.document_id + LEFT JOIN attachment_counts ac ON d.id = ac.document_id + + ORDER BY d.created_at DESC +);--> statement-breakpoint +CREATE VIEW "public"."document_stages_view" AS ( + SELECT + d.id AS document_id, + d.doc_number, + d.title, + d.status, + d.issued_date, + d.contract_id, + (SELECT COUNT(*) FROM issue_stages WHERE document_id = d.id) AS stage_count, + COALESCE( + (SELECT json_agg(i.stage_name) FROM issue_stages i WHERE i.document_id = d.id), + '[]' + ) AS stage_list, + d.created_at, + d.updated_at + FROM documents d +);--> statement-breakpoint +CREATE VIEW "public"."vendor_documents_view" AS ( + SELECT + d.id, + d.doc_number, + d.title, + d.pic, + d.status, + d.issued_date, + d.contract_id, + + (SELECT id FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_id, + (SELECT stage_name FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_name, + (SELECT plan_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_plan_date, + (SELECT actual_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_actual_date, + + (SELECT r.id FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_id, + (SELECT r.revision FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision, + (SELECT r.uploader_type FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_type, + (SELECT r.uploader_name FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_name, + + (SELECT COUNT(*) FROM document_attachments a JOIN revisions r ON a.revision_id = r.id JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id) AS attachment_count, + + d.created_at, + d.updated_at + FROM documents d + JOIN contracts c ON d.contract_id = c.id + );
\ No newline at end of file |