from datetime import datetime, timedelta, date
import pandas as pd
import streamlit as st
import altair as alt
from altair import datum
import pymysql

st.set_page_config(page_title="Activation Dashboard",
                   layout="wide",
                   initial_sidebar_state="collapsed")

st.title("Activation Dashboard")

connection = pymysql.connect(
    host = "gateway01.us-east-1.prod.aws.tidbcloud.com",
    port = 4000,
    user = "2T2wpG2t79tjsbf.root",
    password = "",
    database = "global_metrics",
    ssl_verify_cert = True,
    ssl_verify_identity = True,
    ssl_ca = "/etc/ssl/certs/ca-certificates.crt",
    autocommit = True,
)

@st.cache_data(ttl=60*60*24)
def get_user_list():
    with connection:
        with connection.cursor() as cursor:
            sql = '''
            SELECT tenant_id, signup_time, region, utm_source, utm_medium, utm_campaign
            FROM user_info
            WHERE kind = 'external'
            '''
            cursor.execute(sql)
            result = cursor.fetchall()
    df = pd.DataFrame(result, columns=['tenant_id', 'signup_time', 'region', 'utm_source', 'utm_medium', 'utm_campaign'])
    df['tenant_id'] = df['tenant_id'].astype(str)
    return df


@st.cache_data(ttl=60*60*24)
def get_active_user_list(begin, end):
    with connection:
        with connection.cursor() as cursor:
            sql = '''
            SELECT tenant_id, ts, program_name
            FROM active_user_info
            WHERE ts >= %s
            AND ts <= %s
            '''
            cursor.execute(sql, (begin, end))
            result = cursor.fetchall()
    df = pd.DataFrame(result, columns=['tenant_id', 'ts', 'program_name'])
    df['tenant_id'] = df['tenant_id'].astype(str)
    return df


def exclude_program_name(program_name):
    program_name_list = program_name.split(',')
    exclude_list = [
        'tcds',
        'tcds-vercel-console'
        'tcds-chat2query-console',
        'tcds-dataservice-adminapi',
        'tcds-dataapi-console',
        'upload'
    ]
    if all([i in exclude_list for i in program_name_list]):
        return False
    else:
        return True


@st.cache_data(ttl=60*60*24)
def filter_program_name(df):
    df = df[df.program_name.map(exclude_program_name)]
    return df


end = datetime.now()
begin = end - timedelta(days=210)

d = st.date_input(
    "Select a date range",
    (begin, end),
    format="MM.DD.YYYY",
)
begin, end = d
begin = begin - timedelta(days=29)
gf = get_active_user_list(begin.strftime("%Y-%m-%d"), end.strftime("%Y-%m-%d"))

uf = get_user_list()
uf['signup_time'] = pd.to_datetime(uf['signup_time'])

af = gf
af = filter_program_name(af)
af['ts'] = pd.to_datetime(af['ts'])
af.sort_values(by='ts', inplace=True)


region_select = st.selectbox('Region', uf.region.unique(), index=None)
utm_source_select = st.selectbox('utm_source', uf.utm_source.unique(), index=None)
utm_medium_select = st.selectbox('utm_medium', uf.utm_medium.unique(), index=None)
utm_campaign_select = st.selectbox('utm_campaign', uf.utm_campaign.unique(), index=None)


@st.cache_data(ttl=60*60*24)
def filter_df(tf, af, region_select, utm_source_select, utm_medium_select, utm_campaign_select):
    if region_select:
        tf = tf[tf.region == region_select]
    if utm_source_select:
        tf = tf[tf.utm_source == utm_source_select]
    if utm_medium_select:
        tf = tf[tf.utm_medium == utm_medium_select]
    if utm_campaign_select:
        tf = tf[tf.utm_campaign == utm_campaign_select]
    af = af[af.tenant_id.isin(tf.tenant_id)]

    return tf, af


df, af = filter_df(uf, af, region_select, utm_source_select, utm_medium_select, utm_campaign_select)

# line1
col1, col2 = st.columns(2)
with (col1):
    df = af.ts.unique()
    df = pd.DataFrame(df, columns=['ts'])
    for _, row in df.iterrows():
        df.loc[_, 'WAU'] = af[(af.ts > row.ts - timedelta(days=7)) & (af.ts <= row.ts)].tenant_id.nunique()
        df.loc[_, 'MAU'] = af[(af.ts > row.ts - timedelta(days=28)) & (af.ts <= row.ts)].tenant_id.nunique()
    df = df[28:]
    df['WAU / MAU'] = df['WAU'] / df['MAU']
    df = pd.melt(df, id_vars=['ts'], var_name='type', value_name='value')
    df['date'] = df.ts.dt.date
    left_df = df[df.type.isin(['WAU', 'MAU'])]
    right_df = df[df.type == 'WAU / MAU']
    chart = alt.Chart(left_df).mark_line().encode(
        x='date:T',
        y='value:Q',
        color='type:N'
    )
    chart2 = alt.Chart(right_df).mark_line(strokeDash=[3, 3]).encode(
        x='date:T',
        y='value:Q',
        color='type:N'
    )

    chart = alt.layer(chart, chart2).resolve_scale(
        y='independent'
    )
    col1.altair_chart(chart, use_container_width=True)

