package gq.baijie.onetab.internal.storage;

import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.annotation.Nonnull;

import gq.baijie.onetab.ProgressOrResult;
import gq.baijie.onetab.Results;
import gq.baijie.onetab.StorageServiceSession;
import gq.baijie.onetab.WebArchive;
import rx.Observable;

import static gq.baijie.onetab.StorageService.TYPE_SQLITE;

public class SqliteStorageServiceSession implements StorageServiceSession {

  private static final String DATABASE_URL_PREFIX = "jdbc:sqlite:";

  @Nonnull
  private final String databaseUrl;

  @Nonnull
  private final Path path;

  public SqliteStorageServiceSession(@Nonnull Path path) {
    this.path = path;
    databaseUrl = DATABASE_URL_PREFIX + path;
  }

  @Nonnull
  @Override
  public String getType() {
    return TYPE_SQLITE;
  }

  @Nonnull
  @Override
  public Observable<ProgressOrResult<Void, Throwable>> save(@Nonnull WebArchive webArchive) {
    return Observable.create(subscriber -> {
      if(subscriber.isUnsubscribed()) {
        return;
      }
      doSave(webArchive);
      if (subscriber.isUnsubscribed()) {
        return;
      }
      subscriber.onNext(ProgressOrResult.result(Results.<Void, Throwable>succeed(null)));
    });
  }

  @Nonnull
  @Override
  public Observable<ProgressOrResult<WebArchive, Throwable>> retrieve() {
    return null;
  }

  private void doSave(@Nonnull WebArchive webArchive) {
    try(final Connection connection = DriverManager.getConnection(databaseUrl);
        final Statement statement = connection.createStatement()) {
      connection.setAutoCommit(false);

      statement.executeUpdate("drop table if exists section");
      statement.executeUpdate("create table section (id integer PRIMARY KEY)");
      statement.executeUpdate("drop table if exists item");
      statement.executeUpdate("create table item (id integer PRIMARY KEY, section_id integer, link string, title string)");;
      for(WebArchive.Section section:webArchive.getSections()) {
        saveSection(section, connection);
      }

      connection.commit();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  private void saveSection(@Nonnull WebArchive.Section section, @Nonnull Connection connection)
      throws SQLException {
    try(final PreparedStatement statement = connection.prepareStatement(
//        "insert into section () values ()", Statement.RETURN_GENERATED_KEYS)) {
        "insert into section DEFAULT VALUES", Statement.RETURN_GENERATED_KEYS)) {
      statement.executeUpdate();
      final ResultSet generatedKeys = statement.getGeneratedKeys();
      if (generatedKeys.next()) {
        final long sectionId = generatedKeys.getLong(1);
        saveItems(sectionId, section.getItems(), connection);
      } else {
        throw new IllegalStateException("no generated primary key");
      }
    }
  }

  private void saveItems(
      long sectionId, @Nonnull List<WebArchive.Item> items, @Nonnull Connection connection)
      throws SQLException {
    try(final PreparedStatement statement = connection.prepareStatement(
        "insert into item (section_id, link, title) values (?, ?, ?)")) {
      statement.setLong(1, sectionId);
      for(WebArchive.Item item: items){
        statement.setString(2, item.getLink());
        statement.setString(3, item.getTitle());
        statement.executeUpdate();
      }
    }
  }

}

@@ -234,32 +234,55 @@
 .sql.Statement;%0A
+import java.sql.Types;%0A
 import java.util
@@ -1908,16 +1908,24 @@
 ection (
+storage_
 id integ
@@ -1938,16 +1938,61 @@
 MARY KEY
+, id string, create_date integer DEFAULT NULL
 )%22);%0A   
@@ -2098,16 +2098,24 @@
 e item (
+storage_
 id integ
@@ -2138,16 +2138,24 @@
 section_
+storage_
 id integ
@@ -2157,16 +2157,27 @@
 integer,
+ id string,
  link st
@@ -2414,32 +2414,39 @@
 %0A  %7D%0A%0A  private 
+static 
 void saveSection
@@ -2620,10 +2620,8 @@
 nt(%0A
-//
     
@@ -2646,16 +2646,31 @@
 ection (
+id, create_date
 ) values
@@ -2671,16 +2671,20 @@
 values (
+?, ?
 )%22, Stat
@@ -2725,83 +2725,226 @@
     
-  %22insert into section DEFAULT VALUES%22, Statement.RETURN_GENERATED_KEYS)) %7B
+statement.setString(1, section.getId());%0A      if (section.getCreateDate() == null) %7B%0A        statement.setNull(2, Types.INTEGER);%0A      %7D else %7B%0A        statement.setLong(2, section.getCreateDate().getTime());%0A      %7D
 %0A   
@@ -3303,24 +3303,31 @@
 %0A%0A  private 
+static 
 void saveIte
@@ -3558,16 +3558,28 @@
 section_
+storage_id, 
 id, link
@@ -3600,16 +3600,19 @@
 s (?, ?,
+ ?,
  ?)%22)) %7B
@@ -3721,16 +3721,62 @@
 tring(2,
+ item.getId());%0A        statement.setString(3,
  item.ge
@@ -3805,33 +3805,33 @@
 ement.setString(
-3
+4
 , item.getTitle(

save section.id, section.createDate and item.id in SqliteStorageServiceSession