-- First establish some base parameters for our users
WITH config AS (
    SELECT
        -- For now we're using this as our free-trial filter. We'll treat users
        -- that churn during this period differently since they likely haven't actually
        -- tried the software very much. We'll also use this to isolate feature usage
        -- from this period, as it's likely that user goals are different at this stage.
        14 AS short_term,
        -- This is our ~day74 cutoff. For users, we want to see patterns of usage here
        -- that might differ from those who either churn later or hang around for a while.
        -- For feature aggregates, this should represent the "serious" efforts to use ZenMaid.
        90 AS medium_term,
        -- This is a (in theory) rarer group of churned users that lasted beyond our day 74
        -- danger zone but still churned.
        -- Beyond this we'll consider anyone remaining a "success" (even if they churned at some
        -- point) since things like company closures, etc. start to come into play more
        -- frequently.
        -- For features, we'll look at this as features used in the company's day-to-day, and not
        -- evaluation attempts.
        360 AS long_term,
        -- We'll only look back a few years since we want to make fairish comparisons betwen users
        -- exposed to "modern" ZenMaid tools.
        CAST('2023-01-01' AS timestamp) AS date_cutoff
),
-- Then establish a standard core set of user data
users_with_churn_stats AS (
    SELECT id, active, billing_state,
           DATE_TRUNC('month', created_at) AS month_created,
           created_at, free_trial_ends_at, updated_at,
           updated_at::date - created_at::date AS lifespan
    FROM users, config
    -- We'll only look at newish users.
    WHERE created_at >= config.date_cutoff
),
-- Next use that core data to extract the records we want to analyze
-- and categorize them by date and status
users_to_examine AS (
    -- First the users who exited just after the trial
    SELECT 'quick-exit' AS category, uwcs.*
    FROM users_with_churn_stats uwcs, config
    WHERE billing_state = 'CHURNED'
      AND lifespan <= config.short_term

    UNION

    -- Next, users who gave the tools some time but still left
    SELECT 'fair-trial' AS category, uwcs.*
    FROM users_with_churn_stats uwcs, config
    WHERE billing_state = 'CHURNED'
      AND lifespan < config.medium_term
      AND lifespan > config.short_term

    UNION

    -- Then some longer-time users who still churned.
    -- We'll cut this off after 6 months since afterwards
    -- company closures, etc. could factor in.

    SELECT 'short-termer' AS category, uwcs.*
    FROM users_with_churn_stats uwcs, config
    WHERE billing_state = 'CHURNED'
      AND lifespan >= config.medium_term
      AND lifespan < config.long_term

    UNION

    -- Finally, people who are still kicking.
    -- This cohort is going to be a little wrong for older
    -- dates since it only includes those who are STILL around.
    SELECT 'long-termer' AS category, uwcs.*
    FROM users_with_churn_stats uwcs, config
    -- We could also filter this by the following if we wanted to look at current users:
    -- billing_state NOT IN ('CHURNED', 'ONBOARDING', 'UNKNOWN')
    -- ...but for a multi-year peek into history, we'll lose too many good users who left
    -- for other reasons like closures, etc.
    WHERE lifespan >= config.long_term
),

-- Now we'll start pulling counts from our various core features to help check usage.
-- We'll ALSO bucket these by the same eras we're using to split up our users.
-- For example, things created in the short_term are probably less serious integration
-- efforts - users are just poking around at the software to see if they want to give it a
-- serious try.
-- Medium-term (up to our day 74ish cutoff) is when users REALLY decide if they want to build
-- a business around ZenMaid.
-- Long-term should represent users who are actively using ZenMaid for their day-to-day operations.

