package com.google.tagpost.spanner;

import com.google.cloud.Timestamp;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.tagpost.Comment;
import com.google.tagpost.Tag;
import com.google.tagpost.Thread;

import java.util.ArrayList;
import java.util.List;

import com.google.inject.Singleton;

/** Data access and operations of Spanner */
@Singleton
public class SpannerService implements DataService {

  @Override
  public List<Thread> getAllThreadsByTag(Tag tag) {

    SpannerOptions spannerOptions = SpannerOptions.newBuilder().build();
    Spanner spanner = spannerOptions.getService();
    List<Thread> threadList = new ArrayList<>();

    try {
      DatabaseId db = DatabaseId.of("testing-bigtest", "tagpost", "test");
      DatabaseClient dbClient = spanner.getDatabaseClient(db);
      String query =
          "SELECT ThreadID, PrimaryTag FROM Thread WHERE PrimaryTag = '" + tag.getTagName() + "'";
      ResultSet resultSet = dbClient.singleUse().executeQuery(Statement.of(query));
      convertResultToThreadList(threadList, resultSet);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return threadList;
  }

  @Override
  public List<Comment> getAllCommentsByThreadId(long threadId) {

    SpannerOptions spannerOptions = SpannerOptions.newBuilder().build();
    Spanner spanner = spannerOptions.getService();
    List<Comment> commentList = new ArrayList<>();

    try {
      DatabaseId db = DatabaseId.of("testing-bigtest", "tagpost", "test");
      DatabaseClient dbClient = spanner.getDatabaseClient(db);
      String query = "SELECT * FROM Comment WHERE ThreadID = " + threadId;
      ResultSet resultSet = dbClient.singleUse().executeQuery(Statement.of(query));
      convertResultToCommentList(threadId, commentList, resultSet);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return commentList;
  }

  private void convertResultToThreadList(List<Thread> threadList, ResultSet resultSet) {
    while (resultSet.next()) {
      long threadId = resultSet.getLong("ThreadID");
      Tag primaryTag = Tag.newBuilder().setTagName(resultSet.getString("PrimaryTag")).build();
      Thread thread = Thread.newBuilder().setThreadId(threadId).setPrimaryTag(primaryTag).build();
      threadList.add(thread);
    }
  }

  private void convertResultToCommentList(
      long threadId, List<Comment> commentList, ResultSet resultSet) {
    while (resultSet.next()) {
      long commentId = resultSet.getLong("CommentID");
      String username = resultSet.getString("Username");
      Timestamp timestamp = resultSet.getTimestamp("Timestamp");
      Tag primaryTag = Tag.newBuilder().setTagName(resultSet.getString("PrimaryTag")).build();
      List<Tag> extraTags = new ArrayList<>();
      if (!resultSet.isNull("ExtraTags")) {
        for (String tagName : resultSet.getStringList("ExtraTags")) {
          Tag tag = Tag.newBuilder().setTagName(tagName).build();
          extraTags.add(tag);
        }
      }
      String content = resultSet.getString("CommentContent");
      Comment comment =
          Comment.newBuilder()
              .setCommentId(commentId)
              .setCommentContent(content)
              .addAllExtraTags(extraTags)
              .setPrimaryTag(primaryTag)
              .setThreadId(threadId)
              .setUsername(username)
              .setTimestamp(timestamp.toProto())
              .build();
      commentList.add(comment);
    }
  }
}

@@ -332,16 +332,60 @@
 baseId;%0A
+import com.google.cloud.spanner.Statement;%0A%0A
 import c
@@ -407,24 +407,24 @@
 st.Comment;%0A
-
 import com.g
@@ -737,24 +737,125 @@
 Tag tag) %7B%0A%0A
+    String SQLStatement = %22SELECT ThreadID, PrimaryTag FROM Thread WHERE PrimaryTag = @primaryTag%22;%0A%0A
     SpannerO
@@ -1160,32 +1160,33 @@
 Client(db);%0A
+%0A
       St
 ring query =
@@ -1177,79 +1177,80 @@
   St
-ring query =%0A          %22SELECT ThreadID, PrimaryTag FROM Thread WHERE P
+atement statement =%0A          Statement.newBuilder(SQLStatement).bind(%22p
 rima
@@ -1258,16 +1258,14 @@
 yTag
- = '%22 + 
+%22).to(
 tag.
@@ -1280,15 +1280,19 @@
 me()
- + %22'%22;
+).build();%0A
 %0A   
@@ -1346,33 +1346,33 @@
 xecuteQuery(
-S
+s
 tatement
 .of(query));
@@ -1355,34 +1355,24 @@
 ry(statement
-.of(query)
 );%0A      con
@@ -1581,24 +1581,103 @@
 hreadId) %7B%0A%0A
+    String SQLStatement = %22SELECT * FROM Comment WHERE ThreadID = @threadId%22;%0A%0A
     SpannerO
@@ -1988,24 +1988,25 @@
 nt(db);%0A
+%0A
       St
 ring que
@@ -2001,74 +2001,112 @@
   St
-ring query = %22SELECT * FROM Comment WHERE ThreadID = %22 + threadId;
+atement statement =%0A          Statement.newBuilder(SQLStatement).bind(%22threadId%22).to(threadId).build();%0A
 %0A   
@@ -2164,25 +2164,25 @@
 teQuery(
-S
+s
 tatement
 .of(quer
@@ -2177,18 +2177,8 @@
 ment
-.of(query)
 );%0A 

use Statament.builder to build query