import utility.function as func
from dashboard.genQuery import GenerateQuery
import random
from bigquery.bq import BigQuery
import numpy as np
import pandas as pd
from faker.providers import job, address
from datetime import datetime, timedelta
import json
bq = BigQuery()

private_key = func.Key.load_key_from_env("private_key")
def transformUserData_main_profile(user_profile):
    phoneNumber = []
    email = []
    name = []
    image_profile = []
    if "phoneNumber_PGP" in user_profile:
        phoneNumber_PGP = user_profile['phoneNumber_PGP']
        for i in phoneNumber_PGP:
            id = phoneNumber_PGP[i]['id']
            decode_id = func.Key.pgp_decrypt(id,private_key)
            phoneNumber.append(decode_id)
    
    if "email_PGP" in user_profile:
        email_PGP = user_profile['email_PGP']
        for i in email_PGP:
            id = email_PGP[i]['id']
            decode_id = func.Key.pgp_decrypt(id,private_key)
            email.append(decode_id)
    
    if "name_PGP" in user_profile:
        name_PGP = user_profile['name_PGP']
        for i in name_PGP:
            id = name_PGP[i]['id']
            decode_id = func.Key.pgp_decrypt(id,private_key)
            name.append(decode_id)
    
    if "line" in user_profile:
        line = user_profile['line']
        for i in line:
            pic = line[i].get("pictureUrl", None)
            image_profile.append(pic)

    calculate = {
        "status": '-',
        "lifetime_spend": 0,
        "engagement_score": '-',
        "loyalty_points": '-'
    }

    objectReturn = {
        "id": "main-profile",
        "data": {
                    "name": name,
                    "image_profile": image_profile,
                    "phoneNumber": phoneNumber,
                    "email": email,
                    "calculate": calculate
                }
    }

    return objectReturn

def transformUserData_journey(property_id:str , user_pseudo_id: str):
    genq = GenerateQuery(property_id=property_id)
    query = genq.getJourneyExplore(user_pseudo_id)
    df = bq.get_query_df(query)

    if not df.empty:
    
        df['date'] = pd.to_datetime(df['date'],"%Y-%m-%dT%H:%M:%S")
        df['time'] = df['date'].dt.strftime('%H:%M')
        df['date'] = df['date'].dt.strftime('%Y-%m-%d')
        records = df.to_dict(orient='records')
        objectReturn = {
            "id": "journey",
            "data": records
        }
        return objectReturn
    
    return {
            "id": "journey",
            "data": []
        }

def transformUserData_second_profile():
    objectReturn = {
            "id": "second-profile",
            "data": {
                    "gender": '-',
                    "age": '-',
                    "location": '-',
                    "branch": '-',
                    "interests": []
                }
        }

    return objectReturn

def transformUserData_channel_engagement(property_id:str, user_pseudo_id: str):
    genq = GenerateQuery(property_id=property_id)
    queryRatio = genq.getChannelRatio(user_pseudo_id)
    queryAds = genq.getLastEngageAds(user_pseudo_id)
    df = bq.get_query_df(queryRatio)
    if not df.empty:
        df['value'] = df['value'].astype(int)
        sunEngagement = df['value'].sum()
        df['ratio'] = df['value'] / sunEngagement
        records = df.to_dict(orient='records')

        #pie
        pieObject = {
            "data": records,
            "total": int(sunEngagement)
            }
    else:
        pieObject  = {}
    
    df1 = bq.get_query_df(queryAds)
    if not df1.empty:
        photo_url_max = df1.sort_values(by='date', ascending=False).iloc[0]['photo_url']
        date_url_max = df1.sort_values(by='date', ascending=False).iloc[0]['date']
        photo_url_min = df1.sort_values(by='date', ascending=True).iloc[0]['photo_url']
        date_url_min = df1.sort_values(by='date', ascending=True).iloc[0]['date']
        exampleObject = {
                    "ad_first": {
                        "image": photo_url_min,
                        "datetime": date_url_min
                    },
                    "ad_last": {
                        "image": photo_url_max,
                        "datetime": date_url_max
                    },
                    "product_first": "",
                    "product_last": ""
                }
    else:
        exampleObject = {}
        
    return  {
            "id": "engagement",
            "data": {
                "pie": pieObject,
                "engagement": exampleObject
            }
        }

def transformUserData_conversation(property_id:str, user_pseudo_id: str):
    genq = GenerateQuery(property_id=property_id)
    query = genq.getConversation(user_pseudo_id)
    df = bq.get_query_df(query)
    if not df.empty:
        df['date'] = pd.to_datetime(df['date'])
        df['time'] = df['date'].dt.strftime('%H:%M')
        df['date'] = df['date'].dt.strftime('%Y-%m-%d')
        df.sort_values(by=['source', 'date', 'time'], ascending=False, inplace=True)
        records = df.to_dict(orient='records')

        objectReturn = {
            "id": "conversation",
            "data": records
        }
        return objectReturn

    return {
            "id": "conversation",
            "data": []
        }

