package osm.jp.coverage.busstop;
import osm.jp.api.HttpPOST;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Iterator;

import jp.co.areaweb.tools.csv.CsvFile;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.*;

public class NagoyaBusstop {

	String filter = "";
	String urlStr = "";

	public static final boolean DB_INIT = false;

	// 近くのバス停を探す範囲（バス停を中心としたNEERｍ四方の領域
	static final int NEER = 25;		// 20m
	
	public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");

	/**
	 * メイン
	 *
	 *	java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop <option>
	 *		OPTION: -check	OSMデータ上に既存のバス停が存在するかどうかをチェックする
	 * @throws Exception */
	public static void main(String[] args) throws Exception
	{
		// HSQLディレクトリがなければ作る
		File dbdir = new File("database");
		if (!dbdir.isDirectory()) {
			dbdir.mkdir();
		}

		Connection con = DatabaseTool.openDb("database");
		NagoyaBusstop.initDb(con);
		
		try {
			/**
			 * 都道府県ごとのGMLディレクトリの処理
			 */
			new NagoyaBusstop(con, new File("TRF0009630..csv"));
		}
		finally {
			DatabaseTool.closeDb(con);
		}
	}
	
	/**
	 * 個別の都道府県「GMLディレクトリ」を処理
	 * 
	 * @param con
	 * @param gmldir
	 * @throws Exception 
	 */
	public NagoyaBusstop(Connection con, File csvFile) throws Exception {
		BufferedWriter ow = null;
		ToPostgis postgis = null;
		Connection conPost = null;
		
		File outFile = new File("update.sql");
		if (outFile.isFile()) {
			outFile.delete();
		}
		ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8"));
		
		try {
			NagoyaBusstop.clearDb(con);
			inputFile(con, csvFile);

			/**
			 * 既存のOSMバス停を読み込む
			 * 		--> 'existing.xml'
			 */
			File existingFile = new File("existing.xml");
			
			/**
			 * インポートしたデータの緯度経度範囲を読み取る
			 */
			double maxLat = -90.0D;
			double minLat = 90.0D;
			double maxLon = -180.0D;
			double minLon = 180.0D;
			PreparedStatement ps8;
			ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop");
			ResultSet rset8 = ps8.executeQuery();
			while (rset8.next()) {
				Double lat = rset8.getDouble("lat");
				Double lon = rset8.getDouble("lon");

				if (lat > maxLat) {
					maxLat = lat;
				}
				if (lon > maxLon) {
					maxLon = lon;
				}
				if (lat < minLat) {
					minLat = lat;
				}
				if (lon < minLon) {
					minLon = lon;
				}
			}
			rset8.close();
			
			/**
			 * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する
			 */
			HttpPOST.getCapabilities(existingFile, "highway", "bus_stop", minLat, maxLat, minLon, maxLon);
			readExistingFile(con, existingFile);
			HttpPOST.getCapabilities(existingFile, "highway", "disused:bus_stop", minLat, maxLat, minLon, maxLon);
			readExistingFile(con, existingFile);
			HttpPOST.getCapabilities(existingFile, "public_transport", "platform", minLat, maxLat, minLon, maxLon);
			readExistingFile(con, existingFile);
			
			PreparedStatement ps1;
			ps1 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop");
			PreparedStatement ps2 = con.prepareStatement("SELECT count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
			PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?");
			PreparedStatement ps4 = con.prepareStatement("SELECT count(*) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
			ResultSet rset1 = ps1.executeQuery();
			while (rset1.next()) {
				String idref = rset1.getString(1);
				String name = rset1.getString("name");
				Double lat = rset1.getDouble("lat");
				Double lon = rset1.getDouble("lon");
				
				// 指定の緯度経度を中心とする半径5x2m四方の矩形領域
				System.out.print(idref + "("+ name + ") ....");
				RectArea rect = new RectArea(lat, lon, NEER);		// 25m 四方
				ps2.setDouble(1, rect.minlat);
				ps2.setDouble(2, rect.maxlat);
				ps2.setDouble(3, rect.minlon);
				ps2.setDouble(4, rect.maxlon);
				ResultSet rset2 = ps2.executeQuery();
				if (rset2.next()) {
					int score = rset2.getInt(1);
					if (score > 0) {
						score = 100;
					}
					else {
						rect = new RectArea(lat, lon, NEER*2);		// 50m 四方
						ps4.setDouble(1, rect.minlat);
						ps4.setDouble(2, rect.maxlat);
						ps4.setDouble(3, rect.minlon);
						ps4.setDouble(4, rect.maxlon);
						ResultSet rset4 = ps4.executeQuery();
						if (rset4.next()) {
							score = rset4.getInt(1);
							if (score > 0) {
								score = 50;
							}
						}
						rset4.close();
					}
					System.out.println("."+ score);
					ps3.setInt(1, score);
					ps3.setString(2, idref);
					ps3.executeUpdate();
				}
				rset2.close();
			}
			rset1.close();
			ps4.close();
			ps3.close();
			ps2.close();
			ps1.close();
			
			// ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する
			postgis = new ToPostgis(new File("."));
			postgis.outputDb(con);
			postgis.close();
		}
		finally {
			if (conPost != null) {
				DatabaseTool.closeDb(conPost);
			}
			ow.flush();
			ow.close();
		}
	}

	static String[] shiftArgs(String[] args) {
		String[] values = new String[args.length - 1];
		for (int i=1; i < args.length; i++) {
			values[i - 1] = new String(args[i]);
		}
		return values;
	}

	public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
		int iCounter = 0;

		DocumentBuilderFactory factory;
		DocumentBuilder        builder;
		Node root;

		iCounter = 0;
		factory = DocumentBuilderFactory.newInstance();
		builder = factory.newDocumentBuilder();
		factory.setIgnoringElementContentWhitespace(true);
		factory.setIgnoringComments(true);
		factory.setValidating(true);
		root    = builder.parse(existingFile);

		iCounter += readExistingNodes(con, root);
		System.out.println("既存バス停数["+ iCounter +"]");
	}
	
	static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {
		int iCounter = 0;
		
		NodeList nodes = node.getChildNodes();
		for (int i = 0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("node")) {
				iCounter++;
				importExistingNode(con, node2);
			}
			else {
				iCounter += readExistingNodes(con, node2);
			}
		}
		return iCounter;
	}

