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

public class Fuel {

    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();
        }

        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 {
        String sql4 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=0";
        String sql1 = "SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME;
        String sql2 = "SELECT idref,lat,lon,fixed, area FROM "+ DbFuel.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (fixed1=0)";
        String sql3 = "UPDATE "+ DbFuel.TABLE_NAME +" SET fixed1=? WHERE idref=? and area=?";
        String sql5 = "UPDATE "+ DbFuel.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)";
        try (   PreparedStatement ps1 = con.prepareStatement(sql1);
                PreparedStatement ps4 = con.prepareStatement(sql4);
                PreparedStatement ps5 = con.prepareStatement(sql5))
        {
            System.out.println(sql4);
            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");
                    int area = 0;
                    
                    try (PreparedStatement ps2 = con.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()) {
                            String idref = null;
                            double distance = 999999.9D;
                            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");
                                    area = rset2.getInt("area");
                                }
                            }
                            if (idref != null) {
                                try (PreparedStatement ps3 = con.prepareStatement(sql3)) {
                                    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();
        }
    }
}