-- =============================================================================
-- GeoLynx Usage Analysis Queries
-- =============================================================================
-- Run individual queries as needed — nothing here modifies data.
-- User ID = 1 (owner/admin) is excluded from all queries.
--
-- Data sources:
--   users.users_login_log          — one row per login event
--   projects.project_access_log    — one row per project open
--   accounts.account_access_log    — one row per account open
--   stocklists.stocklist_access_log — one row per stocklist open
--
-- Note: users_login_log.session_id and last_active_datetime were added
-- recently — historical rows will have NULLs in those columns.
-- =============================================================================


-- =============================================================================
-- SECTION 1: USER OVERVIEW
-- =============================================================================

-- 1.1  Monthly login breakdown — total logins and average logins per active user
SELECT
    to_char(date_trunc('month', l.timestamp), 'YYYY-MM')          AS month,
    COUNT(*)                                                       AS total_logins,
    COUNT(DISTINCT l.user_id)                                      AS active_users,
    ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT l.user_id), 0), 1)
                                                                   AS avg_logins_per_user
FROM users.users_login_log l
WHERE l.user_id NOT IN (1,2,3,4)
GROUP BY date_trunc('month', l.timestamp)
ORDER BY month;


-- 1.2  Users who have not logged in for more than 30 days (or never)
/*
    u.id,
    u.username,
    u.email,
    u.is_active,
    u.date_last_login::date                                          AS last_login,
    EXTRACT(DAY FROM now() - u.date_last_login)::int                 AS days_since_login
FROM users.users u
WHERE u.id not in (1,2,3,4)
  AND (u.date_last_login IS NULL
       OR u.date_last_login < now() - interval '30 days')
ORDER BY u.date_last_login ASC NULLS FIRST;*/


-- 1.3  Currently active sessions (last page load within 15 minutes)
/*SELECT
    u.id,
    u.username,
    u.date_last_active,
    EXTRACT(EPOCH FROM now() - u.date_last_active)::int / 60        AS minutes_ago
FROM users.users u
WHERE u.id not in (1,2,3,4)
  AND u.date_last_active > now() - interval '15 minutes'
ORDER BY u.date_last_active DESC;*/


-- =============================================================================
-- SECTION 2: LOGIN PATTERNS
-- =============================================================================

-- 2.1  Logins per month (last 12 months)
/*SELECT
    to_char(date_trunc('month', l.timestamp), 'YYYY-MM')           AS month,
    COUNT(*)                                                        AS total_logins,
    COUNT(DISTINCT l.user_id)                                       AS unique_users
FROM users.users_login_log l
WHERE l.user_id not in (1,2,3,4)
  AND l.timestamp >= date_trunc('month', now()) - interval '11 months'
GROUP BY date_trunc('month', l.timestamp)
ORDER BY month;
*/

-- 2.2  Monthly active users (MAU)
/*SELECT
    to_char(date_trunc('month', l.timestamp), 'YYYY-MM')           AS month,
    COUNT(DISTINCT l.user_id)                                       AS monthly_active_users
FROM users.users_login_log l
WHERE l.user_id not in (1,2,3,4)
GROUP BY date_trunc('month', l.timestamp)
ORDER BY month;*/


-- 2.3  Logins by day of week — are users active at weekends?
/*SELECT
    EXTRACT(DOW FROM l.timestamp)::int                             AS day_num,
    to_char(l.timestamp, 'Day')                                    AS day_name,
    COUNT(*)                                                       AS total_logins
FROM users.users_login_log l
WHERE l.user_id not in (1,2,3,4)
GROUP BY day_num, day_name
ORDER BY day_num;*/


-- 2.4  Logins by hour of day — when is peak usage?
/*SELECT
    EXTRACT(HOUR FROM l.timestamp)::int                            AS hour_of_day,
    COUNT(*)                                                       AS total_logins
FROM users.users_login_log l
WHERE l.user_id not in (1,2,3,4)
GROUP BY hour_of_day
ORDER BY hour_of_day;*/


-- 2.5  Device type breakdown (desktop / mobile / tablet) from user-agent
/*SELECT
    CASE
        WHEN l.user_agent ILIKE '%mobile%'
          OR l.user_agent ILIKE '%android%'  THEN 'Mobile'
        WHEN l.user_agent ILIKE '%ipad%'
          OR l.user_agent ILIKE '%tablet%'   THEN 'Tablet'
        ELSE 'Desktop'
    END                                                            AS device_type,
    COUNT(*)                                                       AS logins,
    COUNT(DISTINCT l.user_id)                                      AS unique_users
FROM users.users_login_log l
WHERE l.user_id not in (1,2,3,4)
GROUP BY device_type
ORDER BY logins DESC;*/


