package datamodel;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * 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;
    private PreparedStatement ps;
    String user;

    /**
     * Create the database interface object. Connection to the database
     * is performed later.
     */
    public Database() {
        conn = null;
        ps = null;
        user = 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 boolean userLogin(String username){
		try{
		String sql = "SELECT username FROM Users WHERE username = ?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, username);
		ResultSet result = ps.executeQuery();
		if(result.next()){
			user = result.getString("username");
		}
		ps.close();
		return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} return false;
	}

	public List<String> getMovies(){
		List<String> movies = new ArrayList<String>();
		try{
		String sql = "SELECT DISTINCT movie FROM SHOWING";
		ps = conn.prepareStatement(sql);
		ResultSet result = ps.executeQuery();
		while(result.next()){
			movies.add(result.getString("movie"));
		}
		return movies;
		} catch (SQLException e){
			e.printStackTrace();
		} return null;
	}

	public List<String> getDatesforMovie(String movie){
		List<String> dates = new ArrayList<String>();
		try{
			String sql = "SELECT date FROM SHOWING WHERE movie = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, movie);
			ResultSet result = ps.executeQuery();
			while(result.next()){
				dates.add(result.getString("movie"));
			}
			ps.close();
			return dates;
		} catch (SQLException e){
			e.printStackTrace();
		} return null;
	}

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

		/* --- TODO: add code for database query --- */
		try{
			String sql = "SELECT * FROM Showing WHERE title = ? AND date = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, mTitle);
			ps.setString(2, mDate);
			ResultSet result = ps.executeQuery();
			while(result.next()){
				mFreeSeats = result.getInt("availableSeats");
				mVenue = result.getString("theater");
			}
			ps.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
		return new Show(mTitle, mDate, mVenue, mFreeSeats);
	}

    /* --- TODO: insert more own code here --- */
  	public Integer bookTicket(String movieName, String date){
  		try{
  			String sqlCheck = "SELECT * FROM SHOWING WHERE movie = ? AND date = ?";
  			ps = conn.prepareStatement(sqlCheck);
  			ps.setString(1, movieName);
  			ps.setString(2, date);
  			ResultSet result1 = ps.executeQuery();
  			result1.next();
  			if(result1.getInt("availableSeats") <= 0){
  				return null;
  			}
  			ps.close();

  			Integer reservation = null;

  			String sqlReserv = "INSERT INTO RESERVATION (date,username,movie) "
  					+ "VALUES (?,?,?)";
  			ps = conn.prepareStatement(sqlReserv);
  			ps.setString(1, date);
  			ps.setString(2, user);
  			ps.setString(3, movieName);
  			ps.executeQuery();
  			ps.close();

  			String sqlReservNbr = "SELECT * FROM RESERVATION";
  			ResultSet result2 = conn.createStatement().executeQuery(sqlReservNbr);
  			while(result2.next()){
  				reservation = result2.getInt("Nbr");
  			}

  			String sqlDecimate = "UPDATE SHOWING SET availableSeats = availableSeats - 1";
  			ps = conn.prepareStatement(sqlDecimate);
  			ps.executeQuery();
  			ps.close();

  			return reservation;
  		} catch (SQLException e){
			e.printStackTrace();
  		} return null;
  	}
}