# MediaVerse — Media Manager Module Specification

## 1. Module Overview

The Media Manager Module transforms MediaVerse from a simple downloader into a comprehensive media organization platform. It provides sophisticated library management, intelligent search, metadata handling, and seamless media playback capabilities.

### Core Philosophy
- **Non-destructive**: Original files are never modified
- **Metadata-rich**: Extract and preserve all available information
- **Instant access**: Sub-second search across thousands of items
- **Visual first**: Thumbnail-driven browsing experience

---

## 2. Feature Specifications

### 2.1 Library Management

#### Library Structure

```
Library
├── Folders (hierarchical)
│   ├── Personal
│   │   ├── Travel
│   │   └── Family
│   └── Work
│       ├── Projects
│       └── Archive
├── Collections (flat, curated)
│   ├── Favorites
│   ├── Watch Later
│   ├── Project Alpha
│   └── Client Presentations
├── Smart Folders (rule-based)
│   ├── Recently Added (7 days)
│   ├── Unwatched
│   ├── 4K Content
│   └── Downloaded This Month
└── Tags (cross-cutting)
    ├── #tutorial
    ├── #inspiration
    ├── #reference
    └── #archived
```

#### Folder Operations

| Operation | Description | Keyboard Shortcut |
|-----------|-------------|-------------------|
| Create | New folder at current level | Ctrl+Shift+N |
| Rename | Change folder name | F2 |
| Move | Drag to new parent | Drag+Drop |
| Delete | Remove (contents to root) | Delete |
| Duplicate | Copy with contents | Ctrl+D |
| Export | Save folder structure | - |

#### Collection Features

```typescript
interface Collection {
  id: string;
  name: string;
  description?: string;
  coverImage?: string;
  items: CollectionItem[];
  sortOrder: SortConfig;
  createdAt: Date;
  updatedAt: Date;
  isSmart: boolean;
  rules?: SmartRule[];
}

interface CollectionItem {
  mediaId: string;
  addedAt: Date;
  addedBy: string;
  notes?: string;
  position: number;
}
```

### 2.2 Tagging System

#### Tag Architecture

```typescript
interface Tag {
  id: string;
  name: string;
  color: string;           // Hex color code
  icon?: string;          // Lucide icon name
  parentId?: string;      // For hierarchical tags
  aliases: string[];      // Alternative names
  usageCount: number;
  createdAt: Date;
}

interface MediaTag {
  mediaId: string;
  tagId: string;
  confidence: number;     // For auto-tagged items (0-1)
  source: 'manual' | 'auto' | 'imported';
  addedAt: Date;
  addedBy: string;
}
```

#### Auto-Tagging Rules

| Rule Type | Trigger | Example |
|-----------|---------|---------|
| Filename Pattern | Regex match | `/\\btutorial\\b/i` → #tutorial |
| Source Platform | Domain match | youtube.com → #youtube |
| Content Analysis | ML detection | Face detection → #people |
| Metadata Match | Field value | duration < 60s → #shorts |
| Date Range | Temporal | Weekend downloads → #weekend |

#### Tag Operations

- **Quick Tag**: Typeahead with recent tags
- **Bulk Tag**: Apply to selection
- **Smart Suggest**: AI-powered recommendations
- **Tag Cloud**: Visual frequency explorer
- **Tag Merge**: Consolidate duplicates

### 2.3 Search Architecture

#### Search Pipeline

```
Query Input
    │
    ▼
┌─────────────────┐
│ Preprocessing   │  Normalize, tokenize, detect filters
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Query Parsing   │  Extract filters, operators, sort
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Index Lookup    │  FTS5 + metadata indexes
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Scoring/Ranking │  BM25 + personalization
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│ Result Assembly │  Hydrate, format, paginate
└─────────────────┘
```

#### Search Syntax

| Syntax | Description | Example |
|--------|-------------|---------|
| `keyword` | Full-text search | `cinematic` |
| `"phrase"` | Exact phrase | `"4k drone footage"` |
| `tag:#name` | Tag filter | `tag:#tutorial` |
| `folder:path` | Folder scope | `folder:work/projects` |
| `type:video` | Media type | `type:audio` |
| `duration:>60` | Duration filter | `duration:1m..10m` |
| `date:2024` | Date range | `date:>2024-01-01` |
| `platform:youtube` | Source platform | `platform:vimeo` |
| `AND` / `OR` / `NOT` | Boolean logic | `tutorial AND NOT beginner` |
| `sort:date` | Sort order | `sort:duration:desc` |

#### Search Performance Targets

| Metric | Target | Implementation |
|--------|--------|----------------|
| Query latency | <50ms | FTS5 + in-memory cache |
| Index update | <100ms | Async batch processing |
| Result limit | 10,000 | Pagination + virtualization |
| Typing response | <16ms | Debounced + cached |

### 2.4 Smart Organization

#### Smart Folder Rules

```typescript
interface SmartRule {
  id: string;
  field: SmartField;
  operator: Operator;
  value: any;
  logic: 'AND' | 'OR';
}

type SmartField = 
  | 'dateAdded' | 'dateModified' | 'dateDownloaded'
  | 'duration' | 'fileSize' | 'bitrate'
  | 'resolution' | 'codec' | 'format'
  | 'platform' | 'uploader' | 'tags'
  | 'watched' | 'rating' | 'playCount';

type Operator = 
  | 'equals' | 'notEquals'
  | 'contains' | 'notContains'
  | 'greaterThan' | 'lessThan'
  | 'between' | 'in' | 'notIn'
  | 'isEmpty' | 'isNotEmpty'
  | 'matches' | 'startsWith' | 'endsWith';
```

