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;

	/** メイン
	 * @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), 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, "bus_course");
		createSt = "CREATE TABLE bus_course (code int, type int, corp VARCHAR(128) NOT NULL, course VARCHAR(512), ifile VARCHAR(128), CONSTRAINT bus_course_pk PRIMARY KEY(code));";
		create(con, createSt);

		drop(con, "bus_ref");
		createSt = "CREATE TABLE bus_ref (idref VARCHAR(12), code INT);";
		create(con, createSt);
		
		// 'table.bus_route'を新規に作る
		drop(con, "bus_route");
		createSt = "CREATE TABLE bus_route (cvid VARCHAR(12), bsc int, boc VARCHAR(128) NOT NULL, bln VARCHAR(512));";
		create(con, createSt);

		// 'table.bus_Curve'を新規に作る
		drop(con, "bus_Curve");
		createSt = "CREATE TABLE bus_Curve (idref VARCHAR(12), seq INT, lat DOUBLE, lon DOUBLE, CONSTRAINT bus_curve_pk PRIMARY KEY(idref,seq));";
		create(con, createSt);
	}

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

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