import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.*;

public class Main {

    private static final String CONNECTION_STRING = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "minions_db";
    private static Connection connection;
    private static String query;
    private static PreparedStatement statement;
    private static BufferedReader reader;
    private static int released_minions;
    public static String minionName;
    public static int minionAge;
    public static String minionTown;
    public static String villainName;
    public static int townId;

    public static void main(String[] args) throws SQLException, IOException {
        reader = new BufferedReader(new InputStreamReader(System.in));

        Properties props = new Properties();

        props.setProperty("user", "root");
        props.setProperty("password", "12345");

        connection = DriverManager.getConnection(CONNECTION_STRING + DATABASE_NAME, props);

        //2. Get Villains Names
        //getVillainsNamesAndCountOfMinions();

        //3. Get Minion Names
        //getMinionNameEx();

        //4. Add Minion
        //addMinion();

        //5. Change Town Names Casing
        //changeTownNamesCasing("Bulgaria");

        //6. *Remove Villain
        //removeVillainEX();

        //7. Print All Minion Names
        //printAllMinionNames();

        //8. Increase Minions Age
        //increaseMinionsAge();

        // 9. Increase Age Stored Procedure
        //increaseAgeStoredProcedure();
    }

    private static void addMinion() throws IOException, SQLException {

            reader = new BufferedReader(
                    new InputStreamReader(
                            System.in
                    )
            );

            System.out.println("Enter minions data: ");
            //Enter data in format: Minion: Robert 14 Berlin
            String[] minionsInfo = reader.readLine().split("\\s+");

            System.out.println("Enter villains data: ");
            //Enter data in format: Villain: Gru
            String[] villainsName = reader.readLine().split("\\s+");

            minionName = minionsInfo[1];
            minionAge = Integer.parseInt(minionsInfo[2]);
            minionTown = minionsInfo[3];

            villainName = villainsName[1];

            checkIfTownExists(minionTown);

            checkIfVillainExists(villainName);

            insertAllDataInToDatabase();

            connection.close();
        }

        private static void checkIfVillainExists(String villainName) throws SQLException {
            query = "SELECT name FROM minions_db.villains";
            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            List<String> villains = new ArrayList<>();

            while (resultSet.next()) {
                villains.add(resultSet.getString("name"));
            }

            if (!villains.contains(villainName)) {
                insertVillainInToTable(villainName);
            }
        }

        private static void insertAllDataInToDatabase() throws SQLException {
            getTownId(minionTown);

            query = "INSERT INTO minions_db.minions(name, age, town_id) " +
                    "VALUES (?, ?, ?)";

            statement = connection.prepareStatement(query);
            statement.setString(1, minionName);
            statement.setInt(2, minionAge);
            statement.setInt(3, townId);

            statement.execute();

            System.out.println(String.format("Successfully added %s to be minion of %s.",
                    minionName, villainName));
        }

        private static void insertVillainInToTable(String villainName) throws SQLException {
            query = "INSERT INTO minions_db.villains(name, evilness_factor) " +
                    "VALUE (?, ?);";

            statement = connection.prepareStatement(query);
            statement.setString(1, villainName);
            statement.setString(2, "evil");

            statement.execute();

            System.out.println(String.format("Villain %s was added to the database.",
                    villainName));
        }

        private static void checkIfTownExists(String minionTown) throws SQLException {
            query = "SELECT name FROM minions_db.towns;";
            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            List<String> minionsNames = new ArrayList<>();

            while (resultSet.next()) {
                minionsNames.add(resultSet.getString("name"));
            }

            if(!minionsNames.contains(minionTown)) {
                insertTownInToTable(minionTown);
            }
        }

        private static void insertTownInToTable(String minionTown) throws SQLException {
            query = "INSERT INTO minions_db.towns(name) " +
                    "VALUE (?);";

            statement = connection.prepareStatement(query);
            statement.setString(1, minionTown);

            statement.execute();
            System.out.println(String.format("Town %s was added to the database.",
                    minionTown));
        }

