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_exploration.exp.*;
import io.delphi.qa.auto.awsmfrw.models.db.sql.snowflake._delphi_exploration.main.*;

import java.util.List;

public interface ICanFetchSnowflakeRlsAeData extends SfRlsDataInterface {

  String STR_DSP_NAME = "AE";
  String BUSINESS_UNIT_AR = "AR";
  String BUSINESS_UNIT_US = "US";
  String LICENSOR_SME = "sme";
  String LICENSOR_THEORCHARD = "theorchard";
  List<? extends BsSqlTable<?>> RLS_TABLES =
      Lists.newArrayList(
          new MlSn_AeActivitiesFeed(),
          new MlSn_AeActivity(),
          new MlSn_AeBrand(),
          new MlSn_AeMembersDomains(),
          new MlSn_AeMembersLogins(),
          new MlSn_AeMembersOptIns(),
          new MlSn_AeMembersVisitTotals(),
          new MlSn_AeSegment()
          //          new MlSn_AeActivitiesFeedPersonal(),
          //          new MlSn_AeMember(),
          //          new MlSn_AeMemberPersonal(),
          //          new MlSn_AeMembersProfiles(),
          //          new MlSn_AeMembersServices(),
          );
  List<? extends BsSqlTable<?>> RLS_VIEWS =
      Lists.newArrayList(
          new MlSn_VAeActivitiesFeed(),
          new MlSn_VAeActivity(),
          new MlSn_VAeBrand(),
          new MlSn_VAeMembersDomains(),
          new MlSn_VAeMembersLogins(),
          new MlSn_VAeMembersOptIns(),
          new MlSn_VAeMembersVisitTotals(),
          new MlSn_VAeSegment()
          //          new MlSn_VAeActivitiesFeedPersonal(),
          //          new MlSn_VAeMember(),
          //          new MlSn_VAeMemberPersonal(),
          //          new MlSn_VAeMembersProfiles(),
          //          new MlSn_VAeMembersServices()
          );
  String AND_REPORT_LICENSOR_THEORCHARD =
      String.format("report_licensor='%s'", LICENSOR_THEORCHARD);
  String AND_REPORT_LICENSOR_SME = String.format("report_licensor='%s'", LICENSOR_SME);
  String WHERE_BUSINESS_UNIT_NAME_EQ_SME =
      String.format("business_unit_name='%s'", BUSINESS_UNIT_AR);
  String WHERE_BUSINESS_UNIT_NAME_EQ_THEORCHARD =
      String.format("business_unit_name='%s'", BUSINESS_UNIT_US);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_VIEWS = buildQueryPatternRlsViews(RLS_VIEWS);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES_TC0 = buildQueryPatternRlsTablesTc0(RLS_TABLES);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES_TC3 = buildQueryPatternRlsTables4Tc3(RLS_TABLES);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_SPECIFIC_VIEWS =
      DslSql.select().columns().from(new MlSn_VAeBusinessUnit().locator()).close();
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_SPECIFIC_TABLES =
      DslSql.select().columns().from(new MlSn_VAeBusinessUnit().locator()).close();
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES_TC4 = buildQueryPatternRlsTablesTc4(RLS_TABLES);

  static InterfaceDslSqlQuery buildQueryPatternRlsTables4Tc3(List<? extends BsSqlTable<?>> tables) {
    DslSqlSelect base = SfRlsDataInterface.getBase();
    tables.forEach(
        view ->
            base.unionAll(
                    DslSql.select()
                        .columns(DslSql.functions().count())
                        .from(view.locator())
                        .where(WHERE_BUSINESS_UNIT_NAME_EQ_SME)
                        .close()
                        .queryText())
                .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }

  static InterfaceDslSqlQuery buildQueryPatternRlsTablesTc0(List<? extends BsSqlTable<?>> tables) {
    DslSqlSelect base = SfRlsDataInterface.getBase();
    tables.forEach(
        view ->
            base.unionAll(
                    DslSql.select()
                        .columns(DslSql.functions().count())
                        .from(view.locator())
                        .where(WHERE_BUSINESS_UNIT_NAME_EQ_SME)
                        .and(AND_REPORT_LICENSOR_SME)
                        .unionAll(
                            DslSql.select()
                                .columns(DslSql.functions().count())
                                .from(view.locator())
                                .where(WHERE_BUSINESS_UNIT_NAME_EQ_THEORCHARD)
                                .and(AND_REPORT_LICENSOR_THEORCHARD)
                                .close()
                                .queryText())
                        .close()
                        .queryText())
                .queryText());
    return DslSql.select()
        .columns(DslSql.functions().sum(InterfaceDslSqlQuery.RESULT))
        .from(base.close().queryText())
        .close();
  }

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