package com.github.davidmoten.geo.db;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.TimeUnit;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;

import com.github.davidmoten.geo.Coverage;
import com.github.davidmoten.geo.GeoHash;

public class DatabaseTest {

	// @Test
	public void testCreateDatabase() throws IOException, SQLException {
		File dir = new File("target/db");
		dir.mkdir();
		FileUtils.deleteDirectory(dir);
		String sql = IOUtils.toString(
				DatabaseTest.class.getResourceAsStream("/create.sql"), "UTF-8");
		String[] commands = sql.split(";");
		String url = "jdbc:h2:file:target/db/test";
		Connection con = DriverManager.getConnection(url, "sa", "");
		for (String command : commands) {
			execute(con, command);
		}
		System.out.println("inserting...");
		PreparedStatement st = con
				.prepareStatement("insert into report(time,lat,lon,name,geohash1,geohash2,geohash3,geohash4,geohash5,geohash6,geohash7,geohash8,geohash9,geohash10,geohash11,geohash12) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		long now = System.currentTimeMillis();
		for (int i = 0; i < 1000000; i++) {
			long t = now - Math.round(TimeUnit.DAYS.toMillis(1));
			double lat = -Math.random() * 10;
			double lon = 135 + Math.random() * 10;
			st.setLong(1, t);
			st.setDouble(2, lat);
			st.setDouble(3, lon);
			st.setString(4, "name");
			for (int j = 5; j <= 16; j++)
				st.setString(j, GeoHash.encodeHash(lat, lon, j - 4));
			st.executeUpdate();
		}
		st.close();
		System.out.println("creating indexes");
		execute(con, "create index idx_report_time on report(time)");
		for (int i = 1; i <= 12; i++)
			execute(con, "create index idx_geohash_" + i + " on report(geohash"
					+ i + ")");
		for (int n = 0; n < 3; n++) {
			for (int length = 2; length <= 4; length++) {
				Coverage coverage = GeoHash.coverBoundingBox(-5, 136, -6, 138,
						length);
				System.out.println(coverage.getHashes());
				StringBuilder s = new StringBuilder();
				for (String hash : coverage.getHashes()) {
					if (s.length() > 1)
						s.append(" or ");
					s.append("geohash" + hash.length() + "='" + hash + "'");
				}
				String sql2 = "select count(*) from report where time >= ? and time <?  and ("
						+ s + ")";
				System.out.println(sql2);
				PreparedStatement ps = con.prepareStatement(sql2);
				ps.setLong(1, now - Math.round(TimeUnit.DAYS.toMillis(1)));
				ps.setLong(2, now);
				long t = System.currentTimeMillis();
				System.out.println("querying...");
				ResultSet rs = ps.executeQuery();
				System.out.println((System.currentTimeMillis() - t) / 1000.0
						+ "ms");
				while (rs.next())
					System.out.println("found=" + rs.getLong(1));
				ps.close();
			}
		}
		con.close();
	}

	private void execute(Connection con, String command) throws SQLException {
		System.out.println(command);
		PreparedStatement st = con.prepareStatement(command);
		st.executeUpdate();
		st.close();
	}

}

@@ -224,32 +224,55 @@
 l.SQLException;%0A
+import java.util.List;%0A
 import java.util
@@ -457,16 +457,56 @@
 GeoHash;
+%0Aimport com.google.common.collect.Lists;
 %0A%0Apublic
@@ -1983,17 +1983,17 @@
  0; n %3C 
-3
+1
 ; n++) %7B
@@ -2407,16 +2407,20 @@
 ect 
-count(*)
+name,lat,lon
  fro
@@ -2768,24 +2768,71 @@
 uteQuery();%0A
+%09%09%09%09List%3CString%3E names = Lists.newArrayList();%0A
 %09%09%09%09System.o
@@ -2920,18 +2920,214 @@
 .next())
+ %7B
 %0A%09
+%09%09%09%09String name = rs.getString(1);%0A%09%09%09%09%09double lat = rs.getDouble(2);%0A%09%09%09%09%09double lon = rs.getDouble(3);%0A%09%09%09%09%09if (lat %3E= -6 && lat %3C= -5 && lon %3E= 136 && lon %3C 138)%0A%09%09%09%09%09%09names.add(name);%0A%09%09%09%09%7D%0A
 %09%09%09%09Syst
@@ -3156,20 +3156,19 @@
 %22 + 
-rs.getLong(1
+names.size(
 ));%0A

add h2 db test