def transformUserData_transaction(property_id:str, user_pseudo_id: str):
    
    transaction_table_exist = bq.ensure_table_exist(f"client_{property_id}.transaction")
    if transaction_table_exist:
        life_time_order = bq.bq_count_rows_with_condition(f"client_{property_id}.transaction",f"user_pseudo_id = {user_pseudo_id}")
        avg_order_amount = bq.bq_avg_column_with_condition(f"client_{property_id}.transaction","total_price",f"user_pseudo_id = {user_pseudo_id}")
        transaction = bq.query_transaction_with_condition(f"client_{property_id}.transaction",f"user_pseudo_id = {user_pseudo_id}")
        
        objectReturn = {
                "id": "transaction",
                "data": {
                    "life_time_orders": life_time_order,
                    "avg_order_amount": avg_order_amount,
                    "transactions": transaction
                }
            }
        
    else:
        objectReturn = {
                "id": "transaction",
                "data": {
                    "life_time_orders": 0,
                    "avg_order_amount": 0,
                    "transactions": []
                }
            }
        
    return objectReturn

def transformUserData_detail_journey(property_id:str, user_pseudo_id: str):
    genq = GenerateQuery(property_id=property_id)
    query = genq.getDetailJourney(user_pseudo_id)
    df = bq.get_query_df(query)

    if not df.empty:
        df['date'] = pd.to_datetime(df['date'])
        df['time'] = df['date'].dt.strftime('%H:%M')
        df['date'] = df['date'].dt.strftime('%Y-%m-%d')

        df_ad = df[df['action']=='click_messenger_ad']
        if not df_ad.empty:
            df_ad['detail'] = df_ad['ad_title']
        df_non_ad = df[df['action']!='click_messenger_ad']
        df_non_ad = df_non_ad[df_non_ad['ep_key'].isin(['message', 'target_id'])]
        df_non_ad.drop_duplicates(subset=['eventId'], inplace=True)
        df_t = pd.concat([df_non_ad, df_ad])
        try:
            df_t.pop('ad_title')
        except:
            pass

        try:
            df_t.pop('ep_key')
        except:
            pass

        df_t.sort_values(by=['date', 'time'], ascending=False, inplace=True)
        records = df_t.to_dict(orient='records')

        click_ads = df_t[(df_t['action']=='click_messenger_ad') & (df_t['channel']=='facebook')].count()['eventId']
        message_facebook = df_t[(df_t['action'].astype(str).str.contains('user_message|contact_info_submitted', regex=True)) & (df_t['channel']=='facebook')].count()['eventId']
        message_line = df_t[(df_t['action'].astype(str).str.contains('user_message|contact_info_submitted', regex=True)) & (df_t['channel']=='line')].count()['eventId']

        objectReturn = {
            "data": {
                "summary": {
                    "click_ads": int(click_ads),
                    "message_facebook": int(message_facebook),
                    "message_line": int(message_line),
                    "website": 0
                },
                "table": {
                    "data": records
                }
            }
        }

        return objectReturn
    else:
        return {}
    
def transformUserData_detail_profile(property_id:str, user_pseudo_id: str, fb):
    from faker import Faker
    fake = Faker()
    age = random.randint(18, 50)
    today = datetime.today()
    birth_year = today.year - age
    birth_month = random.randint(1, 12)
    if birth_month == 2:  # February
        if (birth_year % 4 == 0 and birth_year % 100 != 0) or (birth_year % 400 == 0):
            max_day = 29
        else:
            max_day = 28
    elif birth_month in [4, 6, 9, 11]:
        max_day = 30
    else:
        max_day = 31
    
    birth_day = random.randint(1, max_day)
    birth_date = datetime(birth_year, birth_month, birth_day)

    #social
    profileLine = fb.db.reference().child(f"account/{property_id}/profile/{user_pseudo_id}/line").get()
    profileFacebook = fb.db.reference().child(f"account/{property_id}/profile/{user_pseudo_id}/facebook").get()

    packLINE = []
    if profileLine:
        for i in profileLine:
            type = "line"
            id = profileLine[i]['id']
            page_id = profileLine[i]["source"]['page_id']
            page_name = fb.db.reference().child(f"property/{property_id}/channel/line/{page_id}/name").get()

            pack = {
                "type": type,
                "channel_name": page_name,
                "social_id": id
            }
            packLINE.append(pack)

    packFacebook = []
    if profileFacebook:
        for i in profileFacebook:
            type = 'facebook'
            id = profileFacebook[i]['id']
            page_id = profileFacebook[i]["source"]['page_id']

            #Get page name

            page_name = fb.db.reference().child(f"property/{property_id}/channel/facebook/{page_id}/name").get()

            pack = {
                "type": type,
                "channel_name": page_name,
                "social_id": id
            }
            packFacebook.append(pack)
    
    packLINE.extend(packFacebook)

    objectReturn = {
        "data": {
            "profile": {
                "gender": random.choice(['Male', 'Female']),
                "birth_date": birth_date.strftime("%Y-%m-%d"),
                "age": age,
                "location": random.choice(['Bangkok', 'Lopburi', 'Krabi', 'Nonthaburi', 'Chonburi']),
                "occupation": fake.job(),
                "interest": ["Movie", "Game"],
            },
            "social_profile": packLINE
        }
    }

    return objectReturn