path: root/db/migrations/0381_right_master_mold.sql

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