UPDATE APM_PACKAGE_VERSIONS SET VERSION_NAME = '0.1d2' WHERE VERSION_NAME = '0.2d2' AND PACKAGE_KEY = 'views';


CREATE TABLE VIEWS_BY_TYPE (
        OBJECT_ID       INTEGER
                        CONSTRAINT VIEWS_BY_TYPE_OBJECT_ID_FK
                        REFERENCES ACS_OBJECTS(OBJECT_ID) ON DELETE CASCADE
                        CONSTRAINT VIEWS_BY_TYPE_OBJECT_ID_NN
                        NOT NULL,
        VIEWER_ID       INTEGER
                        CONSTRAINT VIEWS_BY_TYPE_OWNER_ID_FK
                        REFERENCES PARTIES(PARTY_ID) ON DELETE CASCADE
                        CONSTRAINT VIEWS_BY_TYPE_VIEWER_ID_NN
                        NOT NULL,
        VIEW_TYPE       VARCHAR(100) NOT NULL,
        VIEWS_COUNT     INTEGER DEFAULT 1,
        LAST_VIEWED     DATE DEFAULT SYSDATE,
        CONSTRAINT VIEWS_BY_TYPE_PK 
        PRIMARY KEY (OBJECT_ID, VIEWER_ID, VIEW_TYPE)
);

CREATE UNIQUE INDEX VIEWS_BY_TYPE_VIEWER_IDX ON VIEWS_BY_TYPE(VIEWER_ID, OBJECT_ID, VIEW_TYPE);

COMMENT ON TABLE VIEWS_BY_TYPE IS '
        A SIMPLE COUNT OF HOW MANY TIMES AN OBJECT IS VIEWED FOR EACH TYPE.
';

CREATE TABLE VIEW_AGGREGATES_BY_TYPE (
        OBJECT_ID       INTEGER
                        CONSTRAINT VIEW_AGG_B_TYPE_OB_ID_FK
                        REFERENCES ACS_OBJECTS(OBJECT_ID) ON DELETE CASCADE
                        CONSTRAINT VIEW_AGG_B_TYPE_OB_ID_NN
                        NOT NULL,
        VIEW_TYPE       VARCHAR(100) NOT NULL,
        VIEWS_COUNT     INTEGER DEFAULT 1,
        UNIQUE_VIEWS    INTEGER DEFAULT 1,
        LAST_VIEWED     DATE DEFAULT SYSDATE,
        CONSTRAINT VIEW_AGGREGATES_BY_TYPE_PK
        PRIMARY KEY (OBJECT_ID, VIEW_TYPE)
);

COMMENT ON TABLE VIEW_AGGREGATES_BY_TYPE IS '
        A SIMPLE COUNT OF HOW MANY TIMES AN OBJECT IS VIEWED FOR EACH TYPE,
        MULTIPLE VISITS TRIGGER MAINTAINED BY UPDATES ON VIEWS_BY_TYPE.
';

CREATE OR REPLACE PACKAGE VIEWS_VIEW_BY_TYPE AS 
	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE,
			P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE,
			P_VIEW_TYPE IN  VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE; 
END VIEWS_VIEW_BY_TYPE;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW_BY_TYPE AS 
	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE,
			P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE,
			P_VIEW_TYPE IN  VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE IS 
    	V_VIEWS     VIEWS_VIEWS.VIEWS_COUNT%TYPE;
    	V_COUNT INTEGER;
	BEGIN 
	  
    	SELECT COUNT(*) INTO V_COUNT FROM VIEWS_BY_TYPE WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID AND VIEW_TYPE = P_VIEW_TYPE;

    	IF V_COUNT = 0 THEN 
        	INSERT INTO VIEWS_BY_TYPE(OBJECT_ID,VIEWER_ID,VIEW_TYPE) 
        	VALUES (P_OBJECT_ID, P_VIEWER_ID,P_VIEW_TYPE);
        	V_VIEWS := 0;
    	ELSE
    		SELECT VIEWS_COUNT INTO V_VIEWS FROM VIEWS_BY_TYPE WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID AND VIEW_TYPE = P_VIEW_TYPE;
	        UPDATE VIEWS_BY_TYPE
    	    SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE, VIEW_TYPE = P_VIEW_TYPE
        	WHERE OBJECT_ID = P_OBJECT_ID
           	AND VIEWER_ID = P_VIEWER_ID
           	AND VIEW_TYPE = P_VIEW_TYPE;
    	END IF;

    	RETURN V_VIEWS + 1;
	END RECORD_VIEW;
END VIEWS_VIEW_BY_TYPE;
/
SHOW ERRORS;

CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_INS_TR
AFTER INSERT ON VIEWS_BY_TYPE
FOR EACH ROW
DECLARE 
	V_COUNT INTEGER;
BEGIN
	SELECT COUNT(*) INTO V_COUNT FROM VIEW_AGGREGATES_BY_TYPE WHERE OBJECT_ID = :NEW.OBJECT_ID AND VIEW_TYPE = :NEW.VIEW_TYPE;
  	IF V_COUNT < 1 THEN 
	        INSERT INTO VIEW_AGGREGATES_BY_TYPE (OBJECT_ID,VIEW_TYPE,VIEWS_COUNT,UNIQUE_VIEWS,LAST_VIEWED) 
        	VALUES (:NEW.OBJECT_ID,:NEW.VIEW_TYPE,1,1,SYSDATE);
   	ELSE
        	UPDATE VIEW_AGGREGATES_BY_TYPE
           	SET VIEWS_COUNT = VIEWS_COUNT + 1, UNIQUE_VIEWS = UNIQUE_VIEWS + 1, LAST_VIEWED = SYSDATE 
         	WHERE OBJECT_ID = :NEW.OBJECT_ID
           	AND VIEW_TYPE = :NEW.VIEW_TYPE;
   	END IF;
END;
/

CREATE OR REPLACE TRIGGER VIEWS_BY_TYPE_UPD_TR
AFTER UPDATE ON VIEWS_BY_TYPE
FOR EACH ROW
BEGIN
    UPDATE VIEW_AGGREGATES_BY_TYPE 
       SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE
     WHERE OBJECT_ID = :NEW.OBJECT_ID
       AND VIEW_TYPE = :NEW.VIEW_TYPE;
END;
/