package soft234.rental;

import java.sql.*;

public class DatabaseConnector {

    Statement selectStmt;
    Connection myConnection;

    public DatabaseConnector() {
    }

    public void connect() throws Exception {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        myConnection = DriverManager.getConnection("jdbc:odbc:Resources_Rental", "", "");
        selectStmt = myConnection.createStatement();
    }

    public void registerUser(String surname, String forename,
            String phone, String email, String password) throws SQLException {

        selectStmt.executeUpdate("INSERT INTO Member "
                + "(Surname, Forenames, Phone, Email, Password)\n"
                + "VALUES ('" + surname
                + "', '" + forename
                + "', '" + phone
                + "', '" + email
                + "', '" + password
                + "')");

    }

    public ResultSet getDvds() throws SQLException {
        return selectStmt.executeQuery("SELECT *\n"
                + "FROM DVD");
    }

    public ResultSet getEmailAddresses() throws SQLException {
        return selectStmt.executeQuery("SELECT Email\nFROM Member");
    }

    public ResultSet getUser(String usr, String pw) throws SQLException {
        return selectStmt.executeQuery("SELECT Email, Forenames\n"
                + "FROM Member\n"
                + "WHERE StrComp(Password,'" + pw + "',0) = 0\n"
                + "AND Email = '" + usr + "'");
    }

    public ResultSet getUserInfoByEmail(String email) throws SQLException {
        return selectStmt.executeQuery("SELECT *\n"
                + "FROM Member\n"
                + "WHERE Email = '" + email + "'");
    }

    public ResultSet getCurrentRentals(String email) throws SQLException {
        return selectStmt.executeQuery(
                "SELECT RentalID, DVD.Title, Date\n"
                + "FROM Rental, DVD, Member\n"
                + "WHERE Rental.DVDID = DVD.DVDID\n"
                + "AND Rental.MemberID = Member.MemberID\n"
                + "AND Member.Email = '" + email + "'\n"
                + "AND Current = 1");
    }

    public ResultSet getReturnedRentals(String email) throws SQLException {
        return selectStmt.executeQuery(
                "SELECT RentalID, DVD.Title, Date\n"
                + "FROM Rental, DVD, Member\n"
                + "WHERE Rental.DVDID = DVD.DVDID\n"
                + "AND Rental.MemberID = Member.MemberID\n"
                + "AND Member.Email = '" + email + "'\n"
                + "AND Current = 0\n"
                + "ORDER BY Date DESC");
    }

    public ResultSet getDvdById(int id) throws SQLException {
        return selectStmt.executeQuery("SELECT *\n"
                + "FROM DVD\n"
                + "WHERE DVDID = " + id);
    }

    public ResultSet getCheapDvds() throws SQLException {
        return selectStmt.executeQuery(
                "SELECT *\n"
                + "FROM DVD\n"
                + "WHERE Cost <= 2");
    }

    public ResultSet getSearchResults(String search) throws SQLException {
        return selectStmt.executeQuery(
                "SELECT *\n"
                + "FROM DVD\n"
                + "WHERE Title LIKE '%" + search + "%'");
    }

    public void closeConnection() throws SQLException {
        myConnection.close();
    }
}