package osm.jp.coverage;

import javax.xml.parsers.*;
import javax.xml.transform.TransformerException;

import org.xml.sax.*;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import jp.co.areaweb.tools.database.*;
import osm.jp.api.HttpPOST;
import osm.jp.api.Japan;
import osm.jp.api.RectArea;

public class Test {

    // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域
    static final int NEER = 1000;		// 1000m x 2 = 2000m四方
    static final double DISTANCE = 1000.0D;     // 1000m 以内

    public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");

    /**
     * メイン
     *
     *	java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop [option]
     *		OPTION: -check	OSMデータ上に既存のバス停が存在するかどうかをチェックする
     *
     * @param args
     * @throws IOException
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws FileNotFoundException
     * @throws TransformerException
     * @throws SAXException
     * @throws ParserConfigurationException
     */
    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
    {
        // HSQLディレクトリがなければ作る
        File dbdir = new File("database");
        if (!dbdir.isDirectory()) {
            dbdir.mkdir();
        }

        Connection con = DatabaseTool.openDb("database");

        try {
            new Test(con);
        }
        finally {
            DatabaseTool.closeDb(con);
        }
    }
	
    /**
     * 個別の都道府県「GMLディレクトリ」を処理
     * 
     * @param con
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws TransformerException
     */
    public Test(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
        
        String sqlStr1 = "SELECT lat,lon FROM "+ HttpPOST.TABLE_NAME;
        String sqlStr2 = "SELECT gmlid,lat,lon,area FROM "+ DbTest.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)";
        String sqlStr3 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=1 WHERE gmlid=? and area=?";
        String sqlStr4 = "UPDATE "+ DbTest.TABLE_NAME +" SET fixed1=0";
        String sqlStr5 = "UPDATE "+ DbTest.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)";
        try (   PreparedStatement ps2 = con.prepareStatement(sqlStr2);
                PreparedStatement ps1 = con.prepareStatement(sqlStr1);
                PreparedStatement ps3 = con.prepareStatement(sqlStr3);
                PreparedStatement ps4 = con.prepareStatement(sqlStr4);
                PreparedStatement ps5 = con.prepareStatement(sqlStr5))
        {
            System.out.println(sqlStr4);
            ps4.executeUpdate();

            try (ResultSet rset1 = ps1.executeQuery()) {
                System.out.println(sqlStr1);
                while (rset1.next()) {
                    double lat = rset1.getDouble("lat");
                    double lon = rset1.getDouble("lon");

                    // 指定の緯度経度を中心とする半径1000x2m四方の矩形領域
                    RectArea rect = new RectArea(lat, lon, NEER);		// 1000m 四方
                    ps2.setDouble(1, rect.minlat);
                    ps2.setDouble(2, rect.maxlat);
                    ps2.setDouble(3, rect.minlon);
                    ps2.setDouble(4, rect.maxlon);
                    System.out.println(sqlStr2 +" ["+ rect.minlat +", "+ rect.maxlat +", "+ rect.minlon +", "+ rect.maxlon +"]");
                    try (ResultSet rset2 = ps2.executeQuery()) {
                        while (rset2.next()) {
                            String gmlid = rset2.getString("gmlid");
                            double lat2 = rset2.getDouble("lat");
                            double lon2 = rset2.getDouble("lon");
                            int area = rset2.getInt("area");
                            double dd = Japan.distanceKm(lat,lon,lat2,lon2);
                            System.out.println("D"+"distance = "+ dd +"");
                            if ((dd * 1000.0D) < DISTANCE) {
                                System.out.println(sqlStr3 +" ["+ gmlid +", "+ area +"]");
                                ps3.setString(1, gmlid);
                                ps3.setInt(2, area);
                                ps3.executeUpdate();
                            }
                            else {
                                System.out.println("out of distance "+ dd +" : NEER=1000");
                            }
                        }
                    }
                }
            }

            System.out.println(sqlStr5);
            ps5.executeUpdate();
        }
    }
}