package database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.swing.text.html.HTMLDocument.HTMLReader.ParagraphAction;

import org.eclipse.jetty.util.log.Slf4jLog;
import org.eclipse.jetty.websocket.api.Session;
import org.omg.Messaging.SyncScopeHelper;

import java.util.Properties;
import java.util.Set;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.webcerebrium.binance.api.BinanceApi;
import com.webcerebrium.binance.api.BinanceApiException;
import com.webcerebrium.binance.api.BinanceRequest;
import com.webcerebrium.binance.datatype.BinanceCandlestick;
import com.webcerebrium.binance.datatype.BinanceEventKline;
import com.webcerebrium.binance.datatype.BinanceInterval;
import com.webcerebrium.binance.datatype.BinanceSymbol;
import com.webcerebrium.binance.datatype.BinanceTicker;
import com.webcerebrium.binance.websocket.BinanceWebSocketAdapterKline;

import constants.BinanceApiKey;

public class SqlManager {
	private static BinanceApi api;
	private static String pKey = BinanceApiKey.getPublicKey();
	private static String sKey = BinanceApiKey.getSecretKey();
	private static Connection c = null;
	static long hieghsOpenTime = 2;

	public static BinanceApi declareApi(){

		try {
			return new BinanceApi(pKey,sKey);
		} catch (BinanceApiException e) {
			e.printStackTrace();
			return null;
		}

	}

	private static Properties getProperties() {
		Properties prop = new Properties();
		prop.setProperty("BINANCE_API_KEY", pKey);
		prop.setProperty("BINANCE_API_SECRET", sKey);
		return prop;
	}
	public static void main(String[] args) throws SQLException, ClassNotFoundException, BinanceApiException {
		Properties prop = getProperties();
		c = getConnection();
//		 System.setProperty("lombok.extern.slf4j.Slf4j.DEFAULT_LOG_LEVEL_KEY", "ERROR");
		api = new BinanceApi(pKey, sKey);
		System.out.println(System.currentTimeMillis());

		JsonArray allBookTickers = api.allBookTickers();

		ArrayList<String> listOfPairs = new ArrayList<>();

		for (JsonElement jsonElement : allBookTickers) {
			String je = jsonElement.toString();
			if(je.contains("123456"))
				continue;
			String split1 = je.split(",")[0];
			String pair = split1.split(":")[1];
			pair = pair.replaceAll("\"", "");
			System.out.println("++++++++++++++++++++++++++++++++++");

			System.out.println(pair);
			listOfPairs.add(pair);
		}

//		for (String string : listOfPairs) {



		for (String string : listOfPairs) {
			downloadAllCandlesForInterval(string.toUpperCase());
		}


		c.close();

	}

		private static String pairThisPair(String pair) {
			String toReturn= "";
			pair = pair.toLowerCase();
			int lenght = pair.length();

			if(pair.contains("usdt"))
				return "usdt"+pair.substring(0,lenght-4);
			else
				return pair.substring(lenght-3,lenght)+pair.substring(0,lenght-3);
	}

