# Folder-Based Document Management System

## Overview

The TAF ERP system now includes a unified document/folder management system where **folders are special documents** with `mime_type='folder'`. This creates a clean hierarchy using the `parent_document_id` field, eliminating the need for separate folder tables.

## Key Concepts

### Folders as Documents

- **Folders are documents** with metadata `mime_type='folder'`
- **Regular files** are documents with actual file metadata (pdf, docx, etc.)
- **Hierarchy** is established through `parent_document_id`:
  - `parent_document_id = NULL` → Root level
  - `parent_document_id = folder_id` → Inside a folder

### Navigation Flow

1. **Initial View**: Only root-level documents/folders (parent_document_id IS NULL)
2. **Folder Click**: Navigate into folder, showing its contents
3. **Breadcrumb**: Shows current path, allows navigation back
4. **Upload**: Files uploaded to current folder get parent_document_id set

## Database Schema

### Documents Table (Enhanced)

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

### Metadata for Folders

```sql
-- Folders are identified by this metadata entry
INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type, indexed)
VALUES ('folder-uuid', 'mime_type', 'folder', 'text', true);
```

## API Endpoints

### 1. Create Folder

**POST** `/api/documents/create-folder`

Creates a new folder (document with mime_type='folder').

**Request Body**:
```json
{
  "title": "My Documents",
  "description": "Optional description",
  "parent_document_id": "parent-folder-uuid or null",
  "entity_type": "case",
  "entity_id": "case-uuid"
}
```

**Response**:
```json
{
  "folder_id": "new-folder-uuid",
  "title": "My Documents",
  "parent_document_id": "parent-folder-uuid"
}
```

### 2. Get Root Documents

**GET** `/api/documents/root?entity_type=case&entity_id={uuid}`

Returns all root-level documents and folders for an entity (where parent_document_id IS NULL).

**Query Parameters**:
- `entity_type`: Entity type (case, client, etc.)
- `entity_id`: Entity UUID

**Response**:
```json
[
  {
    "document_id": "uuid",
    "parent_document_id": null,
    "title": "Contracts Folder",
    "type": "folder",
    "is_folder": true,
    "metadata": {
      "mime_type": "folder",
      "case_id": "case-uuid"
    },
    "tags": ["folder", "case"]
  },
  {
    "document_id": "uuid",
    "parent_document_id": null,
    "title": "Report.pdf",
    "type": "form-upload",
    "is_folder": false,
    "metadata": {
      "mime_type": "application/pdf",
      "file_size": "12345",
      "file_extension": "pdf"
    },
    "tags": ["case"]
  }
]
```

**Note**: Results are sorted with folders first, then by update date.

### 3. Get Folder Contents

**GET** `/api/documents/folder/{folderId}/contents`

Returns all documents inside a specific folder.

**Response**:
```json
{
  "folder": {
    "document_id": "folder-uuid",
    "title": "My Folder"
  },
  "contents": [
    {
      "document_id": "doc-uuid",
      "parent_document_id": "folder-uuid",
      "title": "Document.pdf",
      "is_folder": false,
      "metadata": {...},
      "tags": [...]
    }
  ]
}
```

### 4. Move Document/Folder

**PUT** `/api/documents/{id}/move`

Move a document or folder to another location.

**Request Body**:
```json
{
  "parent_document_id": "target-folder-uuid or null"
}
```

- `null` = Move to root level
- `folder-uuid` = Move into folder