#### Example Smart Folders

```typescript
// Recently Added
{
  name: "Recently Added",
  rules: [
    { field: "dateAdded", operator: "greaterThan", value: "7 days ago" }
  ],
  sort: { field: "dateAdded", direction: "desc" }
}

// Unwatched 4K Content
{
  name: "Unwatched 4K",
  rules: [
    { field: "watched", operator: "equals", value: false },
    { field: "resolution", operator: "greaterThan", value: "2160" }
  ],
  sort: { field: "dateAdded", direction: "desc" }
}

// Long Form Content
{
  name: "Long Form",
  rules: [
    { field: "duration", operator: "greaterThan", value: 1800 },
    { field: "type", operator: "equals", value: "video" }
  ],
  sort: { field: "duration", direction: "desc" }
}
```

### 2.5 History & Activity

#### View History

```typescript
interface ViewEvent {
  id: string;
  mediaId: string;
  startedAt: Date;
  endedAt: Date;
  duration: number;           // Actual watch time
  position: number;           // Resume position
  completed: boolean;         // Watched >90%
  device: string;
  player: 'internal' | 'external';
}

interface HistorySummary {
  totalWatchTime: number;     // Lifetime seconds
  itemsWatched: number;
  averageCompletion: number;  // Percentage
  favoriteGenres: string[];
  peakHours: number[];        // Hour of day
  streakDays: number;         // Consecutive days
}
```

#### Activity Feed

| Event Type | Description | Grouping |
|------------|-------------|----------|
| downloaded | New download | By day |
| watched | Playback completed | By session |
| tagged | Tag applied | By batch |
| organized | Moved to folder | By session |
| shared | Shared collection | Individual |
| converted | Format converted | By day |

---

## 3. Media Schema

### 3.1 Core Media Entity

```typescript
interface Media {
  // Identity
  id: string;                    // UUID v4
  createdAt: Date;
  updatedAt: Date;
  
  // File Information
  filePath: string;               // Absolute path
  fileName: string;
  fileSize: number;               // Bytes
  fileHash: string;               // SHA-256
  format: string;                 // Extension
  
  // Media Type
  type: 'video' | 'audio' | 'image' | 'document';
  mimeType: string;
  
  // Source Information
  source: {
    platform: string;
    url: string;
    contentId: string;
    downloadedAt: Date;
    downloadId: string;           // Reference to download record
  };
  
  // Video Properties (if applicable)
  video?: {
    duration: number;           // Seconds
    width: number;
    height: number;
    frameRate: number;
    codec: string;
    bitrate: number;              // bps
    colorSpace: string;
    hdrFormat?: string;
  };
  
  // Audio Properties (if applicable)
  audio?: {
    duration: number;
    codec: string;
    bitrate: number;
    sampleRate: number;
    channels: number;
    language?: string;
  };
  
  // Metadata
  metadata: {
    title?: string;
    description?: string;
    uploader?: string;
    uploadDate?: Date;
    category?: string;
    license?: string;
    tags: string[];              // Platform tags
  };
  
  // Thumbnails
  thumbnails: {
    original?: string;          // URL or path
    generated: ThumbnailSet;
  };
  
  // User Data
  userData: {
    rating?: number;            // 1-5 stars
    notes?: string;
    watched: boolean;
    watchCount: number;
    lastPosition: number;        // Resume point
    addedToLibrary: Date;
    customMetadata: Record<string, any>;
  };
  
  // Organization
  folderId?: string;
  collectionIds: string[];
  tags: string[];
  
  // Status
  status: 'active' | 'missing' | 'corrupted' | 'archived';
  indexVersion: number;         // For incremental updates
}
```

### 3.2 Thumbnail Schema

```typescript
interface ThumbnailSet {
  original?: string;            // Source thumbnail URL/path
  
  // Generated variants
  tiny: string;                 // 64x36, blur hash
  small: string;                // 160x90
  medium: string;               // 320x180
  large: string;                // 640x360
  poster: string;               // 1280x720
  
  // Video-specific
  sprite?: string;              // WebVTT sprite sheet
  keyframes?: number[];         // Timestamps for scrubbing
  
  generatedAt: Date;
  generationMethod: 'extract' | 'generate' | 'download';
}
```

### 3.3 Subtitle Schema

```typescript
interface SubtitleTrack {
  id: string;
  mediaId: string;
  language: string;             // ISO 639-1
  label?: string;               // Display name
  format: 'srt' | 'vtt' | 'ass' | 'embedded';
  filePath?: string;            // External file
  streamIndex?: number;         // Embedded stream
  isDefault: boolean;
  isForced: boolean;
  isSDH: boolean;               // Subtitles for deaf/hard of hearing
  downloadedAt: Date;
}
```

---

## 4. Search Architecture

### 4.1 Full-Text Search Implementation

**SQLite FTS5 Configuration:**

```sql
-- Virtual table for full-text search
CREATE VIRTUAL TABLE media_fts USING fts5(
  title,
  description,
  uploader,
  tags,
  content='media',              -- External content table
  content_rowid='id'
);

-- Triggers to keep FTS index synchronized
CREATE TRIGGER media_ai AFTER INSERT ON media BEGIN
  INSERT INTO media_fts(rowid, title, description, uploader, tags)
  VALUES (new.id, new.title, new.description, new.uploader, new.tags);
END;

CREATE TRIGGER media_ad AFTER DELETE ON media BEGIN
  INSERT INTO media_fts(media_fts, rowid, title, description, uploader, tags)
  VALUES ('delete', old.id, old.title, old.description, old.uploader, old.tags);
END;

CREATE TRIGGER media_au AFTER UPDATE ON media BEGIN
  INSERT INTO media_fts(media_fts, rowid, title, description, uploader, tags)
  VALUES ('delete', old.id, old.title, old.description, old.uploader, old.tags);
  INSERT INTO media_fts(rowid, title, description, uploader, tags)
  VALUES (new.id, new.title, new.description, new.uploader, new.tags);
END;
```

