import java.sql.*;
import java.util.Locale;


public class MyDBConnection {
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String login = "system";
    String pass = "1";

    Connection connection;
    PreparedStatement preparedStatement;

    public MyDBConnection(){
        try {
            Locale.setDefault(Locale.ENGLISH);

            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "1");

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

    public User checkRegistration(String login, String pass) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "SELECT * " +
                "FROM tourists " +
                "WHERE login = ? AND pass = ?"
        );

        preparedStatement.setString(1, login);
        preparedStatement.setString(2, pass);

        ResultSet result = preparedStatement.executeQuery();

        if (result.next()) {
            User user = new User(result.getString("login"));
            return user;
        }

        return null;
    }

    public void registerUser(String login, String pass, String email) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "INSERT INTO tourists VALUES (?, ?, ?)"
        );

        preparedStatement.setString(1, login);
        preparedStatement.setString(2, pass);
        preparedStatement.setString(3, email);

        preparedStatement.executeUpdate();
    }

    public void getFriendListForUser(User user) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "SELECT friend " +
                "FROM friends " +
                "WHERE login = ?"
        );

        preparedStatement.setString(1, user.name);

        ResultSet result = preparedStatement.executeQuery();

        if (result.next()) {
            user.addFriend(new User(result.getString("friend")));
        }
    }

    public void getTripListForUser(User user) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "SELECT trips.trip_id, trips.trip_name " +
                "FROM trips JOIN tourist_link_trip ON trips.trip_id = tourist_link_trip.trip_id" +
                "WHERE tourist_link_trip = ?"
        );

        preparedStatement.setString(1, user.name);

        ResultSet result = preparedStatement.executeQuery();

        if (result.next()) {
            user.addTrip(new Trip(result.getInt("trip_id") ,result.getString("trip_name"), null, null));
        }
    }

    public void delTrip(Trip trip) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "DELETE FROM trips WHERE trips.trip_id = ?"
        );

        preparedStatement.setInt(1, trip.id);

        preparedStatement.executeUpdate();


        preparedStatement = connection.prepareStatement(
                "DELETE FROM tourist_link_trip WHERE tourist_link_trip.trip_id = ?"
        );

        preparedStatement.setInt(1, trip.id);

        preparedStatement.executeUpdate();
    }

    public void addNewTripByUser(User user, String trip_name, String description) throws SQLException {

        CallableStatement cst = connection.prepareCall(
                "begin " +
                "? := insert_trip_and_return_id(?, ?, ?); " +
                "end;"
        );

        cst.registerOutParameter(1, Types.INTEGER);
        cst.setString(2, null);
        cst.setString(3, trip_name);
        cst.setString(4, description);

        cst.executeUpdate();

        Integer id = cst.getInt(1);

        preparedStatement = connection.prepareStatement(
                "INSERT INTO tourist_link_trip VALUES " +
                        "(?, ?)"
        );

        preparedStatement.setString(1, user.name);
        preparedStatement.setInt(2, id);

        preparedStatement.executeUpdate();
    }

    public void addNewItemToTrip(Trip trip, TripElement item) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "INSERT INTO items VALUES " +
                        "(?, ?, ?, ?, ?, ?)"
        );

        preparedStatement.setString(1, null);
        preparedStatement.setInt(2, trip.id);
        preparedStatement.setString(3, item.name);
        preparedStatement.setInt(4, item.number);
        preparedStatement.setString(5, item.description);
        preparedStatement.setString(6, item.handler);

        preparedStatement.executeUpdate();
    }

    public void getItemListForTrip(Trip trip) throws SQLException {

        preparedStatement = connection.prepareStatement(
                "SELECT * " +
                "FROM items " +
                "WHERE trip_id = ?"
        );

        preparedStatement.setInt(1, trip.id);

        ResultSet result = preparedStatement.executeQuery();

        if (result.next()) {
            TripElement item = new TripElement(result.getString("item_name"));
            item.number = result.getInt("item_number");
            item.id = result.getInt("item_id");
            item.description = result.getString("desctription");
            item.handler = result.getString("handler_name");

            trip.addThing(item);
        }
    }
}