Untitled

using System.Data.SqlClient;

string connectionString = "connectionstring here";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // get all invalid content versions, [current] might as well be called "edited" and "edited" and "published" should not be on the same version
    

    List<ContentVersionDTO> affectedVersions = new List<ContentVersionDTO>();

    string selectSql = @"
    select cv.* from umbracoContentVersion cv, umbracoDocumentVersion dv, umbracoNode n
    where cv.id = dv.id and cv.[current]= 1 and published = 1
    and n.id = nodeid and n.trashed = 0
    ";
    using (SqlCommand selectCommand = new SqlCommand(selectSql, connection))
    {
        using (SqlDataReader reader = selectCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                affectedVersions.Add(new ContentVersionDTO()
                {
                    VersionId = (int)reader["id"],
                    NodeId = (int)reader["nodeId"],
                    UserId = (int)reader["userId"],
                    Text = (string)reader["text"],
                    PreventCleanup = (bool)reader["preventCleanup"]

                });
            }
        }
    }

    foreach (ContentVersionDTO version in affectedVersions)
    {
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                FIXIT.FixContent(version, connection, transaction);
                transaction.Commit();
                Console.WriteLine("Fixed " + version.NodeId + " " + affectedVersions.IndexOf(version) + " out of " + affectedVersions.Count);
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine("Error processing " + version.NodeId + ": " + ex.Message);
            }
        }
    }


    // cleanup document versions
    string cleanupSQL = @"insert into umbracoDocumentVersion
select cv.id, (select max(templateId) from umbracoDocumentVersion where id in (select id from umbracoContentVersion a where a.nodeId = cv.nodeId)) as templateId, 0 as published
from umbracoDocument d, umbracoContentVersion cv
left join umbracoDocumentVersion dv on dv.id = cv.id
where [current]= 1 and d.nodeId = cv.nodeId
and dv.id is null";
    using (SqlCommand updateCommand = new SqlCommand(cleanupSQL, connection))
    {
        int rowsAffected = updateCommand.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }
}

public class FIXIT
{
    public static void FixContent(ContentVersionDTO cDto, SqlConnection conn, SqlTransaction transaction)
    {
        var content = new List<PropertyDataDTO>();

        string sql = "select * from umbracopropertydata where versionId = " + cDto.VersionId;

        using (SqlCommand cmd = new SqlCommand(sql, conn, transaction))
        {
            // Add the versionId as a parameter
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var propertyDataDTO = new PropertyDataDTO
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("id")),
                        VersionId = reader.GetInt32(reader.GetOrdinal("versionId")),
                        PropertyTypeId = reader.GetInt32(reader.GetOrdinal("propertyTypeId")),
                        LanguageId = reader.IsDBNull(reader.GetOrdinal("languageId")) ? null : reader.GetInt32(reader.GetOrdinal("languageId")),
                        Segment = reader.IsDBNull(reader.GetOrdinal("segment")) ? null : reader.GetString(reader.GetOrdinal("segment")),
                        IntValue = reader.IsDBNull(reader.GetOrdinal("intValue")) ? null : reader.GetInt32(reader.GetOrdinal("intValue")),
                        DecimalValue = reader.IsDBNull(reader.GetOrdinal("decimalValue")) ? null : (decimal?)reader.GetDecimal(reader.GetOrdinal("decimalValue")),
                        DateValue = reader.IsDBNull(reader.GetOrdinal("dateValue")) ? null : (DateTime?)reader.GetDateTime(reader.GetOrdinal("dateValue")),
                        VarcharValue = reader.IsDBNull(reader.GetOrdinal("varcharValue")) ? null : reader.GetString(reader.GetOrdinal("varcharValue")),
                        TextValue = reader.IsDBNull(reader.GetOrdinal("textValue")) ? null : reader.GetString(reader.GetOrdinal("textValue")),
                    };