with col2:
    df = af.groupby(af.ts.dt.to_period("M")).tenant_id.nunique().reset_index().rename(columns={'ts': 'month', 'tenant_id': 'MAU'})
    df.month = df.month.dt.strftime('%Y-%m')
    chart = alt.Chart(df).mark_line().encode(
        x='month:N',
        y='MAU:Q',
    )
    col2.altair_chart(chart, use_container_width=True)

# line2
col1, col2 = st.columns(2)
nf = pd.merge(af, uf, on='tenant_id', how='left')
nf['min_ts'] = nf.groupby('tenant_id')['ts'].transform('min')
new_df = nf[nf.ts == nf.min_ts]
returning_df = nf[nf.ts != nf.min_ts]
with col1:
    df = nf.ts.unique()
    df = pd.DataFrame(df, columns=['ts'])
    for _, row in df.iterrows():
        df.loc[_, 'New MAU'] = new_df[(new_df.ts > row.ts - timedelta(days=28)) & (new_df.ts <= row.ts)].tenant_id.nunique()
        df.loc[_, 'Returning MAU'] = returning_df[(returning_df.ts > row.ts - timedelta(days=28)) & (returning_df.ts <= row.ts)].tenant_id.nunique()
    df = df[28:]
    df = pd.melt(df, id_vars=['ts'], var_name='type', value_name='value')
    df['date'] = df.ts.dt.date
    chart = alt.Chart(df).mark_line().encode(
        x='date:T',
        y='value:Q',
        color='type:N'
    )
    col1.altair_chart(chart, use_container_width=True)

with col2:
    df = nf.ts.map(lambda x: x.strftime('%Y-%m')).unique()
    df = pd.DataFrame(df, columns=['month'])
    for _, row in df.iterrows():
        df.loc[_, 'New MAU'] = new_df[new_df.ts.map(lambda x: x.strftime('%Y-%m')) == row.month].tenant_id.nunique()
        df.loc[_, 'Returning MAU'] = returning_df[returning_df.ts.map(lambda x: x.strftime('%Y-%m')) == row.month].tenant_id.nunique()

    df = pd.melt(df, id_vars=['month'], var_name='type', value_name='value')
    chart = alt.Chart(df).mark_line().encode(
        x='month:N',
        y='value:Q',
        color='type:N'
    )
    col2.altair_chart(chart, use_container_width=True)

# line3
col1, col2 = st.columns(2)
with col1:
    df = af.ts.unique()
    df = pd.DataFrame(df, columns=['ts'])
    min_ts_df = af.groupby('tenant_id').ts.min().reset_index().rename(columns={'ts': 'min_ts'})
    nf = pd.merge(uf, min_ts_df, on='tenant_id', how='right')
    nf['converted_days'] = nf.min_ts - nf.signup_time
    for _, row in df.iterrows():
        last_28 = nf[(nf.signup_time > row.ts - timedelta(days=28)) & (nf.signup_time <= row.ts)]
        if last_28.empty:
            continue
        df.loc[_, '7 days'] = last_28[last_28.converted_days < timedelta(days=7)].tenant_id.nunique() / last_28.tenant_id.nunique()
        df.loc[_, '14 days'] = last_28[(timedelta(days=7) <= last_28.converted_days) & (last_28.converted_days < timedelta(days=14))].tenant_id.nunique() / last_28.tenant_id.nunique()
        df.loc[_, '28 days'] = last_28[(timedelta(days=7) <= last_28.converted_days) & (last_28.converted_days < timedelta(days=28))].tenant_id.nunique() / last_28.tenant_id.nunique()
        df.loc[_, 'over 28 days'] = last_28[last_28.converted_days >= timedelta(days=28)].tenant_id.nunique() / last_28.tenant_id.nunique()
        df.loc[_, 'converted duration avg'] = last_28.converted_days.mean() / timedelta(days=1)
    df = df[28:]
    df = pd.melt(df, id_vars=['ts'], var_name='converted duration', value_name='value')
    df['date'] = df.ts.dt.date
    left_df = df[df['converted duration'].isin(['7 days', '14 days', '28 days', 'over 28 days'])]
    right_df = df[df['converted duration'] == 'converted duration avg']

    chart = alt.Chart(left_df).mark_line().encode(
        x='date:T',
        y='value:Q',
        color='converted duration:N'
    )
    chart2 = alt.Chart(right_df).mark_line(strokeDash=[3, 3]).encode(
        x='date:T',
        y='value:Q',
        color='converted duration:N'
    )

    chart = alt.layer(chart, chart2).resolve_scale(
        y='independent'
    )
    col1.altair_chart(chart, use_container_width=True)

# with col2:
#     df = nf.ts.map(lambda x: x.strftime('%Y-%m')).unique()
#     df = pd.DataFrame(df, columns=['month'])
#     for _, row in df.iterrows():