package swen304;
import java.sql.*;
/*
 * LibraryModel.java
 * Author:
 * Created on:
 */
import java.util.ArrayList;
import java.util.List;

import javax.swing.*;

public class LibraryModel {

	private Connection con = null;

	// For use in creating dialogs and making them modal
	private JFrame dialogParent;

	public LibraryModel(JFrame parent, String userid, String password) {
		dialogParent = parent;
		userid = "vrecicstef";
		try {
			Class.forName("org.postgresql.Driver");
		} catch(ClassNotFoundException cnfe) {
			System.out.println("Can not find the driver class: " +
					"\nEither I have not installed it properly or \n postgresql.jar file is not in my CLASSPATH)");
		}

		String url = "jdbc:postgresql:"+ "//db.ecs.vuw.ac.nz/" + userid + "_jdbc";

		try{
			con = DriverManager.getConnection(url,
					userid, password);
			System.out.println("SUCCESSFULLY CONNECTED");
		}
		catch (SQLException sqlex){
			System.out.println("Can not connect");
			System.out.println(sqlex.getMessage() +"...");
		}

	}

	public String bookLookup(int isbn) {
		String QUERY = "Select * from book WHERE isbn =" + isbn + ";" ;
		String BOOK = "";
		int i = 0;
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QUERY);

			while (rs.next()){
				i++;
				BOOK = rs.getString(2);
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		if (i==0)
			System.out.println("No results");

		return BOOK;
	}

	public String showCatalogue() {

		String QUERY = "select isbn,title,COUNT(isbn) AS dupe  from book_author NATURAL JOIN book group by isbn,title;";
		String RES = "";
		// rs.getString(2);
		int i = 0;

		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QUERY);
			while (rs.next()){
				i++;
				RES = RES + rs.getString(2) + "(" + rs.getInt(3) + ")" + "\n ISBN: " + rs.getString(1) + "\n";
//				System.out.println(rs.getString(2));
//				System.out.println(rs.getString(0));
//				rs.getInt(3);
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}

