# Document File Metadata Extraction

## Overview

This document describes the automatic file metadata extraction and storage feature that has been implemented for the TAF ERP document management system.

## Features

When documents are uploaded via either the `create()` or `simpleUpload()` endpoints, the system now automatically extracts and stores file metadata in the `document_metadata` table.

### Extracted Metadata

The following metadata is automatically extracted and stored for each uploaded file:

1. **mime_type** (text, searchable, indexed)
   - Auto-detected MIME type (e.g., `application/pdf`, `image/jpeg`)
   - Uses PHP's `finfo_open()` or `mime_content_type()`

2. **original_filename** (text, searchable)
   - Original filename as provided by the user
   - Preserved for display and search purposes

3. **file_size** (number)
   - File size in bytes (stored as string for consistency)
   - Can be formatted for display (e.g., "1.5 MB")

4. **file_extension** (text, searchable, indexed)
   - File extension extracted from filename (e.g., `pdf`, `docx`, `jpg`)
   - Useful for filtering and search

5. **creation_date** (date)
   - File creation timestamp in ISO 8601 format
   - Based on filesystem metadata

6. **author** (text)
   - File owner/author from filesystem
   - Uses POSIX functions if available

## Implementation Details

### Backend (PHP)

#### New Reusable Function

```php
private function extractAndStoreFileMetadata(
    array $file, 
    string $documentId, 
    ?string $versionId = null
): array
```

This private method:
1. Uses `MetadataExtractor` service to extract MIME type, creation date, and author
2. Adds file-specific metadata (filename, size, extension)
3. Stores each metadata field in `document_metadata` table with appropriate value types
4. Sets searchable and indexed flags for relevant fields
5. Skips empty or null values
6. Returns the extracted metadata array

#### Updated Methods

**simpleUpload()** (Line ~280)
```php
// Extract and store file metadata
$this->extractAndStoreFileMetadata($_FILES['file'], $documentId, $versionId);
```

**create()** (Line ~110)
```php
// Extract and store file metadata using reusable function
$this->extractAndStoreFileMetadata($_FILES['file'], $id, $verId);
```

### Frontend (JavaScript)

#### API Response

When fetching documents via `/api/documents?case_id=...` or similar endpoints, the response includes a `metadata` field containing all extracted metadata:

```javascript
{
  "document_id": "uuid-...",
  "title": "Contract.pdf",
  "file_size": 123456,
  "tags": ["contract", "legal"],
  "metadata": {
    "mime_type": "application/pdf",
    "original_filename": "Contract.pdf",
    "file_size": "123456",
    "file_extension": "pdf",
    "creation_date": "2024-01-15T10:30:00+00:00",
    "case_id": "uuid-...",
    "case_number": "CASE-2024-001"
  }
}
```

#### Accessing Metadata in UI

The metadata is available in the document object and can be displayed:

```javascript
// Display file type icon based on extension
const extension = doc.metadata?.file_extension || 'unknown';
const icon = getFileIcon(extension);

// Show file info tooltip
const tooltip = `
  Type: ${doc.metadata?.mime_type || 'Unknown'}
  Size: ${formatFileSize(doc.metadata?.file_size || 0)}
  Original: ${doc.metadata?.original_filename || 'N/A'}
`;
```

## Database Schema

### documents Table

