package osm.jp.postgis;
 
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.PipedWriter;
import java.io.PrintWriter;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.json.Json;
import javax.json.JsonArray;
import javax.json.JsonNumber;
import javax.json.JsonObject;
import javax.json.JsonReader;
import javax.json.JsonValue;
import jp.co.areaweb.tools.database.DatabaseTool;
import osm.jp.api.Coverage;
import osm.jp.api.Japan;
import osm.jp.api.RectArea;
 
public class Kml implements Runnable {
    private ToPostgis type;
    private final String name;
    private final double lat;
    private final double lon;
    private final double km;
    
    PipedWriter pipeOut;
 
    public static final int MAX_DATA_COUNT = 1000;
 
    /**
     * 
     * @param pipeOut
     * @param dbname
     * @param name
     * @param lat
     * @param lon
     * @param km 
     * @throws java.io.IOException 
     */
    public Kml(PipedWriter pipeOut, String dbname, String name, double lat, double lon, double km) throws IOException {
        this.name = name;
        this.lat = lat;
        this.lon = lon;
        this.km = km;
        
        this.type = null;
        if (dbname.equals(osm.jp.coverage.busstop.DbBusstop.TABLE_NAME)) {
            this.type = new osm.jp.coverage.busstop.ToPostgis();
        }
        else if (dbname.equals(osm.jp.coverage.fuel.DbFuel.TABLE_NAME)) {
            this.type = new osm.jp.coverage.fuel.ToPostgis();
        }
        else if (dbname.equals(osm.jp.coverage.police.DbPolice.TABLE_NAME)) {
            this.type = new osm.jp.coverage.police.ToPostgis();
        }
        else if (dbname.equals(osm.jp.coverage.postoffice.DbPostoffice.TABLE_NAME)) {
            this.type = new osm.jp.coverage.postoffice.ToPostgis();
        }
        
        this.pipeOut = pipeOut;
    }
 
