-- Survival analysis code reference from https://www.crosstab.io/articles/sql-survival-curves/

-- AVAILABLE FILTER VALUE 
-- Source:preset_custom_filter Column:day_use_to_churn
{% set day_use_to_churn = 120 %}


-- IMPORT
WITH companies AS (
    SELECT * FROM {{ ref('dim_shipping__companies') }}
),

origin_warehouse_shipping_packing_lists AS (
    SELECT * FROM {{ ref('fct_shipping__origin_warehouse_shipping_packing_lists') }}
),


-- LOGIC
duration_table AS (

    SELECT
        companies.sub_company_id,
        companies.sub_company_marking_id,
        companies.sub_company_created_datetime,
        COUNT(origin_warehouse_shipping_packing_lists.origin_warehouse_shipping_packing_list_id)        AS count_order,

        COUNT(IFF(
            origin_warehouse_shipping_packing_lists.origin_warehouse_shipping_order_status IN ('APPROVED', 'COMPLETED'),
            origin_warehouse_shipping_packing_lists.origin_warehouse_shipping_packing_list_id,
            null
        ))      AS count_completed_order,

        MAX(origin_warehouse_shipping_packing_lists.origin_warehouse_shipping_order_created_datetime)    AS last_order_datetime,
        COALESCE(last_order_datetime, sub_company_created_datetime)     AS last_activity_datetime,
        IFF(last_order_datetime IS NULL, 1, 0)                          AS is_never_order_company,

        -- if is_churn_company = 0, the data will be censored
        IFF(
            DATEDIFF(day, last_activity_datetime, CURRENT_DATE()) >= {{day_use_to_churn}},
            1,
            0
        )       AS is_churn_company,

        CASE
            WHEN is_churn_company = 1 THEN
                DATEDIFF(day, companies.sub_company_created_datetime, last_activity_datetime) + {{day_use_to_churn}}
            ELSE
                DATEDIFF(day, companies.sub_company_created_datetime, CURRENT_DATE())
        END     AS survival_time_days --event duration
    
    FROM companies

    LEFT JOIN origin_warehouse_shipping_packing_lists
        ON (companies.sub_company_id = origin_warehouse_shipping_packing_lists.sub_company_id)
    
    GROUP BY
        companies.sub_company_id,
        companies.sub_company_marking_id,
        companies.sub_company_created_datetime
    
    HAVING
        -- companies without order will not be relevant to the analysis
        is_never_order_company = 0

),

-- the daily_tally cte count the total number of observations at each survival_time_day
-- and the number of company that have churned at that survival_time_day
daily_observation_tally AS (

    SELECT
        survival_time_days,
        COUNT(survival_time_days)   AS total_number_of_observations,
        SUM(is_churn_company)       AS events   -- considering only churned company

    FROM duration_table
    
    GROUP BY survival_time_days

    ORDER BY survival_time_days

),

-- the cumulative_tally cte counts the number of subjects still at risk of experiencing churn
cumulative_tally AS (

    SELECT
        survival_time_days,
        events,
        total_number_of_observations,

        ( SELECT COUNT(DISTINCT(sub_company_id)) FROM duration_table )  AS total_number_of_subjects,

        -- cumulative sum of observations at all previous survival_time_days SUBTRACTED by total_number_of_subjects
        total_number_of_subjects - COALESCE(
            SUM(total_number_of_observations) OVER (ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
            ,0
            )   AS at_risk
    
    FROM daily_observation_tally

),

-- At each survival_time_day, count number of censored subject
-- censored subjects = # subject at risk - churned - # subject at risk in the next duration
compute_censored_subjects AS (

    SELECT
        total_number_of_subjects,
        survival_time_days,
        at_risk,
        total_number_of_observations,
        events,
        at_risk - events - COALESCE(
                LEAD(at_risk, 1) OVER (ORDER BY survival_time_days)
                ,0
        )   AS censored
    
    FROM cumulative_tally

    -- Simply subtracting events from number of observations would incorrectly ignore subjects censored at durations that are dropped from the output table
    WHERE events > 0

),

compute_probability AS (

    SELECT
        *,
        -- The survival probability represents the probability of customers that will not churn up to a specific tenure
        -- Example: survival_day = 96, survival_prob = 95%. For customers with 96 days of tenure, the customer has a 95% chance of not churning.
        EXP(SUM(
            -- When events / at_risk = 1, we replace value 1 with a value close to 1, to prevent log 0 which causes infinity value
            LN(CASE WHEN (1 - events / at_risk) = 0 THEN 0.999999 ELSE (1 - events / at_risk) END)     
            ) OVER (
                ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
        )      AS survival_probability,

        100 * (1 - EXP(SUM(
            -- When events / at_risk = 1, we replace value 1 with a value close to 1, to prevent log 0 which causes infinity value
            LN(CASE WHEN (1 - events / at_risk) = 0 THEN 0.999999 ELSE (1 - events / at_risk) END)
            ) OVER (
                ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
        ))     AS conversion_percentage,

        SUM(events / at_risk)
            OVER (
                ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )      AS cumulative_hazard,

        '{{ modules.datetime.datetime.now(modules.pytz.timezone("Asia/Kuala_Lumpur")) }}'       AS _dbt_ran_datetime

    FROM compute_censored_subjects

),


-- FINAL
final__rep_shipping__survival_analysis AS (
    
    SELECT
        -- dimension
        survival_time_days,
        at_risk,
        total_number_of_observations,
        events,
        censored,

        -- measures
        survival_probability,
        conversion_percentage,
        cumulative_hazard,

        -- metadata
        _dbt_ran_datetime
    
    FROM compute_probability

)

SELECT * FROM final__rep_shipping__survival_analysis