package osm.jp.coverage;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import jp.co.areaweb.tools.database.DatabaseTool;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.fail;
import osm.jp.coverage.postoffice.PostofficeTest;

public class PoiTest {
    
    /**
     * 許容できる誤差は 0.00000009D 固定
     * @param rset      調べる値（フィールド名:"lat" or "lon"）
     * @param latStr    基準となる値
     * @param lonStr    許容できる誤差
     * @return
     * @throws SQLException 
     */
    public static boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException {
        return checkRenge(rset, latStr, lonStr, 0.00000009D);
    }

    /**
     * 
     * @param rset      調べる値（フィールド名:"lat" or "lon"）
     * @param latStr    基準となる値
     * @param lonStr    基準となる値
     * @param delta     許容できる誤差
     * @return
     * @throws SQLException 
     */
    public static boolean checkRenge(ResultSet rset, String latStr, String lonStr,  double delta) throws SQLException {
        if (checkRenge(rset.getDouble("lat"), latStr, delta)) {
            if (checkRenge(rset.getDouble("lon"), lonStr, delta)) {
                return true;
            }
        }
        return false;
    }
    
    /**
     * 
     * @param d1    調べる値
     * @param str   基準となる値
     * @param delta 許容できる誤差
     * @return      許容値内ならばtrue
     * @throws SQLException 
     */
    public static boolean checkRenge(double d1, String str, double delta) throws SQLException {
        double base = Double.parseDouble(str);
        double up = d1 + delta;
        double down = d1 - delta;
        boolean ret = true;
        if (Double.compare(base, up) > 0) {
            ret = false;
        }
        if (Double.compare(base, down) < 0) {
            ret = false;
        }
        System.out.println("d1: "+ d1 +" : "+ str +" --> "+ (ret ? "IN" : "out"));
        return ret;
    }
    
    /**
     * 
     * 
     * @param tableName         DbPoi.TABLE_NAME
     * @param lat   35.43035D;  // 35.43035  35.4305614, 139.3662339
     * @param lon   139.36622D
     * @param fixed1
     * @return 
     */
    public String getRefid(String tableName, double lat, double lon, int fixed1) {
        String ret = "";
        
        Connection hsqldb = null;
        try {
            hsqldb =  DatabaseTool.openDb("database");
            
            String where = String.format("(lat > '%s') and (lat < '%s') and (lon > '%s') and (lon < '%s')", 
                    String.valueOf(lat-0.001D), String.valueOf(lat+0.001D), 
                    String.valueOf(lon-0.001D), String.valueOf(lon+0.001D));
            String sql = String.format("SELECT * FROM %s WHERE %s", tableName, where);
            System.out.println(sql);
            PreparedStatement ps1 = hsqldb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    int v = rset1.getInt("fixed1");
                    System.out.println("{");
                    System.out.println("idref:" + rset1.getString("idref"));
                    System.out.println("gid:" + rset1.getString("gid"));
                    System.out.println("area:" + rset1.getInt("area"));
                    System.out.println("fixed:" + rset1.getInt("fixed"));
                    System.out.println("fixed1:" + v);
                    System.out.println("lat:" + rset1.getDouble("lat"));
                    System.out.println("lon:" + rset1.getDouble("lon"));
                    System.out.println("}");
                    if (ret.length() > 0) {
                        ret += ",";
                    }
                    ret += rset1.getString("idref");
                    assertThat(v, is(fixed1));
                    return ret;
                }
                fail("該当のデータがミツカラナイ");
            }
        } catch (IOException | ClassNotFoundException | SQLException ex) {
            Logger.getLogger(PostofficeTest.class.getName()).log(Level.SEVERE, null, ex);
            fail(ex.toString());
        } finally {
            DatabaseTool.closeDb(hsqldb);
        }
        return ret;
    }
}