/jira-project/jira-components/jira-core/src/main/java/com/atlassian/jira/upgrade/tasks/DowngradeTask_Build70024.java

package com.atlassian.jira.upgrade.tasks;

import com.atlassian.jira.database.DbConnection;
import com.atlassian.jira.database.SqlCallback;
import com.atlassian.jira.model.querydsl.QAvatar;
import com.atlassian.jira.model.querydsl.QOSPropertyEntry;
import com.atlassian.jira.model.querydsl.QOSPropertyNumber;
import com.atlassian.jira.model.querydsl.QOSPropertyString;
import com.atlassian.jira.model.querydsl.QProject;
import com.atlassian.jira.upgrade.AbstractDowngradeTask;
import com.atlassian.jira.upgrade.DowngradeException;
import com.atlassian.jira.upgrade.ReindexRequirement;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.querydsl.sql.SQLExpressions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import java.util.List;
import java.util.Map;

/**
 * Downgrades avatar icons from SVG to PNG. This includes only system avatar icons for users, projects and issue types.
 *
 * @since 6.4.8
 */
public final class DowngradeTask_Build70024 extends AbstractDowngradeTask {
    private static final Logger log = LoggerFactory.getLogger(DowngradeTask_Build70024.class);


    private static final int TRUE = 1;
    private static final int NUMBER_TYPE = 3;

    private static final String ISSUE_TYPE_AVATAR = "issuetype";
    private static final String PROJECT_AVATAR = "project";
    private static final String USER_AVATAR = "user";

    private static final String DEFAULT_PROJECT_AVATAR_KEY = "jira.avatar.default.id";
    private static final String DEFAULT_PROJECT_AVATAR_NAME = "rocket.png";

    private static final String USER_ENTITY_NAME = "ApplicationUser";
    private static final String USER_AVATAR_PROPERTY_KEY = "user.avatar.id";

    private static final String PNG_CONTENT_TYPE = "image/png";
    private static final String SVG_CONTENT_TYPE = "image/svg+xml";

    private static final Map<String, String> ISSUE_TYPE_ICONS_TO_RENAME = ImmutableMap.<String, String>builder()
            .put("task.svg", "task.png")
            .put("subtask.svg", "subtask.png")
            .put("story.svg", "story.png")
            .put("improvement.svg", "improvement.png")
            .put("sales.svg", "sales.png")
            .put("design_task.svg", "health.png")
            .put("remove_feature.svg", "remove_feature.png")
            .put("requirement.svg", "requirement.png")
            .put("newfeature.svg", "newfeature.png")
            .put("exclamation.svg", "exclamation.png")
            .put("documentation.svg", "documentation.png")
            .put("defect.svg", "defect.png")
            .put("epic.svg", "epic.png")
            .put("genericissue.svg", "genericissue.png")
            .put("bug.svg", "bug.png")
            .put("question.svg", "undefined.png")
            .put("development_task.svg", "development_task.png")
            .put("feedback.svg", "feedback.png")
            .put("request_access.svg", "request_access.png")
            .build();

    private static final Map<String, String> PROJECT_ICONS_TO_RENAME = ImmutableMap.<String, String>builder()
            .put("cloud.svg", "cloud.png")
            .put("spanner.svg", "config.png")
            .put("cd.svg", "disc.png")
            .put("bird.svg", "eamesbird.png")
            .put("money.svg", "finance.png")
            .put("mouse-hand.svg", "hand.png")
            .put("koala.svg", "kangaroo.png")
            .put("yeti.svg", "new_monster.png")
            .put("power.svg", "power.png")
            .put("nature.svg", "rainbow.png")
            .put("refresh.svg", "refresh.png")
            .put("rocket.svg", "rocket.png")
            .put("phone.svg", "servicedesk.png")
            .put("settings.svg", "settings.png")
            .put("storm.svg", "storm.png")
            .put("plane.svg", "travel.png")
            .build();

    private static final List<String> PROJECT_ICONS_TO_REMOVE = ImmutableList.of("default.svg", "code.svg", "coffee.svg",
            "design.svg", "drill.svg", "food.svg", "notes.svg", "red-flag.svg", "science.svg", "spanner.svg", "support.svg");

