Horizon: Rewrite database structure and migrations

[Studio384]

God, the database structure is a mess. Our relations are broken, etc.

Who's idea was it to use the codename of milestones as IDs?

Ow...

This issue isn't only about cleaning up our database structure and migrations. We'll also provide factories for all relevant models and seeders for anyone who wants to set up a local copy of ChangeWindows.

The new rules

For this project, a few rules have been set up. Some of these are common sense, some of these are Laravel guidelines and some of these are unique to ChangeWindows itself. These are just the rules that the current database wouldn't pass.

• Every database table that isn't a pivot table has a unique numerical auto-incrimenting id, no exceptions. A pivot table uses a combination off the 2 or more foreign keys that make each line unique.
• Every table has Laravel's auto-generated created_at and updated_at fields.
• Any relation-field has the _id-suffix.
• Pivot tables are named in a parent_child-pattern, not alphabetically as is enforced by Laravel. E.g. a platform has channels, thus its pivot table is named platform_channels and not channel_platforms as Laravel defaults towards.
• All relations are defined with their constraints.
• No fields should be added behind Laravel's created_at and updated_at fields, with the exception of Laravel's deleted_at field in case support for soft deleting is required.
• Any table that has url-facing data has a slug field whom's value is set as the route key name to generate readable urls. E.g. platforms should have a slug, releases however shouldn't.

The only exception to these rules is the Buildfeed-table.

What this issue fixes

Right from the start, this means that a lot of things need to be fixed. The items that start with a (6) are new since version 6. Here is a list of all the fixes that Horizon applies:

• Renames the ability_roles table to role_abilities.
• Adds proper constraint from role_abilities to roles.
• Adds proper constraint from role_abilities to abilities.
• Renames abilities.label to abilities.description to properly reflect the function of the field.
• Renames roles.label to roles.description to properly reflect the function of the field.
• Removes the roles.rank-field which is a leftover of ChangeWindows 5.1.
• Reorganizes the entire users-table field order.
• Adds a users.slug field to store the slug for a user account based on name.
• Adds proper constraint from roles to users.
• (6) Renames the channel_platforms table to platform_channels.
• Adds milestones.canonical_version which contains an XXYY-formatted version number for any milestone.
• Renames milestones.osname to milestones.product_name to improve clearity of what the fields contains.
• Renames milestones.preview to milestones.start_preview to better reflect the order of this field.
• Renames milestones.public to milestones.start_public to better reflect the order of this field.
• Renames milestones.mainEol to milestones.start_extended to better reflect the order of this field.
• Renames milestones.mainXol to milestones.start_lts to better reflect the order of this field.
• Renames milestones.ltsEol to milestones.end_lts to better reflect the order of this field.
• Adds a milestones.slug field to store the slug for a milestone based on codename.
• Renames releases-table to flights.
• Renames releases.milestone to flights.milestone_id and changes type to bigint(20) to follow foreign key convention.
• Renames releases.ring to flights.channel_id to follow foreign key convention.
• Renames releases.platform to flights.platform_id to follow foreign key convention.
• Adds flights.user_id to track user.
• Removes the role_user-table.
• Removes the patreons-table.
• Removes the changelogs-table.
• Removes the vnext-table.