CREATE TABLE customer
(
  id      int AUTO_INCREMENT
    PRIMARY KEY,
  name    varchar(30)                         NULL,
  surname varchar(30)                         NULL,
  nip     int                                 NULL,
  created timestamp DEFAULT CURRENT_TIMESTAMP NULL
);

CREATE TABLE bonus_card
(
  customer_id int   NULL,
  balance     int   NULL,
  discount    float NULL,
  card_number int   NULL,
  CONSTRAINT bonus_card_customer_id_fk
  FOREIGN KEY (customer_id) REFERENCES customer (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER negativeFieldsBonusInsert
  BEFORE INSERT
  ON bonus_card
  FOR EACH ROW
  BEGIN
    #
    #     IF (new.balance < 0 || new.discount < 0)
    #     THEN
    #       SIGNAL SQLSTATE '45000'
    #       SET MESSAGE_TEXT = 'Fields can`t be negative.';
    #
    #     END IF;
  END;

CREATE TRIGGER negativeFieldsBonusUpdate
  BEFORE UPDATE
  ON bonus_card
  FOR EACH ROW
  BEGIN

    #     IF (new.balance < 0 || new.discount < 0)
    #     THEN
    #       SIGNAL SQLSTATE '45000'
    #       SET MESSAGE_TEXT = 'Fields can`t be negative.';
    #
    #     ELSEIF (new.balance < old.balance)
    #       THEN
    #         SIGNAL SQLSTATE '45000'
    #         SET MESSAGE_TEXT = 'New balance can`t be smaller than previous one.';
    #     END IF;
  END;

CREATE TRIGGER createCard
  AFTER INSERT
  ON customer
  FOR EACH ROW
  BEGIN

    INSERT INTO bonus_card (customer_id, balance, discount) VALUES (new.id, 0, 0);

  END;

CREATE TRIGGER nipControllerInsert
  BEFORE INSERT
  ON customer
  FOR EACH ROW
  IF (!(new.nip REGEXP '^[0-9]+$' && CHAR_LENGTH(new.nip) != 10))
  THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Wrong nip.';

  END IF;

CREATE TRIGGER nipControllerUpdate
  BEFORE UPDATE
  ON customer
  FOR EACH ROW
  BEGIN
    IF (!(new.nip REGEXP '^[0-9]+$' && CHAR_LENGTH(new.nip) != 10))
    THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Wrong nip.';

    END IF;
  END;

CREATE TABLE logs
(
  worker_id int                                                                 NULL,
  operation enum ('sale', 'delivery', 'adding_customer', 'creating_bonus_card') NULL,
  time      datetime                                                            NULL
);

CREATE TABLE product
(
  name   varchar(30) NOT NULL,
  price  float       NULL,
  tax    float       NOT NULL,
  code   int         NOT NULL
    PRIMARY KEY,
  amount int         NOT NULL
);

CREATE TABLE delivery_element
(
  id         int AUTO_INCREMENT
    PRIMARY KEY,
  product_id int         NULL,
  name       varchar(30) NULL,
  tax        float       NULL,
  price      float       NULL,
  amount     int         NULL,
  CONSTRAINT delivery_elements_product_code_fk
  FOREIGN KEY (product_id) REFERENCES product (code)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER deliveryConnector
  AFTER INSERT
  ON delivery_element
  FOR EACH ROW
  BEGIN

    INSERT INTO delivery_deliveryelement (delivery, delivery_element)
    VALUES ((SELECT id
             FROM delivery
             WHERE storekeeper =
                   (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10)) && closed = FALSE)),
            new.id);


  END;

CREATE TRIGGER stockAfterDeliveryElementDeleteUpdate
  AFTER DELETE
  ON delivery_element
  FOR EACH ROW
  BEGIN

    UPDATE product SET product.amount = product.amount - old.amount WHERE code = old.product_id;
    #     DELETE FROM delivery_deliveryelement WHERE delivery_element = old.id;

  END;

CREATE TRIGGER stockAfterDeliveryUpdate
  AFTER INSERT
  ON delivery_element
  FOR EACH ROW
  BEGIN

    UPDATE product SET product.amount = product.amount + new.amount WHERE new.product_id = code;

  END;

CREATE TRIGGER negativeFieldsProductInsert
  BEFORE INSERT
  ON product
  FOR EACH ROW
  BEGIN

    IF (new.amount < 0 || new.price < 0)
    THEN

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Amount or price can`t be negative.';

    END IF;
  END;

CREATE TRIGGER negativeFieldsProductUpdate
  BEFORE UPDATE
  ON product
  FOR EACH ROW
  BEGIN

    IF (new.amount < 0 || new.price < 0)
    THEN

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Amount or price can`t be negative.';

    END IF;
  END;

CREATE TABLE sale
(
  id         int AUTO_INCREMENT
    PRIMARY KEY,
  product_id int NOT NULL,
  amount     int NULL,
  CONSTRAINT sale_products_code_fk
  FOREIGN KEY (product_id) REFERENCES product (code)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER negativeFieldsSaleInsert
  BEFORE INSERT
  ON sale
  FOR EACH ROW
  BEGIN

    IF (new.amount < 0)
    THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Amount can`t benegative.';
    END IF;

  END;

CREATE TRIGGER negativeFieldsSaleUpdate
  BEFORE UPDATE
  ON sale
  FOR EACH ROW
  BEGIN

    IF (new.amount < 0)
    THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Amount can`t benegative.';
    END IF;

  END;

CREATE TRIGGER saleBillConnector
  AFTER INSERT
  ON sale
  FOR EACH ROW
  BEGIN

    INSERT INTO bill_sale (bill_id, sale_id)
    VALUES ((SELECT id
             FROM bill
             WHERE worker_id =
                   (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10)) && closed = FALSE)),
            new.id);


  END;

