public class CoolingService implements ICoolingService {
	private static final Logger L = LoggerFactory.getLogger(CoolingService.class);
	private Connection connection;

	@Override
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	@SuppressWarnings("unused")
	private Connection useConnection() {
		if (connection == null) {
			throw new DataException("Connection not set");
		}
		return connection;
	}
	
	@Override
	public void transferSample(Integer sampleId, Integer diameterInCM) {
		L.info("transferSample: sampleId: " + sampleId + ", diameterInCM: " + diameterInCM);
  		
		/*
		Schritt 1: 	Überprüfung, ob es eine Probe mit entsprechender SampleID gibt. 
	    			Wenn ja, dann samplekindid und expirationDate der Probe in Variablen speichern 
  		*/
  		String sql_existSample = "SELECT sampleid, samplekindid, expirationdate FROM sample WHERE sampleid = ?";
  		int samplekindId;
  		Date sampleExpirationDate;
  		
  		try (PreparedStatement ps = useConnection().prepareStatement(sql_existSample)) {
	    	ps.setInt(1, sampleId);
	    	try (ResultSet rs = ps.executeQuery()) {
	    		if (rs.next()) {
	    			samplekindId = rs.getInt("samplekindid");
	    			sampleExpirationDate = rs.getDate("expirationdate");
	    		} else {
	    			throw new CoolingSystemException("Folgende SampleID existiert nicht: " + sampleId);
	    		}
	    	}
    	} catch (SQLException e) {
    		L.error("", e);
    		throw new DataException(e);
	    }
  		
  		/*
  		Schritt 2: 	Überprüfung, ob es ein Tablett gibt, welches Plätze mit angefragtem Probenumgang hat. 
  					Es soll dann gleich das Tablett ausgegeben werden, welches das kleinste Ablaufdatum hat, 
  					aber größer als das Ablaufdatum der Probe ist oder das Tablett, wo es noch kein 
  					Ablaufdatum gibt, da es unbelegt ist. Zudem muss es noch freie Plätze geben.
  		*/
  		String sql = String.join(" ",
  				"WITH",
  			    	"traycount(trayid, anzahl_belegteplaetze) AS (SELECT trayid, count(*) FROM place group by trayid)",
	  			"SELECT",
	  			    "tray.trayid,",
	  			    "tray.capacity,",
	  			    "traycount.anzahl_belegteplaetze,",
	  			    "COALESCE(tray.capacity - traycount.anzahl_belegteplaetze, tray.capacity) AS freie_kapazitaet,",
	  			    "tray.DiameterInCM,",
	  			    "tray.expirationdate",
	  			"FROM",
	  			    "tray",
	  			    "LEFT OUTER JOIN traycount ON traycount.trayid = tray.trayid",
	  			"WHERE",
	  			    "tray.DiameterInCM = ? AND",
	  			    "COALESCE(tray.capacity - traycount.anzahl_belegteplaetze, tray.capacity) > 0 AND",
	  			    "(expirationdate >= ? OR expirationdate IS NULL)",
	  			"ORDER BY",
	  			    "tray.expirationdate ASC",
	  			"FETCH FIRST 1 ROWS ONLY");
  		
  		int trayid;
  		int trayCapacity;
  		Date trayExpirationDate;
  		
  		try (PreparedStatement ps = useConnection().prepareStatement(sql)) {
	    	ps.setInt(1, diameterInCM);
	    	ps.setDate(2, sampleExpirationDate);
	    	try (ResultSet rs = ps.executeQuery()) {
	    		if (rs.next()) {
	    			trayid = rs.getInt("trayid");
	    			trayCapacity = rs.getInt("capacity");
	    			trayExpirationDate = rs.getDate("expirationdate");
	    		} else {
	    			throw new CoolingSystemException("Es ist kein Tablett mit folgendem Proben-Durchmesser verfügbar: " + diameterInCM);
	    		}
	    	}
    	} catch (SQLException e) {
    		L.error("", e);
    		throw new DataException(e);
	    }
  		
  		/*
  		Schritt 3: 	Sofern ein Platz vorhanden ist, wird ein neuer Datensatz in der Tabelle "Place" erzeugt.
  					Falls ein neues Tablett verwendet wird, so wird dessen ablaufdatum auf das Ablaufdatum
  					der Probe + 30 Tage gesetzt. Wird ein bereits verwendetes Tablett verwendet, so wird
  					sichergestellt, dass die niedrigste Platznummer verwendet wird
  		*/

		int smallestFreePlaceNo = 1;
  		
  		// Neues Tablett anfangen
  		if (trayExpirationDate == null) {

  			// Leeres Tablett erhält als Ablaufdatum das Ablaufdatum der Probe + 30 Tage
  			LocalDate trayExpDate = sampleExpirationDate.toLocalDate().plusDays(30);
  			
  			L.info("Start update tray");
  			String sql_trayUpdate = "UPDATE tray SET expirationdate = ? WHERE trayid = ?";
  			L.info(sql_trayUpdate);
  			
  			try (PreparedStatement ps = useConnection().prepareStatement(sql_trayUpdate)) {
	            ps.setDate(1, localDate2SqlDate(trayExpDate));
	            ps.setInt(2,  trayid);
	            try {
	                ps.executeUpdate();
	            } catch (SQLIntegrityConstraintViolationException e) {
	                throw new CoolingSystemException("Sample existiert bereits.");
	            }
	        } catch (SQLException e) {
	            L.error("", e);
	            throw new DataException(e);
	        }
  	     
  	     // Es wird ein bereits verwendetes Tablett mit freien Plätzen verwendet
	     } else {
	    	 
	    	 L.info("getSampleKinds: start");
	    	    
	   		// Liste, in der die Bezeichnungen der Probenarten gespeichert werden
	   		List<Integer> listPlaceNo = new LinkedList<Integer>();
	   		
	   		L.info("Start get placenumbers");
	   		String sql_getPlaceNo = "SELECT placeno FROM place WHERE trayid = ? ORDER BY placeno asc";
	   		L.info(sql_getPlaceNo);
	     
	   		try (PreparedStatement ps = useConnection().prepareStatement(sql_getPlaceNo)) {
  	            ps.setInt(1, trayid);
	   			try (ResultSet rs = ps.executeQuery()) {
	   				while (rs.next()) {
	   					listPlaceNo.add(rs.getInt("placeno"));
	   				}
	   			}
	   		} catch (SQLException e) {
	   			L.error("", e);
	   			throw new DataException(e);
	   		}
	   		
	   		// kleinste Platznummer ermitteln
	   		int lastIndex = listPlaceNo.size() - 1;
	   		
			for(int i = 1; i <= listPlaceNo.get(lastIndex)+1; i++) {
				if(listPlaceNo.contains(i)) {
					continue;
				} else {
					smallestFreePlaceNo = i;
					break;
				}
			}
			
	     }
  		
  		// Neuer Datensatz wird in Tabelle "place" eingetragen
		L.info("Start insert");
		String sql_placeInsert =  "insert into place(trayid, placeno, Sampleid)" + " values (?, ?, ?)";
		L.info(sql_placeInsert);
		
		try (PreparedStatement ps = useConnection().prepareStatement(sql_placeInsert)) {
		    ps.setInt(1, trayid);
		    ps.setInt(2, smallestFreePlaceNo);
		    ps.setInt(3, sampleId);
		    try {
		        ps.executeUpdate();
		    } catch (SQLIntegrityConstraintViolationException e) {
		        throw new CoolingSystemException("Place bereits besetzt.");
		    }
		} catch (SQLException e) {
		    L.error("", e);
		    throw new DataException(e);
		}
		
	}
	
	public static java.sql.Date localDate2SqlDate(LocalDate d) {
  	    if (d == null) {
  	      return null;
  	    }
  	    Calendar cal = Calendar.getInstance();
  	    // Month indexing start at 0
  	    cal.set(d.getYear(), d.getMonthValue() - 1, d.getDayOfMonth(), 0, 0, 0);
  	    return new java.sql.Date(cal.getTime().getTime());
  	}
}