Drop TABLE Artikel CASCADE CONSTRAINTS;
Drop TABLE Bestellung CASCADE CONSTRAINTS;
Drop TABLE Nachbestellung CASCADE CONSTRAINTS;

-- Create the tables
CREATE TABLE Artikel (
    Artikel_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Name VARCHAR2(60) NOT NULL,
    Preis NUMBER(10, 2) NOT NULL,
    Bestand_Ist NUMBER NOT NULL,
    Bestand_Min NUMBER NOT NULL,
    Bestand_Max NUMBER NOT NULL
);
CREATE TABLE Bestellung (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Bestellnummer VARCHAR2(9) NOT NULL,
    Pos NUMBER NOT NULL,
    Artikel_ID NUMBER,
    Menge NUMBER NOT NULL,
    Pos_Gesamtpreis NUMBER(10, 2) NOT NULL,
    Gesamtpreis NUMBER(10, 2) NOT NULL,
    Versendet NUMBER(1) NOT NULL,
    FOREIGN KEY (Artikel_ID) REFERENCES Artikel(Artikel_ID)
);
CREATE TABLE Nachbestellung (
    Nachbestellnummer NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Artikel_ID NUMBER,
    Menge NUMBER NOT NULL,
    FOREIGN KEY (Artikel_ID) REFERENCES Artikel(Artikel_ID)
);

INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel1', 8.99, 60, 12, 120);
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel2', 13.49, 25, 6, 60);
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel3', 6.99, 110, 22, 220);
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel4', 23.00, 12, 3, 36);
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel5', 4.75, 80, 16, 160);
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel6', 11.30, 45, 9, 90);
-- Trigger to set Pos before insert on Bestellung
CREATE OR REPLACE TRIGGER set_pos_before_insert
BEFORE INSERT ON Bestellung
FOR EACH ROW
DECLARE
    max_pos NUMBER;
BEGIN
    SELECT NVL(MAX(Pos), 0) INTO max_pos FROM Bestellung WHERE Bestellnummer = :NEW.Bestellnummer;
    :NEW.Pos := max_pos + 1;
END;
/

-- Trigger to update Gesamtpreis after insert on Bestellung
CREATE OR REPLACE TRIGGER update_gesamtpreis_after_insert
AFTER INSERT ON Bestellung
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    gesamt NUMBER(10, 2);
BEGIN
    SELECT SUM(Pos_Gesamtpreis) INTO gesamt FROM Bestellung WHERE Bestellnummer = :NEW.Bestellnummer;
    UPDATE Bestellung SET Gesamtpreis = gesamt WHERE Bestellnummer = :NEW.Bestellnummer;
    COMMIT;
END;
/

SELECT * FROM USER_ERRORS WHERE NAME = 'UPDATE_BESTAND_AFTER_UPDATE';

-- Trigger to update Bestand_Ist after update on Bestellung
CREATE OR REPLACE TRIGGER update_bestand_after_update
AFTER UPDATE ON Bestellung
FOR EACH ROW
BEGIN
    IF :NEW.Versendet = 1 AND :OLD.Versendet = 0 THEN
        UPDATE Artikel SET Bestand_Ist = Bestand_Ist - :NEW.Menge WHERE Artikel_ID = :NEW.Artikel_ID;
        DECLARE
            v_Bestand_Ist NUMBER;
            v_Bestand_Min NUMBER;
            v_Bestand_Max NUMBER;
        BEGIN
            SELECT Bestand_Ist, Bestand_Min, Bestand_Max INTO v_Bestand_Ist, v_Bestand_Min, v_Bestand_Max FROM Artikel WHERE Artikel_ID = :NEW.Artikel_ID;
            IF v_Bestand_Ist < v_Bestand_Min THEN
                INSERT INTO Nachbestellung (Artikel_ID, Menge)
                VALUES (:NEW.Artikel_ID, v_Bestand_Max - v_Bestand_Ist);
            END IF;
        END;
    END IF;
END;
/

-- Trigger to prevent modification of shipped orders
CREATE OR REPLACE TRIGGER prevent_modification_after_shipment
BEFORE UPDATE OR DELETE ON Bestellung
FOR EACH ROW
BEGIN
    IF :OLD.Versendet = 1 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot modify shipped orders');
    END IF;
END;
/

-- Trigger to prevent insert outside business hours
CREATE OR REPLACE TRIGGER prevent_insert_outside_business_hours
BEFORE INSERT ON Bestellung
FOR EACH ROW
DECLARE
    current_time TIMESTAMP;
BEGIN
    SELECT SYSTIMESTAMP INTO current_time FROM DUAL;
    IF TO_CHAR(current_time, 'HH24:MI:SS') NOT BETWEEN '08:00:00' AND '16:00:00' THEN
        RAISE_APPLICATION_ERROR(-20002, 'Orders can only be placed during business hours (08:00 - 16:00)');
    END IF;
END;
/

TRUNCATE TABLE BESTELLUNG;

INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025001', null, 1, 2, 1999.98, 3.98, 0);

INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025001', null, 3, 1, 49.99, 2.97, 0);

INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025001', null, 3, 1, 49.99, 1.33, 0);

INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025001', null, 3, 1, 1.99, 1.33, 0);

INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025002', null, 3, 1, 1.99, 1.33, 0);
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025002', null, 3, 1, 50.99, 1.33, 0);
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet) 
VALUES ('B2025002', null, 3, 1, 60.99, 1.33, 0);


SELECT * FROM BESTELLUNG;