import snowflake.snowpark as snowpark

from utils.common import get_sql_result

def get_agg_follow_info(session: snowpark.Session, p_date: str):
    schema = """
            with 
            
            follow_info as (
                select
                    id,
                    followed_profile_id,
                    following_profile_id
                from RDS_PROFILE_FOLLOW
                where followed_profile_id is not null and following_profile_id is not null and DATE(p_date) = '{p_date}'
            ), 
            
            following_counts as (
                select 
                    following_profile_id as user_id,
                    count(*) as total_following_cnt
                from follow_info
                group by following_profile_id
            ), 
            
            follower_counts as (
                select 
                    followed_profile_id as user_id,
                    count(*) as total_follower_cnt
                from follow_info
                group by followed_profile_id
            )

            select 
                following_counts.user_id, 
                following_counts.total_following_cnt, 
                follower_counts.total_follower_cnt
            from following_counts
            left join follower_counts 
                on following_counts.user_id = follower_counts.user_id
        """.format(p_date=p_date)
    return get_sql_result(session, schema)


def get_agg_clip_info(session: snowpark.Session, p_date: str):
    schema = """
            with clip_info as (
                select
                    user_id,
                    IFF(platform is null, 'web', platform) as platform,
                    count(*) as clip_total_cnt,
                    count_if(type = 'gen') as gen_clip_total_cnt
                from dim_clip
                where user_id is not null and status = 'complete' and DATE(p_date) = '{p_date}'
                group by user_id, platform
            )
            select 
                clip_info.user_id, 
                users.uid as user_uid, 
                iff(
                    users.subscription_status = 'active', 
                    users.subscription_plan_id, 
                    null
                ) as subscription_tier, 
                clip_info.platform, 
                clip_info.clip_total_cnt, 
                clip_info.gen_clip_total_cnt
            from clip_info
            left join rds_discord_info users 
                on users.user_id = clip_info.user_id;
        """.format(p_date=p_date)
    return get_sql_result(session, schema)


def get_agg_web_action_info(session: snowpark.Session, p_date: str):
    schema = """
            select
                a.user_id as user_uid,
                e.user_id as user_id,
                iff(MAX(e.subscription_status) = 'active', e.subscription_plan_id, null) as subscription_tier,
                sum(case when a.action_name = 'LikeSong' then 1 else -1 end) as net_likes_received_cnt,
                'web' as platform,
            from web_audio_actions as a
            left join rds_discord_info as e on a.user_id = e.uid
            where 1=1
                and a.user_id is not null
                and a.action_name in ('LikeSong', 'UndoLikeSong')
                and a.is_user_song_owner!='true'
                and DATE(a.p_date) = '{p_date}'
            group by a.user_id, e.user_id, e.subscription_plan_id
        """.format(p_date=p_date)
    return get_sql_result(session, schema)


def get_agg_ios_action_info(session: snowpark.Session, p_date: str):
    schema = """
            select
                a.user_id as user_uid,
                e.user_id, 
                iff(MAX(e.subscription_status) = 'active', e.subscription_plan_id, null) as subscription_tier,
                sum(case when a.action_name = 'LikeSong' then 1 else -1 end) as net_likes_received_cnt,
                'ios' as platform,
            from app_audio_actions as a
            left join rds_discord_info e 
                on a.user_id = e.uid
            where 1=1
                and a.user_id is not null
                and a.action_name in ('LikeSong', 'UndoLikeSong')
                and DATE(a.p_date) = '{p_date}'
                and a.is_user_song_owner!='true'
                and lower(os_name) in ('ios', 'ipados')
            group by a.user_id, e.user_id, e.subscription_plan_id
        """.format(p_date=p_date)
    return get_sql_result(session, schema)


def get_agg_play_info(session: snowpark.Session, p_date: str):
    schema = """
            select
                a.user_id,
                a.user_uid,
                b.subscription_plan_id as subscription_tier,
                sum(play_duration_sec) as play_duration_sec,
                sum(play_cnt) as play_cnt,
                platform,
            from agg_play_info_hourly as a
            left join rds_discord_info as b 
                on a.user_uid = b.uid and b.subscription_status = 'active'
            where 1=1
                and a.p_date = '{p_date}'
                and a.play_duration_threshold = 5
            group by a.user_id, a.user_uid, b.subscription_plan_id, platform;
    """.format(p_date=p_date)
    return get_sql_result(session, schema)