```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,
  folder_id             uuid DEFAULT NULL,
  category_id           uuid DEFAULT NULL,
  org_id                uuid DEFAULT NULL,
  title                 text NOT NULL,
  description           text DEFAULT NULL,
  language_code         text DEFAULT 'en',
  owner_id              uuid NOT NULL,
  connector_id          uuid DEFAULT NULL,
  type                  varchar(50) DEFAULT NULL,
  expiry_date           date DEFAULT NULL,
  isArchived            BOOLEAN NOT NULL DEFAULT FALSE,
  isDeleted             BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt             timestamptz NOT NULL DEFAULT now()
);

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

#### parent_document_id Use Cases

The `parent_document_id` field enables hierarchical document relationships:

1. **Document Threads/Replies**: Email-style threading where replies reference the original document
2. **Version Chains**: Link document revisions together (complementary to document_versions)
3. **Attachments**: Mark supplementary documents as children of a main document
4. **Related Documents**: Group related documents (cover letter → resume, quote → contract)
5. **Document Decomposition**: Split large documents into parts (chapters, sections)

**Example Query - Get all child documents:**
```sql
SELECT * FROM documents 
WHERE parent_document_id = '...' 
AND isDeleted = FALSE
ORDER BY updatedAt ASC;
```

**Example Query - Get document with all children:**
```sql
WITH RECURSIVE doc_tree AS (
  -- Start with parent
  SELECT document_id, parent_document_id, title, 0 as level
  FROM documents
  WHERE document_id = '...'
  
  UNION ALL
  
  -- Get all descendants
  SELECT d.document_id, d.parent_document_id, d.title, dt.level + 1
  FROM documents d
  INNER JOIN doc_tree dt ON d.parent_document_id = dt.document_id
  WHERE d.isDeleted = FALSE
)
SELECT * FROM doc_tree ORDER BY level, updatedAt;
```

### document_metadata Table

```sql
CREATE TABLE document_metadata (
  document_metadata_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  document_version_id     uuid,  -- Optional: links to specific version
  document_id             uuid REFERENCES documents(document_id) ON DELETE CASCADE,
  metadata_key            varchar(100) NOT NULL,
  metadata_value          text,
  value_type              varchar(20) DEFAULT 'text' 
    CHECK (value_type IN ('text','number','date','uuid','json')),
  searchable              BOOLEAN DEFAULT true,
  indexed                 BOOLEAN DEFAULT false,
  isDeleted               BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt               timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_document_metadata_doc_key UNIQUE (document_id, metadata_key)
);
```

### Metadata Value Types

- **text**: String values (author, mime_type, original_filename, file_extension)
- **number**: Numeric values (file_size)
- **date**: ISO 8601 timestamps (creation_date)
- **uuid**: UUID values (case_id, client_id, etc.)
- **json**: JSON objects (complex structured data)

### Searchable and Indexed Flags

- **searchable**: Field appears in full-text search results
  - `original_filename`, `mime_type`, `file_extension`

- **indexed**: Field has database index for fast filtering
  - `mime_type`, `file_extension`

## Usage Examples

### Backend: Upload with Automatic Metadata Extraction

```php
// Simple upload - metadata extracted automatically
$response = $documentsController->simpleUpload();
// Returns:
// {
//   "document_id": "...",
//   "version_id": "...",
//   "file": {
//     "name": "document.pdf",
//     "size": 123456,
//     "type": "application/pdf"
//   },
//   "linked": true
// }
// Metadata stored in document_metadata table
```

### Frontend: Search by File Type

```javascript
// Find all PDF documents
const response = await fetch('../api/documents/search-by-metadata?key=file_extension&value=pdf');
const pdfs = await response.json();
```

### Frontend: Filter by MIME Type

```javascript
// Find all images
const response = await fetch('../api/documents/search-by-metadata?key=mime_type&value=image/jpeg');
const images = await response.json();
```

## Testing

### PHPUnit Tests

Run the test suite to verify metadata extraction:

```bash
vendor/bin/phpunit api/tests/DocumentsControllerTest.php
```

### Test Coverage

The test suite includes:
1. Basic metadata extraction (MIME type, creation date)
2. File metadata inclusion (filename, size, extension)
3. Value type assignment
4. Searchable/indexed flag configuration
5. Empty value filtering
6. Error handling for missing files

### Manual Testing

1. Upload a document via simple upload form
2. Verify document appears in list
3. Check database for metadata entries:
   ```sql
   SELECT * FROM document_metadata WHERE document_id = '...';
   ```
4. Verify metadata returned by API:
   ```bash
   curl http://localhost/api/documents?case_id=...
   ```

## Benefits

1. **Automatic**: No manual metadata entry required
2. **Consistent**: Same extraction logic for all upload methods
3. **Searchable**: File type and extension indexed for fast search
4. **Reusable**: Single function used by multiple controllers
5. **Extensible**: Easy to add new metadata extractors (OCR, dimensions, etc.)
6. **Type-safe**: Metadata stored with appropriate value types

## Future Enhancements

The `MetadataExtractor` service includes a TODO comment for AI/ML-based content classification:

```php
// TODO: Implement AI/ML based classification of file content
```

Potential enhancements:
- **Image Analysis**: Extract dimensions, orientation, color profile
- **PDF Analysis**: Extract page count, author, keywords
- **Document Classification**: Auto-tag based on content analysis
- **OCR Integration**: Extract text from images/scans
- **Video Metadata**: Extract duration, resolution, codec
- **Audio Metadata**: Extract duration, bitrate, artist/album

## Troubleshooting

### Metadata Not Appearing

1. Check upload succeeded: Look for `document_id` in response
2. Verify database entries:
   ```sql
   SELECT * FROM document_metadata WHERE document_id = '...';
   ```
3. Check error logs for MetadataExtractor failures
4. Verify PHP `finfo` extension is enabled

### Performance Concerns

- Metadata extraction adds minimal overhead (<100ms per file)
- Extraction happens during upload (one-time cost)
- Indexes on `mime_type` and `file_extension` ensure fast queries
- Consider caching metadata queries if needed

## Related Documentation

- `docs/DocumentManagement.md` - Overall document management system
- `api/services/MetadataExtractor.php` - Metadata extraction service
- `api/controllers/DocumentsController.php` - Upload endpoints
- `FrontEnd/js/services/DocumentCache.js` - Frontend caching
- `FrontEnd/js/widgets/documentTable.js` - Document table UI