package io.delphiplatform.api.v3.rdb.service.ads;

import com.google.common.collect.ImmutableMap;
import com.healthmarketscience.sqlbuilder.ComboExpression;
import com.healthmarketscience.sqlbuilder.ComboExpression.Op;
import com.healthmarketscience.sqlbuilder.Expression;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;

import org.springframework.stereotype.Service;

import java.util.List;
import java.util.function.BiConsumer;
import java.util.function.Function;

import io.delphiplatform.api.util.model.ModelUtils;
import io.delphiplatform.api.util.SqlBuilderUtils;
import io.delphiplatform.api.v3.constant.ColumnNameConstants;
import io.delphiplatform.api.v3.model.ads.AdMetrics;

import static io.delphiplatform.api.util.SqlBuilderUtils.aggByCurrency;
import static io.delphiplatform.api.util.SqlBuilderUtils.getCostPer;
import static io.delphiplatform.api.util.SqlBuilderUtils.getDivideOfSums;
import static io.delphiplatform.api.util.SqlBuilderUtils.getSum;
import static io.delphiplatform.api.util.SqlBuilderUtils.getVideoWatched;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_COMMENTS;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_FOLLOWS;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_LIKES;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_SHARES;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_VIDEO_PLAY_ACTIONS;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_WATCHED_2;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.TIKTOK_WATCHED_6;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.AVERAGE_FREQUENCY;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CLICKS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.COMMENTS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CONVERSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CONVERSION_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CPM_IMPRESSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CPM_REACH;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_COMMENT;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_CONVERSION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_CUSTOM_CONVERSION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_ENGAGEMENT;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_LANDING_PAGE_VIEW;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_LEAD;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_REACTION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_THRUPLAY;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_UNIQUE_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_UNIQUE_CONVERSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_UNIQUE_CUSTOM_CONVERSION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_UNIQUE_OUTBOUND_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CP_VIDEO_VIEW;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CTR;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.CUSTOM_CONVERSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.ENGAGEMENTS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.IMPRESSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LANDING_PAGE_VIEWS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LEADS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_BOUNCE_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_CLICKS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_CONVERSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_CTR;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_DIRECT_TO_SERVICE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_EVENTS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_EVENT_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_IN_PAGE_PLAYS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_NEW_SESSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_PAGE_VIEWS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_PLATFORM_TO_LINKFIRE_BOUNCE_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_PLATFORM_TO_LINKFIRE_CLICK_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.LF_PLATFORM_TO_LINKFIRE_EVENT_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CPM_IMPRESSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CPM_REACH;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_COMMENT;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_CONVERSION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_CUSTOM_CONVERSION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_ENGAGEMENT;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_LANDING_PAGE_VIEW;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_LEAD;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_REACTION;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_THRUPLAY;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_UNIQUE_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_UNIQUE_OUTBOUND_CLICK;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_CP_VIDEO_VIEW;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.MC_SPEND;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.PAGE_LIKES;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.REACH;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.REACTIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.SPEND;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.THRUPLAYS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_COMMENTS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_FOLLOWS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_LIKES;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_SHARES;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_VIDEO_PLAY_ACTIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_VIDEO_WATCHED_2_S;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.TT_VIDEO_WATCHED_6_S;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_CLICKS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_CONVERSIONS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_CTR;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_CUSTOM_CONV;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_OUTBOUND_CLICKS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.UNIQUE_OUTBOUND_CTR;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_COMPLETION_RATE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_VIEWS;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_WATCHED100;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_WATCHED25;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_WATCHED50;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_WATCHED75;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_WATCHED_BASE;
import static io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum.VIDEO_THROUGH_RATE;

@Service
public class AdsMetricsQueryMappingInfoProvider {

