package osm.jp.postgis;

import osm.jp.coverage.postoffice.*;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import jp.co.areaweb.tools.database.DatabaseTool;

/**
 * PostGISデータをGeoJSONファイルに出力する。
 * 	出力ファイル名：	"fuel.json"
 * 	テーブル名：		t_fuel
 * 		fixed	OSMの周辺に存在するかどうか、存在しない場合は0,存在する場合は1。ブランド有りは５０。
 * 		geom	PostGIS形式の位置情報(4612:)
 * SELECT row_to_json(feature) FROM (
select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_fuel.geom,4326))::json As geometry, row_to_json((
   SELECT p FROM (SELECT t_fuel.idref, t_fuel.fixed) AS p)) AS properties From t_fuel) As feature
* 
 * @author yuu
 *
 */
public class ToGeoJSON {
    public static void main (String[] argv) throws Exception {
        boolean slim = false;
        for (String arg : argv) {
            if (arg.equals("-slim")) {
                slim = true;
            }
        }
        
        ToGeoJSON obj = new ToGeoJSON(DbPostoffice.TABLE_NAME);
        Connection con = DatabaseTool.openDb("postgis");
        try {
            obj.outputDb(con, "", new File("postoffice.json"), false);
            obj.outputDb(con, "WHERE fixed=0", new File("postoffice0.json"), slim);
            obj.outputDb(con, "WHERE fixed>0 and fixed<50", new File("postoffice1.json"), slim);
            obj.outputDb(con, "WHERE fixed>49", new File("postoffice2.json"), slim);
        }
        finally {
            DatabaseTool.closeDb(con);
        }
    }
    
    public String tableName;
    
    public ToGeoJSON(String tableName) {
        this.tableName = tableName;
    }

    public void outputDb(Connection con, String whereStr, File outputFile, boolean slim) throws Exception {
        try (BufferedWriter ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) {
            String sql = "SELECT row_to_json(feature) FROM (" 
                    + "select 'Feature' As type, "
                    + "ST_AsGeoJSON(ST_Transform(t_"+ this.tableName +".geom,4326))::json As geometry, row_to_json((" 
                    + "SELECT p FROM (SELECT t_"+ this.tableName +".idref, t_"+ this.tableName +".fixed) AS p"
                    + ")) AS properties From t_"+ this.tableName +" "+ whereStr +" order by area,idref) As feature";
            if (slim) {
                sql = "SELECT row_to_json(feature) FROM (" 
                    + "select 'Feature' As type, ST_AsGeoJSON(ST_Transform(t_"+ this.tableName +".geom,4326))::json As geometry From t_"+ this.tableName +" "+ whereStr +" order by area,idref) As feature";
            }
            System.out.println(sql);

            String line = "{" +
                "\"type\": \"FeatureCollection\"," +
                "\"features\": [";
            System.out.println(line);
            ow.write(line);
            ow.newLine();

            PreparedStatement ps8 = con.prepareStatement(sql);
            try (ResultSet rset8 = ps8.executeQuery()) {
                boolean top = true;
                while (rset8.next()) {
                    if (top) {
                        top = false;
                    }
                    else {
                        line = ",";
                        System.out.println(line);
                        ow.write(line);                    
                        ow.newLine();
                    }
                    line = rset8.getString(1);
                    System.out.print(line);
                    ow.write(line);
                }
            }
            System.out.println();
            ow.newLine();
            
            line = "]}";
            System.out.println(line);
            ow.write(line);
            ow.newLine();
            ow.flush();
        }
    }
}