package osm.jp.postgis;
 
import osm.jp.coverage.busstop.*;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.xml.parsers.ParserConfigurationException;
import jp.co.areaweb.tools.database.DatabaseTool;
import org.xml.sax.SAXException;
import osm.jp.api.HttpPOST;
 
/**
 * １０００ｍメッシュをPOSTGIS.t_testへ反映させる
 * ノード: 綾西 (368434484)  35.4342443, 139.4092180
 * から
 * ノード: 庚申供養 (1679559681)  35.4342940, 139.4207050
 * ノード: 内藤橋 (1742708189) : 35.4432806, 139.4104114
 * までが 1,000m となるはず
 * 
 * 	テーブル名：		t_test
 * 	インデックス：	ix_test_geom
 * 		gid		PostGISの識別ID
 * 		fixed	
 * 		geom	PostGIS形式の位置情報(4612:)
 * @author yuu
 *
 */
public class ToPostgis {
    public static void main(String[] argv) throws Exception {
        Connection conHsql = null;
        Connection conPost = null;
        try {
            // DB.tableを作成
            conHsql = DatabaseTool.openDb("database");
            conPost = DatabaseTool.openDb("postgis");
            
            transportNew(conHsql, conPost);
            
        }
        finally {
            if (conHsql != null) {
                DatabaseTool.closeDb(conHsql);
            }
            if (conPost != null) {
                DatabaseTool.closeDb(conPost);
            }
        }
    }
 
    /**
     * POSTGIS "insert into t_test(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));"
     * 
     * @param conHsql
     * @param conPost
     * @throws java.io.FileNotFoundException
     * @throws java.lang.ClassNotFoundException
     * @throws java.sql.SQLException
     * @throws java.io.IOException
     * @throws javax.xml.parsers.ParserConfigurationException
     * @throws org.xml.sax.SAXException
     */
    public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_test CASCADE;");
        HttpPOST.sql(conPost, "CREATE TABLE t_test (gid SERIAL PRIMARY KEY,fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
        HttpPOST.sql(conPost, "CREATE INDEX ix_test_geom ON t_test USING GiST (geom);");
 
        toInsert(conHsql, conPost);
    }
 
    public static void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        String sql = "SELECT gmlid,name,lat,lon,fixed1,area FROM "+ DbBusstop.TABLE_NAME;
        try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) {
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String gmlid = rset1.getString("gmlid");
                    int area = rset1.getInt("area");
                    int fixed1 = rset1.getInt("fixed1");
                    double lat = rset1.getDouble("lat");
                    double lon = rset1.getDouble("lon");
                    String name = rset1.getString("name");
                    
                    int fixed = 0;
                    if (fixed1 >= 100) {
                        fixed = 3;
                    }
                    else if (fixed1 >= 50) {
                        fixed = 2;
                    }
                    else if (fixed1 > 0) {
                        fixed = 1;
                    }
                    String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)";
                    String sqlStr = "INSERT INTO t_busstop (gmlid,name,fixed,area,geom) VALUES ('"+ gmlid +"','"+ name +"',"+ fixed +","+ area +","+ geom +")";
                    System.out.println(sqlStr);
                    sqlStr = "INSERT INTO t_busstop (gmlid,name,fixed,area,geom) VALUES (?,?,?,?,"+ geom +")";
                    try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
                        ps.setString(1, gmlid);
                        ps.setString(2, name);
                        ps.setInt(3, fixed);
                        ps.setInt(4, area);
                        ps.executeUpdate();
                    }
                }
            }
        }
    }
}