package serwer;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/*
 * Klasa implementujaca operacje obslugujace polecenia przychodzacych do serwera
 * Operacje jako argumenty wejsciowe otrzymuje wartosci dostarczone w poleceniu 
 * Operacje wykonuja zapytania do bazy danych
 * nastepnie zwraca odpowiedz jako true/false lub pobrane z bazy rekordy
 * 
 * @author Marcin Celejewski
 * @version v1.0
 * @see Serwer.java 
 * @see FILReply.java 
 * @see FILReplyElement.java
 * @param c polaczenie z baza danych
 * @param url url bazy danych
 * @param user uzytkownik laczacy sie z baza danych
 * @param password haslo sluzace do laczenia sie z baza danych
 * @param logfile sciezka do zapisania loga jednego polaczenia 
 * 
 */
public class Operations {
	private Connection c;
	// private final String url = "jdbc:postgresql://192.168.1.10/cogdziekiedy";
	private final String url = "jdbc:postgresql://127.0.0.1/postgres";
	private final String user = "serwer";
	private final String password = "bananapi";
	private String logfile;

	/*
	 * Konstruktor tworzacy nazwe kolejnego loga
	 * @param ip ip uzytkownika ktory wyslal polecenie
	 * @return nothing
	 * 
	 */
	public Operations(String ip) {
		DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
		Date date = new Date();

		System.out.println("D:\\logiserwera\\" + dateFormat.format(date) + "_" + ip + ".log");
		logfile = "D:\\logiserwera\\" + dateFormat.format(date) + "_" + ip + ".log";

	}