    private static final ImmutableMap<AdsMetricEnum, BiConsumer<AdMetrics, Object>> METRIC_TO_RESPONSE_MAPPER =
        ImmutableMap.<AdsMetricEnum, BiConsumer<AdMetrics, Object>>builder()
            //@formatter:off metric per row
            .put(AVERAGE_FREQUENCY, ((model, o) -> model.getGeneral().averageFrequency(ModelUtils.toBigDecimal(o))))
            .put(CLICKS, ((model, o) -> model.getGeneral().clicks(ModelUtils.toLong(o))))
            .put(COMMENTS, ((model, o) -> model.getGeneral().comments(ModelUtils.toLong(o))))
            .put(CONVERSIONS, ((model, o) -> model.getGeneral().conversions(ModelUtils.toLong(o))))
            .put(CONVERSION_RATE, ((model, o) -> model.getGeneral().conversionRate(ModelUtils.toBigDecimal(o))))
            .put(CPM_IMPRESSIONS, ((model, o) -> model.getFinance().cpmImpressions((ModelUtils.toBigDecimal(o)))))
            .put(CPM_REACH, ((model, o) -> model.getFinance().cpmReach((ModelUtils.toBigDecimal(o)))))
            .put(CP_CLICK, ((model, o) -> model.getFinance().cpClick((ModelUtils.toBigDecimal(o)))))
            .put(CP_COMMENT, ((model, o) -> model.getFinance().cpComment((ModelUtils.toBigDecimal(o)))))
            .put(CP_CONVERSION, ((model, o) -> model.getFinance().cpConversion((ModelUtils.toBigDecimal(o)))))
            .put(CP_CUSTOM_CONVERSION, ((model, o) -> model.getFinance().cpCustomConversion((ModelUtils.toBigDecimal(o)))))
            .put(CP_ENGAGEMENT, ((model, o) -> model.getFinance().cpEngagement((ModelUtils.toBigDecimal(o)))))
            .put(CP_LANDING_PAGE_VIEW, ((model, o) -> model.getFinance().cpLandingPageView((ModelUtils.toBigDecimal(o)))))
            .put(CP_LEAD, ((model, o) -> model.getFinance().cpLead((ModelUtils.toBigDecimal(o)))))
            .put(CP_REACTION, ((model, o) -> model.getFinance().cpReaction((ModelUtils.toBigDecimal(o)))))
            .put(CP_THRUPLAY, ((model, o) -> model.getFinance().cpThruplay((ModelUtils.toBigDecimal(o)))))
            .put(CP_UNIQUE_CLICK, ((model, o) -> model.getFinance().cpUniqueClick((ModelUtils.toBigDecimal(o)))))
            .put(CP_UNIQUE_CONVERSIONS, ((model, o) -> model.getFinance().cpUniqueConversion((ModelUtils.toBigDecimal(o)))))
            .put(CP_UNIQUE_CUSTOM_CONVERSION, ((model, o) -> model.getFinance().cpUniqueCustomConversion((ModelUtils.toBigDecimal(o)))))
            .put(CP_UNIQUE_OUTBOUND_CLICK, ((model, o) -> model.getFinance().cpUniqueOutboundClick((ModelUtils.toBigDecimal(o)))))
            .put(CP_VIDEO_VIEW, ((model, o) -> model.getFinance().cpVideoView((ModelUtils.toBigDecimal(o)))))
            .put(CTR, ((model, o) -> model.getGeneral().ctr(ModelUtils.toBigDecimal(o))))
            .put(CUSTOM_CONVERSIONS, ((model, o) -> model.getGeneral().customConversions(ModelUtils.toLong(o))))
            .put(ENGAGEMENTS, ((model, o) -> model.getGeneral().engagements(ModelUtils.toLong(o))))
            .put(IMPRESSIONS, ((model, o) -> model.getGeneral().impressions(ModelUtils.toLong(o))))
            .put(LANDING_PAGE_VIEWS, ((model, o) -> model.getGeneral().landingPageViews(ModelUtils.toLong(o))))
            .put(LEADS, ((model, o) -> model.getGeneral().leads(ModelUtils.toLong(o))))
            .put(PAGE_LIKES, ((model, o) -> model.getGeneral().facebookPageLikes(ModelUtils.toLong(o))))
            .put(REACH, ((model, o) -> model.getGeneral().reach(ModelUtils.toLong(o))))
            .put(REACTIONS, ((model, o) -> model.getGeneral().reactions(ModelUtils.toLong(o))))
            .put(SPEND, ((model, o) -> model.getFinance().spend(ModelUtils.toBigDecimal(o))))
            .put(THRUPLAYS, ((model, o) -> model.getVideo().thruplays(ModelUtils.toLong(o))))
            .put(UNIQUE_CLICKS, ((model, o) -> model.getGeneral().uniqueClicks(ModelUtils.toLong(o))))
            .put(UNIQUE_CONVERSIONS, ((model, o) -> model.getGeneral().uniqueConv(ModelUtils.toLong(o))))
            .put(UNIQUE_CTR, ((model, o) -> model.getGeneral().uniqueCtr(ModelUtils.toBigDecimal(o))))
            .put(UNIQUE_CUSTOM_CONV, ((model, o) -> model.getGeneral().uniqueCustomConv(ModelUtils.toLong(o))))
            .put(UNIQUE_OUTBOUND_CLICKS, ((model, o) -> model.getGeneral().uniqueOutboundClicks(ModelUtils.toLong(o))))
            .put(UNIQUE_OUTBOUND_CTR, ((model, o) -> model.getGeneral().uniqueOutboundCtr(ModelUtils.toBigDecimal(o))))
            .put(VIDEO_VIEWS, ((model, o) -> model.getVideo().videoViews(ModelUtils.toLong(o))))
            .put(VIDEO_WATCHED_BASE, ((model, o) -> {}))
            .put(VIDEO_WATCHED100, ((model, o) -> model.getVideo().videoWatched100(ModelUtils.toBigDecimal(o))))
            .put(VIDEO_WATCHED25, ((model, o) -> model.getVideo().videoWatched25(ModelUtils.toBigDecimal(o))))
            .put(VIDEO_WATCHED50, ((model, o) -> model.getVideo().videoWatched50(ModelUtils.toBigDecimal(o))))
            .put(VIDEO_WATCHED75, ((model, o) -> model.getVideo().videoWatched75(ModelUtils.toBigDecimal(o))))

