Data Lake (BI)

      WITH
      
      config AS (
        SELECT
          'EMPRESA' AS company_name,
          DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY) AS date_start,
          DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS date_end
      ),
      
      corporate_networks AS (
        SELECT
          ARRAY_AGG(DISTINCT LOWER(TRIM(REPLACE(network, '"', '')))) AS networks
        FROM `fnk-bi-empresa.EMPRESA.network_raw_latest`,
        UNNEST(JSON_EXTRACT_ARRAY(DATA, "$.network")) AS network
        WHERE JSON_EXTRACT_SCALAR(DATA, "$.company") = 'EMPRESA'
      ),
      
      text_clearing_rules AS (
        SELECT
          ARRAY_AGG(STRUCT(
            regex_rule AS regex,
            replace_value AS replacement
          )) AS clearing_array
        FROM `fnk-bi-empresa.EMPRESA.text_clearing_rules`
        WHERE active = TRUE
      ),
      
      app_classification_rules AS (
        SELECT
          ARRAY_AGG(STRUCT<
            company STRING,
            app_name STRING,
            regex_rule STRING,
            app_regex STRING,
            app_class STRING,
            app_class_id INT64,
            rnk INT64
          >(
            company,
            app_name_en,
            regex_rule,
            app_regex,
            app_class,
            app_class_id,
            rnk
          ) ORDER BY
            CASE WHEN company != 'GENERAL_ALL' THEN 0 ELSE 1 END,
            CASE WHEN app_class = 'web_browser__class' THEN 1 ELSE 0 END,
            rnk
          ) AS rules_array
        FROM `fnk-bi-empresa.EMPRESA.app_classificationV2`
        WHERE active = TRUE
          AND (company = 'EMPRESA' OR company = 'GENERAL_ALL')
      ),
      
      filtered_events AS (
        SELECT
          e.*,
          DATE(e.p2o_datetime_in) AS p2o_date
        FROM `fnk-bi-empresa.EMPRESA.events_EMPRESA` e,
        config c
        WHERE
          DATE(e.p2o_datetime_in) BETWEEN c.date_start AND c.date_end
          AND LOWER(e.p2o_username) != 'fhinck'
          AND e.p2o_total_seconds >= e.p2o_total_idle
          AND e.p2o_total_seconds > 0
          AND (e.p2o_source IS NULL OR UPPER(IFNULL(e.p2o_source, '')) != 'CORRUPTED')
          AND (
            e.odd_behavior IS NULL
            OR (
              e.odd_behavior NOT LIKE '%Invalid total_seconds%'
              AND e.odd_behavior NOT LIKE '%Unexpected Time Change%'
              AND e.odd_behavior NOT LIKE '%Corrupted File%'
            )
          )
      ),
      
      windowed_events AS (
        SELECT
          *,
          LAG(p2o_title_windows, 8) OVER w AS lag_title_windows,
          LEAD(p2o_title_windows, 8) OVER w AS lead_title_windows,
          COUNTIF(p2o_title_windows = 'LockingWindow')
            OVER (w ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) -
            IF(p2o_title_windows = 'LockingWindow', 1, 0) AS cnt_prc_lockwindow,
          COUNTIF(p2o_title_windows = 'LockingWindow')
            OVER (w ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING) -
            IF(p2o_title_windows = 'LockingWindow', 1, 0) AS cnt_flw_lockwindow,
          LAG(p2o_url) OVER w AS lag_p2o_url,
          LEAD(CONCAT(IFNULL(p2o_app_exe, ''), IFNULL(p2o_url, ''))) OVER w AS lead_app_url
        FROM filtered_events
        WINDOW w AS (PARTITION BY p2o_username, p2o_hostname ORDER BY p2o_datetime_in, p2o_datetime_out)
      ),
      
      deduplicated_events AS (
        SELECT * EXCEPT(row_num)
        FROM (
          SELECT
            *,
            ROW_NUMBER() OVER (
              PARTITION BY bigquery_id
              ORDER BY p2o_datetime_last_insert DESC, p2o_datetime_in DESC
            ) AS row_num
          FROM windowed_events
        )
        WHERE row_num = 1
      ),
      
      communication_detection AS (
        SELECT
          *,
          CASE
            WHEN REGEXP_CONTAINS(LOWER(IFNULL(p2o_app_exe_fullname, p2o_app_exe)),
                 r'(teams|microsoft.*teams|zoom|skype|webex|cisco.*webex|google.*meet|gotomeeting|bluejeans|jabber|cisco.*jabber|chime|amazon.*chime|hangouts|google.*hangouts|ringcentral|8x8|fuze|avaya|polycom|lifesize|starleaf|pexip|vidyo|adobe.*connect|logmein|citrix.*gotomeeting|join\.me|lync|skype.*for.*business|skype4b|sfb|s4b|zoom.*phone|3cx|aircall|dialpad|vonage|ooma|mitel|cisco.*spark|webex.*teams|ms.*teams)')
            THEN TRUE
            WHEN REGEXP_CONTAINS(LOWER(IFNULL(p2o_url_domain, '')),
                 r'(teams\.microsoft\.com|zoom\.us|skype\.com|webex\.com|meet\.google\.com|gotomeeting\.com|bluejeans\.com|hangouts\.google\.com)')
            THEN TRUE
            ELSE FALSE
          END AS is_communication_app
        FROM deduplicated_events
      ),
      
      locked_detection AS (
        SELECT
          * EXCEPT(p2o_IsWorkstationLocked),
          CASE
            WHEN LOWER(IFNULL(p2o_app_exe, '')) LIKE '%winmainf%'
              OR LOWER(IFNULL(p2o_app_exe, '')) LIKE '%netsendf%'
              OR LOWER(IFNULL(p2o_app_exe, '')) LIKE '%pcf%'
              OR LOWER(IFNULL(p2o_app_exe, '')) LIKE '%p2o%'
              OR LOWER(IFNULL(p2o_app_exe, '')) LIKE '%push2do%'
              OR LOWER(IFNULL(p2o_app_exe, '')) = '[system process]'
              OR p2o_app_exe = 'No app info'
              OR LOWER(IFNULL(p2o_app_exe, '')) LIKE 'lockapp%'
            THEN TRUE
            WHEN p2o_total_idle > 3000
              AND SAFE_DIVIDE(p2o_total_idle, p2o_total_seconds) > 0.9
              AND NOT is_communication_app
            THEN TRUE
            WHEN REGEXP_CONTAINS(LOWER(IFNULL(lag_p2o_url, '')), r'(screen saver)|(windows is being)')
              AND REGEXP_CONTAINS(LOWER(IFNULL(lead_app_url, '')), r'(screen saver)|(windows is being)|(lockapp)')
            THEN TRUE
            WHEN p2o_title_windows = 'LockingWindow'
            THEN TRUE
            WHEN IFNULL(p2o_mouse_hooks, '') = ''
              AND IFNULL(p2o_keyboard_hooks, '') = ''
              AND NOT(lag_title_windows IS NULL AND lead_title_windows IS NULL)
              AND (IFNULL(lag_title_windows, 'LockingWindow') = 'LockingWindow' OR cnt_prc_lockwindow > 0)
              AND (IFNULL(lead_title_windows, 'LockingWindow') = 'LockingWindow' OR cnt_flw_lockwindow > 0)
            THEN TRUE
            ELSE IFNULL(p2o_IsWorkstationLocked, FALSE)
          END AS p2o_IsWorkstationLocked
        FROM communication_detection
      ),
      
      employees_vacation AS (
        SELECT DISTINCT
          employee,
          LAST_VALUE(vacation_date_in IGNORE NULLS) OVER (
            PARTITION BY employee
            ORDER BY creation_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          ) AS vacation_date_in,
          LAST_VALUE(vacation_date_out IGNORE NULLS) OVER (
            PARTITION BY employee
            ORDER BY creation_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          ) AS vacation_date_out,
          LAST_VALUE(vacation_active IGNORE NULLS) OVER (
            PARTITION BY employee
            ORDER BY creation_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          ) AS vacation_active
        FROM `fnk-bi-empresa.EMPRESA.rel_hierarchy_employee_associative_EMPRESA`
        WHERE vacation_date_in IS NOT NULL
          AND vacation_active IS TRUE
      ),
      
      events_no_vacation AS (
        SELECT e.*
        FROM locked_detection e
        LEFT JOIN employees_vacation vac
          ON LOWER(e.p2o_username) = LOWER(vac.employee)
          AND e.p2o_datetime_in >= vac.vacation_date_in
          AND e.p2o_datetime_out <= IFNULL(vac.vacation_date_out, TIMESTAMP('2099-12-31'))
        WHERE vac.employee IS NULL
      ),
      
      -- ===================================================================
      -- JANELA DE JORNADA — porte do pipeline interno da Fhinck
      --
      -- A jornada e definida POR USUARIO a partir dos eventos DESBLOQUEADOS
      -- com duracao > 0 (nao por usuario+maquina). Blocos contiguos sao unidos;
      -- ha quebra de jornada quando o intervalo desde o ultimo desbloqueio
      -- passa de 4h. A data do evento passa a ser a data de ENTRADA da jornada
      -- (nao DATE(p2o_datetime_in)), e SOMENTE eventos dentro da janela
      -- [entrada, saida] permanecem — tempo de maquina ligada fora da jornada
      -- nao e atividade.
      -- ===================================================================
      unlocked_seq AS (
        SELECT
          p2o_username, p2o_datetime_in, p2o_datetime_out,
          LAST_VALUE(p2o_datetime_out IGNORE NULLS) OVER (
            PARTITION BY p2o_username
            ORDER BY p2o_datetime_in, p2o_datetime_out
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
          ) AS last_unlocked
        FROM events_no_vacation
        WHERE p2o_IsWorkstationLocked = FALSE
          AND p2o_total_seconds > 0
      ),
      
      contiguous_blocks AS (
        SELECT
          p2o_username, p2o_datetime_in, p2o_datetime_out, last_unlocked,
          SUM(IF(last_unlocked = p2o_datetime_in, 0, 1)) OVER (
            PARTITION BY p2o_username
            ORDER BY p2o_datetime_in, p2o_datetime_out
          ) AS gp_nr
        FROM unlocked_seq
      ),
      
      journey_blocks AS (
        SELECT
          p2o_username, gp_nr,
          MIN(p2o_datetime_in) AS gp_entry,
          MAX(p2o_datetime_out) AS gp_exit,
          MIN(last_unlocked) AS gp_last_unlocked
        FROM contiguous_blocks
        GROUP BY p2o_username, gp_nr
      ),
      
      journeys AS (
        SELECT
          p2o_username, gp_entry, gp_exit,
          SUM(IF(TIMESTAMP_DIFF(gp_entry, gp_last_unlocked, SECOND) > 14400, 1, 0)) OVER (
            PARTITION BY p2o_username
            ORDER BY gp_entry, gp_exit
          ) AS journey_nr
        FROM journey_blocks
      ),
      
      journey_ranges AS (
        SELECT
          p2o_username,
          MIN(gp_entry) AS journey_entry,
          MAX(gp_exit) AS journey_exit
        FROM journeys
        GROUP BY p2o_username, journey_nr
      ),
      
      journey_by_day AS (
        SELECT
          jr.p2o_username AS jw_user,
          DATE(jr.journey_entry) AS jw_date,
          MIN(jr.journey_entry) AS jw_entry,
          MAX(jr.journey_exit) AS jw_exit
        FROM journey_ranges jr, config c
        WHERE DATE(jr.journey_entry) BETWEEN c.date_start AND c.date_end
        GROUP BY jw_user, jw_date
      ),
      
      entry_exit_calc AS (
        SELECT
          e.* EXCEPT(p2o_date),
          w.jw_date AS p2o_date,
          w.jw_entry AS p2o_entry,
          w.jw_exit AS p2o_exit
        FROM events_no_vacation e
        JOIN journey_by_day w
          ON e.p2o_username = w.jw_user
          AND e.p2o_datetime_in >= w.jw_entry
          AND e.p2o_datetime_out <= w.jw_exit
      ),
      
      
      gap_detection AS (
        SELECT
          *,
          LEAD(p2o_datetime_in) OVER (
            PARTITION BY p2o_username, p2o_hostname, p2o_date
            ORDER BY p2o_datetime_in, p2o_datetime_out
          ) AS next_event_start,
          TIMESTAMP_DIFF(
            LEAD(p2o_datetime_in) OVER (
              PARTITION BY p2o_username, p2o_hostname, p2o_date
              ORDER BY p2o_datetime_in, p2o_datetime_out
            ),
            p2o_datetime_out,
            SECOND
          ) AS gap_seconds
        FROM entry_exit_calc
      ),
      
      events_extended AS (
        SELECT
          * EXCEPT(p2o_datetime_out, p2o_total_seconds, p2o_total_idle, gap_seconds, next_event_start),
          CASE
            WHEN gap_seconds > 0
              AND gap_seconds <= 14400
              AND next_event_start IS NOT NULL
            THEN next_event_start
            ELSE p2o_datetime_out
          END AS p2o_datetime_out,
          CASE
            WHEN gap_seconds > 0
              AND gap_seconds <= 14400
              AND next_event_start IS NOT NULL
            THEN p2o_total_seconds + gap_seconds
            ELSE p2o_total_seconds
          END AS p2o_total_seconds,
          CASE
            WHEN gap_seconds > 0
              AND gap_seconds <= 14400
              AND next_event_start IS NOT NULL
            THEN p2o_total_idle + (gap_seconds * SAFE_DIVIDE(p2o_total_idle, p2o_total_seconds))
            ELSE p2o_total_idle
          END AS p2o_total_idle
        FROM gap_detection
      ),
      
      classified_events AS (
        SELECT
          e.*,
          `fnk-bi-empresa.EMPRESA.clean_textV2`(
            e.p2o_title_windows,
            '{"cleanTitle": true}',
            tcr.clearing_array
          ) AS title_windows_clean,
          `fnk-bi-empresa.EMPRESA.get_app_classification`(
            acr.rules_array,
            [
              `fnk-bi-empresa.EMPRESA.clean_textV2`(e.p2o_title_windows, '{"cleanTitle": true}', tcr.clearing_array),
              LEFT(IFNULL(e.p2o_url, ''), 1500),
              IFNULL(e.p2o_url_domain, ''),
              IFNULL(e.p2o_app_exe, ''),
              IFNULL(e.p2o_app_exe_fullname, '')
            ],
            IFNULL(e.p2o_url_domain, IFNULL(e.p2o_app_exe_fullname, e.p2o_app_exe))
          ) AS app_classification_result
        FROM events_extended e
        CROSS JOIN app_classification_rules acr
        CROSS JOIN text_clearing_rules tcr
      ),
      
      events_with_classification AS (
        SELECT
          * EXCEPT(app_classification_result, p2o_url_domain_corporate),
          IFNULL(app_classification_result.app_name, IFNULL(p2o_url_domain, IFNULL(p2o_app_exe_fullname, p2o_app_exe))) AS app_name,
          IFNULL(app_classification_result.app_class, 'other') AS app_class,
          IFNULL(app_classification_result.app_class_id, 0) AS app_class_id,
          CASE
            WHEN p2o_app_exe IS NOT NULL
              AND LOWER(p2o_app_exe) NOT IN (
                'chrome.exe', 'firefox.exe', 'safari.exe', 'msedge.exe',
                'iexplore.exe', 'opera.exe', 'brave.exe', 'chromium.exe',
                'vivaldi.exe', 'waterfox.exe', 'seamonkey.exe', 'palemoon.exe',
                'maxthon.exe', 'slimbrowser.exe', 'avant.exe', 'tor.exe',
                'epic.exe', 'yandex.exe', 'edge.exe'
              )
            THEN 55
            WHEN app_classification_result.app_class_id IS NOT NULL
              AND app_classification_result.app_class_id > 0
            THEN app_classification_result.app_class_id
            WHEN p2o_url_domain IS NOT NULL
              AND LOWER(p2o_url_domain) LIKE '%fhinck%'
            THEN 1
            WHEN p2o_url_domain IS NULL
              OR TRIM(IFNULL(p2o_url_domain, '')) = ''
            THEN 55
            WHEN NOT REGEXP_CONTAINS(IFNULL(p2o_url_domain, ''),
              r'^(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z]{2,}$')
            THEN 55
            WHEN REGEXP_CONTAINS(LOWER(IFNULL(p2o_url, '')), r'\.(pdf|jpg|png|doc|docx|xls|xlsx|xml)$')
            THEN 55
            ELSE 0
          END AS p2o_url_domain_corporate
        FROM classified_events
      ),
      
      network_extracted AS (
        SELECT
          e.*,
          cn.networks AS corporate_networks_list,
          (
            SELECT
              LOWER(TRIM(SPLIT(network_part, '|')[SAFE_OFFSET(0)]))
            FROM UNNEST(SPLIT(IFNULL(e.p2o_network, ''), ';')) AS network_part
            WHERE ARRAY_LENGTH(SPLIT(network_part, '|')) >= 2
              AND LOWER(TRIM(SPLIT(network_part, '|')[SAFE_OFFSET(1)])) = 'true'
              AND LOWER(TRIM(SPLIT(network_part, '|')[SAFE_OFFSET(0)])) NOT IN (
                'identificando', 'rede nao identificada', 'rede não identificada', '', 'null'
              )
            LIMIT 1
          ) AS detected_network
        FROM events_with_classification e
        CROSS JOIN corporate_networks cn
      ),
      
      network_status AS (
        SELECT
          ne.*,
          LAG(detected_network) OVER (
            PARTITION BY p2o_username, p2o_hostname, p2o_date
            ORDER BY p2o_datetime_in
          ) AS prev_network,
          CASE
            WHEN detected_network IS NOT NULL
              AND detected_network IN UNNEST(corporate_networks_list)
            THEN TRUE
            ELSE FALSE
          END AS is_current_corporate,
          CASE
            WHEN LAG(detected_network) OVER (
                  PARTITION BY p2o_username, p2o_hostname, p2o_date
                  ORDER BY p2o_datetime_in
                 ) IS NOT NULL
              AND LAG(detected_network) OVER (
                  PARTITION BY p2o_username, p2o_hostname, p2o_date
                  ORDER BY p2o_datetime_in
                 ) IN UNNEST(corporate_networks_list)
            THEN TRUE
            ELSE FALSE
          END AS is_prev_corporate
        FROM network_extracted ne
      ),
      
      initial_status AS (
        SELECT
          *,
          CASE
            WHEN is_current_corporate = TRUE
              AND is_prev_corporate = FALSE
              AND prev_network IS NOT NULL
            THEN 'VPN'
            WHEN is_current_corporate = TRUE
            THEN 'Corp'
            ELSE 'Outro'
          END AS initial_network_status
        FROM network_status
      ),
      
      vpn_propagation AS (
        SELECT
          *,
          MAX(CASE WHEN initial_network_status = 'VPN' THEN p2o_datetime_in END) OVER (
            PARTITION BY p2o_username, p2o_hostname, p2o_date
            ORDER BY p2o_datetime_in
            ROWS UNBOUNDED PRECEDING
          ) AS vpn_start_time
        FROM initial_status
      ),
      
      network_final AS (
        SELECT
          *,
          CASE
            WHEN vpn_start_time IS NOT NULL
              AND p2o_datetime_in >= vpn_start_time
              AND is_current_corporate = TRUE
            THEN 'VPN'
            ELSE initial_network_status
          END AS final_network_status
        FROM vpn_propagation
      ),
      
      work_mode_event AS (
        SELECT
          *,
          CASE
            WHEN final_network_status IN ('VPN', 'Outro') THEN 'Home-Office'
            WHEN final_network_status = 'Corp' THEN 'Empresa'
            ELSE 'Indeterminado'
          END AS place_event
        FROM network_final
      ),
      
      events_with_block_count AS (
        SELECT
          *,
          CAST(CEILING(p2o_total_seconds / 600.0) AS INT64) AS num_blocks,
          p2o_total_seconds AS original_total_seconds,
          p2o_total_idle AS original_total_idle
        FROM work_mode_event
      ),
      
      split_blocks AS (
        SELECT
          e.* EXCEPT(p2o_datetime_in, p2o_datetime_out, p2o_total_seconds, p2o_total_idle),
          TIMESTAMP_ADD(e.p2o_datetime_in, INTERVAL (600 * block_idx) SECOND) AS p2o_datetime_in,
          LEAST(
            e.p2o_datetime_out,
            TIMESTAMP_ADD(e.p2o_datetime_in, INTERVAL (600 * (block_idx + 1)) SECOND)
          ) AS p2o_datetime_out,
          block_idx,
          TIMESTAMP_DIFF(
            LEAST(
              e.p2o_datetime_out,
              TIMESTAMP_ADD(e.p2o_datetime_in, INTERVAL (600 * (block_idx + 1)) SECOND)
            ),
            TIMESTAMP_ADD(e.p2o_datetime_in, INTERVAL (600 * block_idx) SECOND),
            SECOND
          ) AS p2o_total_seconds
        FROM events_with_block_count e,
        UNNEST(GENERATE_ARRAY(0, GREATEST(0, num_blocks - 1))) AS block_idx
        WHERE TIMESTAMP_ADD(e.p2o_datetime_in, INTERVAL (600 * block_idx) SECOND) < e.p2o_datetime_out
      ),
      
      events_split AS (
        SELECT
          * EXCEPT(original_total_seconds, original_total_idle, num_blocks, block_idx),
          LEAST(
            CAST(p2o_total_seconds AS FLOAT64),
            GREATEST(
              0.0,
              original_total_idle * SAFE_DIVIDE(CAST(p2o_total_seconds AS FLOAT64), CAST(original_total_seconds AS FLOAT64))
            )
          ) AS p2o_total_idle,
          CASE
            WHEN num_blocks > 1
            THEN CONCAT(bigquery_id, '-', CAST(block_idx AS STRING))
            ELSE bigquery_id
          END AS new_bigquery_id
        FROM split_blocks
      ),
      
      place_day_calc AS (
        SELECT
          e.*,
          SUM(CASE WHEN place_event = 'Home-Office' AND p2o_IsWorkstationLocked = FALSE
                   THEN p2o_total_seconds ELSE 0 END) OVER (
            PARTITION BY p2o_username, p2o_date
          ) AS total_remote_seconds,
          SUM(CASE WHEN place_event = 'Empresa' AND p2o_IsWorkstationLocked = FALSE
                   THEN p2o_total_seconds ELSE 0 END) OVER (
            PARTITION BY p2o_username, p2o_date
          ) AS total_presential_seconds,
          SUM(CASE WHEN p2o_IsWorkstationLocked = FALSE
                   THEN p2o_total_seconds ELSE 0 END) OVER (
            PARTITION BY p2o_username, p2o_date
          ) AS total_active_seconds
        FROM events_split e
      ),
      
      events_with_place_day AS (
        SELECT
          *,
          CASE
            WHEN SAFE_DIVIDE(total_remote_seconds, total_active_seconds) > 0.10
              AND SAFE_DIVIDE(total_presential_seconds, total_active_seconds) > 0.10
            THEN 'Hibrida'
            WHEN total_remote_seconds > total_presential_seconds
            THEN 'Home-Office'
            ELSE 'Empresa'
          END AS place_day
        FROM place_day_calc
      ),
      
      -- ===================================================================
      -- HIERARQUIA DO COLABORADOR
      --
      -- Duas fontes, com semanticas DIFERENTES — nao sao intercambiaveis:
      --
      --   dim_employee_daily  -> nomes VIGENTES NA DATA (historico correto).
      --       Uma linha por colaborador por dia. E daqui que saem employee_alias,
      --       role_name, region_name, department_name e os niveis Department_1..10.
      --
      --   rel_hierarchy_employee_associative -> CODIGOS e PATHS da configuracao
      --       ATUAL (o cadastro no data lake e um snapshot por login, sem
      --       versionamento). Portanto fk_department_code, fk_role_code,
      --       fk_region_code, fk_process_code, department_path e process_path
      --       refletem o cadastro de HOJE, nao o da data do evento. Para analise
      --       historica por departamento, use os campos de nome (dim), nao os codigos.
      -- ===================================================================
      employee_daily AS (
        SELECT
          employee,
          p2o_date,
          employee_alias,
          role_name,
          Region AS region_name,
          department_name,
          Department_1, Department_2, Department_3, Department_4, Department_5,
          Department_6, Department_7, Department_8, Department_9, Department_10
        FROM `fnk-bi-empresa.EMPRESA.dim_employee_daily_EMPRESA`
      ),
      
      employee_registry AS (
        SELECT * EXCEPT(rn)
        FROM (
          SELECT
            LOWER(employee) AS employee_key,
            fk_department_code,
            fk_process_code,
            fk_role_code,
            fk_region_code,
            department_path,
            process_path,
            active AS cadastro_ativo,
            ROW_NUMBER() OVER (
              PARTITION BY LOWER(employee)
              ORDER BY search_date DESC, creation_date DESC
            ) AS rn
          FROM `fnk-bi-empresa.EMPRESA.rel_hierarchy_employee_associative_EMPRESA`
        )
        WHERE rn = 1
      )
      
      SELECT
        new_bigquery_id AS event_id,
        p2o_username,
        p2o_hostname,
        -- hierarquia vigente na data (dim_employee_daily)
        d.employee_alias,
        d.department_name,
        d.role_name,
        d.region_name,
        d.Department_1, d.Department_2, d.Department_3, d.Department_4, d.Department_5,
        d.Department_6, d.Department_7, d.Department_8, d.Department_9, d.Department_10,
        -- codigos e paths do cadastro atual (rel_hierarchy_employee_associative)
        r.fk_department_code,
        r.fk_role_code,
        r.fk_region_code,
        r.fk_process_code,
        r.department_path,
        r.process_path,
        e.p2o_date,
        FORMAT_DATE('%Y-%m', e.p2o_date) AS mes,
        CASE EXTRACT(DAYOFWEEK FROM e.p2o_date)
          WHEN 1 THEN 'Domingo'
          WHEN 2 THEN 'Segunda-feira'
          WHEN 3 THEN 'Terça-feira'
          WHEN 4 THEN 'Quarta-feira'
          WHEN 5 THEN 'Quinta-feira'
          WHEN 6 THEN 'Sexta-feira'
          WHEN 7 THEN 'Sábado'
        END AS dia_semana,
        p2o_datetime_in,
        p2o_datetime_out,
        p2o_entry,
        p2o_exit,
        app_name,
        app_class,
        app_class_id,
        title_windows_clean,
        p2o_url_domain,
        p2o_app_exe,
        p2o_app_exe_fullname,
        p2o_IsWorkstationLocked,
        p2o_url_domain_corporate,
        place_day,
        CAST(p2o_total_seconds AS INT64) AS p2o_total_seconds,
        CAST(p2o_total_idle AS INT64) AS p2o_total_idle,
        CASE
          WHEN REGEXP_CONTAINS(
            IF(p2o_IsWorkstationLocked, 'OutPC',
               IF(p2o_url_domain_corporate < 55, p2o_url_domain,
                  IFNULL(NULLIF(p2o_app_exe_fullname, ''), LOWER(p2o_app_exe)))),
            r'(?i)excel|powerpoint|word|docs.google|analytics.google')
          THEN 'Analítico'
          WHEN IF(p2o_IsWorkstationLocked, 'OutPC',
                 IF(p2o_url_domain_corporate < 55, p2o_url_domain,
                    IFNULL(NULLIF(p2o_app_exe_fullname, ''), LOWER(p2o_app_exe)))) = 'OutPC'
          THEN 'Relacionamento'
          WHEN REGEXP_CONTAINS(
            IF(p2o_IsWorkstationLocked, 'OutPC',
               IF(p2o_url_domain_corporate < 55, p2o_url_domain,
                  IFNULL(NULLIF(p2o_app_exe_fullname, ''), LOWER(p2o_app_exe)))),
            r'(?i)whatsapp|mail|outlook|chat|hangout|teams|jabber|skype|zoom|meet|telegram|whereby|discord|bluejeans|webex')
          THEN 'Comunicação'
          WHEN REGEXP_CONTAINS(
            IF(p2o_IsWorkstationLocked, 'OutPC',
               IF(p2o_url_domain_corporate < 55, p2o_url_domain,
                  IFNULL(NULLIF(p2o_app_exe_fullname, ''), LOWER(p2o_app_exe)))),
            r'(?i)rstudio|datastudio|qlik|tableau|embarcadero|power bi|looker|visual studio|jupyter|colab.research|notepad\+\+|atom|github|comandos|command|sourcetree|powershell|developers|cloud.google|aws|localhost')
            OR REGEXP_CONTAINS(IFNULL(p2o_title_windows, ''), r'(?i)power bi|jupyter')
          THEN 'Data e Desenvolvimento'
          ELSE 'Transacional'
        END AS profile_name_event
      
      FROM events_with_place_day e
      LEFT JOIN employee_daily d
        ON LOWER(e.p2o_username) = d.employee
        AND e.p2o_date = d.p2o_date
      LEFT JOIN employee_registry r
        ON LOWER(e.p2o_username) = r.employee_key