package sailviewserver;

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

public class Regatta
{
	int id;
	String name;
	String location;
	String description;
	java.util.Date startDate;
	java.util.Date endDate;
	String yachtClub;
	String ownerUsername;
	java.util.Date updateDate;

	public Regatta()
	{
	}

	public Regatta(ResultSet result ) throws SQLException
	{
		id = result.getInt("ID");
		name = result.getString("NAME");
		location = result.getString("LOCATION");
		description = result.getString("DESCRIPTION");
		startDate = result.getDate("START_DATE");
		endDate = result.getDate("END_DATE");
		yachtClub = result.getString("YACHT_CLUB");
		ownerUsername = result.getString("OWNER");
		updateDate = result.getDate("UPDATE_DATE");

		System.err.println( SailviewServer.dateTimeFormat.format(updateDate));
	}


	public String getDescription()
	{
		return description;
	}

	public java.util.Date getEndDate()
	{
		return endDate;
	}

	public int getId()
	{
		return id;
	}

	public String getLocation()
	{
		return location;
	}

	public String getName()
	{
		return name;
	}

	public String getOwnerUsername()
	{
		return ownerUsername;
	}

	public java.util.Date getStartDate()
	{
		return startDate;
	}

	public java.util.Date getUpdateDate()
	{
		return updateDate;
	}

	public String getYachtClub()
	{
		return yachtClub;
	}

	public void setDescription(String description)
	{
		this.description = description;
	}

	public void setEndDate(java.util.Date endDate)
	{
		this.endDate = endDate;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	public void setLocation(String location)
	{
		this.location = location;
	}

	public void setName(String name)
	{
		this.name = name;
	}

	public void setOwnerUsername(String ownerUsername)
	{
		this.ownerUsername = ownerUsername;
	}

	public void setStartDate(java.util.Date startDate)
	{
		this.startDate = startDate;
	}

	public void setUpdateDate(java.util.Date updateDate)
	{
		this.updateDate = updateDate;
	}

	public void setYachtClub(String yachtClub)
	{
		this.yachtClub = yachtClub;
	}

	/*
	 * PROTECTED
	 */

	protected void delete(Connection connection)
		throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sql = "DELETE FROM REGATTA WHERE ID=" + id;
		stmt.executeUpdate(sql);
	}

	protected int insert(Connection connection)
		throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sql = "INSERT INTO REGATTA VALUES ("
		 	+ "REGATTA_ID.nextval"
		 	+ ",'" + name + "'"
		 	+ ",'" + location + "'"
		 	+ ",'" + description + "'"
		 	+ ",'" + SailviewServer.dateFormat.format(startDate) + "'"
		 	+ ",'" + SailviewServer.dateFormat.format(endDate) + "'"
		 	+ ",'" + yachtClub + "'"
		 	+ ",'" + ownerUsername + "'"
		 	+ ",'" + SailviewServer.dateTimeFormat.format(updateDate) +  "')";

		stmt.executeUpdate( sql );

		sql = "SELECT last_number FROM DOMAIN.SEQUENCES WHERE sequence_name='REGATTA_ID'";

		ResultSet result = stmt.executeQuery(sql);
		result.first();

		return result.getInt("last_number");
	}

	protected void update(Connection connection) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sql = "UPDATE REGATTA SET "
			+ "NAME='" + name + "', "
			+ "LOCATION='" + location + "', "
			+ "DESCRIPTION='" + description + "', "
			+ "START_DATE='" + SailviewServer.dateFormat.format(startDate) + "', "
			+ "END_DATE='" + SailviewServer.dateFormat.format(endDate)+ "', "
			+ "YACHT_CLUB='" + yachtClub+ "', "
			+ "UPDATE_DATE='" + SailviewServer.dateTimeFormat.format(updateDate) + "' "
			+ "WHERE ID=" + id;

		stmt.executeUpdate( sql );
	}

	/*
	 * PROTECTED AND STATIC
	 */

	protected static Regatta[] getRegattas(Connection connection )
		throws SQLException, RegattaNotFoundException
	{
		return getRegattas( connection, "SELECT * FROM REGATTA" );
	}

	protected static Regatta[] getRegattas( Connection connection, String sql )
		throws SQLException, RegattaNotFoundException
	{
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery(sql);

		if( !result.last() ) {
			throw new RegattaNotFoundException();
		}

		System.err.println( "NR OF ROWS:" + result.getRow() );

		Regatta[] regattas = new Regatta[result.getRow()];
		result.beforeFirst();

		int i = 0;
		while( result.next() ) {
			regattas[i] = new Regatta( result );
			i++;
		}

		return regattas;
	}

	protected static Regatta getRegattaById( Connection connection, int id )
		throws SQLException, RegattaNotFoundException
	{
		Statement stmt = connection.createStatement();
		String sql = "SELECT * FROM REGATTA WHERE ID=" + id;

		ResultSet result = stmt.executeQuery( sql );

		if( !result.first() ) {
			throw new RegattaNotFoundException();
		}

		return new Regatta( result );
	}
}