/*
	Am ales sa modelez o baza de date pentru o retea de socializare
	Baza de date va contine 5 tabele:
		users - pentru datele utilizatorilor
		following - pentru a retine informatii utilizatorii ce urmaresc activitatea altor utilizatori
		photos - pentru a retine informatii despre fotografiile incarcate de utilizatori
		comments - pentru a retine comentariile facute de catre utilizatori pentru anumite fotografii incarcate
		messages - pentru a retine mesajele scrise de utilizatori

*/

--create database VSilviu_SocialNetwork;
use VSilviu_SocialNetwork;

--Tabelul "user" va contine date despre fiecare utilizator
CREATE TABLE users(
	id int PRIMARY KEY IDENTITY, --id-ul unic al utilizatorului
	name varchar(30) NOT NULL, --numele utilizatorului maxim 30 de caractere
	pass varchar(30) NOT NULL, --parola utilizatorului maxim 30 de caractere
	dateOfBirth date, -- data de nastere
);

------
CREATE TABLE following(
	id int PRIMARY KEY IDENTITY, --id-ul unic pentru fiecare urmarie
	followerId int, --id-ul utilizatorului urmaritor
	followedId int, --id-ul utilizatorului urmarit
);

--Tabelul "messages" contine date despre mesajele utilizatorilor
CREATE TABLE messages(
	id int PRIMARY KEY IDENTITY, --id-ul unic pentru fiecare mesaj
	userId int, --id-ul utilizatorului ce a scris mesajul
	content varchar(1000) -- continutul mesajului, maxim 1000 de caractere
);

--Tabelul "photos" contine date despre fotografiile incarcate de utilizator
CREATE TABLE photos(
	id int PRIMARY KEY IDENTITY, -- id-ul unic al fiecare fotografii
	userId int NOT NULL, -- id-ul utilizatorului ce a incarcat fotografia
	fileName varchar(300) NOT NULL, --numele fisierului corespunzator fotografiei
	fileSize int -- dimensiunea fisierului
);

--Tabelul "comments" contine date despre comentariile adaugate de utilizatori pentru fotgrafiile incarcate
CREATE TABLE comments(
	id int PRIMARY KEY IDENTITY, -- id-ul unic al fiecarui comentariu
	userId int, -- id-ul utilizatorului ce a comentat la fotografie
	content varchar(500), --continutul comentariului, maxim 500 de carcatere
	photoId int -- id-ul fotografiei ce contine comentariul
);


--Inseare date in tabelul users
INSERT INTO users VALUES('Silviu0', 'myPass', '19950611');
INSERT INTO users VALUES('Andrei0', 'myPass', '19950721');
INSERT INTO users VALUES('Cosmin', 'pass123', '19941112');

--Inserare date in tabelul following
INSERT INTO following VALUES(1,2); -- utilizatorul cu id-ul 1 il urmareste pe utilizatorul cu id-ul 2
INSERT INTO following VALUES(1,3); -- utilizatorul cu id-ul 1 il urmareste pe utilizatorul cu id-ul 3
INSERT INTO following VALUES(2,3); -- utilizatorul cu id-ul 1 il urmareste pe utilizatorul cu id-ul 3

--Inserare date in tabelul "messages"
INSERT INTO messages VALUES(1,'Salutare prieteni');
INSERT INTO messages VALUES(1,'HELLO');
INSERT INTO messages VALUES(2,'HELLO WORLD');

--Inserare date in tabelul "photos"
INSERT INTO photos VALUES(2,'myPic.jpg',300);
INSERT INTO photos VALUES(2,'myPic2.jpg',650);
INSERT INTO photos VALUES(2,'myPic2.jpg',650);

--Inseare date in tabelul "comments"
INSERT into comments VALUES(1,'Imi place foarte mult fotografia', 1);
INSERT into comments VALUES(1,'As vrea sa vad mai multe fotografii cu tine', 1);
INSERT into comments VALUES(1,'Felicitari', 2);

