package osm.jp.coverage;

import java.io.*;
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.*;
import org.xml.sax.SAXException;
import osm.jp.api.HttpPOST;

public class DbTest {
    public static final String TABLE_NAME = "test";

    File inputFile;
    String filter = "";
    int iCounter = 0;
    String urlStr = "";
    Connection con;
    String timeStampStr = null;
    File dir = null;

    /** メイン  
     * 動作条件； HSQLDBのフォルダを削除した状態で実行すること。  
     * @param args
     * @throws IOException
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws FileNotFoundException
     * @throws javax.xml.parsers.ParserConfigurationException
     * @throws org.xml.sax.SAXException */
    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException 
    {
        // HSQLディレクトリがなければエラー
        File dbdir = new File("database");
        if (!dbdir.isDirectory()) {
            throw new FileNotFoundException("Directory 'database' is not found.");
        }
        
        Connection conHsql = null;
        try {
            conHsql = DatabaseTool.openDb("database");
            HttpPOST hsql = new HttpPOST(conHsql, null);
            hsql.sql("DROP TABLE IF EXISTS "+ TABLE_NAME +" CASCADE");
            //HttpPOST.sql(conHsql, "DROP INDEX "+ TABLE_NAME +"_index;");
            create(conHsql);
            try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
                ps.executeUpdate();
            }
            
            // 対象のファイルが「数値地図情報のGMLデータファイル」の時のみ処理を行う。
            importTest(conHsql, 14);

            DbTest.export(conHsql);
        }
        finally {
            if (conHsql != null) {
                DatabaseTool.closeDb(conHsql);
            }
        }
    }
    
    /**
     * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
     * @param conHsql
     * @param areacode
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws IOException
     * @throws ParserConfigurationException 
     * @throws SAXException 
     */
    public static void importTest (Connection conHsql, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
        int iCounter = 0;
        iCounter += showNodes(conHsql, areacode);
        System.out.println("("+ areacode +") Node数["+ iCounter +"]");
    }
    
    static final double CENTER_LAT = 35.4342443D;
    static final double CENTER_LON = 139.4092180D;
    static final int LOOP = 50;
    static final double SIDE = 0.015D;

    /**
     *  ノード: 綾西 (368434484)  35.4342443, 139.4092180
     * 
     * @param con
     * @param areacode
     * @return
     * @throws IOException
     * @throws SQLException
     */
    public static int showNodes(Connection con, int areacode) throws IOException, SQLException {
        int iCounter = 0;
        double lon = CENTER_LON - SIDE;
        for (int x = 0; x <= LOOP; x++) {
            double lat = CENTER_LAT - SIDE;
            for (int y = 0; y <= LOOP; y++) {
                showGmlPoint(con, lat, lon, areacode, iCounter++);
                lat += SIDE * 2 / LOOP;
            }
            lon += SIDE * 2 / LOOP;
        }
        return iCounter;
    }

    /**
     *
     * @param con
     * @param lat
     * @param lon
     * @param areacode
     * @param id
     * @throws IOException
     * @throws SQLException
     */
    public static void showGmlPoint(Connection con, double lat, double lon, int areacode, int id) throws IOException, SQLException {
        try (PreparedStatement ps6 = con.prepareStatement("INSERT INTO "+ TABLE_NAME +" (lat,lon,fixed,fixed1,area,gmlid,up) VALUES (?,?,0,0,?,?,2)")) {
            ps6.setDouble(1, lat);
            ps6.setDouble(2, lon);
            ps6.setInt(3, areacode);
            ps6.setString(4, Integer.toString(id));
            ps6.executeUpdate();
        }
    }


    /**
     * 'table.BUSSTOP'を新規に作る
     * 既にテーブルが存在する時には何もしない
     * @param conHsql
     * @throws SQLException
     */
    public static void create(Connection conHsql) throws SQLException {
        String createSt;
        HttpPOST hsql = new HttpPOST(conHsql, null);

        // 'table.TEST'を新規に作る
        createSt = "CREATE TABLE "+ TABLE_NAME +" (gmlid VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT "+ TABLE_NAME +"_pk PRIMARY KEY(gmlid, area));";
        hsql.sql(createSt);
        
        createSt = "CREATE INDEX "+ TABLE_NAME +"_index ON "+ TABLE_NAME +" (lat,lon);";
        hsql.sql(createSt);
    }

    /**
     * 'table.BUSSTOP'の内容をCSV形式にして標準出力に出力する
     * @param con
     */
    public static void export(Connection con) {
        try {
            System.out.println("TABLE: " + TABLE_NAME);
            System.out.println("\"gmlid\",\"name\",\"lat\",\"lon\",\"fixed\",\"ifile\"");
            PreparedStatement ps8 = con.prepareStatement("SELECT gmlid,name,lat,lon,fixed,ifile FROM "+ TABLE_NAME);
            try (ResultSet rset8 = ps8.executeQuery()) {
                while (rset8.next()) {
                    String gmlid = rset8.getString("gmlid");
                    String name = rset8.getString("name");
                    Double lat = rset8.getDouble("lat");
                    Double lon = rset8.getDouble("lon");
                    int fixed = rset8.getInt("fixed");
                    String ifile = rset8.getString("ifile");
                    System.out.println("\""+ gmlid +"\",\""+ name +"\","+ lat +","+ lon +","+ fixed +",\""+ ifile +"\"");
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}