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

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 INT NOT NULL PRIMARY KEY,
    Jméno VARCHAR2(127) NOT NULL,
    Příjmení VARCHAR2(127) NOT NULL,
    Rodné_číslo INT NOT NULL,
    Email VARCHAR2(127) NOT NULL,
    Telefon INT NOT NULL,
    Město VARCHAR2(127) NOT NULL,
    Ulice VARCHAR2(127) NOT NULL,
    PSC INT NOT NULL,
    ID_Umístění INT NOT NULL,
    CHECK (regexp_like(Jméno, '^[a-zA-Z-]+$')),
    CHECK (regexp_like(Email, '^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$')),
    CHECK (regexp_like(Rodné_číslo, '^[0-9]{2}[0-8][0-9][0-3][0-9][0-9]{4}$')),
    CHECK (regexp_like(PSC, '^[0-9]{5}$')),
    CHECK (regexp_like(Telefon, '^[0-9]{9,}$'))
);

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

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

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

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

CREATE TABLE Technika (
    ID INT 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í INT NOT NULL,
    ID_Uživatele INT NOT NULL
);

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

CREATE TABLE Žádost (
    ID INT NOT NULL PRIMARY KEY,
    Kdy DATE NOT NULL,
    ID_Techniky INT NOT NULL,
    ID_Opravy INT,
    ID_Správce INT NOT NULL,
    ID_Uživatele INT NOT NULL
);


-- Vztahy
CREATE TABLE Spravuje (
  ID INT NOT NULL PRIMARY KEY,
  ID_Správce INT NOT NULL,
  ID_Techniky INT NOT NULL
);

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

ALTER TABLE Správce
  ADD CONSTRAINT FK_SprávcovskýÚčet
    FOREIGN KEY (Účet) REFERENCES Účet(ID) ON DELETE CASCADE;

ALTER TABLE Uživatel
  ADD CONSTRAINT FK_UživatelskýÚčet
    FOREIGN KEY (Účet) REFERENCES Účet(ID) ON DELETE CASCADE;

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

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

ALTER TABLE Oprava
  ADD CONSTRAINT FK_OpravaTechniky
    FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID) ON DELETE CASCADE
  ADD CONSTRAINT FK_SprávceOpravy
    FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet) ON DELETE SET NULL;

ALTER TABLE Žádost
  ADD CONSTRAINT FK_ŽádostTechniky
    FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID) ON DELETE CASCADE
  ADD CONSTRAINT FK_ŽádostOpravy
    FOREIGN KEY (ID_Opravy) REFERENCES Oprava(ID) ON DELETE SET NULL
  ADD CONSTRAINT FK_SprávceŽádosti
    FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet)
  ADD CONSTRAINT FK_PodavatelŽádosti
    FOREIGN KEY (ID_Uživatele) REFERENCES Uživatel(Účet);

ALTER TABLE Spravuje
  ADD CONSTRAINT FK_Spravuje
    FOREIGN KEY (ID_Správce) REFERENCES Správce(Účet) ON DELETE CASCADE
  ADD CONSTRAINT FK_SpravovanáTech
    FOREIGN KEY (ID_Techniky) REFERENCES Technika(ID) ON DELETE CASCADE;


-- Vlozeni oddeleni
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (1, 'CVT');
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (2, 'Kanceláře');
INSERT INTO Oddělení (Číslo_oddělení, Popis)
    VALUES (3, 'Přednášky');

-- 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', 2);
INSERT INTO Místnost (Číslo_místnosti, Patro, Typ, Popis, ID_Oddělení)VALUES
    (40, 2, 'Hlavní aula', 'Pro technické konference', 3);

