package osm.jp.api;
 
import hayashi.yuu.tools.json.JsonTool;
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.json.Json;
import javax.json.JsonArray;
import javax.json.JsonBuilderFactory;
import javax.json.JsonObject;
import javax.json.JsonObjectBuilder;
import javax.json.JsonValue;
import javax.xml.parsers.ParserConfigurationException;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.DatabaseTool;
import org.hsqldb.HsqlException;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import osm.jp.coverage.busstop.DbBusstop;
 
public abstract class Osmdb {
    public String TABLE_NAME = "EXIST_osm";
    public String tableName = TABLE_NAME;
    public Connection hsqldb = null;   // hsqldb DatabaseTool.openDb("database");
 
    public Osmdb(Connection hsqldb, String tableName) {
        this.hsqldb = hsqldb;
        if (tableName != null) {
            this.tableName = tableName;
        }
    }
    
    /**
     * 'HSQLDB.table.OSM_EXIST'を新規に作る
     * 'HSQLDB.table.AREA_NODE'を新規に作る
     * 既にテーブルが存在する時にはERROR
     * @throws SQLException
     */
    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);
    }
    
    void sql(String sql) throws SQLException {
        System.out.println(sql);
        try (PreparedStatement ps = hsqldb.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 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;    // 評価ポイント｜!(fixme=null) → score=1
    public static final int POINT_NO_BRAND = 2;    // 評価ポイント｜brand=null → score=1
    public static final int POINT_NO_NAME = 4;     // 評価ポイント｜name=null → score=1
    public static final int POINT_BUS_NO = 8;      // 評価ポイント｜!(bus=yes) → score=0
    
    
    /**
     * 'table.OSM_EXIST'の内容をCSV形式にして標準出力に出力する
     * @throws java.sql.SQLException
     */
    public void export() throws SQLException {
        String header = "idref,lat,lon,score";
        System.out.println("TABLE: "+ tableName);
        System.out.println(header);
        PreparedStatement ps8 = hsqldb.prepareStatement(
            "SELECT idref,lat,lon,score,removed FROM "+ tableName
        );
        try (ResultSet rset8 = ps8.executeQuery()) {
            while (rset8.next()) {
                String idcode = rset8.getString(1);
                Double lat = rset8.getDouble(2);
                Double lon = rset8.getDouble(3);
                int score = rset8.getInt(4);
                boolean removed = rset8.getBoolean(5);
                String out = String.format(
                    "OSM: %s,%2.5f,%3.5f,%d,%b", 
                    idcode, lat, lon, score, removed
                );
                System.out.println(out);
            }
        }
    }
    
 
    public int importExistingNode(Connection osmdb, ResultSet rset, int point, boolean removed) throws IOException, SQLException {
        String osmidStr = rset.getString("osm_id");
        String latStr = rset.getString("lat");
        String lonStr = rset.getString("lon");
        String brandStr = rset.getString("brand");
        String nameStr = rset.getString("name");
        String fixmeStr = "";
 
        int score = 50;
        if (((point & POINT_NO_BRAND) != 0) && isNull(brandStr)) {
            score = 1;
        }
        if (((point & POINT_NO_NAME) != 0) && isNull(nameStr)) {
            score = 1;
        }
        
        boolean fixme = false;
        boolean busYes = false;
        if (((point & (POINT_FIXME | POINT_BUS_NO)) != 0)) {
            PreparedStatement ps8 = osmdb.prepareStatement(
                "SELECT * FROM planet_osm_nodes where id='"+ osmidStr +"'"
            );
            try (ResultSet rset8 = ps8.executeQuery()) {
                while (rset8.next()) {
                    String tags = rset8.getString("tags");
                    if (tags != null) {
                        tags = tags.substring(1);
                        tags = tags.substring(0, tags.length()-1);
                        CsvRecord csv = new CsvRecord();
                        csv.analizeRecord(tags);
                        
                        boolean key = true;
                        boolean bus = false;
                        for (String str : csv) {
                            if (key) {
                                if (str.startsWith("fixme")) {
                                    fixme = true;
                                }
                                if (str.equals("bus")) {
                                    bus = true;
                                }
                                key = false;
                            }
                            else {
                                if (bus) {
                                    if (str.equals("yes")) {
                                        busYes = true;
                                    }
                                    bus = false;
                                }
                                key = true;
                            }
                        }
                        
                        //System.out.println(tags);
                    }
                }
            }
            
            if (((point & POINT_FIXME) != 0) && fixme) {
                score = 1;
            }
            if (((point & POINT_BUS_NO) != 0) && !busYes) {
                score = 0;
            }
        }
        
        if (score > 0) {
            // idref と brandStr をデータベースに格納する
            System.out.println(
                "INSERT INTO "+ tableName 
                    + " (idref,lat,lon,score,name,removed) VALUES ("
                    + osmidStr +","
                    + latStr +","
                    + lonStr +","
                    + Integer.toString(score) +","
                    + "'"+ nameStr +"',"
                    + "false"
                    + ")N"
            );
            try (PreparedStatement ps5 = hsqldb.prepareStatement(
                "INSERT INTO "+ tableName +" (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)"
            )) {
                ps5.setString(1, osmidStr);
                ps5.setDouble(2, Double.parseDouble(latStr));
                ps5.setDouble(3, Double.parseDouble(lonStr));
                ps5.setInt(4, score);
                ps5.setString(5, nameStr);
                ps5.setBoolean(6, removed);
                ps5.executeUpdate();
                return 1;
            }
            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]は、無視する
            }
        }
 
        return 0;
    }
 
    /**
     * File(HttpPOST.EXIST_FILE)を読み取って、データベースに反映させる。<br>
     * その際に、OSMノードを評価し、scoreを算定する
     * 
     * @param sqlWhere  POI条件
     * @param point     評価ポイント[POINT_NO|POINT_BRAND|POINT_NAME] 1: 'brand'  2:'name'
     * @param removed
     * @throws Exception              エラー
     */
    public void readExisting(String sqlWhere, int point, boolean removed) throws Exception {
        long counter = 0L;
        try (Connection osmdb =  DatabaseTool.openDb("osmdb")) {
            String sqlSelect = "osm_id,"
            +   "brand,"
            +   "disused,"
            +   "name,"
            +   "ST_Y(ST_Transform(way,4326)) as lat,"
            +   "ST_X(ST_Transform(way,4326)) as lon";
            String sqlNode = String.format(
                "SELECT %s FROM planet_osm_point %s", 
                sqlSelect, 
                sqlWhere
            );
            PreparedStatement ps1 = osmdb.prepareStatement(sqlNode);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    counter += importExistingNode(osmdb, rset1, point, removed);
                }
            }
            
            String sqlArea = String.format(
                "SELECT %s FROM planet_osm_polygon %s", 
                "osm_id,brand,disused,name,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon", 
                sqlWhere
            );
            PreparedStatement ps2 = osmdb.prepareStatement(sqlArea);
            try (ResultSet rset2 = ps2.executeQuery()) {
                while (rset2.next()) {
                    counter += importExistingNode(osmdb, rset2, point, removed);
                }
            }
 
            System.out.println("Exists Node count = " + counter);
        }
    }
 
    /**
     * 
     * SELECT 
     *    planet_osm_nodes.id, 
     *    planet_osm_nodes.tags
     *    planet_osm_nodes.way --> lat & lon
     *  FROM 
     *    public.planet_osm_point, 
     *    public.planet_osm_nodes
     *  WHERE (
     *      planet_osm_point.osm_id = planet_osm_nodes.id
     *      and 'disused:amenity'=ANY(planet_osm_nodes.tags)
     *      and 'fuel'=ANY(planet_osm_nodes.tags)
     *    )
     * 
     * @param kStr
     * @param vStr
     * @param point
     * @throws Exception 
     */
    public void readExistingSub(String kStr, String vStr, int point) throws Exception {
        readExistingSub(kStr, vStr, point, true);
    }
    
    public void readExistingSub(String kStr, String vStr, int point, boolean removed) throws Exception {
        JsonBuilderFactory factory = Json.createBuilderFactory(null);
        JsonObjectBuilder builder = factory.createObjectBuilder();
        builder.add("k",kStr);
        builder.add("v", vStr);
        JsonObject tag = builder.build();
        readExistingSub(tag, point, removed);
    }
    
    /**
     * 
     * @param tag  {k: string, v: string}
     * @param point
     * @throws Exception 
     */
    public void readExistingSub(JsonObject tag,int point) throws Exception {
        readExistingSub(tag, point, true);
    }
 
    /**
     * 
     * @param tag  {k: string, v: string}
     * @param point
     * @param removed
     * @throws Exception 
     */
    public void readExistingSub(JsonObject tag,int point, boolean removed) throws Exception {
        long counter = 0L;
        try (Connection osmdb =  DatabaseTool.openDb("osmdb")) {
            String sqlSelect = "SELECT tags,id,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon";
            String sqlWhere = String.format(
                "WHERE (osm_id=id AND '%s'=ANY(tags) AND '%s'=ANY(tags))", 
                tag.getString("k"), tag.getString("v")
            );
            String sql = String.format(
                "%s FROM planet_osm_point,planet_osm_nodes %s", sqlSelect, sqlWhere
            );
            System.out.println(sql);
            
            PreparedStatement ps1 = osmdb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String tags = rset1.getString("tags");
                    String idref = rset1.getString("id");
                    String latStr = rset1.getString("lat");
                    String lonStr = rset1.getString("lon");
                    double lat = Double.valueOf(latStr);
                    double lon = Double.valueOf(lonStr);
                    int score = score(point, tags);
                    counter += insertExistingNode(idref, lat, lon, score, "", removed);
                }
            }
            System.out.println("Exists Node count = " + counter);
        }
    }
 
    /**
     * 
     * @param array  tags: [{k: string, v: string}]
     * @param point
     * @throws Exception 
     */
    public void readExistingSub(JsonArray array,int point) throws Exception {
        readExistingSub(array,point, true);
    }
 
    /**
     * 
     * @param array  tags: [{k: string, v: string}]
     * @param point
     * @param removed
     * @throws Exception 
     */
    public void readExistingSub(JsonArray array,int point, boolean removed) throws Exception {
        long counter = 0L;
        try (Connection osmdb =  DatabaseTool.openDb("osmdb")) {
            String sqlSelect = "SELECT "
            +   "tags,"
            +   "id,"
            +   "ST_Y(ST_Transform(way,4326)) as lat,"
            +   "ST_X(ST_Transform(way,4326)) as lon";
            String sqlSS = "";
            for (JsonValue v : array) {
                JsonObject tag = (JsonObject)v;
                sqlSS += String.format(
                    " AND ('%s'=ANY(tags) AND '%s'=ANY(tags))", 
                    tag.getString("k"), tag.getString("v")
                );
            }
            String sqlWhere = String.format("WHERE (osm_id=id %s)", sqlSS);
            String sql = String.format(
                "%s FROM planet_osm_point,planet_osm_nodes %s", 
                sqlSelect, sqlWhere
            );
            System.out.println(sql);
            
            PreparedStatement ps1 = osmdb.prepareStatement(sql);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String tags = rset1.getString("tags");
                    String idref = rset1.getString("id");
                    String latStr = rset1.getString("lat");
                    String lonStr = rset1.getString("lon");
                    double lat = Double.valueOf(latStr);
                    double lon = Double.valueOf(lonStr);
                    int score = score(point, tags);
                    counter += insertExistingNode(idref, lat, lon, score, "", removed);
                }
            }
            System.out.println("Exists Node count = " + counter);
        }
    }
 
    int score(int point, String tags) {
        int score = 50;
        if (tags == null) {
            return score;
        }
        
        boolean brandYes = false;
        boolean busYes = false;
        boolean fixmeYes = false;
        boolean nameYes = false;
        
        tags = tags.substring(1);
        tags = tags.substring(0, tags.length()-1);
        CsvRecord csv = new CsvRecord();
        csv.analizeRecord(tags);
 
        boolean key = true;
        boolean bus = false;
        for (String str : csv) {
            if (key) {
                if (str.startsWith("fixme")) {
                    fixmeYes = true;
                }
                else if (str.equals("bus")) {
                    bus = true;
                }
                else if (str.equals("brand")) {
                    brandYes = true;
                }
                else if (str.startsWith("name")) {
                    nameYes = true;
                }
                key = false;
            }
            else {
                if (bus) {
                    if (str.equals("yes")) {
                        busYes = true;
                    }
                    bus = false;
                }
                key = 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;
    }
    
    /**
     * 
     * @param idref
     * @param lat
     * @param lon
     * @param score
     * @param name
     * @return
     * @throws IOException
     * @throws SQLException 
     */
    public int insertExistingNode(String idref, double lat, double lon, int score, String name) throws IOException, SQLException {
        return insertExistingNode(idref, lat, lon, score, name, false);
    }
 
    /**
     * 
     * @param idref
     * @param lat
     * @param lon
     * @param score
     * @param name
     * @param removed
     * @return
     * @throws IOException
     * @throws SQLException 
     */
    public int insertExistingNode(String idref, double lat, double lon, int score, String name, boolean removed) throws IOException, SQLException {
        // idref と brandStr をデータベースに格納する
        /*
        */
        
        String sql = String.format("DELETE FROM %s WHERE idref=?", tableName);
        try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) {
            ps5.setString(1, idref);
            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]は、無視する
        }
 
        sql = String.format(
            "INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (%s,%2.7f,%3.7f,%d,%s,%s)", 
            tableName, 
            idref, lat, lon, score, name, (removed ? "true":"false")
        );
        System.out.println(sql);
        
        sql = String.format(
            "INSERT INTO %s (idref,lat,lon,score,name,removed) VALUES (?,?,?,?,?,?)", 
            tableName
        );
        try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) {
            ps5.setString(1, idref);
            ps5.setDouble(2, lat);
            ps5.setDouble(3, lon);
            ps5.setInt(4, score);
            ps5.setString(5, name);
            ps5.setBoolean(6, removed);
            ps5.executeUpdate();
            return 1;
        }
        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]は、無視する
        }
        return 0;
    }
    
    /**
     * REMOVEDファイルからデータを揉み込む
     * 
     * @param revovedFile
     * @throws FileNotFoundException
     * @throws IOException
     * @throws SQLException 
     */
    public void loadRemoved(File revovedFile) throws FileNotFoundException, IOException, SQLException {
        if (revovedFile == null) {
            throw new FileNotFoundException();
        }
        if (!revovedFile.exists()) {
            throw new FileNotFoundException();
        }
        if (!revovedFile.isFile()) {
            throw new FileNotFoundException();
        }
        
        try (PreparedStatement ps = hsqldb.prepareStatement(
            "UPDATE "
                + tableName 
                + " SET idref=?,removed=true,fixed1=100"
                + " WHERE gmlid=? AND area=?"
        )) {
            LineNumberReader reader = new LineNumberReader(
                new InputStreamReader(new FileInputStream(revovedFile))
            );
            while (reader.readLine() != null) {
                String lineStr = reader.readLine();
                if (lineStr == null) {
                    continue;
                }
                if (lineStr.trim().length() < 3) {
                    continue;
                }
 
                JsonObject json = JsonTool.parse(lineStr);
                String gmlid = json.getString("gmlid");
                String idref = json.getString("idref");
                int area = json.getInt("area");
 
                ps.setString(1, idref);
                ps.setString(2, gmlid);
                ps.setInt(3, area);
                ps.executeUpdate();
            }
        }
    }
    
    /**
     * removedデータをファイルに出力する
     * 
     * @param removedFile
     * @throws SQLException 
     * @throws java.io.IOException 
     */
    public void outputRemoved(File removedFile) throws SQLException, IOException {
        String whereStr = "WHERE (removed=?)";
        String fromStr = "FROM "+ DbBusstop.TABLE_NAME;
        String sortStr = "ORDER BY area,gmlid";
        String sql = String.format("SELECT * %s %s %s", fromStr, whereStr, sortStr);
        
        try (FileWriter fw = new FileWriter(removedFile);
            PreparedStatement ps1 = hsqldb.prepareStatement(sql))
        {
            System.out.println(sql);
            ps1.setBoolean(1, true);
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String gmlid = rset1.getString("gmlid");
                    int area = rset1.getInt("area");
                    String idref = rset1.getString("idref");
                    
                    JsonObjectBuilder jsonBuilder = Json.createObjectBuilder();
                    jsonBuilder.add("gmlid", gmlid);
                    jsonBuilder.add("idref", (idref==null ? "" : idref));
                    jsonBuilder.add("area", area);
                    JsonObject value = jsonBuilder.build();
                    String line = value.toString();
                    
                    fw.write(line);
                    fw.write("\n");
                    fw.flush();
                }
            }
            fw.close();
        }
    }
 
    boolean isNull(String vstr) {
        if (vstr == null) {
            return true;
        }
        return (vstr.trim().length() < 1);
    }
 
    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;
    }
 
    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();
            }
        }
    }
    
    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;
    }
    
    public void dropTable () throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        String sqlStr = "DROP TABLE IF EXISTS "+ tableName;
        try (PreparedStatement ps = hsqldb.prepareStatement(sqlStr)) {
            System.out.println(sqlStr);
            ps.executeUpdate();
        }
    }
    
}