-- Migration: Add parent_document_id to documents table
-- Date: 2025-10-05
-- Description: Adds self-referencing parent_document_id field to support document hierarchies

-- Add parent_document_id column
ALTER TABLE documents 
ADD COLUMN IF NOT EXISTS parent_document_id uuid DEFAULT NULL;

-- Add foreign key constraint (self-referencing)
ALTER TABLE documents 
ADD CONSTRAINT fk_documents_parent 
FOREIGN KEY (parent_document_id) 
REFERENCES documents(document_id) 
ON DELETE SET NULL;

-- Add index for performance when querying child documents
CREATE INDEX IF NOT EXISTS idx_documents_parent 
ON documents (parent_document_id) 
WHERE parent_document_id IS NOT NULL;

-- Add comment explaining the purpose
COMMENT ON COLUMN documents.parent_document_id IS 'Self-referencing parent document ID for hierarchical relationships (replies, versions, related docs, attachments, etc.)';

-- Verify the change
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns
WHERE table_name = 'documents' 
AND column_name = 'parent_document_id';
