/*Datenbank erstellen (löschen wenn vorhanden)*/
drop database if exists Skiverleih;
create database Skiverleih;
use Skiverleih;

/*Tabellen erstellen (löschen wenn vorhanden)*/
drop table if exists PrivatKunde, FirmenKunde, Kunde, RabattGruppe, KundenGruppe, FirmKu_zu_KuGru, SkiTyp, Qualitaet, Zeit, Ski, Ausleihe;

create table PrivatKunde (id_PrivatKunde int not null auto_increment primary key, BLZ int, KontoNr int);

create table FirmenKunde (id_FirmenKunde int not null auto_increment primary key, Ansprechpartner varchar(32));

create table Kunde (id_Kunde int not null auto_increment primary key, PrivatKundeFK int,
FirmenKundeFK int, Name varchar(32), Vorname varchar(32), PLZ varchar(32), Ort varchar(32),
foreign key (PrivatKundeFK) references PrivatKunde(id_PrivatKunde), foreign key (FirmenKundeFK)
references FirmenKunde(id_FirmenKunde));

create table RabattGruppe (id_RabattGruppe int not null auto_increment primary key, Rabatt varchar(32));

create table KundenGruppe (id_KundenGruppe int not null auto_increment primary key, Name varchar(32));

create table FirmKu_zu_KuGru (FirmenKundeFK int not null auto_increment primary key, KundenGruppeFK int not null,
foreign key (FirmenKundeFK) references FirmenKunde(id_FirmenKunde),
foreign key (KundenGruppeFK) references KundenGruppe(id_KundenGruppe));

create table SkiTyp (id_SkiTyp int not null auto_increment primary key, Typ varchar(32));

create table Qualitaet (id_Qualitaet int not null auto_increment primary key, Qualitaet varchar(32));

create table Zeit (id_Zeit int not null auto_increment primary key, StartTag date, EndTag date, AnzTage int);

create table Ski (id_Ski int not null auto_increment primary key, SkiTypFK int, ZeitFK int,
QualitaetFK int, Hersteller varchar(32), Bezeichnung varchar(32), AnzExemplare int,
foreign key (SkiTypFK) references SkiTyp(id_SkiTyp), foreign key (ZeitFK) references Zeit(id_Zeit),
foreign key (QualitaetFK) references Qualitaet(id_Qualitaet));

create table Ausleihe (KundeFK int not null, SkiFK int not null, foreign key (KundeFK) references Kunde(id_Kunde),
foreign key (SkiFK) references FirmenKunde(id_FirmenKunde));

/*Daten einfügen*/
insert into SkiTyp (Typ) values ('Slalomski');
insert into SkiTyp (Typ) values ('Abfahrtsski');
insert into SkiTyp (Typ) values ('Langlaufski');

insert into Qualitaet (Qualitaet) values ('Basis');
insert into Qualitaet (Qualitaet) values ('Mid-Range');
insert into Qualitaet (Qualitaet) values ('Top-Level');

insert into KundenGruppe (name) values ('GrossKunde');
insert into KundenGruppe (name) values ('NeuKunde');
insert into KundenGruppe (name) values ('Kunde');

insert into RabattGruppe (Rabatt) values ('Skonto');
insert into RabattGruppe (Rabatt) values ('MengeRabatt');


insert into Zeit (StartTag, EndTag) values ("2015-11-12", "2015-12-12");
insert into Zeit (StartTag, EndTag) values ("2015-02-11", "2015-03-12");
insert into Zeit (StartTag, EndTag) values ("2015-01-12", "2016-01-01");

insert into FirmenKunde (Ansprechpartner) values ('Herr Meier');
insert into FirmenKunde (Ansprechpartner) values ('Herr Mumentaler');

insert into PrivatKunde (BLZ, KontoNR) values (01538642, 0914);
insert into PrivatKunde (BLZ, KontoNR) values (03569814, 8091);

insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (1,2,'Ackermann', 'Alexa', '3172', 'Niederwangen');
insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (1,2,'Kaufmann', 'Oliver', '4852', 'Rohtrist');
insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (2,1,'Neaf', 'Michael', '3645', 'Gwatt');
insert into Kunde (PrivatKundeFK, FirmenKundeFK, Name, Vorname, PLZ, Ort) values (2,1,'Schättin', 'Remo', '3600', 'Thun');

insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (1,2,1,'Fischer', 'Ranger JR', 5);
insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (1,3,3,'Head', 'World Cup Rebels', 2);
insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (3,3,2,'stoekli', 'Laser SG', 3);
insert into Ski (SkiTypFK, ZeitFK, QualitaetFK, Hersteller, Bezeichnung, AnzExemplare) values (2,1,2,'Fischer', 'RC4 Superior Pro', 6);

/*Benutzer erstellen (löschen wenn vorhanden)*/
drop user if exists 'PK'@'localhost', 'AA'@'localhost', 'M_1'@'%', 'M_2'@'%', 'M_3'@'localhost', 'M_4'@'%', 'M_5'@'%';
create user 'PK'@'localhost' identified by 'r00tpw';
create user 'AA'@'localhost' identified by 'r00tpw';
create user 'M_1'@'%' identified by '123';
create user 'M_2'@'%' identified by '123';
create user 'M_3'@'localhost' identified by '123';
create user 'M_4'@'%' identified by '123';
create user 'M_5'@'%' identified by '123';

/*Rollen erstellen (löschen wenn vorhanden)*/
drop role if exists DBAdmin, Lagerist, HR, Verkauf;
create role DBAdmin;
create role Lagerist;
create role HR;
create role Verkauf;

/*Rechte an Rollen vergeben*/
grant all privilegs on Skiverlei.* to DBAdmin;

grant select, insert, update on Skiverleih.Ski to Lagerist;
grant select, insert, update on Skiverleih.SkiTyp to Lagerist;
grant select, insert, update on Skiverleih.Qualitaet to Lagerist;
grant select on Skiverleih.zeit to Lagerist;

grant select, insert, update on Skiverleih.PrivatKunde to HR;
grant select, insert, update on Skiverleih.FirmenKunde to HR;
grant select, insert, update on Skiverleih.KundenGruppe to HR;
grant select, insert, update on Skiverleih.RabattGruppe to HR;

grant select on Skiverleih.Ski to HR;
grant select on Skiverleih.SkiTyp to HR;
grant select on Skiverleih.Qualitaet to HR;
grant select on Skiverleih.Kunde to HR;
grant select on Skiverleih.RabattGruppe to HR;
grant select, insert on Skiverleih.Zeit to HR;

/*Benutzer den Rollen hinzufügen*/
grant DBAdmin to PK@localhost;
grant DBAdmin to 'AA'@'localhost';
grant Lagerist to 'M_1'@'%';
grant Lagerist to 'M_2'@'%';
grant HR to 'M_3'@'localhost';
grant Verkauf to 'M_4'@'%';
grant Verkauf to 'M_5'@'%';

/*Views (löschen wenn vorhanden)*/
drop view if exists view_Kunde;
drop view if exists view_Ski;
create view view_Kunde as select * from Kunde;
create view view_Ski as select * from Ski;

select * from view_Kunde;
select * from view_Ski;