/**
 * view na zarezervovane apartmany
 * (potvrzenych objednavek)
 *
 */
CREATE VIEW reserved_apartments AS
SELECT 
	reservation.first_name + ' ' + reservation.last_name AS name,
	reservation.street,
	reservation.city,
	reservation.postal_code,
	apartment_reservation.reservation_start,
	apartment_reservation.reservation_end,
	apartment_reservation.price,
	apartment.description
FROM reservation
JOIN apartment_reservation
	ON apartment_reservation.id_reservation = reservation.id
JOIN apartment
	ON apartment.id = apartment_reservation.id_apartment
WHERE 
	reservation.pending = 0;
	

/**
 * prehled cen za kategorie apratmanu
 *
 * z ceniku ve tvaru 'platne od' odelame
 * pohled na tvar 'platne od - platne do'
 */
go
CREATE VIEW price_overview
AS
SELECT
p1.valid_since AS valid_since,
MIN(p2.valid_since) AS valid_till,
p1.id_category AS id_category,
p1.price AS price
FROM price AS p1
JOIN price AS p2 
	ON p1.valid_since < p2.valid_since
	AND p1.id_category = p2.id_category
GROUP BY p1.valid_since, p1.id_category, p1.price;

/*********************************/

/**
 * titulky kategorii by mely but unikatni
 */
go
CREATE UNIQUE INDEX unqCategoryTitle 
	ON category ( title );

/**
 * kombinace data a kategorie je unikatni
 * tez se podle ni vyhledava a joinuje
 */
go
CREATE UNIQUE INDEX unqPriceValidSinceIdCategory 
	ON price ( valid_since, id_category );

/**
 * v rezervacich zrejme budeme vyhledavat
 * podle jmena
 */
go
CREATE INDEX indReservationFirstName 
	ON reservation (first_name);
go
CREATE INDEX indReservationLastName 
	ON reservation (last_name);
	
/**

-- cleanup

DROP VIEW reserved_apartments;
DROP VIEW price_overview;

DROP INDEX unqCategoryTitle ON category;
DROP INDEX unqPriceValidSinceIdCategory ON price;
DROP INDEX indReservationFirstName ON reservation;
DROP INDEX indReservationLastName ON reservation;

*/

