diff options
Diffstat (limited to 'db/migrations/0381_right_master_mold.sql')
| -rw-r--r-- | db/migrations/0381_right_master_mold.sql | 275 |
1 files changed, 275 insertions, 0 deletions
diff --git a/db/migrations/0381_right_master_mold.sql b/db/migrations/0381_right_master_mold.sql new file mode 100644 index 00000000..a58dd11b --- /dev/null +++ b/db/migrations/0381_right_master_mold.sql @@ -0,0 +1,275 @@ +CREATE TABLE "stage_submission_attachments" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "stage_submission_attachments_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "submission_id" integer NOT NULL, + "file_name" varchar(255) NOT NULL, + "original_file_name" varchar(255) NOT NULL, + "file_type" varchar(100), + "file_extension" varchar(20), + "file_size" bigint NOT NULL, + "storage_type" varchar(50) DEFAULT 'S3', + "storage_path" varchar(1000) NOT NULL, + "storage_url" varchar(2000), + "bucket_name" varchar(255), + "mime_type" varchar(100), + "checksum" varchar(255), + "document_type" varchar(100), + "document_category" varchar(100), + "file_version" varchar(50), + "is_latest" boolean DEFAULT true, + "uploaded_by" varchar(100) NOT NULL, + "uploaded_at" timestamp with time zone DEFAULT now() NOT NULL, + "status" varchar(50) DEFAULT 'ACTIVE', + "buyer_system_file_id" varchar(255), + "buyer_system_status" varchar(50), + "buyer_system_comment" text, + "buyer_system_url" varchar(2000), + "sync_status" varchar(20) DEFAULT 'pending', + "sync_started_at" timestamp with time zone, + "sync_completed_at" timestamp with time zone, + "sync_error" text, + "sync_retry_count" integer DEFAULT 0, + "last_sync_attempt" timestamp with time zone, + "transfer_method" varchar(50), + "transferred_bytes" bigint, + "transfer_progress" integer, + "sync_version" integer DEFAULT 0, + "last_modified_by" varchar(100), + "is_public" boolean DEFAULT false, + "expires_at" timestamp with time zone, + "metadata" jsonb, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "stage_submissions" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "stage_submissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "stage_id" integer NOT NULL, + "document_id" integer NOT NULL, + "revision_number" integer DEFAULT 1 NOT NULL, + "revision_type" varchar(50) DEFAULT 'INITIAL', + "submission_status" varchar(50) DEFAULT 'DRAFT' NOT NULL, + "submitted_by" varchar(100) NOT NULL, + "submitted_by_email" varchar(255), + "submitted_at" timestamp with time zone DEFAULT now() NOT NULL, + "reviewed_by" varchar(100), + "reviewed_by_email" varchar(255), + "reviewed_at" timestamp with time zone, + "submission_title" varchar(500), + "submission_description" text, + "submission_notes" text, + "review_status" varchar(50), + "review_comments" text, + "rejection_reason" text, + "vendor_id" integer NOT NULL, + "vendor_code" varchar(50), + "total_files" integer DEFAULT 0, + "total_file_size" bigint DEFAULT 0, + "buyer_system_status" varchar(50), + "buyer_system_comment" text, + "buyer_system_submission_id" varchar(255), + "last_synced_at" timestamp with time zone, + "sync_status" varchar(20) DEFAULT 'pending', + "sync_error" text, + "sync_version" integer DEFAULT 0, + "last_modified_by" varchar(100), + "sync_retry_count" integer DEFAULT 0, + "next_retry_at" timestamp with time zone, + "total_files_to_sync" integer DEFAULT 0, + "synced_files_count" integer DEFAULT 0, + "failed_files_count" integer DEFAULT 0, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +DROP VIEW "public"."document_stages_view";--> statement-breakpoint +ALTER TABLE "stage_submission_attachments" ADD CONSTRAINT "stage_submission_attachments_submission_id_stage_submissions_id_fk" FOREIGN KEY ("submission_id") REFERENCES "public"."stage_submissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "stage_submissions" ADD CONSTRAINT "stage_submissions_stage_id_stage_issue_stages_id_fk" FOREIGN KEY ("stage_id") REFERENCES "public"."stage_issue_stages"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "stage_submissions" ADD CONSTRAINT "stage_submissions_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 INDEX "attachment_submission_id_idx" ON "stage_submission_attachments" USING btree ("submission_id");--> statement-breakpoint +CREATE INDEX "attachment_file_type_idx" ON "stage_submission_attachments" USING btree ("file_type");--> statement-breakpoint +CREATE INDEX "attachment_status_idx" ON "stage_submission_attachments" USING btree ("status");--> statement-breakpoint +CREATE INDEX "attachment_uploaded_at_idx" ON "stage_submission_attachments" USING btree ("uploaded_at");--> statement-breakpoint +CREATE INDEX "attachment_sync_status_idx" ON "stage_submission_attachments" USING btree ("sync_status");--> statement-breakpoint +CREATE INDEX "attachment_buyer_file_id_idx" ON "stage_submission_attachments" USING btree ("buyer_system_file_id");--> statement-breakpoint +CREATE INDEX "attachment_buyer_status_idx" ON "stage_submission_attachments" USING btree ("buyer_system_status");--> statement-breakpoint +CREATE UNIQUE INDEX "unique_stage_revision" ON "stage_submissions" USING btree ("stage_id","revision_number");--> statement-breakpoint +CREATE INDEX "submission_document_id_idx" ON "stage_submissions" USING btree ("document_id");--> statement-breakpoint +CREATE INDEX "submission_vendor_id_idx" ON "stage_submissions" USING btree ("vendor_id");--> statement-breakpoint +CREATE INDEX "submission_status_idx" ON "stage_submissions" USING btree ("submission_status");--> statement-breakpoint +CREATE INDEX "submission_submitted_at_idx" ON "stage_submissions" USING btree ("submitted_at");--> statement-breakpoint +CREATE INDEX "submission_sync_status_idx" ON "stage_submissions" USING btree ("sync_status");--> statement-breakpoint +CREATE INDEX "submission_buyer_status_idx" ON "stage_submissions" USING btree ("buyer_system_status");--> statement-breakpoint +CREATE INDEX "submission_next_retry_idx" ON "stage_submissions" USING btree ("next_retry_at");--> statement-breakpoint +CREATE VIEW "public"."stage_submission_view" AS ( + WITH eligible_documents AS ( + -- buyerSystemStatus가 '승인(DC)'인 문서만 선택 + SELECT + sd.id as document_id, + sd.doc_number, + sd.vendor_doc_number, + sd.title as document_title, + sd.project_id, + sd.vendor_id, + p.code as project_code, + v.vendor_name, + v.vendor_code + FROM stage_documents sd + LEFT JOIN projects p ON sd.project_id = p.id + LEFT JOIN vendors v ON sd.vendor_id = v.id + WHERE sd.buyer_system_status = '승인(DC)' + AND sd.status = 'ACTIVE' + ), + + submission_stats AS ( + SELECT + ss.stage_id, + COUNT(*) as total_submissions, + COUNT(CASE WHEN ss.review_status = 'APPROVED' THEN 1 END) as approved_submissions, + COUNT(CASE WHEN ss.review_status = 'REJECTED' THEN 1 END) as rejected_submissions, + COUNT(CASE WHEN ss.review_status = 'PENDING' OR ss.review_status IS NULL THEN 1 END) as pending_submissions, + SUM(ss.total_files) as total_files, + SUM(ss.total_file_size) as total_file_size, + MAX(ss.revision_number) as latest_revision_number + FROM stage_submissions ss + GROUP BY ss.stage_id + ), + + latest_submission AS ( + SELECT DISTINCT ON (stage_id) + stage_id, + id as submission_id, + revision_number, + submission_status, + submitted_at, + submitted_by, + review_status, + sync_status, + buyer_system_status, + last_synced_at, + total_files_to_sync, + synced_files_count, + CASE + WHEN total_files_to_sync > 0 + THEN ROUND((synced_files_count * 100.0) / total_files_to_sync) + ELSE 0 + END as sync_progress + FROM stage_submissions + ORDER BY stage_id, revision_number DESC + ), + + submission_history_agg AS ( + SELECT + ss.stage_id, + json_agg( + json_build_object( + 'submissionId', ss.id, + 'revisionNumber', ss.revision_number, + 'status', ss.submission_status, + 'submittedAt', ss.submitted_at, + 'submittedBy', ss.submitted_by, + 'reviewStatus', ss.review_status, + 'syncStatus', ss.sync_status, + 'fileCount', ss.total_files + ) ORDER BY ss.revision_number DESC + ) as submission_history + FROM stage_submissions ss + GROUP BY ss.stage_id + ) + + SELECT + ed.document_id, + ed.doc_number, + ed.vendor_doc_number, + ed.document_title, + ed.project_id, + ed.project_code, + ed.vendor_id, + ed.vendor_name, + ed.vendor_code, + + ist.id as stage_id, + ist.stage_name, + ist.stage_order, + ist.stage_status, + ist.plan_date as stage_plan_date, + + ls.submission_id as latest_submission_id, + ls.revision_number as latest_revision_number, + ls.submission_status as latest_submission_status, + ls.submitted_at as latest_submitted_at, + ls.submitted_by as latest_submitted_by, + ls.review_status as latest_review_status, + + -- 동기화 상태 + ls.sync_status as latest_sync_status, + ls.buyer_system_status as latest_buyer_system_status, + ls.last_synced_at, + ls.total_files_to_sync, + ls.synced_files_count, + ls.sync_progress, + + COALESCE(ss.total_submissions, 0) as total_submissions, + COALESCE(ss.approved_submissions, 0) as approved_submissions, + COALESCE(ss.rejected_submissions, 0) as rejected_submissions, + COALESCE(ss.pending_submissions, 0) as pending_submissions, + COALESCE(ss.total_files, 0) as total_files, + COALESCE(ss.total_file_size, 0) as total_file_size, + + -- 제출이 필요한지 판단 + CASE + WHEN ist.stage_status IN ('PLANNED', 'IN_PROGRESS', 'REJECTED') + AND (ls.review_status IS NULL OR ls.review_status != 'APPROVED') + THEN true + ELSE false + END as requires_submission, + + -- 동기화가 필요한지 판단 + CASE + WHEN ls.sync_status IN ('pending', 'failed') + OR ls.synced_files_count < ls.total_files_to_sync + THEN true + ELSE false + END as requires_sync, + + -- 기한 관련 + CASE + WHEN ist.plan_date < CURRENT_DATE + AND ist.stage_status NOT IN ('COMPLETED', 'APPROVED') + THEN true + ELSE false + END as is_overdue, + + CASE + WHEN ist.plan_date IS NOT NULL + THEN ist.plan_date - CURRENT_DATE + ELSE NULL + END as days_until_due, + + COALESCE(sha.submission_history, '[]'::json) as submission_history + + FROM eligible_documents ed + INNER JOIN stage_issue_stages ist ON ed.document_id = ist.document_id + LEFT JOIN submission_stats ss ON ist.id = ss.stage_id + LEFT JOIN latest_submission ls ON ist.id = ls.stage_id + LEFT JOIN submission_history_agg sha ON ist.id = sha.stage_id + + ORDER BY ed.document_id, ist.stage_order +);--> statement-breakpoint +CREATE VIEW "public"."document_stages_view" AS ( + .0 + 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 +);
\ No newline at end of file |