package datamodel;

import java.sql.*;
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;

	/**
	 * 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 movie, String date) {
		Integer mFreeSeats = 0;
		String theater = "";
		try (Statement s = conn.createStatement()) {
			String sql = "SELECT date,tickets, theaterName FROM Shows";
			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				if (rs.getString("date").equals(date)) {
					mFreeSeats = rs.getInt("tickets");
				}
				if (rs.getString("date").equals(date)) {
					theater = rs.getString("theaterName");
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new Show(movie, date, theater, mFreeSeats);
	}

	public boolean checkIfUserExist(String username) {

		try (Statement s = conn.createStatement()) {
			String sql = "SELECT username FROM Users WHERE Users.username = '" + username + "'";
			ResultSet rs = s.executeQuery(sql);

			if (rs.next() == false) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	public List<String> addMovies() {
		List<String> movies = new ArrayList<String>();
		try (Statement s = conn.createStatement()) {
			String sql = "SELECT showName from Shows";
			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {
				if (!movies.contains(rs.getString("showName"))) {
					movies.add(rs.getString("showName"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return movies;

	}

	public List<String> addDates(String movie) {
		List<String> dates = new ArrayList<String>();
		try (Statement s = conn.createStatement()) {
			String sql = "SELECT showName, date FROM Shows";
			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {
				if (rs.getString("showName").equals(movie)) {
					dates.add(rs.getString("date"));
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dates;
	}

	public int bookTicket(String movie, String date, String userName) {

		int resNbr = 0;

		try (Statement s = conn.createStatement()) {
			String sql = "SELECT tickets from Shows where showName = '" + movie + "'";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			if (rs.getInt("tickets") == 0) {
				resNbr = -1;
			} else {

				sql = "insert into Tickets values(null, '" + userName + "', '" + movie + "', '" + date + "')";
				s.executeUpdate(sql);
				sql = "UPDATE Shows SET tickets = tickets -1 WHERE showName = '" + movie + "'";
				s.executeUpdate(sql);
				sql = "Select resNbr from Tickets where username = '" + userName + "' ORDER BY resNbr DESC";
				rs = s.executeQuery(sql);
				rs.next();
				resNbr = rs.getInt("resNbr");

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return resNbr;

	}

}













------------------------------------------







package gui;

import javafx.collections.FXCollections;
import javafx.fxml.FXML;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.text.Text;

import java.util.List;

import datamodel.CurrentUser;
import datamodel.Database;
import datamodel.Show;

import java.sql.SQLException;
import java.util.ArrayList;


public class BookingTab {
	// top context message
	@FXML private Text topContext;
	// bottom message
	@FXML private Text bookMsg;

	// table references
	@FXML private ListView<String> moviesList;
	@FXML private ListView<String> datesList;

	// show info references
	@FXML private Label showTitle;
	@FXML private Label showDate;
	@FXML private Label showVenue;
	@FXML private Label showFreeSeats;

	// booking button
	@FXML private Button bookTicket;

	private Database db;
	private Show crtShow = new Show();

	public void initialize() {
		System.out.println("Initializing BookingTab");


		// set up listeners for the movie list selection
		moviesList.getSelectionModel().selectedItemProperty().addListener(
				(obs, oldV, newV) -> {
					// need to update the date list according to the selected movie
					// update also the details on the right panel
					String movie = newV;
					fillDatesList(newV);
					fillShow(movie,null);
				});



		// set up listeners for the date list selection
		datesList.getSelectionModel().selectedItemProperty().addListener(
				(obs, oldV, newV) -> {
					// need to update the details according to the selected date
					String movie = moviesList.getSelectionModel().getSelectedItem();
					String date = newV;
				    fillShow(movie, date);
				});

		// set up booking button listener
		// one can either use this method (setup a handler in initialize)
		// or directly give a handler name in the fxml, as in the LoginTab class
		bookTicket.setOnAction(
				(event) -> {
					String movie = moviesList.getSelectionModel().getSelectedItem();
					String date = datesList.getSelectionModel().getSelectedItem();
					String userName = CurrentUser.instance().getCurrentUserId();

					int resNbr = db.bookTicket(movie,date,userName);
					if(resNbr == -1){
						Alert alert = new Alert(AlertType.ERROR);
				        alert.setTitle("Booking failure");
				        alert.setHeaderText(null);
				        alert.setContentText("Failed to complete booking, no available seats");
				        alert.showAndWait();

					}else{
						fillShow(movie,date);
						report("Success");
						Alert alert = new Alert(AlertType.INFORMATION);
				        alert.setTitle("Ticket booked");
				        alert.setHeaderText(null);
				        alert.setContentText("You have successfully booked a ticket with the reservation number " + resNbr);
				        alert.showAndWait();
					}



				});

		report("Ready.");
	}

	// helpers
	// updates user display
	private void fillStatus(String usr) {
		if(usr.isEmpty()) topContext.setText("You must log in as a known user!");
		else topContext.setText("Currently logged in as " + usr);
	}

	private void report(String msg) {
		bookMsg.setText(msg);
	}

	public void setDatabase(Database db) {
		this.db = db;
	}



	private void fillNamesList() {
		List<String> allmovies = new ArrayList<String>();
		allmovies = db.addMovies();
		moviesList.setItems(FXCollections.observableList(allmovies));
		// remove any selection
		moviesList.getSelectionModel().clearSelection();
	}



	private void fillDatesList(String m) {
		List<String> alldates = new ArrayList<String>();
		if(m!=null) {

			alldates = db.addDates(m);
		}
		datesList.setItems(FXCollections.observableList(alldates));
		// remove any selection
		datesList.getSelectionModel().clearSelection();
	}




	private void fillShow(String movie, String date) {
		if(movie==null) // no movie selected
			crtShow = new Show();
		else if(date==null) // no date selected yet
			crtShow = new Show(movie);
		else{

				crtShow = db.getShowData(movie, date);

			}

		showTitle.setText(crtShow.getTitle());
		showDate.setText(crtShow.getDate());
		showVenue.setText(crtShow.getVenue());
		if(crtShow.getSeats() >= 0) showFreeSeats.setText(crtShow.getSeats().toString());
		else showFreeSeats.setText("-");
	}




	// called in case the user logged in changed
	public void userChanged() {
		fillStatus(CurrentUser.instance().getCurrentUserId());
		fillNamesList();
		fillDatesList(null);
		fillShow(null,null);
	}

}