import static de.hybris.platform.core.model.order.AbstractOrderEntryModel.ORDER;
import static de.hybris.platform.core.model.order.AbstractOrderEntryModel.PRODUCT;
import static de.hybris.platform.core.model.order.AbstractOrderModel.USER;

import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.Conditions.braces;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.Conditions.condition;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.FlexibleSearchQueryBuilder.select;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.FlexibleSearchQueryBuilder.selectFrom;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.FromClauseElements.table;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.ParameterlessConditionType.IS_NOT_NULL;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.ParameterlessConditionType.IS_NULL;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.TwoParameterConditionType.BETWEEN;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.RegularParameterConditionType.IS_EQUAL_TO;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.RegularParameterConditionType.IS_GREATER_THAN;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.RegularParameterConditionType.LIKE;
import static org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.SqlFunctions.count;

import de.hybris.platform.catalog.model.CatalogUnawareMediaModel;
import de.hybris.platform.catalog.model.ProductReferenceModel;
import de.hybris.platform.category.constants.CategoryConstants;
import de.hybris.platform.category.model.CategoryModel;
import de.hybris.platform.core.model.order.OrderEntryModel;
import de.hybris.platform.core.model.order.OrderModel;
import de.hybris.platform.core.model.product.ProductModel;
import de.hybris.platform.core.model.user.UserModel;
import de.hybris.platform.servicelayer.search.FlexibleSearchQuery;
import de.hybris.platform.variants.model.VariantProductModel;

import org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.AbstractFieldCondition;
import org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.Alias;
import org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.FieldWithType;
import org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.FunctionWithType;
import org.bitbucket.andriichukandrii.hybris.flexiblesearchbuilder.JoinOnElement;


final FlexibleSearchQuery query1 = 
    selectFrom(CatalogUnawareMediaModel.class)
    .where(
        condition(CatalogUnawareMediaModel.CODE, IS_EQUAL_TO, "someLogoCode"))
    .build();

final AbstractFieldCondition nameAndDescriptionNonNull = 
    condition(ProductModel.NAME, IS_NOT_NULL)
    .and()
    .condition(ProductModel.DESCRIPTION, IS_NOT_NULL);

final FlexibleSearchQuery query2 =
    selectFrom(ProductModel.class)
    .where(
        condition(ProductModel.CODE, LIKE, "p%")
        .and()
        .condition(nameAndDescriptionNonNull)
    )
    .orderByAsc(ProductModel.NAME)
    .build();

final Alias p = new Alias("p");
final Alias v = new Alias("v");
final FlexibleSearchQuery query3 =
    select(p)
    .from(
        table(ProductModel.class).as(p)
        join(VariantProductModel.class).as(v)
            .on(p.pk(), IS_EQUAL_TO, v.field(VariantProductModel.BASEPRODUCT))
    )
    .where(
        condition(v.field(VariantProductModel.OFFLINEDATE), IS_GREATER_THAN, timeService.getCurrentTime())
    )
    .build();

final FlexibleSearchQuery query4 =
    select(
        FieldWithType.of(ProductModel.NAME, String.class),
        FieldWithType.of(ProductModel.DESCRIPTION, String.class),
        FieldWithType.of(ProductModel.PK, Long.class)
    )
    .from(
        table(ProductModel.class)
    )
    .where(
        braces(
            condition(ProductModel.SUMMARY, IS_NULL)
            .or()
            .condition(ProductModel.NAME, IS_NOT_NULL)
        )
        .and()
        .condition(ProductModel.PK, BETWEEN, 8796093054977L, 8796193054977L)
    )
    .build();

final UserModel user = userService.getAnonymousUser();
final CategoryModel category = new CategoryModel();//just for demonstration purposes

final Alias r = new Alias("r");
final Alias e = new Alias("e");
final Alias o = new Alias("o");
final Alias c2p = new Alias("c2p");
final Alias c = new Alias("c");

final JoinOnElement joinTables =
    table(ProductModel.class).as(p)
    .leftJoin(ProductReferenceModel.class).as(r)
        .on(p.pk(), r.target())
    .leftJoin(OrderEntryModel.class).as(e)
        .on(r.source(), e.field(PRODUCT))
    .leftJoin(OrderModel.class).as(o)
        .on(o.pk(), e.field(ORDER))
    .leftJoin(CategoryConstants.Relations.CATEGORYPRODUCTRELATION).as(c2p)
        .on(r.source(), c2p.target())
    .leftJoin(CategoryModel.class).as(c)
        .on(c.pk(), c2p.source());

final FlexibleSearchQuery query5 =
    select(p)
    .from(joinTables)
    .where(
        condition(o.field(USER), IS_EQUAL_TO, user)
        .and()
        .condition(c.pk(), IS_EQUAL_TO, category)
    )
    .orderByAsc(p.field(ProductModel.CODE))
    .build();

final FlexibleSearchQuery query6 = 
    select(
        FunctionWithType.of(count(o.field(OrderModel.CODE)), Long.class),
        FieldWithType.of(p.field(ProductModel.CODE), String.class)
    )
    .from(
        table(ProductModel.class).as(p)
        .join(OrderEntryModel.class).as(e)
            .on(p.pk(), e.field(OrderEntryModel.PRODUCT))
        .join(OrderModel.class).as(o)
            .on(o.pk(), e.field(OrderEntryModel.ORDER))
    )
    .groupBy(p.field(ProductModel.CODE))
    .orderByDesc(count(o.field(OrderModel.CODE)))
    .build();