/**
 * @(#)CollectionOfBooks.java
 *
 * Handles most functions and methods of the library.
 * of the user interface.  Does not rely on anything
 * from the user interface classes.
 *
 * @author Rami Awad, Erik Stenström
 * @date 2011/10/24
 */

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.sql.*;

public class CollectionOfBooks {
	public static Connection con = null;



	public static void connect() {
		String user = "erik"; // username, e.g. "postgres"
		String pwd = "12345"; // password to your database
		String database = "lab_databas";
		String server = "jdbc:postgresql://localhost:5432/" + database
				+ "?UseClientEnc=UTF8";

		try {
			Class.forName("org.postgresql.Driver").newInstance();
			con = DriverManager.getConnection(server, user, pwd);
			System.out.println("Connected!");
			Statement stmt = null;
		} catch (Exception e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error connect, " + e.toString());
		}

	}

	public static void disconnect() {
		try {
			con.close();
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error disconnect, " + e.toString());
		}
	}

	public static boolean addArtist(String Artist, String Album,
			String Producer, String Genre, Integer Rating, Integer Year,
			String Label) {
		connect();
		try {
			// Queries for checking if artist and/or album exists
			String checkartist = "SELECT count(*) FROM ARTIST where namn = ?;";
			String checkalbum = "SELECT count(*) FROM Album where namn = ?;";
			PreparedStatement checkArtist = con.prepareStatement(checkartist);
			PreparedStatement checkAlbum = con.prepareStatement(checkalbum);
			checkArtist.setString(1, Artist);
			checkAlbum.setString(1, Album);
			ResultSet rsArtist = checkArtist.executeQuery();
			ResultSet rsAlbum = checkAlbum.executeQuery();
			rsArtist.next();
			rsAlbum.next();
			// if album and artist exist, disconnect and return false
			if (rsAlbum.getInt(1) != 0 && rsArtist.getInt(1) != 0) {
				disconnect();
				return false;
			} else if (rsArtist.getInt(1) != 0) {
				// Query for fetching artist id
				String getartistid = "SELECT artist_id FROM ARTIST where namn = ?;";
				PreparedStatement getArtistId = con
						.prepareStatement(getartistid);
				getArtistId.setString(1, Artist);
				ResultSet rs = getArtistId.executeQuery();
				rs.next();
				int artistid = rs.getInt(1);
				// Query for adding album to already existing artist
				String addalbum = "INSERT INTO Skivbolag (namn) Values (?);"
						+ "INSERT INTO Producent (namn) Values (?);"
						+ "INSERT INTO ALBUM(Artist_id, Betyg, Genre, Utgivningsar, Namn, Skivbolag, Producent) VALUES (?,?,?,?,?,currval('Skivbolag_seq'),currval('Producent_seq'));";
				PreparedStatement addAlbum = con.prepareStatement(addalbum);
				addAlbum.setString(1, Label);
				addAlbum.setInt(2, artistid);
				addAlbum.setInt(3, Rating);
				addAlbum.setString(4, Genre);
				addAlbum.setInt(5, Year);
				addAlbum.setString(6, Album);
			//	addalbum.SetString(7,Producer);
				int m = addAlbum.executeUpdate();
				disconnect();
				return true;
			} else {
				// Query for adding artist and album at the same time.
				String sql = "INSERT INTO Artist(namn) VALUES (?); "
						+ "INSERT INTO Skivbolag (namn) VALUES (?);"
						+ "INSERT INTO Producent (namn) Values (?);"
						+ "INSERT INTO ALBUM(Artist_id, Betyg, Genre, Utgivningsar, Namn, Skivbolag, Producent) VALUES (currval('Artist_id_seq'),?,?,?,?,currval('Skivbolag_seq'),currval(?));";

				PreparedStatement addArtist = con.prepareStatement(sql);

				addArtist.setString(1, Artist);
				addArtist.setString(2, Label);
				addArtist.setInt(3, Rating);
				addArtist.setString(4, Genre);
				addArtist.setInt(5, Year);
				addArtist.setString(6, Album);
			//	addArtist.setInt(7,Producer);
				int n = addArtist.executeUpdate();
				disconnect();
				return true;
			}
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error on insert: " + e.toString());
		}
		return false;
	}