-- 2.6  Login frequency per user — longest streak of consecutive days
/*WITH daily_logins AS (
    SELECT DISTINCT
        user_id,
        timestamp::date AS login_date
    FROM users.users_login_log
    WHERE user_id not in (1,2,3,4)
),
grouped AS (
    SELECT
        user_id,
        login_date,
        login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::int AS grp
    FROM daily_logins
),
streaks AS (
    SELECT user_id, COUNT(*) AS streak_days
    FROM grouped
    GROUP BY user_id, grp
)
SELECT
    u.username,
    MAX(s.streak_days)                                             AS longest_streak_days,
    COUNT(DISTINCT d.login_date)                                   AS total_days_logged_in
FROM users.users u
INNER JOIN streaks s       ON s.user_id = u.id
INNER JOIN daily_logins d  ON d.user_id = u.id
WHERE u.id not in (1,2,3,4)
GROUP BY u.username
ORDER BY longest_streak_days DESC;*/


-- =============================================================================
-- SECTION 3: PROJECT ACCESS
-- =============================================================================

-- 3.1  Most accessed projects (all time)
SELECT
    p.project_id,
    p.project_name,
    p.project_status_desc,
    p.pm_username                                                  AS project_manager,
    COUNT(al.id)                                                   AS total_accesses,
    COUNT(DISTINCT al.user_id)                                     AS unique_users,
    MIN(al.access_datetime)::date                                  AS first_accessed,
    MAX(al.access_datetime)::date                                  AS last_accessed
FROM projects.project_access_log al
INNER JOIN projects.vw_projects_list p ON p.project_id = al.project_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY p.project_id, p.project_name, p.project_status_desc, p.pm_username
ORDER BY total_accesses DESC
--LIMIT 25
;


-- 3.2  Project access per user
SELECT
    u.username,
    COUNT(DISTINCT al.project_id)                                  AS distinct_projects,
    COUNT(al.id)                                                   AS total_accesses,
    MIN(al.access_datetime)::date                                  AS first_access,
    MAX(al.access_datetime)::date                                  AS last_access
FROM projects.project_access_log al
INNER JOIN users.users u ON u.id = al.user_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY u.username
ORDER BY total_accesses DESC;


-- 3.3  Project accesses per month (last 12 months)
SELECT
    to_char(date_trunc('month', al.access_datetime), 'YYYY-MM')    AS month,
    COUNT(*)                                                        AS total_accesses,
    COUNT(DISTINCT al.user_id)                                      AS unique_users,
    COUNT(DISTINCT al.project_id)                                   AS unique_projects
FROM projects.project_access_log al
WHERE al.user_id not in (1,2,3,4)
  AND al.access_datetime >= date_trunc('month', now()) - interval '11 months'
GROUP BY date_trunc('month', al.access_datetime)
ORDER BY month;


-- 3.4  Projects that have never been accessed
/*SELECT
    p.project_id,
    p.project_name,
    p.project_status_desc,
    p.created_datetime::date                                       AS created_date
FROM projects.vw_projects_list p
LEFT JOIN projects.project_access_log al
    ON al.project_id = p.project_id
   AND al.user_id not in (1,2,3,4)
WHERE al.id IS NULL
ORDER BY p.created_datetime DESC;*/


-- 3.5  Read vs write access breakdown (last 90 days)
/*SELECT
    p.project_name,
    al.access_type,
    COUNT(*)                                                       AS access_count
FROM projects.project_access_log al
INNER JOIN projects.projects p ON p.project_id = al.project_id
WHERE al.user_id not in (1,2,3,4)
  AND al.access_datetime >= now() - interval '90 days'
GROUP BY p.project_name, al.access_type
ORDER BY p.project_name, al.access_type;*/


-- =============================================================================
-- SECTION 4: ACCOUNT ACCESS
-- =============================================================================

-- 4.1  Most accessed accounts (all time)
/*SELECT
    a.account_id,
    a.account_name,
    a.account_status_desc,
    COUNT(al.id)                                                   AS total_accesses,
    COUNT(DISTINCT al.user_id)                                     AS unique_users,
    MIN(al.access_datetime)::date                                  AS first_accessed,
    MAX(al.access_datetime)::date                                  AS last_accessed
FROM accounts.account_access_log al
INNER JOIN accounts.vw_accounts_list a ON a.account_id = al.account_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY a.account_id, a.account_name, a.account_status_desc
ORDER BY total_accesses DESC
LIMIT 25;*/


