package Exercises;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class AddMinion {
    private static Connection connection;
    public static void main(String[] args) throws SQLException {
         connection = Utils.getConnection();

        Scanner sc = new Scanner(System.in);

        String[] minionInfo = sc.nextLine().split("\\s+");
        String minionName = minionInfo[1];
        int minionAge = Integer.parseInt(minionInfo[2]);
        String minionTown = minionInfo[3];
        String villainName = sc.nextLine().split("\\s+")[1];


        addTownIfItDoesntExist(minionTown);
        addVillainIfItDoesntExist(villainName);
        addMinion(minionName, minionAge, minionTown);
        setVillainToMinion(minionName, villainName);

    }

    private static void setVillainToMinion(String minionName, String villainName) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("INSERT INTO minions_villains VALUES (?, ?);");
        int minionId = getMinionId(minionName);
        int villainId = getVillainId(villainName);
        preparedStatement.setInt(1, minionId);
        preparedStatement.setInt(2, villainId);
        preparedStatement.executeUpdate();

        System.out.printf("Successfully added %s to be minion of %s.%n", minionName, villainName);
    }

    private static int getVillainId(String villainName) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("SELECT DISTINCT id FROM villains WHERE name = ?");
        preparedStatement.setString(1, villainName);
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();

        return resultSet.getInt(1);
    }

    private static int getMinionId(String minionName) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT DISTINCT id FROM minions WHERE name = ?");
        preparedStatement.setString(1, minionName);
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();
        return resultSet.getInt(1);
    }

    private static void addMinion(String minionName, int minionAge, String minionTown) throws SQLException {
        PreparedStatement addMinion = connection
                .prepareStatement("INSERT INTO minions(name, age, town_id) VALUES(?, ?, ?)");
        addMinion.setString(1, minionName);
        addMinion.setInt(2, minionAge);
        addMinion.setInt(3, getTownIdByName(minionTown));

        addMinion.executeUpdate();
    }

    private static int getTownIdByName(String minionTown) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("SELECT DISTINCT id FROM towns WHERE name = ?;");

        preparedStatement.setString(1, minionTown);

        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();

        return resultSet.getInt(1);
    }

    private static void addVillainIfItDoesntExist(String villainName) throws SQLException {
        if (checkIfVillainExists(villainName)) {
            PreparedStatement addVillain = connection
                    .prepareStatement("INSERT INTO villains(name, evilness_factor) VALUES(?, ?)");
            addVillain.setString(1, villainName);
            addVillain.setString(2, "evil");

            addVillain.executeUpdate();

            System.out.printf("Villain %s was added to the database.%n", villainName);
        }
    }

    private static boolean checkIfVillainExists(String villainName) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("SELECT DISTINCT COUNT(*) FROM villains WHERE name = ?");

        preparedStatement.setString(1, villainName);
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();

        return resultSet.getInt(1) == 0;
    }

    private static void addTownIfItDoesntExist(String minionTown) throws SQLException {
        if (checkIfTownExists(minionTown)) {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("INSERT INTO towns(name) VALUES(?);");
            preparedStatement.setString(1, minionTown);

            preparedStatement.executeUpdate();

            System.out.printf("Town %s was added to the database.%n", minionTown);
        }
    }

    private static boolean checkIfTownExists(String minionTown) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("SELECT DISTINCT COUNT(*) FROM towns WHERE name = ?");

        preparedStatement.setString(1, minionTown);
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();

        return resultSet.getInt(1) == 0;
    }
}