    private static final Map<String, String> USER_ICONS_TO_RENAME = ImmutableMap.<String, String>builder()
            .put("Avatar-default.svg", "Avatar-default.png")
            .build();

    private static final List<String> USER_ICONS_TO_REMOVE = ImmutableList.of("bull.svg", "cat.svg", "dog.svg", "female_1.svg", "female_2.svg",
            "female_3.svg", "female_4.svg", "ghost.svg", "male_1.svg", "male_2.svg", "male_3.svg", "male_4.svg", "male_5.svg",
            "male_6.svg", "male_8.svg", "owl.svg", "pirate.svg", "robot.svg", "vampire.svg");

    @Override
    public void downgrade() throws DowngradeException {
        downgradeIssueTypeIcons();
        downgradeProjectIcons();
        downgradeUserIcons();
    }

    private void downgradeIssueTypeIcons() {
        getDbConnectionManager().execute(new SqlCallback() {
            @Override
            public void run(final DbConnection connection) {
                connection.setAutoCommit(false);

                final QAvatar a = QAvatar.AVATAR;

                for (Map.Entry<String, String> iconToRenameEntry : ISSUE_TYPE_ICONS_TO_RENAME.entrySet()) {
                    final String oldIconName = iconToRenameEntry.getKey();
                    final String newIconName = iconToRenameEntry.getValue();

                    connection
                            .update(a)
                            .where(a.avatarType.eq(ISSUE_TYPE_AVATAR)
                                    .and(a.systemAvatar.eq(TRUE))
                                    .and(a.fileName.eq(oldIconName)))
                            .set(a.fileName, newIconName)
                            .set(a.contentType, PNG_CONTENT_TYPE)
                            .execute();
                }
                connection.commit();
            }
        });
    }

    private void downgradeProjectIcons() {
        getDbConnectionManager().execute(new SqlCallback() {
            @Override
            public void run(final DbConnection connection) {
                connection.setAutoCommit(false);

                final QAvatar a = QAvatar.AVATAR;

                for (Map.Entry<String, String> projectIconToRenameEntry : PROJECT_ICONS_TO_RENAME.entrySet()) {
                    final String oldIconName = projectIconToRenameEntry.getKey();
                    final String newIconName = projectIconToRenameEntry.getValue();

                    connection
                            .update(a)
                            .where(a.avatarType.eq(PROJECT_AVATAR)
                                    .and(a.systemAvatar.eq(TRUE))
                                    .and(a.fileName.eq(oldIconName)))
                            .set(a.fileName, newIconName)
                            .set(a.contentType, PNG_CONTENT_TYPE)
                            .execute();
                }

                final QOSPropertyEntry entry = QOSPropertyEntry.O_S_PROPERTY_ENTRY;
                final QOSPropertyString property = QOSPropertyString.O_S_PROPERTY_STRING;

                final Long defaultProjectAvatarId = obtainDefaultProjectIcon(connection);

                connection
                        .update(property)
                        .where(property.id.eq(SQLExpressions
                                .select(entry.id)
                                .from(entry)
                                .where(entry.propertyKey.eq(DEFAULT_PROJECT_AVATAR_KEY))))
                        .set(property.value, defaultProjectAvatarId.toString())
                        .execute();

                //List of system avatar ids to be renamed. On a sane instance this should be no more than 20.
                final List<Long> oldAvatarIds = connection
                        .newSqlQuery()
                        .select(a.id)
                        .from(a)
                        .where(a.avatarType.eq(PROJECT_AVATAR)
                                .and(a.systemAvatar.eq(TRUE)
                                        .and(a.fileName.in(PROJECT_ICONS_TO_REMOVE))))
                        .fetch();

                final QProject p = QProject.PROJECT;

                if (!oldAvatarIds.isEmpty()) {
                    connection
                            .update(p)
                            .where(p.avatar.in(oldAvatarIds))
                            .set(p.avatar, defaultProjectAvatarId)
                            .execute();
                } else {
                    log.warn("No project icons to downgrade found.");
                }

                connection
                        .delete(a)
                        .where(a.fileName.in(PROJECT_ICONS_TO_REMOVE)
                                .and(a.systemAvatar.eq(TRUE))
                                .and(a.avatarType.eq(PROJECT_AVATAR)
                                        .and(a.contentType.eq(SVG_CONTENT_TYPE))))
                        .execute();

                connection.commit();
            }
        });
    }

