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