package datamodel;

import java.sql.*;
import java.util.ArrayList;
/**
 * Database is a class that specifies the interface to the
 * movie database. Uses JDBC and the MySQL Connector/J driver.
 */
public class Database {
    /**
     * The database connection.
     */
    private Connection conn;

    /**
     * Create the database interface object. Connection to the database
     * is performed later.
     */
    public Database() {
        conn = null;
    }

    /**
     * Open a connection to the database, using the specified user name
     * and password.
     *
     * @param userName The user name.
     * @param password The user's password.
     * @return true if the connection succeeded, false if the supplied
     * user name and password were not recognized. Returns false also
     * if the JDBC driver isn't found.
     */
    public boolean openConnection(String userName, String password) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection
                ("jdbc:mysql://puccini.cs.lth.se/" + userName,
                 userName, password);
        }
        catch (SQLException e) {
            System.err.println(e);
            e.printStackTrace();
            return false;
        }
        catch (ClassNotFoundException e) {
            System.err.println(e);
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * Close the connection to the database.
     */
    public void closeConnection() {
        try {
            if (conn != null)
                conn.close();
        }
        catch (SQLException e) {
        	e.printStackTrace();
        }
        conn = null;

        System.err.println("Database connection closed.");
    }

    /**
     * Check if the connection to the database has been established
     *
     * @return true if the connection has been established
     */
    public boolean isConnected() {
        return conn != null;
    }

  	public Show getShowData(String mTitle, String mDate) {
		Integer mFreeSeats = 0;
		String mVenue = null;

		/* --- TODO: add code for database query --- */
		try{

			ResultSet result = conn.createStatement().executeQuery("SELECT * FROM Showing WHERE date LIKE '" + mDate + "' AND movie LIKE '" + mTitle + "'");
			while(result.next()){
				mFreeSeats = result.getInt("availableSeats");
				mVenue = result.getString("theatreName");
			}
		} catch(SQLException e){
			System.out.println(e.getErrorCode());
		}

		return new Show(mTitle, mDate, mVenue, mFreeSeats);
	}

  	public ArrayList<String> getMovies(){
  		ArrayList<String> movies = new ArrayList<String>();
  		try{
  			 ResultSet result = conn.createStatement().executeQuery("SELECT DISTINCT movie FROM Showing");
	  		while(result.next()){
	  			movies.add((String)result.getString(1));
	  		}
  		} catch(SQLException e){
  			System.out.println(e.getErrorCode());
  		}
  		return movies;
  	}

  	public ArrayList<String> getDates(String mTitle){
  		ArrayList<String> dates = new ArrayList<String>();
  		try{
  			ResultSet result = conn.createStatement().executeQuery( "SELECT date FROM Showing WHERE movie LIKE '" + mTitle + "'");
  			while(result.next()){
  				Date temp = result.getDate(1);
  				String date = temp.toString();
  				dates.add(date);
  			}
  		} catch(SQLException e){
  			System.out.println(e.getErrorCode());
  		}
  		return dates;
  	}

  	public boolean checkLogin(String user){
  		try{
  			ResultSet result = conn.createStatement().executeQuery("SELECT username FROM Users WHERE username LIKE '" + user + "'");
  			if(result.next()) return true;
  		} catch(SQLException e){
  			System.out.println(e.getErrorCode());
  		}
  		return false;
  	}

  	public int makeReservation(String movie, String date){
  		String theatreName = null;
  		int seats = 0, bookingNbr = 0;
  		try{
  			ResultSet rs = conn.createStatement().executeQuery("SELECT theatreName FROM Showing WHERE movie LIKE '" + movie + "' AND date LIKE '" + date + "'");
  			if(rs.next()) theatreName = rs.getString(1);
  		} catch(SQLException e){
  			System.out.println(e.getErrorCode());
  		}
  		try{
  			String sqlCheck = "SELECT * FROM SHOWING WHERE movie = ? AND date = ?";
  			PreparedStatement ps = conn.prepareStatement(sqlCheck);
			ps.setString(1, movie);
			ps.setString(2, date);
			ResultSet result = ps.executeQuery();
			while(result.next()){
				seats = result.getInt(2);
				if(seats == 0) return -1;
			}
			ps.close();
  		} catch(SQLException e){
  			System.out.println(e.getErrorCode());
  		}

  		if(theatreName != null){
  			try{
  				String uname = CurrentUser.instance().getCurrentUserId();
  				conn.createStatement().executeUpdate("INSERT INTO Booking(username, movie, date) " + "VALUES ('" + uname + "', '" + movie + "', '" + date + "')");

  				ResultSet result = conn.createStatement().executeQuery("SELECT * FROM Booking");
  	  			while(result.next()){
  	  				bookingNbr = result.getInt("Nbr");
  	  			}
  				conn.createStatement().executeUpdate("UPDATE Showing SET availableSeats = availableSeats - 1 WHERE movie LIKE '" + movie + "' AND date LIKE '" + date + "'");
  			} catch(SQLException e){
  				System.out.println(e.getErrorCode());
  			}
  		}
  		return bookingNbr;
  	}
    /* --- TODO: insert more own code here --- */
}