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 javax.xml.parsers.ParserConfigurationException;
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 org.xml.sax.SAXException;

/**
 *
 * @author yuu
 */
@FixMethodOrder (MethodSorters.NAME_ASCENDING)
public class DbPoliceTest {
    @Before
    public void setUp() throws Exception {
    }
    
    @After
    public void tearDown() throws Exception {
    }
    
    @Test
    public void test01_police() {
        try {
            String[] args = new String[0];
            DbPolice.main(args);
        }
        catch (IOException | ClassNotFoundException | SQLException | ParserConfigurationException | SAXException ex) {
            ex.printStackTrace();
            fail(ex.toString());
        }

        File dir = new File("database");
        assertTrue(dir.exists());
        assertTrue(dir.isDirectory());
        
        Connection hsqldb = null;
        try {
            hsqldb =  DatabaseTool.openDb("database");
            
            PreparedStatement ps1 = hsqldb.prepareStatement("SELECT count(*) FROM "+ DbPolice.TABLE_NAME);
            try (ResultSet rset1 = ps1.executeQuery()) {
                if (rset1.next()) {
                    long cnt = rset1.getLong(1);
                    assertThat((cnt > 0), is(true));
                }
                else {
                    fail();
                }
            }
                        
            // gml:id="pt_psn549" name=江差警察署鶉駐在所 code=5 場所: 41.933898 140.324846
            checkSql(hsqldb, "pt_psn549", 1, 5, "41.933898", "140.324846");
            
            // pt_psn2 1 北海道警察函館方面本部 函館市五稜郭町15-5 41.796013 140.751973
            checkSql(hsqldb, "pt_psn2", 1, 1, "41.796013", "140.751973");
            
            // pt_psn597 area=1 code=4 栗山警察署長沼交番 長沼町錦町北1-2-18 43.011878 141.691813
            checkSql(hsqldb, "pt_psn597", 1, 4, "43.011878", "141.691813");

            // pt_psn6 area=1 code=2 中央警察署 札幌市中央区北1条西5丁目4 43.062164 141.349533
            checkSql(hsqldb, "pt_psn6", 1, 2, "43.062164", "141.349533");
            
            // pt_psn814 area=1 code=6 南警察署旭ヶ丘警備派出所 札幌市中央区南13条西23丁目19 43.039666 141.324324
            checkSql(hsqldb, "pt_psn814", 1, 6, "43.039666", "141.324324");
            
            // pt_psn831 area=1 code=7 北海道警察学校 札幌市南区真駒内南町5丁目1-7 42.979149 141.348956
            checkSql(hsqldb, "pt_psn831", 1, 7, "42.979149", "141.348956");
            
            // pt_psn833 area=1 code=8 西警察署山の手連絡所 札幌市西区山の手2条6丁目2番14号 43.066532 141.29754
            checkSql(hsqldb, "pt_psn833", 1, 8, "43.066532", "141.29754");
            
            // pt_psn178 area=47 code=7 沖縄県警察学校 うるま市石川3402 26.441508 127.83277
            checkSql(hsqldb, "pt_psn178", 47, 7, "26.441508", "127.83277");
            
            // pt_psn172 area=47 code=6 豊見城警察署那覇空港警備派出所 那覇市字鏡水150 26.208003 127.650617
            checkSql(hsqldb, "pt_psn172", 47, 6, "26.208003", "127.650617");
            
            // pt_psn87 area=47 code=5 八重山警察署伊原間駐在所 石垣市字伊原間40-1 24.508835 124.28265
            checkSql(hsqldb, "pt_psn87", 47, 5, "24.508835", "124.28265");
            
            // pt_psn179 area=47 code=4 那覇警察署おもろまち交番 那覇市おもろまち1-2-32 26.22469593 127.694261
            checkSql(hsqldb, "pt_psn179", 47, 4, "26.22469593", "127.694261");
            
            // pt_psn2 area=47 code=2 那覇警察署 那覇市与儀1-2-9 26.207533 127.691753
            checkSql(hsqldb, "pt_psn2", 47, 2, "26.207533", "127.691753");
            
            // pt_psn1 area=47 code=1 沖縄県警察本部 那覇市泉崎1-2-2 26.211504 127.681059
            checkSql(hsqldb, "pt_psn1", 47, 1, "26.211504", "127.681059");
            

        } catch (ClassNotFoundException | SQLException | IOException ex) {
            Logger.getLogger(DbPoliceTest.class.getName()).log(Level.SEVERE, null, ex);
            fail();
        } finally {
            DatabaseTool.closeDb(hsqldb);
        }
    }
    
    void checkSql(Connection hsqldb, String idref, int area, int code, String lat, String lon) throws SQLException {
            String sql = String.format("SELECT %s.idref, %s.area, %s.code, %s.lat, %s.lon", 
                        DbPolice.TABLE_NAME, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME2)
                    + String.format(" FROM %s,%s", DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2)
                    + String.format(" WHERE (%s.idref=%s.idref) and (%s.area=%s.area) and (%s.idref='%s') and (%s.area=%d)", 
                        DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME, DbPolice.TABLE_NAME2, DbPolice.TABLE_NAME, 
                        idref, DbPolice.TABLE_NAME, area);
            System.out.println(sql);
            PreparedStatement ps1 = hsqldb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                boolean ok = false;
                while (rset1.next()) {
                    assertThat(rset1.getString(1), is(idref));    // idref
                    assertThat(rset1.getInt(2), is(area)); // area
                    assertThat(rset1.getInt(3), is(code)); // code
                    assertThat(checkRenge(rset1, lat, lon), is(true));
                    ok = true;
                }
                if (!ok) {
                    fail();
                }
            }

    }
    
    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.0000005D;
        double down = d1 - 0.0000005D;
        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;
    }
}