SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:EXAMPLES'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;


/********************************************/
/* Домены                                   */
/********************************************/
CREATE DOMAIN D_BOOLEAN AS
SMALLINT
CHECK (VALUE IN (0, 1));

CREATE DOMAIN D_MONEY AS
NUMERIC(15,2);

CREATE DOMAIN D_ZIPCODE AS
CHAR(10)
CHECK (VALUE SIMILAR TO '[0-9]{0,10}');


/********************************************/
/* Последовательности (генераторы)          */
/********************************************/
CREATE SEQUENCE GEN_CUSTOMER_ID;
ALTER SEQUENCE GEN_CUSTOMER_ID RESTART WITH 402;

CREATE SEQUENCE GEN_INVOICE_ID;
ALTER SEQUENCE GEN_INVOICE_ID RESTART WITH 200010;

CREATE SEQUENCE GEN_INVOICE_LINE_ID;
ALTER SEQUENCE GEN_INVOICE_LINE_ID RESTART WITH 1000004;

CREATE SEQUENCE GEN_PRODUCT_ID;
ALTER SEQUENCE GEN_PRODUCT_ID RESTART WITH 2892;



/********************************************/
/* Исключения                               */
/********************************************/

CREATE EXCEPTION E_INVOICE_ALREADY_PAYED 'Счёт фактура оплачена. Измение не возможно';


/********************************************/
/* Таблицы                                  */
/********************************************/
CREATE TABLE CUSTOMER (
    CUSTOMER_ID  INTEGER NOT NULL,
    NAME         VARCHAR(60) NOT NULL,
    ADDRESS      VARCHAR(250),
    ZIPCODE      D_ZIPCODE,
    PHONE        VARCHAR(14)
);


CREATE TABLE INVOICE (
    INVOICE_ID    INTEGER NOT NULL,
    CUSTOMER_ID   INTEGER NOT NULL,
    INVOICE_DATE  TIMESTAMP,
    TOTAL_SALE    NUMERIC(15,2),
    PAYED         D_BOOLEAN DEFAULT 0 NOT NULL
);


CREATE TABLE INVOICE_LINE (
    INVOICE_LINE_ID  INTEGER NOT NULL,
    INVOICE_ID       INTEGER NOT NULL,
    PRODUCT_ID       INTEGER NOT NULL,
    QUANTITY         NUMERIC(15,0) NOT NULL,
    SALE_PRICE       NUMERIC(15,2) NOT NULL
);


CREATE TABLE PRODUCT (
    PRODUCT_ID   INTEGER NOT NULL,
    NAME         VARCHAR(100) NOT NULL,
    PRICE        D_MONEY NOT NULL,
    DESCRIPTION  BLOB SUB_TYPE 1 SEGMENT SIZE 80
);




/********************************************/
/* Первичные ключи таблицы                  */
/********************************************/

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID);
ALTER TABLE INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_ID);
ALTER TABLE INVOICE_LINE ADD CONSTRAINT PK_INVOICE_LINE PRIMARY KEY (INVOICE_LINE_ID);
ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID);


/********************************************/
/* Внешние ключи таблиц                     */
/********************************************/

ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOCE_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);
ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_INVOICE FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (INVOICE_ID);
ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);



SET TERM ^ ;

/********************************************/
/* Триггеры                                 */
/********************************************/

CREATE OR ALTER TRIGGER CUSTOMER_BI FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.customer_id is null) then
    new.customer_id = gen_id(gen_customer_id,1);
end
^


CREATE OR ALTER TRIGGER INVOICE_BI FOR INVOICE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.invoice_id is null) then
    new.invoice_id = gen_id(gen_invoice_id,1);
end
^


CREATE OR ALTER TRIGGER INVOICE_LINE_BI FOR INVOICE_LINE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.invoice_line_id is null) then
    new.invoice_line_id = gen_id(gen_invoice_line_id,1);
end
^


CREATE OR ALTER TRIGGER PRODUCT_BI FOR PRODUCT
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.product_id is null) then
    new.product_id = gen_id(gen_product_id,1);
end
^

/********************************************/
/* Хранимые процедуры                       */
/********************************************/

CREATE OR ALTER PROCEDURE SP_ADD_INVOICE (
    INVOICE_ID INTEGER,
    CUSTOMER_ID INTEGER,
    INVOICE_DATE TIMESTAMP = CURRENT_TIMESTAMP)
AS
BEGIN
  INSERT INTO INVOICE (
    INVOICE_ID,
    CUSTOMER_ID,
    INVOICE_DATE,
    TOTAL_SALE,
    PAYED
  )
  VALUES (
    :INVOICE_ID,
    :CUSTOMER_ID,
    :INVOICE_DATE,
    0,
    0
  );

END^


CREATE OR ALTER PROCEDURE SP_ADD_INVOICE_LINE (
    INVOICE_ID INTEGER,
    PRODUCT_ID INTEGER,
    QUANTITY INTEGER)
