/**
 * prubeh rezervace je navrzen nasledovne
 *
 *  - nejprve se nova rezervace zalozi procedurou *_init
 *  - pote je ozne jeji obsah menit (pridavat a ubirat konkretni amartmany)
 *		tento krok nijak nevaliduje, co se do rezervace pridava,
 *		procedury *_add, *_del
 *  - nakonec je nutne rezervaci finalizovat prodceurou *_final
 *		tento krok overi, zda nektere pozadnovane apartmany nebyly jiz
 *		mezitim rezervovany ( a finalizovany), a provode finalizaci 
 *		rezervace, pripadne selze, doslo-li ke kolizi v pozadovanych apartmanech
 *
 *
 * pro inicializaci a finalizaci rezervace jsou pritomny dve sady funkci, 
 * jedna pro rezervovane, reservation_registered_*,
 * druha pro neregistrovane, reservation_guest_*	
 */

CREATE PROCEDURE FARKAF_sch.reservation_registered_init(
	@id INT OUTPUT,
	@registered_id INT
)
AS
BEGIN
	IF NOT EXISTS ( SELECT * FROM registered WHERE id = @registered_id)
		RAISERROR ('reservation inicialization requires valid user ', 10, 0);

	INSERT INTO FARKAF_sch.reservation (
		first_name,
		last_name,
		street,
		city,
		postal_code,
		created,
		pending,
		id_registered,
		total_price)
	SELECT 
		first_name,
		last_name,
		street,
		city,
		postal_code,
		getdate(),
		1,
		@registered_id,
		0
	FROM
		FARKAF_sch.registered
	WHERE id = @registered_id;

	SET @id = SCOPE_IDENTITY();
END;

GO

CREATE PROCEDURE FARKAF_sch.reservation_guest_init(
	@id INT OUTPUT,
	@first_name VARCHAR(256),
	@last_name VARCHAR(256),
	@street VARCHAR(256),
	@city VARCHAR(256),
	@postal_code INT
)
AS
BEGIN
	INSERT INTO FARKAF_sch.reservation (
		first_name,
		last_name,
		street,
		city,
		postal_code,
		created,
		pending,
		id_registered,
		total_price)
	VALUES (
		@first_name,
		@last_name,
		@street,
		@city,
		@postal_code,
		getdate(),
		0, -- guest id
		null,
		0);

	SET @id = SCOPE_IDENTITY();
END;

GO
--DROP PROCEDURE FARKAF_sch.reservation_add;
CREATE PROCEDURE FARKAF_sch.reservation_add(
	@id_apartment INT,
	@id_reservation INT,
	@reservation_start DATETIME,
	@reservation_end DATETIME
)
AS
BEGIN
	DECLARE @price MONEY;

	BEGIN TRANSACTION
	--get price
	SELECT TOP 1 @price = price * DATEDIFF(d, @reservation_end, @reservation_start)
	FROM FARKAF_sch.price JOIN FARKAF_sch.apartment ON price.id_category = apartment.id_category 
	WHERE apartment.id = @id_apartment AND valid_since < @reservation_start 
	ORDER BY valid_since DESC;

	
	INSERT INTO FARKAF_sch.apartment_reservation(
		id_apartment,
		id_reservation,
		reservation_start,
		reservation_end,
		price)
	VALUES (
		@id_apartment,
		@id_reservation,
		@reservation_start,
		@reservation_end,
		@price);
	
	COMMIT TRANSACTION
END;

GO
CREATE PROCEDURE FARKAF_sch.reservation_del(
	@id_apartment INT,
	@id_reservation INT
)
AS
BEGIN
	
	DELETE FROM FARKAF_sch.apartment_reservation 
	WHERE
		id_apartment = @id_apartment 
		AND id_reservation = @id_reservation;
END;

GO
CREATE PROCEDURE FARKAF_sch.reservation_registered_final(
	@id_reservation INT
)
AS
BEGIN
	DECLARE @total MONEY;

	BEGIN TRANSACTION;

	-- if exist colliding closed reservation
	IF EXISTS (SELECT * FROM FARKAF_sch.apartmen_reservation JOIN FARKAF_sch.reservation 
		ON apartmen_reservation.id_reservation = reservation.id WHERE
		pending = 0 AND (
			(reservation_start > '2011-01-01' AND reservation_start < '2011-01-31') OR
			(reservation_end > '2011-01-01' AND reservation_end < '2011-01-31') OR
			(reservation_start < '2011-01-01' AND reservation_start > '2011-01-31')
		)
	)
		ROLLBACK TRANSACTION;

	SELECT @total = SUM(price) FROM apartmen WHERE id_reservation = @id_reservation;

	UPDATE reservation SET pending = 0, total_price = @total WHERE id = @id_reservation;

	COMMIT TRANSACTION;
END;

GO
CREATE PROCEDURE FARKAF_sch.reservation_quest_final(
	@id_reservation INT
)
AS
BEGIN
	DECLARE @total MONEY;

	BEGIN TRANSACTION;

	-- if exist colliding closed reservation
	IF EXISTS (SELECT * FROM FARKAF_sch.apartmen_reservation JOIN FARKAF_sch.reservation 
		ON apartmen_reservation.id_reservation = reservation.id WHERE
		pending = 0 AND (
			(reservation_start > '2011-01-01' AND reservation_start < '2011-01-31') OR
			(reservation_end > '2011-01-01' AND reservation_end < '2011-01-31') OR
			(reservation_start < '2011-01-01' AND reservation_start > '2011-01-31')
		)
	)
		ROLLBACK TRANSACTION;

	SELECT @total = SUM(price) FROM apartmen WHERE id_reservation = @id_reservation;

	UPDATE reservation SET pending = 0, total_price = @total WHERE id = @id_reservation;

	COMMIT TRANSACTION;
END;