-- Database: Cofetarie

-- DROP DATABASE IF EXISTS "Cofetarie";

-- DROP TABLES
DROP TABLE IF EXISTS Livrari CASCADE;
DROP TABLE IF EXISTS Furnizori CASCADE;
DROP TABLE IF EXISTS DetaliiComanda CASCADE;
DROP TABLE IF EXISTS Comenzi CASCADE;
DROP TABLE IF EXISTS Angajati CASCADE;
DROP TABLE IF EXISTS ProdusCategorie CASCADE;
DROP TABLE IF EXISTS Produse CASCADE;
DROP TABLE IF EXISTS Clienti CASCADE;

-- Tabela pentru angajați
CREATE TABLE Angajati (
    ID_Angajat INT,
    Nume VARCHAR(100),
    Prenume VARCHAR(100),
    Telefon VARCHAR(15),
    Email VARCHAR(100),
    DataAngajare DATE,
    Salariu DECIMAL(10, 2),
    CONSTRAINT PK_Angajati PRIMARY KEY (ID_Angajat)
);

-- Tabela pentru clienți
CREATE TABLE Clienti (
    ID_Client INT ,
    Nume VARCHAR(100),
    Prenume VARCHAR(100),
    Email VARCHAR(100),
    Telefon VARCHAR(15),
    CONSTRAINT PK_Clienti PRIMARY KEY (ID_Client)
);

--Tabel pentru Furnizori
CREATE TABLE Furnizori (
    ID_Furnizor INT,
    Nume VARCHAR(100),
    Adresa VARCHAR(255),
    Telefon VARCHAR(15),
    Email VARCHAR(100),
    CONSTRAINT PK_Furnizori PRIMARY KEY (ID_Furnizor)
);

