package osm.jp.coverage.fuel;

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 org.junit.*;
import static org.junit.Assert.*;
import org.junit.runners.MethodSorters;
import osm.jp.api.HttpPOST;
import osm.jp.coverage.busstop.Busstop;

@FixMethodOrder (MethodSorters.NAME_ASCENDING)
public class FuelTest {
    @Before
    public void setUp() throws Exception {
    }
    
    @After
    public void tearDown() throws Exception {
    }

    @Test
    public void test00_Fuel_main() {
        String[] args = new String[0];
        Busstop.main(args);
    }
    
    /**
     * OSM: removed:amenity=fuel 35.4305614, 139.3662339
     * GML: 14, n00375
     */
    @Test
    public void test01_dataread() {
        Connection hsqldb = null;
        try {
            hsqldb =  DatabaseTool.openDb("database");
            
            double lat = 35.43035D;  // 35.43035  35.4305614, 139.3662339
            double lon = 139.36622D;  //139.36622
            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", DbFuel.TABLE_NAME, where);
            PreparedStatement ps1 = hsqldb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    System.out.println(rset1.getString("idref"));
                    System.out.println(rset1.getString("gid"));
                    System.out.println(rset1.getInt("area"));
                    System.out.println(rset1.getInt("fixed"));
                    System.out.println(rset1.getInt("fixed1"));
                    System.out.println(rset1.getDouble("lat"));
                    System.out.println(rset1.getDouble("lon"));
                }
            }
            
        } catch (IOException | ClassNotFoundException | SQLException ex) {
            Logger.getLogger(FuelTest.class.getName()).log(Level.SEVERE, null, ex);
            fail(ex.toString());
        } finally {
            DatabaseTool.closeDb(hsqldb);
        }
    }
    
    boolean checkRenge(ResultSet rset, String latStr, String lonStr) throws SQLException {
        if (checkRenge(rset.getDouble("lat"), latStr)) {
            if (checkRenge(rset.getDouble("lon"), lonStr)) {
                return true;
            }
        }
        return false;
    }
    
    boolean checkRenge(double d1, String str) throws SQLException {
        double base = Double.parseDouble(str);
        double up = d1 + 0.00000009D;
        double down = d1 - 0.00000009D;
        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;
    }
    
    /**
     * OSM: 5338111023 removed:amenity=fuel 35.4305614, 139.3662339
     * GML: 14, n00375
     */
    @Test
    public void test91_removed() {
        Connection hsqldb = null;
        String sql1 = String.format("SELECT * FROM %s where idref='%s'", HttpPOST.TABLE_NAME, "5338111023");
        try {
            hsqldb =  DatabaseTool.openDb("database");
            try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)){
                try (ResultSet rset1 = ps1.executeQuery()) {
                    assertTrue(rset1.next());
                }
            }
        }
        catch(Exception e){
            e.printStackTrace();
            fail(e.toString());
        } finally {
            DatabaseTool.closeDb(hsqldb);
        }
        
        double lat = 35.43035D;  // 35.43035  35.4305614, 139.3662339
        double lon = 139.36622D;  //139.36622
        String idrefs = getRefid(lat, lon);
        System.out.println(idrefs);
    }

    /**
     * OSM: ノード: 出光 (1600343871)  35.4214195, 139.3600994
     * GML: 14, n00375
     */
    @Test
    public void test92_normal() {
        double lat = 35.4214195D;
        double lon = 139.3600994D;
        String idrefs = getRefid(lat, lon);
        System.out.println(idrefs);
    }

    /**
     * 
     * 
     * @param lat   35.43035D;  // 35.43035  35.4305614, 139.3662339
     * @param lon   139.36622D
     * @return 
     */
    public String getRefid(double lat, double lon) {
        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", DbFuel.TABLE_NAME, where);
            System.out.println(sql);
            PreparedStatement ps1 = hsqldb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    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:" + rset1.getInt("fixed1"));
                    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");
                }
            }
        } catch (IOException | ClassNotFoundException | SQLException ex) {
            Logger.getLogger(FuelTest.class.getName()).log(Level.SEVERE, null, ex);
            fail(ex.toString());
        } finally {
            DatabaseTool.closeDb(hsqldb);
        }
        return ret;
    }
}