package osm.jp.coverage.police;

import java.io.File;
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 org.junit.*;
import static org.junit.Assert.*;
import org.junit.runners.MethodSorters;
import osm.jp.api.Coverage;

@FixMethodOrder (MethodSorters.NAME_ASCENDING)
public class DbExistTest {
    public void init() throws Exception {
    }
    
    @Before
    public void setUp() throws Exception {
    }
    
    @After
    public void tearDown() throws Exception {
    }
    
    @Test
    public void test020_main() {
        try {
            init();
            String[] args = new String[0];
            DbExistPolice.main(args);
        }
        catch (Exception ex) {
            fail(ex.toString());
        }
    }
    
    @Test
    public void test021_police() {
        File dir = new File(Coverage.DB_PORP_LOCALDB);
        assertTrue(dir.exists());
        assertTrue(dir.isDirectory());
        
        Connection hsqldb = null;
        try {
            hsqldb =  DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB);
            
            PreparedStatement ps8 = hsqldb.prepareStatement("SELECT count(*) FROM AREA_NODE");
            try (ResultSet rset8 = ps8.executeQuery()) {
                if (rset8.next()) {
                    assertThat(rset8.getInt(1), is(0));
                }
                else {
                    fail();
                }
            }
            
            PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM OSM_EXIST");
            try (ResultSet rset1 = ps1.executeQuery()) {
                if (rset1.next()) {
                    long cnt = rset1.getLong(1);
                    assertThat((cnt > 0), is(true));
                }
                else {
                    fail();
                }
            }
            
            // ノード: 海老名警察署 (1420826312) 場所: 35.4454680, 139.3915960
            ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='1420826312'");
            try (ResultSet rset1 = ps1.executeQuery()) {
                if (rset1.next()) {
                    assertThat(rset1.getInt("score"), is(50));
                    assertThat(checkRenge(rset1, "35.4454680", "139.3915960"), is(true));
                }
                else {
                    fail();
                }
            }
            
            // ウェイ: 192565027  海老名警察署 場所: 35.4454680, 139.3915960
            ps1 = hsqldb.prepareStatement("SELECT score,name,lat,lon FROM OSM_EXIST where idref='192565027'");
            try (ResultSet rset1 = ps1.executeQuery()) {
                if (rset1.next()) {
                    assertThat(rset1.getInt("score"), is(50));
                    double lat = rset1.getDouble("lat");
                    assertTrue(lat > 35.44);
                    assertTrue(lat < 35.45);
                    double lon = rset1.getDouble("lon");
                    assertTrue(lon > 139.38);
                    assertTrue(lon < 140.00);
                    assertThat(checkRenge(rset1, "35.4454680", "139.3915960"), is(true));
                }
                else {
                    fail();
                }
            }
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
            fail();
        } catch (SQLException ex) {
            Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
            fail();
        } catch (IOException ex) {
            Logger.getLogger(DbExistTest.class.getName()).log(Level.SEVERE, null, ex);
            fail();
        } 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.0001D;
        double down = d1 - 0.0001D;
        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;
    }
}