-- Selectarea tutror utilizatorilor care sunt nascuti dupa 1 ianuarie 1995
SELECT * FROM users WHERE dateOfBirth > '19950101';

-- Selecteaza numarul urmaririlor
SELECT COUNT(id) FROM following ;

-- Selectarea primelor 5 mesaje in ordine alfabetica crescatoare
SELECT TOP(5) * FROM messages ORDER BY content ASC;

-- Selectarea dimensiunii totale a fotografiilor incarcate de utilizatorul cu id = 2
SELECT SUM(fileSize) FROM photos WHERE userId=2;

-- Selectarea utilizatorilor care au incarcat fotografii cu dimensiuni mai mari de 500
SELECT users.id, users.name, photos.fileName, photos.fileSize FROM users INNER JOIN photos ON users.id = photos.userId WHERE photos.fileSize > 500; -- Selectarea utilizatorilor care au incarcat fotografii cu dimensiuni mai mari de 500

-- Selectarea numarului utilizatorilor ce au comentat la fotografia cu id-ul 1
SELECT COUNT(DISTINCT userId) FROM comments WHERE userId='1';


--########################################## TEMA 2 ################################################################33

ALTER TABLE comments ADD CONSTRAINT fk_userId FOREIGN KEY (userId) REFERENCES users(id);
ALTER TABLE following ADD CONSTRAINT fk_followerId FOREIGN KEY (followerId) REFERENCES users(id);
ALTER TABLE following ADD CONSTRAINT fk_followedId FOREIGN KEY(followedId) REFERENCES users(id);
ALTER TABLE photos ADD CONSTRaINT fk_photos_userId FOREIGN KEY(userId) REFERENCES users(id);
ALTER TABLE photos ADD date date; -- adaugarea coloeanei "date" care va retine data cand a fost incarcata o fotografie

INSERT INTO comments VALUES(3,'Frumoasa fotografie',1);
SELECT * FROM comments;
SELECT * FROM photos;
SELECT * FROM following;
SELECT COUNT(id) FROM comments GROUP BY userId;
UPDATE photos SET date='20160811' WHERE id=1;
UPDATE photos SET date='20160621' WHERE id=2;
UPDATE photos SET userId=1 WHERE id=3;

--Selectarea fotografiilor incarcate de utilizatorul cu id-ul 2 in ordine crescatoare a datei la care au fost adaugate
SELECT * FROM photos WHERE userId = 2 ORDER BY date;

--Selectarea tuturor fotografiilor care au numele fisierului incepand cu "my"
SELECT * FROM photos WHERE fileName LIKE 'MY%';

--Selectarea tuturor fotografiilor cu dimensiuni cuprinse intre 400 si 700
SELECT * FROM photos WHERE fileSize BETWEEN 400 AND 700;

--Selectarea utilizatorilor grupati dupa numarul de urmaritori ordonati crescator dupa numarul de urmaritori
SELECT followedId, COUNT(followedId) as number_of_followers FROM following GROUP BY followedId ORDER BY number_of_followers;

--Selectarea utilizatorilor care au incarcat fotografii cu dimensiuni egale cu cea mai mare dimensiune existenta
SELECT users.id, users.name FROM users INNER JOIN photos ON users.id= photos.userId WHERE photos.fileSize = (SELECT MAX(fileSize) from photos);

--Selectarea tuturor utilizatorilor urmariti de utilizatorul cu id-ul 2
SELECT users.id, users.name FROM users INNER JOIN following ON users.id = following.followedId WHERE followerId = 2;

--Selectarea tuturor utilizatorilor ce il urmaresc pe utilizatorul cu id-ul 3
SELECT users.id, users.name FROM users INNER JOIN following ON users.id = following.followerId WHERE followedId = 3;

--Selectarea tuturor utilizatorilor ce au comentat la fotografia cu id-ul 1
SELECT users.id, users.name FROM users INNER JOIN comments ON users.id = comments.userId WHERE comments.photoId = 1;