package ru.micord.ervu.account_applications.dao;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Select;
import org.jooq.Table;
import org.jooq.TableField;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Repository;
import ru.micord.ervu.account_applications.db_beans.public_.Tables;
import ru.micord.ervu.account_applications.db_beans.public_.tables.Recruitment;
import ru.micord.ervu.account_applications.db_beans.public_.tables.UserApplicationList;
import ru.micord.ervu.account_applications.db_beans.public_.tables.records.RecruitmentRecord;

/**
 * @author Adel Kalimullin
 */
@Repository
public class RecruitmentDao extends AbstractDataDao<RecruitmentRecord> {
  private static final String RECRUITMENT_HIERARCHY_NAME = "recruitment_hierarchy";
  private static final String DEPTH_ALIAS = "depth";
  private static final String IDM_ID_FIELD = "idm_id";
  protected RecruitmentDao(DSLContext dsl) {
    super(dsl);
  }

  public List<String> getRecruitmentIdsWithParentByDomainId(String domainId) {
    return getRecruitmentIdmIdsByParentId(domainId, Recruitment.RECRUITMENT.IDM_ID);
  }

  public List<String> getRecruitmentIdsWithParentByRecruitmentId(String recruitmentId) {
    return getRecruitmentIdmIdsByParentId(recruitmentId, Recruitment.RECRUITMENT.ID);
  }

  private List<String> getRecruitmentIdmIdsByParentId(String value, TableField field) {
    var recruitmentHierarchy = DSL.name(RECRUITMENT_HIERARCHY_NAME);

    var cte = dsl.withRecursive(recruitmentHierarchy).as(
        dsl.select(
                Recruitment.RECRUITMENT.IDM_ID,
                DSL.inline(1).as(DEPTH_ALIAS)
            )
            .from(getTable())
            .where(DSL.field(field).eq(value))
            .unionAll(
                dsl.select(
                        Recruitment.RECRUITMENT.IDM_ID,
                        DSL.field(DSL.name(RECRUITMENT_HIERARCHY_NAME, DEPTH_ALIAS), Integer.class).add(1)
                    )
                    .from(getTable())
                    .join(DSL.table(recruitmentHierarchy))
                    .on(Recruitment.RECRUITMENT.PARENT_ID.eq(
                            DSL.field(DSL.name(RECRUITMENT_HIERARCHY_NAME, IDM_ID_FIELD), String.class)
                        )
                    )
            )
    );

    return cte
        .select(DSL.field(DSL.name(RECRUITMENT_HIERARCHY_NAME, IDM_ID_FIELD), String.class))
        .from(DSL.table(recruitmentHierarchy))
        .orderBy(DSL.field(DSL.name(RECRUITMENT_HIERARCHY_NAME, DEPTH_ALIAS)).asc())
        .fetchInto(String.class);
  }

  public List<String> getAllRecruitmentIds() {
    return dsl.select(Recruitment.RECRUITMENT.IDM_ID)
        .from(Recruitment.RECRUITMENT)
        .fetch(Recruitment.RECRUITMENT.IDM_ID);
  }

  public Optional<UUID> getIdByIdmId(String idmId) {
    return getValueByField(Recruitment.RECRUITMENT.ID, Recruitment.RECRUITMENT.IDM_ID, idmId);
  }

  public void setActiveStatus(List<String> ids, boolean active) {
    setFieldWhereIn(
        Recruitment.RECRUITMENT.ACTIVE, active,
        Recruitment.RECRUITMENT.IDM_ID, ids
    );
  }

  public boolean exists(Long appNumber, String domainId, boolean checkParents) {
    Recruitment orgTable = Recruitment.RECRUITMENT;
    UserApplicationList appTable = UserApplicationList.USER_APPLICATION_LIST;
    String parentOrg = "parent_org";
    Name parentOrgName = DSL.name(parentOrg);
    Table<?> parentOrgTable = DSL.table(parentOrgName);
    Field<String> parentIdmId = DSL.field(DSL.name(parentOrg, orgTable.IDM_ID.getName()),
        String.class
    );
    Field<String> parentParentId = DSL.field(DSL.name(parentOrg, orgTable.PARENT_ID.getName()),
        String.class
    );
    Select<?> select = checkParents
                       ? dsl.withRecursive(parentOrgName)
                           .as(dsl.select(orgTable.IDM_ID, orgTable.PARENT_ID)
                               .from(orgTable)
                               .where(orgTable.IDM_ID.eq(
                                   dsl.select(orgTable.IDM_ID)
                                       .from(orgTable)
                                       .join(appTable).on(appTable.RECRUITMENT_ID.eq(orgTable.ID))
                                       .where(appTable.NUMBER_APP.eq(appNumber))))
                                   .unionAll(dsl.select(orgTable.IDM_ID, orgTable.PARENT_ID)
                                       .from(orgTable)
                                       .join(parentOrgTable).on(orgTable.IDM_ID.eq(parentParentId))))
                           .select(parentIdmId)
                           .from(parentOrgTable)
                           .where(parentIdmId.eq(domainId))
                       : dsl.select(orgTable.IDM_ID)
                           .from(orgTable)
                           .join(appTable)
                           .on(appTable.RECRUITMENT_ID.eq(orgTable.ID))
                           .where(appTable.NUMBER_APP.eq(appNumber)
                               .and(orgTable.IDM_ID.eq(domainId)));

    return dsl.fetchExists(select);
  }

  @Override
  protected Table<RecruitmentRecord> getTable() {
    return Tables.RECRUITMENT;
  }
}


