CREATE EXCEPTION EX_DETALLE_PRC
'El precio en el Detalle no puede ser menor al precio base del Producto.';

CREATE EXCEPTION EX_DETALLE_QTY
'La cantidad de productos del Detalle debe ser al menos 1.';

CREATE TABLE CLIENTE
(
  CODCLI bigint NOT NULL,
  NOMCLI varchar(32) NOT NULL,
  CONSTRAINT INTEG_2 PRIMARY KEY (CODCLI)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CLIENTE TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE PRODUCTO
(
  ID bigint NOT NULL,
  DESCR varchar(64) NOT NULL,
  PRECIO_BASE double precision NOT NULL,
  PRECIO_COSTO double precision NOT NULL,
  STOCK integer,
  CONSTRAINT INTEG_8 PRIMARY KEY (ID)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PRODUCTO TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE VENTA
(
  NRO bigint NOT NULL,
  CODCLI bigint NOT NULL,
  IMPORTE double precision,
  CONSTRAINT INTEG_5 PRIMARY KEY (NRO)
);
ALTER TABLE VENTA ADD CONSTRAINT FK_VENTA_CODCLI
  FOREIGN KEY (CODCLI) REFERENCES CLIENTE (CODCLI);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON VENTA TO  SYSDBA WITH GRANT OPTION;


CREATE TABLE DETALLEVENTA
(
  NRO bigint NOT NULL,
  ID bigint NOT NULL,
  CANTIDAD integer NOT NULL,
  PRECIO double precision NOT NULL,
  CONSTRAINT PK_DETALLEVENTA PRIMARY KEY (NRO,ID)
);
ALTER TABLE DETALLEVENTA ADD CONSTRAINT FK_DETALLEVENTA_ID
  FOREIGN KEY (ID) REFERENCES PRODUCTO (ID);
ALTER TABLE DETALLEVENTA ADD CONSTRAINT FK_DETALLEVENTA_NRO
  FOREIGN KEY (NRO) REFERENCES VENTA (NRO);
ALTER TABLE DETALLEVENTA ADD CONSTRAINT INTEG_16
  check (cantidad > 0 );
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON DETALLEVENTA TO  SYSDBA WITH GRANT OPTION;

SET TERM ^ ;
CREATE TRIGGER TGR_AIDETALLEVENTA0 FOR DETALLEVENTA ACTIVE
AFTER insert POSITION 0
as
begin
    update PRODUCTO SET STOCK = STOCK - :NEW.CANTIDAD where ID = NEW.ID ;
end^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER TGR_AIDETALLEVENTA1 FOR DETALLEVENTA ACTIVE
AFTER insert POSITION 1
as
begin
    update VENTA SET IMPORTE = IMPORTE + (NEW.CANTIDAD*NEW.PRECIO) where NRO = NEW.NRO ;
end^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER TGR_BIDETALLEVENTA1 FOR DETALLEVENTA ACTIVE
BEFORE insert POSITION 1
as
declare variable PRECIO_BASE type of column PRODUCTO.PRECIO_BASE;
begin
    select P.PRECIO_BASE from PRODUCTO as P where P.ID = new.ID
        into PRECIO_BASE;
    if (new.PRECIO < PRECIO_BASE) then exception EX_DETALLE_PRC;
end^
SET TERM ; ^

SET TERM ^ ;
CREATE PROCEDURE EXE_ABM_DETALLEVENTA (
    ACCION varchar(1),
    NRO bigint,
    ID bigint,
    CANTIDAD integer,
    PRECIO_BASE double precision )
RETURNS (
    ESTADO integer )
AS
begin
    execute statement (
        case upper(ACCION)
            when 'A' then
                'insert into DETALLEVENTA values ('
                || :NRO || ','
                || :ID || ','
                || :CANTIDAD || ','
                || :PRECIO_BASE || ');'
            when 'B' then
                'delete from DETALLEVENTA as D where ' || :NRO || '= D.NRO AND '|| :ID || '= D.ID ;'
            when 'M' then
                'update DETALLEVENTA
                set CANTIDAD = coalesce(' || :CANTIDAD || ',CANTIDAD),
                    PRECIO_BASE = coalesce(' || :PRECIO_BASE || ',PRECIO_BASE)
                where ' || :NRO || ' = NRO AND ' || :ID || ' = ID ;'
            else 'exception;'
        end);
    ESTADO = 0;
    when any do ESTADO = 22023; --error
end^
SET TERM ; ^
GRANT EXECUTE
 ON PROCEDURE EXE_ABM_DETALLEVENTA TO  SYSDBA WITH GRANT OPTION;

SET TERM ^ ;
CREATE PROCEDURE EXE_ABM_PRODUCTO (
    ACCION varchar(1),
    ID bigint,
    DESCR varchar(64),
    STOCK integer,
    PRECIO_BASE double precision,
    PRECIO_COSTO double precision )
RETURNS (
    ESTADO integer )
AS
begin
    execute statement (
        case upper(ACCION)
            when 'A' then
                'insert into PRODUCTO values ('
                || :ID || ','
                || :DESCR || ','
                || :STOCK || ','
                || :PRECIO_BASE || ','
                || :PRECIO_COSTO || ');'
            when 'B' then
                'delete from PRODUCTO as P where ' || :ID || '= P.ID;'
            when 'M' then
                'update PRODUCTO
                set DESCR = coalesce(''' || :DESCR || ''',DESCR),
                    STOCK = coalesce(' || :STOCK || ',STOCK),
                    PRECIO_BASE = coalesce(' || :PRECIO_BASE || ',PRECIO_BASE),
                    PRECIO_COSTO = coalesce(' || :PRECIO_COSTO || ',PRECIO_COSTO)
                where ' || :ID || ' = ID;'
            else 'exception;'
        end);
    ESTADO = 0;
    when any do ESTADO = 22023; --error
end^
SET TERM ; ^
GRANT EXECUTE
 ON PROCEDURE EXE_ABM_PRODUCTO TO  SYSDBA WITH GRANT OPTION;

