package osm.jp.api;
 
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
 
public abstract class OsmExist extends Osmdb {
    public ArrayList<OsmnodeNode> nodelist = new ArrayList<>();
 
 
    public OsmExist(Connection hsqldb, String tableName) {
        super(hsqldb, tableName);
    }
    
    /**
     * 'HSQLDB.table.OSM_EXIST'を新規に作る
     * 'HSQLDB.table.AREA_NODE'を新規に作る
     * 既にテーブルが存在する時にはERROR
     * @throws SQLException
     */
    @Override
    public void create() throws SQLException {
        String createSt;
        
        sql("DROP TABLE IF EXISTS "+ tableName +" CASCADE");
        sql("DROP INDEX IF EXISTS "+ tableName +"_index;");
        sql("DROP TABLE IF EXISTS AREA_NODE CASCADE");
 
        // 'table.OSM_EXIST'を新規に作る
        createSt = "CREATE TABLE "+ tableName 
            + " ("
            +   "idref VARCHAR(12) NOT NULL, "
            +   "name VARCHAR(128), "
            +   "lat DOUBLE, "
            +   "lon DOUBLE, "
            +   "score INT, "
            +   "gmlid VARCHAR(12), "
            +   "area INT,  "
            +   "PRIMARY KEY(idref), "
            +   "removed BOOLEAN DEFAULT FALSE NOT NULL"
            + ");";
        Db.updateSQL(hsqldb, createSt);
        createSt = "CREATE INDEX "+ tableName +"_index ON "+ tableName 
            + " (lat, lon);";
        Db.updateSQL(hsqldb, createSt);
 
        // 'table.AREA_NODE'を新規に作る
        createSt = "CREATE TABLE AREA_NODE "
            + "("
            +   "idref VARCHAR(12) NOT NULL, "
            +   "pid VARCHAR(12), "
            +   "lat DOUBLE, "
            +   "lon DOUBLE"
            + ");";
        Db.updateSQL(hsqldb, createSt);
    }
    
