package mara.mybox.db.migration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import mara.mybox.db.Database;
import mara.mybox.db.DerbyBase;
import static mara.mybox.db.DerbyBase.stringValue;
import mara.mybox.db.data.ColumnDefinition;
import mara.mybox.db.data.ColumnDefinition.ColumnType;
import mara.mybox.db.table.BaseTable;
import mara.mybox.dev.MyBoxLog;
import mara.mybox.value.Languages;

/**
 * @Author Mara
 * @CreateDate 2020-2-3
 * @License Apache License Version 2.0
 */
public class TableGeographyCode extends BaseTable<GeographyCode> {

    /**
     * One of following can determine an address: 1. gcid. This is accurate
     * matching. 2. level + chinese_name/english_name/alias + ancestors. This is
     * accurate matching. 3. level + chinese_name/english_name/alias. This is
     * fuzzy matching. Duplaited names can happen.
     *
     * Notice: coordinate can not determine an address
     *
     * Extra information, like higher level attribuets, can avoid wrong matching
     * due to duplicated names. Example, 2 cities have same name "A" while they
     * have different country names, then distinct them by appending country
     * name.
     *
     * Should not run batch insert/update of external data because each new data
     * need compare previous data. External data may include unexpected data
     * inconsistent.
     */
    public TableGeographyCode() {
        tableName = "Geography_Code";
        defineColumns();
    }

    public TableGeographyCode(boolean defineColumns) {
        tableName = "Geography_Code";
        if (defineColumns) {
            defineColumns();
        }
    }

