package osm.jp.coverage.busstop;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;

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

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

    /** メイン
     * @param args
     * @throws IOException
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws FileNotFoundException */
    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException 
    {
        Connection con = null;
        try {
            con = DatabaseTool.openDb("database");
            DbBusstop.export(con);
        }
        finally {
            if (con != null) {
                DatabaseTool.closeDb(con);
            }
        }
    }

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

        // 'table.BUS_STOP'を新規に作る
        drop(con, "bus_stop");
        createSt = "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), CONSTRAINT bus_stop_pk PRIMARY KEY(idref));";
        create(con, createSt);

        drop(con, "existing_data");
        createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));";
        create(con, createSt);

        drop(con, "coverage");
        createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);";
        create(con, createSt);
    }

    public static void create(Connection con, String createsql) throws SQLException {
        System.out.println(createsql);
        try (PreparedStatement ps = con.prepareStatement(createsql)) {
            ps.executeUpdate();
        }
        catch (SQLSyntaxErrorException e) {
            System.out.println("83:"+ e.toString());
            if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: object name already exists:"))) {
                throw e;
            }
        }
    }

    /**
     * 'table.BUS_STOP'を削除する
     * @param con
     * @param tableName
     * @throws SQLException
     */
    public static void drop(Connection con, String tableName) throws SQLException {
        String createSt = "DROP TABLE "+ tableName +";";
        System.out.println(createSt);
        try (PreparedStatement ps = con.prepareStatement(createSt)) {
            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;
            }
        }
    }

    /**
     * 'table.BUS_STOP'の内容を空にする
     * @param con
     * @param tableName
     * @throws SQLException
     */
    public static void clear(Connection con, String tableName) throws SQLException {
        String createSt = "DELETE FROM "+ tableName +";";
        System.out.println(createSt);
        try (PreparedStatement ps = con.prepareStatement(createSt)) {
            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;
            }
        }
    }

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