    private void downgradeUserIcons() {
        getDbConnectionManager().execute(new SqlCallback() {
            @Override
            public void run(final DbConnection connection) {
                connection.setAutoCommit(false);

                final QAvatar a = QAvatar.AVATAR;

                for (Map.Entry<String, String> userIconToUpdate : USER_ICONS_TO_RENAME.entrySet()) {
                    final String oldIconName = userIconToUpdate.getKey();
                    final String newIconName = userIconToUpdate.getValue();

                    connection.update(a)
                            .where(a.avatarType.eq(USER_AVATAR)
                                    .and(a.systemAvatar.eq(TRUE))
                                    .and(a.fileName.eq(oldIconName)))
                            .set(a.fileName, newIconName)
                            .set(a.contentType, PNG_CONTENT_TYPE)
                            .execute();
                }

                QOSPropertyEntry e = QOSPropertyEntry.O_S_PROPERTY_ENTRY;
                QOSPropertyNumber n = QOSPropertyNumber.O_S_PROPERTY_NUMBER;

                connection
                        .delete(n)
                        .where(n.id.in(SQLExpressions
                                .select(e.id)
                                .from(e)
                                .where(e.entityName.eq(USER_ENTITY_NAME)
                                        .and(e.propertyKey.eq(USER_AVATAR_PROPERTY_KEY))
                                        .and(e.type.eq(NUMBER_TYPE))))
                                .and(n.value.in(SQLExpressions
                                        .select(a.id)
                                        .from(a)
                                        .where(a.avatarType.eq(USER_AVATAR)
                                                .and(a.systemAvatar.eq(TRUE))
                                                .and(a.fileName.in(USER_ICONS_TO_REMOVE))))))
                        .execute();

                connection
                        .delete(a)
                        .where(a.fileName.in(USER_ICONS_TO_REMOVE)
                                .and(a.systemAvatar.eq(TRUE))
                                .and(a.avatarType.eq(USER_AVATAR)
                                        .and(a.contentType.eq(SVG_CONTENT_TYPE))))
                        .execute();

                connection.commit();
            }
        });
    }

    private Long obtainDefaultProjectIcon(final DbConnection connection) {
        final QAvatar a = QAvatar.AVATAR;
        @Nullable final Long defaultProjectAvatarId = selectDefaultProjectIcon(connection);
        if (defaultProjectAvatarId == null) {
            connection
                    .insert(a)
                    .set(a.fileName, DEFAULT_PROJECT_AVATAR_NAME)
                    .set(a.contentType, PNG_CONTENT_TYPE)
                    .set(a.avatarType, PROJECT_AVATAR)
                    .set(a.systemAvatar, TRUE)
                    .withId()
                    .execute();
            return selectDefaultProjectIcon(connection);
        } else {
            return defaultProjectAvatarId;
        }
    }

    @Nullable
    private Long selectDefaultProjectIcon(final DbConnection connection) {
        final QAvatar a = QAvatar.AVATAR;
        return connection.newSqlQuery()
                .select(a.id)
                .from(a)
                .where(a.avatarType.eq(PROJECT_AVATAR)
                        .and(a.systemAvatar.eq(TRUE))
                        .and(a.fileName.eq(DEFAULT_PROJECT_AVATAR_NAME)))
                .limit(1)
                .fetchOne();
    }


    @Override
    public int getBuildNumber() {
        return 70024;
    }

    @Nonnull
    @Override
    public String getShortDescription() {
        return "Downgrade issue type, project and user icons to raster versions.";
    }

    @Nonnull
    @Override
    public ReindexRequirement reindexRequired() {
        return ReindexRequirement.NONE;
    }
}