package io.delphi.qa.auto.awsmfrw.tests.l2;

import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.DslSql;
import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.EnDslSqlDefaultItems;
import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.InterfaceDslSqlQuery;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.chartmetric.MlSn_ItunesArtist;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.chartmetric.MlSn_SpotifyArtist;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.chartmetric_sys.MlSn_ArtistPriority;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.chartmetric_sys.MlSn_SonyArtist;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake.ds_chartmetric.raw_data.MlSn_CmUrl;
import io.delphi.qa.auto.awsmfrw.models.l2.MlFw_L2ArtistOnboardingReport;
import io.delphi.qa.auto.awsmfrw.models.l2.snow.dc_chartmetric.raw_data.cm_url.EnDelphiSnowSocialTypeLinks;
import io.delphi.qa.auto.awsmfrw.tests.BsTestCase;
import io.qameta.allure.Step;

import java.util.*;
import java.util.stream.Collectors;

public abstract class ArtistOnboarding_BaseTest extends BsTestCase {

  @Step("New artist onboarding INPUT report:")
  protected void reportInput(
      String msg,
      String insertSonyArtist,
      String insertArtistPriority,
      String updateSonyArtist,
      String updateArtistPriority) {
    log().info(msg);
  }

  protected String reportDescription(MlFw_L2ArtistOnboardingReport rtiArtistOnboardingReport) {
    return String.format(
        "RTI Artist Onboarding Benchmark statistics:\n\t- score=%s\n\t- output=%s\n\t- details:\n%s",
        rtiArtistOnboardingReport.get(),
        rtiArtistOnboardingReport.output(),
        gson.toJson(rtiArtistOnboardingReport));
  }

  @Step("New artist onboarding ONBOARDING report:")
  protected void reportOnboarding(String msg) {
    log().info(msg);
  }

  protected MlFw_L2ArtistOnboardingReport runBenchmark(String artistName, String grasId, String isrc) throws Exception {

      String chartmetricId = getCharmetricIdByIsrc(isrc, artistName);
      
    HashMap<String, InterfaceDslSqlQuery> queries = new HashMap<>();
    String sonyArtistLocator = new MlSn_SonyArtist().locator();
    String artistPriorityLocator = new MlSn_ArtistPriority().locator();
    String cmUrlLocator = new MlSn_CmUrl().locator();
    InterfaceDslSqlQuery spotifyArtisQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(new MlSn_SpotifyArtist().locator())
            .where(
                buildWhere(
                    new HashMap<String, String>() {
                      {
                        put("cm_artist", chartmetricId);
                      }
                    }))
            .close();
    InterfaceDslSqlQuery itunesArtistQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(new MlSn_ItunesArtist().locator())
            .where(
                buildWhere(
                    new HashMap<String, String>() {
                      {
                        put("cm_artist", chartmetricId);
                      }
                    }))
            .close();
    //        BANDSINTOWN
    InterfaceDslSqlQuery bandsintownCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new HashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.BANDSINTOWN.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    DEEZER
    InterfaceDslSqlQuery deezerCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.DEEZER.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    FACEBOOK
    InterfaceDslSqlQuery facebookCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.FACEBOOK.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    INSTAGRAM
    InterfaceDslSqlQuery instagramCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.INSTAGRAM.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    SPOTIFY
    InterfaceDslSqlQuery spotifyCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.SPOTIFY.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    TWITTER
    InterfaceDslSqlQuery twitterCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.TWITTER.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    WIKIPEDIA
    InterfaceDslSqlQuery wikipediaCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.WIKIPEDIA.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    //    YOUTUBE
    InterfaceDslSqlQuery youtubeCmUrlQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(cmUrlLocator)
            .where(
                buildWhere(
                    new LinkedHashMap<String, String>() {
                      {
                        put("target_id", chartmetricId);
                        put("type", EnDelphiSnowSocialTypeLinks.YOUTUBE.get());
                        put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                        put("target", "cm_artist");
                      }
                    }))
            .close();
    LinkedHashMap<String, String> tcGrasId =
        new LinkedHashMap<String, String>() {
          {
            put("gras_id", grasId);
          }
        };
    LinkedHashMap<String, String> tcArtistName =
        new LinkedHashMap<String, String>() {
          {
            put("artist_name", artistName);
          }
        };
    InterfaceDslSqlQuery grasIdSonyArtistQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(sonyArtistLocator)
            .where(buildWhere(tcGrasId))
            .close();
    LinkedHashMap<String, String> tcChartmetricId =
        new LinkedHashMap<String, String>() {
          {
            put("chartmetric_id", chartmetricId);
          }
        };
    InterfaceDslSqlQuery chartmetricSonyArtistQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(sonyArtistLocator)
            .where(buildWhere(tcChartmetricId))
            .close();
    InterfaceDslSqlQuery artistNameSonyArtistQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(sonyArtistLocator)
            .where(buildWhere(tcArtistName))
            .close();
    InterfaceDslSqlQuery chartmetricArtistPriorityQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(artistPriorityLocator)
            .where(buildWhere(tcChartmetricId))
            .close();
    InterfaceDslSqlQuery grasIdArtistPriorityQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(artistPriorityLocator)
            .where(buildWhere(tcGrasId))
            .close();
    InterfaceDslSqlQuery artistNameArtistPriorityQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(artistPriorityLocator)
            .where(buildWhere(tcArtistName))
            .close();
    queries.put("spotifyArtist", spotifyArtisQuery);
    queries.put("itunesArtist", itunesArtistQuery);
    queries.put("bandsintownCmUrl", bandsintownCmUrlQuery);
    queries.put("deezerCmUrl", deezerCmUrlQuery);
    queries.put("facebookCmUrl", facebookCmUrlQuery);
    queries.put("instagramCmUrl", instagramCmUrlQuery);
    queries.put("spotifyCmUrl", spotifyCmUrlQuery);
    queries.put("twitterCmUrl", twitterCmUrlQuery);
    queries.put("wikipediaCmUrl", wikipediaCmUrlQuery);
    queries.put("youtubeCmUrl", youtubeCmUrlQuery);
    queries.put("chartmetricSonyArtist", chartmetricSonyArtistQuery);
    queries.put("grasIdSonyArtist", grasIdSonyArtistQuery);
    queries.put("artistNameSonyArtist", artistNameSonyArtistQuery);
    queries.put("chartmetricArtistPriority", chartmetricArtistPriorityQuery);
    queries.put("grasIdArtistPriority", grasIdArtistPriorityQuery);
    queries.put("artistNameArtistPriority", artistNameArtistPriorityQuery);
    HashMap<String, List<Map<String, String>>> result = new HashMap<>();
    queries.entrySet().forEach(o -> result.put(o.getKey(), getQueryResult(o)));
    return new MlFw_L2ArtistOnboardingReport()
        .withGrasId(grasId)
        .withChartmetricId(chartmetricId)
        .withArtistName(artistName)
        .withGrasIdInSonyArtist(result.get("grasIdSonyArtist"))
        .withChartmetricIdInSonyArtist(result.get("chartmetricSonyArtist"))
        .withArtistNameInSonyArtist(result.get("artistNameSonyArtist"))
        .withChartmetricIdInArtistPriority(result.get("chartmetricArtistPriority"))
        .withGrasIdInArtistPriority(result.get("grasIdArtistPriority"))
        .withArtistNameInArtistPriority(result.get("artistNameArtistPriority"))
        .withChartmetricIdInItunesArtist(result.get("itunesArtist"))
        .withChartmetricIdInSpotifyArtist(result.get("spotifyArtist"))
        .withChartmetricIdInCmUrlOfBandsInTown(result.get("bandsintownCmUrl"))
        .withChartmetricIdInCmUrlOfDeezer(result.get("deezerCmUrl"))
        .withChartmetricIdInCmUrlOfFacebook(result.get("facebookCmUrl"))
        .withChartmetricIdInCmUrlOfInstagram(result.get("instagramCmUrl"))
        .withChartmetricIdInCmUrlOfSpotify(result.get("spotifyCmUrl"))
        .withChartmetricIdInCmUrlOfTwitter(result.get("twitterCmUrl"))
        .withChartmetricIdInCmUrlOfWikipedia(result.get("wikipediaCmUrl"))
        .withChartmetricIdInCmUrlOfYoutube(result.get("youtubeCmUrl"));
  }

