DROP TABLE Zakaznik CASCADE CONSTRAINTS;
DROP TABLE Zamestnanec CASCADE CONSTRAINTS;
DROP TABLE Album CASCADE CONSTRAINTS;
DROP TABLE Vypujcka CASCADE CONSTRAINTS;
DROP TABLE Zanr CASCADE CONSTRAINTS;
DROP TABLE Skladba CASCADE CONSTRAINTS;
DROP TABLE Skladba_Zanr CASCADE CONSTRAINTS;
DROP TABLE Skladba_Album CASCADE CONSTRAINTS;
DROP TABLE CD CASCADE CONSTRAINTS;
DROP TABLE LP CASCADE CONSTRAINTS;


CREATE TABLE Zakaznik (
  rodne_cislo VARCHAR2(11) PRIMARY KEY NOT NULL,
  jmeno VARCHAR2(20) NOT NULL,
  prijmeni VARCHAR2(25) NOT NULL,
  adresa VARCHAR2(50),
  telefon NUMBER NOT NULL,
  email VARCHAR(50) NOT NULL,
  datum_registrace DATE NOT NULL
);

CREATE TABLE Zamestnanec (
  login VARCHAR2(20) PRIMARY KEY NOT NULL,
  jmeno VARCHAR2(20) NOT NULL,
  prijmeni VARCHAR2(25) NOT NULL,
  adresa VARCHAR2(50) NOT NULL,
  telefon NUMBER NOT NULL,
  email VARCHAR(50) NOT NULL,
  datum_nastupu DATE NOT NULL,
  plat NUMBER NOT NULL
);

CREATE TABLE Album (
  album_id NUMBER PRIMARY KEY NOT NULL,
  nazev VARCHAR2(80) NOT NULL,
  autor VARCHAR2(80) NOT NULL,
  vydavatel VARCHAR2(80) NOT NULL,
  producent VARCHAR2(50) NOT NULL,
  popis VARCHAR2(100),
  aktualni_cena NUMBER NOT NULL,
  rok_vydani NUMBER NOT NULL
);

CREATE TABLE Skladba (
  skladba_id NUMBER PRIMARY KEY NOT NULL,
  nazev VARCHAR2(80) NOT NULL,
  interpret VARCHAR2(80) NOT NULL,
  delka NUMBER NOT NULL,
  rok_vydani NUMBER NOT NULL,
  producent VARCHAR2(50) NOT NULL
);

CREATE TABLE CD (
  nosic_id NUMBER PRIMARY KEY NOT NULL,
  celkovy_pocet NUMBER NOT NULL,
  dostupny_pocet NUMBER NOT NULL,
  mira_opotrebeni VARCHAR2(80) NOT NULL,
  album NUMBER NOT NULL
);

CREATE TABLE LP (
  nosic_id NUMBER PRIMARY KEY NOT NULL,
  celkovy_pocet NUMBER NOT NULL,
  dostupny_pocet NUMBER NOT NULL,
  mira_opotrebeni VARCHAR2(80) NOT NULL,
  album NUMBER NOT NULL
);

CREATE TABLE Vypujcka (
  vypujcka_id NUMBER PRIMARY KEY NOT NULL,
  datum_od DATE NOT NULL,
  datum_do DATE NOT NULL,
  datum_vraceni DATE NOT NULL,
  stav_vypujcky VARCHAR2(20),
  cena NUMBER NOT NULL,
  zakaznik VARCHAR2(11) NOT NULL,
  zamestnanec VARCHAR2(20) NOT NULL,
  nosic NUMBER NOT NULL
);

CREATE TABLE Zanr (
  zanr_id NUMBER PRIMARY KEY NOT NULL,
  nazev_zanr VARCHAR2(20) NOT NULL
);

CREATE TABLE Skladba_Zanr (
  skladba_id NUMBER NOT NULL,
  zanr_id NUMBER NOT NULL
);

CREATE TABLE Skladba_Album (
  album_id NUMBER NOT NULL,
  skladba_id NUMBER NOT NULL
);


ALTER TABLE Skladba_Zanr ADD CONSTRAINT skladba_zanr_pk PRIMARY KEY (skladba_id, zanr_id);
ALTER TABLE Skladba_Album ADD CONSTRAINT album_skladba_pk PRIMARY KEY (album_id, skladba_id);

