package somiba.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import somiba.domain.YearTeam;


public class MySqlYearTeamDao extends AbstractDatabaseDao implements
		YearTeamDao {

	@Override
	public void delete(YearTeam yearTeam) throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				PreparedStatement stmt = connection
						.prepareStatement("DELETE FROM yearTeam WHERE original=? AND custom=? AND year=? AND team=?");
				try {
					stmt.setString(1, yearTeam.getOriginal());
					stmt.setString(2, yearTeam.getCustom());
					stmt.setString(3, yearTeam.getYear());
					stmt.setString(4, yearTeam.getTeam());
					stmt.executeUpdate();
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to delete yearTeam", e);
		}
	}

	@Override
	public List<YearTeam> getAll() throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				Statement stmt = connection.createStatement();
				try {
					ResultSet rs = stmt.executeQuery("SELECT * FROM yearTeam");
					List<YearTeam> yearTeamList = new ArrayList<YearTeam>();
					while (rs.next()) {
						yearTeamList.add(this.loadFromRow(rs));
					}
					return yearTeamList;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get all yearTeams", e);
		}
	}

	@Override
	public YearTeam get(String original, String year, String team) throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				PreparedStatement stmt = connection
						.prepareStatement("SELECT * FROM yearTeam WHERE original = ? AND year = ? AND team = ?");
				try {
					stmt.setString(1, original);
					stmt.setString(2, year);
					stmt.setString(3, team.substring(0,3));
					ResultSet rs = stmt.executeQuery();
					return rs.next() ? this.loadFromRow(rs) : null;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get yearTeam", e);
		}
	}

	@Override
	public void save(YearTeam yearTeam) throws DataAccessException {
		synchronized (yearTeam) {
			try {
				Connection connection = super.getConnection();
				try {
					PreparedStatement stmt = connection
						.prepareStatement("INSERT INTO yearTeam (original, custom, year, team) VALUES (?, ?, ?, ?)");
					try {
						stmt.setString(1, yearTeam.getOriginal());
						stmt.setString(2, yearTeam.getCustom());
						stmt.setString(3, yearTeam.getYear());
						stmt.setString(4, yearTeam.getTeam());
						stmt.executeUpdate();
					} finally {
						stmt.close();
					}
				} finally {
					connection.close();
				}
			} catch (SQLException e) {
				String msg = e.getMessage();
				if (msg != null && msg.contains("Duplicate")) {
					throw new DuplicateIdException(yearTeam.getCustom(), e);
				}
				throw new DataAccessException("Failed to create: " + yearTeam,
						e);
			}
		}
	}

	private YearTeam loadFromRow(ResultSet row) throws SQLException {
		YearTeam yearTeam = new YearTeam(row.getString(1), row.getString(2), row.getString(3), row.getString(4));
		return yearTeam;
	}
}