package osm.jp.postgis;

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へインサートするためのSQLファイルに出力する。
 * 	出力ファイル名：	"t_POSTOFFICE.sql"
 * 	テーブル名：		t_POSTOFFICE
 * 	インデックス：	ix_t_POSTOFFICE_geom
 * 		gid	PostGISの識別ID
 * 		idstr	国土数値情報のノードID
 * 		fixed	OSMのNodeが周辺に存在するかどうか、存在しない場合は0,存在する場合はそのScoreの合計。
 *              area    都道府県コード
 * 		geom	PostGIS形式の位置情報(4612:)
 * @author yuu
 *
 */
public class ToPostgis {
    protected boolean UPDATE = false;
    String tableName = null;
    
    public ToPostgis(String tableName, boolean update) {
        this.tableName = tableName;
        this.UPDATE = update;
    }
    
    public void transport() throws Exception {
        Connection conHsql = null;
        Connection conPost = null;
        try {
            // DB.tableを作成
            conHsql = DatabaseTool.openDb("database");
            conPost = DatabaseTool.openDb("postgis");
            transport(conHsql, conPost);
        }
        finally {
            if (conHsql != null) {
                DatabaseTool.closeDb(conHsql);
            }
            if (conPost != null) {
                DatabaseTool.closeDb(conPost);
            }
        }
    }
    
    /**
     * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM POSTOFFICE"
     * POSTGIS "insert into t_POSTOFFICE(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
     */
    void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        if (this.UPDATE) {
            transportUpdate(conHsql, conPost);
        }
        else {
            transportNew(conHsql, conPost);
        }
    }

    public void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        HttpPOST.sql(conPost, "DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;");
        HttpPOST.sql(conPost, "CREATE TABLE t_"+ tableName +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
        HttpPOST.sql(conPost, "CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);");
        toInsert(conHsql, conPost);
    }

    void transportUpdate (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        toInsert(conHsql, conPost);

        try (   PreparedStatement ps1 = conHsql.prepareStatement("SELECT gid,fixed1 FROM "+ tableName +" where up=1");
                PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_"+ tableName +" set fixed=? WHERE gid=?")) {
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    long gid = rset1.getLong("gid");
                    int fixed1 = rset1.getInt("fixed1");
                    ps2.setInt(1, fixed1);
                    ps2.setLong(2, gid);
                    System.out.println("UPDATE t_"+ tableName +" set fixed="+ fixed1 +" WHERE gid="+ gid);
                    ps2.executeUpdate();
                }
            }
        }
    }

    public void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        String sql = "SELECT idref,area,fixed1,lat,lon FROM "+ tableName;
        if (this.UPDATE) {
            sql += " where up=2";
        }
        try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) {
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String idref = rset1.getString("idref");
                    int area = rset1.getInt("area");
                    int fixed1 = rset1.getInt("fixed1");
                    double lat = rset1.getDouble("lat");
                    double lon = rset1.getDouble("lon");
                    
                    String sqlStr = "INSERT INTO t_"+ tableName +" (idref,fixed,area,geom) VALUES ('"+ idref +"',"+ fixed1 +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))";
                    try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
                        System.out.println(sqlStr);
                        ps.executeUpdate();
                    }
                }
            }
        }
    }
}