package osm.jp.coverage.fuel;

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;

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

    public static void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        try (PreparedStatement ps2 = conPost.prepareStatement("DELETE FROM t_FUEL")) {
            System.out.println("DELETE FROM t_FUEL;");
            ps2.executeUpdate();
        }
        toInsert(conHsql, conPost);
    }

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

        try (   PreparedStatement ps1 = conHsql.prepareStatement("SELECT gmlid,area,fixed1 FROM FUEL where up=1");
                PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE gmlid=? AND area=?")) {
            try (ResultSet rset1 = ps1.executeQuery()) {
                while (rset1.next()) {
                    String gmlid = rset1.getString("gmlid");
                    int area = rset1.getInt("area");
                    int fixed1 = rset1.getInt("fixed1");
                    ps2.setInt(1, fixed1);
                    ps2.setString(2, gmlid);
                    ps2.setInt(3, area);
                    System.out.println("UPDATE t_FUEL set fixed="+ fixed1 +" WHERE gmlid="+ gmlid +" AND area="+ area);
                    ps2.executeUpdate();
                }
            }
        }
    }

    public static void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        String sql = "SELECT gmlid,area,fixed1,lat,lon,idref FROM "+ DbFuel.TABLE_NAME;
        if (ToPostgis.UPDATE) {
            sql += " where up=2";
        }
        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 idref = rset1.getString("idref");
                    
                    String sqlStr = "INSERT INTO t_FUEL (gmlid,fixed,area,idref,geom) VALUES ('"+ gmlid +"',"+ fixed1 +","+ area +","+ idref +",idref,ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))";
                    System.out.println(sqlStr);
                    sqlStr = "INSERT INTO t_FUEL (gmlid,fixed,area,idref,geom) VALUES (?,?,?,?,ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))";
                    try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
                        ps.setString(1, gmlid);
                        ps.setInt(2, fixed1);
                        ps.setInt(3, area);
                        ps.setString(4, idref);
                        ps.executeUpdate();
                    }
                }
            }
        }
    }
}