-------------------------------------------------------------------------------------------------------------------------------
-- xsedla1e & xsadil07
-- 2. ukol do IDS, 2018/2019
-------------------------------------------------------------------------------------------------------------------------------

-- dodelat CHECK u rc a generalizaci u uctu
-- dodela vztahy u tabulek

DROP TABLE Účet CASCADE CONSTRAINTS;
DROP TABLE Správce CASCADE CONSTRAINTS;
DROP TABLE Uživatel CASCADE CONSTRAINTS;
DROP TABLE Místnost CASCADE CONSTRAINTS;
DROP TABLE Oddělení CASCADE CONSTRAINTS;
DROP TABLE Technika CASCADE CONSTRAINTS;
DROP TABLE Oprava CASCADE CONSTRAINTS;
DROP TABLE Žádost CASCADE CONSTRAINTS;
DROP TABLE Spravuje CASCADE CONSTRAINTS;

CREATE TABLE Účet (
    ID NUMBER NOT NULL PRIMARY KEY,
    Jméno VARCHAR2(100) NOT NULL,
    Příjmení VARCHAR2(100) NOT NULL,
    Rodné_číslo NUMBER, -- TODO udelat check
    Email VARCHAR2(127) NOT NULL CHECK (regexp_like(email, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')),
    Telefon NUMBER NOT NULL,
    Bydliště VARCHAR2(255) NOT NULL,
    ID_Umístění NUMBER NOT NULL
);

CREATE TABLE Správce (
  Účet NUMBER NOT NULL PRIMARY KEY,
  Licence VARCHAR2(100) NOT NULL
);

CREATE TABLE Uživatel (
  Účet NUMBER NOT NULL PRIMARY KEY
);

CREATE TABLE Místnost (
    Číslo_místnosti NUMBER NOT NULL PRIMARY KEY,
    Patro NUMBER NOT NULL,
    Typ VARCHAR2(1024) NOT NULL,
    Popis VARCHAR2(2048) NOT NULL,
    ID_Oddělení NUMBER NOT NULL
);

CREATE TABLE Oddělení(
    Číslo_oddělení NUMBER NOT NULL PRIMARY KEY,
    Popis VARCHAR2(2048) NOT NULL
);

CREATE TABLE Technika (
    ID NUMBER NOT NULL PRIMARY KEY,
    Výrobní_číslo VARCHAR2(127) NOT NULL,
    Typ VARCHAR2(1024) NOT NULL,
    Výrobce VARCHAR2(127) NOT NULL,
    Model_techniky VARCHAR2(127) NOT NULL,
    Specifikace VARCHAR2(1024) NOT NULL,
    ID_Umístění NUMBER NOT NULL,
    ID_Uživatele NUMBER NOT NULL
);

CREATE TABLE Oprava (
    ID NUMBER NOT NULL PRIMARY KEY,
    Kdy DATE NOT NULL,
    Aktuální_stav VARCHAR2(1024) NOT NULL,
    ID_Techniky NUMBER NOT NULL,
    ID_Správce NUMBER
);

CREATE TABLE Žádost (
    ID NUMBER NOT NULL PRIMARY KEY,
    Kdy DATE NOT NULL,
    ID_Techniky NUMBER, -- CO KDYZ OPRAVA NENI VYRIZENA? CO VZTAH V ERD? NOT NULL? HALO?
    ID_Opravy NUMBER NOT NULL,
    ID_Správce NUMBER NOT NULL,
    ID_Uživatele NUMBER NOT NULL
);


-- Vztahy
CREATE TABLE Spravuje (
  ID_Správce NUMBER NOT NULL,
  ID_Techniky NUMBER NOT NULL
);


ALTER TABLE Účet
  ADD FOREIGN KEY (ID_Umístění) REFERENCES Místnost(Číslo_místnosti);

ALTER TABLE Místnost
  ADD FOREIGN KEY (ID_Oddělení) REFERENCES Oddělení(Číslo_oddělení);

ALTER TABLE Technika
  ADD FOREIGN KEY (ID_Umístění) REFERENCES Místnost(Číslo_místnosti)
  ADD FOREIGN KEY (ID_Uživatele) REFERENCES Uživatel(Účet);

ALTER TABLE Oprava
  ADD FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID)
  ADD FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet);

ALTER TABLE Žádost
  ADD FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID)
  ADD FOREIGN KEY (ID_Opravy) REFERENCES Oprava(ID)
  ADD FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet)
  ADD FOREIGN KEY (ID_Uživatele) REFERENCES Uživatel(Účet);

ALTER TABLE Spravuje
  ADD FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet)
  ADD FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID);


-- Vyplneni oddeleni
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (1, 'CVT');
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (2, 'A');
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (3, 'D');
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (4, 'E');

-- Vlozeni mistnosti
INSERT INTO Místnost (Číslo_místnosti, Patro, Typ, Popis, ID_Oddělení)VALUES
    (10, 1, 'Správa CVT', 'Místnost slouží pro zaměstnance správy CVT', '1');
INSERT INTO Místnost (Číslo_místnosti, Patro, Typ, Popis, ID_Oddělení)VALUES
    (20, 1, 'Serverovna', 'V místnosti jsou uloženy veškeřé servery', '1');