	/*
	 * Metoda zapisujaca logi do pliku
	 * @param s string ktory chcemy zapisac do logow
	 * @return nothing
	 * @exception IOException On input error.
	 * @see IOException
	 * 
	 */
	void log(String s) {
		FileWriter fw;
		try {
			fw = new FileWriter(logfile, true);

			fw.write("\n" + s + "\n");
			fw.close();
			System.out.println(s);

		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/*
	 * Metoda sluzaca do polaczenia sie z baza danych 
	 * @param nothing
	 * @return ifconnected
	 * @exception Exception 
	 * @see Exception
	 * 
	 */
	public boolean connect() {
		try {
			Class.forName("org.postgresql.Driver");

			c = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
			log("Cannot connect to databsase. " + e.toString());
			return false;
		}
		log("Opened database successfully");
		return true;
	}
	/*
	 * Metoda obsluzaca logowanie sie do systemu 
	 * @param login login uzytkownika
	 * @param password haslo uzytkownika
	 * @return iflogged
	 * @exception SQLException e
	 * @see SQLException e
	 * 
	 */
	public boolean login(String login, String password) {
		boolean correct = false;
		try {
			PreparedStatement ps = c.prepareStatement("SELECT * FROM Moderators WHERE login = ? AND password = ?;");
			ps.setString(1, login);
			ps.setInt(2, Integer.parseInt(password));
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				correct = true;
			}
			rs.close();
			ps.close();
			c.close();

			log("Loged in succussfully");

		} catch (SQLException e) {
			log("Cannot execute this login statment. " + e.toString());
		}
		return correct;
	}
	/*
	 * Metoda pobierajaca okreslone lokacje z bazy danych 
	 * @param latitude szerokosc geograficzna uzytkownika
	 * @param longitude dlugosc geograficzna uzytkownika
	 * @param radius promien w jakim nalezy szukac lokacji
	 * @param tags tablica tagow z lokacjami, ktore nalezy wyszukac
	 * @return tosend
	 * @exception SQLException e
	 * @see SQLException e
	 * @see FILReply.java
	 */
	public FILReply findPlace(double latitude, double longitude, double radius, String[] tags) {
		List<Long> tags_ID = new ArrayList<Long>();
		List<FILReplyElement> result = new ArrayList<FILReplyElement>();
		FILReply tosend = new FILReply();

		if (tags.length > 0) {
			tags_ID = findTags(tags);
		}

		double deltaLat = radius * 0.00001451;
		double deltaLong = radius * 0.00000878;

		String query = "SELECT Location_ID, Location_Name, Latitude_cord, Longitude_cord, Tags, Description "
				+ "FROM Location " + "WHERE (Latitude_cord BETWEEN ? AND ?) AND (Longitude_cord BETWEEN ? AND ?)";
		if (tags_ID.size() > 0) {
			query = query.concat(" AND (Tags LIKE '%|" + Long.toString(tags_ID.get(0)) + "|%'");
			if (tags_ID.size() > 1) {
				for (int i = 1; i < tags_ID.size(); i++) {
					query = query.concat(" OR Tags LIKE '%|" + Long.toString(tags_ID.get(i)) + "|%'");
				}

			}
			query = query.concat(") GROUP BY Location_ID;");
		} else {
			query = query.concat(" GROUP BY Location_ID;");
		}

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			ps = c.prepareStatement(query);
			ps.setDouble(1, latitude - deltaLat);
			ps.setDouble(2, latitude + deltaLat);
			ps.setDouble(3, longitude - deltaLong);
			ps.setDouble(4, longitude + deltaLong);
			rs = ps.executeQuery();

		} catch (SQLException e) {
			log("findPlace " + e.toString());
		}

		try {
			while (rs.next()) {
				FILReplyElement t = new FILReplyElement();
				t.name = rs.getString("Location_Name");
				t.latitude = rs.getDouble("Latitude_cord");
				t.longitude = rs.getDouble("Longitude_cord");
				t.description = rs.getString("description");
				result.add(t);
			}
		} catch (SQLException e) {
			log("findPlace " + e.toString());
		}
		FILReplyElement[] tab = new FILReplyElement[result.size()];
		for (int i = 0; i < result.size(); i++) {
			tab[i] = result.get(i);

		}
		tosend.arr = tab;
		try {
			rs.close();
			ps.close();
			c.close();
		} catch (SQLException e) {
			log("findPlace " + e.toString());
		}

		log("findedPlace successfully");
		return tosend;
	}
	/*
	 * Metoda pobierajaca okreslone wydarzenia z bazy danych 
	 * @param latitude szerokosc geograficzna uzytkownika
	 * @param longitude dlugosc geograficzna uzytkownika
	 * @param radius promien w jakim nalezy szukac wydarzen
	 * @param tags tablica tagow z wydrzeniami, ktore nalezy wyszukac
	 * @return result
	 * @exception SQLException e
	 * @see SQLException e
	 * @see FILReplyElement.java
	 */
	public FILReplyElement[] findEvent(double latitude, double longitude, double radius, String[] tags) {

		List<Long> tags_ID = new ArrayList<Long>();
		List<FILReplyElement> result = new ArrayList<FILReplyElement>();
		if (tags.length > 0) {
			tags_ID = findTags(tags);
		}

		double deltaLat = radius * 0.00001451;
		double deltaLong = radius * 0.00000878;

		try {
			String query = "SELECT Event_ID, Event_Name, Latitude_cord, Longitude_cord, Date, Time, Tags, Description "
					+ "FROM Events "
					+ "WHERE Time >= ? AND Date == ? AND (Latitude_cord BETWEEN ? AND ?) AND (Longitude_cord BETWEEN ? AND ?)";
			if (tags_ID.size() > 0) {
				query = query.concat(" AND (Tags LIKE '%|" + Long.toString(tags_ID.get(0)) + "|%'");
				if (tags_ID.size() > 1) {
					for (int i = 1; i < tags_ID.size(); i++) {
						query = query.concat(" OR Tags LIKE '%|" + Long.toString(tags_ID.get(i)) + "|%'");
					}

				}
				query = query.concat(") GROUP BY Event_ID;");
			} else {
				query = query.concat(" GROUP BY Event_ID;");
			}

			Calendar cal = Calendar.getInstance();
			SimpleDateFormat time = new SimpleDateFormat("HH:mm:ss");
			SimpleDateFormat date = new SimpleDateFormat("yyyy/mm/dd");

			PreparedStatement ps = c.prepareStatement(query);

			ps.setString(1, time.format(cal.getTime()));
			ps.setString(2, date.format(cal.getTime()));
			ps.setString(3, Double.toString(latitude - deltaLat));
			ps.setString(4, Double.toString(latitude + deltaLat));
			ps.setString(5, Double.toString(longitude - deltaLong));
			ps.setString(6, Double.toString(longitude + deltaLong));

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FILReplyElement t = new FILReplyElement();
				t.name = rs.getString("Event_Name");
				t.latitude = rs.getDouble("Latitude_cord");
				t.longitude = rs.getDouble("Longitude_cord");
				t.date = rs.getString("Date");
				t.time = rs.getString("Time");
				t.description = rs.getString("Description");
				result.add(t);

			}
			rs.close();
			ps.close();
			c.close();

		} catch (SQLException e) {

			log("Cannot execute this find event statment " + e.toString());
		}