        private static void getTownId(String minionTown) throws SQLException {
            query = "SELECT id FROM minions_db.towns WHERE name = ?";
            statement = connection.prepareStatement(query);
            statement.setString(1, minionTown);
            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                townId = resultSet.getInt("id");
            }
        }



    private static void increaseAgeStoredProcedure() throws SQLException, IOException {
            BufferedReader reader =
                    new BufferedReader(
                            new InputStreamReader(
                                    System.in
                            )
                    );

            System.out.println("Enter minion id to be updated it's age: ");

            int id = Integer.parseInt(reader.readLine());

            updateMinionAge(id);

            printNameAndAgeOfUpdatedMinion(id);

            connection.close();
        }

        private static void printNameAndAgeOfUpdatedMinion(int id) throws SQLException {
            query = "SELECT name, age " +
                    "FROM minions_db.minions " +
                    "WHERE id = ?";

            statement = connection.prepareStatement(query);
            statement.setInt(1, id);

            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                System.out.println(String.format(
                        "%s %d",
                        resultSet.getString("name"),
                        resultSet.getInt("age")
                ));
            }
        }

        private static void updateMinionAge(int id) throws SQLException {

            query = "CALL minions_db.usp_get_older(?)";

            statement = connection.prepareStatement(query);
            statement.setInt(1, id);

            statement.executeUpdate();
        }


    private static void increaseMinionsAge() throws SQLException, IOException {
        BufferedReader reader =
                new BufferedReader(
                        new InputStreamReader(
                                System.in
                        )
                );


        System.out.println("Enter minions id's, separated by single spaces: ");

        int[] minionId = Arrays
                .stream(reader.readLine().split("\\s+"))
                .mapToInt(Integer::parseInt)
                .toArray();

        modifyMinionNamesAndAges(minionId);

        connection.close();
    }

        private static void modifyMinionNamesAndAges(int[] minionId) throws SQLException {
            for (int i : minionId) {
                query = "UPDATE minions_db.minions m " +
                        "SET m.name = LOWER(m.name)," +
                        " m.age = m.age + 1 " +
                        "WHERE m.id = ?";

                statement = connection.prepareStatement(query);
                statement.setInt(1, i);
                statement.executeUpdate();
            }

            printMinionsAndTheirAges();
        }

        private static void printMinionsAndTheirAges() throws SQLException {
            query = "SELECT name, age " +
                    "FROM minions_db.minions";

            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                System.out.println(String.format(
                        "%s %d",
                        resultSet.getString("name"),
                        resultSet.getInt("age")
                ));
            }
        }

    private static void printAllMinionNames() throws SQLException {

            executeMinionNames();

            connection.close();
    }
    private static void executeMinionNames() throws SQLException {
        query = "SELECT name FROM minions_db.minions";
        statement = connection.prepareStatement(query);
        ResultSet resultSet = statement.executeQuery();

        Deque<String> minionNames = new ArrayDeque<>();

        while (resultSet.next()) {
            minionNames.add(resultSet.getString(1));
        }

        printMinions(minionNames);
    }

    private static void printMinions(Deque<String> minionNames) {
        int counter = 0;
        while (!minionNames.isEmpty()) {
            counter++;
            if (counter % 2 == 0) {
                String minion = minionNames.removeLast();
                System.out.println(minion);
            } else {
                String minion = minionNames.removeFirst();
                System.out.println(minion);
            }
        }
    }

    private static void removeVillainEX() throws SQLException, IOException {
        System.out.println("Enter villain Id: ");
        int villainId = Integer.parseInt(reader.readLine());

        findOutput(villainId);
        releaseAllMinionsByGivenVillainId(villainId);
        removeVillainFromDatabase(villainId);

        connection.close();

    }
        private static boolean checkIfVillainExists(int villainId) throws SQLException {
            query = "SELECT id " +
                    "FROM minions_db.villains " +
                    "WHERE id = ?";

            statement = connection.prepareStatement(query);
            statement.setInt(1, villainId);

            ResultSet resultSet = statement.executeQuery();

            return resultSet.next();
        }

        private static void findOutput(int villainId) throws SQLException {
            query = "SELECT COUNT(m.name) released_minions_count " +
                    "FROM minions_db.minions m " +
                    "JOIN minions_db.minions_villains mv ON m.id = mv.minion_id " +
                    "JOIN minions_db.villains v ON mv.villain_id = v.id " +
                    "WHERE v.id = ?";

            statement = connection.prepareStatement(query);
            statement.setInt(1, villainId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                 released_minions = resultSet.getInt(
                        "released_minions_count");
            }
        }

        private static void releaseAllMinionsByGivenVillainId(int villainId) throws SQLException {
            query = "DELETE minions_db.minions_villains " +
                    "FROM minions_db.minions_villains " +

                    "WHERE villain_id = ?;";

            statement = connection.prepareStatement(query);
            statement.setInt(1, villainId);

            statement.execute();
        }

        private static void removeVillainFromDatabase(int villainId) throws SQLException {

            if(!checkIfVillainExists(villainId)) {
                System.out.println("No such villain was found");
                return;
            } else {
                printVillainNameInOutput(villainId);
            }

            query = "DELETE FROM minions_db.villains " +
                    "WHERE id = ?";


            statement = connection.prepareStatement(query);
            statement.setInt(1, villainId);
            statement.execute();

        }

        private static void printVillainNameInOutput(int villainId) throws SQLException {
            query = "SELECT name " +
                    "FROM minions_db.villains " +
                    "WHERE id = ?";

            statement = connection.prepareStatement(query);
            statement.setInt(1, villainId);

            ResultSet resultSet = statement.executeQuery();

            if (resultSet.next()) {
                System.out.printf("%s was deleted\r\n", resultSet.getString("name"));
                System.out.println(String.format(
                        "%d minions released",
                        released_minions
                ));
            }
        }

    private static void changeTownNamesCasing(String country) throws SQLException {
        String query = "select name from towns where country = ?;";
        PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, country);
        ResultSet rs;
        rs = statement.executeQuery();
        List<String> changedWords = new ArrayList<>();

        while (rs.next()) {
            String town = rs.getString(1);

            for (int i = 0; i < town.length(); i++) {
                if (Character.isLowerCase(town.charAt(i))) {

                    town = town.toUpperCase();
                    changedWords.add(town);
                    String townQuery = "update towns " +
                            "set name = ?" +
                            "where name = ?";

                    PreparedStatement townUppercaseChange = connection.prepareStatement(townQuery);
                    townUppercaseChange.setString(1, town);
                    townUppercaseChange.setString(2, rs.getString(1));
                    townUppercaseChange.executeUpdate();
                    break;
                }
            }
        }

        String result = changedWords.isEmpty() ? "No town names were affected." :
                String.format("%d town names were affected.%n[%s]", changedWords.size(),
                        String.join(", ", changedWords)
                );
        System.out.println(
                result
        );
    }

    private static void insertEntityInTowns(String minionTown) throws SQLException {
        query = "INSERT INTO towns (name, country) value(?, NULL)";

        statement = connection.prepareStatement(query);
        statement.setString(1, minionTown);

        statement.execute();
    }

    private static boolean checkIfEntityExistsByName(String entityName, String tableName) throws SQLException {
        query = "SELECT * FROM " + tableName + " WHERE name = ?";

        statement = connection.prepareStatement(query);
        statement.setString(1, entityName);

        ResultSet resultSet = statement.executeQuery();

        return resultSet.next();
    }

    private static void getMinionNameEx() throws IOException, SQLException {
        System.out.println("Enter villain id: ");

        int villain_id = Integer.parseInt(reader.readLine());

        if (!checkIfEntityExists(villain_id, "villains")) {
            System.out.printf("No villain with ID %d exists in the database.", villain_id);
            return;
        }
        System.out.printf("Villain: %s%n", getEntityNameById(villain_id, "villains"));

        getAllMinionNamesAndAgeFroVillain(villain_id);
    }

    private static void getAllMinionNamesAndAgeFroVillain(int villain_id) throws SQLException {
        query = "SELECT m.name, m.age FROM Minions AS m\n" +
                "                              JOIN minions_villains mv\n" +
                "                                   ON m.id = mv.minion_id\n" +
                "WHERE mv.villain_id = ?";

        statement = connection.prepareStatement(query);
        statement.setInt(1, villain_id);

        ResultSet resultSet = statement.executeQuery();

        int counter = 0;

        while (resultSet.next()) {
            counter++;

            System.out.printf("%d. %s %d%n", counter, resultSet.getString(1), resultSet.getInt(2));
        }
    }

    private static String getEntityNameById(int villain_id, String tableName) throws SQLException {
        query = "SELECT name FROM " + tableName + " WHERE id = ?";

        statement = connection.prepareStatement(query);
        statement.setInt(1, villain_id);

        ResultSet resultSet = statement.executeQuery();

        return resultSet.next() ? resultSet.getString(1) : null;
    }

    private static boolean checkIfEntityExists(int villain_id, String villains) throws SQLException {
        query = "SELECT * FROM " + villains + " WHERE id = ?";

        statement = connection.prepareStatement(query);
        statement.setInt(1, villain_id);

        ResultSet resultSet = statement.executeQuery();

        return resultSet.next();
    }

    private static void getVillainsNamesAndCountOfMinions() throws SQLException {
        query = "SELECT v.name, COUNT(mv.minion_id) AS 'count'\n" +
                "FROM villains AS v\n" +
                "JOIN minions_villains mv on v.id = mv.villain_id\n" +
                "GROUP BY v.name\n" +
                "HAVING count > 15\n" +
                "ORDER BY count DESC";

        statement = connection.prepareStatement(query);

        ResultSet resultSet = statement.executeQuery();

        while (resultSet.next()) {
            System.out.printf("%s %d%n", resultSet.getString(1), resultSet.getInt(2));
        }
    }


}