INSERT INTO Místnost (Číslo_místnosti, Patro, Typ, Popis, ID_Oddělení)VALUES
    (30, 2, 'Kancelář ředitele', 'Zde sídlí velký šéf', '4');

-- Vlozeni uctu
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (0, 'Jiří', 'Novák', 5955477215, 'example0@email.com', 3107635075, 'Brno 44739, Zahradní 15', '10' );
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (1, 'Jan', 'Svoboda', 8142557343, 'example1@email.com', 4170585496, 'Ostrava 79652, Krátká 15', '10');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (2, 'Petr', 'Novotný', 1906932050, 'example2@email.com', 4217608504, 'Plzeň 25439, Nádražní 15', '10');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (3, 'Josef', 'Dvořák', 3635844726, 'example3@email.com', 5071080405, 'Liberec 25586, Školní 15', '10');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (4, 'Pavel', 'Černý', 9467128671, 'example4@email.com', 9088512650, 'Olomouc 64744, Polní 15', '20');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (5, 'Martin', 'Procházka', 7340528605, 'example5@email.com', 1241925560, 'Budějovice 72848, Luční 15', '20');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (6, 'Jaroslav', 'Kučera', 2670498059, 'example6@email.com', 6680356276, 'Labem 63404, Komenského 15', '30');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (7, 'Tomáš', 'Veselý', 1461806966, 'example7@email.com', 9381258512, 'Králové 46203, Nová 15', '10');
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Bydliště, ID_Umístění)
    VALUES (8, 'Miroslav', 'Horák', 6635251305, 'example8@email.com', 7227762065, 'Pardubice 60765, Husova 15', '10');

INSERT INTO Správce (Účet, Licence) VALUES (0, 'Elektronika');
INSERT INTO Správce (Účet, Licence) VALUES (1, 'Sítě');
INSERT INTO Správce (Účet, Licence) VALUES (2, 'Hardware');
INSERT INTO Správce (Účet, Licence) VALUES (3, 'Grafika');
INSERT INTO Uživatel (Účet) VALUES (4);
INSERT INTO Uživatel (Účet) VALUES (5);
INSERT INTO Uživatel (Účet) VALUES (6);
INSERT INTO Uživatel (Účet) VALUES (7);
INSERT INTO Uživatel (Účet) VALUES (8);

-- Vlozeni techniky
INSERT INTO Technika (ID, Výrobní_číslo, Typ, Výrobce, Model_techniky, Specifikace, ID_Umístění, ID_Uživatele)
    VALUES (0, '7FC0TI3231', 'PC', 'Asus', 'KLM18', '4 Ghz, RAM 124 KB...', 10, 4);
INSERT INTO Technika (ID, Výrobní_číslo, Typ, Výrobce, Model_techniky, Specifikace, ID_Umístění, ID_Uživatele)
    VALUES (1, 'M381KHSV4I', 'Tablet', 'Lenovo', 'LKJS48LS', '4 Ghz, RAM 124 KB...', 30, 5);
INSERT INTO Technika (ID, Výrobní_číslo, Typ, Výrobce, Model_techniky, Specifikace, ID_Umístění, ID_Uživatele)
    VALUES (2, 'QGE87D3J8K', 'Monitor', 'Dell', '23FHDIPS', 'Pekny monitor se stojankem', 20, 6);
INSERT INTO Technika (ID, Výrobní_číslo, Typ, Výrobce, Model_techniky, Specifikace, ID_Umístění, ID_Uživatele)
    VALUES (3, 'A4J734U0ME', 'Monitor', 'Dell', '24FHDTN', 'Ne tak pekny monitor bez stojanku', 10, 7);
INSERT INTO Technika (ID, Výrobní_číslo, Typ, Výrobce, Model_techniky, Specifikace, ID_Umístění, ID_Uživatele)
    VALUES (4, '54J61LE55B', 'Myš', 'Hama', 'SCRL456', 'Obyčejná PC myš', 20, 8);

-- Vlozeni oprav
INSERT INTO Oprava (ID, Kdy, Aktuální_stav, ID_Techniky, ID_Správce)
    VALUES (0, '20.4.2019', 'Probíhá', 3, 3);
INSERT INTO Oprava (ID, Kdy, Aktuální_stav, ID_Techniky)
    VALUES (1, '1.4.2019', 'Nevyřízeno', 0);

-- Vlozeni zadosti
INSERT INTO Žádost (ID, Kdy, ID_Techniky, ID_Opravy, ID_Správce, ID_Uživatele)
    VALUES (0, '1.4.2019', 0, 0, 2, 4);
INSERT INTO Žádost (ID, Kdy, ID_Techniky, ID_Opravy, ID_Správce, ID_Uživatele)
    VALUES (1, '2.4.2019', 1, 1, 3, 5);
INSERT INTO Žádost (ID, Kdy, ID_Techniky, ID_Opravy, ID_Správce, ID_Uživatele)
    VALUES (2, '2.4.2019', 1, 1, 1, 6);

-- Spravuje     NEVIM, ASI JE TO SPATNE, NEJAK OSETRIT ABY TECHNIKA MUSELA MIT SPRAVCE?
INSERT INTO Spravuje (ID_Správce, ID_Techniky)
    VALUES (1, 1);
INSERT INTO Spravuje (ID_Správce, ID_Techniky)
    VALUES (2, 2);