package osm.jp.coverage.fuel;
import osm.jp.coverage.busstop.*;

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.*;

public class Fuel {

    String filter = "";
    String urlStr = "";

    public static final boolean DB_INIT = false;

    // 近くのノードを探す範囲（KJS2を中心としたNEER×2(ｍ)四方の領域
    static final int NEER = 200;		// 200m x 2 = 400m四方

    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 Fuel(con);
        }
        finally {
            DatabaseTool.closeDb(con);
        }
    }
	
    /**
     * 個別の都道府県「GMLディレクトリ」を処理
     * 
     * @param con
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws TransformerException
     */
    public Fuel(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
        try (   PreparedStatement ps2 = con.prepareStatement("SELECT idref,lat,lon,fixed, area FROM "+ DbFuel.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)");
                PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score FROM "+ DbExist.TABLE_NAME);
                PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?");
                PreparedStatement ps4 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0");
                PreparedStatement ps5 = con.prepareStatement("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)" ))
        {
            System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0");
            ps4.executeUpdate();

            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String osmid = rset1.getString("idref");
                    double lat = rset1.getDouble("lat");
                    double lon = rset1.getDouble("lon");
                    int score = rset1.getInt("score");
                    String idref = null;
                    int area = 0;
                    int fixed = 0;

                    // 指定の緯度経度を中心とする半径100x2m四方の矩形領域
                    RectArea rect = new RectArea(lat, lon, NEER);		// 300m 四方
                    ps2.setDouble(1, rect.minlat);
                    ps2.setDouble(2, rect.maxlat);
                    ps2.setDouble(3, rect.minlon);
                    ps2.setDouble(4, rect.maxlon);
                    try (ResultSet rset2 = ps2.executeQuery()) {
                        double distance = 999999.9D;
                        while (rset2.next()) {
                            double lat2 = rset2.getDouble("lat");
                            double lon2 = rset2.getDouble("lon");
                            double dd = distance(lat,lat2,lon,lon2);
                            if (dd < distance) {
                                distance = dd;
                                idref = rset2.getString("idref");
                                area = rset2.getInt("area");
                            }
                        }
                    }
                    if (idref != null) {
                        System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1="+ score +" WHERE idref="+ idref +" and area=" + area);
                        ps3.setInt(1, score);
                        ps3.setString(2, idref);
                        ps3.setInt(3, area);
                        ps3.executeUpdate();
                    }
                    else {
                        System.out.println("NOT FOUND! idref="+ osmid);
                    }
                }
            }

            System.out.println("UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)");
            ps5.executeUpdate();

        }
    }
    
    public static final double ONE_KM_LAT = 0.009013372D;
    public static final double ONE_KM_LON = 0.010966404D;
    static double distance(double lat1, double lon1, double lat2, double lon2) {
        double dlat = Math.abs(lat1 - lat2) / ONE_KM_LAT / 1000D;
        double dlon = Math.abs(lon1 - lon2) / ONE_KM_LON / 1000D;
        return Math.sqrt(dlat*dlat + dlon*dlon);
    }
}