package com.google.sps.utilities;

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.sps.data.User;
import com.google.sps.data.VolunteeringOpportunity;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

public class DatabaseWrapper {
  private String instanceId;
  private String databaseId;
  private static final String USER_TABLE = "Users";
  private static final String VOLUNTEERING_OPPORTUNITY_TABLE = "VolunteeringOpportunity";

  public DatabaseWrapper(String instanceId, String databaseId) {
    this.instanceId = instanceId;
    this.databaseId = databaseId;
  }

  /**
   * Given a user, insert a row with all available fields into the DB
   *
   * @param user the user to be inserted; user's ID field should not exist in DB
   */
  public void insertUser(User user) {
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();
    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);

    List<Mutation> mutations = getMutationsFromBuilder(newInsertBuilderFromUser(), user);
    dbClient.write(mutations);
    spanner.close();
  }

  /**
   * Given a user, insert a row with all available fields into the DB
   *
   * @param user the user to be updated; user's ID field should already exist in DB
   */
  public void updateUser(User user) {
    // Given a user, update its corresponding row's new fields in DB
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();
    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);

    List<Mutation> mutations = getMutationsFromBuilder(newUpdateBuilderFromUser(), user);
    dbClient.write(mutations);
    spanner.close();
  }

  private static Mutation.WriteBuilder newInsertBuilderFromUser() {
    return Mutation.newInsertBuilder(USER_TABLE);
  }

  private static Mutation.WriteBuilder newUpdateBuilderFromUser() {
    return Mutation.newUpdateBuilder(USER_TABLE);
  }

  private static List<Mutation> getMutationsFromBuilder(Mutation.WriteBuilder builder, User user) {
    List<Mutation> mutations = new ArrayList<>();
    builder
        .set("UserID")
        .to(user.getUserId())
        .set("Name")
        .to(user.getName())
        .set("Email")
        .to(user.getEmail())
        .set("Interests")
        .toStringArray(user.getInterests())
        .set("Skills")
        .toStringArray(user.getSkills())
        .set("EventsHosting")
        .toInt64Array(user.getEventsHostingIds())
        .set("EventsParticipating")
        .toInt64Array(user.getEventsParticipatingIds())
        .set("EventsVolunteering")
        .toInt64Array(user.getEventsVolunteeringIds());
    mutations.add(builder.build());
    return mutations;
  }

  /**
   * Given a volunteering opportunity, insert a row with all available fields into the DB
   *
   * @param opportunity the volunteering opportunity to be inserted
   */
  public void insertVolunteeringOpportunity(VolunteeringOpportunity opportunity) {
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();
    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);

    List<Mutation> mutations =
        getMutationsFromBuilder(newInsertBuilderFromVolunteeringOpportunity(), opportunity);
    dbClient.write(mutations);
    spanner.close();
  }

  private static Mutation.WriteBuilder newInsertBuilderFromVolunteeringOpportunity() {
    return Mutation.newInsertBuilder(VOLUNTEERING_OPPORTUNITY_TABLE);
  }

  private static List<Mutation> getMutationsFromBuilder(
      Mutation.WriteBuilder builder, VolunteeringOpportunity opportunity) {
    List<Mutation> mutations = new ArrayList<>();
    builder
        .set("VolunteeringOpportunityID")
        .to(opportunity.getOpportunityId())
        .set("EventID")
        .to(opportunity.getEventId())
        .set("Name")
        .to(opportunity.getName())
        .set("NumSpotsLeft")
        .to(opportunity.getNumSpotsLeft())
        .set("RequiredSkills")
        .toStringArray(opportunity.getRequiredSkills());
    mutations.add(builder.build());
    return mutations;
  }

  /**
   * Given an eventId, retrieve all volunteering opportunities for that eventId
   *
   * @param eventId eventId for the event to retrieve volunteering opportunities for
   * @return volunteering opportunties with given eventId
   */
  public Set<VolunteeringOpportunity> getVolunteeringOpportunitiesByEventId(String eventId) {
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();
    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);

    Set<VolunteeringOpportunity> results = new HashSet<VolunteeringOpportunity>();
    Statement statement =
        Statement.of(
            String.format(
                "SELECT VolunteeringOpportunityID, Name, NumSpotsLeft, RequiredSkills FROM"
                    + " VolunteeringOpportunity WHERE EventID=\"%s\"",
                eventId));
    try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
      while (resultSet.next()) {
        String opportunityId = resultSet.getString(0);
        String name = resultSet.getString(1);
        long numSpotsLeft = resultSet.getLong(2);
        Set<String> requiredSkills =
            resultSet.getStringList(3).stream().collect(Collectors.toSet());
        results.add(
            new VolunteeringOpportunity.Builder(eventId, name, numSpotsLeft)
                .setOpportunityId(opportunityId)
                .setRequiredSkills(requiredSkills)
                .build());
      }
    }
    spanner.close();
    return results;
  }
}

@@ -4717,1606 +4717,7 @@
 %0A  %7D
-%0A%0A  /**%0A   * Given an eventId, retrieve all volunteering opportunities for that eventId%0A   *%0A   * @param eventId eventId for the event to retrieve volunteering opportunities for%0A   * @return volunteering opportunties with given eventId%0A   */%0A  public Set%3CVolunteeringOpportunity%3E getVolunteeringOpportunitiesByEventId(String eventId) %7B%0A    SpannerOptions options = SpannerOptions.newBuilder().build();%0A    Spanner spanner = options.getService();%0A    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);%0A    DatabaseClient dbClient = spanner.getDatabaseClient(db);%0A%0A    Set%3CVolunteeringOpportunity%3E results = new HashSet%3CVolunteeringOpportunity%3E();%0A    Statement statement =%0A        Statement.of(%0A            String.format(%0A                %22SELECT VolunteeringOpportunityID, Name, NumSpotsLeft, RequiredSkills FROM%22%0A                    + %22 VolunteeringOpportunity WHERE EventID=%5C%22%25s%5C%22%22,%0A                eventId));%0A    try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) %7B%0A      while (resultSet.next()) %7B%0A        String opportunityId = resultSet.getString(0);%0A        String name = resultSet.getString(1);%0A        long numSpotsLeft = resultSet.getLong(2);%0A        Set%3CString%3E requiredSkills =%0A            resultSet.getStringList(3).stream().collect(Collectors.toSet());%0A        results.add(%0A            new VolunteeringOpportunity.Builder(eventId, name, numSpotsLeft)%0A                .setOpportunityId(opportunityId)%0A                .setRequiredSkills(requiredSkills)%0A                .build());%0A      %7D%0A    %7D%0A    spanner.close();%0A    return results;%0A  %7D
 %0A%7D%0A

Remove conflicting function for clean merge.