		log("findedEvent successfully");
		return result.toArray(new FILReplyElement[result.size()]);

	}
	/*
	 * Metoda uzupelniajaca baze danych o przeslana lokacje 
	 * @param name nazwa lokacji
	 * @param latitude szerokosc geograficzna lokacji
	 * @param longitude dlugosc geograficzna lokacji
	 * @param tags tablica tagow opisujaca lokacje
	 * @param owner wlasciciel lokacji
	 * @param description opis lokacji
	 * @return correct
	 * @exception SQLException e
	 * @see SQLException e
	 */
	public boolean addPlace(String name, String latitude, String longitude, String tags[], String owner,
			String description) {
		System.out.println("addplane\n");
		boolean correct = false;
		try {
			String stringtags = "|";
			for (int i = 0; i < tags.length; i++) {
				stringtags += Long.toString(checkTag(tags[i])) + "|";
			}
			PreparedStatement ps = c.prepareStatement(
					"INSERT INTO Location (Location_Name, Latitude_cord, Longitude_cord, Tags, Owner, Description)"
							+ " VALUES (?,?,?,?,?,?);");
			ps.setString(1, name);
			ps.setFloat(2, Float.parseFloat(latitude));
			ps.setFloat(3, Float.parseFloat(longitude));
			ps.setString(4, stringtags);
			ps.setString(5, owner);
			ps.setString(6, description);
			ps.executeUpdate();
			correct = true;
			ps.close();
			c.close();

		} catch (SQLException e) {
			log("Cannot execute this addPlace event statment " + e.toString());
		}
		log("addedPlace succussfully");
		return correct;
	}
	/*
	 * Metoda uzupelniajaca baze danych o przeslane wydarzenie 
	 * @param name nazwa lokacji
	 * @param latitude szerokosc geograficzna lokacji
	 * @param longitude dlugosc geograficzna lokacji
	 * @param tags tablica tagow opisujaca lokacje
	 * @param owner wlasciciel lokacji
	 * @param description opis lokacji
	 * @param date data wydarzenia
	 * @param time godzina wydarzenia
	 * @return correct
	 * @exception SQLException e
	 * @see SQLException e
	 */
	public boolean addEvent(String name, Double latitude, Double longitude, String tags[], String owner,
			String description, String date, String time) {
		boolean correct = false;
		try {
			String stringtags = "|";
			for (int i = 0; i < tags.length; i++) {
				stringtags += Long.toString(checkTag(tags[i])) + "|";
			}
			PreparedStatement ps = c.prepareStatement(
					"INSERT INTO Events (Event_Name, Latitude_cord, Longitude_cord, Date, Time, Tags, Owner, Description)"
							+ " VALUES (?,?,?,?,?,?,?,?);");

			ps.setString(1, name);
			ps.setDouble(2, latitude);
			ps.setDouble(3, longitude);
			ps.setString(4, date);
			ps.setString(5, time);
			ps.setString(6, stringtags);
			ps.setString(7, owner);
			ps.setString(8, description);
			ps.executeUpdate();
			ps.close();
			c.close();

			correct = true;
		} catch (SQLException e) {
			log("Cannot add event " + e.toString());
		}
		log("addedEvent succussfully");
		return correct;
	}
	/*
	 * Metoda wyszukujaca id tagow
	 * @param tags tablica tagow 
	 * @return tags_ID
	 * @exception SQLException e
	 * @see SQLException e
	 */
	protected List<Long> findTags(String[] tags) {
		List<Long> tags_ID = new ArrayList<Long>();
		try {
			String query = "SELECT * FROM Tags WHERE ";
			for (int i = 0; i < tags.length - 1; i++) {
				query = query.concat(" Tag LIKE '" + tags[i] + "' OR");
			}
			query = query.concat(" Tag LIKE '" + tags[tags.length - 1] + "' GROUP BY ID;");

			PreparedStatement ps = c.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				long a = rs.getLong("ID");
				tags_ID.add(a);
			}
			rs.close();
			ps.close();

		} catch (SQLException e) {
			log("Cannot find tags " + e.toString());
		}
		log("findedTags successfully");
		return tags_ID;

	}
	/*
	 * Metoda wyszukujaca wszystkie lokacje danego wlasciciela
	 * @param login login wlasciciela
	 * @return result
	 * @exception SQLException e
	 * @see SQLException e
	 * @see FILReplyElement.java
	 */
	public FILReplyElement[] findOwnLocation(String login) {
		// potrzebne zabezpieczenie if login = owner dopiero wykonuj.
		List<FILReplyElement> result = new ArrayList<FILReplyElement>();
		try {
			String query = "SELECT * FROM Location WHERE Owner = ? GROUP BY Location_ID";

			PreparedStatement ps = c.prepareStatement(query);
			ps.setString(1, login);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				FILReplyElement tosend = new FILReplyElement();

				tosend.location_id = rs.getInt("Location_ID");
				tosend.name = rs.getString("Location_Name");
				tosend.latitude = rs.getDouble("Latitude_cord");
				tosend.longitude = rs.getDouble("Longitude_cord");
				tosend.tags = rs.getString("Tags");
				tosend.owner = login;
				tosend.description = rs.getString("Description");
				result.add(tosend);
			}
			rs.close();
			ps.close();
			c.close();

		} catch (SQLException e) {
			log("Cannot find own locations " + e.toString());
		}
		log("findedOwnLocations successfully");
		return result.toArray(new FILReplyElement[result.size()]);
	}
	/*
	 * Metoda wyszukujaca czy dany tag nie znajduje sie juz w bazie
	 * @param tag opis taga
	 * @return id
	 * @exception SQLException e
	 * @see SQLException e
	 */
	public long checkTag(String tag) {
		String query = "SELECT * FROM Tags WHERE Tag=?";
		long id;
		try {
			PreparedStatement ps = c.prepareStatement(query);
			ps.setString(1, tag);
			System.out.println(ps);
			
			ResultSet rs = ps.executeQuery();


			if (rs.next()) {
				id = rs.getLong("ID");
				log("chceckedTag successfully");
				ps.close();
				rs.close();
				return id;
			} else {

				ps.close();
				rs.close();

				log("didnt find tag");

				query = "INSERT into Tags (Tag) values(?);";
				PreparedStatement ps2 = c.prepareStatement(query);
				ps2.setString(1, tag);
				ps2.executeUpdate();

				ps2.close();

				query = "SELECT * FROM Tags WHERE Tag = ?;";
				ps2 = c.prepareStatement(query);
				ps2.setString(1, tag);
				rs = ps2.executeQuery();

				if (rs.next()) {
					id = rs.getLong("ID");
					log("chceckedTag successfully");
					ps2.close();
					rs.close();
					return id;

				} else {
					ps2.close();
					rs.close();
					log("straszny blad check tags");
					return 0;
				}
			}
		} catch (SQLException e) {
			log("Cannot check Tag " + e.toString());
		}
		return 0;

	}

}