-- Tabelle Artikel
CREATE TABLE Artikel (
    aID INT PRIMARY KEY,
    Titel VARCHAR(255),
    Gewicht DECIMAL(10,2),
    Mindestbestand INT,
    Groesse DECIMAL(10,2)
);

-- Tabelle Regal
CREATE TABLE Regal (
    rID INT PRIMARY KEY,
    Art VARCHAR(255),
    maxGewicht DECIMAL(10,2),
    maxGroesse DECIMAL(10,2)
);

-- Tabelle Gang
CREATE TABLE Gang (
    gID INT PRIMARY KEY
);

-- Tabelle OrtArtikel
CREATE TABLE OrtArtikel (
    aID INT,
    gID INT,
    rID INT,
    Menge INT
   -- FOREIGN KEY (aID) REFERENCES Artikel(aID),
   -- FOREIGN KEY (rID) REFERENCES Regal(rID),
    -- FOREIGN KEY (gID) REFERENCES Gang(gID)
);


-- Einfügen von Gang
INSERT INTO Gang (gID)
VALUES (1),
       (2),
       (3);

-- Einfügen von Regalgrößen
INSERT INTO Regal (rID, Art, maxGewicht, maxGroesse)
VALUES (1, 'Small', 1, 0.5),
       (2, 'Middle', 5, 1),
       (3, 'Big', 10, 5),
       (4, 'Small', 1, 0.5),
       (5, 'Middle', 5, 1),
       (6, 'Big', 10, 5),
       (7, 'Small', 1, 0.5),
       (8, 'Middle', 5, 1),
       (9, 'Big', 10, 5),
       (10, 'Small', 1, 0.5),
       (11, 'Middle', 5, 1),
       (12, 'Big', 10, 5),
       (13, 'Middle', 5, 1),
       (14, 'Big', 10, 5),
       (15, 'Big', 10, 5),
       (16, 'Middle', 5, 1),
       (17, 'Small', 1, 0.5),
       (18, 'Small', 1, 0.5),
       (19, 'Big', 10, 5),
       (20, 'Middle', 5, 1);
       
       
       

-- Einfügen von Beispielartikeln
INSERT INTO Artikel (aID, Titel, Gewicht, Groesse, Mindestbestand)
VALUES (1, 'Artikel 1', 0.5, 0.1, 1000),
       (2, 'Artikel 2', 2, 0.3, 20),
       (3, 'Artikel 3', 2.5, 1.5, 1),
       (4, 'Artikel 4', 1.2, 0.8, 500),
       (5, 'Artikel 5', 3.7, 1.2, 100),
       (6, 'Artikel 6', 0.9, 0.5, 2000),
       (7, 'Artikel 7', 2.3, 0.7, 50),
       (8, 'Artikel 8', 1.8, 1.5, 10),
       (9, 'Artikel 9', 2.1, 0.9, 100),
       (10, 'Artikel 10', 1.5, 0.3, 30),
       (11, 'Artikel 11', 4.6, 2.3, 5),
       (12, 'Artikel 12', 0.7, 0.2, 500),
       (13, 'Artikel 13', 3.2, 1.8, 1000),
       (14, 'Artikel 14', 1.1, 0.4, 50),
       (15, 'Artikel 15', 2.8, 1.6, 200);


-- Artikel dem Regal zuweisen
INSERT INTO OrtArtikel (aID, gID, rID, Menge)
VALUES (1, 1, 1, 1),
       (2, 1, 2, 0),
       (3, 2, 1, 1),
       (3, 2, 3, 3),
       (3, 3, 2, 5),
       (1, 3, 1, 2),
       (4, 2, 2, 1),
       (5, 1, 3, 2),
       (6, 1, 1, 3),
       (7, 2, 2, 1),
       (8, 3, 3, 2),
       (9, 2, 1, 1),
       (10, 3, 2, 3),
       (11, 1, 1, 2),
       (12, 2, 3, 1),
       (13, 3, 1, 4);



/*
-- Abfragen --
-- Wo ist ein bestimmter Artikel? --
SELECT g.gID, r.rID
FROM Gang g
JOIN OrtArtikel o ON g.gID = o.gID
JOIN Regal r ON o.rID = r.rID
JOIN Artikel a ON o.aID = a.aID
WHERE a.Titel = 'Artikel 3';

-- Wie viele Artikel sind insgesamt auf Lager? --
SELECT SUM(Menge) AS ArtikelAufLager
FROM OrtArtikel;

-- Summe Menge pro Artikel --
SELECT a.Titel, SUM(oa.Menge) AS SummeMenge
FROM OrtArtikel oa
JOIN Artikel a ON oa.aID = a.aID
WHERE a.Titel = 'Artikel 2'
GROUP BY a.Titel;

-- Wie viele Artikel sind in einem bestimmten Gang.Regal? --
SELECT Menge AS AnzahlArtikel
FROM OrtArtikel
WHERE gID = <gewünschte_gID> AND rID = <gewünschte_rID>;

-- Liegt der Mindestbestand für Artikel vor? --
SELECT a.Titel, 
       CASE WHEN SUM(o.Menge) <= a.Mindestbestand THEN 'Ja' ELSE 'Nein' END AS Mindestbestand
FROM Artikel a
LEFT JOIN OrtArtikel o ON a.aID = o.aID
GROUP BY a.aID, a.Titel, a.Mindestbestand;

-- Wie viele Regale nimmt ein bestimmter Artikel ein? --
SELECT COUNT(DISTINCT CONCAT(o.rID, '.', o.gID)) AS AnzahlRegale
FROM OrtArtikel o
JOIN Artikel a ON o.aID = a.aID
WHERE a.Titel = 'Artikel_Titel';

-- Welche Regale sind noch frei? --
SELECT g.gID, r.rID
FROM Gang g
CROSS JOIN Regal r
LEFT JOIN OrtArtikel o ON g.gID = o.gID AND r.rID = o.rID
WHERE o.aID IS NULL;




-- Anzahl der Produkte anpassen, um Gewicht/Größe innerhalb der Grenzwerte zu halten. Verbleibendes Limit für weitere Produkte berechnen --
SELECT oa.aID, oa.rID, oa.gID, a.Titel,
       CASE
           WHEN (r.maxGewicht - COALESCE(SUM(a.Gewicht * oa.Menge), 0)) = a.Gewicht
               AND (r.maxGroesse - COALESCE(SUM(a.Groesse * oa.Menge), 0)) = a.Groesse
           THEN FLOOR((r.maxGewicht - COALESCE(SUM(a.Gewicht * oa.Menge), 0)) / a.Gewicht)
           ELSE -FLOOR((COALESCE(SUM(a.Gewicht * oa.Menge), 0) - r.maxGewicht) / a.Gewicht)
       END AS MengeDieImRegalAngepasstWerdenMuss
FROM OrtArtikel oa
JOIN Artikel a ON oa.aID = a.aID
JOIN (SELECT rID, maxGewicht, maxGroesse FROM Regal) r ON oa.rID = r.rID
GROUP BY oa.aID, oa.rID, oa.gID, a.Titel, r.maxGewicht, r.maxGroesse;

*/











