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

import com.beust.jcommander.internal.Lists;
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_exploration.ad_data.*;

import java.util.List;

public interface ICanFetchSnowflakeRlsTiktokData extends SfRlsDataInterface {

  String STR_DSP_NAME = "TIKTOK";
  String[] TC1_PARENT_REP_OWNER = new String[] {"0031"};
  String[] TC6_PARENT_REP_OWNERS = new String[] {"0031"};
  String TC6_DSP_ID = "1636091147597829";
  String TC3_DSP_ID = "7074986117749145601";
  String TC4_DSP_ID = "7042433489240670210";
  String TC5_DSP_ID = "7025976193434288130";
  String TC7_DSP_ID = "7025666915741794306";
  String TC8_DSP_ID = "6861887201337147397";
  String TC9_DSP_ID = "7004722611523469313";

  //list of views generates here
  List<? extends BsSqlTable<?>> RLS_VIEWS =
      Lists.newArrayList(
              new MlSn_VTiktokAdsCampaignAgeGenderReport(),
              new MlSn_VTiktokAdsCampaignCountryReport(),
              new MlSn_VTiktokAdsCampaignHistory(),
              new MlSn_VTiktokAdsCampaignPlatformReport(),
              new MlSn_VTiktokAdsCampaignReportDaily(),
              new MlSn_VTiktokAdSetAgeGenderReport(),
              new MlSn_VTiktokAdSetCountryReport(),
              new MlSn_VTiktokAdSetHistory(),
              new MlSn_VTiktokAdSetPlatformReport(),
              new MlSn_VTiktokAdSetReportDaily());


  List<? extends BsSqlTable<?>> RLS_TABLES =
      Lists.newArrayList(
              new MlSn_TiktokAdsCampaignAgeGenderReport(),
              new MlSn_TiktokAdsCampaignCountryReport(),
              new MlSn_TiktokAdsCampaignHistory(),
              new MlSn_TiktokAdsCampaignPlatformReport(),
              new MlSn_TiktokAdsCampaignReportDaily(),
              new MlSn_TiktokAdSetAgeGenderReport(),
              new MlSn_TiktokAdSetCountryReport(),
              new MlSn_TiktokAdSetHistory(),
              new MlSn_TiktokAdSetPlatformReport(),
              new MlSn_TiktokAdSetReportDaily());
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_VIEWS = buildQueryPatternRlsViews(RLS_VIEWS);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_VIEWS_EXPLORATION_USER = buildQueryPatternRlsViewsExplorationUser(RLS_VIEWS);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES = buildQueryPatternRlsTables(RLS_TABLES);

  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("ACCOUNT_ID in ('{customer_id}')")
                        .close()
                        .queryText())
                .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }

  static InterfaceDslSqlQuery buildQueryPatternRlsViewsExplorationUser(List<? extends BsSqlTable<?>> views) {
    DslSqlSelect base = SfRlsDataInterface.getBase();
    views.forEach(
        view ->
            base.unionAll(
                    DslSql.select()
                        .columns(DslSql.functions().count())
                        .from(view.locator())
                        .where("ACCOUNT_ID in ('{customer_id}')")
                        .queryText())
                .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("ACCOUNT_ID in ('{customer_id}')")
                        .queryText())
                .close()
                .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }
}