[videodb] Remove quality-like predefined version types

Encourage use of media flags displayed in skin instead, the user can still add anything he wants.
Some qualities do not exist as media info and will need manual creation by user (remux for example)

Structure of the db is expected to change in v22 so message records are deleted to avoid
hardcoding message id in more places.
The freed messages should not be reused.

2 files changed, 41 insertions, 82 deletions

diff --git a/addons/resource.language.en_gb/resources/strings.po b/addons/resource.language.en_gb/resources/strings.po
index 3d93bb3af5..bf05a1eddf 100644
--- a/addons/resource.language.en_gb/resources/strings.po
+++ b/addons/resource.language.en_gb/resources/strings.po
@@ -24196,11 +24196,7 @@ msgctxt "#40404"
 msgid "Remastered Version"
 msgstr ""
 
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40405"
-msgid "4K"
-msgstr ""
+#empty string id 40405 do not reuse
 
 #. Name of a video version, like "Director's Cut"
 #: xbmc/video/VideoDatabase.cpp
@@ -24274,83 +24270,7 @@ msgctxt "#40417"
 msgid "Black and White Edition"
 msgstr ""
 
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40418"
-msgid "BluRay"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40419"
-msgid "WEB-DL"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40420"
-msgid "3D"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40421"
-msgid "8K"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40422"
-msgid "IMAX"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40423"
-msgid "UHD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40424"
-msgid "FHD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40425"
-msgid "HD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40426"
-msgid "SD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40427"
-msgid "DVD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40428"
-msgid "VHS"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40429"
-msgid "VCD"
-msgstr ""
-
-#. Name of a video version, like "Director's Cut"
-#: xbmc/video/VideoDatabase.cpp
-msgctxt "#40430"
-msgid "REMUX"
-msgstr ""
+#empty strings from id 40418 to 40430 do not reuse
 
 #. Name of a video version, like "Director's Cut"
 #: xbmc/video/VideoDatabase.cpp
diff --git a/xbmc/video/VideoDatabase.cpp b/xbmc/video/VideoDatabase.cpp
index c35af63a16..50bcfb0319 100644
--- a/xbmc/video/VideoDatabase.cpp
+++ b/xbmc/video/VideoDatabase.cpp
@@ -6336,6 +6336,41 @@ void CVideoDatabase::UpdateTables(int iVersion)
                             "AND itemType = %i",
                             VideoAssetTypeOwner::USER, VideoAssetType::VERSION));
   }
+
+  if (iVersion < 131)
+  {
+    // Remove quality-like predefined version types
+
+    // Retrieve current utilization per type
+    m_pDS->query("SELECT vvt.id, vvt.name, count(vv.idType) "
+                 "FROM videoversiontype vvt "
+                 "  LEFT JOIN videoversion vv ON vvt.id = vv.idType "
+                 "WHERE vvt.id = 40405 OR vvt.id BETWEEN 40418 AND 40430 "
+                 "GROUP BY vvt.id");
+
+    while (!m_pDS->eof())
+    {
+      const int typeId{m_pDS->fv(0).get_asInt()};
+      const std::string typeName{m_pDS->fv(1).get_asString()};
+      const int versionsCount{m_pDS->fv(2).get_asInt()};
+
+      if (versionsCount > 0)
+      {
+        // type used by some versions, recreate as user type and link the versions to the new id
+        m_pDS2->exec(PrepareSQL(
+            "INSERT INTO videoversiontype (id, name, owner, itemType) VALUES(NULL, '%s', %i, %i)",
+            typeName.c_str(), VideoAssetTypeOwner::USER, VideoAssetType::VERSION));
+
+        const int newId{static_cast<int>(m_pDS2->lastinsertid())};
+
+        m_pDS2->exec(
+            PrepareSQL("UPDATE videoversion SET idType = %i WHERE idType = %i", newId, typeId));
+      }
+      m_pDS2->exec(PrepareSQL("DELETE FROM videoversiontype WHERE id = %i", typeId));
+      m_pDS->next();
+    }
+    m_pDS->close();
+  }
 }
 
 int CVideoDatabase::GetSchemaVersion() const
@@ -11899,6 +11934,10 @@ void CVideoDatabase::InitializeVideoVersionTypeTable(int schemaVersion)
 
     for (int id = VIDEO_VERSION_ID_BEGIN; id <= VIDEO_VERSION_ID_END; ++id)
     {
+      // Exclude removed pre-populated "quality" values
+      if (id == 40405 || (id >= 40418 && id <= 40430))
+        continue;
+
       const std::string& type{g_localizeStrings.Get(id)};
       if (schemaVersion < 127)
       {