#!/usr/bin/env python
# coding: utf-8

# <li>Get new Clash API: Done </li>
# <li>Get Clash war stats: Done</li>
# <li>Prep Clash clan war data: Done</li>
# <li>Prep Clash member war data: Done</li>
# <li>Get Discord Bot API keys: Done</li>
# <li>Make Clash player tag and Discord account reference table: Done</li>
# <li>Get test code for Discord Bot API and tag a user: Done</li>
# <li>Get a list of players still missing attacks: Done</li>
# <li>Mention players missing attacks on Discord: Pending</li>

# In[2]:


import requests
import pandas as pd
from datetime import date, datetime, timezone, timedelta
import numpy as np
from google.cloud import bigquery


# In[3]:


api_token = 'token'
today = date.today()
client = bigquery.Client.from_service_account_json(r'ServiceAccount.json')
clan_tags = ["QG9UJ08","Q2JLCVLR","P09YGYU2","2YCCGY8JU"]


# In[4]:


def import_to_gbq(df, destination_table):
    try:
        ## If you want the column and row count then
        table_id = destination_table
        job_config = bigquery.LoadJobConfig(
            schema=[
            ],
            write_disposition="WRITE_TRUNCATE",
        )

        job = client.load_table_from_dataframe(
            df, table_id, job_config=job_config
        )  # Make an API request.

        job.result()  # Wait for the job to complete.
        table = client.get_table(table_id)  # Make an API request.
        print("Loaded {} rows and {} columns to {}".format(table.num_rows, len(table.schema), table_id))
    except Exception as e:
        print(e)


# In[5]:


def get_current_war_data(clan_tag):
    url = "https://api.clashofclans.com/v1/clans/%23{clan_tag}/currentwar".format(clan_tag=clan_tag)
    r = requests.get(url, headers={"Accept":"application/json", "authorization":"Bearer "+api_token}) #, params = {"limit":20})
    data = r.json()

    if r.status_code == 200:
        if data.get('state') == "inWar" or data.get('state') == "preparation" or data.get('state') == "warEnded":
            return data
    else:
        return None


# In[6]:


# Define a function to calculate time left in hours and minutes (as a decimal)
def calculate_time_left(end_time, utc_offset_hours=0):
    war_end_time = datetime.strptime(end_time, "%Y%m%dT%H%M%S.%fZ").replace(tzinfo=timezone.utc)
    my_local_time_plus_UTC = datetime.now(timezone.utc) + timedelta(hours=utc_offset_hours)
    time_left = war_end_time - my_local_time_plus_UTC
    hours_left = time_left.total_seconds() / 3600  # Convert total seconds to hours
    return round(hours_left, 1)  # Round to 1 decimal place for readability


# In[7]:


# Define a function to extract the required information and create a dictionary with clan high level info, like total stars, attacks, percent destruction.
def extract_clan_info(clan_info):
    return {
        'clanTag': clan_info.get('tag', ''),
        'clanName': clan_info.get('name', ''),
        'clanLevel': clan_info.get('clanLevel', 0),
        'attacks': clan_info.get('attacks', 0),
        'stars': clan_info.get('stars', 0),
        'destructionPercentage': clan_info.get('destructionPercentage', 0.00)
    }


# Define a function to reformat the clan info for single row representation, for both my clan and enemy clan
def reformat_for_single_row(user_clan, opponent_clan):
    combined_data = {}
    for key, value in user_clan.items():
        combined_data[f'user_{key}'] = value
    for key, value in opponent_clan.items():
        combined_data[f'opponent_{key}'] = value
    return combined_data




# In[8]:


