package osm.jp.postgis;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.DatabaseTool;
import static osm.jp.postgis.ToPostgis.printMark;

/**
 * CSVファイルの内容をPostGIS.db.tableに書き戻す
 * 
 * @author yuu
 *
 */
public class Postgis implements AutoCloseable
{
    /**
     * 
     * $ java -cp .:osmCoverage.jar:hayashi.jar:postgresql.jar \
     *      osm.jp.postgis.Postgis ./share
     * @param args
     * @throws Exception 
     */
    public static void main(String[] args) throws Exception {
        File dir = new File(args[0]);
        try (Postgis db = new Postgis(dir)) {
            db.initTableAll();
            db.importCsvAll();
        }
    }
    
    TableInfo[] tables;
    Connection con = null;
    File workspaceDir;
    
    class TableInfo {
        String tableName;
        String csvPath;
        
        TableInfo(String tableName, String csvPath) {
            this.tableName = tableName;
            this.csvPath = csvPath;
        }
    }
    
    public Postgis(File workspace) throws ClassNotFoundException, SQLException, IOException {
        if (workspace == null) {
            this.workspaceDir = new File(".");
        }
        else {
            this.workspaceDir = workspace;
        }
        ArrayList<TableInfo> list = new ArrayList<>();
        list.add(new TableInfo("t_busstop", "GML_BUSSTOP/t_busstop.csv"));
        list.add(new TableInfo("t_fuel", "GML_FUEL/t_fuel.csv"));
        list.add(new TableInfo("t_police", "GML_POLICE/t_police.csv"));
        list.add(new TableInfo("t_postoffice", "GML_POSTOFFICE/t_postoffice.csv"));
        this.tables = list.toArray(new TableInfo[list.size()]);
        
        con = DatabaseTool.openDb("gisdb");
    }

    @Override
    public void close() throws Exception {
        if (con != null) {
            DatabaseTool.closeDb(con);
        }
    }
    
    public void initTableAll() throws Exception {
        for (TableInfo tableInfo : tables) {
            initTable(tableInfo.tableName);
        }
    }

    public void initTable(String tableName) throws Exception {
        sql("DROP TABLE IF EXISTS "+ tableName +" CASCADE;");
        sql("CREATE TABLE "+ tableName 
            +" ("
                + "gid SERIAL PRIMARY KEY, "
                + "gmlid varchar(24), "
                + "idref varchar(24), "
                + "fixed integer, "
                + "area integer, "
                + "code integer, "
                + "geom GEOMETRY(POINT, 4612)"
            + ");");
        sql("CREATE INDEX ix_"+ tableName +"_geom ON "+ tableName +" USING GiST (geom);");
    }
    
    public void importCsvAll() throws Exception {
        for (TableInfo info : this.tables) {
            importCsv(info);
        }
    }
    
    public void importCsv(TableInfo info) throws Exception {
        String charsetName = "UTF-8";
        File csvFile = new File(this.workspaceDir, info.csvPath);
        try (LineNumberReader reader = new LineNumberReader(new InputStreamReader(new FileInputStream(csvFile), charsetName))) {
            String line;
            ArrayList<String> header = new ArrayList<>();
            while ((line = reader.readLine()) != null) {
                if (line.trim().length() == 0) {
                    continue;
                }
                
                CsvRecord record = new CsvRecord();
                record.analizeRecord(line);
                if (header.isEmpty()) {
                    for (String str : record) {
                        header.add(str);
                    }
                }
                else {
                    toInsert(info.tableName, header, record);
                }
            }
        }
    }

    public void toInsert(String tableName, ArrayList<String> header, CsvRecord record) 
        throws Exception 
    {
        String gmlidStr = getStr(header, record, "gmlid");
        String idrefStr = getStr(header, record, "idref");
        int area = getInt(header, record, "area");
        int fixed = getInt(header, record, "fixed");
        int code = getInt(header, record, "code");
        String latStr = record.get(header.indexOf("lat"));
        String lonStr = record.get(header.indexOf("lon"));
        String latlon = String.format(
            "ST_GeomFromText('POINT(%.7f %.7f)',4612)", 
            Double.parseDouble(lonStr),
            Double.parseDouble(latStr)
        );
        String sqlStr = "INSERT INTO "+ tableName 
            +" (gmlid,idref,area,fixed,code,geom) "
            + "VALUES (?,?,?,?,?, "+ latlon +")";
        try (PreparedStatement ps = this.con.prepareStatement(sqlStr)) {
            printMark();
            ps.setString(1, gmlidStr);   // gmlid
            ps.setString(2, idrefStr);   // idref
            ps.setInt(3, area);       // area
            ps.setInt(4, fixed);      // fixed
            ps.setInt(5, code);       // code
            ps.executeUpdate();
        }
        catch (SQLSyntaxErrorException e) {
            System.out.println("107:"+ e.toString());
            if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) {
                throw e;
            }
        }
    }
    
    String getStr(ArrayList<String> header, CsvRecord record, String key) {
        int i = header.indexOf(key);
        if (i < 0) {
            return "";
        }
        else {
            return record.get(i);
        }
    }
        
    int getInt(ArrayList<String> header, CsvRecord record, String key) {
        int i = header.indexOf(key);
        if (i < 0) {
            return 0;
        }
        else {
            return Integer.parseInt(record.get(i));
        }
    }

    public void sql(String sql) throws SQLException {
        System.out.println(sql);
        try (PreparedStatement ps = this.con.prepareStatement(sql)) {
            ps.executeUpdate();
        }
        catch (SQLSyntaxErrorException e) {
            System.out.println("107:"+ e.toString());
            if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) {
                throw e;
            }
        }
    }
}