drop sequence seq_tbltracking_pagehit;
/
drop sequence seq_tbltracking_visit;
/
drop table tblTracking_visit;
/
drop table tblTracking_PageHit;
/

CREATE TABLE TBLTRACKING_VISIT
(VISITID NUMBER (10,0),
VISITORID NUMBER (10,0),
SESSIONID VARCHAR2 (100),
DATESTART DATE,
DATEEND DATE,
TOTALPAGEHITS NUMBER (10,0),
USERID NUMBER (10,0),
WEBSITEID NUMBER (10,0)
) 
;

CREATE SEQUENCE SEQ_TBLTRACKING_VISIT START WITH 1
/


CREATE TABLE TBLTRACKING_PAGEHIT
(
PAGEHITID NUMBER (10,0),
VISITID NUMBER (10,0),
SECTIONID NUMBER (10,0),
CONTENTID NUMBER (10,0),
DATEPAGEHIT DATE,
SECONDSPAGEREAD NUMBER (10,0)
) 
;

CREATE SEQUENCE SEQ_TBLTRACKING_PAGEHIT START WITH 1
/

CREATE TRIGGER TR_SEQ_TBLTRACKING_PAGEHIT BEFORE INSERT ON TBLTRACKING_PAGEHIT
FOR EACH ROW  BEGIN  SELECT SEQ_TBLTRACKING_PAGEHIT.nextval INTO :new.PAGEHITID FROM dual; END;
/

CREATE or replace PROCEDURE spTracking_PageHit
	(
        	WebsiteId in number := null,
        	UserId    in number := null,
        	VisitorId in number := null,
        	VisitId   in out number,
        	SessionId in varchar2 := null,
        	SectionId in number := null,
        	ContentId in number := null
	)
is
begin

    if spTracking_PageHit.VisitId is null Then

	SELECT SEQ_TBLTRACKING_VISIT.nextval INTO spTracking_PageHit.VisitId FROM dual;

        insert into tblTracking_Visit (VisitId, VisitorId, SessionId, DateStart, TotalPageHits, UserId)
        values (spTracking_PageHit.VisitId, spTracking_PageHit.VisitorId, spTracking_PageHit.SessionId, 
		sysdate, 1, spTracking_PageHit.UserId);

    else

	update tblTracking_Visit
	set TotalPageHits = TotalPageHits + 1,
	UserId = nvl(spTracking_PageHit.UserId, UserId),
	WebsiteId = nvl(spTracking_PageHit.WebsiteId, WebsiteId)
	where VisitId = spTracking_PageHit.VisitId;

    end if;


    insert into tblTracking_PageHit (VisitId, SectionId, ContentId, DatePageHit)
    values (spTracking_PageHit.VisitId, spTracking_PageHit.SectionId, spTracking_PageHit.ContentId, sysdate);

end;
/