**Validation**:
- Target must be a folder (mime_type='folder')
- Prevents circular references (can't move folder into itself or descendants)

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

### 5. Upload to Folder

**POST** `/api/documents/simple-upload`

**FormData Fields**:
- `file`: The file to upload
- `title`: Document title (optional, defaults to filename)
- `entity_type`: Entity type (case, client, etc.)
- `entity_id`: Entity UUID
- `entity_data`: JSON string with additional metadata
- `parent_document_id`: Folder UUID (optional, null for root)

**Example**:
```javascript
const formData = new FormData();
formData.append('file', fileBlob);
formData.append('title', 'Contract.pdf');
formData.append('entity_type', 'case');
formData.append('entity_id', 'case-uuid');
formData.append('parent_document_id', 'folder-uuid'); // Upload to folder

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

## Frontend Components

### Folder Browser Widget

**Location**: `/FrontEnd/js/widgets/folderBrowser.js`

**Features**:
- Breadcrumb navigation
- Create folders
- Upload files to current folder
- Move documents/folders
- Preview/download files
- Tag management
- Delete documents/folders

**Usage**:
```javascript
import { createFolderBrowser } from 'widgets/folderBrowser';

const browser = createFolderBrowser({
  $target: $('#container'),
  entityType: 'case',
  entityId: 'case-uuid',
  formatFileSize: (bytes) => `${bytes} bytes`,
  onUpload: (currentFolderId) => {
    // Handle upload with folder context
  }
});

// Refresh browser
browser.refresh();

// Get current folder ID
const folderId = browser.getCurrentFolderId();

// Cleanup
browser.destroy();
```

### UI Elements

#### Toolbar
- **📁 New Folder**: Create a new folder in current location
- **📤 Upload File**: Upload files to current folder

#### Breadcrumb Navigation
Shows current path and allows clicking to navigate back:
```
📁 Root > Contracts > 2024
```

#### Document/Folder List

| Icon | Name | Type | Tags | Size | Modified | Actions |
|------|------|------|------|------|----------|---------|
| 📁 | Contracts | Folder | — | — | 2024-10-05 | 🏷️ 📦 🗑️ |
| 📄 | Report.pdf | application/pdf | contract | 1.5 MB | 2024-10-05 | 👁️ ⬇️ 🏷️ 📦 🗑️ |

**Folder Rows**: Clickable to navigate into folder
**File Rows**: Cannot be navigated into

**Actions**:
- **👁️ Preview**: View document (files only)
- **⬇️ Download**: Download document (files only)
- **🏷️ Tags**: Manage tags
- **📦 Move**: Move to another folder
- **🗑️ Delete**: Delete document/folder

### Integration with ModalBuilder

The folder browser is automatically integrated into ModalBuilder's document widget:

**Module Configuration**:
```json
{
  "ui": {
    "sections": [
      {
        "id": "documents",
        "title": "Documents",
        "elements": [
          {
            "type": "documents",
            "options": {
              "filterField": "case_id",
              "metadataFields": {
                "case_number": "case_number",
                "case_title": "title"
              }
            }
          }
        ]
      }
    ]
  }
}
```

## User Workflow

### Creating a Folder Structure

1. **Open Document Section**: User opens case/client documents
2. **Create Root Folder**: Click "📁 New Folder", enter "Contracts"
3. **Navigate Into Folder**: Click on "Contracts" folder
4. **Create Subfolder**: Click "📁 New Folder", enter "2024"
5. **Navigate Into Subfolder**: Click on "2024" folder
6. **Upload Files**: Click "📤 Upload File", select files

**Result**:
```
Root
└── Contracts (folder)
    └── 2024 (folder)
        ├── Contract_A.pdf
        └── Contract_B.pdf
```

### Moving Documents

1. **Select Document**: Click **📦 Move** button on document row
2. **Enter Target**: Enter target folder ID (or leave empty for root)
3. **Confirm**: Document moves to new location

**Future Enhancement**: Replace text input with visual folder picker modal.

### Navigating Back

Use breadcrumb navigation:
- Click "Root" to return to root level
- Click any folder in path to return to that level

## Backend Logic

### Folder Creation

```php
// Create folder document
$folderId = $qb->table('documents', 'document_id')->insert([
    'title' => 'My Folder',
    'owner_id' => $uid,
    'type' => 'folder',
    'parent_document_id' => $parentFolderId ?? null
]);

// Set mime_type metadata
$qb->table('document_metadata', 'document_metadata_id')->insert([
    'document_id' => $folderId,
    'metadata_key' => 'mime_type',
    'metadata_value' => 'folder',
    'value_type' => 'text',
    'indexed' => true
]);

// Add folder tag
$qb->table('document_tags', 'document_tag_id')->insert([
    'document_id' => $folderId,
    'tag' => 'folder',
    'tag_type' => 'system'
]);
```

### Retrieving Folder Contents

```php
// Get all documents where parent_document_id = folder_id
$rows = $qb->table('documents d')
    ->where('d.parent_document_id', $folderId)
    ->where('d.isdeleted', false)
    ->orderBy("CASE WHEN d.type = 'folder' THEN 0 ELSE 1 END, d.updatedat", 'DESC')
    ->get();
```

**Sorting**: Folders appear first, then files by date.

### Move Validation

```php
// Prevent circular references
WITH RECURSIVE doc_tree AS (
  SELECT document_id, parent_document_id
  FROM documents
  WHERE document_id = $targetFolderId
  
  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
)
SELECT 1 FROM doc_tree WHERE document_id = $documentToMove;
```

If this query returns a row, moving would create a circular reference.

## Benefits

### 1. Unified Model
- No separate folder tables
- Folders and documents share same permissions/tags/metadata
- Single API for both

### 2. Flexible Hierarchy
- Unlimited depth (within reason)
- Easy to restructure
- Parent deletion handling (ON DELETE SET NULL)

### 3. Metadata Consistency
- All documents have metadata (including folders)
- Folders can have tags, descriptions, etc.
- Entity linking works for folders too

### 4. Simple Queries
- Parent-child relationship is just one field
- Easy to implement navigation
- Efficient with proper indexes

## Security

### Permission Requirements

| Action | Permission Required |
|--------|-------------------|
| Create Folder | `documents.create` |
| View Folders/Documents | `documents.getAll` |
| Move Document/Folder | `documents.update` |
| Delete Document/Folder | `documents.delete` |

### Access Control

- Folders inherit entity linkage (case_id, client_id)
- Documents inside folders maintain their own entity links
- Permission checks apply to all documents regardless of folder

### Validation

- Folder existence checked before move
- Circular reference prevention
- Soft delete prevents data loss

## Performance Considerations

### Indexes

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

-- Index on mime_type for folder identification
CREATE INDEX IF NOT EXISTS idx_doc_metadata_mime_type 
ON document_metadata (metadata_value) 
WHERE metadata_key = 'mime_type';
```

### Query Optimization

- Folders sorted first in results (CASE expression in ORDER BY)
- Metadata aggregated with json_agg for single query
- LEFT JOIN for optional relationships

### Caching

The document cache service automatically invalidates on:
- `documentUploaded` event
- `documentDeleted` event
- `documentTagsUpdated` event
- Folder operations trigger document events

## Limitations & Future Enhancements

### Current Limitations

1. **Move UI**: Currently uses prompt for folder ID (needs visual picker)
2. **Depth Limit**: No hard limit on folder nesting (could impact performance)
3. **Bulk Operations**: Can't move multiple documents at once
4. **Folder Templates**: No preset folder structures
5. **Drag & Drop**: No drag-and-drop file organization

### Planned Enhancements

1. **Folder Picker Modal**: Visual tree selector for move operations
2. **Bulk Move**: Select multiple documents and move together
3. **Folder Templates**: Create preset folder structures for cases
4. **Drag & Drop**: Reorganize files by dragging
5. **Folder Permissions**: Override permissions at folder level
6. **Folder Icons**: Custom icons/colors for folders
7. **Folder Statistics**: Show file count and total size
8. **Recent Folders**: Quick access to frequently used folders
9. **Folder Sharing**: Share entire folder structures
10. **Export Folder**: Download entire folder as ZIP

## Testing

### Manual Testing Checklist

- [ ] Create folder at root level
- [ ] Create subfolder inside folder
- [ ] Upload file to root
- [ ] Upload file to folder
- [ ] Navigate into folder (breadcrumb updates)
- [ ] Navigate back to root
- [ ] Move file to folder
- [ ] Move folder to another folder
- [ ] Try to move folder into itself (should fail)
- [ ] Delete file
- [ ] Delete empty folder
- [ ] Delete folder with contents (contents become root-level)
- [ ] Add tags to folder
- [ ] Add tags to file in folder
- [ ] Preview file in folder
- [ ] Download file from folder

### API Testing

```bash
# Create folder
curl -X POST http://localhost/api/documents/create-folder \
  -H "Content-Type: application/json" \
  -d '{"title":"Test Folder","entity_type":"case","entity_id":"case-uuid"}'

# Get root documents
curl "http://localhost/api/documents/root?entity_type=case&entity_id=case-uuid"

# Get folder contents
curl "http://localhost/api/documents/folder/folder-uuid/contents"

# Move document
curl -X PUT http://localhost/api/documents/doc-uuid/move \
  -H "Content-Type: application/json" \
  -d '{"parent_document_id":"folder-uuid"}'

# Upload to folder
curl -X POST http://localhost/api/documents/simple-upload \
  -F "file=@test.pdf" \
  -F "entity_type=case" \
  -F "entity_id=case-uuid" \
  -F "parent_document_id=folder-uuid"
```

## Troubleshooting

### Folder Not Showing

**Check**: Is `mime_type` metadata set correctly?
```sql
SELECT * FROM document_metadata 
WHERE document_id = 'folder-uuid' AND metadata_key = 'mime_type';
```

### Can't Navigate Into Folder

**Check**: Is folder row clickable? Look for `folder-row` class in HTML.

### Move Operation Fails

**Check**: Circular reference validation or folder existence.

### Documents Not in Folder

**Check**: `parent_document_id` value
```sql
SELECT document_id, title, parent_document_id 
FROM documents 
WHERE parent_document_id = 'folder-uuid';
```

## Related Documentation

- `docs/DocumentHierarchy.md` - parent_document_id feature details
- `docs/DocumentFileMetadataExtraction.md` - File metadata system
- `api/controllers/DocumentsController.php` - Backend implementation
- `FrontEnd/js/widgets/folderBrowser.js` - Frontend widget
- `FrontEnd/js/core/ModalBuilder.js` - Integration with ModalBuilder