Clean up DB

[spathon]

UNUSED TABLES (13 tables - completely not referenced)

Used by other parts

1. bounces - Email bounce tracking table (7 columns)
2. email_log - Email logging table (3 columns)
3. stats_sent_emails - Email statistics tracking (5 columns)

Remove

1. business - Business listings table (11 columns)
2. comments_content - Multi-language comment content (4 columns)
3. event - Events table (11 columns)
4. groups_admins - Group administrators junction table (2 columns)
5. groups_content - Multi-language group content (7 columns)
6. location - Location/address data (12 columns)
7. media_relations - Media relationships (3 columns) - Replaced by post_id in media table
8. posts_content - Multi-language post content (6 columns)
9. translations - Translation cache table (4 columns)
10. wiki - Wiki pages table (9 columns)

Total: 94 unused columns across 13 unused tables

---

UNUSED COLUMNS IN ACTIVE TABLES (44 columns)

comments (5 unused columns)

• lang - Language code
• old_nid - Migration field (old node ID)
• old_pid - Migration field (old post ID)
• old_cid - Migration field (old comment ID)
• old_uid - Migration field (old user ID)

groups (1 unused column)

• old_nid - Migration field (old node ID)

groups_users (3 unused columns)

• old_uid - Migration field (old user ID)
• old_nid - Migration field (old node ID)
• old_type - Migration field (old subscription type)

media (4 unused columns)

• filepath - File path (only filename is used)
• deleted_at - Soft delete timestamp (not implemented)
• old_fid - Migration field (old file ID)
• old_uid - Migration field (old user ID)

messages (4 unused columns)

• deleted_at - Soft delete timestamp (not implemented)
• old_uid - Migration field (old user ID)
• old_mid - Migration field (old message ID)
• old_tid - Migration field (old thread ID)

messages_subscribers (2 unused columns)

• old_tid - Migration field (old thread ID)
• old_uid - Migration field (old user ID)

messages_thread (1 unused column)

• old_tid - Migration field (old thread ID)

posts (4 unused columns)

• image_id - Featured image reference (not used)
• old_uid - Migration field (old user ID)
• old_nid - Migration field (old node ID)
• old_gid - Migration field (old group ID)

users (4 unused columns)

• email_domain - Extracted email domain
• old_uid - Migration field (old user ID)
• avatarpath - Avatar file path (not implemented)
• adminComments - Admin notes field

Tables with all columns in use:

• ✅ activations - All 7 columns used
• ✅ blog - All 8 columns used
• ✅ bookmarks - All 4 columns used

---

SUMMARY

• Total unused tables: 13 (94 columns)
• Total unused columns in active tables: 44 columns
• Grand total unused: 138 columns across all tables

Key Findings:

1. Migration Fields: 24 columns starting with old_ are legacy migration fields that can be safely removed
2. Soft Deletes: deleted_at columns in media and messages are defined but soft delete is not implemented
3. Multi-language Support: comments_content, posts_content, and groups_content tables suggest a multi-language system that was never used (current implementation stores content directly in main tables)
4. Deprecated Features: Entire feature sets appear unused (events, wiki, business listings, location data, email bounce tracking)

Safe to Remove:

All old_* migration columns can be safely dropped as they were only used for data migration from a previous system.

[spathon]

Make comments of deleted user as null to keep parentId

-- Re-add foreign key with SET NULL on delete
ALTER TABLE `comments`
ADD CONSTRAINT `comments_user_id_users_id_fk`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE;

But hide on serverside the comment?