            .put(TT_COMMENTS, ((model, o) -> model.getGeneral().tiktokComments(ModelUtils.toLong(o))))
            .put(TT_LIKES, ((model, o) -> model.getGeneral().tiktokLikes(ModelUtils.toLong(o))))
            .put(TT_SHARES, ((model, o) -> model.getGeneral().tiktokShares(ModelUtils.toLong(o))))
            .put(TT_FOLLOWS, ((model, o) -> model.getGeneral().tiktokFollows(ModelUtils.toLong(o))))
            .put(TT_VIDEO_WATCHED_2_S, ((model, o) -> model.getVideo().videoWatched2s(ModelUtils.toBigDecimal(o))))
            .put(TT_VIDEO_WATCHED_6_S, ((model, o) -> model.getVideo().videoWatched6s(ModelUtils.toBigDecimal(o))))
            .put(TT_VIDEO_PLAY_ACTIONS, ((model, o) -> model.getVideo().videoPlayActions(ModelUtils.toBigDecimal(o))))

            .put(LF_BOUNCE_RATE, ((model, o) -> model.getLinkfireOrInit().bounceRate(ModelUtils.toBigDecimal(o))))
            .put(LF_CLICKS, ((model, o) -> model.getLinkfireOrInit().clicks(ModelUtils.toLong(o))))
            .put(LF_CONVERSIONS, ((model, o) -> model.getLinkfireOrInit().conversions(ModelUtils.toLong(o))))
            .put(LF_CTR, ((model, o) -> model.getLinkfireOrInit().ctr(ModelUtils.toBigDecimal(o))))
            .put(LF_DIRECT_TO_SERVICE, ((model, o) -> model.getLinkfireOrInit().directToService(ModelUtils.toLong(o))))
            .put(LF_EVENT_RATE, ((model, o) -> model.getLinkfireOrInit().eventRate(ModelUtils.toBigDecimal(o))))
            .put(LF_EVENTS, ((model, o) -> model.getLinkfireOrInit().events(ModelUtils.toLong(o))))
            .put(LF_IN_PAGE_PLAYS, ((model, o) -> model.getLinkfireOrInit().inPagePlays(ModelUtils.toLong(o))))
            .put(LF_NEW_SESSIONS, ((model, o) -> model.getLinkfireOrInit().newSessions(ModelUtils.toLong(o))))
            .put(LF_PAGE_VIEWS, ((model, o) -> model.getLinkfireOrInit().pageViews(ModelUtils.toLong(o))))
            .put(LF_PLATFORM_TO_LINKFIRE_BOUNCE_RATE, ((model, o) -> model.getLinkfireOrInit().platformToLinkfireBounceRate(ModelUtils.toBigDecimal(o))))
            .put(LF_PLATFORM_TO_LINKFIRE_CLICK_RATE, ((model, o) -> model.getLinkfireOrInit().platformToLinkfireClickRate(ModelUtils.toBigDecimal(o))))
            .put(LF_PLATFORM_TO_LINKFIRE_EVENT_RATE, ((model, o) -> model.getLinkfireOrInit().platformToLinkfireEventRate(ModelUtils.toBigDecimal(o))))
            //new multi currency metrics
            .put(MC_SPEND, ((model, o) -> model.getFinanceByCurrency().spend(ModelUtils.toMap(o))))
            .put(MC_CPM_IMPRESSIONS, ((model, o) -> model.getFinanceByCurrency().cpmImpressions(ModelUtils.toMap(o))))
            .put(MC_CPM_REACH, ((model, o) -> model.getFinanceByCurrency().cpmReach(ModelUtils.toMap(o))))
            .put(MC_CP_CLICK, ((model, o) -> model.getFinanceByCurrency().cpClick(ModelUtils.toMap(o))))
            .put(MC_CP_COMMENT, ((model, o) -> model.getFinanceByCurrency().cpComment(ModelUtils.toMap(o))))
            .put(MC_CP_CONVERSION, ((model, o) -> model.getFinanceByCurrency().cpConversion(ModelUtils.toMap(o))))
            .put(MC_CP_CUSTOM_CONVERSION, ((model, o) -> model.getFinanceByCurrency().cpCustomConversion(ModelUtils.toMap(o))))
            .put(MC_CP_ENGAGEMENT, ((model, o) -> model.getFinanceByCurrency().cpEngagement((ModelUtils.toMap(o)))))
            .put(MC_CP_LANDING_PAGE_VIEW, ((model, o) -> model.getFinanceByCurrency().cpLandingPageView((ModelUtils.toMap(o)))))
            .put(MC_CP_LEAD, ((model, o) -> model.getFinanceByCurrency().cpLead((ModelUtils.toMap(o)))))
            .put(MC_CP_REACTION, ((model, o) -> model.getFinanceByCurrency().cpReaction(ModelUtils.toMap(o))))
            .put(MC_CP_THRUPLAY, ((model, o) -> model.getFinanceByCurrency().cpThruplay(ModelUtils.toMap(o))))
            .put(MC_CP_UNIQUE_CLICK, ((model, o) -> model.getFinanceByCurrency().cpUniqueClick(ModelUtils.toMap(o))))
            .put(MC_CP_UNIQUE_OUTBOUND_CLICK, ((model, o) -> model.getFinanceByCurrency().cpUniqueOutboundClick(ModelUtils.toMap(o))))
            .put(MC_CP_VIDEO_VIEW, ((model, o) -> model.getFinanceByCurrency().cpVideoView(ModelUtils.toMap(o))))
            .put(VIDEO_COMPLETION_RATE, ((model, o) -> model.getVideo().videoCompletionRate(ModelUtils.toBigDecimal(o))))
            .put(VIDEO_THROUGH_RATE, ((model, o) -> model.getVideo().videoThroughRate(ModelUtils.toBigDecimal(o))))
            //@formatter:on
            .build();
    private static final ImmutableMap<AdsMetricEnum, Function<DbTable, Expression>> METRIC_TO_SUBQUERY_SQL_EXPRESSION =
        ImmutableMap.<AdsMetricEnum, Function<DbTable, Expression>>builder()
            //@formatter:off metric per row
            .put(AVERAGE_FREQUENCY, ((reportTable) -> null)) // not available for sub query
            .put(CLICKS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.CLICKS)))
            .put(COMMENTS, ((reportTable) ->  getSum(reportTable, ColumnNameConstants.COMMENTS)))
            .put(CONVERSIONS, ((reportTable) ->  getSum(reportTable, ColumnNameConstants.CONVERSIONS)))
            .put(CONVERSION_RATE, ((reportTable) -> null)) // not available for sub query
            .put(CPM_IMPRESSIONS, ((reportTable) -> null)) // not available for sub query
            .put(CPM_REACH, ((reportTable) -> null)) // not available for sub query
            .put(CP_CLICK, ((reportTable) -> null)) // not available for sub query
            .put(CP_COMMENT, ((reportTable) -> null)) // not available for sub query
            .put(CP_CONVERSION, ((reportTable) -> null)) // not available for sub query
            .put(CP_CUSTOM_CONVERSION, ((reportTable) -> null)) // not available for sub query
            .put(CP_ENGAGEMENT, ((reportTable) -> null)) // not available for sub query
            .put(CP_LANDING_PAGE_VIEW, ((reportTable) -> null)) // not available for sub query
            .put(CP_LEAD, ((reportTable) -> null)) // not available for sub query
            .put(CP_REACTION, ((reportTable) -> null)) // not available for sub query
            .put(CP_THRUPLAY, ((reportTable) -> null)) // not available for sub query
            .put(CP_UNIQUE_CLICK, ((reportTable) -> null)) // not available for sub query
            .put(CP_UNIQUE_CONVERSIONS, ((reportTable) -> null)) // not available for sub query
            .put(CP_UNIQUE_CUSTOM_CONVERSION, ((reportTable) -> null)) // not available for sub query
            .put(CP_UNIQUE_OUTBOUND_CLICK, ((reportTable) -> null)) // not available for sub query
            .put(CP_VIDEO_VIEW, ((reportTable) -> null)) // not available for sub query
            .put(CTR, ((reportTable) -> null)) // not available for sub query

            .put(TT_COMMENTS, ((reportTable) -> getSum(reportTable, TIKTOK_COMMENTS)))
            .put(TT_LIKES, ((reportTable) -> getSum(reportTable, TIKTOK_LIKES)))
            .put(TT_SHARES, ((reportTable) -> getSum(reportTable, TIKTOK_SHARES)))
            .put(TT_FOLLOWS, ((reportTable) -> getSum(reportTable, TIKTOK_FOLLOWS)))
            .put(TT_VIDEO_WATCHED_2_S, ((reportTable) -> getSum(reportTable, TIKTOK_WATCHED_2)))
            .put(TT_VIDEO_WATCHED_6_S, ((reportTable) -> getSum(reportTable, TIKTOK_WATCHED_6)))
            .put(TT_VIDEO_PLAY_ACTIONS, ((reportTable) -> getSum(reportTable, TIKTOK_VIDEO_PLAY_ACTIONS)))

            .put(CUSTOM_CONVERSIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.CUSTOM_CONVERSION)))
            .put(ENGAGEMENTS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.ENGAGEMENTS)))
            .put(IMPRESSIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.IMPRESSIONS)))
            .put(LANDING_PAGE_VIEWS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LANDING_PAGE_VIEWS)))
            .put(LEADS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LEADS)))
            .put(PAGE_LIKES, ((reportTable) -> getSum(reportTable, ColumnNameConstants.PAGE_LIKES)))
            .put(REACH, ((reportTable) -> getSum(reportTable, ColumnNameConstants.REACH)))
            .put(REACTIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.REACTIONS)))
            .put(SPEND, ((reportTable) -> getSum(reportTable, ColumnNameConstants.SPEND)))
            .put(THRUPLAYS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.THRUPLAYS)))
            .put(UNIQUE_CLICKS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.UNIQUE_CLICKS)))
            .put(UNIQUE_CONVERSIONS, ((reportTable) -> null)) // not available for sub query
            .put(UNIQUE_CTR, ((reportTable) -> null)) // not available for sub query
            .put(UNIQUE_CUSTOM_CONV, ((reportTable) -> null)) // not available for sub query
            .put(UNIQUE_OUTBOUND_CLICKS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.UNIQUE_OUTBOUND_CLICKS)))
            .put(UNIQUE_OUTBOUND_CTR, ((reportTable) -> null)) // not available for sub query
            .put(VIDEO_VIEWS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.VIDEO_VIEWS)))
            .put(VIDEO_WATCHED_BASE, ((reportTable) -> getSum(reportTable, List.of(ColumnNameConstants.VIDEO_PLAY_ACTIONS, ColumnNameConstants.VIDEO_VIEWS))))
            .put(VIDEO_WATCHED100, ((reportTable) -> getSum(reportTable, ColumnNameConstants.VIDEO_WATCHED_100)))
            .put(VIDEO_WATCHED25, ((reportTable) -> getSum(reportTable, ColumnNameConstants.VIDEO_WATCHED_25)))
            .put(VIDEO_WATCHED50, ((reportTable) -> getSum(reportTable, ColumnNameConstants.VIDEO_WATCHED_50)))
            .put(VIDEO_WATCHED75, ((reportTable) -> getSum(reportTable, ColumnNameConstants.VIDEO_WATCHED_75)))

            .put(LF_BOUNCE_RATE, ((reportTable) -> null)) // not available for sub query
            .put(LF_CLICKS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_CLICKS)))
            .put(LF_CONVERSIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_CONVERSIONS)))
            .put(LF_CTR, ((reportTable) -> null)) // not available for sub query
            .put(LF_DIRECT_TO_SERVICE, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_DIRECT_TO_SERVICE)))
            .put(LF_EVENT_RATE, ((reportTable) -> null)) // not available for sub query
            .put(LF_EVENTS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_EVENTS)))
            .put(LF_IN_PAGE_PLAYS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_IN_PAGE_PLAYS)))
            .put(LF_NEW_SESSIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_NEW_SESSIONS_COUNT)))
            .put(LF_PAGE_VIEWS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.LINKFIRE_PAGE_VIEWS)))
            .put(LF_PLATFORM_TO_LINKFIRE_BOUNCE_RATE, ((reportTable) -> null)) // not available for sub query
            .put(LF_PLATFORM_TO_LINKFIRE_CLICK_RATE, ((reportTable) -> null)) // not available for sub query
            .put(LF_PLATFORM_TO_LINKFIRE_EVENT_RATE, ((reportTable) -> null)) // not available for sub query


            .put(MC_SPEND, ((reportTable) -> getSum(reportTable, ColumnNameConstants.SPEND)))
            .put(MC_CPM_IMPRESSIONS, ((reportTable) -> SqlBuilderUtils.getSafeCPM(reportTable, ColumnNameConstants.IMPRESSIONS)))
            .put(MC_CPM_REACH, ((reportTable) -> SqlBuilderUtils.getSafeCPM(reportTable, ColumnNameConstants.REACH)))
            .put(MC_CP_CLICK, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.CLICKS)))
            .put(MC_CP_COMMENT, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.COMMENTS)))
            .put(MC_CP_CONVERSION, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.CONVERSIONS)))
            .put(MC_CP_CUSTOM_CONVERSION, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.CUSTOM_CONVERSION)))
            .put(MC_CP_ENGAGEMENT, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.ENGAGEMENTS)))
            .put(MC_CP_LANDING_PAGE_VIEW, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.LANDING_PAGE_VIEWS)))
            .put(MC_CP_LEAD, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.LEADS)))
            .put(MC_CP_REACTION, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.REACTIONS)))
            .put(MC_CP_THRUPLAY, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.THRUPLAYS)))
            .put(MC_CP_UNIQUE_CLICK, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.UNIQUE_CLICKS)))
            .put(MC_CP_UNIQUE_OUTBOUND_CLICK, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.UNIQUE_OUTBOUND_CLICKS)))
            .put(MC_CP_VIDEO_VIEW, ((reportTable) -> getCostPer(reportTable, ColumnNameConstants.VIDEO_VIEWS)))
            .put(VIDEO_COMPLETION_RATE, ((reportTable) -> null)) // not available for sub query
            .put(VIDEO_THROUGH_RATE, ((reportTable) -> null)) // not available for sub query
            //@formatter:on
            .build();
    private static final ImmutableMap<AdsMetricEnum, Function<DbTable, Expression>> METRIC_TO_ROOT_QUERY_SQL_EXPRESSION =
        ImmutableMap.<AdsMetricEnum, Function<DbTable, Expression>>builder()
            //@formatter:off metric per line
            .put(AVERAGE_FREQUENCY, ((reportTable) -> getDivideOfSums(reportTable, IMPRESSIONS, REACH)))
            .put(CLICKS, ((reportTable) -> getSum(reportTable, CLICKS)))
            .put(COMMENTS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.COMMENTS)))
            .put(CONVERSIONS, ((reportTable) -> getSum(reportTable, ColumnNameConstants.CONVERSIONS)))
            .put(CONVERSION_RATE, ((reportTable) -> getDivideOfSums(reportTable, CONVERSIONS, IMPRESSIONS)))
            .put(CPM_IMPRESSIONS, ((reportTable) -> SqlBuilderUtils.getSafeCPM(reportTable, IMPRESSIONS)))
            .put(CPM_REACH, ((reportTable) -> SqlBuilderUtils.getSafeCPM(reportTable, REACH)))
            .put(CP_CLICK, ((reportTable) -> getCostPer(reportTable, CLICKS)))
            .put(CP_COMMENT, ((reportTable) -> getCostPer(reportTable, COMMENTS)))
            .put(CP_CONVERSION, ((reportTable) -> getCostPer(reportTable, CONVERSIONS)))
            .put(CP_CUSTOM_CONVERSION, ((reportTable) -> getCostPer(reportTable, CUSTOM_CONVERSIONS)))
            .put(CP_ENGAGEMENT, ((reportTable) -> getCostPer(reportTable, ENGAGEMENTS)))
            .put(CP_LANDING_PAGE_VIEW, ((reportTable) -> getCostPer(reportTable, LANDING_PAGE_VIEWS)))
            .put(CP_LEAD, ((reportTable) -> getCostPer(reportTable, LEADS)))
            .put(CP_REACTION, ((reportTable) -> getCostPer(reportTable, REACTIONS)))
            .put(CP_THRUPLAY, ((reportTable) -> getCostPer(reportTable, THRUPLAYS)))
            .put(CP_UNIQUE_CLICK, ((reportTable) -> getCostPer(reportTable, UNIQUE_CLICKS)))

            .put(TT_COMMENTS, ((reportTable) -> getSum(reportTable, TT_COMMENTS)))
            .put(TT_LIKES, ((reportTable) -> getSum(reportTable, TT_LIKES)))
            .put(TT_SHARES, ((reportTable) -> getSum(reportTable, TT_SHARES)))
            .put(TT_FOLLOWS, ((reportTable) -> getSum(reportTable, TT_FOLLOWS)))
            .put(TT_VIDEO_WATCHED_2_S, ((reportTable) -> getSum(reportTable, TT_VIDEO_WATCHED_2_S)))
            .put(TT_VIDEO_WATCHED_6_S, ((reportTable) -> getSum(reportTable, TT_VIDEO_WATCHED_6_S)))
            .put(TT_VIDEO_PLAY_ACTIONS, ((reportTable) -> getSum(reportTable, TT_VIDEO_PLAY_ACTIONS)))

            .put(CP_UNIQUE_CONVERSIONS, ((reportTable) -> null))
            .put(CP_UNIQUE_CUSTOM_CONVERSION, ((reportTable) -> null))
            .put(CP_UNIQUE_OUTBOUND_CLICK, ((reportTable) -> getCostPer(reportTable, UNIQUE_OUTBOUND_CLICKS)))
            .put(CP_VIDEO_VIEW, ((reportTable) -> getCostPer(reportTable, VIDEO_VIEWS)))
            .put(CTR, ((reportTable) -> getDivideOfSums(reportTable, CLICKS, IMPRESSIONS)))
            .put(CUSTOM_CONVERSIONS, ((reportTable) -> getSum(reportTable, CUSTOM_CONVERSIONS)))
            .put(ENGAGEMENTS, ((reportTable) -> getSum(reportTable, ENGAGEMENTS)))
            .put(IMPRESSIONS, ((reportTable) -> getSum(reportTable, IMPRESSIONS)))
            .put(LANDING_PAGE_VIEWS, ((reportTable) -> getSum(reportTable, LANDING_PAGE_VIEWS)))
            .put(LEADS, ((reportTable) -> getSum(reportTable, LEADS)))
            .put(PAGE_LIKES, ((reportTable) -> getSum(reportTable, PAGE_LIKES)))
            .put(REACH, ((reportTable) -> getSum(reportTable, REACH)))
            .put(REACTIONS, ((reportTable) -> getSum(reportTable, REACTIONS)))
            .put(SPEND, ((reportTable) -> getSum(reportTable, SPEND)))
            .put(THRUPLAYS, ((reportTable) -> getSum(reportTable, THRUPLAYS)))
            .put(UNIQUE_CLICKS, ((reportTable) -> getSum(reportTable, UNIQUE_CLICKS)))
            .put(UNIQUE_CONVERSIONS, ((reportTable) -> null)) // missing data for this metric
            .put(UNIQUE_CTR, ((reportTable) -> getDivideOfSums(reportTable, UNIQUE_CLICKS, IMPRESSIONS)))
            .put(UNIQUE_CUSTOM_CONV, ((reportTable) -> null)) // missing data for this metric
            .put(UNIQUE_OUTBOUND_CLICKS, ((reportTable) -> getSum(reportTable, UNIQUE_OUTBOUND_CLICKS)))
            .put(UNIQUE_OUTBOUND_CTR, ((reportTable) -> getDivideOfSums(reportTable, UNIQUE_OUTBOUND_CLICKS, IMPRESSIONS)))
            .put(VIDEO_VIEWS, ((reportTable) -> getSum(reportTable, VIDEO_VIEWS)))
            .put(VIDEO_WATCHED_BASE, ((reportTable) -> getSum(reportTable, VIDEO_WATCHED_BASE)))
            .put(VIDEO_WATCHED100, ((reportTable) -> getVideoWatched(reportTable, VIDEO_WATCHED100)))
            .put(VIDEO_WATCHED25, ((reportTable) -> getVideoWatched(reportTable, VIDEO_WATCHED25)))
            .put(VIDEO_WATCHED50, ((reportTable) -> getVideoWatched(reportTable, VIDEO_WATCHED50)))
            .put(VIDEO_WATCHED75, ((reportTable) -> getVideoWatched(reportTable, VIDEO_WATCHED75)))

            .put(LF_BOUNCE_RATE, ((reportTable) -> new ComboExpression(Op.SUBTRACT, 1, getDivideOfSums(reportTable, LF_CLICKS, LF_PAGE_VIEWS))))
            .put(LF_CLICKS, ((reportTable) -> getSum(reportTable, LF_CLICKS)))
            .put(LF_CONVERSIONS, ((reportTable) -> getSum(reportTable, LF_CONVERSIONS)))
            .put(LF_CTR, ((reportTable) -> getDivideOfSums(reportTable, LF_CLICKS, LF_PAGE_VIEWS)))
            .put(LF_DIRECT_TO_SERVICE, ((reportTable) -> getSum(reportTable, LF_DIRECT_TO_SERVICE)))
            .put(LF_EVENT_RATE, ((reportTable) -> getDivideOfSums(reportTable, LF_EVENTS, LF_PAGE_VIEWS)))
            .put(LF_EVENTS, ((reportTable) -> getSum(reportTable, LF_EVENTS)))
            .put(LF_IN_PAGE_PLAYS, ((reportTable) -> getSum(reportTable, LF_IN_PAGE_PLAYS)))
            .put(LF_NEW_SESSIONS, ((reportTable) -> getSum(reportTable, LF_NEW_SESSIONS)))
            .put(LF_PAGE_VIEWS, ((reportTable) -> getSum(reportTable, LF_PAGE_VIEWS)))
            .put(LF_PLATFORM_TO_LINKFIRE_BOUNCE_RATE, ((reportTable) -> new ComboExpression(Op.SUBTRACT, 1D, getDivideOfSums(reportTable, LF_CLICKS, IMPRESSIONS))))
            .put(LF_PLATFORM_TO_LINKFIRE_CLICK_RATE, ((reportTable) -> getDivideOfSums(reportTable, LF_CLICKS, IMPRESSIONS)))
            .put(LF_PLATFORM_TO_LINKFIRE_EVENT_RATE, ((reportTable) -> getDivideOfSums(reportTable, LF_EVENTS, IMPRESSIONS)))

            .put(MC_SPEND, ((reportTable) -> aggByCurrency(reportTable, MC_SPEND)))
            .put(MC_CPM_IMPRESSIONS, ((reportTable) -> aggByCurrency(reportTable, MC_CPM_IMPRESSIONS)))
            .put(MC_CPM_REACH, ((reportTable) -> aggByCurrency(reportTable, MC_CPM_REACH)))
            .put(MC_CP_CLICK, ((reportTable) -> aggByCurrency(reportTable, MC_CP_CLICK)))
            .put(MC_CP_COMMENT, ((reportTable) -> aggByCurrency(reportTable, MC_CP_COMMENT)))
            .put(MC_CP_CONVERSION, ((reportTable) -> aggByCurrency(reportTable, MC_CP_CONVERSION)))
            .put(MC_CP_CUSTOM_CONVERSION, ((reportTable) -> aggByCurrency(reportTable, MC_CP_CUSTOM_CONVERSION)))
            .put(MC_CP_ENGAGEMENT, ((reportTable) -> aggByCurrency(reportTable, MC_CP_ENGAGEMENT)))
            .put(MC_CP_LANDING_PAGE_VIEW, ((reportTable) -> aggByCurrency(reportTable, MC_CP_LANDING_PAGE_VIEW)))
            .put(MC_CP_LEAD, ((reportTable) -> aggByCurrency(reportTable, MC_CP_LEAD)))
            .put(MC_CP_REACTION, ((reportTable) -> aggByCurrency(reportTable, MC_CP_REACTION)))
            .put(MC_CP_THRUPLAY, ((reportTable) -> aggByCurrency(reportTable, MC_CP_THRUPLAY)))
            .put(MC_CP_UNIQUE_CLICK, ((reportTable) -> aggByCurrency(reportTable, MC_CP_UNIQUE_CLICK)))
            .put(MC_CP_UNIQUE_OUTBOUND_CLICK, ((reportTable) -> aggByCurrency(reportTable, MC_CP_UNIQUE_OUTBOUND_CLICK)))
            .put(MC_CP_VIDEO_VIEW, ((reportTable) -> aggByCurrency(reportTable, MC_CP_VIDEO_VIEW)))
            .put(VIDEO_COMPLETION_RATE, ((reportTable) -> getDivideOfSums(reportTable, VIDEO_WATCHED100, IMPRESSIONS)))
            .put(VIDEO_THROUGH_RATE, ((reportTable) -> getDivideOfSums(reportTable, VIDEO_VIEWS, IMPRESSIONS)))

            //@formatter:on
            .build();

    public Function<DbTable, Expression> getSubQueryExpressionForMetric(
        AdsMetricEnum adsMetricEnum) {
        return METRIC_TO_SUBQUERY_SQL_EXPRESSION.get(adsMetricEnum);
    }

    public Function<DbTable, Expression> getRootQueryExpressionForMetric(
        AdsMetricEnum adsMetricEnum) {
        return METRIC_TO_ROOT_QUERY_SQL_EXPRESSION.get(adsMetricEnum);
    }

    public BiConsumer<AdMetrics, Object> getResponseMapper(AdsMetricEnum metric) {
        return METRIC_TO_RESPONSE_MAPPER.get(metric);
    }

}