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 java.util.ListIterator;

import somiba.domain.Season;


public class MySqlSeasonDao extends AbstractDatabaseDao implements SeasonDao {

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

	}

	@Override
	public Season get(String name, String year, String team)
			throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				PreparedStatement stmt = connection
						.prepareStatement("SELECT * FROM season WHERE name = ? AND year = ? AND team = ?");
				try {
					stmt.setString(1, name);
					stmt.setString(2, year);
					stmt.setString(3, team);
					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 season", e);
		}
	}

	@Override
	public List<Season> getSameYear(String name, String year)
			throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				PreparedStatement stmt = connection
						.prepareStatement("SELECT * FROM season WHERE name = ? AND year = ? AND team != 'composite'");
				try {
					stmt.setString(1, name);
					stmt.setString(2, year);
					ResultSet rs = stmt.executeQuery();
					List<Season> seasonList = new ArrayList<Season>();
					while (rs.next())
						seasonList.add(loadFromRow(rs));
					return seasonList;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get season", e);
		}
	}

	@Override
	public List<Season> getAllByName(String name) throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				Statement stmt = connection.createStatement();
				try {
					ResultSet rs = stmt.executeQuery("SELECT * FROM season WHERE name = '" + name + "'");
					List<Season> seasonList = new ArrayList<Season>();
					List<Season> preY2KList = new ArrayList<Season>();
					while (rs.next()) {
						Season season = this.loadFromRow(rs);
						if (!season.getYear().equals("") && Integer.parseInt(season.getYear()) >= 96)
							preY2KList.add(season);
						else
							seasonList.add(season);
					}
					seasonList.addAll(0, preY2KList);
					return seasonList;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get all seasons", e);
		}
	}

	@Override
	public List<Season> getAll() throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				Statement stmt = connection.createStatement();
				try {
					ResultSet rs = stmt.executeQuery("SELECT * FROM season WHERE team != 'totals'");
					List<Season> seasonList = new ArrayList<Season>();
					while (rs.next()) {
						seasonList.add(this.loadFromRow(rs));
					}
					return seasonList;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get all seasons", e);
		}
	}

	@Override
	public List<Season> getAllTotals() throws DataAccessException {
		try {
			Connection connection = super.getConnection();
			try {
				Statement stmt = connection.createStatement();
				try {
					ResultSet rs = stmt.executeQuery("SELECT * FROM season WHERE team = 'totals'");
					List<Season> seasonList = new ArrayList<Season>();
					while (rs.next()) {
						seasonList.add(this.loadFromRow(rs));
					}
					return seasonList;
				} finally {
					stmt.close();
				}
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			throw new DataAccessException("Failed to get all seasons", e);
		}
	}

	void processForComposite(List<Season> list) {
		ListIterator<Season> itr = list.listIterator();
		while (itr.hasNext()) {
			Season season = itr.next();
			if (!season.getTeam().equals("composite")) {
    			List<Season> seasons = getSameYear(season.getName(), season.getYear());
    			if (seasons.size() > 1)
    				itr.remove();
			}
		}
	}

	@Override
	public List<Season> getAllByQuery(String whereClause, int maxResults) throws DataAccessException {
        try {
            Connection connection = super.getConnection();
            try {
                Statement stmt = connection.createStatement();
                try {
                	ResultSet rs = stmt.executeQuery("SELECT * FROM season " + whereClause + " LIMIT " + maxResults);
                    List<Season> seasonList = new ArrayList<Season>();
                    while (rs.next()) {
                        seasonList.add(this.loadFromRow(rs));
                    }
                    processForComposite(seasonList);
                    return seasonList;
                } finally {
                    stmt.close();
                }
            } finally {
                connection.close();
            }
        } catch (SQLException e) {
            throw new DataAccessException("Failed to get all seasons", e);
        }
	}

	@Override
	public void save(Season season) throws DataAccessException {
		synchronized (season) {
			try {
				Connection connection = super.getConnection();
				try {
					PreparedStatement stmt = connection
						.prepareStatement("INSERT INTO season (name, year, team, gp, gs, mp, fgm, fga, tpm, tpa, ftm, fta, pts, off, def, ast, pf, dq, stl, trn, blk) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
					try {
						stmt.setString(1, season.getName());
						stmt.setString(2, season.getYear());
						stmt.setString(3, season.getTeam());
						stmt.setInt(4, season.getGp());
						stmt.setInt(5, season.getGs());
						stmt.setInt(6, season.getMin());
						stmt.setInt(7, season.getFgm());
						stmt.setInt(8, season.getFga());
						stmt.setInt(9, season.getTpm());
						stmt.setInt(10, season.getTpa());
						stmt.setInt(11, season.getFtm());
						stmt.setInt(12, season.getFta());
						stmt.setInt(13, season.getPts());
						stmt.setInt(14, season.getOff());
						stmt.setInt(15, season.getDef());
						stmt.setInt(16, season.getAst());
						stmt.setInt(17, season.getPf());
						stmt.setInt(18, season.getDq());
						stmt.setInt(19, season.getStl());
						stmt.setInt(20, season.getTo());
						stmt.setInt(21, season.getBlk());
						stmt.executeUpdate();
					} finally {
						stmt.close();
					}
				} finally {
					connection.close();
				}
			} catch (SQLException e) {
				String msg = e.getMessage();
				if (msg != null && msg.contains("Duplicate")) {
					throw new DuplicateIdException(season.getName(), e);
				}
				throw new DataAccessException("Failed to create: " + season,
						e);
			}
		}

	}

	@Override
	public void update(Season season) throws DataAccessException {
		synchronized (season) {
			try {
				Connection connection = super.getConnection();
				try {
					PreparedStatement stmt = connection
						.prepareStatement("UPDATE season SET gp=?, gs=?, mp=?, fgm=?, fga=?, tpm=?, tpa=?, ftm=?, fta=?, pts=?, off=?, def=?, ast=?, pf=?, dq=?, stl=?, trn=?, blk=? WHERE name = ? AND year = ? AND team = ?");
					try {
						stmt.setInt(1, season.getGp());
						stmt.setInt(2, season.getGs());
						stmt.setInt(3, season.getMin());
						stmt.setInt(4, season.getFgm());
						stmt.setInt(5, season.getFga());
						stmt.setInt(6, season.getTpm());
						stmt.setInt(7, season.getTpa());
						stmt.setInt(8, season.getFtm());
						stmt.setInt(9, season.getFta());
						stmt.setInt(10, season.getPts());
						stmt.setInt(11, season.getOff());
						stmt.setInt(12, season.getDef());
						stmt.setInt(13, season.getAst());
						stmt.setInt(14, season.getPf());
						stmt.setInt(15, season.getDq());
						stmt.setInt(16, season.getStl());
						stmt.setInt(17, season.getTo());
						stmt.setInt(18, season.getBlk());
						stmt.setString(19, season.getName());
						stmt.setString(20, season.getYear());
						stmt.setString(21, season.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(season.getName(), e);
				}
				throw new DataAccessException("Failed to create: " + season,
						e);
			}
		}

	}

	private Season loadFromRow(ResultSet row) throws SQLException {
		Season season = new Season(row.getString(1), row.getString(2), row.getString(3));
		season.setGp(row.getInt(4));
		season.setGs(row.getInt(5));
		season.setMin(row.getInt(6));
		season.setFgm(row.getInt(7));
		season.setFga(row.getInt(8));
		season.setTpm(row.getInt(9));
		season.setTpa(row.getInt(10));
		season.setFtm(row.getInt(11));
		season.setFta(row.getInt(12));
		season.setPts(row.getInt(13));
		season.setOff(row.getInt(14));
		season.setDef(row.getInt(15));
		season.setAst(row.getInt(16));
		season.setPf(row.getInt(17));
		season.setDq(row.getInt(18));
		season.setStl(row.getInt(19));
		season.setTo(row.getInt(20));
		season.setBlk(row.getInt(21));
		return season;
	}

}