Pull Request for issue #35102 (comment) and replacement of PR #35102 .

Summary of Changes

This pull request (PR) makes the fix for the wrong asset name which PR #35102 does, but with following extensions:

• Change the name of the asset only if no new asset with the right name has been created already when the workflow stage has been saved in backend for some reason in past.
  That's the first SQL statement in the update SQL scripts added by this PR.
  It requires the same tricks as used in PR #34763 to avoid an SQL error on particular MySQL or MariaDB versions and so might look a bit complicated at first view.

• For fixing the missing asset ID for the basic stage in MySQL, either the initial asset for which the name was fixed before is used or a newly created asset when the workflow stage has been saved in backend for some reason in past.
  That's the last SQL statement in the MySQL update SQL script added by this PR.
  For PostgreSQL that statement is not needed because there was no missing asset ID.

In addition, this PR fixes the finding in this comment #35102 (comment) about wrong titles for some of the assets for workflow transitions.

Testing Instructions

Testers please report back which kind and version of database you have used. If you have several database servers available, test with all you can.

Test 1: Update a new, unmodified 4.0 installation to the patched package of this PR

1. Have a current 4.0-dev or latest 4.0 nightly or 4.0 RC 6 or previous 4.0 version installation on which you haven't edited anything regarding workflows in backend yet and which has the issues described in section "Actual result BEFORE applying this Pull Request" below.
   If you don't have that or see different results as described, make a new installation of current 4.0-dev or latest 4.0 nightly or 4.0 RC 6 installation using an empty database.

2. Update the installation to the patched package created by drone for this PR.
   You can find the link to the update package and the custom update URL here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/35113/downloads/46588/ .
   Alternatively to that update you can execute all SQL statements from the update SQL script added by this PR for your type of database e.g. in phpMyAdmin (or phpPgAdmin for PostgreSQL), having replaced the #__ by the table prefix of your installation.

3. Check in database that everything is as described in the first part of section "Expected result AFTER applying this Pull Request" below.

Test 2: Update a modified 4.0 installation to the patched package of this PR

1. Start again with the same starting conditions as in step 1 of the previous "Test 1".

2. In backend, enable workflows in the integration tab of com_content options if it is not already enabled.

3. Go to the stages of the Basic Workflow and open the only one Basic Stage for editing.

4. Save the stage without any changes.

5. Check in database if there has been created a new record in the #__assets table.
   Result: A new record has been created, see green mark in the following image.
   

6. Check if the old record with the wrong name 'com_content.state.1' in the #__assets table still exists.
   Result: The old, wrong record hasn't changed.
   

7. Check the asset ID of the first (and on a clean 4.0 install only) record in the #__workflow_stages table.
   Result: The asset ID is the one of the new asset checked in step 5.
   

8. Update the installation to the patched package created by drone for this PR.
   You can find the link to the update package and the custom update URL here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/35113/downloads/46588/ .
   Alternatively to that update you can execute all SQL statements from the update SQL script added by this PR for your type of database e.g. in phpMyAdmin (or phpPgAdmin for PostgreSQL), having replaced the #__ by the table prefix of your installation.

9. Check in database that everything is as described in the second part of section "Expected result AFTER applying this Pull Request" below.

Test 3: New installation with the patch of this PR applied

1. Apply the patch of this PR to a current 4.0-dev branch or an installation of latest 4.0 nightly or 4.0 RC 6 or a previous 4.0 version where the installation folder is still present. If that is not the case because you have removed the installation folder after the previous installation, you can either restore the installation folder by unpacking it from the installation package suitable for the tested version and after that apply the patch of this PR, or you make a new installation using the patched full package created by drone for this PR.

2. Remove configuration.php if present, and delete all database tables, and make a new installation into an empty database.

3. Check in database that everything is as described in the first part of section "Expected result AFTER applying this Pull Request" below.

Actual result BEFORE applying this Pull Request

After a new installation, the asset used for the basic stage of com_workflow has a wrong name using "state" instead of "stage", see the red mark in the following image of the #__assets table.

You can see that the name is wrong if you edit that stage in backend and just save without changes. After that you will find a new record with the right name in the assets table, see green mark in the following image, and the wrong record will not have changed and will not be used anymore.

The titles of the first four assets used for workflow transitions coming with the core are wrong compared to the transitions having these asset ID's. See the orange marks in the image above of the #__assets table and the green marks in the image of the #__workflow_transitions table below, which are right:

As you can see, "Unpublish" and "Publish" are mixed up in their assets' titles, and the same for "Trash" and "Archive".

Finally only with MySQL or MariaDB databases but not with PostgreSQL, the asset ID of the basic stage of com_workflow is zero in database, see red mark in the following image of the #__workflow_stages table:

Expected result AFTER applying this Pull Request

1. After a new installation or an update where nothing related to workflow was modified before

The #__assets table shows the right name for the asset of the Basic Stage and the right titles for the assets for the first 4 transitions.

The asset ID of the first (and on a clean 4.0 install only) record for the Basic Stage in the #__workflow_stages table is the right one.

2. After an update where the Basic Stage has been modified before so a new asset had been created for that stage

The #__assets table shows the right titles for the assets for the first 4 transitions, see green marks in the following image.

The record with the wrong name hasn't changed, see red mark in the image.

The asset ID of the first (and on a clean 4.0 install only) record in the #__workflow_stages is still the same one as checked in step 5 of "Test 2".

Documentation Changes Required

None.