CREATE TABLE Lineitem (
	Orderkey INTEGER,
	Partkey INTEGER,
	Suppkey INTEGER,
	Linenumber INTEGER,
	Quantity NUMERIC(12,2),
	Extendedprice NUMERIC(12,2),
	Discount NUMERIC(12,2),
	Tax NUMERIC(12,2),
	Returnflag CHAR(1),
	Linestatus CHAR(1),
	Shipdate DATE,
	Commitdate DATE,
	Receiptdate DATE,
	Shipinstruct CHAR(25),
	Shipmode CHAR(10),
	Comment CHAR(44),
	PRIMARY KEY (Orderkey,Linenumber),
	FOREIGN KEY (Orderkey) REFERENCES Orders
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	FOREIGN KEY (Partkey) REFERENCES Part
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	FOREIGN KEY (Suppkey) REFERENCES Supplier
	ON UPDATE CASCADE
	ON DELETE CASCADE
);

CREATE FUNCTION calcula_extendedprice() RETURNS opaque AS
'DECLARE
	item INTEGER;
	pieza INTEGER;
	cantidad lineitem.quantity%TYPE;
	precio part.retailprice%TYPE;
	seleccion RECORD;
BEGIN
	item:=NEW.linenumber;
	pieza:=NEW.partkey;
	cantidad:=NEW.quantity;
	SELECT INTO seleccion retailprice FROM part WHERE partkey=pieza;
	precio:=seleccion.retailprice;
	NEW.extendedprice=(cantidad*precio);
  	RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION calcula_orderstatus() RETURNS opaque AS
'DECLARE
	pedidos INTEGER;
	efes RECORD;
	oes RECORD;
	seleccion RECORD;
	precio Orders.Totalprice%TYPE;
BEGIN
	pedidos:=NEW.Orderkey;
	SELECT INTO seleccion COUNT(*),sum(extendedprice*(1+tax)*(1-discount)) AS suma FROM lineitem WHERE orderkey=pedidos;
	SELECT INTO efes COUNT(*) FROM Lineitem WHERE Orderkey=pedidos and Linestatus=''F'';
	SELECT INTO oes COUNT(*) FROM Lineitem WHERE Orderkey=pedidos and Linestatus=''O'';
	precio:=seleccion.suma;
	IF seleccion.count=efes.count THEN
		UPDATE Orders SET Orderstatus=''F'',Totalprice=precio
		WHERE Orderkey=pedidos;
		ELSE IF seleccion.count=oes.count THEN
			UPDATE Orders SET Orderstatus=''O'',Totalprice=precio
			WHERE Orderkey=pedidos;
			ELSE
				UPDATE Orders SET Orderstatus=''P'',Totalprice=precio
				WHERE Orderkey=pedidos;
		END IF;
	END IF;

  	RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_orderstatus 
AFTER INSERT ON Lineitem FOR EACH ROW EXECUTE PROCEDURE calcula_orderstatus();

CREATE TRIGGER trigger_extendedprice
BEFORE INSERT ON Lineitem FOR EACH ROW EXECUTE PROCEDURE calcula_extendedprice();