**Search Query Builder:**

```typescript
class SearchBuilder {
  private query: string = '';
  private params: any[] = [];
  
  text(search: string): this {
    // Escape FTS5 special characters
    const escaped = search
      .replace(/"/g, '""')
      .replace(/'/g, "''");
    this.query += `media_fts MATCH ?`;
    this.params.push(escaped);
    return this;
  }
  
  filter(field: string, operator: string, value: any): this {
    const sqlOp = this.mapOperator(operator);
    this.query += ` AND ${field} ${sqlOp} ?`;
    this.params.push(value);
    return this;
  }
  
  sort(field: string, direction: 'asc' | 'desc'): this {
    this.query += ` ORDER BY ${field} ${direction.toUpperCase()}`;
    return this;
  }
  
  build(): { sql: string; params: any[] } {
    return {
      sql: `SELECT m.* FROM media m 
            JOIN media_fts ON m.id = media_fts.rowid 
            WHERE ${this.query}`,
      params: this.params
    };
  }
}
```

### 4.2 Faceted Search

```typescript
interface FacetResult {
  field: string;
  values: Array<{
    value: string | number;
    count: number;
    selected: boolean;
  }>;
}

class FacetedSearch {
  async getFacets(query: string): Promise<FacetResult[]> {
    const facets = [
      'type',
      'platform',
      'resolution',
      'duration_range',
      'date_added_month',
      'tags'
    ];
    
    return Promise.all(facets.map(f => this.getFacet(f, query)));
  }
  
  private async getFacet(field: string, query: string): Promise<FacetResult> {
    const sql = `
      SELECT ${field} as value, COUNT(*) as count
      FROM media
      WHERE id IN (${query})
      GROUP BY ${field}
      ORDER BY count DESC
      LIMIT 20
    `;
    // Execute and format...
  }
}
```

### 4.3 Search Suggestions

```typescript
interface Suggestion {
  type: 'recent' | 'popular' | 'tag' | 'folder' | 'media';
  text: string;
  icon?: string;
  action: () => void;
}

class SearchSuggestions {
  async getSuggestions(partial: string): Promise<Suggestion[]> {
    const [recent, tags, media] = await Promise.all([
      this.getRecentSearches(partial),
      this.getTagSuggestions(partial),
      this.getMediaSuggestions(partial)
    ]);
    
    return [
      ...recent.slice(0, 3),
      ...tags.slice(0, 5),
      ...media.slice(0, 5)
    ];
  }
}
```

---

## 5. Indexing Strategy

### 5.1 Index Types

| Index | Fields | Type | Purpose |
|-------|--------|------|---------|
| `idx_media_type` | type | B-tree | Type filtering |
| `idx_media_folder` | folderId | B-tree | Folder queries |
| `idx_media_date` | createdAt | B-tree | Date sorting |
| `idx_media_duration` | video.duration | B-tree | Duration filters |
| `idx_media_size` | fileSize | B-tree | Size queries |
| `idx_media_status` | status | B-tree | Status filtering |
| `idx_tags_media` | mediaId, tagId | Composite | Tag lookups |
| `idx_collections_media` | collectionId, position | Composite | Collection ordering |

### 5.2 Incremental Indexing

```typescript
class IndexManager {
  private lastIndexVersion: number = 0;
  
  async incrementalUpdate(): Promise<void> {
    const changes = await db.query(`
      SELECT * FROM media 
      WHERE indexVersion > ?
      ORDER BY updatedAt ASC
    `, [this.lastIndexVersion]);
    
    for (const media of changes) {
      await this.updateIndexes(media);
      await this.generateThumbnails(media);
      await this.extractMetadata(media);
    }
    
    this.lastIndexVersion = Date.now();
  }
  
  async fullReindex(): Promise<void> {
    // Drop and recreate FTS
    // Rebuild all thumbnails
    // Re-extract all metadata
  }
}
```

### 5.3 Background Processing Queue

```typescript
interface IndexJob {
  id: string;
  type: 'thumbnail' | 'metadata' | 'analysis' | 'transcode';
  mediaId: string;
  priority: number;
  createdAt: Date;
  startedAt?: Date;
  completedAt?: Date;
  error?: string;
}

class IndexQueue {
  private queue: PriorityQueue<IndexJob>;
  private workers: number = 4;
  
  async enqueue(job: Omit<IndexJob, 'id'>): Promise<void> {
    const id = generateUUID();
    this.queue.push({ ...job, id }, job.priority);
    await this.persistJob({ ...job, id });
  }
  
  private async processJob(job: IndexJob): Promise<void> {
    try {
      job.startedAt = new Date();
      await this.updateJob(job);
      
      switch (job.type) {
        case 'thumbnail':
          await this.generateThumbnail(job.mediaId);
          break;
        case 'metadata':
          await this.extractMetadata(job.mediaId);
          break;
        case 'analysis':
          await this.analyzeContent(job.mediaId);
          break;
      }
      
      job.completedAt = new Date();
    } catch (error) {
      job.error = error.message;
      await this.scheduleRetry(job);
    }
    
    await this.updateJob(job);
  }
}
```

---

## 6. Caching Strategy

### 6.1 Cache Layers

