package io.delphiplatform.api.util;

import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.ComboExpression;
import com.healthmarketscience.sqlbuilder.ComboExpression.Op;
import com.healthmarketscience.sqlbuilder.CommonTableExpression;
import com.healthmarketscience.sqlbuilder.Condition;
import com.healthmarketscience.sqlbuilder.Expression;
import com.healthmarketscience.sqlbuilder.FunctionCall;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbFunction;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;

import java.util.Collection;
import java.util.List;
import java.util.function.Supplier;

import io.delphiplatform.api.v3.constant.ColumnNameConstants;
import io.delphiplatform.api.v3.constant.DbFunctionNamesConstants;
import io.delphiplatform.api.v3.constant.DbSchemaConstants;
import io.delphiplatform.api.v3.rdb.service.ads.AdsMetricEnum;

public abstract class SqlBuilderUtils {

    /**
     * Calculates cost per 1000 of column for the aggregation group.
     *
     * @param column property name to calculate CPM, like "impressions" to calculate CPM Impressions
     */
    public static Expression getSafeCPM(DbTable reportTable, AdsMetricEnum column) {
        return getSafeCPM(reportTable, column.getAlias());
    }

    public static Expression getSafeCPM(DbTable reportTable, String column) {
        return new ComboExpression(Op.DIVIDE,
            FunctionCall.sum().addColumnParams(reportTable.addColumn(ColumnNameConstants.SPEND)),
            notZero(new ComboExpression(Op.DIVIDE,
                FunctionCall.sum().addColumnParams(reportTable.addColumn(column)),
                1000
            ))
        );
    }

    public static FunctionCall getSum(DbTable reportTable, AdsMetricEnum column) {
        return getSum(reportTable, column.getAlias());
    }

    public static FunctionCall getSum(DbTable reportTable, String column) {
        return FunctionCall.sum().addColumnParams(
            new DbColumn(reportTable, column, null));
    }

    public static Expression getVideoWatched(DbTable reportTable, AdsMetricEnum column) {
        return new ComboExpression(Op.MULTIPLY, 100,
            getDivideOfSums(reportTable, column.getAlias(), AdsMetricEnum.VIDEO_WATCHED_BASE.getAlias()));
    }

    public static FunctionCall getSum(DbTable reportTable, List<String> coalesceColumns) {
        DbColumn[] dbColumns = coalesceColumns.stream().map(reportTable::addColumn).toArray(DbColumn[]::new);
        return FunctionCall.sum().addCustomParams(coalesce(dbColumns));
    }

    public static ComboExpression getDivideOfSums(DbTable table, String columnA, String columnB) {
        return new ComboExpression(Op.DIVIDE, getSum(table, columnA),
            notZero(getSum(table, columnB)));
    }

    private static Expression notZero(Expression expression) {
        return new FunctionCall(new DbFunction(null, DbFunctionNamesConstants.NULLIF)).addCustomParams(expression, 0);
    }

    public static ComboExpression getDivideOfSums(DbTable table, AdsMetricEnum columnA, AdsMetricEnum columnB) {
        return getDivideOfSums(table, columnA.getAlias(), columnB.getAlias());
    }

    public static ComboExpression getCostPer(DbTable table, AdsMetricEnum column) {
        return getCostPer(table, column.getAlias());
    }

    public static ComboExpression getCostPer(DbTable table, String column) {
        return getDivideOfSums(table, ColumnNameConstants.SPEND, column);
    }

    public static Expression aggByCurrency(DbTable table, AdsMetricEnum column) {
        return aggByCurrency(table, column.getAlias());
    }

    public static Expression aggByCurrency(DbTable table, String column) {
        return new FunctionCall(new DbFunction(null, DbFunctionNamesConstants.JSONB_OBJECT_AGG)).addColumnParams(
            table.addColumn(ColumnNameConstants.CURRENCY),
            table.addColumn(column));
    }

    public static DbSchema getPublicSchema() {
        return new DbSchema(getDbSpec(), DbSchemaConstants.PUBLIC);
    }

    public static DbSchema getLinkfireSchema() {
        return new DbSchema(getDbSpec(), DbSchemaConstants.LINKFIRE);
    }

    public static DbSchema getDecibelSchema() {
        return new DbSchema(getDbSpec(), DbSchemaConstants.DECIBEL);
    }

    private static DbSpec getDbSpec() {
        return new DbSpec();
    }

    public static FunctionCall coalesce(DbColumn... columns) {
        return new FunctionCall(new DbFunction(null, DbFunctionNamesConstants.COALESCE)).addColumnParams(columns);
    }

    public static FunctionCall coalesce(Object... columns) {
        return new FunctionCall(new DbFunction(null, DbFunctionNamesConstants.COALESCE)).addCustomParams(columns);
    }

    public static InCondition inConditionOrNullIfEmpty(Object dbColumn, Collection<?> values) {
        if (CollectionUtils.isEmpty(values)) {
            return null;
        }
        return new InCondition(dbColumn, values);
    }

    public static InCondition inConditionForCte(Object dbColumn, CommonTableExpression cte) {
        return new InCondition(dbColumn)
            .addObject(new SelectQuery()
                .addFromTable(cte.getTable())
                .addAllColumns()
            );
    }

    public static Condition itemCollectionCondition(Collection items, Supplier<DbTable> tableSupplier,
        String dbColumnName) {
        if (items == null || items.isEmpty()) {
            return null;
        }
        return new InCondition(new DbColumn(tableSupplier.get(), dbColumnName, null), items);
    }

    public static Condition booleanItemCondition(Boolean item, Supplier<DbTable> tableSupplier, String dbColumnName) {
        if (item == null) {
            return null;
        }
        return BinaryCondition.equalTo(new DbColumn(tableSupplier.get(), dbColumnName, null), item.toString());
    }
}