DROP TABLE GroupPosition CASCADE CONSTRAINTS;
DROP TABLE Editor CASCADE CONSTRAINTS;
DROP TABLE Category CASCADE CONSTRAINTS;
DROP TABLE Article CASCADE CONSTRAINTS;
DROP TABLE Membership CASCADE CONSTRAINTS;
DROP TABLE grants_access_to CASCADE CONSTRAINTS;
DROP TABLE RegisteredUser CASCADE CONSTRAINTS;
DROP TABLE ArticleComment CASCADE CONSTRAINTS;

CREATE TABLE GroupPosition
(
  baseSalary FLOAT NOT NULL,
  POSITIONNAME VARCHAR2(30) NOT NULL,
  PRIMARY KEY (POSITIONNAME)
);

CREATE TABLE Editor
(
  email VARCHAR2(30) NOT NULL,
  publishingName VARCHAR2(30) NOT NULL,
  RegisteredUsername VARCHAR2(30) NOT NULL,
  password VARCHAR2(30) NOT NULL,
  POSITIONNAME VARCHAR2(30) NOT NULL,
  PRIMARY KEY (email),
  FOREIGN KEY (POSITIONNAME) REFERENCES GroupPosition(POSITIONNAME)
);

CREATE TABLE Category
(
  categoryName VARCHAR2(30) NOT NULL,
  PRIMARY KEY (categoryName)
);

CREATE TABLE Article
(
  articleID INT NOT NULL,
  title VARCHAR2(60) NOT NULL,
  datePublished DATE NOT NULL,
  email VARCHAR2(30) NOT NULL,
  categoryName VARCHAR2(30) NOT NULL,
  PRIMARY KEY (articleID),
  FOREIGN KEY (email) REFERENCES Editor(email),
  FOREIGN KEY (categoryName) REFERENCES Category(categoryName)
);

CREATE TABLE Membership
(
  TYPE VARCHAR2(30) NOT NULL,
  PRIMARY KEY (TYPE)
);

CREATE TABLE grants_access_to
(
  TYPE VARCHAR2(30) NOT NULL,
  articleID INT NOT NULL,
  PRIMARY KEY (TYPE, articleID),
  FOREIGN KEY (TYPE) REFERENCES Membership(TYPE),
  FOREIGN KEY (articleID) REFERENCES Article(articleID)
);

CREATE TABLE RegisteredUser
(
  email VARCHAR2(30) NOT NULL,
  RegisteredUsername VARCHAR2(30) NOT NULL,
  password VARCHAR2(30) NOT NULL,
  TYPE VARCHAR2(30),
  PRIMARY KEY (email),
  FOREIGN KEY (TYPE) REFERENCES Membership(TYPE)
);

CREATE TABLE ArticleComment
(
  ArticleCommentID INT NOT NULL,
  numberOfLikes INT NOT NULL,
  email VARCHAR2(30) NOT NULL,
  commentText varchar2(60) NOT NULL,
  PRIMARY KEY (ArticleCommentID),
  FOREIGN KEY (email) REFERENCES RegisteredUser(email)
);

DROP SEQUENCE AutoIncSeq;

CREATE SEQUENCE AutoIncSeq
MINVALUE 0
START WITH 0
INCREMENT BY 1
CACHE 10;

DROP SEQUENCE AutoIncSeq2;

CREATE SEQUENCE AutoIncSeq2
MINVALUE 0
START WITH 0
INCREMENT BY 1
CACHE 10;

INSERT INTO GroupPosition VALUES (200,'part-time worker');
INSERT INTO GroupPosition VALUES (150,'new part-time worker');
INSERT INTO GroupPosition VALUES (500,'junior editor');
INSERT INTO GroupPosition VALUES (700,'senior editor');
INSERT INTO GroupPosition VALUES (1000,'veduci oddelenia');