			if (i==0)
				System.out.println("OUT OF BOOKS");
		return RES;
	}

	public String showLoanedBooks() {
//		String QUERY = "Select * from cust_book;";
		String QUERY =  "select isbn,duedate,customerid,title from cust_book NATURAL JOIN book;";
		String loanedBook = "";
		int i = 0;
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QUERY);
			while (rs.next()){
				i++;
				 loanedBook = loanedBook + "Title:" + rs.getString(4) + "\n ISBN:" + rs.getInt(1) +
						 " Customer:" + rs.getInt(3) + " DUE:" + rs.getDate(2) + "\n";

				System.out.println(loanedBook);
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		if (i==0)
				System.out.println("no books issued");

		return loanedBook;
	}

	public String showAuthor(int authorID) {
		String QUERY = "select * from AUTHOR where authorid = " + authorID + ";";
		String auth = "";

		int i = 0;
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QUERY);
			while (rs.next()){
				i++;
				 auth = auth + rs.getString(2) + " " + rs.getString(3);

			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		if (i==0)
				System.out.println("no books issued");


		return auth;
	}

	public String showAllAuthors() {
		String QR = "select * from AUTHOR;";
		List<Integer> authorList = new ArrayList<Integer>();
		String auth = "";
		int i = 0;
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QR);
			while (rs.next()){
				i++;
				auth = auth + rs.getString(2) + " " + rs.getString(3) + "\n";
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		if (i==0) return "NO AUTHORS!";


		return auth;
	}

	public String showCustomer(int customerID) {
		String QR = "select * from customer where customerid = " + customerID + ";";
		int i = 0;
		String CUST = "";
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QR);
			while (rs.next()){
				i++;
				CUST = rs.getString(3) + " " + rs.getString(2) + "(" + rs.getInt(1) + ")" + "\n" + rs.getString(4) ;
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		if (i==0) return "No customer with such ID" ;
		return CUST;
	}

	public String showAllCustomers() {
		String QR = "select * from customer;";
		String CUST = "";
		int i = 0;
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QR);
			while (rs.next()){
				i++;
				CUST = CUST + rs.getString(3) + " " + rs.getString(2) + "(" + rs.getInt(1) + ")" + "\n" + rs.getString(4) + "\n" ;
			}
		} catch (SQLException sqlex)	{
			System.out.println(sqlex.getMessage());
		}
		return CUST;
	}

	public String borrowBook(int isbn, int customerID,
			int day, int month, int year) {

		String QR_CheckCustomer = "Select * from customer where customerid = " + customerID + ";";
		String QR_CheckBook = "Select * from book where isbn = " + isbn + ";";
		String QR_Insert = "Insert into cust_book values(" +isbn + ",'" + year + "-" + month + "-" + day + "'," + customerID + ");" ;
		String QR_BookCt = "Select numLeft from book where isbn = " + isbn + ";";
		System.out.println(QR_Insert);
		int i = 0; int j =0; int k = 0;
		int current_numLeft = -1;

		// check to see if the customer already has this book issued. This library won't allow customers to issue more than one copy of the same book
		String QR_CheckDup = "Select * from cust_book where customerid = " + customerID + " AND isbn = " + isbn + ";";
		try {
			Statement s0 = con.createStatement();
			ResultSet rs0 = s0.executeQuery(QR_CheckDup);
			while (rs0.next()){
				i++;
			}
		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		}
		if (i > 0) return "Customer is greedy, stop trying to issue multiple copies of same book";

		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(QR_CheckCustomer);
			while (rs.next()){
				i++;
			}
		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		}
		if (i==0) return "No such customer";

		try {
			Statement s2 = con.createStatement();
			ResultSet rs2 = s2.executeQuery(QR_CheckBook);
			while (rs2.next()){
				if (rs2.getInt(5) > 0) {
					j++;
					current_numLeft = rs2.getInt(5);
				}
			}
		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		}
		if (j==0) return "No such book";
		// lock customer
		try {
			Statement s3 = con.createStatement();
			s3.executeUpdate(QR_Insert);

		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		}
			// check that it succesfully added
			// reduce a copy

		System.out.println(current_numLeft + "");
		String QR_Update = "Update book set numleft = " + (current_numLeft - 1) + "where isbn = " + isbn + ";";
		try {
			Statement s4 = con.createStatement();
			s4.executeUpdate(QR_Update);
		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		}

		return "Succesfully borrowed book";
	}

	public String returnBook(int isbn, int customerid) {
//		String QR_booksLeft = "select numleft from book where isbn = " + isbn + ";";
		String QR_CheckBook = "Select * from book where isbn = " + isbn + ";";
		int current_numLeft = 0; int j =0;
//		String QR
		// update book set numleft = 5 where isbn = 9009;

		String QR_RETURN = "Delete from cust_book where customerid = " + customerid + " AND isbn = " + isbn + ";";

		try {
			Statement s2 = con.createStatement();
			ResultSet rs2 = s2.executeQuery(QR_CheckBook);
			while (rs2.next()){
				if (rs2.getInt(5) > 0) {
					j++;
					current_numLeft = rs2.getInt(5);
				}
			}
		} catch (SQLException sqlex)	{
			System.out.println("????????????");
			System.out.println(sqlex.getMessage());
		} if (j==0) return "No such book";

		current_numLeft++;
			String QR_updateBooks = "update book set numleft = " + current_numLeft + " where isbn = " + isbn + ";";
			try {
				Statement s4 = con.createStatement();
				s4.executeUpdate(QR_updateBooks);
			} catch (SQLException sqlex)	{
				System.out.println("????????????");
				System.out.println(sqlex.getMessage());
			}

			try {
				Statement s5 = con.createStatement();
				s5.executeUpdate(QR_RETURN);
			} catch (SQLException sqlex)	{
				System.out.println("????????????");
				System.out.println(sqlex.getMessage());
			}

		return "Succesfully returned";
	}

	public void closeDBConnection() {
	}

	public String deleteCus(int customerID) {
		return "Delete Customer";
	}

	public String deleteAuthor(int authorID) {
		return "Delete Author";
	}

	public String deleteBook(int isbn) {
		return "Delete Book";
	}
}