
/**
 *  tabulka kategorii apartmanu
 *
 */

CREATE TABLE FARKAF_sch.category (
	id INT IDENTITY,
	title VARCHAR(256),
	description TEXT,
	CONSTRAINT pk_category PRIMARY KEY (id)
);

/**
 *  tabulka s cenami podle kategorii
 *  kazda cena ma platnost od, v dane kategorii
 *  plati vzdy posledni platna cena k danemu datu
 */

CREATE TABLE FARKAF_sch.price (
	valid_since DATETIME,
	price MONEY,
	id_category INT,
	CONSTRAINT pk_price PRIMARY KEY (valid_since, id_category),
	CONSTRAINT fk_price_category FOREIGN KEY (id_category) REFERENCES FARKAF_sch.category(id) 
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

/**
 *  tabulka apartmanu
 *  aparman se vaze na urcitou kategorii, podle
 *  te se urcuje cena
 */

CREATE TABLE FARKAF_sch.apartment (
	id INT IDENTITY,
	description TEXT,
	id_category INT,
	CONSTRAINT pk_apartment PRIMARY KEY (id),
	CONSTRAINT fk_apartment_category FOREIGN KEY (id_category) REFERENCES FARKAF_sch.category(id)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

/**
 *  tabulka registrovanych zakazniku
 *
 *  registrovani zakaznici jsou navazani na rezervace,
 *  ovsem tak, ze se jejich udaje pri vytvoreni rezervace
 *  zkopiruji do tabulky rezervaci, aby zustaly uchovany
 *  vzhledem k rezervaci i pri zmene udaju zakaznika
 *
 *  sleva se chape jako sleva v celych procentech <0,100)
 *
 *  take existuje specialni zakaznik 'guest', ke kteremu
 *  jsou prirazeny rezervace neregistrovanych zakazniku
 *  tento zakaznik ma vzdy id 0
 */

CREATE TABLE FARKAF_sch.registered (
	id INT IDENTITY (0,1),
	first_name VARCHAR(256),
	last_name VARCHAR(256),
	street VARCHAR(256),
	city VARCHAR(256),
	postal_code INT,
	discount INT,
	CONSTRAINT pk_registered PRIMARY KEY (id),
	CONSTRAINT chck_registered_discount_range CHECK ((discount >= 0) AND (discount < 100))
);

/* jeste vlozime guesta */
INSERT INTO FARKAF_sch.registered(first_name, last_name, discount) VALUES ('guest', 'guest', 0);


/**
 *  tabulka rezervaci
 *
 *  rezervace jsou zpracovavany tak, ze dojde k jejich
 *  inicializaci, jsou ve stavu 'pending', pote je mozne do
 *  nich pridavat a z nich ubirat apartmany k rezervaci
 *  nasledne je mozne je uzavrit jako potvrzene, kdy 
 *  s jejich obsahem jiz nelze manipulovat
 *
 *  udrzuje se tez casove razitko, u pending rezervace
 *  ma vyznam posledni manipulace s ni, u uzavrene
 *  datum jejiho uzavreni
 */

CREATE TABLE FARKAF_sch.reservation (
	id INT IDENTITY,
	first_name VARCHAR(256),
	last_name VARCHAR(256),
	street VARCHAR(256),
	city VARCHAR(256),
	postal_code INT,
	created DATETIME DEFAULT (getdate()),
	pending BIT,
	id_registered INT NULL,
	total_price MONEY,
	CONSTRAINT pk_reservation PRIMARY KEY (id),
	CONSTRAINT fk_reservation_registered FOREIGN KEY (id_registered) REFERENCES FARKAF_sch.registered(id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);


/**
 *  vazebna tabulka rezervaci a apartmanu
 * 
 *  udrzuje od kdy do kdy je apartman rezervovanu,
 *  cenu, za kterou je rezervovany, tak aby nebyla po provedeni
 *  rezervace cena jiz ovlivnovana
 *
 *  vazba je jednoznacne urcena apartmanem a zacatkem rezervce
 *  (i koncem)
 */

CREATE TABLE FARKAF_sch.apartment_reservation (
	id_apartment INT,
	id_reservation INT,
	reservation_start DATETIME,
	reservation_end DATETIME,
	price MONEY,
	CONSTRAINT pk_apartment_reserevation PRIMARY KEY (id_apartment, reservation_start),
	CONSTRAINT fk_apartment_reservation_apartment FOREIGN KEY (id_apartment) REFERENCES FARKAF_sch.apartment(id),
	CONSTRAINT fk_apartment_reservation_reservation FOREIGN KEY (id_reservation) REFERENCES FARKAF_sch.reservation(id)
);

/**
 *  tabulka historie rezervaci
 *
 *  do teto tabulky se automaticky odkopirovavaji veskere zmeny 
 *  tak, aby bylo pozdeji mozne zrekonstruovat historii
 *  kazde rezervace
 */

CREATE TABLE FARKAF_sch.reservation_history (
	id INT IDENTITY,
	id_reservation INT,
	first_name VARCHAR(256),
	last_name VARCHAR(256),
	street VARCHAR(256),
	city VARCHAR(256),
	postal_code INT,
	created DATETIME DEFAULT (getdate()),
	pending BIT,
	id_registered INT,
	total_price MONEY,
	CONSTRAINT pk_reservation_history PRIMARY KEY (id_reservation)
);