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

import io.delphi.qa.auto.awsmfrw.models.api.rest.v3.tracks.PlTrack;
import io.delphi.qa.auto.awsmfrw.models.api.rest.v3.tracks.PlTracks;
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.tests.BsTestCase;

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

public abstract class ArtistOnboardingApi_BaseTest extends BsTestCase {

    public 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" + getTrackIdQuery.queryText())).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");
    }

    public List<Map<String, String>> getSnowflakePriorityTable(String chartmetricId, String snowflackeTableLocator) {
        InterfaceDslSqlQuery grasIdSonyArtistQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from(snowflackeTableLocator)
                        .where(buildWhere(new LinkedHashMap<>() {{put("chartmetric_id", chartmetricId);}}))
                        .limit("10");

        return delphi.db().sf().sysAdmin().getAdminClient().queryRows(grasIdSonyArtistQuery.queryText());
    }

    public List<Map<String, String>> getSnowflakeSmChartmetricStat(String keyId, String tc, String snowflackeTableLocator) {

        LinkedHashMap<String, String> chartmetricId =
                new LinkedHashMap<>() {
                    {
                        put(keyId, tc);
                    }
                };

        InterfaceDslSqlQuery grasIdSonyArtistQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from(snowflackeTableLocator)
                        .where(buildWhere(chartmetricId))
                        .limit("10");

        return delphi.db().sf().sysAdmin().getAdminClient().queryRows(grasIdSonyArtistQuery.queryText());
    }

    public List<Map<String, String>> getSnowflakeCMmUrl(String chartmetricId, String snowflackeTableLocator) {

        InterfaceDslSqlQuery bandsintownCmUrlQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from(snowflackeTableLocator)
                        .where(
                                buildWhere(
                                        new HashMap<>() {
                                            {
                                                put("target_id", chartmetricId);
                                                put("account_id", EnDslSqlDefaultItems.NOT_NULL.get());
                                                put("target", "cm_artist");
                                            }
                                        }))
                        .close();

        return delphi.db().sf().sysAdmin().getAdminClient().queryRows(bandsintownCmUrlQuery.queryText());
    }
  public List<Map<String, String>> getSnowflakeChartmetricStat(String chartmetricId, String snowflackeTableLocator, String startDate) {
    InterfaceDslSqlQuery grasIdSonyArtistQuery =
        DslSql.select(InterfaceDslSqlQuery.ALL)
            .from(snowflackeTableLocator)
            .where(buildWhere(new LinkedHashMap<>() {{put("chartmetric_id", chartmetricId);}}))
            .and("REPORT_DATE > " + "'" + startDate + "'")
            .orderByDesc("report_date")
            .limitNoBracket("10");
    return delphi.db().sf().sysAdmin().getAdminClient().queryRows(grasIdSonyArtistQuery.queryText());
  }

    public List<Map<String, String>> getSnowflakeChartmetricStatForSpecificDate(
            String chartmetricId, String snowflackeTableLocator, String startDate) {

        InterfaceDslSqlQuery grasIdSonyArtistQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from(snowflackeTableLocator)
                        .where(buildWhere(new LinkedHashMap<>() {
                            {
                                put("chartmetric_id", chartmetricId);
                            }
                        }))
                        .and("REPORT_DATE in " + "('" + startDate + "')")
                        .limit("10");

        return delphi.db().sf().sysAdmin().getAdminClient().queryRows(grasIdSonyArtistQuery.queryText());
    }

    public List<Map<String, String>> getIsrcSnowflakeStat(String snowflackeTableLocator, PlTracks playlists) {

        StringJoiner joiner = new StringJoiner(",");
        for (PlTrack a : playlists.getItems()) {
            String s = "'" + a.getIsrc() + "'";
            joiner.add(s);
        }
        String isrcList = joiner.toString();

        InterfaceDslSqlQuery grasIdSonyArtistQuery =
                DslSql.select(InterfaceDslSqlQuery.ALL)
                        .from(snowflackeTableLocator)
                        .where("isrc in (" + isrcList + ")")
                        .close();

        return delphi.db().sf().sysAdmin().getAdminClient().queryRows(grasIdSonyArtistQuery.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 "));
  }
}