```
┌─────────────────────────────────────────┐
│  L1: In-Memory (React Query / Zustand)  │
│  • Current view data                      │
│  • Search results                       │
│  • User preferences                       │
│  TTL: Session                           │
├─────────────────────────────────────────┤
│  L2: IndexedDB (Dexie)                  │
│  • Media metadata                       │
│  • Thumbnail blobs                      │
│  • Search index cache                   │
│  TTL: 7 days                            │
├─────────────────────────────────────────┤
│  L3: File System                        │
│  • Generated thumbnails                 │
│  • Downloaded subtitles                 │
│  • Extracted metadata                   │
│  TTL: Persistent                        │
├─────────────────────────────────────────┤
│  L4: SQLite                             │
│  • Source of truth                      │
│  • Full media records                   │
│  • FTS index                            │
└─────────────────────────────────────────┘
```

### 6.2 Cache Invalidation

```typescript
class CacheManager {
  private caches: Map<string, CacheEntry> = new Map();
  
  async get<T>(key: string, fetcher: () => Promise<T>, ttl: number): Promise<T> {
    const cached = this.caches.get(key);
    
    if (cached && !this.isExpired(cached)) {
      return cached.value as T;
    }
    
    const value = await fetcher();
    this.set(key, value, ttl);
    return value;
  }
  
  invalidate(pattern: string): void {
    for (const [key, entry] of this.caches) {
      if (key.match(pattern)) {
        this.caches.delete(key);
      }
    }
  }
  
  invalidateMedia(mediaId: string): void {
    this.invalidate(`media:${mediaId}`);
    this.invalidate(`thumbnails:${mediaId}`);
    this.invalidate(`search:*`); // Invalidate search
  }
}
```

### 6.3 Thumbnail Caching

```typescript
class ThumbnailCache {
  private cacheDir: string;
  private maxSize: number = 1024 * 1024 * 1024; // 1GB
  
  async getThumbnail(mediaId: string, size: string): Promise<Buffer> {
    const path = this.getCachePath(mediaId, size);
    
    if (await this.exists(path)) {
      return fs.readFile(path);
    }
    
    const thumbnail = await this.generateThumbnail(mediaId, size);
    await this.writeCache(path, thumbnail);
    await this.enforceSizeLimit();
    
    return thumbnail;
  }
  
  private async enforceSizeLimit(): Promise<void> {
    const stats = await this.getCacheStats();
    if (stats.totalSize > this.maxSize) {
      // LRU eviction
      const toDelete = stats.files
        .sort((a, b) => a.lastAccessed - b.lastAccessed)
        .slice(0, Math.floor(stats.files.length * 0.2));
      
      for (const file of toDelete) {
        await fs.unlink(file.path);
      }
    }
  }
}
```

---

## 7. SQLite Schema

### 7.1 Complete Database Schema

