package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import domain.*;

public class PgDatabase extends Database {

	private Connection db;
	private Statement sql;
	private String username;
	private String password;
	private String uri;
	private String location = "machkau.auteur";
	private Database database;

	public PgDatabase() throws DataBaseException{
		setUsername("518561");
		System.out.println(getUsername());
		setPassword("DitIsMijnPasswoordniet");
		setUri("jdbc:postgresql://" + database);

		init();
	}

	public void init() throws DataBaseException {





		try {
			db = DriverManager.getConnection(getUri(), getUsername(),
					getPassword());
			setSql(db.createStatement());
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DataBaseException(e);

		}

	}

	private String getLocation() {
		return this.location;
	}

	private String getUsername() {
		return this.username;
	}

	private String getPassword() {
		return this.password;
	}

	private String getUri() {
		return this.uri;
	}

	private void setUri(String uri) throws DataBaseException {
		if (this.uri == null || uri.equals("")) {
			throw new DataBaseException("uri is null or empty");
		} else {
			this.uri = uri;
		}
	}

	private void setPassword(String password) throws DataBaseException {
		if (this.password == null || password.equals("")) {
			throw new DataBaseException(" password is null or empty");
		} else {

			this.password = password;
		}
	}

	public void setUsername(String username) throws DataBaseException {
		if (username == null || username.equals("")) {
			throw new DataBaseException("username is null or empty");
		} else {
			this.username = username;
		}
	}

	public void setSql(Statement sql) throws DataBaseException {
		if (sql == null || sql.equals("")) {
			throw new DataBaseException("statement is null or empty");
		} else {
			this.sql = sql;
		}
	}

	public Statement getSql() {
		return sql;
	}

	@Override
	public boolean createAuteur(Auteur auteur) throws DataBaseException {
		boolean bool = false;
		if (auteur == null || auteur.equals("")) {
			throw new DataBaseException("auteur is null or empty");
		} else {
			if (getAuteur(auteur.getId()) != null) {// als id al bestaat
				throw new DataBaseException("auteur met dit id bestaat al");
			} else {
				String query = "INSERT INTO " + getLocation()
						+ "(auteursnr,voornaam,naam) " + "values('" + auteur.getId()
						+ "','" + auteur.getName() + "','"
						+ auteur.getLastname() + "');";
				try {
					int res = getSql().executeUpdate(query);
					if (res > 0) {
						bool = true;
					}
				} catch (SQLException e) {
					 System.err.println(e.getMessage());
					throw new DataBaseException(e);
				}
			}
		}
		// TODO Auto-generated method stub
		return bool;
	}

	@Override
	public boolean deleteAuter(int id) throws DataBaseException {
		boolean succes = false;
		if (id < 0) {
			throw new DataBaseException("id is not valid");
		} else {
			if (getAuteur(id) != null) {// eerst kijken of hij bestaat
				String qry = ("DELETE FROM " + getLocation()
						+ " WHERE auteursnr = " + id + ";");
				try {
					int res = sql.executeUpdate(qry);
					if (res > 0) {
						succes = true;
					}
				} catch (SQLException e) {
					throw new DataBaseException(e);
				}
			}
		}
		return succes;
	}

	public boolean deleteAuter(Auteur auteur) throws DataBaseException {
		boolean bool = false;
		if (auteur == null || auteur.equals("")) {
			throw new DataBaseException("auteur is null or empty");
		} else {
			if (getAuteur(auteur.getId()) != null) {
				String qry = ("DELETE FROM " + getLocation()
						+ " WHERE voornaam LIKE '" + auteur.getName()
						+ "' AND naam LIKE '" + auteur.getLastname() + "';");

				try {
					int res = sql.executeUpdate(qry);
					if (res > 0) {
						bool = true;
					}
				} catch (SQLException e) {
					throw new DataBaseException(e);
				}
			}
		}
		return bool;
	}

	@Override
	public ArrayList<Auteur> giveList() throws DataBaseException {
		ArrayList<Auteur> list = null;
		list = database.giveList();
		return list;
		// TODO checken

	}

	@Override
	public boolean updateAuteur(Auteur auteur) throws DataBaseException {
		boolean succes = false;
		boolean bool = false;
		if (auteur == null || auteur.equals("")) {
			throw new DataBaseException("auteur is null or empty");
		} else {

			/*
			 * TODO keuze tussen eerst auteur deleten en dan een nieuwe creeren
			 * of via update in query String qry = "UPDATE " + getLocation() +
			 * " " + "voornaam = '"+ auteur.getName() + "' " + "naam = '" +
			 * auteur.getLastname() + "' ;"; try{ succes=sql.execute(qry);
			 * }catch (SQLException e){ throw new DataBaseException(e); }
			 */
			deleteAuter(auteur);
			bool = true;
		}
		if (bool) {
			createAuteur(auteur);
			succes = true;
		} else {
			succes = false;
		}
		return succes;
	}

	@Override
	public Auteur getAuteur(int id) throws DataBaseException {
		boolean succes = false;
		Auteur auteur = null;
		ResultSet nm;
		ResultSet an;
		if (id < 0) {
			throw new DataBaseException("id is not valid");
		} else {
			String naam = "SELECT voornaam FROM " + getLocation() + " "
					+ "WHERE auteursnr = " + id + " ;";
			String lastname = "SELECT naam FROM " + getLocation() + " "
					+ "WHERE auteursnr = " + id + " ;";
			try {
				// succes = sql.execute(naam);
				// succesLastname = sql.execute(lastname);
				System.out.println(naam);
				nm = sql.executeQuery(naam);
				an = sql.executeQuery(lastname);
				succes = true;
			} catch (SQLException e) {
				throw new DataBaseException(e);
			}
		}
		if (succes) {
			try {
				auteur = new Auteur(id, nm.getString("voornaam"),
						an.getString("naam"));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataBaseException(e);
			}
		} else {
			throw new DataBaseException("did not found auteur with given id");
		}
		return auteur;
	}

	@Override
	public void save() throws DataBaseException {
		// database.save();

	}

}