# Document Hierarchy Feature (parent_document_id)

## Overview

The `parent_document_id` field in the `documents` table enables hierarchical relationships between documents. This self-referencing foreign key allows documents to have parent-child relationships, supporting various use cases like document threads, version chains, attachments, and related document grouping.

## Database Schema

### documents Table Update

```sql
CREATE TABLE documents (
  document_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  parent_document_id    uuid DEFAULT NULL REFERENCES documents(document_id) ON DELETE SET NULL,
  -- ... other fields ...
);

-- Index for efficient child document queries
CREATE INDEX IF NOT EXISTS idx_documents_parent 
ON documents (parent_document_id) 
WHERE parent_document_id IS NOT NULL;
```

### Key Properties

- **Self-Referencing**: Points to another document in the same table
- **Nullable**: Can be NULL for root/independent documents
- **ON DELETE SET NULL**: If parent is deleted, child becomes a root document
- **Indexed**: Optimized for querying child documents
- **Circular Reference Prevention**: API validates to prevent infinite loops

## Use Cases

### 1. Document Threads/Replies

Similar to email threading where replies reference the original document:

```
Original Document (parent_document_id = NULL)
├── Reply 1 (parent_document_id = original_id)
│   └── Reply 1.1 (parent_document_id = reply1_id)
└── Reply 2 (parent_document_id = original_id)
```

**Example**: Case communication where responses are linked to initial correspondence.

### 2. Document Attachments

Main documents with supplementary files:

```
Main Contract (parent_document_id = NULL)
├── Signature Page (parent_document_id = contract_id)
├── Appendix A (parent_document_id = contract_id)
└── Appendix B (parent_document_id = contract_id)
```

**Example**: A contract with multiple annexes and signature pages.

### 3. Version Chains

Linking document revisions (complementary to document_versions table):

```
Version 1.0 (parent_document_id = NULL)
├── Version 2.0 (parent_document_id = v1_id)
│   └── Version 2.1 (parent_document_id = v2_id)
└── Branch Version (parent_document_id = v1_id)
```

**Example**: Policy documents with revision history accessible by users.

### 4. Related Documents

Grouping logically related documents:

```
Job Application (parent_document_id = NULL)
├── Resume (parent_document_id = application_id)
├── Cover Letter (parent_document_id = application_id)
└── References (parent_document_id = application_id)
```

### 5. Document Decomposition

Breaking large documents into manageable parts:

```
Manual (parent_document_id = NULL)
├── Chapter 1 (parent_document_id = manual_id)
├── Chapter 2 (parent_document_id = manual_id)
└── Chapter 3 (parent_document_id = manual_id)
```

## API Endpoints

### 1. Get Child Documents

**Endpoint**: `GET /api/documents/{id}/children`

**Description**: Retrieves all direct children of a document (one level).

**Response**:
```json
[
  {
    "document_id": "uuid-child-1",
    "parent_document_id": "uuid-parent",
    "title": "Child Document 1",
    "description": "First child",
    "type": "attachment",
    "tags": ["appendix"],
    "metadata": {
      "file_extension": "pdf",
      "file_size": "12345"
    }
  },
  {
    "document_id": "uuid-child-2",
    "parent_document_id": "uuid-parent",
    "title": "Child Document 2",
    "description": "Second child",
    "type": "attachment",
    "tags": ["signature"],
    "metadata": {}
  }
]
```

### 2. Get Document Tree

**Endpoint**: `GET /api/documents/{id}/tree`

**Description**: Retrieves the complete document tree (all descendants) using recursive query.

**Features**:
- Includes all levels (not just direct children)
- Prevents circular references with path tracking
- Returns `level` field indicating depth in tree
- Returns `path` array showing ancestry chain

**Response**:
```json
[
  {
    "document_id": "uuid-root",
    "parent_document_id": null,
    "title": "Root Document",
    "level": 0,
    "path": ["uuid-root"],
    "tags": [],
    "metadata": {}
  },
  {
    "document_id": "uuid-child-1",
    "parent_document_id": "uuid-root",
    "title": "Child 1",
    "level": 1,
    "path": ["uuid-root", "uuid-child-1"],
    "tags": [],
    "metadata": {}
  },
  {
    "document_id": "uuid-grandchild",
    "parent_document_id": "uuid-child-1",
    "title": "Grandchild",
    "level": 2,
    "path": ["uuid-root", "uuid-child-1", "uuid-grandchild"],
    "tags": [],
    "metadata": {}
  }
]
```