ALTER TABLE CD ADD CONSTRAINT cd_fk FOREIGN KEY (album) REFERENCES Album;
ALTER TABLE LP ADD CONSTRAINT lp_fk FOREIGN KEY (album) REFERENCES Album;


ALTER TABLE Vypujcka ADD CONSTRAINT vypujcka_zakaznik_fk FOREIGN KEY (zakaznik) REFERENCES Zakaznik;
ALTER TABLE Vypujcka ADD CONSTRAINT vypujcka_zamestnanec_fk FOREIGN KEY (zamestnanec) REFERENCES Zamestnanec;
ALTER TABLE Skladba_Zanr ADD CONSTRAINT skladba_zanr_skladba_fk FOREIGN KEY (skladba_id) REFERENCES Skladba;
ALTER TABLE Skladba_Zanr ADD CONSTRAINT skladba_zanr_zanr_fk FOREIGN KEY (zanr_id) REFERENCES Zanr;
ALTER TABLE Skladba_Album ADD CONSTRAINT skladba_album_skladba_fk FOREIGN KEY (skladba_id) REFERENCES Skladba;
ALTER TABLE Skladba_Album ADD CONSTRAINT skladba_album_album_fk FOREIGN KEY (album_id) REFERENCES Album;


INSERT INTO Zakaznik VALUES ('850131/4581', 'Al', 'Koholik', 'Leva 95 Brno', 778785145, 'nub@aa.com', TO_DATE('23.08.2009', 'dd.mm.yyyy'));
INSERT INTO Zakaznik VALUES ('564831/4581', 'Walter', 'White', 'Prava 42 Brno', 778561415, 'heisengerg@bb.com', TO_DATE('24.12.2011', 'dd.mm.yyyy'));

INSERT INTO Zamestnanec VALUES ('xnovak00', 'Petr', 'Novak', 'Sadova 01', 777777777,'varecka@videopujcovna.cz', TO_DATE('05.01.2009', 'dd.mm.yyyy'), 5);
INSERT INTO Zamestnanec VALUES ('xfajr00', 'Pavel', 'Fajrl', 'Ahoh 01', 77777665,'pavel@videopujcovna.cz', TO_DATE('02.03.2004', 'dd.mm.yyyy'), 6);

INSERT INTO Album VALUES (1, 'New Shit', 'The Hoes', 'Sony music', 'Selfie', 'BEST album ever', 999, 2014);
INSERT INTO Album VALUES (2, 'Adasd', 'ADerw', 'UML', 'TFDGSFD', 'ASDGSCBRW', 995, 2014);
INSERT INTO Album VALUES (3, 'bbb', 'ccc', 'UML', 'TFDGSFD', 'Aasf', 993, 2018);



INSERT INTO Skladba VALUES (1, 'Reload', 'Hoe', 480, 2005, 'ASDRSDF');
INSERT INTO Skladba VALUES (2, 'BDFGB', 'SDFG', 360, 2007, 'ASDRaF');
INSERT INTO Skladba VALUES (3, 'Red', 'Hell', 199, 2005, 'HTYD');

INSERT INTO CD VALUES (1, 4, 5, 'NOVE', 1);
INSERT INTO CD VALUES (2, 3, 45, 'POSKRABANE', 2);

INSERT INTO LP VALUES (1, 5, 65, 'NOVE', 1);
INSERT INTO LP VALUES (2, 4, 86, 'POSKRABANE', 2);

INSERT INTO Zanr VALUES (1, 'Blues');
INSERT INTO Zanr VALUES (2, 'Rock');
INSERT INTO Zanr VALUES (3, 'Jazz');
INSERT INTO Zanr VALUES (4, 'Pop');

INSERT INTO Vypujcka VALUES (1, TO_DATE('05.01.2009', 'dd.mm.yyyy'), TO_DATE('20.01.2009', 'dd.mm.yyyy'), TO_DATE('15.01.2009', 'dd.mm.yyyy'), 'OK', 50, '850131/4581', 'xnovak00', 1);
INSERT INTO Vypujcka VALUES (2, TO_DATE('05.01.2009', 'dd.mm.yyyy'), TO_DATE('20.01.2009', 'dd.mm.yyyy'), TO_DATE('15.01.2009', 'dd.mm.yyyy'), 'OK', 50, '564831/4581', 'xfajr00', 2);