/********************* ROLES **********************/

/********************* UDFS ***********************/

/****************** SEQUENCES ********************/

/******************** DOMAINS *********************/

CREATE DOMAIN MON$SEC_DATABASE
 AS char(7) CHARACTER SET ASCII
 NOT NULL
 COLLATE ASCII;
CREATE DOMAIN SEC$KEY
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$NAME_PART
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$USER_NAME
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
CREATE DOMAIN SEC$VALUE
 AS varchar(85) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;
/******************* PROCEDURES ******************/

/******************** TABLES **********************/

CREATE TABLE CLIENTE
(
  CODIGO integer NOT NULL,
  RAZON varchar(100) NOT NULL,
  TIVA char(1) DEFAULT '1',
  SALDO double precision DEFAULT 0 NOT NULL,
  CONSTRAINT PK_CLIENTE PRIMARY KEY (CODIGO)
);
CREATE TABLE DETALLE
(
  NRO integer NOT NULL,
  IDP integer NOT NULL,
  CANT integer NOT NULL,
  PRECIO double precision DEFAULT 0 NOT NULL,
  ST double precision DEFAULT 0 NOT NULL,
  CONSTRAINT PK_DETALLE PRIMARY KEY (NRO,IDP)
);
CREATE TABLE FACTURA
(
  NRO integer NOT NULL,
  FECHA date DEFAULT 'NOW' NOT NULL,
  CODIGO integer NOT NULL,
  GRA double precision DEFAULT 0 NOT NULL,
  NOGRA double precision DEFAULT 0 NOT NULL,
  IVA1 double precision DEFAULT 0 NOT NULL,
  IVA2 double precision DEFAULT 0 NOT NULL,
  MONTO double precision DEFAULT 0 NOT NULL,
  ESTADO char(1) DEFAULT '0',
  CONSTRAINT PK_FACTURA PRIMARY KEY (NRO)
);
CREATE TABLE PRODUCTO
(
  IDP integer NOT NULL,
  DESCR varchar(100) NOT NULL,
  GRAVADO char(1) DEFAULT '1',
  PRECIO double precision NOT NULL,
  CONSTRAINT PK_PRODUCTO PRIMARY KEY (IDP)
);
/********************* VIEWS **********************/

/******************* EXCEPTIONS *******************/

CREATE EXCEPTION EX_CLIENTE1
'Cliente con facturas iniciadas no puede modificarse';
CREATE EXCEPTION EX_FACTURA1
'Factura en estado Terminada no puede modificarse';
/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER TRG_ADDETALLE FOR DETALLE ACTIVE
AFTER delete POSITION 0
AS
    DECLARE VARIABLE GRA TYPE OF COLUMN FACTURA.GRA;
    DECLARE VARIABLE NOGRA TYPE OF COLUMN FACTURA.NOGRA;
    DECLARE VARIABLE IVA1 TYPE OF COLUMN FACTURA.IVA1;
    DECLARE VARIABLE IVA2 TYPE OF COLUMN FACTURA.IVA2;
    DECLARE VARIABLE MONTO TYPE OF COLUMN FACTURA.MONTO;
