/********************* 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 FACTURA
(
  NRO integer NOT NULL,
  FECHA date NOT NULL,
  MONTO double precision NOT NULL,
  ESTADO smallint NOT NULL,
  CONSTRAINT PK_FACTURA PRIMARY KEY (NRO)
);
CREATE TABLE FACTURA_AUX
(
  NRO integer NOT NULL,
  SALDOD double precision NOT NULL,
  CONSTRAINT PK_FACTURA_AUX PRIMARY KEY (NRO)
);
CREATE TABLE PAGO
(
  NRO integer NOT NULL,
  FECHA date NOT NULL,
  IMPORTE double precision NOT NULL,
  CONSTRAINT PK_PAGO PRIMARY KEY (NRO,FECHA)
);
CREATE TABLE PAGO_AUX
(
  NRO integer NOT NULL,
  FECHA date NOT NULL,
  CONSTRAINT PK_PAGO_AUX PRIMARY KEY (NRO,FECHA)
);
/********************* VIEWS **********************/

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

CREATE EXCEPTION EX_ERROR
'Rayos! algo anduvo mal';
/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER TRG_ADFACTURA FOR FACTURA ACTIVE
AFTER delete POSITION 0
AS
BEGIN
   DELETE FROM FACTURA_AUX WHERE NRO = OLD.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_ADPAGO FOR PAGO ACTIVE
AFTER delete POSITION 0
AS
BEGIN
   UPDATE FACTURA_AUX SET SALDOD = SALDOD + OLD.IMPORTE WHERE NRO = OLD.NRO;
   DELETE FROM PAGO_AUX WHERE NRO = OLD.NRO AND FECHA = OLD.FECHA;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIFACTURA FOR FACTURA ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    INSERT INTO FACTURA_AUX(NRO,SALDOD) VALUES(NEW.NRO,NEW.MONTO);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIPAGO FOR PAGO ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    UPDATE FACTURA_AUX SET SALDOD = SALDOD - NEW.IMPORTE WHERE NRO = NEW.NRO;
    INSERT INTO PAGO_AUX(NRO,FECHA) VALUES(NEW.NRO,NEW.FECHA);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AUFACTURA FOR FACTURA ACTIVE
AFTER update POSITION 0
AS
BEGIN
   UPDATE FACTURA_AUX SET SALDOD = NEW.MONTO WHERE NRO = NEW.NRO;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIFACTURA FOR FACTURA ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    NEW.MONTO = 0.0; NEW.ESTADO = 1; -- FACTURA INICIADA
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIPAGO FOR PAGO ACTIVE
BEFORE insert POSITION 0
AS
    DECLARE VARIABLE UFECHA TYPE OF COLUMN PAGO_AUX.FECHA;
BEGIN
    IF (NEW.IMPORTE <= 0) THEN EXCEPTION EX_ERROR 'El importe no puede ser menor o igual que cero!';
    SELECT MAX(FECHA) FROM PAGO_AUX WHERE NRO = NEW.NRO INTO :UFECHA;
    IF ( UFECHA IS NULL ) THEN SELECT FECHA FROM FACTURA WHERE NRO = NEW.NRO INTO :UFECHA;
    IF ( NEW.FECHA <= UFECHA ) THEN EXCEPTION EX_ERROR  'La Fecha ' || NEW.FECHA || ' debe ser posterior a ' || :UFECHA;
    IF ( (SELECT ESTADO FROM FACTURA WHERE NRO = NEW.NRO) <> 2 ) THEN EXCEPTION EX_ERROR  'El estado de esta factura impide que ingresemos pagos a la misma';
    IF ( NEW.IMPORTE > (SELECT SALDOD FROM FACTURA_AUX WHERE NRO = NEW.NRO) ) THEN EXCEPTION EX_ERROR  'El importe del pago no puede superar al saldo deudor de la factura';
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUFACTURA FOR FACTURA ACTIVE
BEFORE update POSITION 0
AS
    DECLARE VARIABLE CAMBIO_MONTO SMALLINT;
    DECLARE VARIABLE CAMBIO_ESTADO SMALLINT;
BEGIN
   IF ( NEW.ESTADO NOT IN(1,2,3) ) THEN EXCEPTION EX_ERROR 'El estado solo puede ser 1,2,3';
   IF ( (NEW.NRO <> OLD.NRO) OR (NEW.FECHA <> OLD.FECHA) ) THEN EXCEPTION EX_ERROR 'No puede cambiar la fecha o el nro de la factura';
   CAMBIO_MONTO = 0; CAMBIO_ESTADO = 0;
   IF ( NEW.MONTO <> OLD.MONTO ) THEN CAMBIO_MONTO = 1;
   IF ( NEW.ESTADO <> OLD.ESTADO ) THEN CAMBIO_ESTADO = 1;
   IF ( NOT (CAMBIO_MONTO = 1 AND CAMBIO_ESTADO = 1) ) THEN EXCEPTION EX_ERROR 'Debe cambiar monto y estado al mismo tiempo';
   -- cambio monto y estado!
   IF ( OLD.ESTADO = 2 AND NEW.ESTADO = 1 ) THEN EXCEPTION EX_ERROR 'No puedo iniciar una factura que esta finalizada';
   IF ( OLD.ESTADO = 2 AND NEW.ESTADO = 3 AND EXISTS(SELECT * FROM PAGO WHERE NRO = NEW.NRO) ) THEN EXCEPTION EX_ERROR 'No puede anular una factura que tiene pagos';
   IF ( OLD.ESTADO = 3 AND NEW.ESTADO IN(1,2) ) THEN EXCEPTION EX_ERROR 'No puedo pasar a iniciada o finalizada una factura anulada';
   IF ( NEW.MONTO <= 0.0 AND NEW.ESTADO = 2 ) THEN EXCEPTION EX_ERROR 'El monto no puede ser menor o igual a cero en una factura finalizada'; 
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUPAGO FOR PAGO ACTIVE
BEFORE update POSITION 0
AS
BEGIN
   EXCEPTION EX_ERROR 'No se pueden hacer cambios en pago';
END^
SET TERM ; ^

ALTER TABLE PAGO ADD CONSTRAINT FK_PAGO_FACTURA
  FOREIGN KEY (NRO) REFERENCES FACTURA (NRO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON FACTURA TO  SYSDBA WITH GRANT OPTION;

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

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

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

