package mara.mybox.db.migration;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.xml.parsers.DocumentBuilderFactory;
import mara.mybox.controller.LoadingController;
import mara.mybox.db.DerbyBase;
import mara.mybox.db.data.*;
import static mara.mybox.db.migration.GeoCoordinateSystem.Value.GCJ_02;
import mara.mybox.dev.MyBoxLog;
import mara.mybox.fxml.FxTask;
import mara.mybox.tools.CsvTools;
import mara.mybox.tools.DoubleTools;
import mara.mybox.tools.FileTmpTools;
import mara.mybox.tools.FileTools;
import mara.mybox.tools.HtmlReadTools;
import mara.mybox.tools.TextFileTools;
import mara.mybox.tools.UrlTools;
import mara.mybox.value.AppValues;
import mara.mybox.value.Languages;
import mara.mybox.value.UserConfig;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import thridparty.CoordinateConverter;

/**
 * @Author Mara
 * @CreateDate 2020-8-11
 * @License Apache License Version 2.0
 */
public class GeographyCodeTools {


    /*
        Query codes by web service
     */
    public static GeographyCode geoCode(GeoCoordinateSystem coordinateSystem, String address) {
        try {
            if (coordinateSystem == null) {
                return null;
            }
            if (coordinateSystem.getValue() == GeoCoordinateSystem.Value.GCJ_02) {
                // GaoDe Map only supports info codes of China
                String urlString = "https://restapi.amap.com/v3/geocode/geo?address="
                        + URLEncoder.encode(address, "UTF-8") + "&output=xml&key="
                        + UserConfig.getString("GaoDeMapServiceKey", AppValues.GaoDeMapWebServiceKey);
                GeographyCode geographyCode = new GeographyCode();
                geographyCode.setChineseName(address);
                geographyCode.setCoordinateSystem(coordinateSystem);
                return gaodeCode(urlString, geographyCode);
            } else if (coordinateSystem.getValue() == GeoCoordinateSystem.Value.CGCS2000) {
                //  http://api.tianditu.gov.cn/geocoder?ds={"keyWord":"泰山"}&tk=0ddeb917def62b4691500526cc30a9b1
                String urlString = "http://api.tianditu.gov.cn/geocoder?ds="
                        + URLEncoder.encode("{\"keyWord\":\""
                                + address
                                + "\"}", "UTF-8") + "&tk="
                        + UserConfig.getString("TianDiTuWebKey", AppValues.TianDiTuWebKey);
                URL url = UrlTools.url(urlString);
                if (url == null) {
                    return null;
                }
                File jsonFile = FileTmpTools.getTempFile(".json");
                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                connection.setRequestMethod("GET");
                connection.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
                connection.connect();
                try (final BufferedInputStream inStream = new BufferedInputStream(connection.getInputStream());
                        final BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(jsonFile))) {
                    byte[] buf = new byte[AppValues.IOBufferLength];
                    int len;
                    while ((len = inStream.read(buf)) > 0) {
                        outputStream.write(buf, 0, len);
                    }
                }
                String data = TextFileTools.readTexts(null, jsonFile);
                double longitude = -200;
                double latitude = -200;
                String flag = "\"lon\":";
                int pos = data.indexOf(flag);
                if (pos >= 0) {
                    String subData = data.substring(pos + flag.length());
                    flag = ",";
                    pos = subData.indexOf(flag);
                    if (pos >= 0) {
                        try {
                            longitude = Double.parseDouble(subData.substring(0, pos));
                        } catch (Exception e) {
                        }
                    }
                }
                flag = "\"lat\":";
                pos = data.indexOf(flag);
                if (pos >= 0) {
                    String subData = data.substring(pos + flag.length());
                    flag = ",";
                    pos = subData.indexOf(flag);
                    if (pos >= 0) {
                        try {
                            latitude = Double.parseDouble(subData.substring(0, pos));
                        } catch (Exception e) {
                        }
                    }
                }
                if (validCoordinate(longitude, latitude)) {
                    return geoCode(coordinateSystem, longitude, latitude, true);
                }
            }
            return null;
        } catch (Exception e) {
            return null;
        }
    }

    public static GeographyCode geoCode(GeoCoordinateSystem coordinateSystem, double longitude, double latitude, boolean decodeAncestors) {
        try {
            GeographyCode geographyCode = TableGeographyCode.readCode(coordinateSystem, longitude, latitude, decodeAncestors);
            if (geographyCode != null) {
                return geographyCode;
            }
            return geoCode(coordinateSystem, longitude, latitude);
        } catch (Exception e) {
            return null;
        }
    }

    public static GeographyCode geoCode(GeoCoordinateSystem coordinateSystem, double longitude, double latitude) {
        try {
            if (coordinateSystem == null) {
                return null;
            }
            if (coordinateSystem.getValue() == GeoCoordinateSystem.Value.GCJ_02) {
                String urlString = "https://restapi.amap.com/v3/geocode/regeo?location="
                        + longitude + "," + latitude + "&output=xml&key="
                        + UserConfig.getString("GaoDeMapServiceKey", AppValues.GaoDeMapWebServiceKey);
                GeographyCode geographyCode = new GeographyCode();
                geographyCode.setLongitude(longitude);
                geographyCode.setLatitude(latitude);
                geographyCode.setCoordinateSystem(coordinateSystem);
                return gaodeCode(urlString, geographyCode);
            } else if (coordinateSystem.getValue() == GeoCoordinateSystem.Value.CGCS2000) {
                String urlString = "http://api.tianditu.gov.cn/geocoder?postStr={'lon':"
                        + longitude + ",'lat':" + latitude
                        + ",'ver':1}&type=geocode&tk="
                        + UserConfig.getString("TianDiTuWebKey", AppValues.TianDiTuWebKey);
                GeographyCode geographyCode = new GeographyCode();
                geographyCode.setLongitude(longitude);
                geographyCode.setLatitude(latitude);
                geographyCode.setCoordinateSystem(coordinateSystem);
                return tiandituCode(urlString, geographyCode);
            }
            return null;
        } catch (Exception e) {
            return null;
        }
    }

    //{"result":{"formatted_address":"泰安市泰山区升平街39号（泰山区区政府大院内）泰山区统计局",
    // "location":{"lon":117.12899999995,"lat":36.19280999998},
    // "addressComponent":{"address":"泰山区升平街39号（泰山区区政府大院内）","city":"泰安市",
    // "county_code":"156370902","nation":"中国","poi_position":"西北","county":"泰山区","city_code":"156370900",
    // "address_position":"西北","poi":"泰山区统计局","province_code":"156370000","province":"山东省","road":"运粮街",
    //"road_distance":65,"poi_distance":10,"address_distance":10}},"msg":"ok","status":"0"}
    public static GeographyCode tiandituCode(String urlString, GeographyCode geographyCode) {
        try {
            URL url = UrlTools.url(urlString);
            if (url == null) {
                return null;
            }
            File jsonFile = FileTmpTools.getTempFile(".json");
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("GET");
            connection.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
            connection.connect();
            try (final BufferedInputStream inStream = new BufferedInputStream(connection.getInputStream());
                    final BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(jsonFile))) {
                byte[] buf = new byte[AppValues.IOBufferLength];
                int len;
                while ((len = inStream.read(buf)) > 0) {
                    outputStream.write(buf, 0, len);
                }
            }
            String data = TextFileTools.readTexts(null, jsonFile);
//            MyBoxLog.debug(data);
            String flag = "\"formatted_address\":\"";
            int pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    String v = subData.substring(0, pos);
                    if (geographyCode.getChineseName() == null) {
                        geographyCode.setChineseName(v);
                    } else {
                        geographyCode.setAlias1(v);
                    }
                }
            }
            flag = "\"lon\":";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = ",";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    try {
                        double longitude = Double.parseDouble(subData.substring(0, pos));
                        geographyCode.setLongitude(longitude);
                    } catch (Exception e) {
                    }
                }
            }
            flag = "\"lat\":";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "}";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    try {
                        double latitude = Double.parseDouble(subData.substring(0, pos));
                        geographyCode.setLatitude(latitude);
                    } catch (Exception e) {
                    }
                }
            }
            flag = "\"address\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    String name = subData.substring(0, pos);
                    if (geographyCode.getChineseName() == null) {
                        geographyCode.setChineseName(name);
                    } else if (!geographyCode.getChineseName().equals(name)) {
                        geographyCode.setAlias1(geographyCode.getChineseName());
                        geographyCode.setChineseName(name);
                    }
                }
            }
            flag = "\"city\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCityName(subData.substring(0, pos));
                }
            }
            flag = "\"county_code\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCode3(subData.substring(0, pos));
                }
            }
            flag = "\"nation\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCountryName(subData.substring(0, pos));
                }
            }
            flag = "\"county\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCountyName(subData.substring(0, pos));
                }
            }
            flag = "\"city_code\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCode2(subData.substring(0, pos));
                }
            }
            flag = "\"poi\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    String name = subData.substring(0, pos);
                    if (geographyCode.getChineseName() == null) {
                        geographyCode.setChineseName(name);
                    } else if (!geographyCode.getChineseName().equals(name)) {
                        if (geographyCode.getAlias1() == null) {
                            geographyCode.setAlias1(geographyCode.getChineseName());
                            geographyCode.setChineseName(name);
                        } else if (!geographyCode.getAlias1().equals(name)) {
                            if (geographyCode.getAlias2() == null) {
                                geographyCode.setAlias2(geographyCode.getChineseName());
                                geographyCode.setChineseName(name);
                            } else if (!geographyCode.getAlias2().equals(name)) {
                                if (geographyCode.getAlias3() == null) {
                                    geographyCode.setAlias3(geographyCode.getChineseName());
                                    geographyCode.setChineseName(name);
                                } else if (!geographyCode.getAlias3().equals(name)) {
                                    if (geographyCode.getAlias4() == null) {
                                        geographyCode.setAlias4(geographyCode.getChineseName());
                                        geographyCode.setChineseName(name);
                                    } else if (!geographyCode.getAlias4().equals(name)) {
                                        geographyCode.setAlias5(geographyCode.getChineseName());
                                        geographyCode.setChineseName(name);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            flag = "\"province_code\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setCode1(subData.substring(0, pos));
                }
            }
            flag = "\"province\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setProvinceName(subData.substring(0, pos));
                }
            }
            flag = "\"road\":\"";
            pos = data.indexOf(flag);
            if (pos >= 0) {
                String subData = data.substring(pos + flag.length());
                flag = "\"";
                pos = subData.indexOf(flag);
                if (pos >= 0) {
                    geographyCode.setVillageName(subData.substring(0, pos));
                }
            }
            if (!validCoordinate(geographyCode)) {
                return null;
            }
            return geographyCode;
        } catch (Exception e) {
            MyBoxLog.debug(e);
            return null;
        }
    }

    // {"status":"1","info":"OK","infocode":"10000","count":"1","geocodes":
    // [{"formatted_address":"山东省泰安市泰山区泰山","country":"中国","province":"山东省","citycode":"0538",
    //"city":"泰安市","county":"泰山区","township":[],
    //"village":{"name":[],"type":[]},
    //"building":{"name":[],"type":[]},
    //"adcode":"370902","town":[],"number":[],"location":"117.157242,36.164988","levelCode":"兴趣点"}]}
    public static GeographyCode gaodeCode(String urlString, GeographyCode geographyCode) {
        try {
            URL url = UrlTools.url(urlString);
            if (url == null) {
                return null;
            }
            File xmlFile = HtmlReadTools.download(null, url.toString());
//            MyBoxLog.debug(FileTools.readTexts(xmlFile));
            Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(xmlFile);
            NodeList nodes = doc.getElementsByTagName("formatted_address");
            if (nodes != null && nodes.getLength() > 0) {
                String v = nodes.item(0).getTextContent();
                if (geographyCode.getChineseName() == null) {
                    geographyCode.setChineseName(v);
                } else {
                    geographyCode.setAlias1(v);
                }
            }
            nodes = doc.getElementsByTagName("country");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setCountryName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("province");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setProvinceName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("citycode");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setCode2(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("city");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setCityName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("district");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setCountyName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("township");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setTownName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("neighborhood");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setVillageName(nodes.item(0).getFirstChild().getTextContent());
            }
            nodes = doc.getElementsByTagName("building");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setBuildingName(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("adcode");
            if (nodes != null && nodes.getLength() > 0) {
                geographyCode.setCode1(nodes.item(0).getTextContent());
            }
            nodes = doc.getElementsByTagName("streetNumber");
            if (nodes != null && nodes.getLength() > 0) {
                nodes = nodes.item(0).getChildNodes();
                if (nodes != null) {
                    Map<String, String> values = new HashMap<>();
                    for (int i = 0; i < nodes.getLength(); i++) {
                        Node node = nodes.item(i);
                        values.put(node.getNodeName(), node.getTextContent());
                    }
                    String s = "";
                    String v = values.get("street");
                    if (v != null) {
                        s += v;
                    }
                    v = values.get("number");
                    if (v != null) {
                        s += v;
                    }
                    v = values.get("location");
                    if (v != null) {
                        s += v;
                    }
                    v = values.get("direction");
                    if (v != null) {
                        s += v;
                    }
                    v = values.get("distance");
                    if (v != null) {
                        s += v;
                    }
                    if (geographyCode.getBuildingName() == null) {
                        geographyCode.setBuildingName(s);
                    } else if (geographyCode.getAlias1() == null) {
                        geographyCode.setAlias1(s);
                    }
                }
            } else {
                nodes = doc.getElementsByTagName("street");
                if (nodes != null && nodes.getLength() > 0) {
                    String s = nodes.item(0).getTextContent();
                    if (geographyCode.getBuildingName() == null) {
                        geographyCode.setBuildingName(s);
                    } else if (geographyCode.getAlias1() == null) {
                        geographyCode.setAlias1(s);
                    } else if (geographyCode.getAlias2() == null) {
                        geographyCode.setAlias2(s);
                    }
                }
                nodes = doc.getElementsByTagName("number");
                if (nodes != null && nodes.getLength() > 0) {
                    geographyCode.setCode5(nodes.item(0).getTextContent());
                }
                if (!validCoordinate(geographyCode)) {
                    nodes = doc.getElementsByTagName("location");
                    if (nodes != null && nodes.getLength() > 0) {
                        String[] values = nodes.item(0).getFirstChild().getTextContent().split(",");
                        geographyCode.setLongitude(Double.parseDouble(values[0].trim()));
                        geographyCode.setLatitude(Double.parseDouble(values[1].trim()));
                    } else {
                        geographyCode.setLongitude(-200);
                        geographyCode.setLatitude(-200);
                    }
                }
                if (geographyCode.getChineseName() == null) {
                    geographyCode.setChineseName(geographyCode.getLongitude() + "," + geographyCode.getLatitude());
                }
                nodes = doc.getElementsByTagName("level");
                if (nodes != null && nodes.getLength() > 0) {
                    String v = nodes.item(0).getTextContent();
                    if (Languages.message("zh", "Country").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Country"));
                    } else if (Languages.message("zh", "Province").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Province"));
                    } else if (Languages.message("zh", "City").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("City"));
                    } else if (Languages.message("zh", "County").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("County"));
                    } else if (Languages.message("zh", "Town").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Town"));
                    } else if (Languages.message("zh", "Neighborhood").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Village"));
                    } else if (Languages.message("zh", "PointOfInterest").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Point Of Interest"));
                    } else if (Languages.message("zh", "Street").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Village"));
                    } else if (Languages.message("zh", "Building").equals(v)) {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Building"));
                    } else {
                        geographyCode.setLevelCode(new GeographyCodeLevel("Point Of Interest"));
                    }
                }
            }
            if (!validCoordinate(geographyCode)) {
                return null;
            }
            return geographyCode;
        } catch (Exception e) {
            MyBoxLog.debug(e);
            return null;
        }
    }

    public static GeographyCode encode(FxTask task, GeographyCode code) {
        if (code == null) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection();
                PreparedStatement geoInsert = conn.prepareStatement(TableGeographyCode.Insert)) {
            return encode(task, conn, geoInsert, code, true);
        } catch (Exception e) {
            MyBoxLog.error(e.toString());
        }
        return null;
    }

    public static GeographyCode encode(FxTask task, Connection conn, PreparedStatement geoInsert,
            GeographyCode code, boolean decodeAncestors) {
        if (code == null) {
            return null;
        }
        try {
            Map<String, Object> codeRet = encode(task, conn, geoInsert, code.getLevel(),
                    code.getLongitude(), code.getLatitude(),
                    code.getContinentName(), code.getCountryName(),
                    code.getProvinceName(), code.getCityName(), code.getCountyName(),
                    code.getTownName(), code.getVillageName(), code.getBuildingName(),
                    code.getName(), true, decodeAncestors);
            conn.commit();
//            if (codeRet.get("message") != null) {
//                MyBoxLog.error((String) codeRet.get("message"));
//            }
            if (codeRet.get("code") != null) {
                GeographyCode encoded = (GeographyCode) codeRet.get("code");
                return encoded;
            }
        } catch (Exception e) {
            MyBoxLog.error(e.toString());
        }
        return null;
    }

    // !! Caller responses for committing the update
    public static Map<String, Object> encode(FxTask task, Connection conn, PreparedStatement geoInsert,
            int level, double longitude, double latitude, String continent, String country, String province,
            String city, String county, String town, String village, String building, String poi,
            boolean create, boolean decodeAncestors) {
        Map<String, Object> ret = new HashMap<>();
        try {
            String msg = "";
            if (conn == null) {
                ret.put("message", "conn is null");
                return ret;
            }
            String sql;
            GeographyCode earch = TableGeographyCode.earth(conn);
            if (level == 1) {
                if (earch == null) {
                    importPredefined(task, conn, null);
                    earch = TableGeographyCode.earth(conn);
                }
                earch.setOwnerCode(earch);
                ret.put("code", earch);
                return ret;
            }
            GeographyCode continentCode = null;
            if (continent != null) {
                continentCode = TableGeographyCode.readCode(conn, 2, continent, decodeAncestors);
                if (continentCode == null) {
                    continentCode = new GeographyCode();
                    continentCode.setLevelCode(new GeographyCodeLevel("Continent"));
                    continentCode.setLongitude(longitude);
                    continentCode.setLatitude(latitude);
                    continentCode.setChineseName(continent);
                    continentCode.setEnglishName(continent);
                    continentCode.setOwnerCode(earch);
                    msg += "continent :" + continent + ", " + longitude + "," + latitude;
                    if (create) {
                        if (!TableGeographyCode.insert(conn, geoInsert, continentCode)) {
                            continentCode = null;
                            msg += " is not existed, and failed to create. ";
                        } else {
                            msg += " is not existed, and  created now.";
                            ret.put("inserted", "true");
                        }
                    } else {
                        msg += " is not existed, and will not be created.";
                    }
                }
            }
            if (level == 2) {
                ret.put("message", msg);
                ret.put("code", continentCode);
                return ret;
            }
            GeographyCode countryCode = null;
            if (country != null) {
                countryCode = TableGeographyCode.readCode(conn, 3, country, decodeAncestors);
                if (countryCode == null) {
                    countryCode = new GeographyCode();
                    countryCode.setLevelCode(new GeographyCodeLevel("Country"));
                    countryCode.setLongitude(longitude);
                    countryCode.setLatitude(latitude);
                    countryCode.setChineseName(country);
                    countryCode.setEnglishName(country);
                    if (continentCode != null) {
                        countryCode.setContinent(continentCode.getGcid());
                        countryCode.setOwner(continentCode.getGcid());
                    }
                    msg += "country :" + country + ", " + longitude + "," + latitude;
                    if (create) {
                        if (!TableGeographyCode.insert(conn, geoInsert, countryCode)) {
                            countryCode = null;
                            msg += " is not existed, and failed to create. ";
                        } else {
                            msg += " is not existed, and  created now.";
                            ret.put("inserted", "true");
                        }
                    } else {
                        msg += " is not existed, and will not be created.";
                    }
                }
            }
            if (level == 3) {
                ret.put("message", msg);
                ret.put("code", countryCode);
                return ret;
            }
            GeographyCode provinceCode = null;
            if (province != null) {
                if (countryCode != null && create) {
                    sql = "SELECT * FROM Geography_Code WHERE " + " level=4 AND country="
                            + countryCode.getGcid() + " AND (" + TableGeographyCode.nameEqual(province) + ")";
                    provinceCode = TableGeographyCode.queryCode(conn, sql, decodeAncestors);
                } else {
                    provinceCode = TableGeographyCode.readCode(conn, 4, province, decodeAncestors);
                }
                if (provinceCode == null) {
                    provinceCode = new GeographyCode();
                    provinceCode.setLevelCode(new GeographyCodeLevel("Province"));
                    provinceCode.setLongitude(longitude);
                    provinceCode.setLatitude(latitude);
                    provinceCode.setChineseName(province);
                    provinceCode.setEnglishName(province);
                    if (countryCode != null) {
                        provinceCode.setContinent(countryCode.getContinent());
