package mil.dds.anet.search.pg;

import mil.dds.anet.beans.UserActivity;
import mil.dds.anet.beans.search.UserActivitySearchQuery;
import mil.dds.anet.search.AbstractSearchQueryBuilder;
import mil.dds.anet.search.AbstractUserActivitySearcher;

public class PostgresqlUserActivitySearcher extends AbstractUserActivitySearcher {

  public PostgresqlUserActivitySearcher() {
    super(new PostgresqlSearchQueryBuilder<>("PostgresqlUserActivitySearch"));
  }

  @Override
  protected void buildQuery(final UserActivitySearchQuery query) {
    final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb =
        new PostgresqlSearchQueryBuilder<>("with-clause");
    switch (query.getSearchType()) {
      case PERSON:
        queryByPerson(query, withQb);
        break;
      case ORGANIZATION:
        queryByOrganization(query, withQb);
        break;
      case TOP_LEVEL_ORGANIZATION:
        queryByTopLevelOrganization(query, withQb);
        break;
    }
    qb.addWithClause(String.format("%1$s AS (%2$s)", WITH_CLAUSE_NAME, withQb.build()));
    qb.addSqlArgs(withQb.getSqlArgs());
    super.buildQuery(query);
  }

  private void queryByPerson(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("u.\"personUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause("EXISTS (SELECT uuid FROM people WHERE uuid = u.\"personUuid\")");
    }
    withQb.addGroupByClause("u.\"personUuid\"");
  }

  private void queryByOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("p.\"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addFromClause("LEFT JOIN positions p ON p.\"currentPersonUuid\" = u.\"personUuid\"");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause("p.\"organizationUuid\" IS NOT NULL");
    }
    withQb.addGroupByClause("p.\"organizationUuid\"");
  }

  private void queryByTopLevelOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.createWithClause(null, "parent_orgs", "organizations", "\"parentOrgUuid\"", false);
    withQb.addSelectClause("parent_orgs.parent_uuid AS \"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addFromClause("LEFT JOIN positions p ON p.\"currentPersonUuid\" = u.\"personUuid\"");
    withQb.addFromClause("LEFT JOIN parent_orgs ON parent_orgs.uuid = p.\"organizationUuid\"");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    final String parentQueryFmt =
        "(%s parent_orgs.parent_uuid IN (SELECT uuid FROM organizations WHERE \"parentOrgUuid\" IS NULL))";
    withQb.addWhereClause(String.format(parentQueryFmt,
        !query.getShowDeleted() ? "" : "parent_orgs.uuid IS NULL OR"));
    withQb.addGroupByClause("parent_orgs.parent_uuid");
  }
}

@@ -2016,33 +2016,33 @@
 ddSelectClause(%22
-p
+u
 .%5C%22organizationU
@@ -2114,105 +2114,8 @@
 %22);%0A
-    withQb.addFromClause(%22LEFT JOIN positions p ON p.%5C%22currentPersonUuid%5C%22 = u.%5C%22personUuid%5C%22%22);%0A
     
@@ -2411,26 +2411,89 @@
 WhereClause(
-%22p
+%0A          %22EXISTS (SELECT uuid FROM organizations WHERE uuid = u
 .%5C%22organizat
@@ -2505,20 +2505,9 @@
 id%5C%22
- IS NOT NULL
+)
 %22);%0A
@@ -2541,17 +2541,17 @@
 Clause(%22
-p
+u
 .%5C%22organ
@@ -2980,105 +2980,8 @@
 %22);%0A
-    withQb.addFromClause(%22LEFT JOIN positions p ON p.%5C%22currentPersonUuid%5C%22 = u.%5C%22personUuid%5C%22%22);%0A
     
@@ -3046,17 +3046,17 @@
 .uuid = 
-p
+u
 .%5C%22organ

Update query to use new organizationUuid column