drop table Borrows;
drop table Books;
drop table Authors;
drop table Types;
drop table Students;




CREATE TABLE Students(
  StudentID Number NOT NULL,
  Name Varchar2(30 ) NOT NULL,
  Surname Varchar2(30 ) NOT NULL,
  Email Varchar2(50) NOT NULL,
  Password Varchar2(30) NOT NULL,
  Birthdate Date  NOT NULL,
  Gender Char(1)  NOT NULL,
  Class Varchar2(10 ) NOT NULL
)
/

-- Add keys for table Students

ALTER TABLE Students ADD CONSTRAINT Key1 PRIMARY KEY (StudentID)
/

-- Table Books

CREATE TABLE Books(
  BookID Number NOT NULL,
  ISBN Number NOT NULL,
  Name Varchar2(30 ) NOT NULL,
  Pagecount Number NOT NULL,
  AuthorID Number NOT NULL,
  TypeID Number NOT NULL
)
/

-- Add keys for table Books

ALTER TABLE Books ADD CONSTRAINT Key2 PRIMARY KEY (BookID)
/

-- Table Authors

CREATE TABLE Authors(
  AuthorID Number NOT NULL,
  Name Varchar2(20 ) NOT NULL,
  Surname Varchar2(20 ) NOT NULL
)
/

-- Add keys for table Authors

ALTER TABLE Authors ADD CONSTRAINT Key3 PRIMARY KEY (AuthorID)
/

-- Table Types

CREATE TABLE Types(
  TypeID Number NOT NULL,
  Name Varchar2(20) NOT NULL
)
/

-- Add keys for table Types

ALTER TABLE Types ADD CONSTRAINT Key4 PRIMARY KEY (TypeID)
/

-- Table Borrows

CREATE TABLE Borrows(
  BorrowID Number NOT NULL,
  StudentID Number NOT NULL,
  BookID Number NOT NULL,
  TakenDate Date  NOT NULL,
  BroughtDate Date
)
/


ALTER TABLE Borrows ADD CONSTRAINT Key5 PRIMARY KEY (BorrowID)
/


ALTER TABLE Borrows ADD CONSTRAINT Relationship1 FOREIGN KEY (StudentID) REFERENCES Students (StudentID);
ALTER TABLE Borrows ADD CONSTRAINT Relationship2 FOREIGN KEY (BookID) REFERENCES Books (BookID);

ALTER TABLE Books ADD CONSTRAINT Relationship3 FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID);
ALTER TABLE Books ADD CONSTRAINT Relationship4 FOREIGN KEY (TypeID) REFERENCES Types (TypeID);



--AUTOINCREMENT STUDENTS
drop sequence seq_auto_stud;
CREATE SEQUENCE seq_auto_stud;

CREATE OR REPLACE TRIGGER auto_stud
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    SELECT  seq_auto_stud.NEXTVAL
    INTO    :new.StudentID
    FROM    dual;
END;

create or replace Trigger userInsertCheck
before insert on STUDENTS
is
	passwordNotComplex exception;
	emailInvalid exception;
	passchar char;
	emailchar char;
	checkupper byte := 0;
	checknumber byte := 0;
BEGIN
	for i in 1..LENGTH(:new.Password)
	loop
		passchar:= substr(:new.Password,i,1)
		if passchar = UPPER(passchar) then
			checkupper := 1;
		end if;

		if passchar like '%[^0-9]%'
			checknumber := 1;
		end if;

	End Loop;

	if checkupper = 0 and checknumber = 0 then
		raise passwordNotComplex;
	end if;

	if :new.Email not like '%@%';
		raise emailInvalid;
	end if;


	exception
		when passwordNotComplex then raise_application_error(-21001,'Password muss Gross-/Kleinbuchstaben und eine Ziffer beinhalten!');
		when emailInvalid then raise_application_error(-21002,'Die Email ist fehlerhaft!');
END;


--AUTOINCREMENT BOOKS
drop sequence seq_auto_book;
CREATE SEQUENCE seq_auto_book;

CREATE OR REPLACE TRIGGER auto_book
BEFORE INSERT ON Books
FOR EACH ROW

BEGIN
    SELECT  seq_auto_book.NEXTVAL
    INTO    :new.BookID
    FROM    dual;
END;

-- AUTOINCREMENT AUTHORS
drop sequence seq_auto_auth;
CREATE SEQUENCE seq_auto_auth;