-- 4.2  Account access per user
/*SELECT
    u.username,
    COUNT(DISTINCT al.account_id)                                  AS distinct_accounts,
    COUNT(al.id)                                                   AS total_accesses,
    MAX(al.access_datetime)::date                                  AS last_access
FROM accounts.account_access_log al
INNER JOIN users.users u ON u.id = al.user_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY u.username
ORDER BY total_accesses DESC;*/


-- 4.3  Accounts that have never been accessed
/*SELECT
    a.account_id,
    a.account_name,
    a.account_status_desc,
    a.created_datetime::date                                       AS created_date
FROM accounts.vw_accounts_list a
LEFT JOIN accounts.account_access_log al
    ON al.account_id = a.account_id
   AND al.user_id not in (1,2,3,4)
WHERE al.id IS NULL
ORDER BY a.created_datetime DESC;*/


-- =============================================================================
-- SECTION 5: STOCKLIST ACCESS
-- =============================================================================

-- 5.1  Most accessed stocklists (all time)
/*SELECT
    s.stocklist_id,
    s.stocklist_name,
    COUNT(al.id)                                                   AS total_accesses,
    COUNT(DISTINCT al.user_id)                                     AS unique_users,
    MIN(al.access_datetime)::date                                  AS first_accessed,
    MAX(al.access_datetime)::date                                  AS last_accessed
FROM stocklists.stocklist_access_log al
INNER JOIN stocklists.stocklists s ON s.stocklist_id = al.stocklist_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY s.stocklist_id, s.stocklist_name
ORDER BY total_accesses DESC
LIMIT 25;*/


-- 5.2  Stocklist access per user
/*SELECT
    u.username,
    COUNT(DISTINCT al.stocklist_id)                                AS distinct_stocklists,
    COUNT(al.id)                                                   AS total_accesses,
    MAX(al.access_datetime)::date                                  AS last_access
FROM stocklists.stocklist_access_log al
INNER JOIN users.users u ON u.id = al.user_id
WHERE al.user_id not in (1,2,3,4)
GROUP BY u.username
ORDER BY total_accesses DESC;*/


-- 5.3  Stocklists that have never been accessed
/*SELECT
    s.stocklist_id,
    s.stocklist_name,
    s.created_datetime::date                                       AS created_date
FROM stocklists.stocklists s
LEFT JOIN stocklists.stocklist_access_log al
    ON al.stocklist_id = s.stocklist_id
   AND al.user_id not in (1,2,3,4)
WHERE al.id IS NULL
ORDER BY s.created_datetime DESC;*/


-- =============================================================================
-- SECTION 6: COMBINED ENGAGEMENT
-- =============================================================================

-- 6.1  Lifetime engagement per user — logins + all item types in one view
/*SELECT
    u.id,
    u.username,
    u.is_active,
    COALESCE(lg.logins,             0)                             AS logins,
    COALESCE(pa.project_accesses,   0)                             AS project_accesses,
    COALESCE(aa.account_accesses,   0)                             AS account_accesses,
    COALESCE(sa.stocklist_accesses, 0)                             AS stocklist_accesses,
    COALESCE(pa.project_accesses,   0)
        + COALESCE(aa.account_accesses,   0)
        + COALESCE(sa.stocklist_accesses, 0)                       AS total_item_accesses,
    u.date_last_login::date                                        AS last_login,
    u.date_last_active::date                                       AS last_active
FROM users.users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS logins
    FROM users.users_login_log WHERE user_id not in (1,2,3,4)
    GROUP BY user_id
) lg ON lg.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS project_accesses
    FROM projects.project_access_log WHERE user_id not in (1,2,3,4)
    GROUP BY user_id
) pa ON pa.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS account_accesses
    FROM accounts.account_access_log WHERE user_id not in (1,2,3,4)
    GROUP BY user_id
) aa ON aa.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS stocklist_accesses
    FROM stocklists.stocklist_access_log WHERE user_id not in (1,2,3,4)
    GROUP BY user_id
) sa ON sa.user_id = u.id
WHERE u.id not in (1,2,3,4)
ORDER BY total_item_accesses DESC;
*/

-- 6.2  Daily active users and total events across all item types (last 90 days)
--      Useful for spotting quiet periods, weekends, and growth trends
SELECT
    activity_date,
    COUNT(DISTINCT user_id)                                        AS active_users,
    SUM(events)                                                    AS total_events