CREATE TRIGGER stockAfterSaleDeleteUpdate
  AFTER DELETE
  ON sale
  FOR EACH ROW
  BEGIN
    UPDATE product SET amount = product.amount + old.amount;
    #     DELETE FROM bill_sale WHERE sale_id = old.id;
  END;

CREATE TRIGGER stockAfterSaleUpdate
  AFTER INSERT
  ON sale
  FOR EACH ROW
  BEGIN

    UPDATE product SET product.amount = product.amount - new.amount WHERE new.product_id = code;

  END;

CREATE TABLE worker
(
  id             int AUTO_INCREMENT,
  name           varchar(30)                                     NOT NULL,
  surname        varchar(30)                                     NOT NULL,
  contract_start date                                            NOT NULL,
  contract_end   date                                            NULL,
  status         enum ('working', 'unemplyed') DEFAULT 'working' NOT NULL,
  job            enum ('seller', 'storekeeper', 'manager')       NULL,
  CONSTRAINT workers_id_uindex
  UNIQUE (id)
);

ALTER TABLE worker
  ADD PRIMARY KEY (id);

CREATE TABLE bill
(
  id        int AUTO_INCREMENT
    PRIMARY KEY,
  time      timestamp DEFAULT CURRENT_TIMESTAMP NULL,
  value     float                               NULL,
  worker_id int                                 NULL,
  customer  int                                 NULL,
  closed    tinyint(1) DEFAULT '0'              NULL,
  CONSTRAINT bill_customer_id_fk
  FOREIGN KEY (customer) REFERENCES customer (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT bill_worker_id_fk
  FOREIGN KEY (worker_id) REFERENCES worker (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER balanceUpdate
  AFTER UPDATE
  ON bill
  FOR EACH ROW
  BEGIN

    UPDATE bonus_card SET balance = balance + new.value WHERE customer_id = old.customer;

  END;

CREATE TRIGGER discountCounter
  AFTER UPDATE
  ON bill
  FOR EACH ROW
  BEGIN

    DECLARE moneySpent FLOAT;
    DECLARE newDiscount FLOAT;

    SET moneySpent = (SELECT ROUND(SUM(value))
                      FROM bill
                      WHERE customer = new.customer
                        AND time BETWEEN now() - INTERVAL 1 MONTH AND now());

    IF (moneySpent < 500)
    THEN
      SET newDiscount = 0;
    ELSEIF (moneySpent > 2000)
      THEN
        SET newDiscount = 0.15;
    ELSEIF (moneySpent > 1000)
      THEN
        SET newDiscount = 0.1;
    ELSEIF (moneySpent > 500)
      THEN
        SET newDiscount = 0.05;
    END IF;

    UPDATE bonus_card SET discount = newDiscount WHERE customer_id = new.customer;
  END;

CREATE TRIGGER salesDelete
  BEFORE DELETE
  ON bill
  FOR EACH ROW
  BEGIN

    DELETE FROM sale WHERE ID IN (SELECT sale_id FROM bill_sale WHERE bill_id = old.id);


  END;

CREATE TABLE bill_sale
(
  bill_id int NULL,
  sale_id int NULL,
  CONSTRAINT bill_sale_bill_id_fk
  FOREIGN KEY (bill_id) REFERENCES bill (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT bill_sale_sale_id_fk
  FOREIGN KEY (sale_id) REFERENCES sale (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TABLE delivery
(
  id          int AUTO_INCREMENT
    PRIMARY KEY,
  time        timestamp DEFAULT CURRENT_TIMESTAMP NULL,
  storekeeper int                                 NULL,
  closed      tinyint(1) DEFAULT '0'              NULL,
  deliverer   varchar(30)                         NULL,
  CONSTRAINT delivery_deliverer_worker_id_fk
  FOREIGN KEY (storekeeper) REFERENCES worker (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER deliveryElementDelete
  BEFORE DELETE
  ON delivery
  FOR EACH ROW
  BEGIN

    DELETE
    FROM delivery_element
    WHERE id IN (SELECT delivery_element FROM delivery_deliveryelement WHERE delivery = old.id);

  END;

CREATE TABLE delivery_deliveryelement
(
  delivery         int NOT NULL,
  delivery_element int NOT NULL,
  CONSTRAINT delivery_deliveryElements_delivery_elements_id_fk
  FOREIGN KEY (delivery_element) REFERENCES delivery_element (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT delivery_deliveryElements_delivery_id_fk
  FOREIGN KEY (delivery) REFERENCES delivery (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TABLE storekeeper
(
  worker_id    int                       NULL,
  product_type enum ('fuel', 'articles') NULL,
  CONSTRAINT deliverer_worker_id_fk
  FOREIGN KEY (worker_id) REFERENCES worker (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TABLE users
(
  worker_id int         NOT NULL,
  login     varchar(20) NOT NULL,
  password  varchar(10) NOT NULL,
  last_log  datetime    NULL,
  CONSTRAINT users_worker_id_uindex
  UNIQUE (worker_id),
  CONSTRAINT users_workers_id_fk
  FOREIGN KEY (worker_id) REFERENCES worker (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TRIGGER dateController
  BEFORE INSERT
  ON worker
  FOR EACH ROW
  BEGIN

    IF (new.contract_start > new.contract_end OR new.contract_start < current_date)
    THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Wrong start and end dates.';
    END IF;
  END;

CREATE TRIGGER userCreator
  AFTER INSERT
  ON worker
  FOR EACH ROW
  BEGIN

    INSERT INTO users (worker_id, login, password, last_log)
    VALUES (new.id, CONCAT_WS('.', new.name, new.surname), SUBSTRING(MD5(new.id) FROM 1 FOR 6), NULL);

  END;

CREATE PROCEDURE addManager(IN username varchar(45), IN pass varchar(200))
  BEGIN
    DECLARE host CHAR(14) DEFAULT '@\'localhost\'';
    SET username := CONCAT('\'', REPLACE(TRIM(username), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),
    pass := CONCAT('\'', REPLACE(pass, CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');
    SET @sql := CONCAT('CREATE USER ', username, host, ' IDENTIFIED BY ', pass);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET @sql := CONCAT('GRANT \'manager\' TO', username, host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END;

CREATE PROCEDURE addSale(IN eCode int, IN eAmount int)
  BEGIN

    INSERT INTO sale (id, product_id, amount) VALUES (DEFAULT, eCode, eAmount);


  END;

CREATE PROCEDURE addSeller(IN username varchar(45), IN pass varchar(200))
  BEGIN
    DECLARE host CHAR(14) DEFAULT '@\'localhost\'';
    SET username := CONCAT('\'', REPLACE(TRIM(username), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),
    pass := CONCAT('\'', REPLACE(pass, CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');
    SET @sql := CONCAT('CREATE USER ', username, host, ' IDENTIFIED BY ', pass);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET @sql := CONCAT('GRANT \'seller\' TO', username, host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END;

CREATE PROCEDURE addStorekeeper(IN username varchar(45), IN pass varchar(200))
  BEGIN
    DECLARE host CHAR(14) DEFAULT '@\'localhost\'';
    SET username := CONCAT('\'', REPLACE(TRIM(username), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),
    pass := CONCAT('\'', REPLACE(pass, CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');
    SET @sql := CONCAT('CREATE USER ', username, host, ' IDENTIFIED BY ', pass);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET @sql := CONCAT('GRANT \'storekeeper\' TO', username, host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END;

CREATE PROCEDURE billValueUpdate(IN ePrice float)
  BEGIN

    UPDATE bill
    SET value = value + ePrice
    WHERE (worker_id = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) &&
           closed = FALSE);

  END;

CREATE PROCEDURE closeBill(IN eNip int)
  BEGIN

    UPDATE bill
    SET customer = (SELECT id FROM customer WHERE eNip = nip)
    WHERE worker_id = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) && closed = FALSE;
    UPDATE bill
    SET closed = TRUE
    WHERE worker_id = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) && closed = FALSE;

  END;

CREATE PROCEDURE closeBillWithoutCustomer()
  BEGIN
    UPDATE bill
    SET closed = TRUE
    WHERE worker_id = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) && closed = FALSE;

  END;

CREATE PROCEDURE createBill()
  BEGIN

    INSERT INTO bill (id, time, value, worker_id, customer, closed)
    VALUES (DEFAULT,
            current_timestamp,
            0,
            (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))),
            NULL,
            DEFAULT);

  END;

CREATE PROCEDURE createDelivery()
  BEGIN

    INSERT INTO delivery (id, time, storekeeper, closed, deliverer)
    VALUES (DEFAULT,
            current_timestamp,
            (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))),
            DEFAULT,
            NULL);


  END;

CREATE PROCEDURE createNewCustomer(IN eName varchar(30), IN eSurname varchar(30), IN eNIP int)
  BEGIN

    INSERT INTO customer (id, name, surname, nip, created) VALUES (DEFAULT, eName, eSurname, eNIP, current_timestamp);

  END;

CREATE PROCEDURE createUser(IN eID int)
  BEGIN
    DECLARE host CHAR(14) DEFAULT '@\'localhost\'';

    DECLARE username VARCHAR(30) DEFAULT (SELECT login FROM users WHERE worker_id = eID);
    DECLARE password VARCHAR(11) DEFAULT (SELECT password FROM users WHERE worker_id = eID);
    DECLARE job VARCHAR(15) DEFAULT (SELECT job FROM worker WHERE id = eID);

    SET username := CONCAT('\'', REPLACE(TRIM(username), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),
    password := CONCAT('\'', password, '\'');
    SET @sql := CONCAT('CREATE USER', username, host, 'IDENTIFIED BY ', password);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    SET @sql := CONCAT('GRANT EXECUTE ON stacjapaliw.* TO ', username, host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;


    IF (job = 'storekeeper')
    THEN
      SET @sql := CONCAT('GRANT UPDATE,SELECT ON stacjapaliw.* TO ', username, host);
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
    ELSEIF (job = 'manager')
      THEN
        SET @sql := CONCAT('GRANT UPDATE,DELETE,SELECT ON stacjapaliw.* TO ', username, host);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
    ELSE
      SET @sql := CONCAT('GRANT SELECT,UPDATE ON stacjapaliw.* TO ', username, host);
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
    END IF;

    DEALLOCATE PREPARE stmt;

  END;

CREATE PROCEDURE createWorker(IN eName varchar(30), IN eSurname varchar(30),
                              IN eJob  enum ('seller', 'manager', 'storekeeper'), IN startD date, IN endD date)
  BEGIN

    INSERT INTO worker (id, name, surname, contract_start, contract_end, status, job)
    VALUES (DEFAULT, eName, eSurname, startD, endD, DEFAULT, eJob);

  END;

CREATE PROCEDURE deleteBill()
  BEGIN

    DELETE
    FROM sale
    WHERE id = (SELECT sale_id
                FROM bill_sale
                WHERE bill_id = (SELECT id
                                 FROM bill
                                 WHERE closed = FALSE && worker_id = (SELECT worker_id
                                                                      FROM users
                                                                      WHERE login = (LEFT(user(), length(user()) - 10)))));

    DELETE
    FROM bill
    WHERE closed = FALSE && worker_id = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10)));


  END;

CREATE PROCEDURE deleteCustomer(IN eID int)
  BEGIN

    DELETE FROM customer WHERE id = eID;

  END;

CREATE PROCEDURE deleteDelivery()
  BEGIN

    DELETE
    FROM delivery_element
    WHERE id = (SELECT delivery_element
                FROM delivery_deliveryelement
                WHERE delivery = (SELECT id
                                  FROM delivery
                                  WHERE closed = FALSE && storekeeper = (SELECT worker_id
                                                                         FROM users
                                                                         WHERE login = (LEFT(user(), length(user()) - 10)))));

    DELETE
    FROM delivery
    WHERE closed = FALSE &&
          storekeeper = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10)));


  END;

CREATE PROCEDURE deleteDeliveryElement(IN deliveryID int)
  BEGIN

    DELETE FROM delivery_element WHERE id = deliveryID;

  END;

CREATE PROCEDURE deleteProduct(IN saleID int)
  BEGIN

    DELETE FROM sale WHERE sale.id = saleID;

  END;

CREATE PROCEDURE deleteUser(IN ID int)
  BEGIN


    DECLARE username VARCHAR(30) DEFAULT (SELECT login FROM users WHERE worker_id = ID);
    DECLARE host CHAR(14) DEFAULT '@\'localhost\'';

    SET username := CONCAT('\'', REPLACE(TRIM(username), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');
    SET @`sql` := CONCAT('DROP USER ', username, host);
    PREPARE `stmt` FROM @`sql`;
    EXECUTE `stmt`;
    DEALLOCATE PREPARE `stmt`;
  END;

CREATE PROCEDURE endDelivery()
  BEGIN

    UPDATE delivery
    SET closed = TRUE
    WHERE storekeeper = (SELECT worker_id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) &&
          closed = FALSE;

  END;

CREATE PROCEDURE existingProductDelivery(IN eCode int, IN eAmount int)
  BEGIN

    DECLARE eName varchar(30);
    DECLARE ePrice FLOAT;
    DECLARE eTax FLOAT;

    SELECT name, price, tax INTO eName, ePrice, eTax FROM product WHERE code = eCode;

    INSERT INTO delivery_element (id, product_id, tax, amount, price, name)
    VALUES (DEFAULT, eCode, eTax, eAmount, ePrice, eName);

  END;

CREATE FUNCTION findSaleID(eCode int, eAmount int)
  RETURNS int
  BEGIN
    DECLARE result int;

    SET result = (SELECT id
                  FROM sale
                  WHERE product_id = eCode && amount = eAmount && (SELECT worker_id
                                                                   FROM bill b
                                                                          JOIN bill_sale ON b.id = bill_sale.bill_id
                                                                          JOIN sale s ON bill_sale.sale_id = s.id
                                                                   WHERE s.product_id = eCode && s.amount = eAmount) = (
                                                                                                                       SELECT worker_id
                                                                                                                       FROM users
                                                                                                                       WHERE login = 'test.seller'));

    RETURN result;

  END;

CREATE FUNCTION getDiscount(eNIP int)
  RETURNS float
  BEGIN
    DECLARE result FLOAT;

    SET result = (SELECT discount FROM bonus_card WHERE eNIP = (SELECT nip FROM customer WHERE id = customer_id));

    RETURN result;

  END;

CREATE PROCEDURE insertExistingProduct(IN eCode int, IN eAmount int)
  BEGIN

    DECLARE eName varchar(30);
    DECLARE ePrice FLOAT;
    DECLARE eTax FLOAT;

    SELECT name, price, tax INTO eName, ePrice, eTax FROM product WHERE code = eCode;

    INSERT INTO product (name, price, tax, code, amount) VALUES (eName, ePrice, eTax, eCode, eAmount);

  END;

CREATE PROCEDURE insertNewProduct(IN eCode int, IN eName varchar(30), IN ePrice float, IN eTax float, IN eAmount int)
  BEGIN

    INSERT INTO product (name, price, tax, code, amount) VALUES (eName, ePrice, eTax, eCode, eAmount);

  END;

CREATE PROCEDURE newProductDelivery(IN eCode int, IN eName varchar(30), IN ePrice float, IN eAmount int, IN eTax float)
  BEGIN

    INSERT INTO product (name, price, tax, code, amount) VALUES (eName, ePrice, eTax, eCode, 0);

    INSERT INTO delivery_element (id, product_id, name, tax, price, amount)
    VALUES (DEFAULT, eCode, eName, eTax, ePrice, eAmount);

  END;

CREATE FUNCTION searchProductFromCode(eCode int)
  RETURNS tinyint(1)
  BEGIN

    DECLARE result bool;

    SET @rs = (SELECT code FROM product WHERE product.code = eCode);
    SET result = FALSE;

    IF (@rs IS NOT NULL)
    THEN
      SET result = TRUE;
    END IF;

    RETURN result;

  END;

CREATE FUNCTION searchProductFromName(eName varchar(30))
  RETURNS tinyint(1)
  BEGIN
    DECLARE result bool;

    SET @rs = (SELECT code FROM product WHERE product.name = eName);
    SET result = FALSE;

    IF (@rs IS NOT NULL)
    THEN
      SET result = TRUE;
    END IF;

    RETURN result;

  END;

CREATE PROCEDURE sellProduct(IN eCode int, IN eAmount int, IN eCustomer int)
  BEGIN


    #TODO ZMIENIC ZEBY SPRZEDAWAC NA PODSTAWIE TABLICY kodów, a nie na podstawie jednego kodu
    #TODO dopisa© funkcje calculateBill
    DECLARE bill FLOAT;

    SET bill = calculateBill(eCode, eAmount);

    INSERT INTO sale (worker_id, id, product_id, buyer, amount, sale_time, bill)
    VALUES (current_user, DEFAULT, eCode, eCustomer, eAmount, `current_timestamp`(), bill);

  END;

CREATE PROCEDURE setDeliverer(IN eDeliverer varchar(30))
  BEGIN

    UPDATE delivery
    SET deliverer = eDeliverer
    WHERE id = (SELECT id FROM users WHERE login = (LEFT(user(), length(user()) - 10))) && closed = FALSE;

  END;