CREATE OR REPLACE TRIGGER checkitemsales
BEFORE INSERT ON sales_items FOR EACH ROW
    DECLARE
    itval NUMBER;
    noitems exception;
BEGIN
    SELECT items.QUANTITY_ITEMS INTO itval FROM items WHERE items.id_items = :NEW.ID_ITEMS;
    IF (itval - :NEW.QUANTITY_SALES_ITEMS) < 0 THEN
        raise noitems;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER updateQuantity
AFTER INSERT ON sales_items FOR EACH ROW
BEGIN
    UPDATE items SET quantity_items = quantity_items - :NEW.QUANTITY_SALES_ITEMS WHERE items.id_items = :NEW.ID_ITEMS;
END;
/

CREATE OR REPLACE FUNCTION DOBUYFUNC(
    iname IN clients.name_clients%TYPE,
    isurname IN clients.surname_clients%TYPE,
    iemail IN clients.email_clients%TYPE,
    iaddress IN clients.address_clients%TYPE,
    iphone IN clients.phone_number_clients%TYPE,
    basketPrice IN sales.sales_price%TYPE,
    itemsvalues IN VARCHAR
) RETURN NUMBER
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    rescntcl NUMBER(1);
    resclid clients.id_clients%TYPE;
    salesid sales.id_sales%TYPE;
    itsid sales_items.id_items%TYPE;
    itsval sales_items.quantity_sales_items%TYPE;
BEGIN
    IF iname IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Empty name');
    RETURN 0;
    END IF;
    IF isurname IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Empty surname');
    RETURN 0;
    END IF;
    IF iemail IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Empty email');
    RETURN 0;
    END IF;
    IF iaddress IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Empty address');
    RETURN 0;
    END IF;
    IF iphone IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Empty phone number');
    RETURN 0;
    END IF;

    SELECT COUNT(clients.id_clients) INTO rescntcl FROM clients WHERE clients.name_clients = iname AND clients.surname_clients = isurname AND clients.email_clients = iemail AND clients.address_clients = iaddress AND clients.phone_number_clients = iphone;
    IF (rescntcl = 0) THEN
        INSERT INTO clients VALUES(clients_seq.NEXTVAL, iname, isurname, iemail, iaddress, iphone);
    END IF;
    SELECT id_clients INTO resclid FROM clients WHERE clients.name_clients = iname AND clients.surname_clients = isurname AND clients.email_clients = iemail AND clients.address_clients = iaddress AND clients.phone_number_clients = iphone;
    IF (resclid > 0) THEN
    dbms_output.put_line('Company code no.'||resclid);
        INSERT INTO sales VALUES(sales_seq.NEXTVAL, NULL, resclid, NULL, basketPrice, 0);
        SELECT MAX(sales.id_sales) INTO salesid FROM sales WHERE sales.id_clients = resclid AND sales.sales_price = basketPrice;
        IF (salesid > 0) THEN
            FOR i IN
                (SELECT level,
                TRIM(regexp_substr(itemsvalues, '[^;]+', 1, LEVEL)) str
                FROM dual
                CONNECT BY regexp_substr(itemsvalues , '[^;]+', 1, LEVEL) IS NOT NULL
                )
            LOOP
              SELECT regexp_substr(i.str, '[^,]+', 1, 1), regexp_substr(i.str, '[^,]+', 1, 2) INTO itsid,itsval FROM dual;
              INSERT INTO sales_items (id_sales, id_items, quantity_sales_items) VALUES(salesid, itsid, itsval);
            END LOOP;
            COMMIT;
      RETURN 1;
        END IF;
    ROLLBACK;
    RETURN 0;
    END IF;
  ROLLBACK;
  RETURN 0;
END;
/

CREATE OR REPLACE FUNCTION DECLINEPROVIDEFUNC(id IN provides.id_provides%TYPE)
RETURN NUMBER
IS
CURSOR declineprovidecursor IS
SELECT PROVIDES_ITEMS.ID_ITEMS, PROVIDES_ITEMS.QUANTITY_PROVIDES_ITEMS FROM PROVIDES_ITEMS JOIN items ON items.id_items = provides_items.id_items WHERE PROVIDES_ITEMS.ID_PROVIDES = id;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    FOR i IN declineprovidecursor LOOP
    EXIT WHEN declineprovidecursor%notfound;
        UPDATE items SET QUANTITY_ITEMS = QUANTITY_ITEMS - i.QUANTITY_PROVIDES_ITEMS WHERE ID_ITEMS = i.ID_ITEMS;
    END LOOP;
    DELETE FROM PROVIDES_ITEMS WHERE ID_PROVIDES = id;
    DELETE FROM provides WHERE id_provides = id;
    COMMIT;
    RETURN 1;
    exception
        WHEN OTHERS THEN
            ROLLBACK;
            RETURN 0;
END;
/