    @Override
    @SuppressWarnings({"CallToPrintStackTrace", "SleepWhileInLoop"})
    public void run() {
        BufferedWriter bw = new BufferedWriter(pipeOut);
        try (PrintWriter ow = new PrintWriter(bw))
        {
            ow.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
            ow.println("<kml xmlns=\"http://www.opengis.net/kml/2.2\">");
            ow.println("<Document>");
            ow.flush();
            try {
                Thread.sleep(0);
            }
            catch (InterruptedException ex) {
                ex.printStackTrace();
            }
            
            int count = 0;
            try (Connection conPost = DatabaseTool.openDb(Coverage.DB_PORP_GISDB))
            {
                ow.println(getStringStyle());
                
                ow.println("<name>"+ name +"</name>");
                ow.println("<visibility>1</visibility>");
                ow.println(getStringExtendedData(name));
                ow.flush();
                try {
                    Thread.sleep(0);
                }
                catch(InterruptedException ex) {
                    ex.printStackTrace();
                }
                
                String sql = "SELECT area,gmlid,fixed,ST_Y(geom) AS lat, ST_X(geom) AS lon, ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry "
                        + "FROM t_%s "
                        + "WHERE (ST_Y(geom) > ?) and (ST_Y(geom) < ?) and (ST_X(geom) > ?) and (ST_X(geom) < ?)";
                sql = String.format(sql, type.tableName, type.tableName);
                try (PreparedStatement ps8 = conPost.prepareStatement(sql)) {
                    RectArea rect = new RectArea(lat, lon, (int)(km * 2000));
                    ps8.setDouble(1, rect.minlat);
                    ps8.setDouble(2, rect.maxlat);
                    ps8.setDouble(3, rect.minlon);
                    ps8.setDouble(4, rect.maxlon);
                    try (ResultSet rset8 = ps8.executeQuery()) {
                        while (rset8.next()) {
                            String gmlid = rset8.getString("gmlid");
                            int area = rset8.getInt("area");
                            int fixed = rset8.getInt("fixed");
                            
                            // geom: {"type":"Point","coordinates":[143.3147749,42.2984888]}
                            String geom = rset8.getString("geometry");
                            String lonStr = null;
                            String latStr = null;
                            try (JsonReader reader = Json.createReader(new StringReader(geom))) {
                                JsonObject geojson = reader.readObject();
                                JsonArray coordinates = geojson.getJsonArray("coordinates");
                                for (JsonValue v1 : coordinates) {
                                    if (v1.getValueType() == JsonValue.ValueType.NUMBER) {
                                        JsonNumber num = (JsonNumber)v1;
                                        if (lonStr == null) {
                                            lonStr = num.toString();
                                        }
                                        else {
                                            latStr = num.toString();
                                        }
                                    }
                                }
                            }
                            if ((lonStr != null) && (latStr != null)) {
                                double lat0 = Double.parseDouble(latStr);
                                double lon0 = Double.parseDouble(lonStr);
                                double dd = Japan.distanceKm(lat,lon,lat0,lon0);
                                if (dd < km) {
                                    count++;
                                    if (count <= MAX_DATA_COUNT) {
                                        String poiname = String.format("%02d - %s", area, gmlid);
                                        String color = decideColor(fixed);
                                        String str = getStringPlacemark(poiname, latStr, lonStr, color);
                                        ow.println(str);
                                        ow.flush();
                                        try {
                                            Thread.sleep(0);
                                        }
                                        catch(InterruptedException ex) {
                                            ex.printStackTrace();
                                        }
                                    }
                                    else {
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            ow.println("</Document>");
            ow.println("</kml>");
            ow.flush();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    String decideColor(int fixed) {
        if (this.type instanceof osm.jp.coverage.busstop.ToPostgis) {
            if (fixed == 0) {
                return "red";
            }
            else if (fixed < 100) {
                return "orange";
            }
            else {
                return "green";
            }
        }
        else if (this.type instanceof osm.jp.coverage.fuel.ToPostgis) {
            if (fixed == 0) {
                return "red";
            }
            else if (fixed < 50) {
                return "orange";
            }
            else {
                return "green";
            }
        }
        else if (this.type instanceof osm.jp.coverage.police.ToPostgis) {
            if (fixed == 0) {
                return "red";
            }
            else {
                return "green";
            }
        }
        else if (this.type instanceof osm.jp.coverage.postoffice.ToPostgis) {
            if (fixed == 0) {
                return "red";
            }
            else {
                return "green";
            }
        }
        return "brown";
    }
    
    String getStringStyle() {
        StringBuilder sbuf = new StringBuilder();
        sbuf.append(icon("red"));
        sbuf.append(icon("orange"));
        sbuf.append(icon("green"));
        sbuf.append(icon("brown"));
        return sbuf.toString();
    }
    
    /**
     * 
     * @param color ["red"|"blue"|"purple"|"yellow"|"pink"|"brown"|"green"|"orange"]
     * @return 
     */
    String icon(String color) {
        StringBuilder sbuf = new StringBuilder();
        sbuf.append(String.format("<Style id=\"placemark-%s\">", color));
        sbuf.append("<IconStyle>");
        sbuf.append("<Icon>");
        sbuf.append(String.format("<href>http://maps.me/placemarks/placemark-%s.png</href>", color));
        sbuf.append("</Icon>");
        sbuf.append("</IconStyle>");
        sbuf.append("</Style>");
        return sbuf.toString();
    }
    
    /**
     * 
        <ExtendedData xmlns:mwm="https://maps.me">
          <mwm:name>
            <mwm:lang code="default">poi</mwm:lang>
          </mwm:name>
          <mwm:annotation></mwm:annotation>
          <mwm:description></mwm:description>
          <mwm:lastModified>2018-12-23T01:50:40Z</mwm:lastModified>
          <mwm:accessRules>Local</mwm:accessRules>
        </ExtendedData>
    * 
    * 
    */
    String getStringExtendedData(String name) {
        StringBuilder sbuf = new StringBuilder();
        sbuf.append("<ExtendedData xmlns:mwm=\"https://maps.me\">");
        sbuf.append("<mwm:name><mwm:lang code=\"default\">");
        sbuf.append(name);
        sbuf.append("</mwm:lang></mwm:name>");
        sbuf.append("<mwm:annotation></mwm:annotation>");
        sbuf.append("<mwm:description></mwm:description>");
        
        sbuf.append("<mwm:lastModified>");
        sbuf.append((new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ")).format(new Date()));
        sbuf.append("</mwm:lastModified>");
        
        sbuf.append("<mwm:accessRules>Local</mwm:accessRules>");        
        sbuf.append("</ExtendedData>");
        return sbuf.toString();
    }
    
    /**
     * 
        <Placemark>
          <name>新日本海フェリー 苫小牧東港ターミナル</name>
          <TimeStamp><when>2017-12-28T11:15:56Z</when></TimeStamp>
          <styleUrl>#placemark-blue</styleUrl>
          <Point><coordinates>141.81955,42.610008</coordinates></Point>
          <ExtendedData xmlns:mwm="https://maps.me">
            <mwm:name>
              <mwm:lang code="default">新日本海フェリー 苫小牧東港ターミナル</mwm:lang>
            </mwm:name>
            <mwm:description>
            </mwm:description>
            <mwm:customName>
              <mwm:lang code="default">新日本海フェリー 苫小牧東港ターミナル</mwm:lang>
            </mwm:customName>
            <mwm:scale>13</mwm:scale>
          </ExtendedData>
        </Placemark>
     * 
     */
    String getStringPlacemark(String name, String latStr, String lonStr, String color) {
        StringBuilder sbuf = new StringBuilder();
        sbuf.append("<Placemark>");
        sbuf.append("<name>");
        sbuf.append(name);
        sbuf.append("</name>");
        sbuf.append("<TimeStamp><when>");
        sbuf.append((new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ")).format(new Date()));
        sbuf.append("</when></TimeStamp>");
        sbuf.append(String.format("<styleUrl>#placemark-%s</styleUrl>", color));
        sbuf.append("<Point><coordinates>");
        sbuf.append(String.format("%s,%s", lonStr, latStr));
        sbuf.append("</coordinates></Point>");
        sbuf.append(getStringPlacemarkExtendedData(name));
        sbuf.append("</Placemark>");
        return sbuf.toString();
    }
    
    /**
     * 
        <ExtendedData xmlns:mwm="https://maps.me">
          <mwm:name>
            <mwm:lang code="default">新日本海フェリー 苫小牧東港ターミナル</mwm:lang>
          </mwm:name>
          <mwm:description>
          </mwm:description>
          <mwm:customName>
            <mwm:lang code="default">新日本海フェリー 苫小牧東港ターミナル</mwm:lang>
          </mwm:customName>
          <mwm:scale>13</mwm:scale>
        </ExtendedData>
    * 
    */
    String getStringPlacemarkExtendedData(String name) {
        StringBuilder sbuf = new StringBuilder();
        sbuf.append("<ExtendedData xmlns:mwm=\"https://maps.me\">");
        sbuf.append("<mwm:name><mwm:lang code=\"default\">");
        sbuf.append(name);
        sbuf.append("</mwm:lang></mwm:name>");
        sbuf.append("<mwm:description></mwm:description>");
        sbuf.append("<mwm:customName><mwm:lang code=\"default\">");
        sbuf.append(name);
        sbuf.append("</mwm:lang></mwm:customName>");
        sbuf.append("<mwm:scale>13</mwm:scale>");
        sbuf.append("</ExtendedData>");
        return sbuf.toString();
    }
}