FROM (
    SELECT access_datetime::date AS activity_date, user_id, COUNT(*) AS events
    FROM projects.project_access_log
    WHERE user_id not in (1,2,3,4) AND access_datetime >= now() - interval '90 days'
    GROUP BY activity_date, user_id

    UNION ALL

    SELECT access_datetime::date, user_id, COUNT(*)
    FROM accounts.account_access_log
    WHERE user_id not in (1,2,3,4) AND access_datetime >= now() - interval '90 days'
    GROUP BY access_datetime::date, user_id

    UNION ALL

    SELECT access_datetime::date, user_id, COUNT(*)
    FROM stocklists.stocklist_access_log
    WHERE user_id not in (1,2,3,4) AND access_datetime >= now() - interval '90 days'
    GROUP BY access_datetime::date, user_id

    UNION ALL

    SELECT access_datetime::date, user_id, COUNT(*)
    FROM prospector.opportunity_access_log
    WHERE user_id not in (1,2,3,4) AND access_datetime >= now() - interval '90 days'
    GROUP BY access_datetime::date, user_id
) combined
GROUP BY activity_date
ORDER BY activity_date;


-- 6.3  Per-user activity in the last 30 days
--      Quick view of who is currently active
/*SELECT
    u.username,
    COUNT(DISTINCT pa.project_id)                                  AS projects_opened,
    COUNT(DISTINCT aa.account_id)                                  AS accounts_opened,
    COUNT(DISTINCT sa.stocklist_id)                                AS stocklists_opened,
    GREATEST(
        MAX(pa.access_datetime),
        MAX(aa.access_datetime),
        MAX(sa.access_datetime),
        MAX(oa.access_datetime)
    )::date                                                        AS last_item_access
FROM users.users u
LEFT JOIN projects.project_access_log pa
    ON pa.user_id = u.id
   AND pa.user_id not in (1,2,3,4)
   AND pa.access_datetime >= now() - interval '30 days'
LEFT JOIN accounts.account_access_log aa
    ON aa.user_id = u.id
   AND aa.user_id not in (1,2,3,4)
   AND aa.access_datetime >= now() - interval '30 days'
LEFT JOIN stocklists.stocklist_access_log sa
    ON sa.user_id = u.id
   AND sa.user_id not in (1,2,3,4)
   AND sa.access_datetime >= now() - interval '30 days'
LEFT JOIN prospector.opportunity_access_log oa
    ON oa.user_id = u.id
   AND oa.user_id not in (1,2,3,4)
   AND oa.access_datetime >= now() - interval '30 days'
WHERE u.id not in (1,2,3,4)
  AND (pa.id IS NOT NULL OR aa.id IS NOT NULL OR sa.id IS NOT NULL)
GROUP BY u.username
ORDER BY last_item_access DESC;
*/

-- 6.4  Hour-of-day heatmap across all item types
--      Shows when users are most active during the working day
/*SELECT
    EXTRACT(HOUR FROM access_datetime)::int                        AS hour_of_day,
    COUNT(*)                                                       AS total_events
FROM (
    SELECT access_datetime FROM projects.project_access_log    WHERE user_id not in (1,2,3,4)
    UNION ALL
    SELECT access_datetime FROM accounts.account_access_log    WHERE user_id not in (1,2,3,4)
    UNION ALL
    SELECT access_datetime FROM stocklists.stocklist_access_log WHERE user_id not in (1,2,3,4)
) all_events
GROUP BY hour_of_day
ORDER BY hour_of_day;
*/

-- 6.5  Monthly event totals per item type — shows which module drives most usage
SELECT
    to_char(date_trunc('month', access_datetime), 'YYYY-MM')       AS month,
    SUM(CASE WHEN item_type = 'project'   THEN 1 ELSE 0 END)       AS project_accesses,
    SUM(CASE WHEN item_type = 'account'   THEN 1 ELSE 0 END)       AS account_accesses,
    SUM(CASE WHEN item_type = 'stocklist' THEN 1 ELSE 0 END)       AS stocklist_accesses,
    SUM(CASE WHEN item_type = 'opportunity' THEN 1 ELSE 0 END)       AS opporunity_accesses,
    COUNT(*)                                                        AS total_events
FROM (
    SELECT access_datetime, 'project'   AS item_type FROM projects.project_access_log    WHERE user_id not in (1,2,3,4)
    UNION ALL
    SELECT access_datetime, 'account'              FROM accounts.account_access_log    WHERE user_id not in (1,2,3,4)
    UNION ALL
    SELECT access_datetime, 'stocklist'            FROM stocklists.stocklist_access_log WHERE user_id not in (1,2,3,4)
    UNION ALL
    SELECT access_datetime, 'opportunity'            FROM prospector.opportunity_access_log WHERE user_id not in (1,2,3,4)
) combined
WHERE access_datetime >= date_trunc('month', now()) - interval '11 months'
GROUP BY date_trunc('month', access_datetime)
ORDER BY month;