    public final TableGeographyCode defineColumns() {
        addColumn(new ColumnDefinition("gcid", ColumnType.Long, true, true).setAuto(true).setMinValue((long) 0));
        addColumn(new ColumnDefinition("level", ColumnType.Short, true).setMaxValue((short) 10).setMinValue((short) 1));
        addColumn(new ColumnDefinition("coordinate_system", ColumnType.Short));
        addColumn(new ColumnDefinition("longitude", ColumnType.Double, true).setMaxValue((double) 180).setMinValue((double) -180));
        addColumn(new ColumnDefinition("latitude", ColumnType.Double, true).setMaxValue((double) 90).setMinValue((double) -90));
        addColumn(new ColumnDefinition("altitude", ColumnType.Double));
        addColumn(new ColumnDefinition("precision", ColumnType.Double));
        addColumn(new ColumnDefinition("chinese_name", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("english_name", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("continent", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("country", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("province", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("city", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("county", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("town", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("village", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("building", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("code1", ColumnType.String).setLength(1024));
        addColumn(new ColumnDefinition("code2", ColumnType.String).setLength(1024));
        addColumn(new ColumnDefinition("code3", ColumnType.String).setLength(1024));
        addColumn(new ColumnDefinition("code4", ColumnType.String).setLength(1024));
        addColumn(new ColumnDefinition("code5", ColumnType.String).setLength(1024));
        addColumn(new ColumnDefinition("alias1", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("alias2", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("alias3", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("alias4", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("alias5", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("area", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("population", ColumnType.Long).setMinValue((long) 0));
        addColumn(new ColumnDefinition("owner", ColumnType.Long).setMinValue((long) 0)
                .setReferName("Geography_Code_owner_fk").setReferTable("Geography_Code").setReferColumn("gcid"));
        addColumn(new ColumnDefinition("comments", ColumnType.String).setLength(StringMaxLength));
        addColumn(new ColumnDefinition("gcsource", ColumnType.Short).setMinValue((short) 0));
        return this;
    }

    public static long MaxID = -1;

    public static final String Create_Index_levelIndex
            = " CREATE INDEX  Geography_Code_level_index on Geography_Code ( "
            + "  level, continent, country ,province ,city ,county ,town , village , building "
            + " )";

    public static final String Create_Index_codeIndex
            = " CREATE INDEX  Geography_Code_code_index on Geography_Code ( "
            + "  level, country ,province ,city ,county ,town , village , building "
            + " )";

    public static final String Create_Index_gcidIndex
            = " CREATE INDEX  Geography_Code_gcid_index on Geography_Code ( "
            + "  gcid DESC, level, continent, country ,province ,city ,county ,town , village , building "
            + " )";

    public static final String AllQeury
            = "SELECT * FROM Geography_Code ORDER BY gcid";

    public static final String PageQeury
            = "SELECT * FROM Geography_Code ORDER BY gcid "
            + " OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

    public static final String GCidQeury
            = "SELECT * FROM Geography_Code WHERE gcid=?";

    public static final String NameEqual
            = "( chinese_name IS NOT NULL AND LCASE(chinese_name)=? ) OR "
            + " ( english_name IS NOT NULL AND LCASE(english_name)=? ) OR "
            + " ( alias1 IS NOT NULL AND LCASE(alias1)=? ) OR "
            + " ( alias2 IS NOT NULL AND LCASE(alias2)=? ) OR "
            + " ( alias3 IS NOT NULL AND LCASE(alias3)=? ) OR "
            + " ( alias4 IS NOT NULL AND LCASE(alias4)=? ) OR "
            + " ( alias5 IS NOT NULL AND LCASE(alias5)=? )";

    public static final String LevelNameEqual
            = "level=? AND  ( " + NameEqual + " )";

    public static final String LevelNameQeury
            = "SELECT * FROM Geography_Code WHERE " + LevelNameEqual;

    public static final String CoordinateQeury
            = "SELECT * FROM Geography_Code WHERE coordinate_system=? AND longitude=? AND latitude=?";

    public static final String MaxLevelGCidQuery
            = "SELECT max(gcid) FROM Geography_Code WHERE level=?";

    public static final String GCidExistedQuery
            = "SELECT gcid FROM Geography_Code where gcid=?";

    public static final String MaxGCidQuery
            = "SELECT max(gcid) FROM Geography_Code";

    public static final String FirstChildQuery
            = "SELECT gcid FROM Geography_Code WHERE "
            + " owner=? OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY";

    public static final String ChildrenQuery
            = "SELECT * FROM Geography_Code WHERE owner=? ORDER BY gcid";

    public static final String LevelSizeQuery
            = "SELECT count(gcid) FROM Geography_Code WHERE level=?";

    public static final String Update
            = "UPDATE Geography_Code SET "
            + " level=?, longitude=?, latitude=?, gcsource=?, chinese_name=?, english_name=?, "
            + " code1=?, code2=?, code3=?, code4=?, code5=?,alias1=?, alias2=?, alias3=?, alias4=?, alias5=?, "
            + " area=?, population=?, comments=?, "
            + " continent=?, country=?, province=? , city=?, county=?, town=?, village=? , building=?, "
            + " owner=?, altitude=? , precision=?, coordinate_system=? "
            + " WHERE gcid=?";

    public static final String Insert
            = "INSERT INTO Geography_Code( "
            + " gcid, level, gcsource, longitude, latitude, chinese_name, english_name,"
            + " code1, code2, code3,  code4, code5, alias1, alias2, alias3, alias4, alias5, "
            + " area, population, comments,"
            + " continent, country, province, city, county, town, village, building,"
            + " owner, altitude, precision,coordinate_system  ) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";

    public static final String Delete
            = "DELETE FROM Geography_Code WHERE gcid=?";

    @Override
    public boolean setValue(GeographyCode data, String column, Object value) {
        if (data == null || column == null) {
            return false;
        }
        return data.setValue(column, value);
    }

    @Override
    public Object getValue(GeographyCode data, String column) {
        if (data == null || column == null) {
            return null;
        }
        return data.getValue(column);
    }

    @Override
    public boolean valid(GeographyCode data) {
        if (data == null) {
            return false;
        }
        return data.valid();
    }

    private static long generateID(Connection conn) {
        try (Statement statement = conn.createStatement()) {
            try (ResultSet results = statement.executeQuery("SELECT max(gcid) FROM Geography_Code")) {
                if (results.next()) {
                    MaxID = results.getInt(1);
                }
            }
            long gcid = Math.max(2000000, MaxID + 1);
            MaxID = gcid;
            String sql = "ALTER TABLE Geography_Code ALTER COLUMN gcid RESTART WITH " + (MaxID + 1);
            statement.executeUpdate(sql);
            return gcid;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return -1;
        }
    }

    public static boolean validCoordinate(double longitude, double latitude) {
        return longitude >= -180 && longitude <= 180
                && latitude >= -90 && latitude <= 90;
    }

    public static boolean validCoordinate(GeographyCode code) {
        return code.getLongitude() >= -180 && code.getLongitude() <= 180
                && code.getLatitude() >= -90 && code.getLatitude() <= 90;
    }

    public static void setNameParameters(PreparedStatement statement, String name, int fromIndex) {
        if (statement == null || name == null || fromIndex < 0) {
            return;
        }
        try {
            String lname = name.toLowerCase();
            statement.setString(fromIndex + 1, lname);
            statement.setString(fromIndex + 2, lname);
            statement.setString(fromIndex + 3, lname);
            statement.setString(fromIndex + 4, lname);
            statement.setString(fromIndex + 5, lname);
            statement.setString(fromIndex + 6, lname);
            statement.setString(fromIndex + 7, lname);
        } catch (Exception e) {
            MyBoxLog.error(e);
        }
    }

    public static String nameEqual(String value) {
        String v = stringValue(value).toLowerCase();
        return "  ( chinese_name IS NOT NULL AND LCASE(chinese_name)='" + v + "' ) OR "
                + " ( english_name IS NOT NULL AND LCASE(english_name)='" + v + "' ) OR "
                + " ( alias1 IS NOT NULL AND LCASE(alias1)='" + v + "' ) OR "
                + " ( alias2 IS NOT NULL AND LCASE(alias2)='" + v + "' ) OR "
                + " ( alias3 IS NOT NULL AND LCASE(alias3)='" + v + "' ) OR "
                + " ( alias4 IS NOT NULL AND LCASE(alias4)='" + v + "' ) OR "
                + " ( alias5 IS NOT NULL AND LCASE(alias5)='" + v + "' ) ";
    }

    public static String codeEqual(GeographyCode code) {
        if (code.getGcid() > 0) {
            return "gcid=" + code.getGcid();
        }
        int level = code.getLevel();
        String s = "level=" + level;
        switch (level) {
            case 3:
                break;
            case 4:
                s += " AND country=" + code.getCountry();
                break;
            case 5:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince();
                break;
            case 6:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince()
                        + " AND city=" + code.getCity();
                break;
            case 7:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince()
                        + " AND city=" + code.getCity()
                        + " AND county=" + code.getCounty();
                break;
            case 8:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince()
                        + " AND city=" + code.getCity()
                        + " AND county=" + code.getCounty()
                        + " AND town=" + code.getTown();
                break;
            case 9:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince()
                        + " AND city=" + code.getCity()
                        + " AND county=" + code.getCounty()
                        + " AND town=" + code.getTown()
                        + " AND village=" + code.getVillage();
                break;
            case 10:
            default:
                s += " AND country=" + code.getCountry()
                        + " AND province=" + code.getProvince()
                        + " AND city=" + code.getCity()
                        + " AND county=" + code.getCounty()
                        + " AND town=" + code.getTown()
                        + " AND village=" + code.getVillage()
                        + " AND building=" + code.getBuilding();
                break;
        }

        if (code.getChineseName() != null) {
            String name = stringValue(code.getChineseName()).toLowerCase();
            s += " AND  ( ( chinese_name IS NOT NULL AND LCASE(chinese_name)='" + name + "' ) OR "
                    + " ( alias1 IS NOT NULL AND LCASE(alias1)='" + name + "' ) OR "
                    + " ( alias2 IS NOT NULL AND LCASE(alias2)='" + name + "' ) OR "
                    + " ( alias3 IS NOT NULL AND LCASE(alias3)='" + name + "' ) OR "
                    + " ( alias4 IS NOT NULL AND LCASE(alias4)='" + name + "' ) OR "
                    + " ( alias5 IS NOT NULL AND LCASE(alias5)='" + name + "' ) ) ";

        } else if (code.getEnglishName() != null) {
            String name = stringValue(code.getEnglishName()).toLowerCase();
            s += " AND ( ( english_name IS NOT NULL AND LCASE(english_name)='" + name + "' ) OR "
                    + " ( alias1 IS NOT NULL AND LCASE(alias1)='" + name + "' ) OR "
                    + " ( alias2 IS NOT NULL AND LCASE(alias2)='" + name + "' ) OR "
                    + " ( alias3 IS NOT NULL AND LCASE(alias3)='" + name + "' ) OR "
                    + " ( alias4 IS NOT NULL AND LCASE(alias4)='" + name + "' ) OR "
                    + " ( alias5 IS NOT NULL AND LCASE(alias5)='" + name + "' ) ) ";
        }
        return s;
    }

    public static String codeEqual(String value) {
        String v = stringValue(value).toLowerCase();
        return " ( ( code1 IS NOT NULL AND LCASE(code1)='" + v + "' ) OR "
                + " ( code2 IS NOT NULL AND LCASE(code2)='" + v + "' ) OR "
                + " ( code3 IS NOT NULL AND LCASE(code3)='" + v + "' ) OR "
                + " ( code4 IS NOT NULL AND LCASE(code4)='" + v + "' ) OR "
                + " ( code5 IS NOT NULL AND LCASE(code5)='" + v + "' ) )  ";
    }

    public static GeographyCode earth() {
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return earth(conn);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode earth(Connection conn) {
        String sql = "SELECT * FROM Geography_Code WHERE level=1 AND chinese_name='地球'";
        return queryCode(conn, sql, false);
    }

    public static GeographyCode China() {
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return China(conn);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode China(Connection conn) {
        String sql = "SELECT * FROM Geography_Code WHERE level=3 AND chinese_name='中国'";
        return queryCode(conn, sql, false);
    }

    public static GeographyCode queryCode(String sql, boolean decodeAncestors) {
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return queryCode(conn, sql, decodeAncestors);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode queryCode(Connection conn, String sql, boolean decodeAncestors) {
        if (conn == null || sql == null) {
            return null;
        }
        try {
            GeographyCode code;
            try (Statement statement = conn.createStatement()) {
                statement.setMaxRows(1);
                try (ResultSet results = statement.executeQuery(sql)) {
                    if (results.next()) {
                        code = readResults(results);
                    } else {
                        return null;
                    }
                }
            }
            if (decodeAncestors && code != null) {
                decodeAncestors(conn, code);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    // this way is not accurate since multiple addresses can have same coordinate
    public static GeographyCode readCode(GeoCoordinateSystem coordinateSystem,
            double longitude, double latitude, boolean decodeAncestors) {
        if (coordinateSystem == null || !validCoordinate(longitude, latitude)) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return readCode(conn, coordinateSystem, longitude, latitude, decodeAncestors);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(Connection conn,
            GeoCoordinateSystem coordinateSystem,
            double longitude, double latitude, boolean decodeAncestors) {
        if (coordinateSystem == null || !validCoordinate(longitude, latitude)) {
            return null;
        }
        try {
            GeographyCode code;
            try (PreparedStatement statement = conn.prepareStatement(CoordinateQeury)) {
                statement.setShort(1, coordinateSystem.shortValue());
                statement.setDouble(2, longitude);
                statement.setDouble(3, latitude);
                code = readCode(conn, statement, decodeAncestors);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(long gcid, boolean decodeAncestors) {
        if (gcid <= 0) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return readCode(conn, gcid, decodeAncestors);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(Connection conn, long gcid, boolean decodeAncestors) {
        if (conn == null || gcid < 0) {
            return null;
        }
        try {
            GeographyCode code;
            try (PreparedStatement query = conn.prepareStatement(GCidQeury)) {
                query.setLong(1, gcid);
                code = readCode(conn, query, decodeAncestors);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(int level, String name, boolean decodeAncestors) {
        if (level <= 0 || name == null) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return readCode(conn, level, name, decodeAncestors);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(Connection conn, int level, String name, boolean decodeAncestors) {
        if (level <= 0 || name == null) {
            return null;
        }
        try {
            GeographyCode code;
            try (PreparedStatement statement = conn.prepareStatement(LevelNameQeury)) {
                statement.setInt(1, level);
                setNameParameters(statement, name, 1);
                code = readCode(conn, statement, decodeAncestors);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode readCode(GeographyCode code, boolean decodeAncestors) {
        if (code == null || !GeographyCode.valid(code)) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            return readCode(conn, code, decodeAncestors);
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static boolean setCodeQueryParameters2(PreparedStatement statement, GeographyCode code) {
        if (statement == null || code == null) {
            return false;
        }
        try {
            fixValues(code);
            int level = code.getLevel();
            String name;
            if (code.getChineseName() != null) {
                name = stringValue(code.getChineseName()).toLowerCase();
            } else if (code.getEnglishName() != null) {
                name = stringValue(code.getEnglishName()).toLowerCase();
            } else {
                return false;
            }
            statement.setShort(1, (short) level);
            statement.setLong(2, level > 2 ? code.getContinent() : -1);
            statement.setLong(3, level > 3 ? code.getCountry() : -1);
            statement.setLong(4, level > 4 ? code.getProvince() : -1);
            statement.setLong(5, level > 5 ? code.getCity() : -1);
            statement.setLong(6, level > 6 ? code.getCounty() : -1);
            statement.setLong(7, level > 7 ? code.getTown() : -1);
            statement.setLong(8, level > 8 ? code.getVillage() : -1);
            statement.setString(9, name);
            statement.setString(10, name);
            statement.setString(11, name);
            statement.setString(12, name);
            statement.setString(13, name);
            statement.setString(14, name);
            statement.setString(15, name);

            MyBoxLog.debug(name);
            return true;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return false;
        }
    }

    public static GeographyCode readCode(Connection conn, GeographyCode code, boolean decodeAncestors) {
        if (conn == null || code == null || !GeographyCode.valid(code)) {
            return null;
        }
        try {
            if (code.getGcid() > 0) {
                try (PreparedStatement statement = conn.prepareStatement(GCidQeury)) {
                    statement.setLong(1, code.getGcid());
                    return readCode(conn, statement, decodeAncestors);
                }
            } else {
                int level = code.getLevel();
                String condition = "level=" + level;
                switch (level) {
                    case 3:
//                        condition += " AND continent=" + code.getContinent();
                        break;
                    case 4:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry();
                        break;
                    case 5:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince();
                        break;
                    case 6:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince()
                                + " AND city=" + code.getCity();
                        break;
                    case 7:
                        condition += " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince()
                                + " AND city=" + code.getCity()
                                + " AND county=" + code.getCounty();
                        break;
                    case 8:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince()
                                + " AND city=" + code.getCity()
                                + " AND county=" + code.getCounty()
                                + " AND town=" + code.getTown();
                        break;
                    case 9:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince()
                                + " AND city=" + code.getCity()
                                + " AND county=" + code.getCounty()
                                + " AND town=" + code.getTown()
                                + " AND village=" + code.getVillage();
                        break;
                    case 10:
                    default:
                        condition += " AND continent=" + code.getContinent()
                                + " AND country=" + code.getCountry()
                                + " AND province=" + code.getProvince()
                                + " AND city=" + code.getCity()
                                + " AND county=" + code.getCounty()
                                + " AND town=" + code.getTown()
                                + " AND village=" + code.getVillage()
                                + " AND building=" + code.getBuilding();
                        break;
                }
                String nameEqual = "";
                if (code.getChineseName() != null) {
                    String name = stringValue(code.getChineseName()).toLowerCase();
                    nameEqual = " ( chinese_name IS NOT NULL AND LCASE(chinese_name)='" + name + "' ) OR "
                            + " ( alias1 IS NOT NULL AND LCASE(alias1)='" + name + "' ) OR "
                            + " ( alias2 IS NOT NULL AND LCASE(alias2)='" + name + "' ) OR "
                            + " ( alias3 IS NOT NULL AND LCASE(alias3)='" + name + "' ) OR "
                            + " ( alias4 IS NOT NULL AND LCASE(alias4)='" + name + "' ) OR "
                            + " ( alias5 IS NOT NULL AND LCASE(alias5)='" + name + "' ) ";
                }
                if (code.getEnglishName() != null) {
                    String name = stringValue(code.getEnglishName()).toLowerCase();
                    nameEqual = nameEqual.isBlank() ? "" : nameEqual + " OR ";
                    nameEqual += " ( english_name IS NOT NULL AND LCASE(english_name)='" + name + "' ) OR "
                            + " ( alias1 IS NOT NULL AND LCASE(alias1)='" + name + "' ) OR "
                            + " ( alias2 IS NOT NULL AND LCASE(alias2)='" + name + "' ) OR "
                            + " ( alias3 IS NOT NULL AND LCASE(alias3)='" + name + "' ) OR "
                            + " ( alias4 IS NOT NULL AND LCASE(alias4)='" + name + "' ) OR "
                            + " ( alias5 IS NOT NULL AND LCASE(alias5)='" + name + "' )  ";
                }
                if (nameEqual.isBlank()) {
                    return null;
                }
                String sql = "SELECT * FROM Geography_Code WHERE " + condition + " AND (" + nameEqual + ")";
                return queryCode(conn, sql, decodeAncestors);
            }
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    // Generally, when location full name is need, "decodeAncestors" should be true
    public static GeographyCode readCode(Connection conn, PreparedStatement query, boolean decodeAncestors) {
        if (conn == null || query == null) {
            return null;
        }
        try {
            GeographyCode code;
            query.setMaxRows(1);
            conn.setAutoCommit(true);
            try (ResultSet results = query.executeQuery()) {
                if (results.next()) {
                    code = readResults(results);
                } else {
                    return null;
                }
            }
            if (decodeAncestors && code != null) {
                decodeAncestors(conn, query, code);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
//            MyBoxLog.debug(e);
            return null;
        }
    }

    public static GeographyCode readResults(Connection conn, ResultSet results, boolean decodeAncestors) {
        if (conn == null || results == null) {
            return null;
        }
        try {
            GeographyCode code = readResults(results);
            if (decodeAncestors && code != null) {
                decodeAncestors(conn, code);
            }
            return code;
        } catch (Exception e) {
            MyBoxLog.error(e);
            return null;
        }
    }

    public static GeographyCode decodeAncestors(GeographyCode code) {
        if (code == null) {
            return null;
        }
        try (Connection conn = DerbyBase.getConnection()) {
            conn.setReadOnly(true);
            decodeAncestors(conn, code);
        } catch (Exception e) {
            MyBoxLog.error(e);
        }
        return code;
    }

    public static void decodeAncestors(Connection conn, GeographyCode code) {
        if (conn == null || code == null) {
            return;
        }
        try (PreparedStatement query = conn.prepareStatement(GCidQeury)) {
            decodeAncestors(conn, query, code);
        } catch (Exception e) {
            MyBoxLog.error(e);
        }
    }

    public static void decodeAncestors(Connection conn, PreparedStatement query, GeographyCode code) {
        if (conn == null || code == null) {
            return;
        }
        int level = code.getLevel();
        if (level < 3 || level > 10) {
            return;
        }
        try {
            conn.setAutoCommit(true);

            if (code.getContinent() > 0) {
                query.setLong(1, code.getContinent());

                try (ResultSet cresults = query.executeQuery()) {
                    if (cresults.next()) {
                        code.setContinentCode(readResults(cresults));
                    }
                }
            }
            if (level < 4) {
                return;
            }
            if (code.getCountry() > 0) {
                query.setLong(1, code.getCountry());
                try (ResultSet cresults = query.executeQuery()) {
                    if (cresults.next()) {
                        code.setCountryCode(readResults(cresults));
                    }
                }
            }

            if (level < 5) {
                return;
            }
            if (code.getProvince() > 0) {