    /*
    Test data:
    ノード: エネオス (2015835273)  場所: 35.4367770, 139.403571TABLE_NAME0
    ノード: ENEOS (1769261234) 場所: 35.4330583, 139.4006876 brand=no
    ノード: 出光 (3877535257) 場所: 45.3985390, 141.6882450 brand=no
    
    select osm_id,amenity,brand,disused,name,
        ST_Y(ST_Transform(way,4326)) as lat,
        ST_X(ST_Transform(way,4326)) as lon
    from planet_osm_point
    where amenity='fuel';
    
    
    ウェイ: 出光 (161877397) ノード
                                1738352013
                                1738351984
                                1738352019
                                1738352024
                                1738352017
                                1738352013
    select osm_id,amenity,brand,name,way_area,
            ST_Astext(ST_Transform(way,4326)) ,
            ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,
            ST_X(ST_Transform(ST_Centroid(way),4326)) as lon
    from planet_osm_polygon 
    where amenity='fuel';
    
    161877397;"fuel";"出光";"出光";1415.14;"POLYGON((139.402982078119 35.4372453832977,139.403208992559 35.4373490207424,139.4033330499 35.4371591650393,139.403407160911 35.4370741177365,139.403148446109 35.4369273706731,139.402982078119 35.4372453832977))"
    ST_Centroid(way)
    
    */
    /**
     * 
     * <pre>{@code 
     *  (
     *    node[disused:amenity=fuel](35.42,139.39,35.45,139.42);
     *    way[amenity=fuel](35.42,139.39,35.45,139.42);
     *    node[amenity=fuel](35.42,139.39,35.45,139.42);
     *    (way[amenity=fuel](35.42,139.39,35.45,139.42);>;);
     *  );
     *  out;
     * }</pre>
     * 
     * <pre>{@code 
     *  select osm_id,amenity,brand,disused,name from planet_osm_point where amenity='fuel';
     * }</pre>
     *
     * <pre>{@code 
     *  select osm_id,amenity,brand,name,way_area,ST_Astext(ST_Transform(way,4326)) from planet_osm_polygon where amenity='fuel';
     * 
     * 168977587;"fuel";"JA";"兼城SS";1378;"POLYGON((127.692529751123 26.1483225993078,127.692852156479 26.1482644594179,127.692800683013 26.1478020809547,127.692690280065 26.1478324815483,127.692623984397 26.1480452048431,127.692529751123 26.1483225993078))"
     * 
     * 
     *  select id,nodes,tags from planet_osm_ways;
     * }</pre>
     * 
     * @param hsqldb
     * @param osmdb
     * @param features
     * @return XML
     * @throws Exception
     */
    /**
     * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
     * その際に、OSMノードを評価し、scoreを算定する
     * 
     * @param features
     * @param boxes
     * @throws Exception
     */
    public void getJapanCapabilities(ArrayList<Feature> features, Japan[] boxes) throws Exception {
        
        /*
            ```
            (node(changed:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.a;
            (node(newer:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.b;
            (way(changed:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.c;
            (way(newer:"2018-05-20T09:00:00Z")(34.0,138.0,36.0,140.0);)->.d;
            (
             node.a.b["amenity"="fuel"];
             node.a.b["disused:amenity"="fuel"];
             node.a.b["abandoned:amenity"="fuel"];
             node.a.b["demolished:amenity"="fuel"];
             node.a.b["historic:amenity"="fuel"];
             node.a.b["was:amenity"="fuel"];
             node.a.b["removed:amenity"="fuel"];
             node.a.b["no:amenity"="fuel"];
             way.c.d["amenity"="fuel"];
             way.c.d["disused:amenity"="fuel"];
             way.c.d["abandoned:amenity"="fuel"];
             way.c.d["demolished:amenity"="fuel"];
             way.c.d["historic:amenity"="fuel"];
             way.c.d["was:amenity"="fuel"];
             way.c.d["removed:amenity"="fuel"];
             way.c.d["no:amenity"="fuel"];
            );
            (._;>;);
            out meta;
            ```
        */
        Calendar now = Calendar.getInstance();
        now.add(Calendar.DATE, -7);     // before 1 week
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String startDate = sdf.format(now.getTime()) +"T09:00:00Z";
        
        StringBuilder sb = new StringBuilder();
        for (Japan box : boxes) {
            String boxq = String.format("(%2.1f,%3.1f,%2.1f,%3.1f)", box.minLat, box.minLon, box.maxLat, box.maxLon);
            sb.append(String.format("(node(changed:\"%s\")%s;)->.a;\n", startDate, boxq));
            sb.append(String.format("(node(newer:\"%s\")%s;)->.b;\n", startDate, boxq));
            sb.append(String.format("(way(changed:\"%s\")%s;)->.c;\n", startDate, boxq));
            sb.append(String.format("(way(newer:\"%s\")%s;)->.d;\n", startDate, boxq));
            sb.append("(");
            int point = 0;
            for (Feature f : features) {
                if (f.node == Feature.NODE) {
                    sb.append("node.a.b");
                }
                else if (f.node == Feature.AREA) {
                    sb.append("way.c.d");
                }
                sb.append(String.format("[\"%s\"=\"%s\"];\n", f.key, f.v));
                point += f.point;
            }
            sb.append(");\n");
            sb.append("(._;>;);\n");
            sb.append("out body;");
 
            /*--------------------------------------------
                Overpass API を実行して XMLを受信する
            ---------------------------------------------*/
            HttpPOST.getQuery(sb.toString());
            File xmlFile = new File(HttpPOST.EXIST_FILE);
 
            /*--------------------------------------------
                受信したXMLファイルをパースする
            ---------------------------------------------*/
            DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
            Document document = documentBuilder.parse(xmlFile);
            Node osmNode = document.getDocumentElement();
            Node itemNodes = osmNode.getFirstChild();
            while(itemNodes != null) {
                String nodeName = itemNodes.getNodeName();
                switch (nodeName) {
                case "node":
                    OsmnodeNode osmnode = new OsmnodeNode(itemNodes);
                    nodelist.add(osmnode);
                    if (osmnode.tags.size() > 0) {
                        insertExistingNode(osmnode.id, Double.parseDouble(osmnode.latStr), Double.parseDouble(osmnode.lonStr), score(point, osmnode.tags), "", osmnode.isRemoved());
                    }
                    break;
                case "way":
                    OsmnodeArea osmway = new OsmnodeArea(itemNodes);
                    osmway.setPosition(nodelist);
                    if (osmway.tags.size() > 0) {
                        insertExistingNode(osmway.id, Double.parseDouble(osmway.latStr), Double.parseDouble(osmway.lonStr), score(point, osmway.tags), "", osmway.isRemoved());
                    }
                    break;
                }
                itemNodes = itemNodes.getNextSibling();
            }
        }
        
    }
    
    /**
     * 
     * @param point
     * @param tags
     * @return 
     */
    int score(int point, ArrayList<OsmnodeTag> tags) {
        int score = 50;
        if (tags == null) {
            return 0;
        }
        
        boolean brandYes = false;
        boolean busYes = false;
        boolean fixmeYes = false;
        boolean nameYes = false;
        for (OsmnodeTag tag : tags) {
            if (tag.key.startsWith("fixme")) {
                fixmeYes = true;
            }
            else if (tag.key.equals("bus")) {
                if (tag.value.equals("yes")) {
                    busYes = true;
                }
            }
            else if (tag.key.equals("brand")) {
                brandYes = true;
            }
            else if (tag.key.startsWith("name")) {
                nameYes = true;
            }
        }
        
        if (((point & POINT_NO_BRAND) != 0) && !brandYes) {
            score = 1;
        }
        if (((point & POINT_NO_NAME) != 0) && !nameYes) {
            score = 1;
        }
        if (((point & POINT_FIXME) != 0) && fixmeYes) {
            score = 1;
        }
        if (((point & POINT_BUS_NO) != 0) && !busYes) {
            score = 0;
        }
        return score;
    }
}