INSERT INTO EDITOR VALUES ('KristopherDenzel@yahoo.com','Kristopher Denzel','KristopherDenzel','PASSWORD1','part-time worker');
INSERT INTO EDITOR VALUES ('BillPrudence@gmail.com','Bill Prudence','BillPrudence',            'PASSWORD2','new part-time worker');
INSERT INTO EDITOR VALUES ('LinfordMilford@gmail.com','Linford Milford','LinfordMilford',      'PASSWORD3','junior editor');
INSERT INTO EDITOR VALUES ('AydanTyson@gmail.com','Aydan Tyson','AydanTyson',                  'PASSWORD32','junior editor');
INSERT INTO EDITOR VALUES ('MaryanneWat@yahoo.com','Maryanne Wat','MaryanneWat',               'PASSWORD4','junior editor');
INSERT INTO EDITOR VALUES ('RaynerBarnabas@gmail.com','Rayner Barnabas','RaynerBarnabas',      'PASSWORD42','senior editor');
INSERT INTO EDITOR VALUES ('UlricStevie@gmail.com','Ulric Stevie','UlricStevie',               'PASSWORD5','veduci oddelenia');

INSERT INTO CATEGORY VALUES ('travel');
INSERT INTO CATEGORY VALUES ('local news');
INSERT INTO CATEGORY VALUES ('world news');
INSERT INTO CATEGORY VALUES ('fashion');
INSERT INTO CATEGORY VALUES ('music');
INSERT INTO CATEGORY VALUES ('politics');
INSERT INTO CATEGORY VALUES ('stars');
INSERT INTO CATEGORY VALUES ('tech');
INSERT INTO CATEGORY VALUES ('science');
INSERT INTO CATEGORY VALUES ('finance');

INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Russia used fake news to manipulate election','18-03-2017','KristopherDenzel@yahoo.com','politics');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Colombian police seize more than six tonnes of cocaine','18-03-2017','KristopherDenzel@yahoo.com','world news');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Top Asian News 5:23 p.m. GMT','1-03-2017','RaynerBarnabas@gmail.com','world news');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Mammoth discovery: Six-foot tusk found on Essex beach','21-02-2017','RaynerBarnabas@gmail.com','science');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'NASAs ice-world robots can reach, launch, and melt','15-03-2017','RaynerBarnabas@gmail.com','science');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Windows 10 Creators Update: Microsofts best just got better','3-01-2017','AydanTyson@gmail.com','tech');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'This season new trends','12-02-2017','AydanTyson@gmail.com','fashion');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Best places to travel to in the summer','3-01-2017','AydanTyson@gmail.com','travel');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'How to choose right savings account','3-01-2017','BillPrudence@gmail.com','finance');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'What to wear this spring','30-01-2017','MaryanneWat@yahoo.com','fashion');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Hollywood stars that rocked this red carpet','14-02-2017','MaryanneWat@yahoo.com','stars');
INSERT INTO ARTICLE VALUES (AutoIncSeq2.NEXTVAL,'Plastic makes up nearly 70% of all ocean litter','06-03-2017','MaryanneWat@yahoo.com','science');

INSERT INTO MEMBERSHIP VALUES ('NoMembership');
INSERT INTO MEMBERSHIP VALUES ('Basic');
INSERT INTO MEMBERSHIP VALUES ('Silver');
INSERT INTO MEMBERSHIP VALUES ('Gold');
INSERT INTO MEMBERSHIP VALUES ('Diamond');

INSERT INTO GRANTS_ACCESS_TO VALUES ('NoMembership',1);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Basic',1);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Basic',2);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',1);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',2);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',3);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',1);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',2);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',3);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',4);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',12);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',11);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',1);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',2);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',3);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',4);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',5);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',6);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',7);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',8);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',9);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',10);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',11);
INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',12);

INSERT INTO REGISTEREDUSER VALUES ('DarceyRowanne@gmail.com','Darcey','PASSWORD1','Diamond');
INSERT INTO REGISTEREDUSER VALUES ('DaynaYorick@gmail.com','Dayna','PASSWORD2','Diamond');
INSERT INTO REGISTEREDUSER VALUES ('ErnieTria@gmail.com','Ernie','PASSWORD3','Diamond');
INSERT INTO REGISTEREDUSER VALUES ('WestleyCandice@yahoo.com','Westley','PASSWORD4','Gold');
INSERT INTO REGISTEREDUSER VALUES ('KierstenTabatha@gmail.com','Kiersten','PASSWORD5','Gold');
INSERT INTO REGISTEREDUSER VALUES ('SteveSherry@gmail.com','Steve','PASSWORD6','Silver');
INSERT INTO REGISTEREDUSER VALUES ('AuraRachelle@yahoo.com','Aura','PASSWORD7','Basic');
INSERT INTO REGISTEREDUSER VALUES ('FelixParker@gmail.com','Felix','PASSWORD8','NoMembership');
INSERT INTO REGISTEREDUSER VALUES ('IbbieMelicent@yahoo.com','Ibbie','PASSWORD9','NoMembership');

INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,1,'DarceyRowanne@gmail.com','text komentara1');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,0,'DaynaYorick@gmail.com','text komentara2');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,0,'ErnieTria@gmail.com','text komentara3');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,5,'ErnieTria@gmail.com','text komentara4');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,7,'ErnieTria@gmail.com','text komentara5');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,3,'ErnieTria@gmail.com','text komentara51');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,12,'AuraRachelle@yahoo.com','text komentara6');
INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.NEXTVAL,3,'AuraRachelle@yahoo.com','text komentara7');

------- JEDNODUCHE SELECTY
/*Zobraz vsetkych registrovanych uzivatelov kt. maju gmail.*/
CREATE OR REPLACE VIEW GmailUsers AS
SELECT * FROM REGISTEREDUSER WHERE REGEXP_LIKE (EMAIL, '*.@gmail.com');

/*Zobraz všetky články ktoré patria do kategórie fashion.*/
CREATE OR REPLACE VIEW FashionCategory AS
SELECT * FROM ARTICLE WHERE CATEGORYNAME='fashion';

/*najdi vsetky clanky ktore patria do kategorie science a boli napisane senior editorom*/
CREATE OR REPLACE VIEW ScienceArticlesBySeniorEditor AS
SELECT * FROM ARTICLE
WHERE ARTICLE.EMAIL=(SELECT EMAIL
FROM EDITOR
WHERE EDITOR.POSITIONNAME='senior editor')
AND ARTICLE.CATEGORYNAME='science';

------- SPAJANIE TABULIEK
/*Mená a emaily editorov, ktorí už napísali nejaký (aspoň 1) článok. ----------- outer JOIN */
CREATE OR REPLACE VIEW OuterJOIN AS
SELECT UNIQUE EDITOR.PUBLISHINGNAME, ARTICLE.EMAIL
FROM EDITOR
RIGHT OUTER JOIN ARTICLE
ON EDITOR.EMAIL = ARTICLE.EMAIL;

/*Vypise pouzivatelov, ktory napisali nejaký komentár, daný komentár a pocet likov. -------- spajanie 2 tabuliek */
CREATE OR REPLACE VIEW TwoTablesJOIN AS
SELECT u.REGISTEREDUSERNAME,u.EMAIL,a.COMMENTTEXT,a.NUMBEROFLIKES
FROM ARTICLECOMMENT a
JOIN REGISTEREDUSER u
ON a.EMAIL = u.EMAIL
ORDER BY a.ARTICLECOMMENTID;

/*vsetky clanky napisane serior editorom ku ktorym maju pristup uzivatelia s gold membershipom ---------- spajanie 3 tabuliek */
CREATE OR REPLACE VIEW ThreeTablesJOIN AS
SELECT a.TITLE, e.POSITIONNAME, gat.TYPE
FROM ARTICLE a
JOIN EDITOR e ON a.EMAIL=e.EMAIL
JOIN GRANTS_ACCESS_TO gat ON gat.ARTICLEID=a.ARTICLEID
WHERE gat.TYPE='Gold' AND e.POSITIONNAME='senior editor';

------ AGREGACNE FUNKCIE
/*Pocet najnovsich clankov - napisanych v posledny den publikacie na stranke.*/
CREATE OR REPLACE VIEW NewestArticles AS
SELECT COUNT(ARTICLEID) AS "NUMBER OF NEWEST ARTICLES"
FROM ARTICLE
WHERE ARTICLE.DATEPUBLISHED=(SELECT MAX(DATEPUBLISHED) FROM ARTICLE);

/*Najkratsie meno používatela, ktorý neodberá ziaden membership.*/
CREATE OR REPLACE VIEW ShortestNameWithNoMembership AS
SELECT MIN(REGISTEREDUSERNAME)  AS "SHORTEST NAME"
FROM REGISTEREDUSER
WHERE TYPE='NoMembership';

/*Pocet clankov napisanych danym editorom.*/
CREATE OR REPLACE VIEW NumberOfArticlesByEditor AS
SELECT EMAIL, COUNT (EMAIL) AS "NUMBER OF ARTICLES"
FROM ARTICLE
GROUP BY EMAIL
ORDER BY 2 DESC;