	static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
		String idrefStr = "";
		String latStr = "";
		String lonStr = "";
		String nameStr = "";
		String fixmeStr = "";
		int	score = 0;
		

		NamedNodeMap nodeMap = node.getAttributes();
		if (null != nodeMap) {
			for (int j=0; j < nodeMap.getLength(); j++) {
				if (nodeMap.item(j).getNodeName().equals("id")) {
					idrefStr = nodeMap.item(j).getNodeValue();
				}
				else if (nodeMap.item(j).getNodeName().equals("lat")) {
					latStr = nodeMap.item(j).getNodeValue();
				}
				else if (nodeMap.item(j).getNodeName().equals("lon")) {
					lonStr = nodeMap.item(j).getNodeValue();
				}
			}
			
			NodeList nodes = node.getChildNodes();
			for (int i = 0; i < nodes.getLength(); i++) {
				Node node2 = nodes.item(i);
				if (node2.getNodeName().equals("tag")) {
					NamedNodeMap nodeMap2 = node2.getAttributes();
					if (null != nodeMap2) {
						String key = null;
						String value = null;
						for (int j=0; j < nodeMap2.getLength(); j++) {
							if (nodeMap2.item(j).getNodeName().equals("k")) {
								key = nodeMap2.item(j).getNodeValue();
							}
							else if (nodeMap2.item(j).getNodeName().equals("v")) {
								value = nodeMap2.item(j).getNodeValue();
							}
						}
						
						if ((key != null) && key.toLowerCase().equals("name") && (value != null)) {
							nameStr = new String(value);
						}
						if ((key != null) && key.toLowerCase().equals("fixme") && (value != null)) {
							fixmeStr = new String(value);
						}
					}
				}
			}
			
			score = 50;
			if (nameStr.equals("")) {
				score = 1;
			}
			if (!fixmeStr.equals("")) {
				score = 1;
			}

			// idref と nameStr をデータベースに格納する
			PreparedStatement ps1 = con.prepareStatement("SELECT name,score FROM existing_data WHERE idref=?");
			ps1.setString(1, idrefStr);
			ResultSet rset1 = ps1.executeQuery();
			if (rset1.next()) {
				int fixed = rset1.getInt("score");
				if (fixed < score) {
					System.out.println("import existing_data : [id:"+ idrefStr +"] score="+ Integer.toString(score) +" "+ nameStr);
					PreparedStatement ps5 = con.prepareStatement("UPDATE existing_data SET score=? WHERE idref=?");
					ps5.setInt(1, score);
					ps5.setString(2, idrefStr);
					ps5.executeUpdate();
					ps5.close();
				}
			}
			else {
				System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")["+ Integer.toString(score) +"]"+ nameStr);
				PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name, score) VALUES (?,?,?,?,?)");
				ps5.setString(1, idrefStr);
				ps5.setDouble(2, Double.parseDouble(latStr));
				ps5.setDouble(3, Double.parseDouble(lonStr));
				ps5.setString(4, nameStr);
				ps5.setInt(5, score);
				ps5.executeUpdate();
				ps5.close();
			}
			rset1.close();
			ps1.close();
		}
	}
	
	/**
	 * 
	 */
	public static void updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
		int iCounter = 0;
		PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?");
		PreparedStatement ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)");

		// idref と nameStr をデータベースに格納する
		ps1.setInt(1, areacode);
		ResultSet rset1 = ps1.executeQuery();
		while (rset1.next()) {
			iCounter++;
			int idref = rset1.getInt("gid");
			String nameStr = rset1.getString("name");
			int fixed = rset1.getInt("fixed");
			double lat = rset1.getDouble(4);
			double lon = rset1.getDouble(5);
			
			ps2.setInt(1, idref);
			ps2.setString(2, nameStr);
			ps2.setInt(3, fixed);
			ps2.setInt(4, areacode);
			ps2.setDouble(5, lon);
			ps2.setDouble(6, lat);
			//System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")");
			ps2.executeUpdate();
		}
		rset1.close();
		ps1.close();
		ps2.close();

		System.out.println("("+ areacode +") バス停数["+ iCounter +"]");
	}
	
	/**
	 * 数値地図情報のGMLデータファイルを読み取ってローカルベータベースへ記録する
	 * @param con
	 * @param iFile
	 * @throws Exception 
	 */
	public static void inputFile (Connection con, File iFile) throws Exception {
		PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,kana,fixed,area,lat,lon) VALUES (?,?,?,?,?,?,?)");

		CsvFile csv = new CsvFile(iFile);
		csv.load();
		int cnt = 0;
		for (Iterator<CsvRecord> i = csv.iterator(); i.hasNext(); ) {
			CsvRecord record = (CsvRecord)i.next();
			if (cnt > 0) {
				String name = (String)record.get(0);
				String idStr = (String)record.get(1);
				String kana = (String)record.get(5);
				String latStr = (String)record.get(6);
				String lonStr = (String)record.get(7);
				System.out.println(record.toString());
				if (!idStr.isEmpty()) {
					ps2.setString(1, idStr);
					ps2.setString(2, name);
					ps2.setString(3, kana);
					ps2.setInt(4, 0);
					ps2.setInt(5, 0);
					ps2.setDouble(6, Double.parseDouble(latStr));
					ps2.setDouble(7, Double.parseDouble(lonStr));
					ps2.executeUpdate();
				}
			}
			cnt++;
		}
		ps2.close();
		System.out.println("バス停数["+ cnt +"]");
	}

	public static void clearDb(Connection con) throws SQLException {
		Statement stmt = con.createStatement();
		long count = stmt.executeUpdate("delete from bus_stop");
		System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。");
	    
	    count = stmt.executeUpdate("delete from existing_data");
	    System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");

	    count = stmt.executeUpdate("delete from bus_course");
	    System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。");

	    count = stmt.executeUpdate("delete from bus_ref");
	    System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。");
	    stmt.close();
	}

	public static void initDb(Connection con) throws SQLException {
		// 'table.BUS_STOP'を新規に作る
		DbBusstop.create(con);
	}


	public static String[] anaComm(Node briNode) {
		String[] rtn = new String[3];
		rtn[0] = "";	// corp type
		rtn[1] = "";	// course name
		rtn[2] = "";	// corp name

		NodeList nodes = briNode.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("ksj:BSC")) {
				rtn[0] = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:BLN")) {
				rtn[1] = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:BOC")) {
				rtn[2] = node2.getTextContent();
			}
		}
		return rtn;
	}
}