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 );