package osm.jp.coverage.busstop;
import osm.jp.api.RectArea;
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 java.util.logging.Level;
import java.util.logging.Logger;

import jp.co.areaweb.tools.database.*;
import osm.jp.api.Japan;

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

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

    /**
     * メイン
     *  OSM_EXISTのscoreを集計して,BUS_STOPのfixedをUPDATE
     *
     * @param args
     * */
    public static void main(String[] args) 
    {
        // HSQLディレクトリがなければ作る
        File dbdir = new File("database");
        if (!dbdir.isDirectory()) {
            dbdir.mkdir();
        }

        Connection con;
        try {
            con = DatabaseTool.openDb("database");
            try {
                new Busstop(con);
            }
            catch (SQLException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } catch (IOException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } catch (SAXException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } catch (TransformerException ex) {
                Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            }        finally {
                DatabaseTool.closeDb(con);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Busstop.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
	
    /**
     * 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 {
        String sqlStr1 = "SELECT idref,lat,lon,score,name FROM "+ HttpPOST.TABLE_NAME;
        String sqlStr2 = "SELECT gmlid,lat,lon,fixed,fixed1,area,name FROM "+ DbBusstop.TABLE_NAME +" WHERE (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)";
        String sqlStr3 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=? WHERE gmlid=? and area=?";
        String sqlStr4 = "UPDATE "+ DbBusstop.TABLE_NAME +" SET fixed1=0";
        String sqlStr5 = "UPDATE "+ DbBusstop.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();

            System.out.println(sqlStr1);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    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;

                    // 指定の緯度経度を中心とする半径200x2m四方の矩形領域
                    RectArea rect = new RectArea(lat, lon, NEER*2);		// 600m 四方
                    System.out.println(sqlStr2 +"["+ rect.minlat +", "+ rect.maxlat +", "+ rect.minlon +", "+ rect.maxlon +"]");
                    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()) {
                            System.out.print("{");
                            gmlid = rset2.getString("gmlid");
                            System.out.print("gmlid:"+gmlid +", ");
                            area = rset2.getInt("area");
                            System.out.print("area:"+area +", ");
                            fixed1 = rset2.getInt("fixed1");
                            fixed1 += score;
                            System.out.print("fixed1:"+fixed1 +", ");
                            String ksjName = rset2.getString("name");
                            System.out.print("ksjName:"+ksjName +", ");
                            double lat2 = rset2.getDouble("lat");
                            System.out.print("lat2:"+lat2 +", ");
                            double lon2 = rset2.getDouble("lon");
                            System.out.print("lon2:"+lon2 +", ");
                            
                            double dd = Japan.distanceKm(lat,lon,lat2,lon2);
                            System.out.print("dd:"+dd +" ");
                            System.out.println("}");
                            System.out.println("distance() -> "+ dd +"(km)");
                            if ( ((dd * 1000.0D) < NEER)
                             || (((dd * 1000.0D) < NEER*2) && (name != null) && (name.equals(ksjName)))) 
                            {
                                System.out.println(sqlStr3 +" ["+ fixed1 +", "+ gmlid +", "+ area +"]");
                                ps3.setInt(1, fixed1);
                                ps3.setString(2, gmlid);
                                ps3.setInt(3, area);
                                ps3.executeUpdate();
                            }
                        }
                    }
                }
            }

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