package osm.jp.postgis;
 
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
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.Coverage;
import osm.jp.api.HttpPOST;
 
/**
 * 国土数値情報から読み取ったデータをPostGISへインサートする。
 * 	出力db名：	"gisdb.properties"
 * 	テーブル名：		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 {
    String tableName = null;
    public PostgisItems items = null;
    
    /**
     * コンストラクタ
     * @param tableName 出力先データベーステーブル
     */
    public ToPostgis(String tableName) {
        this.tableName = tableName;
        this.items = new PostgisItems();
        items.add(new PostgisItem("gmlid", "gmlid"));
        items.add(new PostgisItem("idref", "idref"));
        items.add(new PostgisItem("area", "area"));
        items.add(new PostgisItem("fixed", "fixed1"));
        items.add(new PostgisItem("code", "code"));
    }
 
    public ToPostgis(String tableName, PostgisItems items) {
        this(tableName);
        this.items = items;
    }
    
    /**
     * HSQLDBからPOSTGISへ
     * 
     * @throws Exception 
     */
    public void transport() throws Exception {
        transport(Coverage.DB_PORP_GISDB);
    }
    public void transport(String toPropertiy) throws Exception {
        try (Connection conHsql = DatabaseTool.openDb(Coverage.DB_PORP_LOCALDB);
            Connection conPost = DatabaseTool.openDb(toPropertiy))
        {
            transportNew(conHsql, conPost);
            toInsert(conHsql, 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
     */
 
    /*
    <pre>{@code
        CREATE TABLE public.t_TABLENAME
        (
          gid integer NOT NULL DEFAULT nextval('t_TABLENAME_gid_seq'::regclass),
          gmlid text,
          idref text,
          name text,
          fixed integer,
          area integer,
          code integer,
          geom geometry(Point,4612),
          CONSTRAINT t_TABLENAME_pkey PRIMARY KEY (gid)
        )
        WITH (OIDS=FALSE);
        ALTER TABLE public.t_TABLENAME
        OWNER TO postgres;
 
        CREATE INDEX ix_TABLENAME_geom ON public.t_TABLENAME USING gist (geom);
        GRANT SELECT ON t_fuel TO gisuser;
    }</pre>
    */
    public void transportNew(Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        HttpPOST posgre = new HttpPOST(conPost, null);
        posgre.sql("DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;");
        posgre.sql("CREATE TABLE t_"+ tableName 
            +" ("
                + "gid SERIAL PRIMARY KEY, "
                + "gmlid varchar(24), "
                + "idref varchar(24), "
                + "fixed integer, "
                + "area integer, "
                + "code integer, "
                + "geom GEOMETRY(POINT, 4612)"
            + ");");
        posgre.sql("CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);");
        posgre.sql("GRANT SELECT ON t_"+ tableName +" TO gisuser;");
    }
 
    public void toInsert (Connection conHsql, Connection conPost) 
        throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException 
    {
        String sql = items.getSqlStr(tableName);
        try (PreparedStatement ps1 = conHsql.prepareStatement(sql);
            ResultSet rset1 = ps1.executeQuery()) {
            while (rset1.next()) {
                items.setResuit(rset1);
 
                String latlon = String.format(
                    "ST_GeomFromText('POINT(%.7f %.7f)',4612)", 
                    items.lon,
                    items.lat
                );
                String sqlStr = "INSERT INTO t_"+ tableName 
                    +" (gmlid,idref,fixed,area,code,geom) "
                    + "VALUES (?,?,?,?,?, "+ latlon +")";
                try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
                    printMark();
                    ps.setString(1, items.gmlid);
                    ps.setString(2, items.idref);
                    ps.setInt(3, items.fixed);
                    ps.setInt(4, items.area);
                    ps.setInt(5, items.code);
                    ps.executeUpdate();
                }
            }
        }
    }
    
    public void toCsv (File csvFile) 
        throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException 
    {
        try (Connection conPost = DatabaseTool.openDb(Coverage.DB_PORP_GISDB);
            BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8")))
        {
            String header = items.getCsvHeader();
            ow.write(header);
            ow.newLine();
            
            String sql = items.getPostSqlStr(tableName);
            PreparedStatement ps8 = conPost.prepareStatement(sql);
            try (ResultSet rset8 = ps8.executeQuery()) {
                while (rset8.next()) {
                    items.setPostResuit(rset8);
                    String osm_node = items.getValue();
                    System.out.println(osm_node);
                    ow.write(osm_node);
                    ow.newLine();
                }
            }
            ow.flush();
        }
    }
 
    public static int outCnt = 0;
    public static void printMark() {
        System.out.print(".");
        outCnt++;
        if (outCnt >= 100) {
            outCnt = 0;
            System.out.println();
        }
    }
}