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

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

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

CREATE SEQUENCE GEN_LOG;
/******************** 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 ******************/

SET TERM ^ ;
CREATE PROCEDURE PFACTURA (
    NRO integer,
    IDP integer,
    ST double precision )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE P_ABM_CLIENTE (
    ACCION char(1),
    CODIGO integer,
    RAZON varchar(100),
    TIVA char(1),
    SALDO double precision )
RETURNS (
    RESULTADO char(5),
    MENSAJE varchar(100) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE P_CLIENTE
AS
BEGIN SUSPEND; END^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE SP_FACTURA (
    IANIO integer )
RETURNS (
    ANIO integer,
    TRIM1 double precision,
    TRIM2 double precision,
    TRIM3 double precision,
    TRIM4 double precision,
    TOTAL double precision )
AS
BEGIN SUSPEND; END^
SET TERM ; ^

/******************** 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 LOG
(
  ID integer NOT NULL,
  FECHA timestamp DEFAULT 'NOW' NOT NULL,
  USUARIO char(32) DEFAULT USER NOT NULL,
  TABLA varchar(100) DEFAULT 'N/A',
  PROCEDIMIENTO varchar(100) DEFAULT 'N/A',
  ACCION char(1) DEFAULT 'A',
  MENSAJE varchar(100) DEFAULT 'N/A',
  CONSTRAINT PK_LOG PRIMARY KEY (ID)
);
CREATE TABLE PARAM
(
  CLAVE char(30) NOT NULL,
  VALOR varchar(255) NOT NULL,
  CONSTRAINT PK_PARAM PRIMARY KEY (CLAVE)
);
CREATE TABLE PRODUCTO
(
  IDP integer NOT NULL,
  DESCR varchar(100) NOT NULL,
  GRAVADO char(1) DEFAULT '1',
  PRECIO double precision NOT NULL,
  STOCK integer,
  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 o Anulada no puede modificarse';
CREATE EXCEPTION EX_PK
'No se permiten cambios en atributos clave';
CREATE EXCEPTION EX_SUBTOTAL
'Subtotal no puede ser cero o negativo';
/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER TRG_ADDETALLE FOR DETALLE ACTIVE
AFTER delete POSITION 0
AS
BEGIN
    --DESCUENTO STOCK DE PRODUCTO
    UPDATE PRODUCTO SET STOCK = STOCK + :OLD.CANT WHERE IDP = :OLD.IDP;
    --CALCULAMOS FACTURA
    EXECUTE PROCEDURE PFACTURA(:OLD.NRO,:OLD.IDP,-:OLD.ST);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIDETALLE FOR DETALLE ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    --DESCUENTO STOCK DE PRODUCTO
    UPDATE PRODUCTO SET STOCK = STOCK - :NEW.CANT WHERE IDP = :NEW.IDP;
    --CALCULAMOS FACTURA
    EXECUTE PROCEDURE PFACTURA(:NEW.NRO,:NEW.IDP,:NEW.ST);

END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AIFACTURA FOR FACTURA ACTIVE
AFTER insert POSITION 0
AS
BEGIN
    --CONVERSION AUTOMATICA DE INTEGER A CHAR??? 
    UPDATE PARAM SET VALOR = :NEW.NRO WHERE CLAVE = 'UNRO';
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_AUDETALLE FOR DETALLE ACTIVE
AFTER update POSITION 0
AS
BEGIN
    -- ACTUALIZAMOS STOCK DE PRODUCTO 
    IF ( OLD.CANT <> NEW.CANT ) THEN UPDATE PRODUCTO SET STOCK = STOCK - :NEW.CANT + :OLD.CANT WHERE IDP = :NEW.IDP;
    --CALCULAMOS FACTURA
    EXECUTE PROCEDURE PFACTURA(:NEW.NRO,:NEW.IDP,:NEW.ST-:OLD.ST);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BDDETALLE FOR DETALLE ACTIVE
BEFORE delete POSITION 0
AS
BEGIN
    IF ( (SELECT ESTADO FROM FACTURA WHERE NRO = :OLD.NRO) <> 'I' ) THEN EXCEPTION EX_FACTURA1; 
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIDETALLE FOR DETALLE ACTIVE
BEFORE insert POSITION 0
AS
    DECLARE VARIABLE FCODIGO INTEGER;
BEGIN
    --RESULTADO DEL QUERY PUEDE SER EL PRECIO O NULL
    IF ( (SELECT ESTADO FROM FACTURA WHERE NRO = :NEW.NRO) <> 'I' ) THEN EXCEPTION EX_FACTURA1; 
    SELECT CODIGO FROM FACTURA WHERE NRO = :NEW.NRO INTO :FCODIGO;
    SELECT PRECIO FROM PRODUCTO WHERE IDP = :NEW.IDP INTO :NEW.PRECIO;  --CURSOR IMPLICITO
    IF ( (SELECT TIVA FROM CLIENTE WHERE CODIGO = :FCODIGO) = '2' ) THEN NEW.PRECIO = NEW.PRECIO * 1.21;
    IF ( NEW.CANT <= 0 ) THEN NEW.CANT = 1;
    NEW.ST = NEW.CANT * NEW.PRECIO;
    IF (NEW.ST <= 0) THEN EXCEPTION EX_SUBTOTAL;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BIFACTURA FOR FACTURA ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    SELECT CAST(VALOR AS INTEGER)+1 FROM PARAM WHERE CLAVE = 'UNRO' INTO :NEW.NRO;
    NEW.ESTADO = 'I'; -- ESTADO DE FACTURA INICIADA
    NEW.FECHA = 'TODAY';
    NEW.GRA=0.0;
    NEW.IVA1=0.0;
    NEW.IVA2=0.0;
    NEW.NOGRA=0.0;
    NEW.MONTO=0.0;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BILOG FOR LOG ACTIVE
BEFORE insert POSITION 0
AS
BEGIN
    /* enter trigger code here */ 
    NEW.ID = GEN_ID(GEN_LOG,1);
    NEW.USUARIO = USER;
    NEW.FECHA = CURRENT_TIMESTAMP;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUDETALLE FOR DETALLE ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    /* ojo! falta considerar el cambio de la cantidad para sumar/restar del stock de producto */
    -- si hay cambios en Pk entonces error
    IF ( (NEW.NRO <> OLD.NRO) OR (NEW.IDP <> OLD.IDP)) THEN EXCEPTION EX_PK;
    IF ( (SELECT ESTADO FROM FACTURA WHERE NRO = :NEW.NRO) <> 'I' ) THEN EXCEPTION EX_FACTURA1; 
    IF ( NEW.CANT <= 0 ) THEN NEW.CANT = 1;
    NEW.PRECIO = OLD.PRECIO;
    NEW.ST = NEW.PRECIO * NEW.CANT;
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRG_BUFACTURA FOR FACTURA ACTIVE
BEFORE update POSITION 0
AS
BEGIN
    IF ( NEW.NRO <> OLD.NRO ) THEN EXCEPTION EX_PK;
END^
SET TERM ; ^

SET TERM ^ ;
ALTER PROCEDURE PFACTURA (
    NRO integer,
    IDP integer,
    ST double precision )
AS
DECLARE VARIABLE FGRA       DOUBLE PRECISION;
    DECLARE VARIABLE FNOGRA     DOUBLE PRECISION;
    DECLARE VARIABLE FIVA1      DOUBLE PRECISION;
    DECLARE VARIABLE FIVA2      DOUBLE PRECISION;
    DECLARE VARIABLE FMONTO     DOUBLE PRECISION;
    DECLARE VARIABLE FTIVA      CHAR(1);
BEGIN
    --CALCULAMOS FACTURA
    --RECUPERAMOS DATOS DE LA FACTURA-CLIENTE Y HACEMOS CALCULOS EN MEMORIA
    SELECT F.GRA,F.NOGRA,F.IVA1,F.IVA2,C.TIVA 
    FROM FACTURA F INNER JOIN CLIENTE C ON F.CODIGO = C.CODIGO 
    WHERE NRO = :NRO INTO :FGRA,:FNOGRA,:FIVA1,:FIVA2,:FTIVA;
    IF ( (SELECT GRAVADO FROM PRODUCTO WHERE IDP = :IDP) = '1' ) THEN BEGIN --PRODUCTO GRAVADO
        FGRA = FGRA + :ST;
    END ELSE BEGIN --PRODUCTO NO GRAVADO
        FNOGRA = FNOGRA + :ST;
    END 
    IF ( FTIVA = '1' ) THEN BEGIN 
        -- CLIENTE RESPONSABLE INSCRIPTO
        FIVA1 = FGRA * 0.21;
    END 
    FMONTO = FGRA+FNOGRA+FIVA1+FIVA2;
    UPDATE FACTURA SET GRA = :FGRA, NOGRA = :FNOGRA, IVA1 = :FIVA1, IVA2 = :FIVA2, MONTO = :FMONTO WHERE NRO = :NRO;
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE P_ABM_CLIENTE (
    ACCION char(1),
    CODIGO integer,
    RAZON varchar(100),
    TIVA char(1),
    SALDO double precision )
RETURNS (
    RESULTADO char(5),
    MENSAJE varchar(100) )
AS
BEGIN
    RESULTADO = 'OK';
    MENSAJE = '';
    IF ( ACCION = 'A' ) THEN BEGIN 
        INSERT INTO CLIENTE(CODIGO,RAZON,TIVA,SALDO) VALUES(:CODIGO,:RAZON,:TIVA,:SALDO);
    END 
    IF ( ACCION = 'B' ) THEN BEGIN 
        DELETE FROM CLIENTE WHERE CODIGO = :CODIGO;
    END 
    IF ( ACCION = 'C' ) THEN BEGIN 
        UPDATE CLIENTE SET RAZON = :RAZON, TIVA = :TIVA, SALDO = :SALDO WHERE CODIGO = :CODIGO;
    END
    WHEN ANY DO BEGIN 
        RESULTADO = 'ERROR';
        MENSAJE = 'ERROR EN ACCION=' || :ACCION || ' SQLCODE=' || SQLCODE;
        INSERT INTO LOG(TABLA,PROCEDIMIENTO,ACCION,MENSAJE) VALUES('CLIENTE','P_ABM_CLIENTE',:ACCION,:MENSAJE);
    END
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE P_CLIENTE
AS
DECLARE VARIABLE RES  CHAR(5);
    DECLARE VARIABLE MSG  VARCHAR(100);  
BEGIN
    EXECUTE PROCEDURE P_ABM_CLIENTE('A',102,'CLIENTE 102',1,150.0)
    RETURNING_VALUES(:RES,:MSG);
END^
SET TERM ; ^


SET TERM ^ ;
ALTER PROCEDURE SP_FACTURA (
    IANIO integer )
RETURNS (
    ANIO integer,
    TRIM1 double precision,
    TRIM2 double precision,
    TRIM3 double precision,
    TRIM4 double precision,
    TOTAL double precision )
AS
DECLARE VARIABLE SQLCMD VARCHAR(3000);      --COMANDO SQL
    DECLARE VARIABLE TTOTAL DOUBLE PRECISION;   --GUARDO TOTAL DEL TRIMESTRE
    DECLARE VARIABLE FMES INTEGER;              --PRIMER MES DEL TRIMESTRE
BEGIN
  /* write your code here */ 
  FOR SELECT EXTRACT(YEAR FROM F.FECHA) FROM FACTURA F 
    WHERE EXTRACT(YEAR FROM F.FECHA) = COALESCE(:IANIO,EXTRACT(YEAR FROM F.FECHA))
    GROUP BY EXTRACT(YEAR FROM F.FECHA) INTO :ANIO DO BEGIN
    -- PROCESO ANIO
    FMES=1;
    WHILE(FMES <= 10) DO BEGIN 
        SQLCMD = 'SELECT COALESCE(SUM(F.MONTO),0) FROM FACTURA F WHERE EXTRACT(YEAR FROM F.FECHA) = ' || :ANIO || 
            ' AND EXTRACT(MONTH FROM F.FECHA) BETWEEN ' || :FMES || ' AND ' || (:FMES+2);
        EXECUTE STATEMENT SQLCMD INTO :TTOTAL;
        IF (FMES BETWEEN 1 AND 3) THEN TRIM1=TTOTAL;
        IF (FMES BETWEEN 4 AND 6) THEN TRIM2=TTOTAL;
        IF (FMES BETWEEN 7 AND 9) THEN TRIM3=TTOTAL;
        IF (FMES BETWEEN 10 AND 12) THEN TRIM4=TTOTAL;
        FMES=FMES+3;
    END 
    TOTAL=TRIM1+TRIM2+TRIM3+TRIM4;
    SUSPEND;
  END  
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 EXECUTE
 ON PROCEDURE PFACTURA TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE P_ABM_CLIENTE TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE P_CLIENTE TO  SYSDBA WITH GRANT OPTION;

GRANT EXECUTE
 ON PROCEDURE SP_FACTURA TO  SYSDBA WITH GRANT OPTION;

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 LOG TO  SYSDBA WITH GRANT OPTION;

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

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