	public static boolean addTrack(String Album, String Track, String Length) {
		connect();
		try {
			// Queries for checking if album and/or track exists
			String checkalbum = "SELECT count(*) FROM ALBUM where namn = ?;";
			String checktrack = "SELECT count(*) FROM LAT where namn = ?;";
			PreparedStatement checkAlbum = con.prepareStatement(checkalbum);
			PreparedStatement checkTrack = con.prepareStatement(checktrack);
			checkAlbum.setString(1, Album);
			checkTrack.setString(1, Track);
			ResultSet rsAlbum = checkAlbum.executeQuery();
			ResultSet rsTrack = checkTrack.executeQuery();
			rsAlbum.next();
			rsTrack.next();
			// if album AND track exist, disconnect and return false
			if (rsAlbum.getInt(1) != 0 && rsTrack.getInt(1) != 0) {
				disconnect();
				return false;
			} else if (rsAlbum.getInt(1) != 0) {// if album already exists
				// Query for fetching artist id
				String getalbumid = "SELECT album_id FROM ALBUM where namn = ?;";
				PreparedStatement getAlbumId = con.prepareStatement(getalbumid);
				getAlbumId.setString(1, Album);
				ResultSet rs = getAlbumId.executeQuery();
				rs.next();
				int Albumid = rs.getInt(1);
				// Query for adding track to already existing album
				String addtrack = "INSERT INTO LAT (Namn, langd, album_id) Values (?,?,?);";
				PreparedStatement addTrack = con.prepareStatement(addtrack);
				addTrack.setString(1, Track);
				addTrack.setString(2, Length);
				addTrack.setInt(3, Albumid);
				int m = addTrack.executeUpdate();
				disconnect();
				return true;
			}

		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error on insert: " + e.toString());
		}
		return false;
	}

	public ResultSet searcByArtist(String Artist){
		ResultSet rs = null;
		try{
		String getbyartist = "SELECT l.namn, ar.namn, l.langd, al.namn FROM lat l, artist ar, album al where ar.namn = ? and ar.artist_id = al.artist_id and l.album_id = al.album_id;";
		PreparedStatement getByArtist = con.prepareStatement(getbyartist);
		getByArtist.setString(1, Artist);
		rs = getByArtist.executeQuery();
		return rs;
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error: " + e.toString());
		}
		return rs;
	}

	public ResultSet searcByAlbum(String Album){
		ResultSet rs = null;
		try{
		String getbyalbum = "SELECT l.namn, ar.namn, l.langd, al.namn FROM lat l, artist ar, album al where al.namn = ? and al.album_id = l.album_id and al.artist_id = ar.artist_id;";
		PreparedStatement getByAlbum = con.prepareStatement(getbyalbum);
		getByAlbum.setString(1, Album);
		rs = getByAlbum.executeQuery();
		return rs;
		} catch (SQLException e) {
			javax.swing.JOptionPane.showMessageDialog(null,
					"Database error: " + e.toString());
		}
		return rs;
	}
		public ResultSet searcByGenre(String Genre){
			ResultSet rs = null;
			try{
			String getbygenre = "SELECT l.namn, ar.namn, l.langd, al.namn FROM lat l, artist ar, album al where al.namn = ? and al.album_id = l.album_id and al.artist_id = ar.artist_id;";
			PreparedStatement getByGenre = con.prepareStatement(getbygenre);
			getByGenre.setString(1, Genre);
			rs = getByGenre.executeQuery();
			return rs;
			} catch (SQLException e) {
				javax.swing.JOptionPane.showMessageDialog(null,
						"Database error: " + e.toString());
			}
			return rs;


	}


		public ResultSet searcByLabel(String Label){
			ResultSet rs = null;
			try{
			String getbylabel = "SELECT l.namn, ar.namn, l.langd, al.namn FROM lat l, artist ar, album al where al.namn = ? and al.album_id = l.album_id and al.artist_id = ar.artist_id;";
			PreparedStatement getByLabel = con.prepareStatement(getbylabel);
			getByLabel.setString(1, Label);
			rs = getByLabel.executeQuery();
			return rs;
			} catch (SQLException e) {
				javax.swing.JOptionPane.showMessageDialog(null,
						"Database error: " + e.toString());
			}
			return rs;


	}

	public static void executeQuery(Connection con, String query)
			throws SQLException {

		Statement stmt = null;
		try {
			// Execute the SQL statement
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			// Get the attribute names
			ResultSetMetaData metaData = rs.getMetaData();
			int ccount = metaData.getColumnCount();
			for (int c = 1; c <= ccount; c++) {
				System.out.print(metaData.getColumnName(c) + "\t");
			}
			System.out.println();

			// Get the attribute values
			while (rs.next()) {
				for (int c = 1; c <= ccount; c++) {
					System.out.print(rs.getObject(c) + "\t");
				}
				System.out.println();
			}

		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
	}

}