events_query = '''
WITH saveSuccess_sessions as (
    SELECT DISTINCT
      event.editing_session_id AS ss_session_id,
      event.user_id AS user_id,
      event.editor_interface AS editor_interface,
      event.platform AS platform,
      event.integration AS integration,
      useragent.device_family as user_agent,
      wiki AS ss_wiki
FROM event.editattemptstep
WHERE
    YEAR = 2024
    AND MONTH >= 01
    AND wiki = 'metawiki'
    AND event.page_title LIKE 'Event:%'
    AND event.action = 'saveSuccess' 
    AND useragent.is_bot = false
   AND NOT event.is_oversample
),

js_wt_sessions AS(
  SELECT
    event.editingSessionid AS wt_js_session_id,
    wiki AS js_wt_wiki,
    event.action AS wikitext_js_edit
FROM event.visualeditorfeatureuse 
WHERE
    YEAR = 2024
    AND MONTH >= 01
    AND wiki = 'metawiki'
    AND event.action = 'source-has-js'
    AND event.feature = 'mwSave'
    AND event.editor_interface = 'wikitext'
    AND useragent.is_bot = false
)


SELECT 
  wiki,
  user_agent, 
SUM(CAST(user_id = 0 AND editor_interface = 'wikitext' AND integration = 'page' AND platform = 'desktop' AND wikitext_js_edit is NULL AS INT)) AS anon_no_js_edit,
  SUM(CAST(user_id != 0 AND editor_interface = 'wikitext' AND integration = 'page' AND platform = 'desktop' AND wikitext_js_edit is NULL AS INT)) AS user_no_js_edit,
  SUM(CAST(user_id = 0 AS INT)) AS anon_all_edits,
  SUM(CAST(user_id != 0 AS INT)) AS user_all_edits 
FROM (
  SELECT 
    user_id AS user_id,
    ss_wiki AS wiki,
    editor_interface AS editor_interface,
    integration AS integration,
    platform AS platform,
    user_agent AS user_agent,
    ss_session_id AS ss_session_id,
    wikitext_js_edit
  FROM saveSuccess_sessions ss  
  LEFT JOIN 
    js_wt_sessions ON ss.ss_session_id = js_wt_sessions.wt_js_session_id
    AND ss.ss_wiki = js_wt_sessions.js_wt_wiki
  GROUP BY 
    ss.user_id, 
    ss.ss_wiki,
    ss.editor_interface,
    ss.integration,
    ss.platform,
    ss.user_agent,
    ss.ss_session_id, 
    js_wt_sessions.wikitext_js_edit
    ) edit_sessions
GROUP BY
    wiki,
    user_agent
'''

df['user_all_edits'].sum()

SELECT COUNT(DISTINCT event.page_title) AS unique_event_count
FROM event.editattemptstep
WHERE
    YEAR = 2024
    AND MONTH >= 01
    AND wiki = 'metawiki'
    AND event.page_title LIKE 'Event:%'
    AND event.action = 'saveSuccess' 
    AND useragent.is_bot = false
   AND NOT event.is_oversample

events_query_count_mariadb = '''
    SELECT 
       COUNT(DISTINCT campaign_events.event_id) AS unique_event_count
    FROM campaign_events 
    WHERE 
        campaign_events.event_created_at BETWEEN "20240101000000" and "20240508000000" OR
        campaign_events.event_last_edit BETWEEN "20240101000000" and "20240508000000"
    '''

campaign_events_confirm_count = mariadb.run(events_query_count_mariadb , 'wikishared')

events_query_review = '''
WITH saveSuccess_sessions as (
    SELECT DISTINCT
      event.editing_session_id AS ss_session_id,
      event.user_id AS user_id,
      event.editor_interface AS editor_interface,
      event.platform AS platform,
      event.integration AS integration,
      useragent.device_family as user_agent,
      wiki AS ss_wiki
FROM event.editattemptstep
WHERE
    YEAR = 2024
    AND MONTH >= 01
    AND wiki = 'metawiki'
    --AND event.page_title LIKE 'Event:%'
    AND event.page_id IN {events_list}
    AND event.action = 'saveSuccess' 
    AND useragent.is_bot = false
   AND NOT event.is_oversample
),

js_wt_sessions AS(
  SELECT
    event.editingSessionid AS wt_js_session_id,
    wiki AS js_wt_wiki,
    event.action AS wikitext_js_edit
FROM event.visualeditorfeatureuse 
WHERE
    YEAR = 2024
    AND MONTH >= 01
    AND wiki = 'metawiki'
    AND event.action = 'source-has-js'
    AND event.feature = 'mwSave'
    AND event.editor_interface = 'wikitext'
    AND useragent.is_bot = false
)


SELECT 
  wiki,
  user_agent, 
SUM(CAST(user_id = 0 AND editor_interface = 'wikitext' AND integration = 'page' AND platform = 'desktop' AND wikitext_js_edit is NULL AS INT)) AS anon_no_js_edit,
  SUM(CAST(user_id != 0 AND editor_interface = 'wikitext' AND integration = 'page' AND platform = 'desktop' AND wikitext_js_edit is NULL AS INT)) AS user_no_js_edit,
  SUM(CAST(user_id = 0 AS INT)) AS anon_all_edits,
  SUM(CAST(user_id != 0 AS INT)) AS user_all_edits 
FROM (
  SELECT 
    user_id AS user_id,
    ss_wiki AS wiki,
    editor_interface AS editor_interface,
    integration AS integration,
    platform AS platform,
    user_agent AS user_agent,
    ss_session_id AS ss_session_id,
    wikitext_js_edit
  FROM saveSuccess_sessions ss  
  LEFT JOIN 
    js_wt_sessions ON ss.ss_session_id = js_wt_sessions.wt_js_session_id
    AND ss.ss_wiki = js_wt_sessions.js_wt_wiki
  GROUP BY 
    ss.user_id, 
    ss.ss_wiki,
    ss.editor_interface,
    ss.integration,
    ss.platform,
    ss.user_agent,
    ss.ss_session_id, 
    js_wt_sessions.wikitext_js_edit
    ) edit_sessions
GROUP BY
    wiki,
    user_agent
'''
df_review = spark.run(events_query.format(events_list = events_list))