drop table movielog_json;

-- Create tble and then view over movielog json (creating table to ensure it's available).  This is used for sql pattern matching
CREATE TABLE movielog_t
  (click VARCHAR2(4000))
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_HDFS
   DEFAULT DIRECTORY DEFAULT_DIR
   LOCATION ('/user/oracle/moviework/applog_json/')
  ) 
  REJECT LIMIT UNLIMITED;
  
-- create view that parses json
CREATE OR REPLACE VIEW movielog_json AS
SELECT
  CAST(m.click.custid	AS NUMBER) custid,
  CAST(m.click.movieid AS NUMBER) movieid,
  CAST(m.click.activity	AS NUMBER) activity,
  CAST(m.click.genreid AS NUMBER) genreid,
  CAST(m.click.recommended AS VARCHAR2(1)) recommended,
  CAST(m.click.time AS VARCHAR2(20)) time,
  CAST(m.click.rating AS NUMBER) rating,
  CAST(m.click.price AS NUMBER) price
FROM movielog_t m;

CREATE OR REPLACE VIEW VW_SESSION_DATA AS
SELECT
  m.click.custid as cust_id,
  m.click.movieid as movie_id,
  m.click.activity as activity_id,
  m.click.genreid AS genre_id,
  to_date(m.click.time,'YYYY-MM-DD HH24:MI:SS') AS SESS_DATE,
  SUBSTR(m.click.time,1,7) AS MONTH_ID,
  m.click.recommended AS rec_id,
  CASE m.click.recommended WHEN 'N' THEN 1 ELSE NULL END as NOT_REC_ID,
  CASE m.click.recommended WHEN 'Y' THEN 1 ELSE NULL END as YES_REC_ID,
  m.click.rating AS  rating_id,
  m.click.PRICE  price
    , case m.click.activity WHEN '1' THEN 'Y' END as act_rate
    , case m.click.activity WHEN '2' THEN 'Y' END as act_complete          
    , case m.click.activity WHEN '3' THEN 'Y' END as act_pause
    , case m.click.activity WHEN '4' THEN 'Y' END as act_start          
    , case m.click.activity WHEN '5' THEN 'Y' END as act_browse                    
    , case m.click.activity WHEN '6' THEN 'Y' END as act_list
    , case m.click.activity WHEN '7' THEN 'Y' END as act_search
    , case m.click.activity WHEN '8' THEN 'Y' END as act_login
    , case m.click.activity WHEN '9' THEN 'Y' END as act_logout
    , case m.click.activity WHEN '10' THEN 'Y' END as act_incomplete
    , case m.click.activity WHEN '11' THEN 'Y' END as act_purchase
FROM movielog_t m;

CREATE OR REPLACE VIEW  MOVIEAPP_LOG_JSON_V
AS SELECT 
   CUSTID AS CUST_ID, 
   MOVIEID AS MOVIE_ID, 
   GENREID AS GENRE_ID, 
   TO_DATE(TIME, 'YYYY-MM-DD:hh24:mi:ss') AS TIME_ID, 
   RECOMMENDED,
   ACTIVITY,
   case ACTIVITY WHEN 1 THEN 'Y' END as act_rated,
   case ACTIVITY WHEN 2 THEN 'Y' END as act_completed,          
   case ACTIVITY WHEN 3 THEN 'Y' END as act_paused,
   case ACTIVITY WHEN 4 THEN 'Y' END as act_started,  
   case ACTIVITY WHEN 5 THEN 'Y' END as act_browsed,                    
   case ACTIVITY WHEN 6 THEN 'Y' END as act_list,
   case ACTIVITY WHEN 7 THEN 'Y' END as act_search,
   case ACTIVITY WHEN 8 THEN 'Y' END as act_login,
   case ACTIVITY WHEN 9 THEN 'Y' END as act_logout,
   case ACTIVITY WHEN 10 THEN 'Y' END as act_incomplete,
   case ACTIVITY WHEN 11 THEN 'Y' END as act_purchased,
   RATING, 
   PRICE
FROM movielog_json; 

create or replace view movieapp_analytics_v
AS SELECT
  cust_id,
  session_id,
  no_of_events,
  start_time,
  end_time,
  mins_duration,
  time_id
 FROM movieapp_log_json_v
 MATCH_RECOGNIZE
  (PARTITION BY cust_id ORDER BY time_id
   MEASURES MATCH_NUMBER() AS session_id,
            COUNT(*) AS no_of_events,
            TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,
            TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time,
            TO_CHAR(FIRST(time_id),'YYYY-MM') AS time_id, 
            TO_CHAR(to_date('00:00:00','HH24:MI:SS') + (LAST(sess.time_id)-FIRST(bgn.time_id)),'hh24:mi:ss') AS mins_duration
   ONE ROW PER MATCH
   PATTERN (bgn sess+) 
   DEFINE 
         sess as time_id <= PREV(sess.time_id) + interval '2' hour
 );

BEGIN
  DBMS_REDACT.ADD_POLICY(
  object_schema => 'MOVIEDEMO',
  object_name => 'VW_SESSION_DATA',
  column_name => 'CUST_ID',
  policy_name => 'vw_sessiondata_redact',
  function_type => DBMS_REDACT.PARTIAL,
  function_parameters => 'VVVVVVV,VVVVVVV,9,1,7',
  expression => '1=1'
  );
END;
/

