package osm.jp.api;
 
import java.net.*;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.SQLSyntaxErrorException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.hsqldb.HsqlException;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
 
public abstract class Osmdb {
    public static String host = "http://overpass-api.de";
    public static final String EXIST_FILE = "exist.osm.xml";
    public static final String TABLE_NAME = "OSM_EXIST"; 
 
    /*
    public static void main(String[] args) throws MalformedURLException, ProtocolException, IOException {
        double minlat = 35.13d;
        double maxlat = 35.66d;
        double minlon = 138.99d;
        double maxlon = 139.79d;
        //getCapabilities(new File("output.xml"), "highway", "bus_stop", minlat, maxlat, minlon, maxlon);
        //getCapabilities(new File("output.xml"), "highway", "disused:bus_stop", minlat, maxlat, minlon, maxlon);
        //getCapabilities(new File("output.xml"), "amenity", "bus_station", minlat, maxlat, minlon, maxlon);
        //getCapabilities(new File("output.xml"), "public_transport", "platform", minlat, maxlat, minlon, maxlon);
        getCapabilities("public_transport", "stop_position", minlat, maxlat, minlon, maxlon, "node");
        getCapabilities("amenity", "fuel", minlat, maxlat, minlon, maxlon, "way");
    }
    */
    
    /**
     * 'HSQLDB.table.OSM_EXIST'を新規に作る
     * 'HSQLDB.table.AREA_NODE'を新規に作る
     * 既にテーブルが存在する時にはERROR
     * @param con
     * @throws SQLException
     */
    public static void create(Connection con) throws SQLException {
        String createSt;
        
        sql(con, "DROP TABLE IF EXISTS "+ Osmdb.TABLE_NAME +" CASCADE");
        //sql(con, "DROP INDEX "+ HttpPOST.TABLE_NAME +"_index;");
        sql(con, "DROP TABLE IF EXISTS AREA_NODE CASCADE");
 
        // 'table.FUEL_EXIST'を新規に作る
        createSt = "CREATE TABLE "+ Osmdb.TABLE_NAME +" (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, PRIMARY KEY(idref));";
        Db.updateSQL(con, createSt);
        createSt = "CREATE INDEX "+ Osmdb.TABLE_NAME +"_index ON "+ Osmdb.TABLE_NAME +" (lat, lon);";
        Db.updateSQL(con, createSt);
 
        // 'table.AREA_NODE'を新規に作る
        createSt = "CREATE TABLE AREA_NODE (idref VARCHAR(12) NOT NULL, pid VARCHAR(12), lat DOUBLE, lon DOUBLE);";
        Db.updateSQL(con, createSt);
    }
    
