public enum BookingStatus {
	CREATED(1), VALIDATED(2), REFUSED(3), SUSPENDED(4);

	private final int status;

	BookingStatus(int pStatus) {
		this.status = pStatus;
	}
	public int status(){
		return status;
	}
} public final static String RBS_NAME = "RBS"; public class BookingUtils {
	private final static String DATE_FORMAT = BookingServiceSqlImpl.DATE_FORMAT;
	public static final String TIMESTAMP_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSS";


	/**
	 * @param lastSelectedDay : index of last selected day
	 * @param selectedDays    : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @param periodicity     : slots are repeated every "periodicity" week
	 * @return Number of days until next slot
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public static int getNextInterval(final int lastSelectedDay, final String selectedDays, final int periodicity) {
		if (!selectedDays.contains("1")) {
			throw new IllegalArgumentException("Argument selectedDays must contain char '1'");
		}

		int count = 0;
		int k = lastSelectedDay;

		do {
			k++;
			if (k == 1) {
				// On monday, go to next week
				count += (periodicity - 1) * 7;
			} else if (k >= 7) {
				k = k % 7;
			}
			count++;
		} while (selectedDays.charAt(k) != '1');

		return count;
	}

	/**
	 * @param firstSelectedDay : index of the first slot's day
	 * @param selectedDays     : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @param durationInDays   : difference in days between the first slot's end date and the end date of the periodic booking
	 * @param periodicity      : slots are repeated every "periodicity" week
	 * @return Number of occurrences
	 * @throws IndexOutOfBoundsException
	 */
	public static int getOccurrences(final int firstSelectedDay, final String selectedDays,
	                                 final long durationInDays, final int periodicity) {
		int count = 0;
		int k = firstSelectedDay;
		int i = 0;

		while (i <= durationInDays) {
			if (k >= 7) {
				k = k % 7;
			}
			if (selectedDays.charAt(k) == '1') {
				count++;
			}
			k++;
			if (k == 1) {
				// On monday, go to next week
				i += (periodicity - 1) * 7;
			}
			i++;
		}

		return count;
	}

	/**
	 * @param occurrences   : number of occurrences
	 * @param periodicity   : slots are repeated every "periodicity" week
	 * @param firstSlotDate : Unix timestamp of the first slot's date (start or end date)
	 * @param firstSlotDay  : index of the first slot's day
	 * @param selectedDays  : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @return Unix timestamp of the last slot's date (start or end date)
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public static long getLastSlotDate(final int occurrences, final int periodicity,
	                                   final long firstSlotDate, final int firstSlotDay, final String selectedDays) {

		long lastSlotDate = firstSlotDate;
		if (occurrences > 1) {
			int selectedDay = firstSlotDay;
			int intervalFromFirstDay = 0;

			for (int i = 1; i <= (occurrences - 1); i++) {
				int interval = getNextInterval(selectedDay, selectedDays, periodicity);
				intervalFromFirstDay += interval;
				selectedDay = (selectedDay + interval) % 7;
			}
			lastSlotDate += TimeUnit.SECONDS.convert(intervalFromFirstDay, TimeUnit.DAYS);
		}

		return lastSlotDate;
	}

	/**
	 * @return Current unix timestamp in seconds
	 */
	public static long getCurrentTimestamp() {
		return TimeUnit.SECONDS.convert(Calendar.getInstance().getTimeInMillis(), TimeUnit.MILLISECONDS);
	}

	/**
	 * @return Unix timestamp of tomorrow midnight in seconds
	 */
	public static long getTomorrowTimestamp() {
		// Get tomorrow by adding one day to current time
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DAY_OF_MONTH, 1);

		// Set time to midnight
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);

		return TimeUnit.SECONDS.convert(cal.getTimeInMillis(), TimeUnit.MILLISECONDS);
	}

	/**
	 * @param unixTimestamp in seconds
	 * @return 0 for sunday, 1 for monday, etc
	 */
	public static int getDayFromTimestamp(final long unixTimestamp) {
		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(TimeUnit.MILLISECONDS.convert(unixTimestamp, TimeUnit.SECONDS));
		// "- 1", so that sunday is 0, monday is 1, etc
		int day = cal.get(Calendar.DAY_OF_WEEK) - 1;

		return day;
	}

	public static boolean haveSameTime(final long thisTimestamp, final long thatTimestamp) {
		TimeZone utc = TimeZone.getTimeZone("UTC");

		Calendar thisCal = Calendar.getInstance(utc);
		thisCal.setTimeZone(utc);
		thisCal.setTimeInMillis(
				TimeUnit.MILLISECONDS.convert(thisTimestamp, TimeUnit.SECONDS));

		Calendar thatCal = Calendar.getInstance(utc);
		thatCal.setTimeZone(utc);
		thatCal.setTimeInMillis(
				TimeUnit.MILLISECONDS.convert(thatTimestamp, TimeUnit.SECONDS));

		return (thisCal.getTime().getHours() == thatCal.getTime().getHours()
				 && thisCal.get(Calendar.MINUTE) == thatCal.get(Calendar.MINUTE)
				 && thisCal.get(Calendar.SECOND) == thatCal.get(Calendar.SECOND));
	 }


	/**
	 * @return Return scope (i.e. the list of school_ids) of a local administrator
	 */
	public static List<String> getLocalAdminScope(final UserInfos user) {
		Map<String, UserInfos.Function> functions = user.getFunctions();
		if (functions != null && functions.containsKey(DefaultFunctions.ADMIN_LOCAL)) {
			Function adminLocal = functions.get(DefaultFunctions.ADMIN_LOCAL);
			if (adminLocal != null) {
				return adminLocal.getScope();
			}
		}

		return new ArrayList<String>();
	}

	public static List<String> getUserIdAndGroupIds(UserInfos user) {
		final List<String> groupsAndUserIds = new ArrayList<>();
		groupsAndUserIds.add(user.getUserId());
		if (user.getGroupsIds() != null) {
			groupsAndUserIds.addAll(user.getGroupsIds());
		}
		return groupsAndUserIds;
	}

	public static boolean isDelayLessThanMin(JsonObject resource, long startDate, long now) {
		long minDelay = resource.getLong("min_delay", -1L);
		long delay = startDate - now;

		return (minDelay > -1 && minDelay > delay);
	}

	public static boolean isDelayGreaterThanMax(JsonObject resource, long endDate, long now) {
		long maxDelay = resource.getLong("max_delay", -1L);
		if (maxDelay == -1) {
			return false;
		}

		// Authorize users to book a resource N days in advance, without taking hour/minute/seconds into account
		maxDelay = (getTomorrowTimestamp() - now) + maxDelay;
		long delay = endDate - now;

		return (delay > maxDelay);
	}

	/**
	 * Transforms an SQL formatted date to a java date
	 *
	 * @param strDate formatted string to be transformed from SQL type
	 * @return parsed java date
	 */
	public static Date parseDateFromDB(String strDate) throws ParseException {
		String format = DATE_FORMAT;
		// the format is adapted to SQL. So we have to parse and replace some fields.
		format = format.replace("MI", "mm");
		format = format.replace("HH24", "HH");
		format = format.replace("DD", "dd");
		format = format.replace("YY", "yy");
		SimpleDateFormat formatter = new SimpleDateFormat(format);
		return formatter.parse(strDate);
	}

	/**
	 * Transforms an SQL formatted timestamp to a java date
	 *
	 * @param strDate formatted string to be transformed from SQL type
	 * @return parsed java date
	 */
	public static Date parseTimestampFromDB(String strDate) throws ParseException {
		String format = TIMESTAMP_FORMAT;

		SimpleDateFormat formatter = new SimpleDateFormat(format);
		formatter.setTimeZone(TimeZone.getTimeZone("UTC"));
		return formatter.parse(strDate);
	}


	/**
	 * Owner or managers of a resourceType, as well as local administrators of a resourceType's schoolId,
	 * do no need to respect constraints on resources' delays
	 */
	public static boolean canBypassDelaysConstraints(String owner, String schoolId, UserInfos user, JsonArray managers) {
		if (user.getUserId().equals(owner)) {
			return true;
		}

		List<String> scope = getLocalAdminScope(user);
		if (scope != null && !scope.isEmpty() && scope.contains(schoolId)) {
			return true;
		}

		if (managers != null && managers.size() > 0) {
			// Create a list containing userId and groupIds of current user
			List<String> userAndGroupIds = new ArrayList<>();
			userAndGroupIds.add(user.getUserId());
			userAndGroupIds.addAll(user.getGroupsIds());

			// Return true if managers and userAndGroupIds have at least one common element
			if (!Collections.disjoint(userAndGroupIds, managers.getList())) {
				return true;
			}
		}

		return false;
	}
} public class Rbs extends BaseServer {

	public final static String RBS_NAME = "RBS";
	public final static String BOOKING_TABLE_NAME = "booking";
	public final static String RESOURCE_TABLE_NAME = "resource";
	public final static String RESOURCE_SHARE_TABLE_NAME = "resource_shares";
	public final static String RESOURCE_TYPE_TABLE_NAME = "resource_type";
	public final static String RESOURCE_TYPE_SHARE_TABLE_NAME = "resource_type_shares";

	public static String DB_SCHEMA;
	public static String AVAILABILITY_TABLE;
	public static String BOOKING_TABLE;
	public static String GROUPS_TABLE;
	public static String MEMBERS_TABLE;
	public static String NOTIFICATIONS_TABLE;
	public static String RESOURCE_TABLE;
	public static String RESOURCE_SHARES_TABLE;
	public static String RESOURCE_TYPE_TABLE;
	public static String RESOURCE_TYPE_SHARES_TABLE;
	public static String USERS_TABLE;

	@Override
	public void start() throws Exception {
		super.start();
		final EventBus eb = getEventBus(vertx);

		// Set RepositoryEvents implementation used to process events published for transition
		setRepositoryEvents(new RbsRepositoryEvents());
		if (config.getBoolean("searching-event", true)) {
			setSearchingEvents(new RbsSearchingEvents());
		}

		// Store tables' SQL name in constants
		DB_SCHEMA = "rbs";
		AVAILABILITY_TABLE = DB_SCHEMA + ".availability";
		BOOKING_TABLE = DB_SCHEMA + ".booking";
		GROUPS_TABLE = DB_SCHEMA + ".groups";
		MEMBERS_TABLE = DB_SCHEMA + ".members";
		NOTIFICATIONS_TABLE = DB_SCHEMA + ".notifications";
		RESOURCE_TABLE = DB_SCHEMA + ".resource";
		RESOURCE_SHARES_TABLE = DB_SCHEMA + ".resource_shares";
		RESOURCE_TYPE_TABLE = DB_SCHEMA + ".resource_type";
		RESOURCE_TYPE_SHARES_TABLE = DB_SCHEMA + ".resource_type_shares";
		USERS_TABLE = DB_SCHEMA + ".users";


		// Controllers
		addController(new DisplayController());

		SqlConf confType = SqlConfs.createConf(ResourceTypeController.class.getName());
		confType.setTable(RESOURCE_TYPE_TABLE_NAME);
		confType.setShareTable(RESOURCE_TYPE_SHARE_TABLE_NAME);
		confType.setSchema(getSchema());
		ResourceTypeController typeController = new ResourceTypeController(eb);
		SqlCrudService typeSqlCrudService = new SqlCrudService(getSchema(), RESOURCE_TYPE_TABLE_NAME, RESOURCE_TYPE_SHARE_TABLE_NAME,
				new fr.wseduc.webutils.collections.JsonArray().add("*"), new JsonArray().add("*"), true);
		typeController.setCrudService(typeSqlCrudService);
		typeController.setShareService(new SqlShareService(getSchema(), RESOURCE_TYPE_SHARE_TABLE_NAME,
				eb, securedActions, null));
		addController(typeController);

		SqlConf confResource = SqlConfs.createConf(ResourceController.class.getName());
		confResource.setTable(RESOURCE_TABLE_NAME);
		confResource.setShareTable(RESOURCE_SHARE_TABLE_NAME);
		confResource.setSchema(getSchema());
		ResourceController resourceController = new ResourceController();
		SqlCrudService resourceSqlCrudService = new SqlCrudService(getSchema(), RESOURCE_TABLE_NAME, RESOURCE_SHARE_TABLE_NAME,
				new fr.wseduc.webutils.collections.JsonArray().add("*"), new JsonArray().add("*"), true);
		resourceController.setCrudService(resourceSqlCrudService);
		resourceController.setShareService(new SqlShareService(getSchema(), RESOURCE_SHARE_TABLE_NAME,
				eb, securedActions, null));
		addController(resourceController);


		DeploymentOptions options = new DeploymentOptions().setWorker(true);
		vertx.deployVerticle(new PdfExportService(), options);
		vertx.deployVerticle(new IcalExportService(), options);

		addController(new BookingController(eb));
		addController(new AvailabilityController());

		setDefaultResourceFilter(new TypeOwnerSharedOrLocalAdmin());
	}

} public class Slots extends ArrayList<Slot> {

    public Slots() {
        super();
    }

    public Slots(JsonArray slots) {
        slots.forEach(stringSlot -> {
            Slot slot = new Slot(new JsonObject(stringSlot.toString()));
            this.add(slot);
        });
    }

    public boolean areNotStartingAndEndingSameDay() {
        boolean oneFound = false;
        for(Slot slot : this){
            if(slot.isNotStartingAndEndingSameDay()){ oneFound = true;}
        }
        return oneFound;
    }

    public boolean areNotRespectingMinDelay(Optional<Long> minDelay) {
        boolean oneFound = false;
        for(Slot slot : this){
            if((minDelay.isPresent() && minDelay.get() > slot.getDelayFromNowToStart())){ oneFound = true;}
        }
        return oneFound;
    }
    public boolean areNotRespectingMaxDelay(Booking booking, Long maxDelay) {
        boolean oneFound = false;
        for(Slot slot : this){
            if(booking.hasPeriodicEndDate() || booking.getOccurrences(0) != 0){
                for (Slot itSlot : new Slot.SlotIterable(booking, slot)){
                    maxDelay = (BookingDateUtils.tomorrowTimestampSecondsForIana(itSlot.getIana()) -
                            BookingDateUtils.currentTimestampSecondsForIana(itSlot.getIana())) + maxDelay;
                    if(itSlot.getDelayFromNowToEnd() > maxDelay){ oneFound = true;}
                }
            }else{
                maxDelay = (BookingDateUtils.tomorrowTimestampSecondsForIana(slot.getIana()) -
                        BookingDateUtils.currentTimestampSecondsForIana(slot.getIana())) + maxDelay;
                if(slot.getDelayFromNowToEnd() > maxDelay){ oneFound = true;}
            }
        }
        return oneFound;
    }
    public Slot getSlotWithLatestEndDate(){
        Slot slot = this.get(0);
        if(this.size() > 1){
            for(int i =1 ; i< this.size(); i++){
                if(slot.getEnd().isBefore(this.get(i).getEnd())){
                    slot = this.get(i);
                }
            }
        }
        return slot;
    }

    public Slot getSlotWithFirstStartDate(){
        Slot slot = this.get(0);
        if(this.size() > 1){
            for(int i =1 ; i< this.size(); i++){
                if(slot.getStart().isAfter(this.get(i).getStart())){
                    slot = this.get(i);
                }
            }
        }
        return slot;
    }

} public class TypeAndResourceAppendPolicy implements ResourcesProvider {

	@Override
	public void authorize(final HttpServerRequest request, final Binding binding, final UserInfos user, final Handler<Boolean> handler) {
		SqlConf conf = SqlConfs.getConf(ResourceController.class.getName());
		String resourceId = request.params().get(conf.getResourceIdLabel());
		String bookingId = request.params().get("bookingId");
		boolean hasBooking = (bookingId != null && !bookingId.trim().isEmpty());

		if (resourceId != null && !resourceId.trim().isEmpty() && (parseId(resourceId) instanceof Integer)) {
			request.pause();
			// Replace character '.' by '-' in the called method's name
			String sharedMethod = binding.getServiceMethod().replaceAll("\\.", "-");

			// Groups and users
			final List<String> groupsAndUserIds = new ArrayList<>();
			groupsAndUserIds.add(user.getUserId());
			if (user.getGroupsIds() != null) {
				groupsAndUserIds.addAll(user.getGroupsIds());
			}

			// Query
			StringBuilder query = new StringBuilder();
			JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
			query.append("SELECT count(*)")
				.append(" FROM rbs.resource AS r")
				.append(" INNER JOIN rbs.resource_type AS t ON r.type_id = t.id");
			if (hasBooking) {
				// Additional join when parameter bookingId is used
				query.append(" INNER JOIN rbs.booking AS b ON b.resource_id = r.id");
			}

			query.append(" LEFT JOIN rbs.resource_type_shares AS ts ON t.id = ts.resource_id")
					.append(" LEFT JOIN rbs.resource_shares AS rs ON r.id = rs.resource_id")
					.append(" WHERE (");

			if (isUpdateBooking(binding) || isUpdatePeriodicBooking(binding) || isDeleteBooking(binding)) {
				query.append("(ts.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray())).append(" AND ts.action = 'net-atos-entng-rbs-controllers-BookingController|processBooking')");
				for (String groupOruser : groupsAndUserIds) {
					values.add(groupOruser);
				}

				query.append(" OR (rs.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray())).append(" AND rs.action = 'net-atos-entng-rbs-controllers-BookingController|processBooking')");
				for (String groupOruser : groupsAndUserIds) {
					values.add(groupOruser);
				}
			} else {
				query.append("(ts.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray())).append(" AND ts.action = ?)");
				for (String groupOruser : groupsAndUserIds) {
					values.add(groupOruser);
				}
				values.add(sharedMethod);

				query.append(" OR (rs.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray())).append(" AND rs.action = ?)");
				for (String groupOruser : groupsAndUserIds) {
					values.add(groupOruser);
				}
				values.add(sharedMethod);
			}

			// Authorize user if he is a local administrator for the resourceType's school_id
			List<String> scope = getLocalAdminScope(user);
			if (scope!=null && !scope.isEmpty()) {
				query.append(" OR t.school_id IN ").append(Sql.listPrepared(scope.toArray()));
				for (String schoolId : scope) {
					values.add(schoolId);
				}
			}

			query.append(" OR t.owner = ? OR r.owner = ?");
			values.add(user.getUserId()).add(user.getUserId());
			if(isDeleteBooking(binding)) {
				query.append(" OR b.owner = ?"); // Owner can delete his booking
				values.add(user.getUserId());
			} else if(isUpdateBooking(binding) || isUpdatePeriodicBooking(binding)) {
				// Check that the user is the booking's owner
				query.append(" OR b.owner = ?"); // Owner can delete his booking
				values.add(user.getUserId());
			}

			query.append(") AND r.id = ?");
			values.add(Sql.parseId(resourceId));
			if (hasBooking) {
				query.append(" AND b.id = ?");
				values.add(Sql.parseId(bookingId));
			}

			if (isCreatePeriodicBooking(binding)) {
				// Check that the resource allows periodic_booking
				query.append(" AND r.periodic_booking = ?");
				values.add(true);
			}
//			else if(isProcessBooking(binding)) {
//				// A booking can be validated or refused, only if its status is not "suspended"
//				query.append(" AND b.status != ?");
//				values.add(SUSPENDED.status());
//			}

			// Execute
			Sql.getInstance().prepared(query.toString(), values, message -> {
				request.resume();
				Long count = SqlResult.countResult(message);
				handler.handle(count != null && count > 0);
			});
		} else {
			handler.handle(false);
		}
	}


	private boolean isCreateBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.POST, "net.atos.entng.rbs.controllers.BookingController|createBooking");
	}

	private boolean isCreatePeriodicBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.POST, "net.atos.entng.rbs.controllers.BookingController|createPeriodicBooking");
	}

	private boolean isUpdateBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.PUT, "net.atos.entng.rbs.controllers.BookingController|updateBooking");
	}

	private boolean isUpdatePeriodicBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.PUT, "net.atos.entng.rbs.controllers.BookingController|updatePeriodicBooking");
	}

	private boolean isProcessBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.PUT, "net.atos.entng.rbs.controllers.BookingController|processBooking");
	}

	private boolean isDeleteBooking(final Binding binding) {
		return bindingIsThatMethod(binding, HttpMethod.DELETE, "net.atos.entng.rbs.controllers.BookingController|deleteBooking");
	}

	private boolean bindingIsThatMethod(final Binding binding, final HttpMethod thatHttpMethod, final String thatServiceMethod) {
		return (thatHttpMethod.equals(binding.getMethod()) && thatServiceMethod.equals(binding.getServiceMethod()));
	}


} public class ExportBooking {

	public static final String BOOKING_ID = "id";
	public static final String BOOKING_OWNER_ID = "owner"; // "ff2238cd-5110-431b-a6fa-bbc4fa51411d",
	public static final String BOOKING_START_DATE = "start_date"; //:"2017-06-26T09:00:00.000",
	public static final String BOOKING_END_DATE = "end_date"; // "2017-06-26T10:00:00.000",
	public static final String BOOKING_REASON = "booking_reason"; // free text
	public static final String BOOKING_OWNER_NAME = "owner_name"; // "Catherine BAILLY",
	public static final String BOOKING_MODERATOR_ID = "moderator_id"; // null,
	public static final String BOOKING_MODERATOR_NAME = "moderator_name"; // null
	public static final String SCHOOL_ID = "school_id"; // "a4d06ecc-e3cd-428b-b239-0a82a208f715",
	public static final String RESOURCE_TYPE_ID = "type_id"; // 1,
	public static final String RESOURCE_TYPE_COLOR = "type_color"; // "#02ed09",
	public static final String RESOURCE_ID = "resource_id"; //2,
	public static final String RESOURCE_NAME = "resource_name"; // "PC Windaube",
	public static final String RESOURCE_COLOR = "resource_color"; // "#02ed19",
	public static final String SCHOOL_NAME = "school_name"; // added by post treatment (stored in MongoDB, not PostgreSQL)
	public static final String QUANTITY = "quantity"; // 5

	private JsonObject data;

	public ExportBooking(JsonObject data) {
		this.data = data;
	}

	public JsonObject toJson() {
		return data;
	}

	public String getSchoolId() {
		return data.getString(SCHOOL_ID);
	}

	public void setSchoolName(String schoolName) {
		data.put(SCHOOL_NAME, schoolName);
	}

	public String getColor() {
		String resourceColor = data.getString(RESOURCE_COLOR);
		if (resourceColor == null || resourceColor.isEmpty()) {
			return data.getString(RESOURCE_TYPE_COLOR);
		}
		return resourceColor;
	}

	public String getStart() {
		return data.getString(BOOKING_START_DATE);
	}

	public String getEnd() {
		return data.getString(BOOKING_END_DATE);
	}

	public Number getQuantity() {
		return data.getInteger(QUANTITY);
	}


	@Override
	public String toString() {
		final StringBuffer sb = new StringBuffer("ExportBooking{");
		sb.append("resource=").append(getResource());
		sb.append(", owner=").append(getOwner());
		sb.append(", start=").append(getStart());
		sb.append(", end=").append(getEnd());
		sb.append(", quantity=").append(getQuantity());
		sb.append('}');
		return sb.toString();
	}

	public String getOwner() {
		return data.getString(BOOKING_OWNER_NAME);
	}

	public String getResource() {
		return data.getString(RESOURCE_NAME);
	}

	public String getSchoolName() {
		return data.getString(SCHOOL_NAME);
	}

	public String getBookingReason() {
		return data.getString(BOOKING_REASON);
	}

	public Number getId() {
		return data.getInteger(BOOKING_ID);
	}

	public String getOwnerId() {
		return data.getString(BOOKING_OWNER_ID);
	}
} public class ExportRequest {

	private static final SimpleDateFormat DATE_FORMATTER = new SimpleDateFormat("yyyy-MM-dd");
	public static final String USER_TZ = "usertimezone";
	public static final String START_DATE = "startdate";
	public static final String END_DATE = "enddate";
	public static final String FORMAT = "format";
	public static final String VIEW = "view";
	public static final String RESOURCE_IDS = "resourceIds";

    public enum Format {ICAL, PDF}

    public enum View {DAY, WEEK, LIST, NA}

	private UserInfos userInfos;
	private View view = View.WEEK;
	private Format format = Format.ICAL;
	private List<Long> resourceIds = new ArrayList<>();
	private String userTz; // Needed for dates to be expressed in the user time zone. (UTC by default)
	private String startDate;
	private String endDate;

    public ExportRequest(JsonObject data) {
        this(data, null);
    }

	public ExportRequest(JsonObject userExportRequest, UserInfos userInfos) {
		this.userInfos = userInfos;
		this.format = Format.valueOf(userExportRequest.getString(FORMAT));
		this.view = View.valueOf(userExportRequest.getString(VIEW));
		
		this.userTz = userExportRequest.getString(USER_TZ);

        this.startDate = userExportRequest.getString(START_DATE);
        this.endDate = userExportRequest.getString(END_DATE);
        checkPeriodValid(startDate, endDate);

        try {
            JsonArray userExportResourceArray = userExportRequest.getJsonArray(RESOURCE_IDS, new fr.wseduc.webutils.collections.JsonArray());
            for (Object resourceId : userExportResourceArray) {
                this.resourceIds.add(new Long((Integer) resourceId));
            }
        } catch (NumberFormatException | NullPointerException | ClassCastException e) {
            throw new IllegalArgumentException("params resources must be defined with an array of Integer");
        }

    }

    private void checkPeriodValid(String startInput, String endInput) {
        Date start;
        Date end;
        try {
            start = DATE_FORMATTER.parse(startInput);
            end = DATE_FORMATTER.parse(endInput);
        } catch (NullPointerException | ParseException e) {
            throw new IllegalArgumentException("params start and end date must be defined with YYYY-MM-DD format !");
        }
        if (start.after(end)) {
            throw new IllegalArgumentException("Param startdate must be before enddate");
        }
    }

    public UserInfos getUserInfos() {
        return userInfos;
    }

    public View getView() {
        return view;
    }

    public Format getFormat() {
        return format;
    }

    public List<Long> getResourceIds() {
        return resourceIds;
    }

    public String getStartDate() {
        return startDate;
    }

    public String getEndDate() {
        return endDate;
    }


    public void setFormat(Format format) {
        this.format = format;
    }

    public void setView(View view) {
        this.view = view;
    }

    public void setResourceIds(List<Long> resourceIds) {
        this.resourceIds = resourceIds;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

	public String getUserTz() {
		return userTz;
	}

	public void setUserTz(String userTz) {
		this.userTz = userTz;
	}
} public class ExportResponse {
	public static final String BOOKINGS = "bookings";

	private ExportRequest request;
	private List<ExportBooking> bookings;

	public ExportResponse(ExportRequest request) {
		this.request = request;
	}

	public ExportRequest getRequest() {
		return request;
	}

	public List<ExportBooking> getBookings() {
		return bookings;
	}

	public void setBookings(List<ExportBooking> bookings) {
		this.bookings = bookings;
	}

	public void setSchoolNames(Map<String, String> schoolNamesById) {
		for (ExportBooking exportBooking : bookings) {
			String schoolName = null;
			String schoolId = exportBooking.getSchoolId();
			if (schoolId != null) {
				schoolName = schoolNamesById.get(schoolId);
			}
			if (schoolName == null || schoolName.isEmpty()) {
				schoolName = "Inconnue";
			}
			exportBooking.setSchoolName(schoolName);
		}
	}

	public JsonObject toJson() {
		JsonObject response = new JsonObject();
		response.put(ExportRequest.START_DATE, request.getStartDate());
		response.put(ExportRequest.END_DATE, request.getEndDate());
		response.put(ExportRequest.FORMAT, request.getFormat().name());
		response.put(ExportRequest.VIEW, request.getView().name());
		JsonArray jsonBookings = new fr.wseduc.webutils.collections.JsonArray();
		for (ExportBooking booking : bookings) {
			jsonBookings.add(booking.toJson());
		}
		response.put(BOOKINGS, jsonBookings);
		return response;
	}

	public static ExportRequest.View getView(JsonObject exportResponse) {
		String viewAsString = exportResponse.getString(ExportRequest.VIEW, ExportRequest.View.WEEK.name());
		return ExportRequest.View.valueOf(viewAsString);
	}

	public static ExportResponse fromJson(JsonObject data) {
		ExportRequest exportRequest = new ExportRequest(data);
		ExportResponse exportResponse = new ExportResponse(exportRequest);
		JsonArray array = data.getJsonArray(BOOKINGS);
		List<ExportBooking> bookings = new ArrayList<>();
		for (Object o : array) {
			ExportBooking booking = new ExportBooking((JsonObject) o);
			bookings.add(booking);
		}
		exportResponse.setBookings(bookings);
		return exportResponse;
	}
} public class PdfExportService extends AbstractVerticle implements Handler<Message<JsonObject>> {
	private static final Logger LOG = LoggerFactory.getLogger(PdfExportService.class);

	public static final String PDF_HANDLER_ADDRESS = "rbs.pdf.handler";
	/**
	 * Actions handled by worker
	 */
	public static final String ACTION_CONVERT = "convert";


	private static final String WEEK_HTML_TEMPLATE = "./pdftemplate/booking_week.pdf.xhtml";
	private static final String DAY_HTML_TEMPLATE = "./pdftemplate/booking_day.pdf.xhtml";
	private static final String LIST_HTML_TEMPLATE = "./pdftemplate/booking_list.pdf.xhtml";


	@Override
	public void start() throws Exception {
		super.start();
		vertx.eventBus().consumer(PDF_HANDLER_ADDRESS, this);
	}

	@Override
	public void handle(Message<JsonObject> message) {
		System.out.println("1 INSIDE HANDLE() = " + message.body());

		String action = message.body().getString("action", "");
		JsonObject exportResponse = message.body().getJsonObject("data", new JsonObject());
		String scheme = message.body().getString("scheme", "");
		String host = message.body().getString("host", "");
		String acceptLanguage = message.body().getString("acceptLanguage", "");
		if(acceptLanguage == null || acceptLanguage.isEmpty()) {
			acceptLanguage = "fr";
		}
		String userTimeZone = message.body().getString("userTimeZone", "");

		String[] langs = acceptLanguage.split(",");
		Locale locale =  Locale.forLanguageTag(langs[0].split("-")[0]);

		switch (action) {
			case ACTION_CONVERT:
				generatePdfFile(exportResponse, scheme, host, locale, userTimeZone, message);
				break;
			default:
				JsonObject results = new JsonObject();
				results.put("message", "Unknown action");
				results.put("status", 400);
				message.reply(results);
		}
	}

	private void generatePdfFile(final JsonObject exportResponse, final String scheme, final String host, final Locale locale,
								 final String userTimeZone, final Message<JsonObject> message) {
		final String htmlTemplateFile = getTemplate(exportResponse);

		String absolutePath = FileResolver.absolutePath(htmlTemplateFile);

		vertx.fileSystem().readFile(absolutePath, result -> {
			if (!result.succeeded()) {
				Throwable cause = result.cause();
				LOG.error("Template " + htmlTemplateFile + "  could not be read", cause);
				JsonObject results = new JsonObject();
				results.put("status", 500);
				results.put("message", cause != null ? cause.getMessage() : "");
				message.reply(results);
				return;
			}

			try {
				JsonObject preparedData = prepareData(exportResponse, host, locale, userTimeZone);

				String filledTemplate = fillTemplate(result.result().toString("UTF-8"), preparedData);
				LocalMap<Object, Object> skins = vertx.sharedData().getLocalMap("skins");
				final String baseUrl = scheme + "://" + host + "/assets/themes/" + skins.get(host) + "/img/";
				JsonObject actionObject = new JsonObject();
				actionObject
						.put("content", filledTemplate.getBytes())
						.put("baseUrl", baseUrl);
				String node = (String) vertx.sharedData().getLocalMap("server").get("node");
				if (node == null) {
					node = "";
				}
				vertx.eventBus().send(node + "entcore.pdf.generator", actionObject, handlerToAsyncHandler(reply -> {
					JsonObject pdfResponse = reply.body();
					if (!"ok".equals(pdfResponse.getString("status"))) {
						String pdfResponseString = pdfResponse.getString("message");
						LOG.error("Conversion error: " + pdfResponseString);
						JsonObject results = new JsonObject();
						results.put("status", 500);
						results.put("message", pdfResponseString);
						message.reply(results);
						return;
					}

					byte[] pdf = pdfResponse.getBinary("content");

					JsonObject results = new JsonObject();
					results.put("status", 200);
					results.put("content", pdf);
					message.reply(results);
				}));

			} catch (Exception e) {
				LOG.error("Conversion Error", e);
				JsonObject results = new JsonObject();
				results.put("status", 500);
				results.put("message", e.getMessage());
				message.reply(results);
				return;
			}
		});
	}

	private JsonObject prepareData(JsonObject jsonExportResponse, String host, Locale locale, String userTimeZone) {
		JsonFormatter formatter = JsonFormatter.buildFormater(jsonExportResponse, host, locale, userTimeZone);

		JsonObject convertedJson = formatter.format();
		JsonArray jsonFileArray = new fr.wseduc.webutils.collections.JsonArray();
		jsonFileArray.add(convertedJson);

		return new JsonObject().put("export", jsonFileArray);
	}

	private String fillTemplate(String templateAsString, JsonObject preparedData) {
		Mustache.Compiler compiler = Mustache.compiler().defaultValue("");
		Template template = compiler.compile(templateAsString);

		Map<String, Object> ctx = new JsonObject(preparedData.toString()).getMap();

		StringWriter writer = new StringWriter();
		template.execute(ctx, writer);
		return writer.getBuffer().toString();
	}

	private String getTemplate(JsonObject exportResponse) {
		String htmlTemplateFile;
		ExportRequest.View view = getView(exportResponse);
		switch (view) {
			case DAY:
				htmlTemplateFile = DAY_HTML_TEMPLATE;
				break;
			case LIST:
				htmlTemplateFile = LIST_HTML_TEMPLATE;
				break;
			case WEEK:
				htmlTemplateFile = WEEK_HTML_TEMPLATE;
				break;
			default:
				htmlTemplateFile = WEEK_HTML_TEMPLATE;
				break;
		}
		return htmlTemplateFile;
	}


} public class BookingUtils {
	private final static String DATE_FORMAT = BookingServiceSqlImpl.DATE_FORMAT;
	public static final String TIMESTAMP_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSS";


	/**
	 * @param lastSelectedDay : index of last selected day
	 * @param selectedDays    : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @param periodicity     : slots are repeated every "periodicity" week
	 * @return Number of days until next slot
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public static int getNextInterval(final int lastSelectedDay, final String selectedDays, final int periodicity) {
		if (!selectedDays.contains("1")) {
			throw new IllegalArgumentException("Argument selectedDays must contain char '1'");
		}

		int count = 0;
		int k = lastSelectedDay;

		do {
			k++;
			if (k == 1) {
				// On monday, go to next week
				count += (periodicity - 1) * 7;
			} else if (k >= 7) {
				k = k % 7;
			}
			count++;
		} while (selectedDays.charAt(k) != '1');

		return count;
	}

	/**
	 * @param firstSelectedDay : index of the first slot's day
	 * @param selectedDays     : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @param durationInDays   : difference in days between the first slot's end date and the end date of the periodic booking
	 * @param periodicity      : slots are repeated every "periodicity" week
	 * @return Number of occurrences
	 * @throws IndexOutOfBoundsException
	 */
	public static int getOccurrences(final int firstSelectedDay, final String selectedDays,
	                                 final long durationInDays, final int periodicity) {
		int count = 0;
		int k = firstSelectedDay;
		int i = 0;

		while (i <= durationInDays) {
			if (k >= 7) {
				k = k % 7;
			}
			if (selectedDays.charAt(k) == '1') {
				count++;
			}
			k++;
			if (k == 1) {
				// On monday, go to next week
				i += (periodicity - 1) * 7;
			}
			i++;
		}

		return count;
	}

	/**
	 * @param occurrences   : number of occurrences
	 * @param periodicity   : slots are repeated every "periodicity" week
	 * @param firstSlotDate : Unix timestamp of the first slot's date (start or end date)
	 * @param firstSlotDay  : index of the first slot's day
	 * @param selectedDays  : bit string, used like an array of boolean, representing selected days. Char 0 is sunday, char 1 monday...
	 * @return Unix timestamp of the last slot's date (start or end date)
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public static long getLastSlotDate(final int occurrences, final int periodicity,
	                                   final long firstSlotDate, final int firstSlotDay, final String selectedDays) {

		long lastSlotDate = firstSlotDate;
		if (occurrences > 1) {
			int selectedDay = firstSlotDay;
			int intervalFromFirstDay = 0;

			for (int i = 1; i <= (occurrences - 1); i++) {
				int interval = getNextInterval(selectedDay, selectedDays, periodicity);
				intervalFromFirstDay += interval;
				selectedDay = (selectedDay + interval) % 7;
			}
			lastSlotDate += TimeUnit.SECONDS.convert(intervalFromFirstDay, TimeUnit.DAYS);
		}

		return lastSlotDate;
	}

	/**
	 * @return Current unix timestamp in seconds
	 */
	public static long getCurrentTimestamp() {
		return TimeUnit.SECONDS.convert(Calendar.getInstance().getTimeInMillis(), TimeUnit.MILLISECONDS);
	}

	/**
	 * @return Unix timestamp of tomorrow midnight in seconds
	 */
	public static long getTomorrowTimestamp() {
		// Get tomorrow by adding one day to current time
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DAY_OF_MONTH, 1);

		// Set time to midnight
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);

		return TimeUnit.SECONDS.convert(cal.getTimeInMillis(), TimeUnit.MILLISECONDS);
	}

	/**
	 * @param unixTimestamp in seconds
	 * @return 0 for sunday, 1 for monday, etc
	 */
	public static int getDayFromTimestamp(final long unixTimestamp) {
		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(TimeUnit.MILLISECONDS.convert(unixTimestamp, TimeUnit.SECONDS));
		// "- 1", so that sunday is 0, monday is 1, etc
		int day = cal.get(Calendar.DAY_OF_WEEK) - 1;

		return day;
	}

	public static boolean haveSameTime(final long thisTimestamp, final long thatTimestamp) {
		TimeZone utc = TimeZone.getTimeZone("UTC");

		Calendar thisCal = Calendar.getInstance(utc);
		thisCal.setTimeZone(utc);
		thisCal.setTimeInMillis(
				TimeUnit.MILLISECONDS.convert(thisTimestamp, TimeUnit.SECONDS));

		Calendar thatCal = Calendar.getInstance(utc);
		thatCal.setTimeZone(utc);
		thatCal.setTimeInMillis(
				TimeUnit.MILLISECONDS.convert(thatTimestamp, TimeUnit.SECONDS));

		return (thisCal.getTime().getHours() == thatCal.getTime().getHours()
				 && thisCal.get(Calendar.MINUTE) == thatCal.get(Calendar.MINUTE)
				 && thisCal.get(Calendar.SECOND) == thatCal.get(Calendar.SECOND));
	 }


	/**
	 * @return Return scope (i.e. the list of school_ids) of a local administrator
	 */
	public static List<String> getLocalAdminScope(final UserInfos user) {
		Map<String, UserInfos.Function> functions = user.getFunctions();
		if (functions != null && functions.containsKey(DefaultFunctions.ADMIN_LOCAL)) {
			Function adminLocal = functions.get(DefaultFunctions.ADMIN_LOCAL);
			if (adminLocal != null) {
				return adminLocal.getScope();
			}
		}

		return new ArrayList<String>();
	}

	public static List<String> getUserIdAndGroupIds(UserInfos user) {
		final List<String> groupsAndUserIds = new ArrayList<>();
		groupsAndUserIds.add(user.getUserId());
		if (user.getGroupsIds() != null) {
			groupsAndUserIds.addAll(user.getGroupsIds());
		}
		return groupsAndUserIds;
	}

	public static boolean isDelayLessThanMin(JsonObject resource, long startDate, long now) {
		long minDelay = resource.getLong("min_delay", -1L);
		long delay = startDate - now;

		return (minDelay > -1 && minDelay > delay);
	}

	public static boolean isDelayGreaterThanMax(JsonObject resource, long endDate, long now) {
		long maxDelay = resource.getLong("max_delay", -1L);
		if (maxDelay == -1) {
			return false;
		}

		// Authorize users to book a resource N days in advance, without taking hour/minute/seconds into account
		maxDelay = (getTomorrowTimestamp() - now) + maxDelay;
		long delay = endDate - now;

		return (delay > maxDelay);
	}

	/**
	 * Transforms an SQL formatted date to a java date
	 *
	 * @param strDate formatted string to be transformed from SQL type
	 * @return parsed java date
	 */
	public static Date parseDateFromDB(String strDate) throws ParseException {
		String format = DATE_FORMAT;
		// the format is adapted to SQL. So we have to parse and replace some fields.
		format = format.replace("MI", "mm");
		format = format.replace("HH24", "HH");
		format = format.replace("DD", "dd");
		format = format.replace("YY", "yy");
		SimpleDateFormat formatter = new SimpleDateFormat(format);
		return formatter.parse(strDate);
	}

	/**
	 * Transforms an SQL formatted timestamp to a java date
	 *
	 * @param strDate formatted string to be transformed from SQL type
	 * @return parsed java date
	 */
	public static Date parseTimestampFromDB(String strDate) throws ParseException {
		String format = TIMESTAMP_FORMAT;

		SimpleDateFormat formatter = new SimpleDateFormat(format);
		formatter.setTimeZone(TimeZone.getTimeZone("UTC"));
		return formatter.parse(strDate);
	}


	/**
	 * Owner or managers of a resourceType, as well as local administrators of a resourceType's schoolId,
	 * do no need to respect constraints on resources' delays
	 */
	public static boolean canBypassDelaysConstraints(String owner, String schoolId, UserInfos user, JsonArray managers) {
		if (user.getUserId().equals(owner)) {
			return true;
		}

		List<String> scope = getLocalAdminScope(user);
		if (scope != null && !scope.isEmpty() && scope.contains(schoolId)) {
			return true;
		}

		if (managers != null && managers.size() > 0) {
			// Create a list containing userId and groupIds of current user
			List<String> userAndGroupIds = new ArrayList<>();
			userAndGroupIds.add(user.getUserId());
			userAndGroupIds.addAll(user.getGroupsIds());

			// Return true if managers and userAndGroupIds have at least one common element
			if (!Collections.disjoint(userAndGroupIds, managers.getList())) {
				return true;
			}
		}

		return false;
	}
} public class Booking {
	private final String bookingId;
	private final JsonObject json;
	private Resource resource;
	private Slots slots = new Slots();
	private String selectedDaysBitString;

	public Booking(JsonObject json, Resource resource) {
		this(json, resource, null);
	}

	public Booking(JsonObject json, Resource resource, final String id) {
		super();
		this.json = json;
		this.bookingId = id;
		this.resource = resource;
	}

	public Booking(JsonObject json, final String id, Slots slots) {
		super();
		this.json = json;
		this.bookingId = id;
		this.slots = slots;
	}

	public void setResource(Resource resource) {
		this.resource = resource;
	}
	public void setSlots(Slots slots) {
		this.slots = slots;
	}
	public String getBookingId() {
		return bookingId;
	}

	public JsonObject getJson() {
		return json;
	}

	public String getIana() {
		return json.getString("iana");
	}

	public Slots getSlots() { return  this.slots.size() == 0 ? new Slots(json.getJsonArray("slots", null)) : this.slots ; }

	public Object getRawStartDate() {
		return this.json.getValue("start_date");
	}

	public Object getRawEndDate() {
		return this.json.getValue("end_date");
	}




	public int getPeriodicity() {
		return json.getInteger("periodicity");
	}

	public long getPeriodicEndDateAsUTCSeconds() {
		return json.getLong("periodic_end_date", 0l);
	}

	public void setPeriodicEndDateAsUTCSeconds(long timestamp) {
		json.put("periodic_end_date", timestamp);
	}

	public String getBookingReason() {
		return json.getString("booking_reason");
	}

	public Integer getBookingQuantity() {
		return json.getInteger("quantity");
	}

	public Optional<JsonArray> getDays() {
		return Optional.ofNullable(json.getJsonArray("days", null));
	}

	public int getOccurrences(int defaut) {
		return json.getInteger("occurrences", defaut);
	}

	public void setOccurrences(int occurences) {
		json.put("occurrences", occurences);
	}

	public Optional<Long> getMaxDelayAsSeconds() {
		return resource.getMaxDelayAsSeconds();
	}

	public Optional<Long> getMinDelayAsSeconds() {
		return resource.getMinDelayAsSeconds();
	}

	public Long minDelayAsDay() {
		return BookingDateUtils.secondToDays(getMinDelayAsSeconds().orElse(-1l));
	}

	public Long maxDelayAsDay() {
		return BookingDateUtils.secondToDays(getMaxDelayAsSeconds().orElse(-1l));
	}

	public String getSelectedDaysBitString() {
		if (selectedDaysBitString == null) {
			computeSelectedDaysAsBitString();
		}
		return selectedDaysBitString;
	}
	public int dayOfWeekForPeriodicEndDate() {
		final long firstSlotEndDate = getPeriodicEndDateAsUTCSeconds();
		return BookingDateUtils.dayOfWeekForTimestampSecondsAndIana(firstSlotEndDate, getIana());
	}

	public boolean hasPeriodicEndDate() {
		return getPeriodicEndDateAsUTCSeconds() > 0l;
	}

	public boolean hasMinDelay() {
		return this.getMinDelayAsSeconds().isPresent();
	}

	public boolean hasMaxDelay() {
		return this.getMaxDelayAsSeconds().isPresent();
	}

	public boolean slotsNotRespectingMinDelay() {
		Optional<Long> minDelay = getMinDelayAsSeconds();
		return this.getSlots().areNotRespectingMinDelay(minDelay);
	}
    public boolean slotsNotRespectingMaxDelay() {
        Long maxDelay = getMaxDelayAsSeconds().orElse(-1l);
        if (maxDelay == -1) {
            return false;
        }
        return this.getSlots().areNotRespectingMaxDelay(this,maxDelay);
    }

	public boolean isNotPeriodic() {
		final long endDate = getPeriodicEndDateAsUTCSeconds();
		final int occurrences = getOccurrences(0);
		return endDate == 0L && occurrences == 0;
	}

	public boolean hasPeriodicEndAsLastDay() {
		final int endDateDay = dayOfWeekForPeriodicEndDate();
		JsonArray selectedDaysArray = getDays().get();
		Object endDateDayIsSelected = selectedDaysArray.getList().get(endDateDay);
		return (Boolean) endDateDayIsSelected;
	}

	public boolean hasNotSelectedDays() {
		final Optional<JsonArray> selectedDaysArrayOpt = getDays();
		return (!selectedDaysArrayOpt.isPresent() || selectedDaysArrayOpt.get().size() != 7);
	}

	public boolean hasNotSelectedStartDayOfWeek() {
        boolean oneFound = true;
        for(Slot slot : this.getSlots()){
			final int startDay = slot.dayOfWeekForStartDate();
            JsonArray selectedDaysArray = getDays().orElse(null);
            if (selectedDaysArray == null) {
                return true;
            }
            Object firstSlotDayIsSelected = selectedDaysArray.getList().get(startDay);
            if(!(boolean) firstSlotDayIsSelected){ oneFound = false;}
        }
        return !oneFound;
	}



	public void computeSelectedDaysAsBitString() {
		JsonArray selectedDaysArray = getDays().get();
		StringBuilder selectedDays = new StringBuilder();
		for (Object day : selectedDaysArray) {
			int isSelectedDay = ((Boolean) day) ? 1 : 0;
			selectedDays.append(isSelectedDay);
		}
		selectedDaysBitString = selectedDays.toString();
	}

	public long daysBetweenFirstSlotEndAndPeriodicEndDate(Slot slot) {
		long endDate = getPeriodicEndDateAsUTCSeconds();
		long firstSlotEnd = slot.getEndUTC();
		long duration = endDate - firstSlotEnd;
		return BookingDateUtils.secondToDays(duration);
	}

	/**
	 *
	 * firstSelectedDay : index of the first slot's day<br/>
	 * selectedDays : bit string, used like an array of boolean, representing
	 * selected days. Char 0 is sunday, char 1 monday...<br/>
	 * durationInDays : difference in days between the first slot's end date and the
	 * end date of the periodic booking<br/>
	 * periodicity : slots are repeated every "periodicity" week<br/>
	 * 
	 * @return Number of occurrences
	 *
	 * @throws IndexOutOfBoundsException
	 */
	public int countOccurrences(Slot slot) {
		final int periodicity = getPeriodicity();
		final long durationInDays = daysBetweenFirstSlotEndAndPeriodicEndDate(slot);
		final int firstSelectedDay = slot.dayOfWeekForStartDate();
		final String selectedDays = getSelectedDaysBitString();
		//
		int count = 0;
		int k = firstSelectedDay;
		int i = 0;

		while (i <= durationInDays) {
			if (k >= 7) {
				k = k % 7;
			}
			if (selectedDays.charAt(k) == '1') {
				count++;
			}
			k++;
			if (k == 1) {
				// On monday, go to next week
				i += (periodicity - 1) * 7;
			}
			i++;
		}
		return count;
	}

	/**
	 *
	 * occurrences : number of occurrences<br/>
	 * periodicity : slots are repeated every "periodicity" week<br/>
	 * firstSlotDate : Unix timestamp of the first slot's date (end date)<br/>
	 * firstSlotDay : index of the first slot's day<br/>
	 * selectedDays : bit string, used like an array of boolean, representing
	 * selected days. Char 0 is sunday, char 1 monday...<br/>
	 * 
	 * @return Unix timestamp of the last slot's date (start or end date)
	 *
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public long getLastSlotDate(final int occurrences) {
		final Slot lastSlot = getSlots().getSlotWithLatestEndDate();
		final long firstSlotDate = lastSlot.getEndUTC();
		final int firstSlotDay =lastSlot.dayOfWeekForStartDate();
		//
		long lastSlotDate = firstSlotDate;
		if (occurrences > 1) {
			int selectedDay = firstSlotDay;
			int intervalFromFirstDay = 0;

			for (int i = 1; i <= (occurrences - 1); i++) {
				int interval = getNextIntervalAsDayOfWeek(selectedDay);
				intervalFromFirstDay += interval;
				selectedDay = (selectedDay + interval) % 7;
			}
			lastSlotDate += BookingDateUtils.daysToSecond(intervalFromFirstDay);
		}

		return lastSlotDate;
	}

	/**
	 *
	 * lastSelectedDay : index of last selected day<br/>
	 * selectedDays : bit string, used like an array of boolean, representing
	 * selected days. Char 0 is sunday, char 1 monday...<br/>
	 * periodicity : slots are repeated every "periodicity" week<br/>
	 * 
	 * @return Number of days until next slot
	 *
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	public int getNextIntervalAsDayOfWeek(final int lastSelectedDay) {
		final int periodicity = getPeriodicity();
		final String selectedDays = getSelectedDaysBitString();
		if (!selectedDays.contains("1")) {
			throw new IllegalArgumentException("Argument selectedDays must contain char '1'");
		}

		int count = 0;
		int k = lastSelectedDay;

		do {
			k++;
			if (k == 1) {
				// On monday, go to next week
				count += (periodicity - 1) * 7;
			} else if (k >= 7) {
				k = k % 7;
			}
			count++;
		} while (selectedDays.charAt(k) != '1');

		return count;
	}


	public long computeAndSetLastEndDateAsUTCSedonds() {
		if (hasPeriodicEndDate()) { // Case when end_date is supplied
			if (hasPeriodicEndAsLastDay()) {
				return getPeriodicEndDateAsUTCSeconds();
			} else {
				// If the endDateDay is not a selected day, compute the end date
				// of the last slot
				int nbOccurrences = countOccurrences(this.getSlots().getSlotWithLatestEndDate());
				final long lastSlotEndDate = getLastSlotDate(nbOccurrences);
				// Replace the end date with the last slot's end date
				setPeriodicEndDateAsUTCSeconds(lastSlotEndDate);
				// Put the computed value of occurrences
				setOccurrences(nbOccurrences);
				return lastSlotEndDate;
			}
		} else { // Case when occurrences is supplied
			return getLastSlotDate(getOccurrences(0));
		}
	}

} public class Resource {
	private final JsonObject json;

	public Resource(JsonObject resource) {
		super();
		this.json = resource;
	}

	public Optional<Long> getMaxDelayAsSeconds() {
		return Optional.ofNullable(json.getLong("max_delay", null));
	}

	public Optional<Long> getMinDelayAsSeconds() {
		return Optional.ofNullable(json.getLong("min_delay", null));
	}

	public Optional<String> getOwner() {
		return Optional.ofNullable(json.getString("owner", null));
	}

	public Optional<String> getSchoolId() {
		return Optional.ofNullable(json.getString("school_id", null));
	}

	public Optional<String> getManagers() {
		return Optional.ofNullable(json.getString("managers", null));
	}

	public boolean hasNotOwnerOrSchoolId() {
		return !getOwner().isPresent() || !getSchoolId().isPresent();
	}

	public Optional<JsonArray> getManagersAsJsonArray() {
		Optional<String> managersString = getManagers();
		JsonArray managers = (managersString.isPresent()) ? new JsonArray(managersString.get()) : null;
		return Optional.ofNullable(managers);
	}

	/*
	 * Owner or managers of a resourceType, as well as local administrators of a
	 * resourceType's schoolId, do no need to respect constraints on resources'
	 * delays
	 */
	public boolean canBypassDelaysConstraints(UserInfos user) {
		String owner = getOwner().orElse(null);
		String schoolId = getSchoolId().orElse(null);
		JsonArray managers = getManagersAsJsonArray().orElse(null);

		if (user.getUserId().equals(owner)) {
			return true;
		}

		List<String> scope = getLocalAdminScope(user);
		if (scope != null && !scope.isEmpty() && scope.contains(schoolId)) {
			return true;
		}

		if (managers != null && managers.size() > 0) {
			// Create a list containing userId and groupIds of current user
			List<String> userAndGroupIds = new ArrayList<>();
			userAndGroupIds.add(user.getUserId());
			userAndGroupIds.addAll(user.getGroupsIds());

			// Return true if managers and userAndGroupIds have at least one common element
			if (!Collections.disjoint(userAndGroupIds, managers.getList())) {
				return true;
			}
		}

		return false;
	}
} public interface BookingService extends CrudService {

    /**
     * Create a booking
     *
     * @param resourceId : id of current resource
     * @param data       : object which contains information of booking
     * @param user       : information of current user logged
     * @param handler    : handler which contains the response
     */
    void createBooking(final String resourceId, final Booking data, final UserInfos user, final Handler<Either<String, JsonArray>> handler);

    /**
     * Create a periodic booking
     *
     * @param resourceId       : id of current resource
     * @param booking          : object which contains information of booking
     * @param user             : information of current user logged
     * @param handler          : handler which contains the response
     */
    void createPeriodicBooking(final String resourceId, Booking booking, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler);

    /**
     * Update a simple booking
     * @param resourceId        : id of current resource
     * @param data              : object which contains information of booking
     * @param handler           : handler which contains the response
     */
    void updateBooking(final String resourceId, final Booking data,
			final Handler<Either<String, JsonObject>> handler);

    /**
     * Update a periodic booking
     * @param resourceId        : id of current resource
     * @param data              : object which contains information of booking
     * @param user              : information of current user logged
     * @param handler           : handler which contains the response
     */
	public void updatePeriodicBooking(final String resourceId, final Booking data, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler);

    /**
     *  Update the status (validated or refused) of a booking which is in Processing status
     *
     * @param resourceId        : id of current resource
     * @param bookingId         : id of current booking
     * @param newStatus         : new status for the booking
     * @param data              : object which contains information of periodic booking
     * @param user              : object which contains information of periodic booking
     * @param handler           : handler which contains the response
     */
	public void processBooking(final String resourceId, final String bookingId, final int newStatus,
			final JsonObject data, final UserInfos user, final Handler<Either<String, JsonArray>> handler);

    /**
     * Get the list of bookings which are owned by the current user
     *
     * @param user    : information of current user logged
     * @param handler : handler which contains the response
     */
	public void listUserBookings(final UserInfos user, final Handler<Either<String, JsonArray>> handler);

    /**
     * Get the list of all bookings
     *
     * @param user             : information of current user logged
     * @param groupsAndUserIds : list of groups and id of users who are authorized
     * @param handler          : handler which contains the response
     */
	public void listAllBookings(final UserInfos user, final List<String> groupsAndUserIds,
			final Handler<Either<String, JsonArray>> handler);
    /**
     * Get the list of all bookings sorted by date
     *
     * @param user             : information of current user logged
     * @param groupsAndUserIds : list of groups and id of users who are authorized
     * @param startDate        : the beginning of booking
     * @param endDate          : the end of booking
     * @param handler          : handler which contains the response
     */
	public void listAllBookingsByDates(final UserInfos user, final List<String> groupsAndUserIds,
			final String startDate, final String endDate, final Handler<Either<String, JsonArray>> handler);
    /**
     * Get the list of all slots of current booking
     *
     * @param bookingId : id of current booking
     * @param handler   : handler which contains the response
     */
	public void listFullSlotsBooking(final String bookingId, final Handler<Either<String, JsonArray>> handler);
    /**
     * Get the booking list for current resource
     *
     * @param resourceId : id of current resource
     * @param handler    : handler which contains the response
     */
	public void listBookingsByResource(final String resourceId, final Handler<Either<String, JsonArray>> handler);

	/**
	 * Get the list of bookings unprocessed
	 *
	 * @param groupsAndUserIds : list of groups and id of users who are authorized
	 * @param user             : information of current user logged
	 * @param handler          : handler which contains the response
	 */
	void listUnprocessedBookings(final List<String> groupsAndUserIds, final UserInfos user,
	                             final Handler<Either<String, JsonArray>> handler);

	/**
	 * Get the moderator list of current booking
	 *
	 * @param bookingId : id of current booking
	 * @param user      : information of current user logged
	 * @param handler   : handler which contains the response
	 */
	void getModeratorsIds(final String bookingId, final UserInfos user,
	                      final Handler<Either<String, JsonArray>> handler);

	/**
	 * Get the resource name of current booking
	 *
	 * @param bookingId : id of current booking
	 * @param handler   : handler which contains the response
	 */
	void getResourceName(final String bookingId, final Handler<Either<String, JsonObject>> handler);

	/**
	 * Get the information of booking + the resource name of current booking
	 *
	 * @param bookingId : id of current booking
	 * @param handler   : handler which contains the response
	 */
	void getBookingWithResourceName(final String bookingId, final Handler<Either<String, JsonObject>> handler);

	/**
	 * Get the parent booking of current booking
	 *
	 * @param bookingId : id of current booking
	 * @param handler   : handler which contains the response
	 */
	void getParentBooking(final String bookingId, final Handler<Either<String, JsonObject>> handler);

	/**
	 * Get the information of current booking
	 *
	 * @param bookingId : id of current booking
	 * @param handler   : handler which contains the response
	 */
	void getBooking(final String bookingId, final Handler<Either<String, JsonObject>> handler);

	/**
	 * Delete the future bookings for a periodic booking
	 *
	 * @param bookingId : id of current booking
	 * @param startDate : the beggining of booking
	 * @param handler   : handler which contains the response
	 */
	void deleteFuturePeriodicBooking(String bookingId, Date startDate, Handler<Either<String, JsonArray>> handler);

	/**
	 * Get the list of bookings for the export
	 *
	 * @param exportRequest : the export request for generate an export
	 * @param handler       : handler which contains the response
	 */
	void getBookingsForExport(ExportRequest exportRequest, Handler<Either<String, List<ExportBooking>>> handler);
} public class BookingServiceSqlImpl extends SqlCrudService implements BookingService {

	private final static String LOCK_BOOKING_QUERY = "LOCK TABLE rbs.booking IN SHARE ROW EXCLUSIVE MODE;";
	private final static String UPSERT_USER_QUERY = "SELECT rbs.merge_users(?,?)";
	public final static String DATE_FORMAT = "DD/MM/YY HH24:MI";
	private static final Logger log = LoggerFactory.getLogger(BookingServiceSqlImpl.class);
	private static DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss", Locale.ENGLISH)
			.withZone(ZoneOffset.UTC);

	public BookingServiceSqlImpl() {
		super("rbs", "booking");
	}

	static String toSQLTimestamp(Long timestamp) {
		return timestamp == null ? null : sqlFormatter.format(Instant.ofEpochSecond(timestamp));
	}

	@Override
	public void createBooking(final String resourceId, final Booking booking, final UserInfos user, final Handler<Either<String, JsonArray>> handler) {
		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		// Upsert current user
		statementsBuilder.prepared(UPSERT_USER_QUERY, new JsonArray().add(user.getUserId()).add(user.getUsername()));

		// Lock query to avoid race condition
		statementsBuilder.raw(LOCK_BOOKING_QUERY);

		booking.getSlots().forEach(slot-> {
			JsonObject statement = getCreationBooking(resourceId, booking, slot, user);
			statementsBuilder.prepared(statement.getString("query"), statement.getJsonArray("values"));
		});

		// Send queries to eventbus
		Sql.getInstance().transaction(statementsBuilder.build(), event -> {
			Either<String, JsonArray> result = SqlResult.validResults(event);
			if (result.isLeft()) {
				String message = "[RBS@BookingServiceSqlImpl::createBooking] An error has occured during statements transaction";
				log.error(message, result.left().getValue());
				handler.handle(new Either.Left<>(result.left().getValue()));
			} else {
				JsonArray statementsResults = result.right().getValue();
				// Removing the first (upsert current user) and second statements (lock query)
				statementsResults.remove(0);
				statementsResults.remove(0);

				JsonArray bookings = new JsonArray();
				// Looping our statementsResult as each JsonObject is inside a JsonArray then we clear data by creating
				// new JsonArray
				for (int i = 0; i < statementsResults.size(); i++) {
					if (Boolean.FALSE.equals(statementsResults.getJsonArray(i).isEmpty())) {
						bookings.add(statementsResults.getJsonArray(i).getJsonObject(0));
					}
				}
				handler.handle(new Either.Right<>(bookings));
			}
		});


	}

	private JsonObject getCreationBooking(final String resourceId, final Booking booking, final Slot slot, final UserInfos user) {


		Object rId = parseId(resourceId);

		// Insert query
		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		query.append("INSERT INTO rbs.booking")
				.append("(resource_id, owner, booking_reason, quantity, status, start_date, end_date)")
				.append(" SELECT  ?, ?, ?, ?,");
		values.add(rId).add(user.getUserId()).add(booking.getBookingReason()).add(booking.getBookingQuantity());

		// If validation is activated, the booking is created with status "created".
		// Otherwise, it is created with status "validated".
		// TODO V2 : la reservation doit etre automatiquement validee si le demandeur
		// est valideur
		query.append(" (SELECT CASE WHEN (r.validation IS true) THEN ? ELSE ? END")
				.append(" FROM rbs.resource AS r")
				.append(" WHERE r.id = ?),");
		values.add(CREATED.status()).add(VALIDATED.status()).add(rId);

		// Unix timestamps are converted into postgresql timestamps.
		query.append(" ?, ?");
		values.add(toSQLTimestamp(slot.getStartUTC()))
				.add(toSQLTimestamp(slot.getEndUTC()));

		// Returning result
		query.append(" RETURNING id, quantity, status, to_char(start_date, '").append(DATE_FORMAT)
				.append("') AS start_date, to_char(end_date, '").append(DATE_FORMAT).append("') AS end_date");

		return new JsonObject().put("query", query).put("values", values);
	}

	/**
	 * @throws IllegalArgumentException, IndexOutOfBoundsException
	 */
	@Override
	public void createPeriodicBooking(final String resourceId, Booking booking, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler) {

		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		statementsBuilder.prepared(UPSERT_USER_QUERY,
				new fr.wseduc.webutils.collections.JsonArray().add(user.getUserId()).add(user.getUsername()));


		Slots slots = booking.getSlots() ;
		// create a periodic reservation dedicated to each slot
		for (Slot slot: slots) {
            StringBuilder query = new StringBuilder();
            JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

            Object rId = parseId(resourceId);
            final long endDate = booking.getPeriodicEndDateAsUTCSeconds();
            final int occurrences = booking.getOccurrences(0);

            // 1. WITH clause to insert the "parent" booking (i.e. the periodic booking)
            query.append("WITH parent_booking AS (")
                    .append(" INSERT INTO rbs.booking (resource_id, owner, booking_reason, quantity, start_date, end_date,")
                    .append(" is_periodic, periodicity, occurrences, days)")
                    .append(" VALUES (?, ?, ?, ?, ?,");
            values.add(rId)
                    .add(user.getUserId())
                    .add(booking.getBookingReason())
					.add(booking.getBookingQuantity())
                    .add(toSQLTimestamp(slot.getStartUTC()));

            query.append(" ?,");
            values.add(endDate > 0L ? toSQLTimestamp(endDate) : null); // the null value will be replaced by the last slot's
            // end date, which
            // will be computed
            final int endDateIndex = values.size() - 1;

            query.append(" ?, ?, ?,");
            values.add(true).add(booking.getPeriodicity())
                    .add(occurrences != 0 ? occurrences : null);

            // NB : Bit string type cannot be used in a preparedStatement
            query.append(" B'").append(booking.getSelectedDaysBitString()).append("') RETURNING id) ");

            // 2. Insert clause for the child bookings
            final long lastSlotEndDate = appendInsertChildBookingsQuery(query, values, rId, booking,slot, user, null);

            // Update end_date value in JsonArray values
            if (endDate <= 0L) {
                values = getValuesWithProperEndDate(values, toSQLTimestamp(lastSlotEndDate), endDateIndex);
            }

            statementsBuilder.prepared(query.toString(), values);
        }
		Sql.getInstance().transaction(statementsBuilder.build(), validResultHandler(1, handler));

	}

	/**
	 * Appends the insert child bookings query to parameter query, and the
	 * associated values to parameter values
	 *
	 * @param bookingId : used when updating a periodic booking
	 * @return Unix timestamp of the last child booking's end date
	 */
	private long appendInsertChildBookingsQuery(StringBuilder query, JsonArray values, final Object resourceId,final Booking booking,
			final Slot slot, final UserInfos user, final Object bookingId) {

		final boolean isUpdate = (bookingId != null);
		final long firstSlotStartDate = slot.getStartUTC();
		final long firstSlotEndDate = slot.getEndUTC();
		final String bookingReason = booking.getBookingReason();
		long lastSlotEndDateUTC = firstSlotEndDate;

		// 1. INSERT clause for the first child booking
		query.append(
				" INSERT INTO rbs.booking (resource_id, owner, booking_reason, quantity, start_date, end_date, parent_booking_id, status, refusal_reason)")
				.append(" VALUES(?, ?, ?, ?, ?, ?,");
		values.add(resourceId)
				.add(user.getUserId())
				.add(bookingReason)
				.add(booking.getBookingQuantity())
				.add(toSQLTimestamp(firstSlotStartDate))
				.add(toSQLTimestamp(firstSlotEndDate));

		if (isUpdate) { // Update of a periodic booking
			query.append(" ?,");
			values.add(bookingId);
		} else { // Creation of a periodic booking
			query.append("(select id from parent_booking),");
		}

		// Subquery to insert proper status : created if validation is activated. Validated otherwise

		query.append(" (SELECT CASE WHEN (r.validation IS true) THEN ? ELSE ? END")
				.append(" FROM rbs.resource AS r")
				.append(" WHERE r.id = ?), ?)");
		values.add(CREATED.status()).add(VALIDATED.status()).add(resourceId).addNull();

		// 2. Additional VALUES to insert the other child bookings
		int nbOccurences = booking.getOccurrences(-1);

		if (nbOccurences == -1) {
			nbOccurences = booking.countOccurrences(slot);
		}

		SlotIterable it = new SlotIterable(booking,slot);
		for (Slot slotIt : it) {
			query.append(", (?, ?, ?, ?, ?, ?,");
			values.add(resourceId).add(user.getUserId()).add(bookingReason).add(booking.getBookingQuantity())
					.add(toSQLTimestamp(slotIt.getStartUTC())).add(toSQLTimestamp(slotIt.getEndUTC()));

			if (isUpdate) { // Update of a periodic booking
				query.append(" ?,");
				values.add(bookingId);
			} else { // Creation of a periodic booking
				query.append("(select id from parent_booking),");
			}

			query.append(" (SELECT CASE WHEN (r.validation IS true) THEN ? ELSE ? END")
					.append(" FROM rbs.resource AS r")
					.append(" WHERE r.id = ?), ?)");
			values.add(CREATED.status()).add(VALIDATED.status()).add(resourceId).addNull();
			//
			lastSlotEndDateUTC = lastSlotEndDateUTC < slotIt.getEndUTC() ? slotIt.getEndUTC() : lastSlotEndDateUTC;
		}
		query.append(" RETURNING id, status");

		return lastSlotEndDateUTC;
	}

	/**
	 * @param values
	 * @param lastSlotEndDate
	 * @param endDateIndex
	 * @return new fr.wseduc.webutils.collections.JsonArray, with proper end date
	 */
	private JsonArray getValuesWithProperEndDate(JsonArray values, String lastSlotEndDate, int endDateIndex) {
		JsonArray newValues = new fr.wseduc.webutils.collections.JsonArray();
		int i = 0;
		for (Object object : values) {
			if (i == endDateIndex) {
				newValues.add(lastSlotEndDate);
			} else {
				newValues.add(object);
			}
			i++;
		}

		return newValues;
	}

	@Override
	public void updateBooking(final String resourceId, final Booking booking,
			final Handler<Either<String, JsonObject>> handler) {

		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		Object rId = parseId(resourceId);
		Object bId = parseId(booking.getBookingId());
		Slot slot = booking.getSlots().get(0);
		// Lock query to avoid race condition
		statementsBuilder.raw(LOCK_BOOKING_QUERY);

		// Update query
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		StringBuilder sb = new StringBuilder();


		StringBuilder query = new StringBuilder();
		query.append("UPDATE rbs.booking").append(" SET ").append(sb).append("modified = NOW()")
				.append(", booking_reason = ?")
				.append(", quantity = ?")
				.append(", start_date = ?")
				.append(", end_date = ?" );
		values.add(booking.getBookingReason());
		values.add(booking.getBookingQuantity());
		values.add(toSQLTimestamp(slot.getStartUTC()));
		values.add(toSQLTimestamp(slot.getEndUTC()));

		// If validation is activated, the booking status is updated to "created" (the
		// booking must be validated anew).
		// Otherwise, it is updated to "validated".
		query.append(", status = (SELECT CASE ").append(" WHEN (t.validation IS true) THEN ?").append(" ELSE ?")
				.append(" END").append(" FROM rbs.resource_type AS t")
				.append(" INNER JOIN rbs.resource AS r ON r.type_id = t.id")
				.append(" INNER JOIN rbs.booking AS b on b.resource_id = r.id").append(" WHERE b.id = ?)");
		values.add(CREATED.status()).add(VALIDATED.status()).add(bId);

		query.append(" WHERE id = ?").append(" AND resource_id = ? RETURNING *");
		values.add(bId).add(rId);

		statementsBuilder.prepared(query.toString(), values);

		// Send queries to eventbus
		Sql.getInstance().transaction(statementsBuilder.build(), validUniqueResultHandler(1, handler));
	}

	@Override
	public void updatePeriodicBooking(final String resourceId, final Booking booking, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler) {

		final long endDate = booking.getPeriodicEndDateAsUTCSeconds();
		final int occurrences = booking.getOccurrences(0);
		final Slot slot = booking.getSlots().get(0);
		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		Object rId = parseId(resourceId);
		Object bId = parseId(booking.getBookingId());

		// 1. Lock query to avoid race condition
		statementsBuilder.raw(LOCK_BOOKING_QUERY);

		// 2. Update parent booking
		StringBuilder parentQuery = new StringBuilder();
		JsonArray parentValues = new fr.wseduc.webutils.collections.JsonArray();
		parentQuery.append("UPDATE rbs.booking").append(" SET booking_reason = ?, quantity = ?, start_date = ?, end_date = ?,");
		parentValues.add(booking.getBookingReason()).add(booking.getBookingQuantity()).add(toSQLTimestamp(slot.getStartUTC()))
				.add(endDate > 0L ? toSQLTimestamp(endDate) : null); // the null value will be replaced by the last slot's end date
		final int endDateIndex = parentValues.size() - 1;

		parentQuery.append(" periodicity = ?, occurrences = ?, modified = NOW(),");
		parentValues.add(booking.getPeriodicity()).add(occurrences != 0 ? occurrences : null);

		parentQuery.append(" days = B'").append(booking.getSelectedDaysBitString()).append("' WHERE resource_id = ?")
				.append(" AND id = ?").append(" AND is_periodic = ?");
		parentValues.add(rId).add(bId).add(true);

		// 3. Delete child bookings
		String deleteQuery = "DELETE FROM rbs.booking WHERE parent_booking_id = ?";

		// 4. Create new child bookings
		StringBuilder insertQuery = new StringBuilder();
		JsonArray insertValues = new fr.wseduc.webutils.collections.JsonArray();
		final long lastSlotEndDate = appendInsertChildBookingsQuery(insertQuery, insertValues, rId, booking,booking.getSlots().get(0), user, bId);

		// Update end_date value in JsonArray parentValues
		if (endDate <= 0L) {
			parentValues = getValuesWithProperEndDate(parentValues, toSQLTimestamp(lastSlotEndDate), endDateIndex);
		}

		// Add queries to SqlStatementsBuilder
		statementsBuilder.prepared(parentQuery.toString(), parentValues);
		statementsBuilder.prepared(deleteQuery, new fr.wseduc.webutils.collections.JsonArray().add(bId));
		statementsBuilder.prepared(insertQuery.toString(), insertValues);

		// Send queries to event bus
		Sql.getInstance().transaction(statementsBuilder.build(), validResultHandler(3, handler));
	}

	@Override
	public void deleteFuturePeriodicBooking(String bookingId, Date startDate, Handler<Either<String, JsonArray>> handler) {
		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		Object bId = parseId(bookingId);

		// 1. Lock query to avoid race condition
		statementsBuilder.raw(LOCK_BOOKING_QUERY);
		// 2. Delete child bookings
		StringBuilder deleteQuery = new StringBuilder();
		JsonArray deleteValues = new fr.wseduc.webutils.collections.JsonArray();
		deleteQuery.append("DELETE FROM rbs.booking")
				.append(" WHERE parent_booking_id = ?")
				.append(" AND start_date >= to_timestamp(?)");
		deleteValues.add(bId);
		deleteValues.add(startDate.getTime() / 1000);


		// 3. Update parent booking with last end date slot
		StringBuilder parentQuery = new StringBuilder();
		JsonArray parentValues = new fr.wseduc.webutils.collections.JsonArray();
		parentQuery.append("UPDATE rbs.booking")
				.append(" SET end_date = (SELECT CASE WHEN MAX(end_date) IS NULL THEN (SELECT end_date FROM rbs.booking WHERE id = ?) ELSE MAX(end_date) END FROM rbs.booking WHERE parent_booking_id = ?)")
				.append(" , modified = NOW()")
				.append(" WHERE id = ?");
		parentValues.add(bId);
		parentValues.add(bId);
		parentValues.add(bId);
		// 4. Purge parent bookings
		StringBuilder deleteParentQuery = new StringBuilder();
		deleteParentQuery.append("DELETE FROM rbs.booking B1")
				.append(" WHERE B1.is_periodic = true")
				.append(" AND NOT EXISTS (SELECT * FROM rbs.booking B2 WHERE B2.parent_booking_id = B1.id)");

		// Add queries to SqlStatementsBuilder
		statementsBuilder.prepared(deleteQuery.toString(), deleteValues);
		statementsBuilder.prepared(parentQuery.toString(), parentValues);
		statementsBuilder.raw(deleteParentQuery.toString());

		// Send queries to event bus
		Sql.getInstance().transaction(statementsBuilder.build(), validResultHandler(2, handler));
	}

	@Override
	public void getBookingsForExport(final ExportRequest exportRequest, final Handler<Either<String, List<ExportBooking>>> handler) {
		final String startDate = exportRequest.getStartDate();
		final String endDate = exportRequest.getEndDate();
		final UserInfos user = exportRequest.getUserInfos();
		final List<String> groupsAndUserIds = getUserIdAndGroupIds(user);

		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		// find all booking without periodic booking parents
		query.append("SELECT b.").append(ExportBooking.BOOKING_ID);
		query.append(", b.").append(ExportBooking.BOOKING_OWNER_ID);
		query.append(", b.").append(ExportBooking.BOOKING_START_DATE);
		query.append(", b.").append(ExportBooking.BOOKING_END_DATE);
		query.append(", b.").append(ExportBooking.BOOKING_REASON);
		query.append(", b.").append(ExportBooking.BOOKING_MODERATOR_ID);
		query.append(", b.").append(ExportBooking.RESOURCE_ID);
		query.append(", b.").append(ExportBooking.QUANTITY);
		query.append(", t.").append(ExportBooking.SCHOOL_ID);
		query.append(", t.id AS ").append(ExportBooking.RESOURCE_TYPE_ID);
		query.append(", t.color AS ").append(ExportBooking.RESOURCE_TYPE_COLOR);
		query.append(", r.name AS ").append(ExportBooking.RESOURCE_NAME);
		query.append(", r.color AS ").append(ExportBooking.RESOURCE_COLOR);
		query.append(", u.username AS ").append(ExportBooking.BOOKING_OWNER_NAME);
		query.append(", m.username AS ").append(ExportBooking.BOOKING_MODERATOR_NAME);
		query.append(" FROM rbs.booking AS b")
				.append(" LEFT JOIN rbs.resource AS r ON r.id = b.resource_id")
				.append(" LEFT JOIN rbs.resource_type AS t ON r.type_id = t.id")
				.append(" LEFT JOIN rbs.resource_type_shares AS rs ON rs.resource_id = r.type_id")
				.append(" LEFT JOIN rbs.users AS u ON u.id = b.owner")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id")
				.append(" WHERE b.status = ?") // only validated
				.append(" AND ((b.is_periodic=FALSE AND b.start_date::date >= ?::date AND b.end_date::date < (?::date + interval '1 day')) ")
				.append(" OR (b.occurrences IS NULL AND b.start_date::date >= ?::date AND b.end_date::date < (?::date + interval '1 day'))")
				// this part is for reservations across 2 weeks
				.append(" OR (b.is_periodic=FALSE AND b.start_date::date <= ?::date AND b.end_date::date > (?::date + interval '1 day'))")
				.append(" OR (b.is_periodic=FALSE AND b.start_date::date >= ?::date AND b.start_date::date < ?::date)")
				.append(" OR (b.is_periodic=FALSE AND b.end_date::date >= (?::date + interval '1 day') AND b.end_date::date < (?::date + interval '1 day')))")
				.append(" AND (rs.member_id IN ")
				//
				.append(Sql.listPrepared(groupsAndUserIds.toArray()))
				.append(" OR t.owner = ?");
		values.add(VALIDATED.status());
		values.add(startDate);
		values.add(endDate);
		values.add(startDate);
		values.add(endDate);
		values.add(startDate);
		values.add(endDate);
		values.add(startDate);
		values.add(endDate);
		values.add(startDate);
		values.add(endDate);

		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}
		values.add(user.getUserId());

		// A local administrator of a given school can see all resources of the school's types, even if he is not owner or manager of these types or resources
		List<String> scope = getLocalAdminScope(user);
		if (scope != null && !scope.isEmpty()) {
			query.append(" OR t.school_id IN ").append(Sql.listPrepared(scope.toArray()));
			for (String schoolId : scope) {
				values.add(schoolId);
			}
		}

		// Add user restriction on resources if any
		final List<Long> resourceIds = exportRequest.getResourceIds();
		if (!resourceIds.isEmpty()) {
			query.append(") AND (r.id IN ")
					.append(Sql.listPrepared(resourceIds.toArray()));
			for (Long resourceId : resourceIds) {
				values.add(resourceId);
			}
		}

		query.append(") GROUP BY t.id, b.id, r.id, u.username, m.username ORDER BY b.start_date, b.end_date");

		Sql.getInstance().prepared(query.toString(), values, event -> {
			final Either<String, JsonArray> ei = validResult(event);
			if (ei.isRight()) {
				final List<ExportBooking> exportBookings = new ArrayList<>();
				final JsonArray jsonBookingResult = ei.right().getValue();
				for (final Object o : jsonBookingResult) {
					if (!(o instanceof JsonObject)) {
						continue;
					} else {
						// keep-it without understanding business rule here...
						final JsonObject jo = (JsonObject) o;
						Number parentBookingId = jo.getInteger("parent_booking_id");
						if (parentBookingId != null) {
							exportBookings.add(new ExportBooking(jo));
						} else if (jo.getInteger("occurrences") == null) {
							if (isSearchOverlapBookingDates(jo, startDate, endDate)) {
								exportBookings.add(new ExportBooking(jo));
							}
						} else {
							exportBookings.add(new ExportBooking(jo));
						}
					}
				}
				handler.handle(new Right<>(exportBookings));
			} else {
				String value = ei.left().getValue();
				handler.handle(new Either.Left<>(value));
			}
		});
	}

	private boolean searchDatesOverlapBookingDates(Date searchStartDate, Date searchEndDate, Date bookingStartDate, Date bookingEndDate) {
		return DateUtils.isBetween(searchStartDate, bookingStartDate, bookingEndDate) ||
				DateUtils.isBetween(searchEndDate, bookingStartDate, bookingEndDate) ||
				(DateUtils.isBetween(bookingStartDate, searchStartDate, searchEndDate) &&
						DateUtils.isBetween(bookingEndDate, searchStartDate, searchEndDate));
	}


	@Override
	public void processBooking(final String resourceId, final String bookingId, final int newStatus,
			final JsonObject data, final UserInfos user, final Handler<Either<String, JsonArray>> handler) {

		SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
		Object rId = parseId(resourceId);
		Object bId = parseId(bookingId);

		// 1. Upsert current user
		statementsBuilder.prepared(UPSERT_USER_QUERY, new JsonArray().add(user.getUserId()).add(user.getUsername()));

		// 2. Lock query to avoid race condition
		statementsBuilder.raw(LOCK_BOOKING_QUERY);

		// 3. Query to validate or refuse booking
		StringBuilder sb = new StringBuilder();
		JsonArray processValues = new JsonArray();
		for (String attr : data.fieldNames()) {
			sb.append(attr).append(" = ?, ");
			processValues.add(data.getValue(attr));
		}

		StringBuilder processQuery = new StringBuilder();
		processQuery.append("UPDATE rbs.booking").append(" SET ").append(sb).append("modified = NOW() ")
				.append("WHERE id = ?").append(" AND resource_id = ? ");
		processValues.add(bId).add(rId);

		StringBuilder returningClause = new StringBuilder(" RETURNING id, status, owner, ")
				.append(" to_char(start_date, '").append(DATE_FORMAT).append("') AS start_date,")
				.append(" to_char(end_date, '").append(DATE_FORMAT).append("') AS end_date");

		processQuery.append(returningClause);
		statementsBuilder.prepared(processQuery.toString(), processValues);

		// Send queries to event bus
		Sql.getInstance().transaction(statementsBuilder.build(), validResultsHandler(handler));
	}

	@Override
	public void listUserBookings(final UserInfos user, final Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name")
				.append(" FROM rbs.booking AS b").append(" INNER JOIN rbs.users AS u ON b.owner = u.id")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id").append(" WHERE b.owner = ?")
				.append(" ORDER BY b.start_date, b.end_date");

		JsonArray values = new JsonArray().add(user.getUserId());

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void listAllBookings(final UserInfos user, final List<String> groupsAndUserIds,
			final Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		query.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name")
				.append(" FROM rbs.booking AS b").append(" LEFT JOIN rbs.resource AS r ON r.id = b.resource_id")
				.append(" LEFT JOIN rbs.resource_type AS t ON r.type_id = t.id")
				.append(" LEFT JOIN rbs.resource_type_shares AS rs ON rs.resource_id = r.type_id")
				.append(" LEFT JOIN rbs.users AS u ON u.id = b.owner")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id").append(" WHERE rs.member_id IN ")
				.append(Sql.listPrepared(groupsAndUserIds.toArray())).append(" OR t.owner = ?");
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}
		values.add(user.getUserId());

		// A local administrator of a given school can see all resources of the school's
		// types, even if he is not owner or manager of these types or resources
		List<String> scope = getLocalAdminScope(user);
		if (scope != null && !scope.isEmpty()) {
			query.append(" OR t.school_id IN ").append(Sql.listPrepared(scope.toArray()));
			for (String schoolId : scope) {
				values.add(schoolId);
			}
		}

		query.append(" GROUP BY b.id, u.username, m.username ORDER BY b.start_date, b.end_date");

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	private String listBaseQuery(boolean withShares, String afterWhere){
		final StringBuilder baseQuery = new StringBuilder();
		baseQuery.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name FROM rbs.booking AS b ")
				.append(" LEFT JOIN rbs.resource AS r ON r.id = b.resource_id ")
				.append(" LEFT JOIN rbs.resource_type AS t ON r.type_id = t.id ")
				.append(withShares ? " LEFT JOIN rbs.resource_type_shares AS rs ON rs.resource_id = r.type_id " : "")
				.append(" LEFT JOIN rbs.users AS u ON u.id = b.owner ")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id ")
				.append(" WHERE ((b.is_periodic=FALSE AND b.start_date::date >= ?::date AND b.end_date::date < ?::date) ")
				.append(" OR (b.occurrences IS NULL AND b.start_date::date >= ?::date AND b.end_date::date < ?::date) ")
				// this part is for reservations across 2 weeks
				.append(" OR (b.is_periodic=FALSE AND b.start_date::date <= ?::date AND b.end_date::date > ?::date) ")
				.append(" OR (b.is_periodic=FALSE AND b.start_date::date >= ?::date AND b.start_date::date < ?::date) ")
				.append(" OR (b.is_periodic=FALSE AND b.end_date::date >= ?::date AND b.end_date::date < ?::date)) ")
				.append(afterWhere)
				.append(" GROUP BY b.id, u.username, m.username ");
		return baseQuery.toString();
	}

	@Override
	public void listAllBookingsByDates(final UserInfos user, final List<String> groupsAndUserIds,
			final String startDate, final String endDate, final Handler<Either<String, JsonArray>> handler) {
		//=== base values
		final JsonArray baseValue = new JsonArray();
		baseValue.add(startDate);
		baseValue.add(endDate);
		baseValue.add(startDate);
		baseValue.add(endDate);
		baseValue.add(startDate);
		baseValue.add(endDate);
		baseValue.add(startDate);
		baseValue.add(endDate);
		baseValue.add(startDate);
		baseValue.add(endDate);
		// A local administrator of a given school can see all resources of the school's
		// types, even if he is not owner or manager of these types or resources
		final List<String> scope = getLocalAdminScope(user);
		final boolean isLocalAdmin = scope != null && !scope.isEmpty();
		//===query find all booking without periodic booking
		final StringBuilder query = new StringBuilder();
		query.append("SELECT * FROM ( ");
		query.append(listBaseQuery(true, " AND rs.member_id IN " + Sql.listPrepared(groupsAndUserIds.toArray())));
		query.append(" UNION ").append(listBaseQuery(false, " AND t.owner = ? "));
		if(isLocalAdmin){
			query.append(" UNION ").append(listBaseQuery(false," AND t.school_id IN " + Sql.listPrepared(scope.toArray())));
		}
		query.append(" ) AS tmp ORDER BY tmp.start_date, tmp.end_date ");
		//===values
		final JsonArray values = new JsonArray();
		// by member
		values.addAll(baseValue);
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}
		// by owner
		values.addAll(baseValue).add(user.getUserId());
		// by school
		if (isLocalAdmin) {
			values.addAll(baseValue);
			for (String schoolId : scope) {
				values.add(schoolId);
			}
		}

		Sql.getInstance().prepared(query.toString(), values, new Handler<Message<JsonObject>>() {
			@Override
			public void handle(Message<JsonObject> event) {
				final Either<String, JsonArray> ei = validResult(event);
				if (ei.isRight()) {
					final Set<Number> setIdsPeriodicBooking = new HashSet<Number>();
					final JsonArray jsonBookingResult = ei.right().getValue();
					final JsonArray jsonAllBookingResult = new fr.wseduc.webutils.collections.JsonArray();
					for (final Object o : jsonBookingResult) {
						if (!(o instanceof JsonObject)) {
							continue;
						} else {
							final JsonObject jo = (JsonObject) o;
							if (jo.getLong("parent_booking_id") != null) {
								setIdsPeriodicBooking.add(jo.getLong("parent_booking_id"));
								jsonAllBookingResult.add(jo);
							} else if (jo.getValue("occurrences") == null) {
								try {
									final Date currentStartDate = DateUtils
											.parseTimestampWithoutTimezone(jo.getString("start_date"));
									final Date currentEndDate = DateUtils
											.parseTimestampWithoutTimezone(jo.getString("end_date"));
									final List<String> startSearched = StringUtils.split(startDate, "-");
									final Date dateStartSearched = DateUtils.create(
											Integer.parseInt(startSearched.get(0)),
											Integer.parseInt(startSearched.get(1)) - 1,
											Integer.parseInt(startSearched.get(2)));
									final List<String> endSearched = StringUtils.split(endDate, "-");
									final Date dateEndSearched = DateUtils.create(Integer.parseInt(endSearched.get(0)),
											Integer.parseInt(endSearched.get(1)) - 1,
											Integer.parseInt(endSearched.get(2)));
									if (DateUtils.isBetween(dateStartSearched, currentStartDate, currentEndDate)
											|| DateUtils.isBetween(dateEndSearched, currentStartDate, currentEndDate)
											|| (DateUtils.isBetween(currentStartDate, dateStartSearched,
													dateEndSearched)
													&& DateUtils.isBetween(currentEndDate, dateStartSearched,
															dateEndSearched))) {
										jsonAllBookingResult.add(jo);
									}
								} catch (ParseException e) {
									log.error("Can't parse date form RBS DB", e);
								}
							} else {
								jsonAllBookingResult.add(jo);
							}
						}
					}
					if (!setIdsPeriodicBooking.isEmpty()) {
						// find periodicBooking according to ids of sons
						final StringBuilder queryPeriodicBooking = new StringBuilder();
						queryPeriodicBooking
								.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name")
								.append(" FROM rbs.booking AS b").append(" LEFT JOIN rbs.users AS u ON u.id = b.owner")
								.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id").append(" WHERE b.id IN ")
								.append(Sql.listPrepared(setIdsPeriodicBooking.toArray()));
						final JsonArray values = new fr.wseduc.webutils.collections.JsonArray(
								new ArrayList<>(setIdsPeriodicBooking));

						Sql.getInstance().prepared(queryPeriodicBooking.toString(), values,
								event1 -> {
									final Either<String, JsonArray> ei1 = validResult(event1);
									if (ei1.isRight()) {
										final JsonArray jsonBookingPeriodicResult = ei1.right().getValue();

										if (jsonBookingPeriodicResult.size() != 0) {
											for (final Object o : jsonBookingPeriodicResult) {
												jsonAllBookingResult.add(o);
											}
										}
										handler.handle(new Right<>(jsonAllBookingResult));
									} else {
										handler.handle(ei1);
									}
								});
					} else {
						handler.handle(ei);
					}
				} else {
					handler.handle(ei);
				}
			}
		});
	}

	private boolean isSearchOverlapBookingDates(JsonObject booking, String startDate, String endDate) {
		boolean overlapBookingDates = false;
		try {
			final Date currentStartDate = DateUtils.parseTimestampWithoutTimezone(booking.getString("start_date"));
			final Date currentEndDate = DateUtils.parseTimestampWithoutTimezone(booking.getString("end_date"));
			final List<String> startSearched = StringUtils.split(startDate, "-");
			final Date dateStartSearched = DateUtils.create(Integer.parseInt(startSearched.get(0)),
					Integer.parseInt(startSearched.get(1)) - 1, Integer.parseInt(startSearched.get(2)));
			final List<String> endSearched = StringUtils.split(endDate, "-");
			final Date dateEndSearched = DateUtils.create(Integer.parseInt(endSearched.get(0)),
					Integer.parseInt(endSearched.get(1)) - 1, Integer.parseInt(endSearched.get(2)) + 1);
			overlapBookingDates = searchDatesOverlapBookingDates(dateStartSearched, dateEndSearched, currentStartDate, currentEndDate);

		} catch (ParseException e) {
			log.error("Can't parse date form RBS DB", e);
		}
		return overlapBookingDates;
	}

	@Override
	public void listFullSlotsBooking(final String bookingId, final Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		// find all booking without periodic booking
		query.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name")
				.append(" FROM rbs.booking AS b").append(" LEFT JOIN rbs.users AS u ON u.id = b.owner")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id").append(" WHERE b.parent_booking_id=?");
		values.add(parseId(bookingId));

		query.append(" ORDER BY b.start_date, b.end_date");

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void listBookingsByResource(final String resourceId, final Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT b.*, u.username AS owner_name, m.username AS moderator_name")
				.append(" FROM rbs.booking AS b").append(" INNER JOIN rbs.users AS u ON b.owner = u.id")
				.append(" LEFT JOIN rbs.users AS m on b.moderator_id = m.id").append(" WHERE b.resource_id = ?")
				.append(" ORDER BY b.start_date, b.end_date");

		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(parseId(resourceId));

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void listUnprocessedBookings(final List<String> groupsAndUserIds, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler) {

		// Query
		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		query.append("SELECT DISTINCT b.*, u.username AS owner_name").append(" FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.users AS u ON b.owner = u.id")
				.append(" INNER JOIN rbs.resource AS r ON b.resource_id = r.id")
				.append(" INNER JOIN rbs.resource_type AS t ON t.id = r.type_id")
				.append(" LEFT JOIN rbs.resource_type_shares AS ts ON t.id = ts.resource_id")
				.append(" LEFT JOIN rbs.resource_shares AS rs ON r.id = rs.resource_id");

		// Get child bookings with status "created" and parent bookings that have at
		// least one child booking with status "created"
		query.append(" WHERE (b.status = ?").append(" OR (b.is_periodic = ? AND EXISTS(")
				.append(" SELECT 1 FROM rbs.booking AS c").append(" WHERE c.parent_booking_id = b.id")
				.append(" AND c.status = ?)))");
		values.add(CREATED.status()).add(true).add(CREATED.status());

		// Restrict results to bookings visible by current user
		query.append(" AND (ts.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray()));
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}

		query.append(" OR rs.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray()));
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}

		query.append(" OR t.owner = ?");
		values.add(user.getUserId());

		query.append(" OR r.owner = ?)");
		values.add(user.getUserId());

		query.append(" ORDER BY b.start_date, b.end_date;");

		// Send query to event bus
		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	/**
	 * Get ids of groups and users that are moderators. user.getUserId() is
	 * excluded, because the booking owner does not need to be notified.
	 */
	@Override
	public void getModeratorsIds(final String bookingId, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler) {

		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		Object bId = parseId(bookingId);

		// Resource owner is a moderator
		query.append("SELECT DISTINCT m.user_id, m.group_id FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.resource AS r on r.id = b.resource_id")
				.append(" INNER JOIN rbs.members AS m on r.owner = m.id").append(" WHERE b.id = ? AND r.owner != ?");
		values.add(bId).add(user.getUserId());

		// Type owner is a moderator
		query.append(" UNION").append(" SELECT DISTINCT m.user_id, m.group_id FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.resource AS r on r.id = b.resource_id")
				.append(" INNER JOIN rbs.resource_type AS t on t.id = r.type_id")
				.append(" INNER JOIN rbs.members AS m on t.owner = m.id ").append(" WHERE b.id = ? AND t.owner != ?");
		values.add(bId).add(user.getUserId());

		// Users with resource right "processBooking" are moderators
		query.append(" UNION").append(" SELECT DISTINCT m.user_id, m.group_id FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.resource AS r on r.id = b.resource_id")
				.append(" INNER JOIN rbs.resource_shares AS rs ON r.id = rs.resource_id")
				.append(" INNER JOIN rbs.members AS m on rs.member_id = m.id")
				.append(" WHERE b.id = ? AND rs.member_id != ?")
				.append(" AND rs.action = 'net-atos-entng-rbs-controllers-BookingController|processBooking'");
		values.add(bId).add(user.getUserId());

		query.append(" UNION").append(" SELECT DISTINCT m.user_id, m.group_id FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.resource AS r on r.id = b.resource_id")
				.append(" INNER JOIN rbs.resource_type AS t on t.id = r.type_id")
				.append(" INNER JOIN rbs.resource_type_shares AS ts ON t.id = ts.resource_id")
				.append(" INNER JOIN rbs.members AS m on ts.member_id = m.id")
				.append(" WHERE b.id = ? AND ts.member_id != ?")
				.append(" AND ts.action = 'net-atos-entng-rbs-controllers-BookingController|processBooking'");
		values.add(bId).add(user.getUserId());

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void getResourceName(final String bookingId, final Handler<Either<String, JsonObject>> handler) {
		this.getResourceName(bookingId, handler, false);
	}

	@Override
	public void getBookingWithResourceName(final String bookingId, final Handler<Either<String, JsonObject>> handler) {
		this.getResourceName(bookingId, handler, true);
	}

	private void getResourceName(final String bookingId, final Handler<Either<String, JsonObject>> handler,
			boolean withBooking) {
		StringBuilder query = new StringBuilder("SELECT r.name AS resource_name");
		if (withBooking) {
			query.append(", b.owner, b.is_periodic,").append("to_char(b.start_date, '").append(DATE_FORMAT)
					.append("') as start_date,").append("to_char(b.end_date, '").append(DATE_FORMAT)
					.append("') as end_date").append(", b.parent_booking_id");
		}
		query.append(" FROM rbs.resource AS r").append(" INNER JOIN rbs.booking AS b on r.id = b.resource_id")
				.append(" WHERE b.id = ?");

		Sql.getInstance().prepared(query.toString(),
				new fr.wseduc.webutils.collections.JsonArray().add(parseId(bookingId)),
				validUniqueResultHandler(handler));
	}

	@Override
	public void getParentBooking(final String bookingId, final Handler<Either<String, JsonObject>> handler) {
		StringBuilder query = new StringBuilder();

		query.append("SELECT DISTINCT p.id, r.name as resource_name, ").append("to_char(p.start_date, '")
				.append(DATE_FORMAT).append("') as start_date,").append("to_char(p.end_date, '").append(DATE_FORMAT)
				.append("') as end_date").append(" FROM rbs.booking AS b")
				.append(" INNER JOIN rbs.booking AS p ON b.parent_booking_id = p.id")
				.append(" INNER JOIN rbs.resource AS r ON b.resource_id = r.id").append(" WHERE b.id = ?");

		Sql.getInstance().prepared(query.toString(),
				new fr.wseduc.webutils.collections.JsonArray().add(parseId(bookingId)),
				validUniqueResultHandler(handler));
	}

	public void getBooking(final String bookingId, final Handler<Either<String, JsonObject>> handler) {
		this.retrieve(bookingId, handler);
	}

} public interface ResourceService extends CrudService {

	/**
	 * Create a resource
	 *
	 * @param resource : id of current resource
	 * @param user     : information of current user logged
	 * @param handler  : handler which contains the response
	 */
	void createResource(JsonObject resource, UserInfos user, Handler<Either<String, JsonObject>> handler);

	/**
	 * Get all resources
	 *
	 * @param groupsAndUserIds : list of groups and id of users who are authorized
	 * @param user             : information of current user logged
	 * @param handler          : handler which contains the response
	 */
	void listResources(List<String> groupsAndUserIds, UserInfos user, Handler<Either<String, JsonArray>> handler);

	/**
	 * Update a resource
	 *
	 * @param id      : id of current resource
	 * @param data    : object which contains information of resource
	 * @param handler : handler which contains the response
	 */
	void updateResource(String id, JsonObject data, Handler<Either<String, JsonObject>> handler);

	/**
	 * Get the booking owners list of current resource
	 *
	 * @param resourceId : id of current resource
	 * @param handler    : handler which contains the response
	 */
	void getBookingOwnersIds(long resourceId, Handler<Either<String, JsonArray>> handler);

	/**
	 * Get max_delay and min_delay of resource, owner, school_id and managers (userIds and groupIds) of resourceType
	 *
	 * @param resourceId : id of current resource
	 * @param handler    : handler which contains the response
	 */
	void getDelaysAndTypeProperties(long resourceId, Handler<Either<String, JsonObject>> handler);

	/**
	 * Add the notification for current user on current resource
	 *
	 * @param id      : id of current resource
	 * @param user    : information of current user logged
	 * @param handler : handler which contains the response
	 */
	void addNotification(String id, UserInfos user, Handler<Either<String, JsonObject>> handler);

	/**
	 * Remove the notification for current user on current resource
	 *
	 * @param id      : id of current resource
	 * @param user    : information of current user logged
	 * @param handler : handler which contains the response
	 */
	void removeNotification(String id, UserInfos user, Handler<Either<String, JsonObject>> handler);

	/**
	 * Get notification list for current user
	 *
	 * @param user    : information of current user logged
	 * @param handler : handler which contains the response
	 */
	void getNotifications(UserInfos user, Handler<Either<String, JsonArray>> handler);

	/**
	 * Get the user's id for current resource
	 *
	 * @param resourceId : id of current resource
	 * @param user       : information of current user logged
	 * @param handler    : handler which contains the response
	 */
	void getUserNotification(long resourceId, UserInfos user, Handler<Either<String, JsonArray>> handler);
} public class ResourceServiceSqlImpl extends SqlCrudService implements ResourceService {

	public ResourceServiceSqlImpl() {
		super("rbs", "resource");
	}

	@Override
	public void listResources(final List<String> groupsAndUserIds, final UserInfos user,
			final Handler<Either<String, JsonArray>> handler) {

		StringBuilder query = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();

		query.append("SELECT r.*,")
			.append(" json_agg(row_to_json(row(rs.member_id,rs.action)::rbs.share_tuple)) as shared,")
			.append(" array_to_json(array_agg(m.group_id)) as groups ")
			.append(" FROM rbs.resource AS r")
			.append(" INNER JOIN rbs.resource_type AS t ON r.type_id = t.id")
			.append(" LEFT JOIN rbs.resource_shares AS rs ON r.id = rs.resource_id")
			.append(" LEFT JOIN rbs.resource_type_shares AS ts ON t.id = ts.resource_id")
			.append(" LEFT JOIN rbs.members AS m ON (rs.member_id = m.id AND m.group_id IS NOT NULL)");

		query.append(" WHERE rs.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray()));
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}

		query.append(" OR r.owner = ? ");
		values.add(user.getUserId());

		query.append(" OR ts.member_id IN ").append(Sql.listPrepared(groupsAndUserIds.toArray()));
		for (String groupOruser : groupsAndUserIds) {
			values.add(groupOruser);
		}

		query.append(" OR t.owner = ?");
		values.add(user.getUserId());

 		// A local administrator of a given school can see all resources of the school's types, even if he is not owner or manager of these types or resources
		List<String> scope = getLocalAdminScope(user);
		if (scope!=null && !scope.isEmpty()) {
			query.append(" OR t.school_id IN ").append(Sql.listPrepared(scope.toArray()));
			for (String schoolId : scope) {
				values.add(schoolId);
			}
		}

		query.append(" GROUP BY r.id")
			.append(" ORDER BY r.name");

		Sql.getInstance().prepared(query.toString(), values, parseShared(handler));
	}

	@Override
	public void updateResource(final String resourceId, final JsonObject resource,
			final Handler<Either<String, JsonObject>> handler) {

		StringBuilder sb = new StringBuilder();
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		for (String attr : resource.fieldNames()) {
			if ("was_available".equals(attr)) {
				continue;
			}
			sb.append(attr).append(" = ?, ");
			values.add(resource.getValue(attr));
		}
		unsetFieldIfNull(resource, sb, values, "max_delay");
		unsetFieldIfNull(resource, sb, values, "min_delay");

		StringBuilder query = new StringBuilder();
		query.append("UPDATE rbs.resource")
			.append(" SET ")
			.append(sb.toString())
			.append("modified = NOW()")
			.append(" WHERE id = ?")
			.append(" RETURNING id, name");
		values.add(parseId(resourceId));

		final boolean isAvailable = resource.getBoolean("is_available");
		final boolean wasAvailable = resource.getBoolean("was_available");

		if(isAvailable == wasAvailable) {
			Sql.getInstance().prepared(query.toString(), values, validUniqueResultHandler(handler));
		}
		else {
			SqlStatementsBuilder statementsBuilder = new SqlStatementsBuilder();
			statementsBuilder.prepared(query.toString(), values);

			// Update bookings' status to "created" if resource is available again, and to "suspended" if resource is now unavailable
			BookingStatus newStatus = isAvailable ? CREATED : SUSPENDED;

			StringBuilder bookingQuery = new StringBuilder("UPDATE rbs.booking");
			bookingQuery.append(" SET status = ").append(newStatus.status())
				.append(" WHERE resource_id = ?")
				.append(" AND start_date >= now()")
				.append(" AND is_periodic = false");

			JsonArray bookingValues = new fr.wseduc.webutils.collections.JsonArray().add(parseId(resourceId));

			statementsBuilder.prepared(bookingQuery.toString(), bookingValues);

			Sql.getInstance().transaction(statementsBuilder.build(), validUniqueResultHandler(0, handler));
		}

	}

	private void unsetFieldIfNull(final JsonObject data, final StringBuilder sb,
			final JsonArray values, final String fieldname){

		if(data.getValue(fieldname) == null) {
			sb.append(fieldname).append(" = ?, ");
			values.addNull();
		}
	}

	@Override
	public void getBookingOwnersIds(long resourceId, Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT owner FROM rbs.booking")
			.append(" WHERE resource_id = ?")
			.append(" AND status != ?")
			.append(" AND start_date >= now()")
			.append(" AND is_periodic = false");

		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(resourceId)
			.add(REFUSED.status());

		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void getDelaysAndTypeProperties(long resourceId, Handler<Either<String, JsonObject>> handler) {
		StringBuilder query = new StringBuilder("SELECT r.min_delay, r.max_delay, t.owner, t.school_id,");

		// Subquery to return managers
		query.append(" (SELECT json_agg(DISTINCT ts.member_id) FROM rbs.resource_type_shares AS ts")
			.append(" WHERE ts.resource_id = t.id")
			.append(" AND ts.action = 'net-atos-entng-rbs-controllers-ResourceTypeController|shareJsonSubmit') AS managers");

		query.append(" FROM rbs.resource AS r")
			.append(" INNER JOIN rbs.resource_type AS t ON r.type_id = t.id")
			.append(" WHERE r.id = ?");

		JsonArray values = new fr.wseduc.webutils.collections.JsonArray().add(resourceId);

		Sql.getInstance().prepared(query.toString(), values, validUniqueResultHandler(handler));
	}

	@Override
	public void addNotification(String id, UserInfos user, Handler<Either<String, JsonObject>> handler) {
		StringBuilder query = new StringBuilder("INSERT INTO rbs.notifications (resource_id, user_id)" +
				" SELECT r.id, ? FROM rbs.resource AS r WHERE r.id = ? AND NOT EXISTS (SELECT resource_id, user_id FROM rbs.notifications WHERE user_id = ? AND resource_id = r.id)");
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(user.getUserId());
		values.add(parseId(id));
		values.add(user.getUserId());
		Sql.getInstance().prepared(query.toString(), values, validUniqueResultHandler(handler));
	}

	@Override
	public void removeNotification(String id, UserInfos user, Handler<Either<String, JsonObject>> handler) {
		StringBuilder query = new StringBuilder("DELETE FROM rbs.notifications WHERE resource_id = CAST (? AS BIGINT) AND user_id = ?");
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(parseId(id));
		values.add(user.getUserId());
		Sql.getInstance().prepared(query.toString(), values, validRowsResultHandler(handler));
	}

	@Override
	public void getNotifications(UserInfos user, final Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder("SELECT resource_id FROM rbs.notifications WHERE user_id = ?");
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(user.getUserId());
		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void getUserNotification(long resourceId, UserInfos user, Handler<Either<String, JsonArray>> handler) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT user_id FROM rbs.notifications")
				.append(" WHERE resource_id = ?")
				.append(" AND user_id != ?");
		JsonArray values = new fr.wseduc.webutils.collections.JsonArray();
		values.add(resourceId);
		values.add(user.getUserId());
		Sql.getInstance().prepared(query.toString(), values, validResultHandler(handler));
	}

	@Override
	public void createResource(final JsonObject resource, final UserInfos user,
			final Handler<Either<String, JsonObject>> handler) {

		String typeId = resource.getString("type_id");
		resource.put("type_id", parseId(typeId));

		super.create(resource, user, handler);
	}

}
import static net.atos.entng.rbs.BookingStatus.*;
import static net.atos.entng.rbs.Rbs.RBS_NAME;
import static org.entcore.common.http.response.DefaultResponseHandler.arrayResponseHandler;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import io.vertx.core.buffer.Buffer;
import net.atos.entng.rbs.BookingUtils;
import net.atos.entng.rbs.Rbs;
import net.atos.entng.rbs.models.Slots;
import org.entcore.common.controller.ControllerHelper;
import org.entcore.common.events.EventHelper;
import org.entcore.common.events.EventStore;
import org.entcore.common.events.EventStoreFactory;
import org.entcore.common.http.filter.ResourceFilter;
import org.entcore.common.user.UserInfos;
import org.entcore.common.user.UserUtils;
import fr.wseduc.rs.ApiDoc;
import fr.wseduc.rs.Delete;
import fr.wseduc.rs.Get;
import fr.wseduc.rs.Post;
import fr.wseduc.rs.Put;
import fr.wseduc.security.ActionType;
import fr.wseduc.security.SecuredAction;
import fr.wseduc.webutils.Either;
import fr.wseduc.webutils.I18n;
import fr.wseduc.webutils.http.Renders;
import fr.wseduc.webutils.request.RequestUtils;
import io.vertx.core.AsyncResult;
import io.vertx.core.Handler;
import io.vertx.core.http.HttpServerRequest;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import net.atos.entng.rbs.filters.TypeAndResourceAppendPolicy;
import net.atos.entng.rbs.model.ExportBooking;
import net.atos.entng.rbs.model.ExportRequest;
import net.atos.entng.rbs.model.ExportResponse;
import net.atos.entng.rbs.service.*;
import net.atos.entng.rbs.service.pdf.PdfExportService;
import io.vertx.core.Vertx;
import io.vertx.core.eventbus.EventBus;
import io.vertx.core.eventbus.Message;
import org.vertx.java.core.http.RouteMatcher;
import static net.atos.entng.rbs.BookingUtils.*;
import net.atos.entng.rbs.models.Booking;
import net.atos.entng.rbs.models.Resource;
import net.atos.entng.rbs.service.BookingService;
import net.atos.entng.rbs.service.BookingServiceSqlImpl;
import net.atos.entng.rbs.service.ResourceService;
import net.atos.entng.rbs.service.ResourceServiceSqlImpl;
/*
 * Copyright © Région Nord Pas de Calais-Picardie,  Département 91, Région Aquitaine-Limousin-Poitou-Charentes, 2016.
 *
 * This file is part of OPEN ENT NG. OPEN ENT NG is a versatile ENT Project based on the JVM and ENT Core Project.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation (version 3 of the License).
 *
 * For the sake of explanation, any module that communicate over native
 * Web protocols, such as HTTP, with OPEN ENT NG is outside the scope of this
 * license and could be license under its own terms. This is merely considered
 * normal use of OPEN ENT NG, and does not fall under the heading of "covered work".
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 */

package net.atos.entng.rbs.controllers;








public class BookingController extends ControllerHelper {
	static final String RESOURCE_NAME = "resource_booking";

	private static final String BOOKING_CREATED_EVENT_TYPE = RBS_NAME + "_BOOKING_CREATED";
	private static final String BOOKING_UPDATED_EVENT_TYPE = RBS_NAME + "_BOOKING_UPDATED";
	private static final String BOOKING_DELETED_EVENT_TYPE = RBS_NAME + "_BOOKING_DELETED";
	private static final String BOOKING_VALIDATED_EVENT_TYPE = RBS_NAME + "_BOOKING_VALIDATED";
	private static final String BOOKING_REFUSED_EVENT_TYPE = RBS_NAME + "_BOOKING_REFUSED";
	private static final String PERIODIC_BOOKING_CREATED_EVENT_TYPE = RBS_NAME + "_PERIODIC_BOOKING_CREATED";
	private static final String PERIODIC_BOOKING_UPDATED_EVENT_TYPE = RBS_NAME + "_PERIODIC_BOOKING_UPDATED";
	private static final String PERIODIC_BOOKING_DELETED_EVENT_TYPE = RBS_NAME + "_PERIODIC_BOOKING_DELETED";

	private static final I18n i18n = I18n.getInstance();

	private final SchoolService schoolService;
	private final BookingService bookingService;
	private final ResourceService resourceService;
	private BookingNotificationService bookingNotificationService;
	private final EventHelper eventHelper;


	public BookingController(EventBus eb) {
		super();
		bookingService = new BookingServiceSqlImpl();
		resourceService = new ResourceServiceSqlImpl();
		schoolService = new SchoolService(eb);
		final EventStore eventStore = EventStoreFactory.getFactory().getEventStore(Rbs.class.getSimpleName());
		eventHelper = new EventHelper(eventStore);
	}

	@Override
	public void init(Vertx vertx, JsonObject config, RouteMatcher rm, Map<String, fr.wseduc.webutils.security.SecuredAction> securedActions) {
		super.init(vertx, config, rm, securedActions);
		bookingNotificationService = new BookingNotificationService(BookingController.log, eb, notification, bookingService);
	}

	@Post("/resource/:id/booking")
	@ApiDoc("Create booking of a given resource")
	@SecuredAction(value = "rbs.contrib", type = ActionType.RESOURCE)
	@ResourceFilter(TypeAndResourceAppendPolicy.class)
	public void createBooking(final HttpServerRequest request) {
		UserUtils.getUserInfos(eb, request, user -> {
			if (user != null) {
				RequestUtils.bodyToJson(request, pathPrefix + "createBooking", getBookingHandler(user, request, true));
			} else {
				log.debug("User not found in session.");
				unauthorized(request);
			}
		});
	}

	/**
	 * @param isCreation : true when creating a booking, false when updating a booking.
	 *
	 * @return Handler to create or update a booking
	 */
	private Handler<JsonObject> getBookingHandler(final UserInfos user, final HttpServerRequest request, final boolean isCreation) {
		return json -> {
			final String resourceId = request.params().get("id");
			final String bookingId = request.params().get("bookingId");
			Slots slots = new Slots(json.getJsonArray("slots")) ;