package com.antonromanov.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Main {
    public static void main(String[] args) {
        Connection conn = PostgreSQLConnUtils.getMySQLConnection();
        long[] idArray = new long[3];
        idArray[0] =  insertUser(new User("Василий", "vasiliy@yandex.ru"), conn);
        idArray[1] =  insertUser(new User("Антон", "anton@yandex.ru"), conn);
        idArray[2] =  insertUser(new User("Геннадий", "gen@yandex.ru"), conn);

        select(conn);

        for (int i = 0; i < idArray.length; i++) {
            System.out.println("Удаляем елемент с Id = " + idArray[i]);
            delete(conn, idArray[i]);
        }
    }

    public static long insertUser(User user, Connection connection) {
        String SQL = "INSERT INTO test_jdbc.public.user (name, email) VALUES(?,?)";
        long id = 0;
        try {
            PreparedStatement pstmt = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        id = rs.getLong(1);
                    }
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    public static void select(Connection conn) {
        List<User> result = new ArrayList<>();
        String SQL_SELECT = "Select * from test_jdbc.public.user";

        try {
            PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                long id = resultSet.getLong("user_id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");

                User obj = new User();
                obj.setId(id);
                obj.setName(name);
                obj.setEmail(email);
                result.add(obj);
            }

            for (User user : result) {
                System.out.println("User {id: " + user.getId() + "," +
                        " name: " + user.getName() +
                        ", email: " + user.getEmail() + "}");
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void delete(Connection conn, long id) {

        String SQL_SELECT = "delete from test_jdbc.public.user where user_id = ?";

        try {
            PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT);
            preparedStatement.setLong(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}