```sql
-- Core media table
CREATE TABLE media (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  
  -- File info
  file_path TEXT NOT NULL UNIQUE,
  file_name TEXT NOT NULL,
  file_size INTEGER NOT NULL,
  file_hash TEXT,
  format TEXT NOT NULL,
  
  -- Type
  type TEXT NOT NULL CHECK(type IN ('video', 'audio', 'image', 'document')),
  mime_type TEXT NOT NULL,
  
  -- Source
  source_platform TEXT,
  source_url TEXT,
  source_content_id TEXT,
  downloaded_at INTEGER,
  download_id TEXT,
  
  -- Video
  video_duration REAL,
  video_width INTEGER,
  video_height INTEGER,
  video_frame_rate REAL,
  video_codec TEXT,
  video_bitrate INTEGER,
  video_color_space TEXT,
  video_hdr_format TEXT,
  
  -- Audio
  audio_duration REAL,
  audio_codec TEXT,
  audio_bitrate INTEGER,
  audio_sample_rate INTEGER,
  audio_channels INTEGER,
  audio_language TEXT,
  
  -- Metadata
  meta_title TEXT,
  meta_description TEXT,
  meta_uploader TEXT,
  meta_upload_date INTEGER,
  meta_category TEXT,
  meta_license TEXT,
  
  -- Thumbnails
  thumb_original TEXT,
  thumb_tiny TEXT,
  thumb_small TEXT,
  thumb_medium TEXT,
  thumb_large TEXT,
  thumb_poster TEXT,
  thumb_sprite TEXT,
  thumb_generated_at INTEGER,
  thumb_method TEXT,
  
  -- User data
  user_rating REAL,
  user_notes TEXT,
  user_watched INTEGER DEFAULT 0,
  user_watch_count INTEGER DEFAULT 0,
  user_last_position REAL DEFAULT 0,
  user_added_to_library INTEGER,
  user_custom_metadata TEXT, -- JSON
  
  -- Organization
  folder_id TEXT REFERENCES folders(id) ON DELETE SET NULL,
  tags TEXT, -- JSON array
  
  -- Status
  status TEXT DEFAULT 'active' CHECK(status IN ('active', 'missing', 'corrupted', 'archived')),
  index_version INTEGER DEFAULT 0
);

-- Full-text search virtual table
CREATE VIRTUAL TABLE media_fts USING fts5(
  meta_title,
  meta_description,
  meta_uploader,
  tags,
  content='media',
  content_rowid='id'
);

-- Folders
CREATE TABLE folders (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  parent_id TEXT REFERENCES folders(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  path TEXT NOT NULL UNIQUE,
  icon TEXT,
  color TEXT,
  sort_order INTEGER DEFAULT 0,
  expanded INTEGER DEFAULT 1
);

-- Collections
CREATE TABLE collections (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  cover_image TEXT,
  is_smart INTEGER DEFAULT 0,
  rules TEXT, -- JSON for smart collections
  sort_field TEXT DEFAULT 'addedAt',
  sort_direction TEXT DEFAULT 'desc'
);

-- Collection items
CREATE TABLE collection_items (
  id TEXT PRIMARY KEY,
  collection_id TEXT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
  media_id TEXT NOT NULL REFERENCES media(id) ON DELETE CASCADE,
  added_at INTEGER NOT NULL,
  added_by TEXT,
  notes TEXT,
  position INTEGER NOT NULL,
  UNIQUE(collection_id, media_id)
);

-- Tags
CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  name TEXT NOT NULL UNIQUE,
  color TEXT DEFAULT '#6366F1',
  icon TEXT,
  parent_id TEXT REFERENCES tags(id),
  aliases TEXT, -- JSON array
  usage_count INTEGER DEFAULT 0
);

-- Media-Tag relationship
CREATE TABLE media_tags (
  media_id TEXT NOT NULL REFERENCES media(id) ON DELETE CASCADE,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  confidence REAL DEFAULT 1.0,
  source TEXT DEFAULT 'manual',
  added_at INTEGER NOT NULL,
  added_by TEXT,
  PRIMARY KEY (media_id, tag_id)
);

-- View history
CREATE TABLE view_history (
  id TEXT PRIMARY KEY,
  media_id TEXT NOT NULL REFERENCES media(id) ON DELETE CASCADE,
  started_at INTEGER NOT NULL,
  ended_at INTEGER,
  duration INTEGER,
  position REAL,
  completed INTEGER DEFAULT 0,
  device TEXT,
  player TEXT
);

-- Index jobs
CREATE TABLE index_jobs (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  type TEXT NOT NULL,
  media_id TEXT REFERENCES media(id) ON DELETE CASCADE,
  priority INTEGER DEFAULT 0,
  started_at INTEGER,
  completed_at INTEGER,
  error TEXT
);

-- Indexes
CREATE INDEX idx_media_type ON media(type);
CREATE INDEX idx_media_folder ON media(folder_id);
CREATE INDEX idx_media_date ON media(created_at);
CREATE INDEX idx_media_status ON media(status);
CREATE INDEX idx_media_platform ON media(source_platform);
CREATE INDEX idx_folders_parent ON folders(parent_id);
CREATE INDEX idx_collection_items_collection ON collection_items(collection_id);
CREATE INDEX idx_collection_items_media ON collection_items(media_id);
CREATE INDEX idx_media_tags_media ON media_tags(media_id);
CREATE INDEX idx_media_tags_tag ON media_tags(tag_id);
CREATE INDEX idx_view_history_media ON view_history(media_id);
CREATE INDEX idx_view_history_started ON view_history(started_at);

-- Triggers for FTS sync
CREATE TRIGGER media_ai AFTER INSERT ON media BEGIN
  INSERT INTO media_fts(rowid, meta_title, meta_description, meta_uploader, tags)
  VALUES (new.id, new.meta_title, new.meta_description, new.meta_uploader, new.tags);
END;

CREATE TRIGGER media_ad AFTER DELETE ON media BEGIN
  INSERT INTO media_fts(media_fts, rowid, meta_title, meta_description, meta_uploader, tags)
  VALUES ('delete', old.id, old.meta_title, old.meta_description, old.meta_uploader, old.tags);
END;

CREATE TRIGGER media_au AFTER UPDATE ON media BEGIN
  INSERT INTO media_fts(media_fts, rowid, meta_title, meta_description, meta_uploader, tags)
  VALUES ('delete', old.id, old.meta_title, old.meta_description, old.meta_uploader, old.tags);
  INSERT INTO media_fts(rowid, meta_title, meta_description, meta_uploader, tags)
  VALUES (new.id, new.meta_title, new.meta_description, new.meta_uploader, new.tags);
END;
```

---

## 8. Repository Layer

### 8.1 Base Repository

```typescript
abstract class BaseRepository<T> {
  protected db: Database;
  protected table: string;
  
  async findById(id: string): Promise<T | null> {
    const row = await this.db.get(
      `SELECT * FROM ${this.table} WHERE id = ?`,
      [id]
    );
    return row ? this.mapFromDB(row) : null;
  }
  
  async findAll(options?: QueryOptions): Promise<T[]> {
    let sql = `SELECT * FROM ${this.table}`;
    const params: any[] = [];
    
    if (options?.where) {
      const conditions = Object.entries(options.where)
        .map(([k, v]) => `${k} = ?`)
        .join(' AND ');
      sql += ` WHERE ${conditions}`;
      params.push(...Object.values(options.where));
    }
    
    if (options?.orderBy) {
      sql += ` ORDER BY ${options.orderBy.field} ${options.orderBy.direction}`;
    }
    
    if (options?.limit) {
      sql += ` LIMIT ?`;
      params.push(options.limit);
    }
    
    const rows = await this.db.all(sql, params);
    return rows.map(r => this.mapFromDB(r));
  }
  
  async create(entity: Omit<T, 'id'>): Promise<T> {
    const id = generateUUID();
    const row = this.mapToDB({ ...entity, id } as T);
    
    const columns = Object.keys(row).join(', ');
    const placeholders = Object.keys(row).map(() => '?').join(', ');
    
    await this.db.run(
      `INSERT INTO ${this.table} (${columns}) VALUES (${placeholders})`,
      Object.values(row)
    );
    
    return this.findById(id) as Promise<T>;
  }
  
  async update(id: string, updates: Partial<T>): Promise<T> {
    const row = this.mapToDB(updates as T);
    const setClause = Object.keys(row)
      .map(k => `${k} = ?`)
      .join(', ');
    
    await this.db.run(
      `UPDATE ${this.table} SET ${setClause}, updated_at = ? WHERE id = ?`,
      [...Object.values(row), Date.now(), id]
    );
    
    return this.findById(id) as Promise<T>;
  }
  
  async delete(id: string): Promise<void> {
    await this.db.run(`DELETE FROM ${this.table} WHERE id = ?`, [id]);
  }
  
  protected abstract mapFromDB(row: any): T;
  protected abstract mapToDB(entity: T): any;
}
```