-- =============================================================================
-- SECTION 7: SESSION ANALYSIS (INFERRED)
-- Session duration is inferred from access logs:
--   session starts  = login_log.timestamp
--   session ends    = last access event (across all modules) before the next login
--                     for the most recent session, COALESCE to now()
-- Sessions where the user logged in but opened nothing show NULL duration.
-- These queries will also work once last_active_datetime is populated —
-- at that point the inferred end will be even more accurate.
-- =============================================================================

-- Shared CTEs used by all queries in this section
-- (copy the WITH block into each query you run standalone)

-- 7.1  All inferred sessions with duration — most recent first
WITH logins AS (
    SELECT
        l.user_id,
        l.id                                                           AS log_id,
        l.timestamp                                                    AS login_at,
        LEAD(l.timestamp) OVER (
            PARTITION BY l.user_id ORDER BY l.timestamp
        )                                                              AS next_login_at
    FROM users.users_login_log l
    WHERE l.user_id NOT IN (1,2,3,4)
),
all_access AS (
    SELECT user_id, access_datetime FROM projects.project_access_log       WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM accounts.account_access_log       WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM stocklists.stocklist_access_log   WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM prospector.opportunity_access_log WHERE user_id NOT IN (1,2,3,4)
),
session_ends AS (
    SELECT
        l.user_id,
        l.login_at,
        l.next_login_at,
        MAX(a.access_datetime)                                         AS last_activity_at
    FROM logins l
    LEFT JOIN all_access a
           ON a.user_id          =  l.user_id
          AND a.access_datetime  >= l.login_at
          AND a.access_datetime  <  COALESCE(l.next_login_at, now())
    GROUP BY l.user_id, l.login_at, l.next_login_at
)
SELECT
    u.username,
    s.login_at,
    s.last_activity_at,
    (coalesce(EXTRACT(EPOCH FROM s.last_activity_at - s.login_at)::int / 60,0)) + 5::int  AS duration_minutes,
    CASE WHEN s.last_activity_at IS NULL THEN 'No activity recorded' END AS note
FROM session_ends s
INNER JOIN users.users u ON u.id = s.user_id
ORDER BY s.login_at DESC;


-- 7.2  Average inferred session duration per user
--      Excludes sessions with no recorded activity and zero-duration sessions
WITH logins AS (
    SELECT
        l.user_id,
        l.timestamp                                                    AS login_at,
        LEAD(l.timestamp) OVER (
            PARTITION BY l.user_id ORDER BY l.timestamp
        )                                                              AS next_login_at
    FROM users.users_login_log l
    WHERE l.user_id NOT IN (1,2,3,4)
),
all_access AS (
    SELECT user_id, access_datetime FROM projects.project_access_log       WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM accounts.account_access_log       WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM stocklists.stocklist_access_log   WHERE user_id NOT IN (1,2,3,4)
    UNION ALL
    SELECT user_id, access_datetime FROM prospector.opportunity_access_log WHERE user_id NOT IN (1,2,3,4)
),
session_ends AS (
    SELECT
        l.user_id,
        l.login_at,
        MAX(a.access_datetime)                                         AS last_activity_at
    FROM logins l
    LEFT JOIN all_access a
           ON a.user_id          =  l.user_id
          AND a.access_datetime  >= l.login_at
          AND a.access_datetime  <  COALESCE(l.next_login_at, now())
    GROUP BY l.user_id, l.login_at
)
SELECT
    u.username,
    COUNT(*)                                                           AS total_sessions,
    COUNT(last_activity_at)                                            AS sessions_with_activity,
    COUNT(*) - COUNT(last_activity_at)                                 AS sessions_no_activity,
    (AVG(EXTRACT(EPOCH FROM last_activity_at - login_at)) / 60)::int  AS avg_duration_minutes,
    (MAX(EXTRACT(EPOCH FROM last_activity_at - login_at)) / 60)::int  AS max_duration_minutes,
    (MIN(EXTRACT(EPOCH FROM last_activity_at - login_at)) / 60)::int  AS min_duration_minutes
FROM session_ends s
INNER JOIN users.users u ON u.id = s.user_id
WHERE s.last_activity_at IS NOT NULL
  AND s.last_activity_at > s.login_at
GROUP BY u.username
ORDER BY avg_duration_minutes DESC;



---- Distance Analysis 

select 
EXTRACT(EPOCH FROM (completed_at - created_at))
 as timetaken,
j.* from strategy.distance_analysis_jobs j

where j.created_user_id != 1
and created_at >= '2026-03-01'
;



