package io.delphiplatform.api.v3.rdb.service.ads;

import com.google.common.collect.Lists;
import com.healthmarketscience.sqlbuilder.ComboCondition;
import com.healthmarketscience.sqlbuilder.CommonTableExpression;
import com.healthmarketscience.sqlbuilder.Condition;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.healthmarketscience.sqlbuilder.SetOperationQuery.Type;
import com.healthmarketscience.sqlbuilder.UnaryCondition;
import com.healthmarketscience.sqlbuilder.UnionQuery;
import com.healthmarketscience.sqlbuilder.dbspec.Table;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Collection;
import java.util.List;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

import io.delphiplatform.api.util.SelectQueryHelper;
import io.delphiplatform.api.v3.model.ads.LinkfireLink;
import io.delphiplatform.api.v3.rdb.repository.LinkfireDimLinkRepository;

import static io.delphiplatform.api.v3.constant.ColumnNameConstants.CAMPAIGN_ID;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.LINK_ID;
import static io.delphiplatform.api.v3.constant.ColumnNameConstants.PROJECT_ID;
import static io.delphiplatform.api.v3.constant.DecibelTableNames.LINKFIRE_LINK_CAMPAIGN;
import static io.delphiplatform.api.v3.constant.DecibelTableNames.LINKFIRE_LINK_PROJECT;
import static io.delphiplatform.api.v3.constant.ExpressionConstants.LINK_EXPRESSION_NAME;

@Service
public class LinkfireDimLinkService {

    private final LinkfireDimLinkRepository repository;

    public LinkfireDimLinkService(
        LinkfireDimLinkRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    public List<LinkfireLink> findAllById(Collection<UUID> ids) {
        return repository.findAllById(ids).stream()
            .map(LinkfireLink::from)
            .collect(Collectors.toList());
    }

    public CommonTableExpression findByProjectsAndCampaignsExpression(Table fromTable, Set<String> projectIds,
        Table projectIdsSourceTable) {
        SelectQueryHelper linkHelper = SelectQueryHelper.forDecibelSchema()
            .fromTable(LINKFIRE_LINK_CAMPAIGN)
            .addSelectColumns(LINK_ID)
            .done();

        DbColumn ltLinkIdColumn = linkHelper.getDbColumnForTable(LINKFIRE_LINK_CAMPAIGN, LINK_ID);
        DbColumn ltCampaignIdColumn = linkHelper.getDbColumnForTable(LINKFIRE_LINK_CAMPAIGN, CAMPAIGN_ID);

        SelectQuery linkTableSelectQuery = linkHelper
            .buildQuery()
            .setIsDistinct(true)
            .addCondition(
                ComboCondition.and(
                    UnaryCondition.isNotNull(ltLinkIdColumn),
                    new InCondition(ltCampaignIdColumn)
                        .addObject(
                            new SelectQuery()
                                .addFromTable(fromTable)
                                .addAllColumns()
                        )
                )
            );

        UnionQuery rootSelectQuery = new UnionQuery(Type.UNION, linkTableSelectQuery);

        boolean projectSourceFromCollection = projectIds != null && !projectIds.isEmpty();
        if (projectSourceFromCollection || projectIdsSourceTable != null) {
            SelectQueryHelper projectHelper = SelectQueryHelper.forDecibelSchema()
                .fromTable(LINKFIRE_LINK_PROJECT)
                .addSelectColumns(LINK_ID)
                .done();

            DbColumn ptLinkIdColumn = projectHelper.getDbColumnForTable(LINKFIRE_LINK_PROJECT, LINK_ID);
            DbColumn ptProjectIdColumn = projectHelper.getDbColumnForTable(LINKFIRE_LINK_PROJECT, PROJECT_ID);

            Condition projectSourceCondition = projectSourceFromCollection ?
                new InCondition(ptProjectIdColumn, projectIds) :
                new InCondition(ptProjectIdColumn, new SelectQuery()
                    .addFromTable(projectIdsSourceTable)
                    .addAllColumns());

            Condition condition = ComboCondition.and(
                UnaryCondition.isNotNull(ptLinkIdColumn),
                projectSourceCondition
            );

            SelectQuery projectTableSelectQuery = projectHelper
                .buildQuery()
                .setIsDistinct(true)
                .addCondition(condition);

            rootSelectQuery.addQueries(projectTableSelectQuery);
        }

        return new CommonTableExpression(LINK_EXPRESSION_NAME)
            .setQuery(rootSelectQuery);
    }
}