import java.io.*;
import java.util.*;
import java.sql.*;

public class Main {
	public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {
		BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
		PlayerBO playerBO = new PlayerBO();
		PlayerDAO playerDAO = new PlayerDAO();
		TeamDAO teamDAO = new TeamDAO();
		SkillDAO skillDAO = new SkillDAO();
		List<Player> playerList = null;

		String menu = "Menu\n1.List all Players\n2.Add new player\n3.Search Player";
		System.out.println(menu);
		int choice = Integer.parseInt(br.readLine());

		switch(choice) {
		case 1:
			playerList = playerDAO.getAllPlayerDetails();
			playerBO.displayPlayerDetails(playerList);
			break;
		case 2:
			System.out.println("Enter the player name");
			String playerName = br.readLine();
			Long playerId = playerDAO.findByPlayerName(playerName).getPlayerId();
			System.out.println("Enter the country");
			String country = br.readLine();
			System.out.println("Enter the skill");
			String skillName = br.readLine();
			Long skillId = skillDAO.findBySkillName(skillName).getSkillId();
			Skill skillIns = new Skill(skillId, skillName);
			System.out.println("Enter the team name");
			String teamName = br.readLine();
			Long teamId = teamDAO.findByTeamName(teamName).getTeamId();
			Team teamIns = new Team(teamId, teamName);
			Player player = new Player(playerId, playerName, country, skillIns, teamIns);
			playerDAO.createPlayer(player);
			System.out.println("Player has been inserted.");
			playerList = playerDAO.getAllPlayerDetails();
			playerBO.displayPlayerDetails(playerList);
			break;
		case 3:
			System.out.println("Enter the Skill name");
			String sName = br.readLine();
			playerList = playerDAO.getPlayerDetails(sName);
			playerBO.displayPlayerDetails(playerList);
			break;
		}
	}
}



public class Player {
	Long playerId;
	String playerName;
	String country;
	Skill skillIns;
	Team teamIns;
	public Player() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Player(Long playerId, String playerName, String country, Skill skillIns, Team teamIns) {
		super();
		this.playerId = playerId;
		this.playerName = playerName;
		this.country = country;
		this.skillIns = skillIns;
		this.teamIns = teamIns;
	}
	public Long getPlayerId() {
		return playerId;
	}
	public void setPlayerId(Long playerId) {
		this.playerId = playerId;
	}
	public String getPlayerName() {
		return playerName;
	}
	public void setPlayerName(String playerName) {
		this.playerName = playerName;
	}
	public String getCountry() {
		return country;
	}
	public void setCountry(String country) {
		this.country = country;
	}
	public Skill getSkillIns() {
		return skillIns;
	}
	public void setSkillIns(Skill skillIns) {
		this.skillIns = skillIns;
	}
	public Team getTeamIns() {
		return teamIns;
	}
	public void setTeamIns(Team teamIns) {
		this.teamIns = teamIns;
	}
}


import java.util.*;

public class PlayerBO {
	public void displayPlayerDetails(List<Player> playerList) {
		System.out.println("Player details");
		System.out.println(String.format("%-15s %-15s %-15s %-15s", "Player Name", "Country", "Skill", "Team Name"));

		for (Player player : playerList) {
			System.out.println(String.format("%-15s %-15s %-15s %-15s", player.getPlayerName(), player.getCountry(),
					player.getSkillIns().getSkillName(), player.getTeamIns().getTeamName()));

		}
	}
}


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

