create database penzion;
use penzion;

create table kraje(
id_kraje varchar(2) not null,
nazev_kraje varchar(20) not null,
primary key (id_kraje)
);

create table uzivatel (
id_uzivatele int unsigned auto_increment not null,
jmeno varchar(15) not null,
prijmeni varchar(25) not null,
telefon varchar(14) not null,
id_kraje varchar(2) not null,
email varchar(50),
ucet varchar(3) not null,
login varchar(15),
heslo varchar(13),
opravneni varchar(3),
primary key (id_uzivatele),
foreign key (id_kraje) references kraje
);

create table pokoje (
id_pokoje varchar(1) not null,
nazev_pokoje varchar(9) not null,
pocet_mist tinyint not null,
primary key (id_pokoje)
);

create table strava (
id_strava varchar(1) not null,
nazev_stravy varchar(10) not null,
primary key(id_strava)
);

create table druh_akce (
id_druh_akce varchar(1) not null,
nazev_druhu_akce varchar(30) not null,
primary key(id_druh_akce)
);

create table obdobi (
id_obdobi smallint unsigned auto_increment not null,
nazev_obdobi varchar(25) not null,
datumplatod date not null,
datumplatdo date not null,
popis tinytext,
id_druh_akce varchar(1) not null,
primary key(id_obdobi),
foreign key(id_druh_akce) references druh_akce
);

create table archiv_obdobi (
id_obdobi smallint unsigned not null,
nazev_obdobi varchar(25) not null,
datumplatod date not null,
datumplatdo date not null,
popis tinytext,
id_druh_akce varchar(1) not null,
primary key(id_obdobi),
foreign key(id_druh_akce) references druh_akce
);

create table druh_hostu(
id_druh_hostu tinyint unsigned auto_increment not null,
nazev_druhu_hostu varchar(20) not null,
primary key(id_druh_hostu)
);

create table strava_v_obdobi(
id_strava_v_obdobi smallint unsigned auto_increment not null,
id_strava varchar(1) not null,
id_obdobi smallint unsigned not null,
primary key(id_strava_v_obdobi),
foreign key(id_strava) references strava,
foreign key(id_obdobi) references obdobi
);


create table archiv_strava_v_obdobi(
id_strava_v_obdobi smallint unsigned not null,
id_strava varchar(1) not null,
id_obdobi smallint unsigned not null,
primary key(id_strava_v_obdobi),
foreign key(id_strava) references strava,
foreign key(id_obdobi) references archiv_obdobi
);

create table cenik_sluzeb(
id_sluzby smallint unsigned auto_increment not null,
id_strava_v_obdobi smallint unsigned not null,
id_druh_hostu tinyint unsigned not null,
cena decimal(5,2) not null,
zobrazit varchar(3) not null,
primary key(id_sluzby),
foreign key(id_strava_v_obdobi) references strava_v_obdobi,
foreign key(id_druh_hostu) references druh_hostu
);

create table archiv_cenik_sluzeb(
id_sluzby smallint unsigned not null,
id_strava_v_obdobi smallint unsigned not null,
id_druh_hostu tinyint unsigned not null,
cena decimal(5,2) not null,
zobrazit varchar(3) not null,
primary key(id_sluzby),
foreign key(id_strava_v_obdobi) references archiv_strava_v_obdobi,
foreign key(id_druh_hostu) references druh_hostu
);

create table rezervace(
id_rezervace int unsigned auto_increment not null,
id_uzivatele int unsigned not null,
od date not null,
do date not null,
poznamka tinytext,
primary key (id_rezervace),
foreign key (id_uzivatele) references uzivatel
);

create table archiv_rezervace(
id_rezervace int unsigned not null,
id_uzivatele int unsigned not null,
od date not null,
do date not null,
poznamka tinytext,
primary key (id_rezervace),
foreign key (id_uzivatele) references uzivatel
);

create table detail_rezervace(
id_rezervace int unsigned not null,
id_pokoje varchar(1) not null,
id_sluzby smallint unsigned not null,
pocet_lidi tinyint not null,
primary key(id_pokoje,id_rezervace,id_sluzby),
foreign key(id_pokoje) references pokoje,
foreign key(id_sluzby) references cenik_sluzeb,
foreign key(id_rezervace) references rezervace
);

create table archiv_detail_rezervace(
id_rezervace int unsigned not null,
id_pokoje varchar(1) not null,
id_sluzby smallint unsigned not null,
pocet_lidi tinyint not null,
primary key(id_pokoje,id_rezervace,id_sluzby),
foreign key(id_pokoje) references pokoje,
foreign key(id_sluzby) references archiv_cenik_sluzeb,
foreign key(id_rezervace) references archiv_rezervace
);


