package osm.jp.coverage;

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;

/**
 * HSQLDB.TESTの結果をPOSTGIS.t_testへ反映させる
 * 	テーブル名：		t_test
 * 	インデックス：	ix_test_geom
 * 		gid		PostGISの識別ID
 * 		name	名称
 * 		fixed	OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。
 * 		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);
            }
        }
    }

    /**
     * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM BUS_STOP"
     * POSTGIS "insert into t_busstop(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 postgres = new HttpPOST(conPost, null);
        postgres.sql("DROP TABLE IF EXISTS t_test CASCADE;");
        postgres.sql("CREATE TABLE t_test (gid SERIAL PRIMARY KEY,gmlid text,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
        postgres.sql("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,lat,lon,fixed1,area FROM "+ DbTest.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");
                    
                    int fixed = 0;
                    if (fixed1 > 0) {
                        fixed = 1;
                    }
                    String geom = "ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612)";
                    String sqlStr = "INSERT INTO t_test (gmlid,fixed,area,geom) VALUES (?,?,?,"+ geom +")";
                    System.out.println(sqlStr +" ["+ gmlid +", "+ fixed +", "+ area +"]");
                    try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
                        ps.setString(1, gmlid);
                        ps.setInt(2, fixed);
                        ps.setInt(3, area);
                        ps.executeUpdate();
                    }
                }
            }
        }
    }
}