BEGIN
    /* actualizo factura */ 
    SELECT GRA,NOGRA,IVA1,IVA2,MONTO 
        FROM FACTURA 
        WHERE NRO = :OLD.NRO 
        INTO :GRA,:NOGRA,:IVA1,:IVA2,:MONTO;
    IF ( EXISTS(SELECT * FROM PRODUCTO WHERE IDP = :OLD.IDP AND GRAVADO = '1') ) THEN 
        GRA = GRA - OLD.ST;
    ELSE 
        NOGRA = NOGRA - OLD.ST;
    IF ( EXISTS(SELECT * FROM CLIENTE C 
                        INNER JOIN FACTURA F ON C.CODIGO = F.CODIGO 
                        WHERE F.NRO = :OLD.NRO AND C.TIVA = '1') ) THEN
        IVA1 = GRA * 0.21;
    MONTO = GRA + NOGRA + IVA1 + IVA2;
    UPDATE FACTURA SET GRA = :GRA, NOGRA = :NOGRA, IVA1 = :IVA1, IVA2 = :IVA2, MONTO = :MONTO 
    WHERE NRO = :OLD.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIDETALLE FOR DETALLE ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    /* actualizo factura */
    IF ( EXISTS(SELECT * FROM PRODUCTO WHERE IDP = :NEW.IDP AND GRAVADO = '1') ) THEN 
        UPDATE FACTURA SET GRA = GRA + :NEW.ST WHERE NRO = :NEW.NRO;
    ELSE 
        UPDATE FACTURA SET NOGRA = NOGRA + :NEW.ST WHERE NRO = :NEW.NRO;
    IF ( EXISTS(SELECT * FROM CLIENTE C 
                        INNER JOIN FACTURA F ON C.CODIGO = F.CODIGO 
                        WHERE F.NRO = :NEW.NRO AND C.TIVA = '1') ) THEN
        UPDATE FACTURA SET IVA1 = GRA * 0.21 WHERE NRO = :NEW.NRO;
    UPDATE FACTURA SET MONTO = GRA+NOGRA+IVA1+IVA2 WHERE NRO = :NEW.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AUDETALLE FOR DETALLE ACTIVE
AFTER update POSITION 0
AS
    DECLARE VARIABLE GRA TYPE OF COLUMN FACTURA.GRA;
    DECLARE VARIABLE NOGRA TYPE OF COLUMN FACTURA.NOGRA;
    DECLARE VARIABLE IVA1 TYPE OF COLUMN FACTURA.IVA1;
    DECLARE VARIABLE IVA2 TYPE OF COLUMN FACTURA.IVA2;
    DECLARE VARIABLE MONTO TYPE OF COLUMN FACTURA.MONTO;
BEGIN
    /* actualizo factura */ 
    SELECT GRA,NOGRA,IVA1,IVA2,MONTO 
        FROM FACTURA 
        WHERE NRO = :NEW.NRO 
        INTO :GRA,:NOGRA,:IVA1,:IVA2,:MONTO;
    IF ( EXISTS(SELECT * FROM PRODUCTO WHERE IDP = :NEW.IDP AND GRAVADO = '1') ) THEN 
        GRA = GRA - OLD.ST + NEW.ST;
    ELSE 
        NOGRA = NOGRA - OLD.ST + NEW.ST;
    IF ( EXISTS(SELECT * FROM CLIENTE C 
                        INNER JOIN FACTURA F ON C.CODIGO = F.CODIGO 
                        WHERE F.NRO = :NEW.NRO AND C.TIVA = '1') ) THEN
        IVA1 = GRA * 0.21;
    MONTO = GRA + NOGRA + IVA1 + IVA2;
    UPDATE FACTURA SET GRA = :GRA, NOGRA = :NOGRA, IVA1 = :IVA1, IVA2 = :IVA2, MONTO = :MONTO 
    WHERE NRO = :NEW.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AUFACTURA FOR FACTURA ACTIVE
AFTER update POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    IF ( OLD.ESTADO <> NEW.ESTADO AND NEW.ESTADO = '1' ) THEN 
        UPDATE CLIENTE SET SALDO = SALDO + :NEW.MONTO WHERE CODIGO = NEW.CODIGO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BDDETALLE FOR DETALLE ACTIVE
BEFORE delete POSITION 0
AS
BEGIN
    /* si la factura esta terminada no puede modificarse */ 
    IF ( EXISTS(SELECT * FROM FACTURA WHERE NRO = :OLD.NRO AND ESTADO = '1') ) THEN 
        EXCEPTION EX_FACTURA1;    
    
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIDETALLE FOR DETALLE ACTIVE
BEFORE insert POSITION 0
AS
    DECLARE VARIABLE PRECIO TYPE OF COLUMN PRODUCTO.PRECIO;