### 8.2 Media Repository

```typescript
class MediaRepository extends BaseRepository<Media> {
  protected table = 'media';
  
  async search(query: string, filters?: SearchFilters): Promise<Media[]> {
    let sql = `
      SELECT m.* FROM media m
      JOIN media_fts fts ON m.id = fts.rowid
      WHERE media_fts MATCH ?
    `;
    const params = [query];
    
    if (filters?.type) {
      sql += ` AND m.type = ?`;
      params.push(filters.type);
    }
    
    if (filters?.folderId) {
      sql += ` AND m.folder_id = ?`;
      params.push(filters.folderId);
    }
    
    // ... more filters
    
    sql += ` ORDER BY rank`;
    
    const rows = await this.db.all(sql, params);
    return rows.map(r => this.mapFromDB(r));
  }
  
  async findByFolder(folderId: string | null): Promise<Media[]> {
    const sql = folderId === null
      ? `SELECT * FROM media WHERE folder_id IS NULL ORDER BY created_at DESC`
      : `SELECT * FROM media WHERE folder_id = ? ORDER BY created_at DESC`;
    
    const rows = await this.db.all(sql, folderId ? [folderId] : []);
    return rows.map(r => this.mapFromDB(r));
  }
  
  async findByTag(tagId: string): Promise<Media[]> {
    const rows = await this.db.all(`
      SELECT m.* FROM media m
      JOIN media_tags mt ON m.id = mt.media_id
      WHERE mt.tag_id = ?
      ORDER BY m.created_at DESC
    `, [tagId]);
    
    return rows.map(r => this.mapFromDB(r));
  }
  
  async addTag(mediaId: string, tagId: string, source: string = 'manual'): Promise<void> {
    await this.db.run(`
      INSERT OR IGNORE INTO media_tags (media_id, tag_id, source, added_at)
      VALUES (?, ?, ?, ?)
    `, [mediaId, tagId, source, Date.now()]);
  }
  
  async removeTag(mediaId: string, tagId: string): Promise<void> {
    await this.db.run(`
      DELETE FROM media_tags WHERE media_id = ? AND tag_id = ?
    `, [mediaId, tagId]);
  }
  
  async updateWatchProgress(mediaId: string, position: number, completed: boolean): Promise<void> {
    await this.db.run(`
      UPDATE media SET 
        user_last_position = ?,
        user_watched = ?,
        user_watch_count = user_watch_count + ?
      WHERE id = ?
    `, [position, completed ? 1 : 0, completed ? 1 : 0, mediaId]);
  }
  
  protected mapFromDB(row: any): Media {
    return {
      id: row.id,
      createdAt: new Date(row.created_at),
      // ... map all fields
    };
  }
  
  protected mapToDB(entity: Media): any {
    return {
      id: entity.id,
      created_at: entity.createdAt.getTime(),
      // ... map all fields
    };
  }
}
```

---

## 9. Domain Layer

### 9.1 Media Service

```typescript
class MediaService {
  constructor(
    private mediaRepo: MediaRepository,
    private folderRepo: FolderRepository,
    private tagRepo: TagRepository,
    private thumbnailService: ThumbnailService,
    private indexQueue: IndexQueue
  ) {}
  
  async addMedia(filePath: string, options: AddOptions): Promise<Media> {
    // Validate file exists
    const stats = await fs.stat(filePath);
    if (!stats.isFile()) {
      throw new Error('Path is not a file');
    }
    
    // Check for duplicates
    const hash = await this.computeHash(filePath);
    const existing = await this.mediaRepo.findByHash(hash);
    if (existing) {
      throw new DuplicateError('Media already exists', existing);
    }
    
    // Create media record
    const media = await this.mediaRepo.create({
      filePath,
      fileName: path.basename(filePath),
      fileSize: stats.size,
      fileHash: hash,
      // ... other fields
    });
    
    // Queue background processing
    await this.indexQueue.enqueue({
      type: 'thumbnail',
      mediaId: media.id,
      priority: 10
    });
    
    await this.indexQueue.enqueue({
      type: 'metadata',
      mediaId: media.id,
      priority: 5
    });
    
    return media;
  }
  
  async organizeMedia(mediaId: string, folderId: string | null): Promise<void> {
    const media = await this.mediaRepo.findById(mediaId);
    if (!media) throw new NotFoundError('Media not found');
    
    // Validate folder exists
    if (folderId) {
      const folder = await this.folderRepo.findById(folderId);
      if (!folder) throw new NotFoundError('Folder not found');
    }
    
    await this.mediaRepo.update(mediaId, { folderId });
    
    // Move physical file if needed
    if (options.moveFile) {
      await this.moveFileToFolder(media.filePath, folderId);
    }
  }
  
  async search(query: string, filters?: SearchFilters): Promise<SearchResult> {
    const startTime = performance.now();
    
    // Parse query
    const parsed = this.queryParser.parse(query);
    
    // Execute search
    const media = await this.mediaRepo.search(parsed.text, filters);
    
    // Get facets
    const facets = await this.getFacets(parsed, filters);
    
    // Sort and paginate
    const sorted = this.applySorting(media, parsed.sort);
    const paginated = this.applyPagination(sorted, filters?.page, filters?.limit);
    
    return {
      items: paginated,
      total: media.length,
      facets,
      query: parsed,
      duration: performance.now() - startTime
    };
  }
  
  async getRecommendations(mediaId: string): Promise<Media[]> {
    const media = await this.mediaRepo.findById(mediaId);
    if (!media) throw new NotFoundError('Media not found');
    
    // Content-based: same tags, similar duration
    const similar = await this.mediaRepo.findSimilar({
      tags: media.tags,
      type: media.type,
      durationRange: [media.video?.duration! * 0.8, media.video?.duration! * 1.2]
    });
    
    // Collaborative: users who watched this also watched...
    // (requires view history analysis)
    
    return similar.slice(0, 10);
  }
}
```