public class PlayerDAO {
	public void createPlayer(Player player) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("INSERT INTO player VALUES(?, ?, ?, ?, ?)");
		Long pId = player.getPlayerId();
		String pName = player.getPlayerName();
		String pCountry = player.getCountry();
		Long pSkillId = player.getSkillIns().getSkillId();
		Long pTeamId = player.getTeamIns().getTeamId();
		pstmt.setLong(1, pId);
		pstmt.setString(2, pName);
		pstmt.setString(3, pCountry);
		pstmt.setLong(4, pSkillId);
		pstmt.setLong(5, pTeamId);
		pstmt.execute();

	}

	public Player findByPlayerName(String playerName) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT p.id, p.name, p.country, s.id, s.name, t.id, t.name "
				+ "FROM player p, skill s, team t WHERE p.skill_id = s.id and p.team_id = t.id and p.name = ?");
		pstmt.setString(1, playerName);
		ResultSet rs = pstmt.executeQuery();
		Player player = null;

		while (rs.next()) {
			Long playerId = rs.getLong(1);
			String playerName1 = rs.getString(2);
			String country = rs.getString(3);
			Long skillId = rs.getLong(4);
			String skillName = rs.getString(5);
			Skill skillIns = new Skill(skillId, skillName);
			Long teamId = rs.getLong(6);
			String teamName = rs.getString(7);
			Team teamIns = new Team(teamId, teamName);
			player = new Player(playerId, playerName1, country, skillIns, teamIns);
		}
		return player;
	}

	public Player findByPlayerId(Long playerId) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT p.id, p.name, p.country, s.id, s.name, t.id, t.name "
				+ "FROM player p, skill s, team t WHERE p.skill_id = s.id and p.team_id = t.id and p.id = ?");
		pstmt.setLong(1, playerId);
		ResultSet rs = pstmt.executeQuery();
		Player player = null;

		while (rs.next()) {
			Long playerId1 = rs.getLong(1);
			String playerName1 = rs.getString(2);
			String country = rs.getString(3);
			Long skillId = rs.getLong(4);
			String skillName = rs.getString(5);
			Skill skillIns = new Skill(skillId, skillName);
			Long teamId = rs.getLong(6);
			String teamName = rs.getString(7);
			Team teamIns = new Team(teamId, teamName);
			player = new Player(playerId1, playerName1, country, skillIns, teamIns);
		}
		return player;
	}

	public List<Player> getPlayerDetails(String skillName) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		List<Player> playerList = new ArrayList<Player>();
		PreparedStatement pstmt = con.prepareStatement("SELECT p.id, p.name, p.country, s.id, s.name, t.id, t.name"
				+ " FROM player p, skill s, team t WHERE p.skill_id = s.id and p.team_id = t.id and s.name = ?");
		pstmt.setString(1, skillName);

		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			Long playerId = rs.getLong(1);
			String playerName = rs.getString(2);
			String country = rs.getString(3);
			Long skillId = rs.getLong(4);
			String skillName1 = rs.getString(5);
			Skill skillIns = new Skill(skillId, skillName1);
			Long teamId = rs.getLong(6);
			String teamName = rs.getString(7);
			Team teamIns = new Team(teamId, teamName);
			Player player = new Player(playerId, playerName, country, skillIns, teamIns);
			playerList.add(player);
		}
		return playerList;
	}

	public List<Player> getAllPlayerDetails() throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("rb.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		List<Player> playerList = new ArrayList<Player>();

		PreparedStatement pstmt = con.prepareStatement("SELECT p.id, p.name, p.country, s.id, s.name, t.id, t.name "
				+ "FROM player p, skill s, team t WHERE p.skill_id = s.id and p.team_id = t.id");

		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			Long playerId = rs.getLong(1);
			String playerName = rs.getString(2);
			String country = rs.getString(3);
			Long skillId = rs.getLong(4);
			String skillName1 = rs.getString(5);
			Skill skillIns = new Skill(skillId, skillName1);
			Long teamId = rs.getLong(6);
			String teamName = rs.getString(7);
			Team teamIns = new Team(teamId, teamName);
			Player player = new Player(playerId, playerName, country, skillIns, teamIns);
			playerList.add(player);
		}
		return playerList;
	}
}



public class Skill {
	Long skillId;
	String skillName;
	public Skill() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Skill(Long skillId, String skillName) {
		super();
		this.skillId = skillId;
		this.skillName = skillName;
	}
	public Long getSkillId() {
		return skillId;
	}
	public void setSkillId(Long skillId) {
		this.skillId = skillId;
	}
	public String getSkillName() {
		return skillName;
	}
	public void setSkillName(String skillName) {
		this.skillName = skillName;
	}
}



public class SkillBO {

}


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class SkillDAO {
	public Skill findBySkillName(String skillName) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT * FROM skill WHERE name = ?");
		pstmt.setString(1, skillName);
		ResultSet rs = pstmt.executeQuery();

		Skill skill = null;

		while (rs.next()) {
			Long id = rs.getLong(1);
			String name = rs.getString(2);
			skill = new Skill(id, name);
		}

		return skill;
	}

	public Skill findBySkillId(long skillId) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT * FROM skill WHERE id = ?");
		pstmt.setLong(1, skillId);
		ResultSet rs = pstmt.executeQuery();

		Skill skill = null;
		while (rs.next()) {
			Long id = rs.getLong(1);
			String teamName = rs.getString(2);
			skill = new Skill(id, teamName);
		}
		return skill;
	}
}



public class Team {
	Long teamId;
	String teamName;
	public Team() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Team(Long teamId, String teamName) {
		super();
		this.teamId = teamId;
		this.teamName = teamName;
	}
	public Long getTeamId() {
		return teamId;
	}
	public void setTeamId(Long teamId) {
		this.teamId = teamId;
	}
	public String getTeamName() {
		return teamName;
	}
	public void setTeamName(String teamName) {
		this.teamName = teamName;
	}
}



public class TeamBO {

}


import java.util.ResourceBundle;
import java.sql.*;

public class TeamDAO {
	public Team findByTeamName(String teamName) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT * FROM team WHERE name = ?");
		pstmt.setString(1, teamName);
		ResultSet rs = pstmt.executeQuery();

		Team team = null;

		while (rs.next()) {
			Long id = rs.getLong(1);
			String name = rs.getString(2);
			team = new Team(id, name);
		}

		return team;
	}

	public Team findByTeamId(long teamId) throws ClassNotFoundException, SQLException {
		ResourceBundle rb = ResourceBundle.getBundle("mysql");
		String url = rb.getString("db.url");
		String user = rb.getString("db.username");
		String pass = rb.getString("db.password");
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, user, pass);

		PreparedStatement pstmt = con.prepareStatement("SELECT * FROM team WHERE id = ?");
		pstmt.setLong(1, teamId);
		ResultSet rs = pstmt.executeQuery();

		Team team = null;
		while (rs.next()) {
			Long id = rs.getLong(1);
			String teamName = rs.getString(2);
			team = new Team(id, teamName);
		}
		return team;
	}
}