                    content.Add(propertyDataDTO);
                }
            }
        }

        // make new version
        sql = "select max(id) from umbracoContentVersion";
       

        string update = "update umbracoContentVersion set [current] = 0 where id = " + cDto.VersionId;
        using (SqlCommand cmd = new SqlCommand(update, conn, transaction))
        {
            cmd.ExecuteNonQuery();
        }
        
        

        string insertSql = "INSERT INTO umbracoContentVersion (NodeId, UserId, Text, PreventCleanup, [Current]) " +
                   "VALUES (@NodeId, @UserId, @Text, @PreventCleanup, 1)";

        using (SqlCommand cmd = new SqlCommand(insertSql, conn, transaction))
        {
            //cmd.Parameters.AddWithValue("@VersionId", cDto.VersionId);
            cmd.Parameters.AddWithValue("@NodeId", cDto.NodeId);
            cmd.Parameters.AddWithValue("@UserId", cDto.UserId);
            cmd.Parameters.AddWithValue("@Text", cDto.Text ?? (object)DBNull.Value); // Handling potential null value
            cmd.Parameters.AddWithValue("@PreventCleanup", cDto.PreventCleanup);

            cmd.ExecuteNonQuery();
        }
        int nextVersionId = 0;
        using (SqlCommand cmd = new SqlCommand(sql, conn, transaction))
        {
            object result = cmd.ExecuteScalar();
            if (result != null && result != DBNull.Value)
            {
                nextVersionId = Convert.ToInt32(result);
            }
        }
        insertSql = "INSERT INTO umbracoPropertyData (VersionId, PropertyTypeId, LanguageId, Segment, IntValue, DecimalValue, DateValue, VarcharValue, TextValue) " +
                   "VALUES (@VersionId, @PropertyTypeId, @LanguageId, @Segment, @IntValue, @DecimalValue, @DateValue, @VarcharValue, @TextValue)";

        foreach (var line in content)
        {
            line.VersionId = nextVersionId;

            using (SqlCommand cmd = new SqlCommand(insertSql, conn, transaction))
            {
                cmd.Parameters.AddWithValue("@VersionId", line.VersionId);
                cmd.Parameters.AddWithValue("@PropertyTypeId", line.PropertyTypeId);
                cmd.Parameters.AddWithValue("@LanguageId", line.LanguageId.HasValue ? (object)line.LanguageId : DBNull.Value);
                cmd.Parameters.AddWithValue("@Segment", string.IsNullOrEmpty(line.Segment) ? (object)DBNull.Value : line.Segment);
                cmd.Parameters.AddWithValue("@IntValue", line.IntValue.HasValue ? (object)line.IntValue : DBNull.Value);
                cmd.Parameters.AddWithValue("@DecimalValue", line.DecimalValue.HasValue ? (object)line.DecimalValue : DBNull.Value);
                cmd.Parameters.AddWithValue("@DateValue", line.DateValue.HasValue ? (object)line.DateValue : DBNull.Value);
                cmd.Parameters.AddWithValue("@VarcharValue", string.IsNullOrEmpty(line.VarcharValue) ? (object)DBNull.Value : line.VarcharValue);
                cmd.Parameters.AddWithValue("@TextValue", string.IsNullOrEmpty(line.TextValue) ? (object)DBNull.Value : line.TextValue);

                cmd.ExecuteNonQuery();
            }
        }

        

    }
}

public class ContentVersionDTO
{
    public int VersionId { get; set; }
    public int NodeId { get; set; }
    public int UserId { get; set; }
    public string Text { get; set; }
    public bool PreventCleanup { get; set; }
}


public class PropertyDataDTO
{
    public int Id { get; set; }
    public int VersionId { get; set; }
    public int PropertyTypeId { get; set; }
    public int? LanguageId { get; set; }
    public string Segment { get; set; }
    public int? IntValue { get; set; }
    public decimal? DecimalValue { get; set; }
    public DateTime? DateValue { get; set; }
    public string VarcharValue { get; set; }
    public string TextValue { get; set; }
}