package osm.jp.coverage.fuel;

import osm.jp.api.RectArea;
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.Japan;

public class Fuel {
    Connection hsqldb = null;
    String filter = "";
    String urlStr = "";

    public static final boolean DB_INIT = false;

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

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

    /**
     * メイン
     *
     * @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();
        }

        try (Connection hsql = DatabaseTool.openDb("database")) {
            new Fuel(hsql);
        }
    }
    
    /**
     * 個別の都道府県「GMLディレクトリ」を処理
     * 
     * @param hsqldb   hsqldb
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws TransformerException
     */
    public Fuel(Connection hsqldb) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
        this.hsqldb = hsqldb;
        
        String sql1 = "SELECT gmlid,area,lat,lon,idref FROM "+ DbFuel.TABLE_NAME;
        try (PreparedStatement ps1 = hsqldb.prepareStatement(sql1)) {
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String gmlid = rset1.getString("gmlid");
                    int area = rset1.getInt("area");
                    double lat = rset1.getDouble("lat");
                    double lon = rset1.getDouble("lon");
                    String idref = rset1.getString("idref");
                    if (idref != null) {
                        continue;
                    }
                    
                    idref = null;
                    int score = 0;
                    boolean removed = false;
                    double distance = 999999.9D;

                    String whereStr = "WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (gmlid IS NULL)";
                    String fromStr = "FROM "+ DbExistFuel.TABLE_NAME;
                    String sql2 = String.format("SELECT idref,lat,lon,score,removed %s %s", fromStr, whereStr);
                    try (PreparedStatement ps2 = hsqldb.prepareStatement(sql2)) {
                        // 指定の緯度経度を中心とする半径100x2m四方の矩形領域
                        RectArea rect = new RectArea(lat, lon, NEER);		// 100m 四方
                        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()) {
                            while (rset2.next()) {
                                double lat2 = rset2.getDouble("lat");
                                double lon2 = rset2.getDouble("lon");
                                double dd = Japan.distanceKm(lat,lon,lat2,lon2);
                                if (dd < distance) {
                                    distance = dd;
                                    idref = rset2.getString("idref");
                                    score = rset2.getInt("score");
                                    removed = rset2.getBoolean("removed");
                                }
                            }
                        }
                    }
                    
                    if (idref != null) {
                        String setStr = "SET idref=?,fixed1=?,removed=?";
                        whereStr = "WHERE gmlid=? AND area=?";
                        String sql3 = String.format("UPDATE %s %s %s",DbFuel.TABLE_NAME, setStr, whereStr);
                        try (PreparedStatement ps3 = hsqldb.prepareStatement(sql3)) {
                            System.out.println(sql3);
                            ps3.setString(1, idref);
                            ps3.setInt(2, score);
                            ps3.setBoolean(3, removed);
                            ps3.setString(4, gmlid);
                            ps3.setInt(5, area);
                            ps3.executeUpdate();
                        }

                        setStr = "SET gmlid=?,area=?";
                        whereStr = "WHERE idref=?";
                        String sql4 = String.format("UPDATE %s %s %s",DbExistFuel.TABLE_NAME, setStr, whereStr);
                        try (PreparedStatement ps4 = hsqldb.prepareStatement(sql4)) {
                            System.out.println(sql4);
                            ps4.setString(1, gmlid);
                            ps4.setInt(2, area);
                            ps4.setString(3, idref);
                            ps4.executeUpdate();
                        }
                    }
                    else {
                        System.out.println(String.format("NOT FOUND! gmlid=%s,area=%d", gmlid, area));
                    }
                }
            }
        }

        String sql5 = "UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)";
        try (PreparedStatement ps5 = hsqldb.prepareStatement(sql5)) {
            System.out.println(sql5);
            ps5.executeUpdate();
        }
    }
    
}