### 9.2 Folder Service

```typescript
class FolderService {
  async createFolder(name: string, parentId?: string): Promise<Folder> {
    const path = await this.generatePath(name, parentId);
    
    return this.folderRepo.create({
      name,
      parentId,
      path,
      createdAt: new Date(),
      updatedAt: new Date()
    });
  }
  
  async moveFolder(folderId: string, newParentId?: string): Promise<void> {
    const folder = await this.folderRepo.findById(folderId);
    if (!folder) throw new NotFoundError('Folder not found');
    
    // Prevent circular reference
    if (await this.isDescendant(newParentId, folderId)) {
      throw new Error('Cannot move folder into its own descendant');
    }
    
    const newPath = await this.generatePath(folder.name, newParentId);
    
    await this.folderRepo.update(folderId, {
      parentId: newParentId,
      path: newPath,
      updatedAt: new Date()
    });
    
    // Update all descendant paths
    await this.updateDescendantPaths(folderId, newPath);
  }
  
  async getFolderTree(): Promise<FolderTree> {
    const folders = await this.folderRepo.findAll();
    
    // Build tree structure
    const map = new Map<string, FolderNode>();
    const roots: FolderNode[] = [];
    
    for (const folder of folders) {
      map.set(folder.id, { ...folder, children: [] });
    }
    
    for (const folder of folders) {
      const node = map.get(folder.id)!;
      if (folder.parentId) {
        const parent = map.get(folder.parentId);
        parent?.children.push(node);
      } else {
        roots.push(node);
      }
    }
    
    return { roots, map };
  }
  
  private async generatePath(name: string, parentId?: string): Promise<string> {
    if (!parentId) return `/${slugify(name)}`;
    
    const parent = await this.folderRepo.findById(parentId);
    return `${parent.path}/${slugify(name)}`;
  }
}
```

---

## 10. UI Wireframes

### 10.1 Main Layout

```
┌─────────────────────────────────────────────────────────────────┐
│  MediaVerse                                      [🔍] [⚙️] [👤] │
├──────────┬──────────────────────────────────────────────────────┤
│          │                                                      │
│  LIBRARY │  ┌────────────────────────────────────────────────┐ │
│          │  │  [All] [Videos] [Audio] [Images] [📁 Folders]   │ │
│  📁 All  │  └────────────────────────────────────────────────┘ │
│  🎬 Videos│                                                      │
│  🎵 Audio │  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐   │
│  🖼️ Images│  │ ▶️   │ │ ▶️   │ │ ▶️   │ │ ▶️   │ │ ▶️   │ │ ▶️   │   │
│          │  │     │ │     │ │     │ │     │ │     │ │     │   │
│  ────────│  │Title│ │Title│ │Title│ │Title│ │Title│ │Title│   │
│  FOLDERS │  │ 4K  │ │ 1080│ │ 720 │ │ 4K  │ │ 1080│ │ 720 │   │
│  📂 Work │  │ 12m │ │ 45m │ │ 2h  │ │ 8m  │ │ 1h  │ │ 30m │   │
│  📂 Personal│ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘   │
│  📂 Archive│                                                      │
│          │  ┌────────────────────────────────────────────────┐   │
│  ────────│  │  ⏮️  ⏪  ⏯️  ⏩  ⏭️  ───────⚪──────  🔊    │   │
│  COLLECTIONS│  │  Now Playing: Video Title...          [👁️ Mini]│   │
│  ⭐ Favorites│  └────────────────────────────────────────────────┘   │
│  ⏱️ Recent │                                                      │
│  📥 Downloads│                                                      │
│          │                                                      │
│  [+ New] │                                                      │
│          │                                                      │
└──────────┴──────────────────────────────────────────────────────┘
```

### 10.2 Search Interface

```
┌─────────────────────────────────────────────────────────────────┐
│  🔍 Search: "tutorial lighting" [❌]                          │
├─────────────────────────────────────────────────────────────────┤
│  Filters: [Type ▼] [Duration ▼] [Date ▼] [Quality ▼] [More ▼]  │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  Found 247 results in 23ms                                       │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │  💡 Did you mean: "studio lighting tutorial"?              │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │  📊 Facets:                                                 │ │
│  │  Type: Video (198) | Audio (49)                             │ │
│  │  Platform: YouTube (156) | Vimeo (45) | Other (46)          │ │
│  │  Duration: <5m (89) | 5-30m (98) | >30m (60)               │ │
│  │  Quality: 4K (23) | 1080p (156) | 720p (68)                 │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                                                                  │
│  [Grid/List] Sort: [Relevance ▼]    Page 1 of 25                │
│                                                                  │
│  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐               │
│  │ ... │ │ ... │ │ ... │ │ ... │ │ ... │ │ ... │               │
│  └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘               │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘
```

### 10.3 Media Detail View

