package osm.jp.coverage.busstop;
import osm.jp.api.HttpPOST;
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.coverage.fuel.DbFuel;

public class Busstop {
    // 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域
    static final int NEER = 150;		// 150m(0.15km)

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

    /**
     * メイン
     *  OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE
     *
     * @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
    {
        Connection con = DatabaseTool.openDb("database");
        try {
            new Busstop(con);
        }
        finally {
            DatabaseTool.closeDb(con);
        }
    }
	
    /**
     * OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE
     * 
     * @param con
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws TransformerException
     */
    public Busstop(Connection con) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
        try (   PreparedStatement ps2 = con.prepareStatement("SELECT gmlid,lat,lon,fixed,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
                PreparedStatement ps1 = con.prepareStatement("SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME);
                PreparedStatement ps3 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?");
                PreparedStatement ps4 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0");
                PreparedStatement ps5 = con.prepareStatement("UPDATE "+ DbBusstop.TABLE_NAME +" SET up=1 WHERE (fixed<>fixed1) and (up=0)" ))
        {
            System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0");
            ps4.executeUpdate();

            System.out.println("SELECT idref,lat,lon,score FROM "+ HttpPOST.TABLE_NAME);
            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 name = rset1.getString("name");
                    if ((name != null) && !name.trim().isEmpty()) {
                        score = 50;
                    }
                    String gmlid = null;
                    int area = 0;
                    int fixed1 = 0;

                    // 指定の緯度経度を中心とする半径150x2m四方の矩形領域
                    RectArea rect = new RectArea(lat, lon, NEER);		// 300m 四方
                    //System.out.println("SELECT gmlid,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
                    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;
                                gmlid = rset2.getString("gmlid");
                                area = rset2.getInt("area");
                                fixed1 = rset2.getInt("fixed1");
                            }
                        }
                    }
                    if (gmlid != null) {
                        score += fixed1;
                        System.out.println("UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1="+ score +" WHERE gmlid="+ gmlid +" and area=" + area);
                        ps3.setInt(1, score);
                        ps3.setString(2, gmlid);
                        ps3.setInt(3, area);
                        ps3.executeUpdate();
                    }
                    else {
                        System.out.println("NOT FOUND! osmid="+ 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);
    }
}