BEGIN
    /* enter trigger code here */ 
    IF ( EXISTS(SELECT * FROM FACTURA WHERE NRO = :NEW.NRO AND ESTADO = '1') ) THEN 
        EXCEPTION EX_FACTURA1;
    SELECT PRECIO FROM PRODUCTO WHERE IDP = :NEW.IDP INTO :PRECIO;
    -- Tasa iva de cliente vs precio de productos
    IF ( (SELECT TIVA FROM CLIENTE C 
                    INNER JOIN FACTURA F ON F.CODIGO = C.CODIGO 
                    WHERE F.NRO = :NEW.NRO) = '2' ) THEN
        IF ( EXISTS(SELECT * FROM PRODUCTO WHERE IDP = :NEW.IDP AND GRAVADO = '1') ) THEN
            NEW.PRECIO = PRECIO * 1.21;
        ELSE 
            NEW.PRECIO = PRECIO;
    ELSE 
        NEW.PRECIO = PRECIO;
    NEW.ST = NEW.CANT*NEW.PRECIO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIFACTURA FOR FACTURA ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    NEW.FECHA = 'NOW';
    NEW.ESTADO = '0';
    NEW.GRA = 0.0;
    NEW.NOGRA = 0.0;
    NEW.IVA1 = 0.0;
    NEW.IVA2 = 0.0;
    NEW.MONTO = 0.0;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUCLIENTE FOR CLIENTE ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* enter trigger code here */
    IF ( NEW.CODIGO <> OLD.CODIGO OR NEW.TIVA <> OLD.TIVA ) THEN 
        IF ( EXISTS(SELECT * FROM FACTURA WHERE CODIGO IN(:NEW.CODIGO,:OLD.CODIGO) AND ESTADO = '0') ) THEN 
            EXCEPTION EX_CLIENTE1;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUDETALLE FOR DETALLE ACTIVE
BEFORE update POSITION 0
AS
    DECLARE VARIABLE PRECIO TYPE OF COLUMN PRODUCTO.PRECIO;
BEGIN
    /* enter trigger code here */
    IF ( EXISTS(SELECT * FROM FACTURA WHERE NRO = :NEW.NRO AND ESTADO = '1') ) THEN 
        EXCEPTION EX_FACTURA1;
    NEW.NRO = OLD.NRO;
    NEW.IDP = OLD.IDP;
    SELECT PRECIO FROM PRODUCTO WHERE IDP = :NEW.IDP INTO :PRECIO;
    -- Tasa iva de cliente vs precio de productos
    IF ( (SELECT TIVA FROM CLIENTE C 
                    INNER JOIN FACTURA F ON F.CODIGO = C.CODIGO 
                    WHERE F.NRO = :NEW.NRO) = '2' ) THEN
        IF ( EXISTS(SELECT * FROM PRODUCTO WHERE IDP = :NEW.IDP AND GRAVADO = '1') ) THEN
            NEW.PRECIO = PRECIO * 1.21;
        ELSE 
            NEW.PRECIO = PRECIO;
    ELSE 
        NEW.PRECIO = PRECIO;
    NEW.ST = NEW.CANT*NEW.PRECIO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUFACTURA FOR FACTURA ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* enter trigger code here */
    IF ( OLD.ESTADO = '1' ) THEN EXCEPTION EX_FACTURA1;
    /* ignoro los cambios en nro, fecha, codigo */
    NEW.NRO = OLD.NRO;
    NEW.FECHA = OLD.FECHA;
    NEW.CODIGO = OLD.CODIGO; 
END^
SET TERM ; ^

ALTER TABLE DETALLE ADD CONSTRAINT FK_DETALLE_FACTURA
  FOREIGN KEY (NRO) REFERENCES FACTURA (NRO);
ALTER TABLE DETALLE ADD CONSTRAINT FK_DETALLE_PRODUCTO
  FOREIGN KEY (IDP) REFERENCES PRODUCTO (IDP);
ALTER TABLE FACTURA ADD CONSTRAINT FK_FACTURA_CLIENTE
  FOREIGN KEY (CODIGO) REFERENCES CLIENTE (CODIGO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CLIENTE TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON DETALLE TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON FACTURA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PRODUCTO TO  SYSDBA WITH GRANT OPTION;