-- Tabela pentru relația produs-categorie 
CREATE TABLE ProdusCategorie (
    ID_Categorie INT,
    NumeCategorie VARCHAR(100),
    CONSTRAINT PK_ProdusCategorie PRIMARY KEY (ID_Categorie)

);
-- Tabela pentru produse
CREATE TABLE Produse (
    ID_Produs INT ,
	ID_Categorie INT,
    ID_Furnizor INT,
    NumeProdus VARCHAR(100),
    Descriere TEXT,
    Pret DECIMAL(10, 2),
    Stoc INT,
    CONSTRAINT PK_Produs PRIMARY KEY (ID_Produs),
    CONSTRAINT FK_ProdusFurnizor FOREIGN KEY (ID_Furnizor) REFERENCES Furnizori(ID_Furnizor) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT FK_ProdusCategorie_Produs FOREIGN KEY (ID_Categorie) REFERENCES ProdusCategorie(ID_Categorie) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Tabela pentru comenzi
CREATE TABLE Comenzi (
    ID_Comanda INT ,
    ID_Client INT,
    ID_Angajat INT,
    DataComanda DATE,
    CONSTRAINT PK_Comenzi PRIMARY KEY (ID_Comanda),
    CONSTRAINT FK_Comenzi_Clienti FOREIGN KEY (ID_Client) REFERENCES Clienti(ID_Client) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT FK_Comenzi_Angajat FOREIGN KEY (ID_Angajat) REFERENCES Angajati(ID_Angajat) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Tabela pentru detaliile comenzii
CREATE TABLE DetaliiComanda (
    ID_DetaliiComanda INT,
    ID_Comanda INT,
    ID_Produs INT,
    Cantitate INT,
    Pret DECIMAL(10, 2),
    CONSTRAINT PK_DetaliiComanda PRIMARY KEY (ID_DetaliiComanda),
    CONSTRAINT FK_DetaliiComanda_Comenzi FOREIGN KEY (ID_Comanda) REFERENCES Comenzi(ID_Comanda) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_DetaliiComanda_Produse FOREIGN KEY (ID_Produs) REFERENCES Produse(ID_Produs) ON DELETE CASCADE ON UPDATE CASCADE

);


-- Tabela pentru livrări
CREATE TABLE Livrari (
    ID_Livrare INT ,
    ID_Produs INT,
	ID_DetaliiComanda INT,
    DataLivrare DATE,
    Cantitate INT,
    PretUnitar DECIMAL(10, 2),
    CONSTRAINT PK_Livarari PRIMARY KEY (ID_Livrare),
    CONSTRAINT FK_Livrari_Produse FOREIGN KEY (ID_Produs) REFERENCES Produse(ID_Produs) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT FK_Livrari_DetaliiComanda FOREIGN KEY (ID_DetaliiComanda) REFERENCES DetaliiComanda(ID_DetaliiComanda) ON DELETE CASCADE ON UPDATE CASCADE
);

--Popularea tabelelor

-- Angajati
INSERT INTO Angajati (ID_Angajat, Nume, Prenume, Telefon, Email, DataAngajare, Salariu) 
VALUES 
(1, 'Popescu', 'Ion', '0721123456', 'ion.popescu@example.com', '2023-05-10', 3000.00),
(2, 'Ionescu', 'Maria', '0732123456', 'maria.ionescu@example.com', '2023-06-15', 3200.00),
(3, 'Constantinescu', 'Ana', '0743123456', 'ana.constantinescu@example.com', '2023-07-20', 3100.00),
(4, 'Dumitrescu', 'Mihai', '0754123456', 'mihai.dumitrescu@example.com', '2023-08-25', 3500.00),
(5, 'Georgescu', 'Elena', '0765123456', 'elena.georgescu@example.com', '2023-09-30', 3300.00);


--  Clienti
INSERT INTO Clienti (ID_Client, Nume, Prenume, Email, Telefon) 
VALUES 
(1, 'Pop', 'Andrei', 'andrei.pop@example.com', '0722111111'),
(2, 'Ionescu', 'Ana', 'ana.ionescu@example.com', '0733222222'),
(3, 'Constantin', 'Maria', 'maria.constantin@example.com', '0744333333'),
(4, 'Dumitru', 'Mihai', 'mihai.dumitru@example.com', '0755444444'),
(5, 'Georgescu', 'Elena', 'elena.georgescu@example.com', '0766555555'),
(6, 'Stanciu', 'Cristina', 'cristina.stanciu@example.com', '0777666666'),
(7, 'Radu', 'Alexandru', 'alexandru.radu@example.com', '0788777777'),
(8, 'Voinea', 'Laura', 'laura.voinea@example.com', '0799888888'),
(9, 'Iftimie', 'Marian', 'marian.iftimie@example.com', '0700999999'),
(10, 'Dragomirescu', 'Andreea', 'andreea.dragomirescu@example.com', '0710101010');

--  Furnizori
INSERT INTO Furnizori (ID_Furnizor, Nume, Adresa, Telefon, Email) 
VALUES 
(1, 'Furnizorul Ciocolata', 'Adresa Furnizorului Ciocolata', '0711111111', 'furnizor1@example.com'),
(2, 'Furnizorul Deserturi', 'Adresa Furnizorului Deserturi', '0722222222', 'furnizor2@example.com'),
(3, 'Furnizorul Biscuiti', 'Adresa Furnizorului Biscuiti', '0733333333', 'furnizor3@example.com'),
(4, 'Furnizorul Vafe si Croissante', 'Adresa Furnizorului Vafe si Croissante', '0744444444', 'furnizor4@example.com'),
(5, 'Furnizorul Produse Raw', 'Adresa Furnizorului Produse Raw', '0755555555', 'furnizor5@example.com');

-- ProdusCategorie
INSERT INTO ProdusCategorie (ID_Categorie, NumeCategorie) 
VALUES 
(1, 'Torturi și prăjituri'),
(2, 'Produse de patiserie'),
(3, 'Produse de panificație'),
(4, 'Dulciuri fine'),
(5, 'Produse raw/vegane');

--  Produse
INSERT INTO Produse (ID_Produs,ID_Categorie, ID_Furnizor, NumeProdus, Descriere, Pret, Stoc) 
VALUES 
(1, 1, 1, 'Tort cu ciocolată', 'Tort delicios cu umplutură de ciocolată și decor de ciocolată topită.', 50.00, 100),
(2, 5, 5, 'Vanilie Cremoasă', 'Prăjitură fină cu cremă de vanilie și glazură de caramel.', 40.00, 120),
(3, 1, 4, 'Biscuiți integrali', 'Biscuiți crocanți făcuți din ingrediente bio, ideal pentru gustări sănătoase.', 20.00, 200),
(4, 2, 4, 'Croissant cu ciocolată', 'Croissant proaspăt copt umplut cu ciocolată caldă.', 15.00, 150),
(5, 1, 1, 'Tarta cu fructe de pădure', 'Tartă răcoritoare cu fructe de pădure proaspete și cremă de vanilie.', 35.00, 80),
(6, 4, 2, 'Macarons assortate', 'Delicioase macarons în diferite arome: ciocolată, vanilie, zmeură și lămâie.', 2.50, 150),
(7, 2, 3, 'Eclere cu frișcă', 'Eclere moi umplute cu frișcă proaspătă și glazurate cu ciocolată fină.', 3.00, 250),
(8, 1, 1, 'Cheesecake clasic', 'Cheesecake delicios cu blat de biscuiți și topping de fructe de pădure.', 45.00, 100),
(9, 5, 5, 'Produs energizant raw', 'Baton raw vegan bogat în nutrienți și energie, perfect pentru un boost de energie.', 10.00, 150),
(10, 1, 2, 'Prăjitură cu mere și scorțișoară', 'Prăjitură cu mere proaspete și scorțișoară, ideală pentru sezonul rece.', 30.00, 90),
(11, 2, 2, 'Croissant cu ciocolada alba', 'Croissant proaspăt copt umplut cu ciocolată alba caldă.', 15.00, 150),
(12, 3, 3, 'Eclere cu caramel sarat', 'Eclere moi umplute cu caramel sarat.', 4.00, 250),
(13, 1, 5, 'Tort cu capusune', 'Tort delicios cu umplutură de dulceata de capusune și decor de ciocolată topită.', 45.00, 100);

--Comenzi
INSERT INTO Comenzi (ID_Comanda, ID_Client, ID_Angajat, DataComanda) VALUES
(1, 1, 3, '2024-01-15'),
(2, 2, 4, '2024-02-20'),
(3, 3, 4, '2024-03-25'),
(4, 4, 1, '2024-04-30'),
(5, 5, 4, '2024-05-05'),
(6, 6, 2, '2024-06-10'),
(7, 7, 5, '2024-07-15'),
(8, 8, 3, '2024-08-20'),
(9, 9, 2, '2024-09-25'),
(10, 10, 1, '2024-10-29'),
(11, 10, 2, '2024-10-30'),
(12, 10, 2, '2024-10-03'),
(13, 10, 2, '2024-10-07'),
(14, 1, 2, '2024-10-07'),
(15, 3, 2, '2024-10-20'),
(16, 8, 1, '2024-10-05');

--DetaliiComanda
INSERT INTO DetaliiComanda (ID_DetaliiComanda, ID_Comanda, ID_Produs, Cantitate, Pret) VALUES
(1, 1, 1, 2, 100),
(2, 2, 2, 2, 80.00),
(3, 3, 3, 5, 150.00),
(4, 4, 4, 3, 95.00),
(5, 5, 5, 4, 140.00),
(6, 6, 6, 2, 5.00),
(7, 7, 7, 1, 3.00),
(8, 8, 8, 2, 90.00),
(9, 9, 9, 8, 80),
(10, 10, 10, 1, 30.00),
(11, 11, 12, 1, 40.00),
(12, 12, 12, 3, 120.00),
(13, 13, 2, 1, 40.00),
(14, 14, 11, 1, 15.00),
(15, 15, 12, 1, 4.00),
(16, 16, 13, 2, 90.00);

--Livrari
INSERT INTO Livrari (ID_Livrare, ID_Produs, ID_DetaliiComanda, DataLivrare, Cantitate, PretUnitar) VALUES
(1, 1, 1,'2024-01-15', 2, 100),
(2, 2, 2,'2024-02-20', 2, 80.00),
(3, 3, 3,'2024-03-25', 5, 150.00),
(4, 4, 4,'2024-04-30', 3, 95.00),
(5, 5, 5,'2024-05-05', 4, 140.00),
(6, 6, 6,'2024-06-10', 2, 5.00),
(7, 7, 7,'2024-07-15', 1, 3.00),
(8, 8, 8,'2024-08-20', 2, 90.00),
(9, 9, 9,'2024-09-25', 8, 80),
(10, 10, 10,'2024-10-29', 1, 30.00),
(11, 2, 11,'2024-10-30', 1, 40.00),
(12, 2, 12,'2024-10-03', 3, 120.00),
(13, 3, 13,'2024-10-07', 1, 40.00),
(14, 11, 14,'2024-10-07', 1, 15.00),
(15, 12, 15,'2024-10-20', 1, 4.00),
(16, 13, 16,'2024-10-05', 2, 90.00);

----------------------------------------------------------------------------------------------------------------------------------------
--Interogarii
--Pavlenko Oleksandra
--1.Extrageti suma încasată de fiecare angajat în anul 2024
SELECT a.Nume, a.Prenume, SUM(dc.Pret) AS Suma_Incasata
FROM  Angajati a
INNER JOIN Comenzi c ON a.ID_Angajat = c.ID_Angajat
INNER JOIN DetaliiComanda dc ON c.ID_Comanda = dc.ID_Comanda
INNER JOIN Livrari l ON dc.ID_DetaliiComanda = l.ID_DetaliiComanda
WHERE EXTRACT(YEAR FROM c.DataComanda) = 2024
GROUP BY a.ID_Angajat, a.Nume, a.Prenume
ORDER BY Suma_Incasata DESC;

--2.Extrage furnizorul cu cele mai multe produse furnizate:
SELECT Nume, NumarProduseFurnizate
FROM (SELECT Furnizori.Nume, COUNT(Produse.ID_Produs) AS NumarProduseFurnizate,
        RANK() OVER (ORDER BY COUNT(Produse.ID_Produs) DESC) AS SupplierRank
    FROM  Furnizori
    JOIN Produse ON Furnizori.ID_Furnizor = Produse.ID_Furnizor
    GROUP BY  Furnizori.Nume) AS RankedSuppliers
WHERE SupplierRank = 1;

--3.Extrageti angajatii care a preluat mai mult de 2 comenzi in luna octombrie?
SELECT a.ID_Angajat, a.Nume, a.Prenume, COUNT(c.ID_Comanda) AS Numar_Comenzi
FROM Angajati a
JOIN Comenzi c ON a.ID_Angajat = c.ID_Angajat
WHERE EXTRACT(MONTH FROM c.DataComanda) = 10
GROUP BY a.ID_Angajat, a.Nume, a.Prenume
HAVING COUNT(c.ID_Comanda) > 2;

--4.Extrageti produsul preferat din luna octombrie?
WITH NumarComenziPeProdus AS (
    SELECT p.ID_Produs, p.NumeProdus, COUNT(dc.ID_Comanda) AS NumarComenzi
    FROM Produse p
    JOIN DetaliiComanda dc ON p.ID_Produs = dc.ID_Produs
    JOIN Comenzi c ON dc.ID_Comanda = c.ID_Comanda
    WHERE EXTRACT(MONTH FROM c.DataComanda) = 10
    GROUP BY p.ID_Produs, p.NumeProdus
)
SELECT NumeProdus, NumarComenzi
FROM (
    SELECT *,
           RANK() OVER (ORDER BY NumarComenzi DESC) AS Ranking
    FROM NumarComenziPeProdus
) AS RankedProducts
WHERE Ranking = 1;


--Cojocaru Diana
--1.Care sunt clienti care au comandat Cheesecake clasic?
SELECT nume, prenume
FROM clienti c1
	INNER JOIN comenzi c2 ON c1.id_client = c2.id_comanda
	INNER JOIN  detaliicomanda c3 ON c2.id_comanda = c3.id_detaliicomanda
	INNER JOIN  produse c4 ON c3.id_detaliicomanda = c4.id_produs
WHERE c4.numeprodus = 'Cheesecake clasic'
ORDER BY c4.numeprodus

--2.Aflati cantitatea vanduta fiecarui client?
SELECT nume, SUM(cantitate) AS client_cantitate
FROM clienti c1
     INNER JOIN comenzi c2 ON c1.id_client = c2.id_comanda
	INNER JOIN  detaliicomanda c3 ON c2.id_comanda = c3.id_detaliicomanda
GROUP BY nume
ORDER BY 2 DESC ;

--3.Afisati top 3 produse cu cel mai mare pretunitar?
WITH pret_rank AS (
    SELECT numeprodus, 
        RANK () OVER (ORDER BY pretunitar DESC) AS pret_rank
	FROM produse t1  INNER JOIN livrari t2 ON t1.id_produs = t2.id_livrare
    )
SELECT * FROM pret_rank
WHERE pret_rank <= 3 ;

--4.Care sunt produsele care au fost comandate la data 25.03.2024?
SELECT numeprodus
FROM produse p
INNER JOIN detaliicomanda d ON p.id_produs = d.id_detaliicomanda
INNER JOIN comenzi c ON d.id_detaliicomanda = c.id_comanda
WHERE c.datacomanda = '2024-03-25';