		private static void downloadAllCandlesForInterval(String pairName) {
			ArrayList<BinanceInterval> intervalList = getIntervalList();

			for (BinanceInterval interval : intervalList) {
				System.out.println(pairName+" "+interval);

				boolean executePSQL = executePSQL("select * from "+pairName+".candle"+interval);

				if(!executePSQL)
					createSchemaAndTable(pairName, interval);
				else {
					System.out.println("Tatak tabela juz istnieje");
					ResultSet set = executeSqlQuerry("select min(openTime) from "+pairName+".candle"+interval);
					try {
						if(set.next()) {
							System.out.println(set.getFetchSize());
							long timeValue = set.getLong(1)-1;
							if(timeValue >0) {
							List<BinanceCandlestick> klines;

							do {
								klines = klines(new BinanceSymbol(pairName), interval, 500, timeValue);
								addDataToProBotDataBase(pairName,interval,klines);
								timeValue = getLastestTime(klines)-1;
								System.out.println("List size -> "+klines.size() + "dla "+interval);
							} while(klines.size() == 500);
						}
						}
						set = executeSqlQuerry("select max(openTime) from "+pairName+".candle"+interval);
						set.next();
						hieghsOpenTime = set.getLong(1);
						System.out.println(hieghsOpenTime +"<-- hie");

					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (BinanceApiException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				Long timeValue = System.currentTimeMillis();

				try {
					List<BinanceCandlestick> klines;

					do {
						klines = klines(new BinanceSymbol(pairName), interval, 500, timeValue);
						boolean stop = addDataToProBotDataBase(pairName,interval,klines);
						timeValue = getLastestTime(klines)-1;
						System.out.println("List size -> "+klines.size() + "dla "+interval);
						if(stop) {
							System.out.println("Stop!");
							break;
						}
					} while(klines.size() == 500);

				} catch (BinanceApiException | SQLException e) {
					e.printStackTrace();
				}

				try {
					Thread.sleep(689);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}


		}


		private static Long getLastestTime(List<BinanceCandlestick> klines) {
			long time = System.currentTimeMillis();

			for (BinanceCandlestick binanceCandlestick : klines) {
				time = time < binanceCandlestick.openTime ? time: binanceCandlestick.openTime;

			}

			return time;
		}

		private static boolean addDataToProBotDataBase(String pairName, BinanceInterval interval, List<BinanceCandlestick> klines) throws SQLException {
			c.setAutoCommit(false);
			Statement createStatement = c.createStatement();

			for (BinanceCandlestick candle: klines) {
				if(candle.openTime == hieghsOpenTime)
				{
					System.out.println("Robie Update "+hieghsOpenTime);

					createStatement.executeUpdate("update "+pairName+".candle"+interval
							+"  set openTime = " + candle.open
							+ ", high = " + candle.high
					 		+ ", low = " + candle.low
					 		+ ", close = " + candle.close
					 		+ ", volume = " + candle.volume
					 		+ ", closeTime = " + candle.closeTime
					 		+ ", quoteAssetVolume = " + candle.quoteAssetVolume
					 		+ ", numberOfTrades = " +  candle.numberOfTrades
					 		+ ", takerBuyBaseAssetVolume = " + candle.takerBuyBaseAssetVolume
					 		+ ", takerBuyQuoteAssetVolume = " + candle.takerBuyQuoteAssetVolume
					 		+ " where openTime = " + hieghsOpenTime
							);
					c.commit();

					return true;
				}
				createStatement.executeUpdate("insert into "+pairName+".candle"+interval
						+ "(openTime"
				 		+ ", open"
				 		+ ", high"
				 		+ ", low"
				 		+ ", close"
				 		+ ", volume"
				 		+ ", closeTime"
				 		+ ", quoteAssetVolume"
				 		+ ", numberOfTrades"
				 		+ ", takerBuyBaseAssetVolume"
				 		+ ", takerBuyQuoteAssetVolume)"
						+ "  values ("+candle.openTime
						+", " + candle.open
						+", " + candle.high
						+", " + candle.low
						+", " + candle.close
						+", " + candle.volume
						+", " + candle.closeTime
						+", " + candle.quoteAssetVolume
						+", " + candle.numberOfTrades
						+", " + candle.takerBuyBaseAssetVolume
						+", " + candle.takerBuyQuoteAssetVolume
						+")");

				}
			c.commit();

			return false;

		}

		private static ArrayList<BinanceInterval> getIntervalList() {
			ArrayList<BinanceInterval> toReturn = new ArrayList<>();
			toReturn.add(BinanceInterval.ONE_DAY);
			toReturn.add(BinanceInterval.ONE_HOUR);
			toReturn.add(BinanceInterval.ONE_MIN);
			toReturn.add(BinanceInterval.ONE_MONTH);
			toReturn.add(BinanceInterval.ONE_WEEK);
			toReturn.add(BinanceInterval.FIVE_MIN);
			toReturn.add(BinanceInterval.FIFTEEN_MIN);
			toReturn.add(BinanceInterval.FOUR_HOURS);
			toReturn.add(BinanceInterval.SIX_HOURS);
			toReturn.add(BinanceInterval.EIGHT_HOURS);
			toReturn.add(BinanceInterval.THIRTY_MIN);
			toReturn.add(BinanceInterval.THREE_DAYS);
			toReturn.add(BinanceInterval.THREE_MIN);
			toReturn.add(BinanceInterval.TWELVE_HOURS);
			toReturn.add(BinanceInterval.TWO_HOURS);

			return toReturn;
		}

		public static List<BinanceCandlestick> klines(BinanceSymbol symbol, BinanceInterval interval, int limit, Long timeValue) throws BinanceApiException {
	        String u = api.baseUrl + "v1/klines?symbol=" + symbol.get() + "&interval=" + interval.toString() + "&limit=" + limit;
	        u += "&" + "endTime" + "=" + timeValue;
	        JsonArray jsonElements = (new BinanceRequest(u)).read().asJsonArray();
	        List<BinanceCandlestick> list = new LinkedList<BinanceCandlestick>();
	        for (JsonElement e : jsonElements) list.add(new BinanceCandlestick(e.getAsJsonArray()));
	        return list;
	    }

		public static boolean executePSQL(String querry) {

			try {
			ResultSet rs;
			Statement stmt	 = getConnection().createStatement();
				stmt.execute(querry);
				return true;

			} catch (SQLException e) {
				return false;
			}

		}

		public static ResultSet executeSqlQuerry(String querry) {

			try {
			ResultSet rs;
			Statement stmt	 = c.createStatement();
				rs = stmt.executeQuery(querry);
				return rs;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;

		}

		private static java.sql.Connection getConnection() {
			final String login = "postgres";
			final String password = "postgres";
			final String connectionLink = "jdbc:postgresql://localhost:5432/probot";
			java.sql.Connection c;

			try {
				c = DriverManager.getConnection(connectionLink, login, password);
				return c;
			} catch (SQLException e) {
				System.out.println("Nie ma po³¹czenia");
				e.printStackTrace();
			}
			return null;

		}

		public static void createSchemaAndTable(String pairName, BinanceInterval interval) {
			String querry = "create schema "+pairName;
			executePSQL(querry); // robie schema

			System.out.println(interval.toString());
			querry = "create table "+pairName+".candle"+interval
					+ "(openTime numeric"
			 		+ ", open numeric"
			 		+ ", high numeric"
			 		+ ", low numeric"
			 		+ ", close numeric"
			 		+ ", volume numeric"
			 		+ ", closeTime numeric"
			 		+ ", quoteAssetVolume numeric"
			 		+ ", numberOfTrades int"
			 		+ ", takerBuyBaseAssetVolume numeric"
			 		+ ", takerBuyQuoteAssetVolume numeric)";

			executePSQL(querry); // robie tabele
			ResultSet executeSqlQuerry = executeSqlQuerry("select * from "+pairName+".candle"+interval);
			try {
				System.out.println(executeSqlQuerry.next());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}



		}



		public static void insertDateToTable(Candle someCandle) {


		}

	}