    private String getCharmetricIdByIsrc(String isrc, String artistName) throws Exception {
        InterfaceDslSqlQuery getTrackIdQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from("DS_CHARTMETRIC.RAW_DATA.CM_TRACK")
                        .where(buildWhere(new LinkedHashMap<>() {{put("ISRC", isrc);}}))
                        .limit("10");

        List<Map<String, String>> sfDataChTrack =
                delphi.db().sf().sysAdmin().getAdminClient().queryRows(getTrackIdQuery.queryText());

        String trackId = sfDataChTrack.stream().findFirst().orElseThrow(() ->
                new Exception("ISRC is not Correect! Please check the ISRC or try another one")).get("ID");

        InterfaceDslSqlQuery getChartmetrickIdQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .fromNoBraskets("DS_CHARTMETRIC.RAW_DATA.L_CM_TRACK_CM_ARTIST a")
                        .innerJoin(" (SELECT distinct NAME,ID FROM DS_CHARTMETRIC.RAW_DATA.CM_ARTIST) p " +
                                "ON p.NAME = '" + artistName + "' and a.CM_ARTIST = p.ID")
                        .whereNoBracket(buildWhere(new LinkedHashMap<>() {{put("CM_TRACK", trackId);}}))
                        .limit("10");

        List<Map<String, String>> sfDataLcmTrackCmArtist =
                delphi.db().sf().sysAdmin().getAdminClient().queryRows(getChartmetrickIdQuery.queryText());

        return sfDataLcmTrackCmArtist.stream().findFirst().orElseThrow(() ->
                        new Exception("Name is not related to this ISRC. Please input parameters and try again"))
                .get("CM_ARTIST");
    }

    @Step("Query rows - {entry.key}")
  private List<Map<String, String>> getQueryResult(Map.Entry<String, InterfaceDslSqlQuery> entry) {
    return delphi.db().sf().sysAdmin().getAdminClient().queryRows(entry.getValue().queryText());
  }

  private String buildWhere(Map<String, String> where) {
    return where.entrySet().stream()
        .map(
            entry ->
                String.format(
                    entry.getValue().toLowerCase(Locale.ROOT).contains("null")
                        ? "%1$s is %2$s"
                        : entry.getValue().split(",").length > 0
                            ? "%1$s in ('%2$s')"
                            : "%1$s = '%2$s'",
                    entry.getKey(),
                    entry.getValue()))
        .collect(Collectors.joining("\nAND "));
  }
}