drop table inwards;
drop table sales;
drop table products_on_stocks;
drop table products;
drop table stocks;
drop sequence seq_inward_id;


create table products(
	id int constraint product_id_pk primary key,
	name varchar2(20) constraint product_name_notnull not null,
	ean int constraint product_ean_notnull not null constraint product_ean_uniq unique,
	price int constraint product_price_notnull not null,
	vat number(3,2) constraint product_vat_notnull not null
);

create table stocks(
	id int constraint stock_id_pk primary key,
	name varchar2(20) constraint stock_name_notnull not null,
	address varchar2(20)
);

create table products_on_stocks(
	id int constraint pos_id_pk primary key,
	product_id int constraint pos_product_id_fk references products(id),
	stock_id int constraint pos_stock_id_fk references stocks(id),
	quantity int constraint pos_quantity_notnull not null
);

create table sales(
	id int constraint ps_id_pk primary key,
	product_id int constraint ps_product_id_fk references products(id),
	stock_id int constraint ps_stock_id_fk references stocks(id),
	sale_date date default sysdate constraint sale_date_notnull not null,
	quantity int constraint sale_quantity_notnull not null
);

create table inwards(
	id int constraint pi_id_pk primary key,
	product_id int constraint pi_product_id_fk references products(id),
	stock_id int constraint pi_stock_id_fk references stocks(id),
	inward_date date default sysdate constraint inward_date_not_null not null,
	quantity int constraint inward_quantity_notnull not null
);

CREATE SEQUENCE seq_inward_id;


CREATE OR REPLACE TRIGGER watch_sales_minimum INSTEAD OF UPDATE ON products_on_stocks FOR EACH ROWl
DECLARE
  BEGIN
      update products_on_stocks set quantity=1 where 1;

  END;
/



CREATE OR REPLACE TRIGGER inwards_sales_trigger AFTER INSERT OR UPDATE ON products_on_stocks FOR EACH ROW
DECLARE
  BEGIN
    IF inserting THEN
      insert into inwards values(seq_inward_id.nextval, :new.product_id, :new.stock_id, sysdate, :new.quantity);
    END IF;

    IF updating THEN

      IF :new.quantity > :old.quantity THEN
        insert into inwards values(seq_inward_id.nextval, :new.product_id, :new.stock_id, sysdate, :new.quantity-:old.quantity);
      END IF;

      IF :new.quantity < :old.quantity THEN
        insert into sales values(seq_inward_id.nextval, :new.product_id, :new.stock_id, sysdate, :old.quantity-:new.quantity);
      END IF;

    END IF;
  END;
/






insert into products values(3, 'Mlieko', 435654356, 27, 0.20);
insert into products values(4, 'Nohavice', 12345643, 350, 0.20);
insert into products values(5, 'Kanon', 4365345536, 12000, 0.20);
insert into products values(6, 'Karbo-bruska', 8765765, 6584, 0.20);
insert into products values(7, 'Umele sladidlo', 345423216, 12.5, 0.20);
insert into products values(32, 'Slivovica', 97865424, 150, 0.30);
insert into products values(56, 'Notebook', 34577435, 14444, 0.20);
insert into products values(43, 'Penazenka', 23457432, 540, 0.19);
insert into products values(57, 'Zaclony', 324565, 589, 0.20);

insert into stocks values(1, 'Komarov', 'Komarov 12');
insert into stocks values(2, 'Velodrom', 'Udolni 25');
insert into stocks values(3, 'Poprad', 'Nabrezna 28');
insert into stocks values(6, 'FI MUNI', 'Botanicka 34');

insert into products_on_stocks values(1, 3, 3, 100);
insert into products_on_stocks values(2, 4, 2, 96);
insert into products_on_stocks values(3, 4, 1, 47);
insert into products_on_stocks values(4, 3, 6, 80);
insert into products_on_stocks values(5, 7, 3, 16);
insert into products_on_stocks values(6, 32, 2, 150);