```
┌─────────────────────────────────────────────────────────────────┐
│  [← Back]              Video Title Here              [✏️] [🗑️] │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌─────────────────────────────────────┐  ┌─────────────────┐  │
│  │                                     │  │ Metadata        │  │
│  │         [Video Player]              │  │ ─────────────── │  │
│  │                                     │  │ Duration: 12:34 │  │
│  │         ▶️  (click to play)         │  │ Quality: 1080p  │  │
│  │                                     │  │ Size: 234 MB    │  │
│  │                                     │  │ Format: MP4     │  │
│  │                                     │  │ Codec: H.264    │  │
│  │                                     │  │ ─────────────── │  │
│  │         [Progress Bar]              │  │ Source          │  │
│  │                                     │  │ Platform: YT    │  │
│  │         [⏮️] [⏪] [⏯️] [⏩] [⏭️]      │  │ Uploader: Name  │  │
│  │                                     │  │ Date: 2024-01-15│  │
│  │         [🔊] [⚙️] [⛶] [💬]         │  │ ─────────────── │  │
│  │                                     │  │ Tags            │  │
│  └─────────────────────────────────────┘  │ #tutorial #light│  │
│                                          │ ─────────────── │  │
│  [▶️ Play] [➕ Add to Collection] [📤 Share]│ Location        │  │
│                                          │ ~/Videos/...    │  │
│  Description:                            │ [Show in Folder]│  │
│  This video covers professional lighting...└─────────────────┘  │
│                                                                  │
│  [💬 Comments] [📋 Chapters] [🔗 Related]                        │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘
```

### 10.4 Folder/Collection Editor

```
┌─────────────────────────────────────────────────────────────────┐
│  Edit Collection: "Photography Tutorials"              [Save] │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  Name: [Photography Tutorials                    ]              │
│                                                                  │
│  Description:                                                   │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Best tutorials for learning photography techniques       │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                  │
│  Cover Image: [Choose Image...] [🖼️ Current]                   │
│                                                                  │
│  Sort Order: [Date Added ▼]                                   │
│                                                                  │
│  Smart Collection: [ ] Enable rules-based auto-population       │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Rules (if enabled):                                      │   │
│  │ [Tags] [contains] [photography] [AND] [+ Add Rule]       │   │
│  │ [Duration] [greater than] [10] [minutes]                 │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                  │
│  Items (24):                                                    │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ ⠿ │ Title                          │ Duration │ Added   │   │
│  │───│────────────────────────────────│──────────│─────────│   │
│  │ ⠿ │ Understanding Exposure         │ 15:23    │ 2d ago  │   │
│  │ ⠿ │ Composition Rules              │ 12:45    │ 3d ago  │   │
│  │ ...                                                    │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘
```

---

## 11. Navigation Structure

### 11.1 Route Map

| Route | Component | Description |
|-------|-----------|-------------|
| `/` | LibraryView | Default library view |
| `/search` | SearchView | Search results |
| `/media/:id` | MediaDetail | Single media view |
| `/media/:id/play` | PlayerView | Full-screen player |
| `/folders` | FolderView | Folder management |
| `/folders/:id` | FolderDetail | Folder contents |
| `/collections` | CollectionsView | Collection list |
| `/collections/:id` | CollectionDetail | Collection contents |
| `/collections/:id/edit` | CollectionEditor | Edit collection |
| `/tags` | TagsView | Tag management |
| `/tags/:id` | TagDetail | Tag contents |
| `/history` | HistoryView | Watch history |
| `/favorites` | FavoritesView | Quick access favorites |
| `/settings` | SettingsView | Application settings |

### 11.2 Keyboard Shortcuts

| Shortcut | Action | Context |
|----------|--------|---------|
| `Space` | Play/Pause | Media selected |
| `←/→` | Seek ±5s | Player active |
| `↑/↓` | Volume ±10% | Player active |
| `F` | Fullscreen | Player active |
| `M` | Mute | Player active |
| `Ctrl+F` | Focus search | Global |
| `Ctrl+N` | New folder | Library |
| `Ctrl+T` | Add tag | Media selected |
| `Ctrl+D` | Add to favorites | Media selected |
| `Delete` | Delete (confirm) | Media selected |
| `Ctrl+1-9` | Switch view | Global |
| `Esc` | Back/Close | Global |

---

## 12. Screen Definitions

### 12.1 Screen Specifications

| Screen | Min Width | Layout | Key Features |
|--------|-----------|--------|--------------|
| Library | 800px | Sidebar + Grid | Browse, filter, sort |
| Search | 800px | Full width | Results, facets, suggestions |
| Media Detail | 600px | Two column | Player, metadata, actions |
| Player | 400px | Fullscreen | Controls, chapters, related |
| Folders | 600px | Tree + List | CRUD, drag-drop |
| Collections | 800px | Grid + Editor | Curate, organize |
| Tags | 600px | Cloud + List | Manage, merge |
| Settings | 500px | Form sections | Configure, customize |

### 12.2 Responsive Breakpoints

```typescript
const breakpoints = {
  mobile: 0,      // Stack everything, bottom nav
  tablet: 768,    // Collapsible sidebar
  desktop: 1024,  // Full sidebar + grid
  wide: 1440,     // Larger thumbnails, more columns
  ultrawide: 1920 // Maximize content density
};
```

### 12.3 Mobile Adaptations

- Bottom navigation bar instead of sidebar
- Swipe gestures for navigation
- Pull-to-refresh
- Touch-optimized player controls
- Simplified search (no advanced filters)
- Floating action button for primary actions

---

*Document Version: 1.0*
*Last Updated: 2026-05-27*
*Owner: Engineering Team*