AS
DECLARE sale_price D_MONEY;
DECLARE payed      D_BOOLEAN;
BEGIN
  SELECT
      payed
  FROM
      invoice
  WHERE
        invoice_id = :invoice_id
  INTO :payed;

  -- не позволяем редактировать уже оплаченную счёт фактуру
  IF (payed = 1) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  SELECT
      price
  FROM
      product
  WHERE
        product_id = :product_id
  INTO :sale_price;

  INSERT INTO invoice_line (invoice_line_id,
                            invoice_id,
                            product_id,
                            quantity,
                            sale_price)
  VALUES (NEXT VALUE FOR gen_invoice_line_id,
          :invoice_id,
          :product_id,
          :quantity,
          :sale_price);

  -- увеличиваем сумму счёта
  UPDATE invoice
  SET total_sale = COALESCE(total_sale, 0) + :sale_price * :quantity
  WHERE invoice_id = :invoice_id;
END^


CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE (
    INVOICE_ID INTEGER)
AS
BEGIN
  IF (EXISTS(SELECT * FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAYED = 1)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  DELETE FROM INVOICE WHERE INVOICE_ID = :INVOICE_ID;
END^


CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE_LINE (
    INVOICE_LINE_ID INTEGER)
AS
DECLARE invoice_id INT;
DECLARE price      D_MONEY;
DECLARE quantity   INT;
BEGIN
  IF (EXISTS(SELECT *
             FROM invoice_line
                 JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
             WHERE invoice.payed = 1
               AND invoice_line.invoice_line_id = :invoice_line_id)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  DELETE FROM invoice_line
  WHERE invoice_line.invoice_line_id = :invoice_line_id
  RETURNING invoice_id, quantity, sale_price
  INTO invoice_id, quantity, price;

  -- уменьшаем сумму счёта
  UPDATE invoice
  SET total_sale = total_sale - :quantity * :price
  WHERE invoice_id = :invoice_id;
END^


CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE (
    INVOICE_ID INTEGER,
    CUSTOMER_ID INTEGER,
    INVOICE_DATE TIMESTAMP)
AS
BEGIN
  IF (EXISTS(SELECT *
             FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAYED = 1)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  UPDATE INVOICE
  SET CUSTOMER_ID = :CUSTOMER_ID,
      INVOICE_DATE = :INVOICE_DATE
  WHERE INVOICE_ID = :INVOICE_ID;
END^


CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE_LINE (
    INVOICE_LINE_ID INTEGER,
    QUANTITY INTEGER)
AS
DECLARE invoice_id INT;
DECLARE price      D_MONEY;
DECLARE payed      D_BOOLEAN;
BEGIN
  SELECT
      product.price,
      invoice.invoice_id,
      invoice.payed
  FROM
      invoice_line
      JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
      JOIN product ON product.product_id = invoice_line.product_id
  WHERE
        invoice_line.invoice_line_id = :invoice_line_id
  INTO :price,
       :invoice_id,
       :payed;

  -- не позволяем редактировать уже оплаченную счёт фактуру
  IF (payed = 1) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  -- обновляем цену и количество
  UPDATE invoice_line
  SET sale_price = :price,
      quantity = :quantity
  WHERE invoice_line_id = :invoice_line_id;

  -- а теперь обновляем сумму счёта
  MERGE INTO invoice
  USING (SELECT
             invoice_id,
             SUM(sale_price * quantity) AS total_sale
         FROM invoice_line
         WHERE invoice_id = :invoice_id
         GROUP BY invoice_id) L
  ON invoice.invoice_id = L.invoice_id
  WHEN MATCHED THEN
      UPDATE SET total_sale = L.total_sale;
END^


CREATE OR ALTER PROCEDURE SP_PAY_FOR_INOVICE (
    INVOICE_ID INTEGER)
AS
begin
  IF (EXISTS(SELECT *
             FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAYED = 1)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  UPDATE INVOICE
  SET PAYED = 1
  WHERE INVOICE_ID = :INVOICE_ID;
end^



SET TERM ; ^



/********************************************/
/* Комментарии для различных типов объектов */
/********************************************/

/* Домены */
COMMENT ON DOMAIN D_BOOLEAN IS 
'Логическое значение. 0 - FALSE, 1- TRUE';

COMMENT ON DOMAIN D_ZIPCODE IS 
'Почтовый индекс';


/* Таблицы */
COMMENT ON TABLE CUSTOMER IS 
'Заказчики';

COMMENT ON TABLE INVOICE IS 
'Счёт-фактура';

COMMENT ON TABLE INVOICE_LINE IS 
'Строки счёт-фактура';

COMMENT ON TABLE PRODUCT IS 
'Продукты';


/* Хранимые процедуры */
COMMENT ON PROCEDURE SP_ADD_INVOICE IS 
'Добавление счёт-фактуры';

COMMENT ON PROCEDURE SP_ADD_INVOICE_LINE IS 
'Добавление строки счёт-фактуры';

COMMENT ON PROCEDURE SP_DELETE_INVOICE IS 
'Удаление счёт-фактуры';

COMMENT ON PROCEDURE SP_DELETE_INVOICE_LINE IS 
'Удаление строки счёт-фактуры';

COMMENT ON PROCEDURE SP_EDIT_INVOICE IS 
'Редактирование счёт-фактуры';

COMMENT ON PROCEDURE SP_EDIT_INVOICE_LINE IS 
'Редактирование строки счёт-фактуры';

COMMENT ON PROCEDURE SP_PAY_FOR_INOVICE IS 
'Оплата счёт-фактуры';


/* Генераторы */
COMMENT ON GENERATOR GEN_CUSTOMER_ID IS 
'Последовательность для идентификатора заказчика';

COMMENT ON GENERATOR GEN_INVOICE_ID IS 
'Последовательность для номера счёт-фактуры';

COMMENT ON GENERATOR GEN_INVOICE_LINE_ID IS 
'Последовательность для идентификатора строки счёт-фактуры';

COMMENT ON GENERATOR GEN_PRODUCT_ID IS 
'Последовательность для идентификатора продукта';


/* Поля таблиц */
COMMENT ON COLUMN CUSTOMER.CUSTOMER_ID IS 
'Идентификатор заказчика';

COMMENT ON COLUMN CUSTOMER.NAME IS 
'Наименование заказчика';

COMMENT ON COLUMN CUSTOMER.ADDRESS IS 
'Адрес';

COMMENT ON COLUMN CUSTOMER.ZIPCODE IS 
'Почтовый индекс';

COMMENT ON COLUMN CUSTOMER.PHONE IS 
'Телефон';

COMMENT ON COLUMN INVOICE.INVOICE_ID IS 
'Номер счёт-фактуры';

COMMENT ON COLUMN INVOICE.CUSTOMER_ID IS 
'Идентификатор заказчика';

COMMENT ON COLUMN INVOICE.INVOICE_DATE IS 
'Дата выписки счёт-фактуры';

COMMENT ON COLUMN INVOICE.TOTAL_SALE IS 
'Продано на сумму';

COMMENT ON COLUMN INVOICE.PAYED IS 
'Оплачено';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_LINE_ID IS 
'Идентификатор строки счёт-фактуры';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_ID IS 
'Идентификатор счёт-фактуры';

COMMENT ON COLUMN INVOICE_LINE.PRODUCT_ID IS 
'Идентификатор счёт-продукты';

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

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

COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS 
'Идентификатор продукта';

COMMENT ON COLUMN PRODUCT.NAME IS 
'Наименование продукта';

COMMENT ON COLUMN PRODUCT.PRICE IS 
'Стоимость';

COMMENT ON COLUMN PRODUCT.DESCRIPTION IS 
'Описание продукта';


/* Параметры процедур */
COMMENT ON PARAMETER SP_ADD_INVOICE.CUSTOMER_ID IS 
'Код заказчика';

COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_DATE IS 
'Дата';

COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_ID IS 
'Код счёт-фактуры';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.INVOICE_ID IS 
'Код счёт-фактуры';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.PRODUCT_ID IS 
'Код продукта';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.QUANTITY IS 
'Количество';

COMMENT ON PARAMETER SP_DELETE_INVOICE.INVOICE_ID IS 
'Код счёт-фактуры';

COMMENT ON PARAMETER SP_DELETE_INVOICE_LINE.INVOICE_LINE_ID IS 
'Код строки счёт-фактуры';

COMMENT ON PARAMETER SP_EDIT_INVOICE.CUSTOMER_ID IS 
'Код заказчика';

COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_DATE IS 
'Дата';

COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_ID IS 
'Код счёт-фактуры';

COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.INVOICE_LINE_ID IS 
'Код строки счёт-фактуры';

COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.QUANTITY IS 
'Количество';

COMMENT ON PARAMETER SP_PAY_FOR_INOVICE.INVOICE_ID IS 
'Код счёт-фактуры';


/********************************************/
/* Привилегии                               */
/********************************************/

/* Для пользователей */
GRANT SELECT ON TABLE CUSTOMER TO USER manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE CUSTOMER TO USER superuser;
GRANT SELECT ON TABLE PRODUCT TO USER manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE product TO USER superuser;
GRANT SELECT ON TABLE INVOICE TO USER manager, USER superuser;
GRANT SELECT ON TABLE INVOICE_LINE TO USER manager, USER superuser;

GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO USER manager, USER superuser;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO USER manager, USER superuser;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO USER manager, USER superuser;
GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INOVICE TO USER manager, USER superuser;


GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO USER manager, USER superuser;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO USER manager, USER superuser;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO USER manager, USER superuser;

/* Для процедур */
GRANT INSERT ON INVOICE TO PROCEDURE SP_ADD_INVOICE;
GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT INSERT ON INVOICE_LINE TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT SELECT ON PRODUCT TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT SELECT, DELETE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE;
GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE_LINE;
GRANT SELECT, DELETE ON INVOICE_LINE TO PROCEDURE SP_DELETE_INVOICE_LINE;
GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE;
GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE_LINE;
GRANT SELECT, UPDATE ON INVOICE_LINE TO PROCEDURE SP_EDIT_INVOICE_LINE;
GRANT SELECT ON PRODUCT TO PROCEDURE SP_EDIT_INVOICE_LINE;
GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_PAY_FOR_INOVICE;