### 3. Set Parent Document

**Endpoint**: `PUT /api/documents/{id}/parent`

**Description**: Sets or updates the parent document for a document.

**Request Body**:
```json
{
  "parent_document_id": "uuid-parent"
}
```

To remove parent (make document a root):
```json
{
  "parent_document_id": null
}
```

**Validations**:
1. Parent document must exist and not be deleted
2. Circular reference check (parent cannot be a descendant of child)
3. Requires `documents.update` permission

**Response**:
```json
{
  "success": true,
  "document_id": "uuid-document",
  "parent_document_id": "uuid-parent"
}
```

**Error Response (Circular Reference)**:
```json
{
  "error": "Circular reference detected",
  "message": "Cannot set parent: would create a circular reference"
}
```

## Database Queries

### Get Direct Children

```sql
SELECT * FROM documents 
WHERE parent_document_id = 'uuid-parent' 
  AND isDeleted = FALSE
ORDER BY updatedAt ASC;
```

### Get Document Tree (Recursive)

```sql
WITH RECURSIVE doc_tree AS (
  -- Start with root
  SELECT 
    document_id, 
    parent_document_id, 
    title, 
    0 as level,
    ARRAY[document_id] as path
  FROM documents
  WHERE document_id = 'uuid-root'
    AND isDeleted = FALSE
  
  UNION ALL
  
  -- Get all descendants
  SELECT 
    d.document_id, 
    d.parent_document_id, 
    d.title, 
    dt.level + 1,
    dt.path || d.document_id
  FROM documents d
  INNER JOIN doc_tree dt ON d.parent_document_id = dt.document_id
  WHERE d.isDeleted = FALSE
    AND NOT d.document_id = ANY(dt.path)  -- Prevent circular references
)
SELECT * FROM doc_tree ORDER BY level, updatedAt;
```

### Check for Circular Reference

```sql
WITH RECURSIVE doc_tree AS (
  SELECT document_id, parent_document_id
  FROM documents
  WHERE document_id = 'proposed-parent-id'
  
  UNION ALL
  
  SELECT d.document_id, d.parent_document_id
  FROM documents d
  INNER JOIN doc_tree dt ON d.parent_document_id = dt.document_id
  WHERE d.isDeleted = FALSE
)
SELECT 1 FROM doc_tree WHERE document_id = 'proposed-child-id';
```

If this query returns a row, a circular reference would be created.

### Get Root Documents Only

```sql
SELECT * FROM documents 
WHERE parent_document_id IS NULL 
  AND isDeleted = FALSE
ORDER BY updatedAt DESC;
```

### Count Children

```sql
SELECT 
  parent_document_id,
  COUNT(*) as child_count
FROM documents
WHERE parent_document_id IS NOT NULL
  AND isDeleted = FALSE
GROUP BY parent_document_id;
```

## Frontend Usage

### Upload Child Document

```javascript
// Upload document with parent reference
const formData = new FormData();
formData.append('file', file);
formData.append('title', 'Attachment 1');
formData.append('parent_document_id', parentDocId);
formData.append('entity_type', 'case');
formData.append('entity_id', caseId);

const response = await fetch('../api/documents/simple-upload', {
  method: 'POST',
  body: formData
});

const result = await response.json();
```

### Fetch Child Documents

```javascript
// Get all children of a document
const response = await fetch(`../api/documents/${parentId}/children`);
const children = await response.json();

// Display in UI
children.forEach(child => {
  console.log(`Child: ${child.title} (Level: ${child.level || 1})`);
});
```

### Fetch Document Tree

```javascript
// Get complete document hierarchy
const response = await fetch(`../api/documents/${rootId}/tree`);
const tree = await response.json();

// Build hierarchical UI
const buildTreeUI = (nodes) => {
  const grouped = {};
  
  nodes.forEach(node => {
    if (!grouped[node.level]) grouped[node.level] = [];
    grouped[node.level].push(node);
  });
  
  return grouped;
};
```

### Set/Update Parent

```javascript
// Link document to parent
await fetch(`../api/documents/${docId}/parent`, {
  method: 'PUT',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    parent_document_id: parentId
  })
});

// Remove parent (make root document)
await fetch(`../api/documents/${docId}/parent`, {
  method: 'PUT',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    parent_document_id: null
  })
});
```

## Model Configuration

The `models/documents.php` file has been updated:

```php
'fields' => [
    'parent_document_id' => ['type' => 'uuid', 'nullable' => true],
    // ... other fields
]
```

This allows QueryBuilder to properly handle the parent_document_id field in insert/update operations.

## Migration

To add this field to an existing database:

```bash
psql -U your_user -d your_database -f migrations/add_parent_document_id.sql
```

The migration script:
1. Adds the `parent_document_id` column
2. Creates the foreign key constraint
3. Creates the index
4. Adds documentation comments

## Best Practices

### 1. Avoid Deep Nesting

Keep hierarchies reasonably shallow (3-4 levels max) for performance:

```
✅ GOOD: Root → Child → Grandchild (3 levels)
❌ AVOID: Root → L2 → L3 → L4 → L5 → L6 → L7 (7 levels)
```

### 2. Use Appropriate Types

Set document `type` field to indicate relationship:

```php
// Parent document
['type' => 'contract']

// Child documents
['type' => 'contract-attachment']
['type' => 'contract-signature']
['type' => 'contract-appendix']
```

### 3. Tag Hierarchical Documents

Add tags to identify role in hierarchy:

```php
['tags' => ['main-document']]      // Root
['tags' => ['attachment']]         // Child
['tags' => ['supporting-doc']]     // Child
```

### 4. Metadata for Context

Use metadata to store relationship context:

```php
'metadata' => [
    'relationship_type' => 'attachment',
    'attachment_order' => '1',
    'is_required' => 'true'
]
```

### 5. Clean Up Orphans

Periodically check for orphaned documents if parents were hard-deleted:

```sql
-- Find orphaned documents (parent_document_id points to deleted/missing doc)
SELECT d.document_id, d.title, d.parent_document_id
FROM documents d
LEFT JOIN documents p ON d.parent_document_id = p.document_id
WHERE d.parent_document_id IS NOT NULL
  AND (p.document_id IS NULL OR p.isDeleted = TRUE);
```

## Performance Considerations

### Index Usage

The `idx_documents_parent` index optimizes:
- Child document queries (`WHERE parent_document_id = ?`)
- Tree traversal operations
- Reference validation

### Recursive Query Limits

For very deep trees, consider:
1. Adding recursion depth limits in CTEs
2. Caching tree structures
3. Using materialized views for frequently accessed trees

### Bulk Operations

When working with many documents:

```sql
-- Batch update to set parent
UPDATE documents 
SET parent_document_id = 'uuid-parent'
WHERE document_id = ANY(ARRAY['uuid1', 'uuid2', 'uuid3']);
```

## Security

### Permission Checks

All endpoints require appropriate permissions:
- `getChildren()`: `documents.getAll`
- `getTree()`: `documents.getAll`
- `setParent()`: `documents.update`

### Validation

The API prevents:
1. Setting parent to a non-existent document
2. Creating circular references
3. Setting parent to a deleted document

### Access Control

Consider implementing:
- Parent document permissions inherit to children
- Restrict viewing children based on parent access
- Audit trail for parent-child relationship changes

## Testing

### Manual Testing

```bash
# 1. Create parent document
curl -X POST http://localhost/api/documents/simple-upload \
  -F "file=@parent.pdf" \
  -F "title=Parent Document"

# 2. Create child document
curl -X POST http://localhost/api/documents/simple-upload \
  -F "file=@child.pdf" \
  -F "title=Child Document" \
  -F "parent_document_id=uuid-parent"

# 3. Get children
curl http://localhost/api/documents/uuid-parent/children

# 4. Get tree
curl http://localhost/api/documents/uuid-parent/tree

# 5. Update parent
curl -X PUT http://localhost/api/documents/uuid-child/parent \
  -H "Content-Type: application/json" \
  -d '{"parent_document_id":"uuid-new-parent"}'
```

## Future Enhancements

1. **Bulk Parent Operations**: Set parent for multiple documents at once
2. **Move Subtree**: Move entire document tree to new parent
3. **Copy Hierarchy**: Duplicate document tree structure
4. **Visual Tree Builder**: UI component for managing hierarchies
5. **Auto-Tagging**: Automatically tag based on position in tree
6. **Inherited Metadata**: Propagate metadata from parent to children
7. **Tree Export**: Export entire document tree as ZIP/bundle

## Related Documentation

- `docs/DocumentManagement.md` - Overall document system
- `docs/DocumentFileMetadataExtraction.md` - File metadata features
- `api/controllers/DocumentsController.php` - Implementation
- `TAFDB.pgsql` - Database schema