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

import java.util.List;

public interface ICanFetchSnowflakeRlsLinkfireData extends SfRlsDataInterface {

  String STR_DSP_NAME = "LINKFIREREPORTING";
  String TC3_DSP_ID = "6EF8D584-4C47-11E6-9FD0-066C3E7A8751";

  List<? extends BsSqlTable<?>> RLS_VIEWS = Lists.newArrayList(new MlSn_VLinkfireRawData());
  List<? extends BsSqlTable<?>> RLS_TABLES = Lists.newArrayList(new MlSn_LinkfireRawData());
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_VIEWS = buildQueryPatternRlsViews(RLS_VIEWS);
  InterfaceDslSqlQuery QUERY_PATTERN_RLS_TABLES = buildQueryPatternRlsTables(RLS_TABLES);
  String QUERY_PATTERN_RLS_DSP_IDS =
      DslSql.select().distinct("org_id").from(new MlSn_LinkfireRawData().locator()).queryText();

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