-- Well add booking forms created by era
booking_form_counts AS (
    -- Establish a summary of records with ages
    WITH summary AS (
        SELECT booking_forms.user_id,  booking_forms.created_at::date - users.created_at::date AS created_age
        FROM config, booking_forms
            JOIN users ON booking_forms.user_id = users.id
        WHERE booking_forms.created_at >= config.date_cutoff
            AND users.created_at >= config.date_cutoff
            AND booking_forms.created_at <= users.created_at::date + config.long_term
    ),
    -- Group by week for line charts
    weekly_counts AS (
        SELECT user_id,
               jsonb_object_agg(
                 'week_' || weeks_in,
                 weekly_count
               ) AS weekly_counts,
               COUNT(*) AS total
        FROM (SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count
              FROM summary
              GROUP BY user_id, weeks_in) by_weeks
        GROUP BY user_id
    ),
    -- Group by era for bar charts
    bucket_counts AS (
        SELECT user_id,
               SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term,
               SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term,
               SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term,
               COUNT(*) AS total
        FROM config, summary
        GROUP BY user_id
    )
    -- Put it all together
    SELECT bucket_counts.*, weekly_counts.weekly_counts
    FROM bucket_counts JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id
),
-- Bookings made by era
booking_counts AS (
    -- Establish a summary of records with ages
    WITH summary AS (
        SELECT booking_forms.user_id, bookings.created_at::date - users.created_at::date AS created_age
         FROM config,
              booking_forms
                  JOIN users ON booking_forms.user_id = users.id
                  JOIN bookings ON bookings.booking_form_id = booking_forms.id
         WHERE booking_forms.created_at >= config.date_cutoff
           AND bookings.created_at >= config.date_cutoff
           AND users.created_at >= config.date_cutoff
    ),
     -- Group by week for line charts
     weekly_counts AS (
        SELECT user_id,
          jsonb_object_agg(
              'week_' || weeks_in,
              weekly_count
          ) AS weekly_counts,
          COUNT(*) AS total
        FROM (
            SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count
            FROM summary
            GROUP BY user_id, weeks_in) by_weeks
        GROUP BY user_id
    ),
    -- Group by era for bar charts
    bucket_counts AS (
        SELECT user_id,
            SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term,
            SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term,
            SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term,
            COUNT(*) AS total
        FROM config, summary
        GROUP BY user_id
    )
    -- Put it all together
    SELECT bucket_counts.*, weekly_counts.weekly_counts
    FROM bucket_counts
    JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id
),
-- Employees added by era
employee_counts AS (
    -- Establish a summary of records with ages
    WITH summary AS (
        SELECT employees.user_id,  employees.created_at::date - users.created_at::date AS created_age
        FROM config, employees
            JOIN users ON employees.user_id = users.id
        WHERE employees.created_at >= config.date_cutoff
            AND users.created_at >= config.date_cutoff
            AND employees.created_at <= users.created_at::date + config.long_term
    ),
    -- Group by week for line charts
    weekly_counts AS (
        SELECT user_id,
               jsonb_object_agg(
                 'week_' || weeks_in,
                 weekly_count
               ) AS weekly_counts,
               COUNT(*) AS total
        FROM (SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count
              FROM summary
              GROUP BY user_id, weeks_in) by_weeks
        GROUP BY user_id
    ),
    -- Group by era for bar charts
    bucket_counts AS (
        SELECT user_id,
               SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term,
               SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term,
               SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term,
               COUNT(*) AS total
        FROM config, summary
        GROUP BY user_id
    )
    -- Put it all together
    SELECT bucket_counts.*, weekly_counts.weekly_counts
    FROM bucket_counts JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id
),
-- Contacts created by era
contact_counts AS (
    -- Establish a summary of records with ages
    WITH summary AS (
        SELECT customers.user_id,  customers.created_at::date - users.created_at::date AS created_age
        FROM config, customers
            JOIN users ON customers.user_id = users.id
        WHERE customers.created_at >= config.date_cutoff
            AND users.created_at >= config.date_cutoff
            AND customers.created_at <= users.created_at::date + config.long_term
    ),
    -- Group by week for line charts
    weekly_counts AS (
        SELECT user_id,
               jsonb_object_agg(
                 'week_' || weeks_in,
                 weekly_count
               ) AS weekly_counts,
               COUNT(*) AS total
        FROM (SELECT user_id, FLOOR(created_age / 7) AS weeks_in, COUNT(*) as weekly_count
              FROM summary
              GROUP BY user_id, weeks_in) by_weeks
        GROUP BY user_id
    ),
    -- Group by era for bar charts
    bucket_counts AS (
        SELECT user_id,
               SUM(CASE WHEN created_age < config.short_term THEN 1 ELSE 0 END) AS short_term,
               SUM(CASE WHEN created_age >= config.short_term AND created_age < config.medium_term THEN 1 ELSE 0 END) AS medium_term,
               SUM(CASE WHEN created_age >= config.medium_term AND created_age < config.long_term THEN 1 ELSE 0 END) AS long_term,
               COUNT(*) AS total
        FROM config, summary
        GROUP BY user_id
    )
    -- Put it all together
    SELECT bucket_counts.*, weekly_counts.weekly_counts
    FROM bucket_counts JOIN weekly_counts ON weekly_counts.user_id = bucket_counts.user_id
)
-- Finally, we'll flatten it all out into a repurposable flat table
SELECT users_to_examine.id,
       users_to_examine.active,
       users_to_examine.billing_state,
       users_to_examine.free_trial_ends_at,
       users_to_examine.category,
       users_to_examine.created_at,
       users_to_examine.month_created,
       users_to_examine.updated_at,
       users_to_examine.lifespan,
       COALESCE(booking_form_counts.short_term, 0) AS booking_forms_short_term,
       COALESCE(booking_form_counts.medium_term, 0) AS booking_forms_medium_term,
       COALESCE(booking_form_counts.long_term, 0) AS booking_forms_long_term,
       COALESCE(booking_form_counts.total, 0) AS booking_forms,
       COALESCE(booking_form_counts.weekly_counts, '{}'::jsonb) AS booking_forms_weekly_counts,
       COALESCE(booking_counts.short_term, 0) AS bookings_short_term,
       COALESCE(booking_counts.medium_term, 0) AS bookings_medium_term,
       COALESCE(booking_counts.long_term, 0) AS bookings_long_term,
       COALESCE(booking_counts.total, 0) AS bookings,
       COALESCE(booking_counts.weekly_counts, '{}'::jsonb) AS bookings_weekly_counts,
       COALESCE(employee_counts.short_term, 0) AS employees_short_term,
       COALESCE(employee_counts.medium_term, 0) AS employees_medium_term,
       COALESCE(employee_counts.long_term, 0) AS employees_long_term,
       COALESCE(employee_counts.total, 0) AS employees,
       COALESCE(employee_counts.weekly_counts, '{}'::jsonb) AS employee_weekly_counts,
       COALESCE(contact_counts.short_term, 0) AS contacts_short_term,
       COALESCE(contact_counts.medium_term, 0) AS contacts_medium_term,
       COALESCE(contact_counts.long_term, 0) AS contacts_long_term,
       COALESCE(contact_counts.total, 0) AS contacts,
       COALESCE(contact_counts.weekly_counts, '{}'::jsonb) AS contacts_weekly_counts
FROM users_to_examine
    LEFT JOIN booking_form_counts ON booking_form_counts.user_id = users_to_examine.id
    LEFT JOIN booking_counts ON booking_counts.user_id = users_to_examine.id
    LEFT JOIN employee_counts ON employee_counts.user_id = users_to_examine.id
    LEFT JOIN contact_counts ON contact_counts.user_id = users_to_examine.id
;