    public static void sql(Connection con, String sql) throws SQLException {
        System.out.println(sql);
        try (PreparedStatement ps = con.prepareStatement(sql)) {
            ps.executeUpdate();
        }
        catch (SQLSyntaxErrorException e) {
            System.out.println("107:"+ e.toString());
            if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) {
                throw e;
            }
        }
    }
 
    /*
    */
    public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon) throws MalformedURLException, ProtocolException, IOException {
        getCapabilities(key, value, minLat, maxLat, minLon, maxLon, "node");
    }
 
    public static void getCapabilities(String key, String value, double minLat, double maxLat, double minLon, double maxLon, String type) throws MalformedURLException, ProtocolException, IOException {
        StringBuilder queryText = new StringBuilder();
        queryText.append("<osm-script timeout=\"900\" element-limit=\"1073741824\">");
        queryText.append(" <union>");
        queryText.append("  <query type=\""+ type +"\">");
        queryText.append("   <has-kv k=\""+ key +"\" v=\""+ value +"\"/>");
        queryText.append("   <bbox-query s=\"" + minLat + "\" n=\"" + maxLat + "\" w=\"" + minLon + "\" e=\"" + maxLon + "\"/>");
        queryText.append("  </query>");
        queryText.append(" </union>");
        queryText.append(" <print/>");
        queryText.append("</osm-script>");
        getQuery(queryText.toString());
    }
 
    /**
     *
     * @param queryText クエリテキスト（Overpass_API/Overpass_QL）
     * @throws MalformedURLException
     * @throws ProtocolException
     * @throws IOException
     */
    public static void getQuery(String queryText) throws MalformedURLException, ProtocolException, IOException {
        System.out.println(host + "/api/interpreter");
        URL url = new URL(host + "/api/interpreter");
        int responsecode = 0;
 
        do {
            HttpURLConnection urlconn = (HttpURLConnection)url.openConnection();
            try {
                urlconn.setRequestMethod("POST");
                urlconn.setDoOutput(true);              		// POSTのデータを後ろに付ける
                urlconn.setInstanceFollowRedirects(false);		// 勝手にリダイレクトさせない
                urlconn.setRequestProperty("Accept-Language", "ja;q=0.7,en;q=0.3");
                urlconn.setRequestProperty("Content-Type","text/xml;charset=utf-8");
                urlconn.connect();
 
                try (PrintWriter pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(urlconn.getOutputStream(), "utf-8")))) {
                    outputWriter(pw, queryText);
                    pw.flush();
                }
 
                try {
                    TimeUnit.SECONDS.sleep(1);
                } catch (InterruptedException e) {}
 
                responsecode = urlconn.getResponseCode();
                System.out.println("レスポンスコード[" + responsecode + "] " +
                                   "レスポンスメッセージ[" + urlconn.getResponseMessage() + "]");
                Map<String,List<String>> headers = urlconn.getHeaderFields();
                for (Map.Entry<String, List<String>> bar : headers.entrySet()) {
                    System.out.print("\t" + bar.getKey() +"\t: ");  // キーを取得
                    List<String> vals = bar.getValue();  // 値を取得
                    for(String str : vals) {
                        System.out.print("["+ str +"],");
                    }
                    System.out.println();
                }
                if ((responsecode == 429) || (responsecode == 504) || (responsecode == 500)) {
                    // レスポンスコード[429] レスポンスメッセージ[Too Many Requests]
                    // レスポンスコード[500] レスポンスメッセージ[Internal server error]
                    // レスポンスコード[504] レスポンスメッセージ[Gateway Timeout]
                    System.out.print("Waite 5 minites.");
                    try {
                        TimeUnit.MINUTES.sleep(5);
                    } catch (InterruptedException e) {}
                }
                else {
                    System.out.println("\n---- ボディ ----");
 
                    File oFile = new File(Osmdb.EXIST_FILE);
                    oFile.deleteOnExit();
                    try (
                            BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(oFile), "UTF-8"));
                            BufferedReader reader = new BufferedReader(new InputStreamReader(urlconn.getInputStream(), "UTF-8"))
                    ) {
                        while (true) {
                            String line = reader.readLine();
                            if (line == null) {
                                break;
                            }
                            hw.write(line);
                            hw.newLine();
                        }
                        hw.flush();
                    }
                }
            }
            catch (java.net.ConnectException e) {
                // レスポンスコード[600] レスポンスメッセージ[接続がタイムアウトしました (Connection timed out)]
                responsecode = 600;
            }
            finally {
                urlconn.disconnect();
            }
            System.out.print("Waite 5 seconds.");
            try {
                TimeUnit.SECONDS.sleep(5);
            } catch (InterruptedException e) {}
        }
        while ((responsecode == 429) || (responsecode == 504) || (responsecode == 600));
    }
	
    public static void outputWriter(PrintWriter pw, String text) {
        System.out.println("\t" + text);
        pw.print(text);
    }
    
    public static final int POINT_NO = 0;       // 評価ポイント無し→ score=50
    public static final int POINT_FIXME = 1;    // 評価ポイント無し→ score=50
    public static final int POINT_BRAND = 2;    // 評価ポイント｜brand=null → score=1
    public static final int POINT_NAME = 4;     // 評価ポイント｜name=null → score=1
    
    /**
     * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
     * その際に、OSMノードを評価し、scoreを算定する
     * @param con       反映先のデータベースコネクタ(HSQLDB)
     * @param point     評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand'  2:'name'
     * @throws FileNotFoundException    File(HttpPOST.EXIST_FILE)が存在しない
     * @throws ClassNotFoundException
     * @throws SQLException             データベースエラー
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException 
     */
    public void readExistingFile (Connection con, int point) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
 
        DocumentBuilderFactory factory;
        DocumentBuilder        builder;
 
        factory = DocumentBuilderFactory.newInstance();
        builder = factory.newDocumentBuilder();
        factory.setIgnoringElementContentWhitespace(true);
        factory.setIgnoringComments(true);
        factory.setValidating(true);
 
        Node root = builder.parse(new File(Osmdb.EXIST_FILE));
 
        readAreaNodes(con, root);
        int iCounter = readExistingNodes(con, root, point);
        System.out.println("既存ノード数["+ iCounter +"]");
    }
    
    public int readAreaNodes(Connection con, Node node) throws IOException, SQLException {
        int iCounter = 0;
 
        NodeList nodes = node.getChildNodes();
        for (int i = 0; i < nodes.getLength(); i++) {
            Node node2 = nodes.item(i);
            switch (node2.getNodeName()) {
                case "node":
                    iCounter++;
                    importAreaNode(con, node2);
                    break;
                default:
                    iCounter += readAreaNodes(con, node2);
                    break;
            }
        }
        return iCounter;
    }
    
    public void importAreaNode(Connection con, Node node) throws IOException, SQLException {
        String idrefStr = "";
        String latStr = "";
        String lonStr = "";
 
        NodeList nodes = node.getChildNodes();
        if (nodes.getLength() > 0) {
            return;
        }
 
        NamedNodeMap nodeMap = node.getAttributes();
        if (nodeMap != null) {
            for (int j=0; j < nodeMap.getLength(); j++) {
                switch (nodeMap.item(j).getNodeName()) {
                    case "id":
                        idrefStr = nodeMap.item(j).getNodeValue();
                        break;
                    case "lat":
                        latStr = nodeMap.item(j).getNodeValue();
                        break;
                    case "lon":
                        lonStr = nodeMap.item(j).getNodeValue();
                        break;
                    default:
                        break;
                }
            }
            
            // idref と brandStr をデータベースに格納する
            System.out.println("insert into AREA_NODE(idref,lat,lon) values("+ idrefStr +","+ latStr +","+ lonStr+")");
            try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO AREA_NODE (idref,lat,lon) VALUES (?,?,?)")) {
                ps5.setString(1, idrefStr);
                ps5.setDouble(2, Double.parseDouble(latStr));
                ps5.setDouble(3, Double.parseDouble(lonStr));
                ps5.executeUpdate();
            }
        }
    }
 
    public int readExistingNodes(Connection con, Node node, int point) throws IOException, SQLException {
        int iCounter = 0;
 
        NodeList nodes = node.getChildNodes();
        for (int i = 0; i < nodes.getLength(); i++) {
            Node node2 = nodes.item(i);
            switch (node2.getNodeName()) {
                case "node":
                    iCounter++;
                    importExistingNode(con, node2, point);
                    break;
                case "way":
                    iCounter++;
                    importExistingArea(con, node2, point);
                    break;
                default:
                    iCounter += readExistingNodes(con, node2, point);
                    break;
            }
        }
        return iCounter;
    }
 
    public void importExistingNode(Connection con, Node node, int point) throws IOException, SQLException {
        String idrefStr = "";
        String latStr = "";
        String lonStr = "";
        String brandStr = "";
        String nameStr = "";
        String fixmeStr = "";
 
        NamedNodeMap nodeMap = node.getAttributes();
        if (nodeMap != null) {
            for (int j=0; j < nodeMap.getLength(); j++) {
                switch (nodeMap.item(j).getNodeName()) {
                    case "id":
                        idrefStr = nodeMap.item(j).getNodeValue();
                        break;
                    case "lat":
                        latStr = nodeMap.item(j).getNodeValue();
                        break;
                    case "lon":
                        lonStr = nodeMap.item(j).getNodeValue();
                        break;
                    default:
                        break;
                }
            }
 
            NodeList nodes = node.getChildNodes();
            if (nodes.getLength() == 0) {
                return;
            }
            for (int i = 0; i < nodes.getLength(); i++) {
                Node node2 = nodes.item(i);
                if (node2.getNodeName().equals("tag")) {
                    OsmnodeTag tagnode = new OsmnodeTag(node2);
                    String value = tagnode.getValue("brand");
                    if (value != null) {
                        brandStr = value;
                    }
                    value = tagnode.getValue("name");
                    if (value != null) {
                        nameStr = value;
                    }
                    value = tagnode.getValue("name:ja");
                    if (value != null) {
                        nameStr = value;
                    }
                    value = tagnode.getValue("fixme");
                    if (value != null) {
                        fixmeStr = value;
                    }
                }
            }
 
            int score = 50;
            if (((point & POINT_BRAND) != 0) && brandStr.equals("")) {
                score = 1;
            }
            if (((point & POINT_NAME) != 0) && brandStr.equals("")) {
                score = 1;
            }
            if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) {
                score = 1;
            }
            
            // idref と brandStr をデータベースに格納する
            System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES ("+ idrefStr +","+ latStr +","+ lonStr+","+ Integer.toString(score) +",'"+ nameStr +"')N");
            try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) {
                ps5.setString(1, idrefStr);
                ps5.setDouble(2, Double.parseDouble(latStr));
                ps5.setDouble(3, Double.parseDouble(lonStr));
                ps5.setInt(4, score);
                ps5.setString(5, nameStr);
                ps5.executeUpdate();
            }
            catch (HsqlException | SQLIntegrityConstraintViolationException e) {
                // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
                // [HsqlException]は、無視する
                // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
                // [SQLIntegrityConstraintViolationException]は、無視する
            }
        }
    }
 
    public void importExistingArea(Connection con, Node node, int point) throws IOException, SQLException {
        String idrefStr = "";
        double maxlat = -90.0D;
        double maxlon = -180.0D;
        double minlat = 90.0D;
        double minlon = 180.0D;
        String nameStr = "";
        String brandStr = "";
        String fixmeStr = "";
 
        NamedNodeMap nodeMap = node.getAttributes();
        if (nodeMap != null) {
            for (int j=0; j < nodeMap.getLength(); j++) {
                switch (nodeMap.item(j).getNodeName()) {
                    case "id":
                        idrefStr = nodeMap.item(j).getNodeValue();
                        break;
                    default:
                        break;
                }
            }
 
            NodeList nodes = node.getChildNodes();
            for (int i = 0; i < nodes.getLength(); i++) {
                Node node2 = nodes.item(i);
                if (node2.getNodeName().equals("tag")) {
                    OsmnodeTag tagnode = new OsmnodeTag(node2);
                    String value = tagnode.getValue("brand");
                    if (value != null) {
                        brandStr = value;
                    }
                    value = tagnode.getValue("name:ja");
                    if (value != null) {
                        nameStr = value;
                    }
                    value = tagnode.getValue("name");
                    if (value != null) {
                        nameStr = value;
                    }
                    value = tagnode.getValue("fixme");
                    if (value != null) {
                        fixmeStr = value;
                    }
                }
                else if (node2.getNodeName().equals("nd")) {
                    OsmnodeNd ndnode = new OsmnodeNd(node2);
                    String ref = ndnode.getRef();
                    Position pos = getNdPosition(con, ref);
                    if (pos != null) {
                        minlat = (pos.lat < minlat ? pos.lat : minlat);
                        minlon = (pos.lon < minlon ? pos.lon : minlon);
                        maxlat = (pos.lat > maxlat ? pos.lat : maxlat);
                        maxlon = (pos.lon > maxlon ? pos.lon : maxlon);
                    }
                }
            }
            if ((maxlat != -90.0D) && (maxlon != -180.0D) && (minlon != 180.0D) && (minlat != 90.0D)) {
                double lat = (maxlat + minlat) / 2;
                double lon = (maxlon + minlon) / 2;
                int score = 50;
 
                if (((point & POINT_BRAND) != 0) && brandStr.equals("")) {
                    score = 1;
                }
                if (((point & POINT_NAME) != 0) && nameStr.equals("")) {
                    score = 1;
                }
                if (((point & POINT_FIXME) != 0) && !fixmeStr.equals("")) {
                    score = 1;
                }
 
                // idref と nameStr をデータベースに格納する
                System.out.println("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES("+ idrefStr +","+ lat +","+ lon+","+ Integer.toString(score) +",'"+ nameStr +"');");
                try (PreparedStatement ps5 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (idref,lat,lon,score,name) VALUES (?,?,?,?,?)")) {
                    ps5.setString(1, idrefStr);
                    ps5.setDouble(2, lat);
                    ps5.setDouble(3, lon);
                    ps5.setInt(4, score);
                    ps5.setString(5, nameStr);
                    ps5.executeUpdate();
                }
                catch (HsqlException | SQLIntegrityConstraintViolationException e) {
                    // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
                    // [HsqlException]は、無視する
                    // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
                    // [SQLIntegrityConstraintViolationException]は、無視する
                }
            }
        }
    }
    
    public static Position getNdPosition(Connection con, String idref) throws SQLException {
        PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM AREA_NODE where idref=?");
        ps8.setString(1, idref);
        try (ResultSet rset8 = ps8.executeQuery()) {
            while (rset8.next()) {
                Double lat = rset8.getDouble(1);
                Double lon = rset8.getDouble(2);
                return new Position(lat,lon);
            }
        }
        return null;
    }
}