# SQL Schema Conversion Guide - PascalCase to snake_case

## Authoritative Table Mappings (from PostgresDatabaseProvider.OnModelCreating)

### Activity Log Schema
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"ActivityLogs"` | `activity_logs` | `ActivityLog` |

### Authentication Schema
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"ApiKeys"` | `api_keys` | `ApiKey` |
| `"Devices"` | `devices` | `Device` |
| `"DeviceOptions"` | `device_options` | `DeviceOptions` |

### Display Preferences Schema
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"DisplayPreferences"` | `display_preferences` | `DisplayPreferences` |
| `"ItemDisplayPreferences"` | `item_display_preferences` | `ItemDisplayPreferences` |
| `"CustomItemDisplayPreferences"` | `custom_item_display_preferences` | `CustomItemDisplayPreferences` |
| `"HomeSections"` | `home_sections` | `HomeSection` |

### Users Schema
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"Users"` | `users` | `User` |
| `"Permissions"` | `permissions` | `Permission` |
| `"Preferences"` | `preferences` | `Preference` |
| `"AccessSchedules"` | `access_schedules` | `AccessSchedule` |

### Library Schema
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"BaseItems"` | `base_items` | `BaseItemEntity` |
| `"Chapters"` | `chapters` | `Chapter` |
| `"MediaStreamInfos"` | `media_stream_infos` | `MediaStreamInfo` |
| `"AttachmentStreamInfos"` | `attachment_stream_infos` | `AttachmentStreamInfo` |
| `"ImageInfos"` | `image_infos` | `ImageInfo` |
| `"BaseItemImageInfos"` | `base_item_image_infos` | `BaseItemImageInfo` |
| `"BaseItemProviders"` | `base_item_providers` | `BaseItemProvider` |
| `"BaseItemMetadataFields"` | `base_item_metadata_fields` | `BaseItemMetadataField` |
| `"BaseItemTrailerTypes"` | `base_item_trailer_types` | `BaseItemTrailerType` |
| `"ItemValues"` | `item_values` | `ItemValue` |
| `"ItemValuesMaps"` | `item_values_map` | `ItemValueMap` |
| `"Peoples"` | `peoples` | `People` |
| `"PeopleBaseItemMaps"` | `people_base_item_map` | `PeopleBaseItemMap` |
| `"UserData"` | `user_data` | `UserData` |
| `"AncestorIds"` | `ancestor_ids` | `AncestorId` |
| `"TrickplayInfos"` | `trickplay_infos` | `TrickplayInfo` |
| `"MediaSegments"` | `media_segments` | `MediaSegment` |
| `"KeyframeData"` | `keyframe_data` | `KeyframeData` |
| `"LibraryOptions"` | `library_options` | `LibraryOptionsEntity` |

### Special Tables
| Current (SQL) | Target (C#/PostgreSQL) | Notes |
|--------------|------------------------|-------|
| `"__EFMigrationsHistory"` | `__ef_migrations_history` | System table, special handling |

### Views (Library Schema)
| Current (SQL) | Target (C#/PostgreSQL) | Entity Class |
|--------------|------------------------|--------------|
| `"media_stream_infos_v"` | `media_stream_infos_v` | `MediaStreamInfoView` (already lowercase) |
| `"video_items_v"` | `video_items_v` | `VideoItemView` (already lowercase) |
| `"user_playback_history_v"` | `user_playback_history_v` | `UserPlaybackHistoryView` (already lowercase) |
| `"item_providers_v"` | `item_providers_v` | `ItemProviderView` (already lowercase) |
| `"item_people_v"` | `item_people_v` | `ItemPersonView` (already lowercase) |
| `"library_summary_v"` | `library_summary_v` | `LibrarySummaryView` (already lowercase) |

---

## Column Naming Rules

### SnakeCaseNamingConvention Conversion Rules

```csharp
// From SnakeCaseNamingConvention.cs - regex patterns applied:
// Rule 1: ([A-Z]+)([A-Z][a-z])  → "$1_$2"   // Consecutive capitals: HTTPServer → HTTP_Server
// Rule 2: ([a-z\d])([A-Z])      → "$1_$2"   // Lowercase/digit to capital: dateCreated → date_Created
// Rule 3: .ToLowerInvariant()                // Lowercase: date_Created → date_created
```

### Common Column Conversions

Single-Word Columns:
- `"Id"` → `id`
- `"Name"` → `name`
- `"Type"` → `type`
- `"UserId"` → `user_id`
- `"ItemId"` → `item_id`

Multi-Word Columns:
- `"DateCreated"` → `date_created`
- `"DateModified"` → `date_modified`
- `"DateLastActivity"` → `date_last_activity`
- `"AccessToken"` → `access_token`
- `"IsMovie"` → `is_movie`
- `"IsSeries"` → `is_series`
- `"ShortOverview"` → `short_overview`
- `"LogSeverity"` → `log_severity`
- `"RowVersion"` → `row_version`
- `"DisplayOrder"` → `display_order`
- `"BaseItemId"` → `base_item_id`
- `"DeviceId"` → `device_id`
- `"ProviderId"` → `provider_id`
- `"SortName"` → `sort_name`
- `"ProviderIds"` → `provider_ids`
- `"DvVersionMajor"` → `dv_version_major`
- `"DvVersionMinor"` → `dv_version_minor`
- `"DvProfile"` → `dv_profile`
- `"DvLevel"` → `dv_level`
- `"RpuVersion"` → `rpu_version`
- `"CodecTag"` → `codec_tag`
- `"IsHDR"` → `is_hdr`
- `"IsHDR10"` → `is_hdr10`
- `"SeriesPresentationUniqueKey"` → `series_presentation_unique_key`

---

## Conversion Strategy in create_database_schema.sql

### Phase 1: Table Definition Lines
Find and replace quoted PascalCase table names immediately after `CREATE TABLE`:
- `CREATE TABLE activitylog."ActivityLogs"` → `CREATE TABLE activitylog.activity_logs`
- `CREATE TABLE authentication."ApiKeys"` → `CREATE TABLE authentication.api_keys`
- etc. (all 19 tables in schema sections)

### Phase 2: Column Definitions
Within each `CREATE TABLE` block, replace all quoted column names:
- `"Id"` → `id`
- `"Name"` → `name`
- `"DateCreated"` → `date_created`
- etc. (200+ column replacements across all tables)

### Phase 3: Sequence Names
Update sequence definitions to match renamed tables:
- `SEQUENCE NAME activitylog."ActivityLogs_Id_seq"` → `SEQUENCE NAME activitylog.activity_logs_id_seq`
- etc.

### Phase 4: Constraint References
Update all foreign key and constraint definitions:
- `CONSTRAINT "fk_base_items_users_user_id" FOREIGN KEY ("BaseItemId") REFERENCES users."Users" ("Id")` 
  → `CONSTRAINT fk_base_items_users_user_id FOREIGN KEY (base_item_id) REFERENCES users.users (id)`

### Phase 5: Index References
Update index definitions:
- `CREATE INDEX "idx_activity_logs_user_id" ON activitylog."ActivityLogs" ("UserId")`
  → `CREATE INDEX idx_activity_logs_user_id ON activitylog.activity_logs (user_id)`

### Phase 6: Special Table (__EFMigrationsHistory)
Update system table for consistency:
- `"__EFMigrationsHistory"` → `__ef_migrations_history`
- All its columns follow the same snake_case rule

---

## File Location
- **Source**: `scripts/sql/schema_init/create_database_schema.sql` (1879 lines)
- **Output**: Same file (in-place conversion)

---

## Validation Checklist
- [ ] All 19 application tables converted from PascalCase to snake_case
- [ ] All 200+ columns converted to lowercase/snake_case
- [ ] All sequence names updated
- [ ] All foreign key references updated
- [ ] All indexes updated
- [ ] Special table `__ef_migrations_history` created with lowercase columns
- [ ] Views remain unchanged (already lowercase)
- [ ] SQL syntax is valid
- [ ] No orphaned quotes remain in identifier positions
- [ ] Schema still respects owner = jellyfin

---

Generated: 2025-05-01  
Reference Documents:
- `PostgresDatabaseProvider.cs` lines 763-875
- `SnakeCaseNamingConvention.cs` lines 16-40
- `create_database_schema.sql` full schema dump