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