--DROP TABLE DOC;

CREATE TABLE DOC (
  DOCID    INTEGER NOT NULL,
  FROMID   INTEGER,
  TOID     INTEGER,
  DOCDATE  DATE,
  DOCNUM   VARCHAR(20),
  DOCTYPE  SMALLINT,
  /* Keys */
  PRIMARY KEY (DOCID)
);

CREATE INDEX DOC_INDEX01
  ON DOC
  (DOCDATE, DOCID);

COMMENT ON COLUMN DOC.DOCID
  IS 'ИД документ';

COMMENT ON COLUMN DOC.FROMID
  IS 'Откуда';

COMMENT ON COLUMN DOC.TOID
  IS 'Куда';

COMMENT ON COLUMN DOC.DOCDATE
  IS 'Дата документа';

COMMENT ON COLUMN DOC.DOCNUM
  IS 'Док номер';

COMMENT ON COLUMN DOC.DOCTYPE
  IS '1 - Постуление (молоко)
2 - Продажа  (Готовых продуктов. и молока тоже)
3 - Возврат от покупателей ( Если не испорченный то в склад если испорченный то склад не добавим )
4 - Поступление готовых продуктов из производства ( то есть прибавляем склад )';


--Table: DOCITEMS

--DROP TABLE DOCITEMS;

CREATE TABLE DOCITEMS (
  DOCID        INTEGER NOT NULL,
  GOODSID      INTEGER,
  GOODSQTY     INTEGER,
  GOODSPRICE   VARCHAR(20),
  GOODSSTATE   SMALLINT,
  GOODSMSRMNT  INTEGER
);

CREATE UNIQUE INDEX DOCITEMS_INDEX01
  ON DOCITEMS
  (DOCID, GOODSID);

COMMENT ON COLUMN DOCITEMS.DOCID
  IS 'Мастер ИД';

COMMENT ON COLUMN DOCITEMS.GOODSID
  IS 'Ид товара';

COMMENT ON COLUMN DOCITEMS.GOODSQTY
  IS 'Количество';

COMMENT ON COLUMN DOCITEMS.GOODSPRICE
  IS 'Цена продажи';

COMMENT ON COLUMN DOCITEMS.GOODSSTATE
  IS 'Состояние товара';

COMMENT ON COLUMN DOCITEMS.GOODSMSRMNT
  IS 'Ед изм';

--Table: GOODS

--DROP TABLE GOODS;

CREATE TABLE GOODS (
  GOODSID        INTEGER NOT NULL,
  GOODSNAME      VARCHAR(20),
  GOODSPRICE     DECIMAL,
  GOODS_COST     DECIMAL,
  GOODSMSRMNT    INTEGER NOT NULL,
  GOODSTYPE      SMALLINT,
  GOODS_GOODSID  INTEGER NOT NULL,
  GOODS_OUT      DECIMAL,
  GOODS_IN       DECIMAL(9,3),
  /* Keys */
  PRIMARY KEY (GOODSID)
);

COMMENT ON COLUMN GOODS.GOODSID
  IS 'Ид Товара';

COMMENT ON COLUMN GOODS.GOODSNAME
  IS 'Название товара
';

COMMENT ON COLUMN GOODS.GOODSPRICE
  IS 'Цена продажи по умолчание';

COMMENT ON COLUMN GOODS.GOODS_COST
  IS 'Себестоимость товара';

COMMENT ON COLUMN GOODS.GOODSMSRMNT
  IS 'Ед измерение';

COMMENT ON COLUMN GOODS.GOODSTYPE
  IS '1- 0 Продукт или товар для продукта';

COMMENT ON COLUMN GOODS.GOODS_GOODSID
  IS 'ИД товара для продукта пример (молока)';

COMMENT ON COLUMN GOODS.GOODS_IN
  IS 'Сколько товар уходит на один продукт';

--Table: MSTMNT

--DROP TABLE MSTMNT;

CREATE TABLE MSTMNT (
  ID    INTEGER,
  NAME  VARCHAR(20) CHARACTER SET NONE
);

--View: PRODUCTSHISTORY

--DROP VIEW PRODUCTSHISTORY;

CREATE VIEW PRODUCTSHISTORY
(
  DOCTYPE,
  DOCDATE,
  GOODSID,
  GOODSQTY
)
AS
select doc.DOCTYPE,doc.DOCDATE,docitems.GOODSID,docitems.GOODSQTY from docitems
  join doc on docitems.DOCID = doc.DOCID
   and doc.DOCTYPE=1
  join goods on docitems.GOODSID = goods.GOODSID
   and goods.GOODSTYPE = 0   
union 
select doc.DOCTYPE,doc.DOCDATE,docitems.GOODSID,-docitems.GOODSQTY from docitems
  join doc on docitems.DOCID = doc.DOCID
   and doc.DOCTYPE=2
  join goods on docitems.GOODSID = goods.GOODSID
union  
select doc.DOCTYPE,doc.DOCDATE,docitems.GOODSID,docitems.GOODSQTY from docitems
  join doc on docitems.DOCID = doc.DOCID
   and doc.DOCTYPE=3
   and docitems.GOODSSTATE=0
  join goods on docitems.GOODSID = goods.GOODSID
union
select doc.DOCTYPE,doc.DOCDATE,docitems.GOODSID,docitems.GOODSQTY from docitems
  join doc on docitems.DOCID = doc.DOCID
   and doc.DOCTYPE=4
  join goods on docitems.GOODSID = goods.GOODSID
   and goods.GOODSTYPE = 1   
union
select doc.DOCTYPE,doc.DOCDATE,goods.GOODS_GOODSID as GOODSID,-(docitems.GOODSQTY*goods.GOODS_OUT*goods.GOODS_IN) as GOODSQTY from docitems
  join doc on docitems.DOCID = doc.DOCID
   and doc.DOCTYPE=4
  join goods on docitems.GOODSID = goods.GOODSID
   and goods.GOODSTYPE = 1;


--View: WAREHOUSE

--DROP VIEW WAREHOUSE;

CREATE VIEW WAREHOUSE
(
  GOODSID,
  GOODSQTY
)
AS
SELECT GOODSID, SUM(GOODSQTY) GOODSQTY FROM ProductsHistory GROUP