CREATE OR REPLACE TRIGGER auto_auth
BEFORE INSERT ON Authors
FOR EACH ROW

BEGIN
    SELECT  seq_auto_auth.NEXTVAL
    INTO    :new.AuthorID
    FROM    dual;
END;

--AUTOINCREMENT TYPES
drop sequence seq_auto_type;
CREATE SEQUENCE seq_auto_type;

CREATE OR REPLACE TRIGGER auto_type
BEFORE INSERT ON Types
FOR EACH ROW

BEGIN
    SELECT  seq_auto_type.NEXTVAL
    INTO    :new.TypeID
    FROM    dual;
END;

--AUTOINCREMENT BORROWS
drop sequence seq_auto_borr;
CREATE SEQUENCE seq_auto_borr;

CREATE OR REPLACE TRIGGER auto_borr
BEFORE INSERT ON Borrows
FOR EACH ROW

BEGIN
    SELECT  seq_auto_borr.NEXTVAL
    INTO    :new.BorrowID
    FROM    dual;
END;
-- Add keys for table Borrows


Insert into Students values(null,'Rogala','Kacper','kacper.rogala@gso.de','Kacper654', to_date('14.09.1999','dd.mm.yyyy'),'M','IAF62');
Insert into Students values(null,'Dinges','Marvin','marvin.dinges@gso.de','Marvin1',to_date('02.08.1999','dd.mm.yyyy'),'M','IAF62');
Insert into Students values(null,'Brandscheidt','Alexander','alexander.brandscheidt@gso.de','Alexander56',to_date('25.06.1999','dd.mm.yyyy'),'M','IAF62');

select* from students;

Insert into Authors values(null,'Ditgens','John');
Insert into Authors values(null,'Koeksal','Safak');
Insert into Authors values(null,'Yavuz','Eray');

select* from Authors;

Insert into Types values(null,'Drama');
Insert into Types values(null,'Comedy');
Insert into Types values(null,'Horror');

select* from Types;

Insert into Books values(null,1234567,'Er ist wieder da',340,1,1);
Insert into Books values(null,092109876,'Pipi Langstrumpf',540,2,2);
Insert into Books values(null,0909876,'Hansel und Gretel',120,3,3);

select * from Books;

insert into Borrows values(null,1,1,to_date('13.03.2019','dd.mm.yyyy'),null);
insert into Borrows values(null,2,2,to_date('13.03.2019','dd.mm.yyyy'),to_date('23.03.2019','dd.mm.yyyy'));
insert into Borrows values(null,3,3,to_date('13.03.2019','dd.mm.yyyy'),to_date('23.03.2019','dd.mm.yyyy'));

select * from Borrows;

drop view BucherAus;

create view BucherAus as
select bk.Name, st.Surname
from borrows br, students st, books bk
where  br.StudentID = st.StudentID AND br.BookID = bk.BookID;

Select * from BucherAus;

create view BucherInfo as
select bk.Name, at.Name, tp.Name
from Books bk, Authors at, Types tp
where bk.AuthorID = at.AuthorID AND bk.TypeID = tp.TypeID;


create or replace procedure BorrowBook(studentID number, bookID number)
is
    alreadyBorrowedError exception;
    borrowid number := null;
Begin
    select br.BorrowID into borrowid from borrows br where bookID = br.BookID AND br.BroughtID is NULL;
    if borrwoid is not null then
        raise alreadyBorrowedError;
    end if;

    insert into borrows values(null,studentID, bookID, Sysdate(), null);
	COMMIT;

	exception when alreadyBorrowedError then raise_application_error(-21000,'The book is already borrowed!');
end;

create or replace procedure BroughtBack(BookID number)
is
begin
	Update BORROWS br
	set BroughtDate = Sysdate()
	where BookID = br.BookID AND br.BroughtDate is NULL;
	COMMIT;
end;

Select st.surname, (
    Select tp.Name
    from Types tp
    where tp.TypeID = (
        Select TypeID from (
            Select bk.TypeID, count(bk.Name) c
            from Books bk, Borrows br
            where bk.BookID = br.BookID and br.StudentID = st.StudentID     -- st.StudentID kann hier nicht genutzt werden, obwohl es außen deklariert ist?
            group by bk.TypeID
            order by c DESC
        ) where rownum <= 1
    )
) likes
from Students st;