# Define a function to process member attack data
def process_member_attacks(member):
    # Initialize default values
    attack_details = {
        'player_tag': member.get('tag',''),
        'name': member.get('name', ''),
        'total_attacks': 0,
        # 'first_attack_stars': None,
        # 'second_attack_stars': None,
        # 'first_attack_destruction': None,
        # 'second_attack_destruction': None
        'first_attack_stars': np.nan,
        'second_attack_stars': np.nan,
        'first_attack_destruction': np.nan,
        'second_attack_destruction': np.nan
    }
    
    # Process each attack
    attacks = member.get('attacks', [])
    attack_details['total_attacks'] = len(attacks)
    for i, attack in enumerate(attacks):
        if i == 0:  # First attack
            attack_details['first_attack_stars'] = attack.get('stars', 0)
            attack_details['first_attack_destruction'] = attack.get('destructionPercentage', 0)
        elif i == 1:  # Second attack
            attack_details['second_attack_stars'] = attack.get('stars', 0)
            attack_details['second_attack_destruction'] = attack.get('destructionPercentage', 0)
    
    return attack_details


# ### Get Aggregated data for each clan and insert in BigQuery for storing

# In[9]:


single_row_df_list = []
members_attacks_df_list = []
try:
    for clan_tag in clan_tags:
        data = get_current_war_data(clan_tag)
        if not data:
            print("Clan Tag {clan_tag} not found, please review the tag, or perhaps the clan doesn't have war log public.".format(clan_tag=clan_tag))
        else:
            # Extract the necessary data for both the user's clan and the enemy clan
            start_time = data.get('startTime', '')
            end_time = data.get('endTime', '')
            war_status = data.get('state', '')

            
            clan_data = data['clan']
            opponent_data = data['opponent']

            clan_stats = extract_clan_info(clan_data)
            opponent_stats = extract_clan_info(opponent_data)

            # Reformat the clan and opponent data
            single_row_data = reformat_for_single_row(clan_stats, opponent_stats)

            # Create a DataFrame from the single row of data
            single_row_df = pd.DataFrame([single_row_data])

            single_row_df['startTime'] = start_time
            single_row_df['endTime'] = end_time
            single_row_df['war_end_ts'] = datetime.strptime(end_time, "%Y%m%dT%H%M%S.%fZ")
            # Calculate the time left in the war
            time_left_in_hours = calculate_time_left(single_row_df['endTime'][0], utc_offset_hours=0)
            single_row_df['timeLeft'] = str(time_left_in_hours)
            single_row_df['war_status'] = war_status

            single_row_df_list.append(single_row_df)


            # Get a list of each player clan to see their stats and also add all information into one list/dataframe
            members_attack_data = [process_member_attacks(member) for member in data['clan']['members']]

            members_attacks_df = pd.DataFrame(members_attack_data)
            members_attacks_df['clan_tag'] = "#"+clan_tag
            members_attacks_df['war_end_ts'] = datetime.strptime(end_time, "%Y%m%dT%H%M%S.%fZ")
            members_attacks_df['timeLeft'] = str(time_left_in_hours)
            members_attacks_df['war_status'] = war_status
            
            members_attacks_df_list.append(members_attacks_df)
except Exception as e:
    print(e)

#Add all clans from the list into one Dataframe before inserting to BigQuery
final_aggregated_clan_df = pd.concat(single_row_df_list)
final_members_attacks_df = pd.concat(members_attacks_df_list)

# clan_war_status = data.get('state')
# final_members_attacks_df['war_status'] = clan_war_status


# In[10]:


final_aggregated_clan_df


# In[11]:


final_members_attacks_df


# In[12]:


#Load both dataframes as string to bigquery
import_to_gbq(final_aggregated_clan_df.astype(str), 'fleet-parser-330316.luis_stage.stg_coc_clan_war_clan_information')
import_to_gbq(final_members_attacks_df.astype(str), 'fleet-parser-330316.luis_stage.stg_coc_clan_war_player_information')
try:
    query_job = client.query('CALL `fleet-parser-330316.luistest.sp_coc_clan_war_clan_information`();')
    results = query_job.result()

    if query_job.state == 'DONE':
        print('Updated fleet-parser-330316.luistest.stg_coc_clan_war_clan_information.')

    query_job = client.query('CALL `fleet-parser-330316.luistest.sp_coc_clan_war_player_information`();')
    results = query_job.result()

    if query_job.state == 'DONE':
        print('Updated fleet-parser-330316.luistest.stg_coc_clan_war_player_information.')        
except Exception as e:
    print(e)