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.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.sps.data.User;
import java.util.ArrayList;
import java.util.List;

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

  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;
  }
}

@@ -421,24 +421,86 @@
 databaseId;%0A
+  private Spanner spanner;%0A  private DatabaseClient dbClient;%0A
   private st
@@ -613,537 +613,445 @@
     
-this.instanceId = instanceId;%0A    this.databaseId = databaseId;%0A  %7D%0A%0A  /**%0A   * Given a user, insert a row with all available fields into the DB%0A   *%0A   * @param user the user to be inserted; user's ID field should not exist in DB%0A   */%0A  public void insertUser(User user) %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
+SpannerOptions options = SpannerOptions.newBuilder().build();%0A    spanner = options.getService();%0A    DatabaseId db = DatabaseId.of(options.getProjectId(), instanceId, databaseId);%0A    dbClient = spanner.getDatabaseClient(db);%0A  %7D%0A%0A  /**%0A   * Given a user, insert a row with all available fields into the DB%0A   *%0A   * @param user the user to be inserted; user's ID field should not exist in DB%0A   */%0A  public void insertUser(User user) %7B
 %0A   
@@ -1476,263 +1476,8 @@
  DB%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
     
@@ -2638,11 +2638,127 @@
 ons;%0A  %7D
+%0A%0A  /**%0A   * Close the Spanner database connection.%0A   */%0A  public void closeConnection() %7B%0A    spanner.close();%0A  %7D
 %0A%7D%0A

Move initialization of spanner and dbClient to constructor and add close method.