package io.delphi.qa.auto.awsmfrw.models.db.snowflake;

import com.beust.jcommander.internal.Lists;
import io.delphi.qa.auto.awsmfrw.common_baby.EnSnQueryExecutionStatusItems;
import io.delphi.qa.auto.awsmfrw.common_baby.IsaCommonMethods;
import io.delphi.qa.auto.awsmfrw.models.db.BsSqlTable;
import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.DslSql;
import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.DslSqlSelect;
import io.delphi.qa.auto.awsmfrw.models.db.sql.dsl.InterfaceDslSqlQuery;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_ads_data.exp.*;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_ads_data.raw_facebook_ads_account.MlSn_AdSetHistory;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_ads_data.sys.MlSn_FacebookAdsAdSet;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.ad_data.MlSn_VFacebookAdSetHistory;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.ads_data_consolidated.*;

import java.util.List;

public interface ICanFetchSnowflakeRlsFacebookData {

  String STR_DSP_NAME = "FACEBOOK";
  String[] TC1_PARENT_REP_OWNER = new String[] {"0226"};
  String[] TC2_PARENT_REP_OWNER = new String[] {"E008"};
  String[] TC5_PARENT_REP_OWNERS = new String[] {"D179", "0215"};
  String[] TC6_PARENT_REP_OWNERS = new String[] {"C322"};
  String[] TC7_PARENT_REP_OWNERS = new String[] {"C283", "E008"};
  String TC3_DSP_ID = "192227596";
  String TC6_DSP_ID = "9424762860";
  String TC7_DSP_ID = "9424762860";

  List<? extends BsSqlTable<?>> RLS_VIEWS =
      Lists.newArrayList(
          new MlSn_VFacebookAgeGenderDailyReport(),
          new MlSn_VFacebookAgeGenderDailyReportActions(),
          new MlSn_VFacebookAgeGenderDailyReportConversions(),
          new MlSn_VFacebookAgeGenderDailyReportUniqueActions(),
          new MlSn_VFacebookCountryDailyReport(),
          new MlSn_VFacebookCountryDailyReportActions(),
          new MlSn_VFacebookCountryDailyReportConversions(),
          new MlSn_VFacebookCountryDailyReportUniqueActions(),
          new MlSn_VFacebookPublisherPlatformDailyReport(),
          new MlSn_VFacebookPublisherPlatformDailyReportActions(),
          new MlSn_VFacebookPublisherPlatformDailyReportConversions(),
          new MlSn_VFacebookPublisherPlatformDailyReportUniqueActions());
  List<? extends BsSqlTable<?>> RLS_TABLES =
      Lists.newArrayList(
          new MlSn_FacebookAgeGenderDailyReport(),
          new MlSn_FacebookAgeGenderDailyReportActions(),
          new MlSn_FacebookAgeGenderDailyReportConversions(),
          new MlSn_FacebookAgeGenderDailyReportUniqueActions(),
          new MlSn_FacebookCountryDailyReport(),
          new MlSn_FacebookCountryDailyReportActions(),
          new MlSn_FacebookCountryDailyReportConversions(),
          new MlSn_FacebookCountryDailyReportUniqueActions(),
          new MlSn_FacebookPublisherPlatformDailyReport(),
          new MlSn_FacebookPublisherPlatformDailyReportActions(),
          new MlSn_FacebookPublisherPlatformDailyReportConversions(),
          new MlSn_FacebookPublisherPlatformDailyReportUniqueActions());
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_VIEWS = buildQueryPatternRlsViews(RLS_VIEWS);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_AD_SET_IDS =
      DslSql.select()
          .columns("AD_SET_ID")
          .from(new MlSn_FacebookAdsAdSet().locator())
          .where("ACCOUNT_ID in ('{account_id}')")
          .close();
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES = buildQueryPatternRlsTables(RLS_TABLES);
  List<String> SPECIFIC_VIEWS =
      Lists.newArrayList(
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTION_VALUES"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_CONVERSION"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_THRUPLAY"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_UNIQUE_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_COST_PER_UNIQUE_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS_CTR"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_30_SEC_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_AVG_TIME_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_100_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_25_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_50_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_75_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_CAMPAIGN_NAMING_CONVENTION"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_ACTION_VALUES"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_CONVERSION"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_THRUPLAY"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_UNIQUE_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_COST_PER_UNIQUE_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_OUTBOUND_CLICKS_CTR"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS_CTR"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_30_SEC_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_AVG_TIME_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_PLAY_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_P_100_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_P_25_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_P_50_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_P_75_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT_ACTION_VALUES"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT_COST_PER_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTION_VALUES"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_CONVERSION"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_THRUPLAY"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_UNIQUE_ACTION_TYPE"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_COST_PER_UNIQUE_OUTBOUND_CLICK"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_OUTBOUND_CLICKS_CTR"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS_CTR"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_30_SEC_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_AVG_TIME_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_PLAY_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_P_100_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_P_25_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_P_50_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_P_75_WATCHED_ACTIONS"),
          ("DELPHI_EXPLORATION.AD_DATA.V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS"));
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_SPECIFIC_VIEWS = buildQueryPatternRlsSpecificViews(SPECIFIC_VIEWS);

  static InterfaceDslSqlQuery buildQueryPatternRlsSpecificViews(List<String> specificViews) {
    DslSqlSelect base = DslSql.select(DslSql.constants(InterfaceDslSqlQuery.TRUE));
    specificViews.forEach(
        x ->
            base.and(
                DslSql.caseWhen()
                    .exists(DslSql.select().columns().from(IsaCommonMethods.formatEnv(x)).close())
                    .then(EnSnQueryExecutionStatusItems.TRUE.name())
                    .elseEnd(String.format("'%s'", x))
                    .queryText()));
    return base;
  }

  static InterfaceDslSqlQuery buildQueryPatternRlsTables(List<? extends BsSqlTable<?>> tables) {
    DslSqlSelect base = SfRlsDataInterface.getBase();
    tables.forEach(
        view ->
            base.unionAll(
                    DslSql.select()
                        .columns(DslSql.functions().count())
                        .from(view.locator())
                        .where("ADSET_ID in ('{adset_id}')")
                        .queryText())
                .close()
                .queryText());
    base.unionAll(
        DslSql.select()
            .columns(DslSql.functions().count())
            .from(new MlSn_AdSetHistory().locator())
            .where("ADSET_SOURCE_ID in ('{adset_id}')")
            .close()
            .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }

  static InterfaceDslSqlQuery buildQueryPatternRlsViews(List<? extends BsSqlTable<?>> views) {
    DslSqlSelect base = SfRlsDataInterface.getBase();
    views.forEach(
        view ->
            base.unionAll(
                    DslSql.select()
                        .columns(DslSql.functions().count())
                        .from(view.locator())
                        .where("ADSET_ID in ('{adset_id}')")
                        .queryText())
                .close()
                .queryText());
    base.unionAll(
        DslSql.select()
            .columns(DslSql.functions().count())
            .from(new MlSn_VFacebookAdSetHistory().locator())
            .where("ADSET_SOURCE_ID in ('{adset_id}')")
            .close()
            .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }
}