-- Vlozeni uctu
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (1, 'Jiří', 'Novák', 5955127215, 'example0@email.com', 107635075, 'Brno', 'Zahradní 15', 44739, 10 );
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (2, 'Jan', 'Svoboda', 8142317343, 'example1@email.com', 410585496, 'Ostrava', 'Krátká 15', 79652, 10);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (3, 'Petr', 'Novotný', 1906212050, 'example2@email.com', 421608504, 'Plzeň', 'Nádražní 15', 25439, 10);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (4, 'Josef', 'Dvořák', 3635184726, 'example3@email.com', 501080405, 'Liberec', 'Školní 15', 25586, 10);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (5, 'Pavel', 'Černý', 9467101671, 'example4@email.com', 908512650, 'Olomouc', 'Polní 15', 64744, 20);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (6, 'Martin', 'Procházka', 7340288605, 'example5@email.com', 124925560, 'Budějovice', 'Luční 15', 72848, 20);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (7, 'Jaroslav', 'Kučera', 2670098059, 'example6@email.com', 660356276, 'Pardubice', 'Komenského 15', 63404, 30);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (8, 'Tomáš', 'Veselý', 1461136966, 'example7@email.com', 938258512, 'Králové', 'Nová 15', 46203, 10);
INSERT INTO Účet (ID, Jméno, Příjmení, Rodné_číslo, Email, Telefon, Město, Ulice, PSC, ID_Umístění)
    VALUES (9, 'Miroslav', 'Horák', 6635251305, 'example8@email.com', 722762065, 'Pardubice', 'Husova 15', 60765, 10);

INSERT INTO Správce (Účet, Licence) VALUES (1, 'Elektronika');
INSERT INTO Správce (Účet, Licence) VALUES (2, 'Sítě');
INSERT INTO Správce (Účet, Licence) VALUES (3, 'Hardware');
INSERT INTO Správce (Účet, Licence) VALUES (4, 'Grafika');
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);
INSERT INTO Uživatel (Účet) VALUES (9);

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

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

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

-- Spravuje
INSERT INTO Spravuje (ID, ID_Správce, ID_Techniky)
    VALUES (1, 1, 1);
INSERT INTO Spravuje (ID, ID_Správce, ID_Techniky)
    VALUES (2, 2, 2);
INSERT INTO Spravuje (ID, ID_Správce, ID_Techniky)
    VALUES (3, 3, 3);
INSERT INTO Spravuje (ID, ID_Správce, ID_Techniky)
    VALUES (4, 4, 4);
INSERT INTO Spravuje (ID, ID_Správce, ID_Techniky)
    VALUES (5, 1, 5);

-- SELECTY
-- Umístění uživatelů amístností, kteří jsou v prvním patře a nejmenují se Pavel (spojení dvou tabulek)
SELECT U.Jméno, U.Příjmení, M.Číslo_místnosti AS místnost
FROM Účet U
  INNER JOIN Místnost M ON U.ID_Umístění = M.Číslo_místnosti
WHERE M.Patro = 1 AND Jméno <> 'Pavel';

-- Výpis jmen uživatelů, kteří bydlí v Pardubicích, a kteří vlastní techniku (spojení dvou tabulek)
SELECT T.Typ, T.Výrobce, U.Jméno, U.Příjmení
FROM Účet U
  INNER JOIN Technika T ON U.ID = T.ID_Uživatele
WHERE U.Město = 'Pardubice';

-- Vypsání monitorů v opravě včetně správce, který za ní zodpovídá (spojení tří tabulek)
SELECT O.Aktuální_stav, T.Výrobní_číslo, T.Typ, T.Výrobce, U.Jméno AS Jméno_správce, U.Příjmení AS Příjmení_správce
FROM Technika T
  INNER JOIN Oprava O ON T.ID = O.ID_Techniky
  INNER JOIN Účet U ON O.ID_Správce = U.ID
WHERE T.Typ = 'Monitor';

-- Vypis počtů technik stejného typu s počtem větším než 1 (dotaz s klauzulí GROUP BY a agregační funkcí)
SELECT T.Typ, COUNT(*) AS Počet
FROM Technika T
GROUP BY T.Typ
HAVING COUNT(*) > 1;

-- Počet lidí v jednotlivých místnostech v prvním patře (dotaz s klauzulí GROUP BY a agregační funkcí)
SELECT M.Číslo_místnosti, COUNT(*) AS Počet
FROM Účet U INNER JOIN Místnost M ON U.ID_Umístění = M.Číslo_místnosti
WHERE M.Patro = 1
GROUP BY M.Číslo_místnosti;

SELECT