import pandas as pd
import numpy as np
import gradio as gr
import datetime
from dateutil.relativedelta import relativedelta
from function import get_fish_qty, get_estat, dr_prediction_deployment

import yaml
with open('config.yaml') as file:
    config = yaml.safe_load(file.read())

def create_prediction_data(fish_sell_ach, oil_price_url, fuel_procurement_cost_url):
    oil_price_df = pd.read_excel(oil_price_url, header=5)
    oil_price_df = oil_price_df.rename(columns={oil_price_df.columns[0]:'年'})
    oil_price_df['年'] = oil_price_df['年'].interpolate(method='ffill')
    oil_price_df['年月'] = oil_price_df['年'] + oil_price_df['月'].astype(str) + '月'
    oil_price_df['年月'] = pd.to_datetime(oil_price_df['年月'], format='%Y年%m月')
    oil_price_df['年月'] = oil_price_df['年月'].apply(lambda x:x+relativedelta(months=3))
    oil_price_df['年月'] = oil_price_df['年月'].apply(lambda x:''.join(str(x).split('-'))[:6]).astype(int)
    oil_price_df = oil_price_df.drop(['年', '月'], axis=1)
    for i in oil_price_df.columns:
        if i != '年月':
            oil_price_df = oil_price_df.rename(columns={i:f'{i}_lag3'})
            oil_price_df[f'{i}_lag3'] = oil_price_df[f'{i}_lag3'].shift(1)
    
    fuel_procurement_cost_df = pd.read_excel(fuel_procurement_cost_url, header=4)
    fuel_procurement_cost_df = fuel_procurement_cost_df.iloc[:, 3:]
    for i in fuel_procurement_cost_df.columns:
        if '\n' in i:
            fuel_procurement_cost_df = fuel_procurement_cost_df.rename(columns={i:i.replace('\n', '')})

    fuel_procurement_cost_df['燃料費調整単価適用期間'] = fuel_procurement_cost_df['燃料費調整単価適用期間'].interpolate(method='ffill')
    fuel_procurement_cost_df['燃料費調整単価適用期間'] = pd.to_datetime(fuel_procurement_cost_df['燃料費調整単価適用期間'],
                                                             format='%Y年\n%m月').astype(str).apply(lambda x:''.join(x.split('-'))[:6]).astype(int)
    
    col_list=['するめいか_卸売数量計(kg)',
         'いわし_卸売数量計(kg)',
         'ぶり・わらさ_卸売数量計(kg)',
         '冷さけ_卸売数量計(kg)', 
         '塩さけ_卸売数量計(kg)',
         'さけます類_卸売数量計(kg)',
         '全卸売数量計(kg)']
    
    for shift_i in [7, 14, 21, 28]:
        change_col_list = [f'{i}_lag{shift_i}' for i in col_list]
        fish_sell_ach[change_col_list] = fish_sell_ach[col_list].shift(shift_i)
    
    fish_sell_ach['target_date'] = fish_sell_ach['date'].apply(lambda x:int((pd.to_datetime(str(x))+relativedelta(months=1)).strftime('%Y%m%d')))
    fish_sell_ach['年月'] = fish_sell_ach['target_date'].astype(str).str[:6].astype(int)
    
    prediction_df = pd.merge(fish_sell_ach, 
                         oil_price_df,
                         on='年月',
                         how='left')
    
    for kind in fuel_procurement_cost_df['種別'].unique():
        temp_df = fuel_procurement_cost_df.loc[fuel_procurement_cost_df['種別']==kind].drop('種別', axis=1)
        temp_df = temp_df.rename(columns={temp_df.columns[0]:'年月'})
        for i in temp_df.columns:
            if i != '年月':
                temp_df = temp_df.rename(columns={i:f'{i}_{kind}_lag1'})
                temp_df['年月'] = pd.to_datetime(temp_df['年月'], format='%Y%m')
                temp_df['年月'] = temp_df['年月'].apply(lambda x:x+relativedelta(months=1))
                temp_df['年月'] = temp_df['年月'].apply(lambda x:''.join(str(x).split('-'))[:6]).astype(int)
        prediction_df = pd.merge(prediction_df,
                                 temp_df,
                                 on='年月')
    prediction_df = prediction_df.rename(columns={'date':'forecast_point'})
    
    return prediction_df

def prediction_to_dr(oil_price_url, fuel_procurement_cost_url):
    today = datetime.datetime.now()
    last_prediction_result = pd.read_csv('data/prediction_result.csv')
    last_time_fish_arch = pd.read_csv('data/fish_sell_ach.csv')
    if (str(last_prediction_result['forecast_point'].max()) == today.strftime('%Y%m%d'))|(str(last_time_fish_arch['date'].max()) == int(today.strftime('%Y%m%d'))):
        pass

    else:
        start_date = pd.to_datetime(str(last_time_fish_arch['date'].max()))
        end_date = pd.to_datetime(today + relativedelta(days=1))
        use_fish_list = config['use_fish_list']
        temp_sell_ach = get_fish_qty.get_fish_price_data(start_date, end_date, use_fish_list)
        temp_sell_ach['date'] = temp_sell_ach['date'].astype(int)
        if str(temp_sell_ach['date'].max()) != today.strftime('%Y%m%d'):
            pass

        else:
            temp_sell_ach = pd.concat([last_time_fish_arch,
                                       temp_sell_ach.loc[~temp_sell_ach['date'].isin(last_time_fish_arch['date'].unique())]])
            temp_sell_ach.to_csv('data/fish_sell_ach.csv', index=False)
            prediction_df = create_prediction_data(temp_sell_ach, 
                                              oil_price_url,
                                              fuel_procurement_cost_url)
            prediction_df = prediction_df.loc[(prediction_df['forecast_point'].astype(int)>last_prediction_result['forecast_point'].max())
                                              &
                                              (prediction_df['forecast_point'].astype(int)<=int(today.strftime('%Y%m%d')))].reset_index(drop=True)

            display(prediction_df)
            DEPLOYMENT_ID = '640d791796a6a52d92c368a0'
            prediction_df.to_csv('data/temp_prediction.csv', index=False)
            prediction_json = dr_prediction_deployment.main('data/temp_prediction.csv', DEPLOYMENT_ID)

            prediction_result = pd.DataFrame({ 
                                                'target_date':prediction_df['target_date'],
                                                'forecast_point':prediction_df['forecast_point'],
                                                '電気代':pd.json_normalize(prediction_json['data'])['prediction']
                                            })

            prediction_result = pd.merge(prediction_df,
                                 prediction_result,
                                 on=['target_date', 'forecast_point'])

            last_prediction_result = pd.concat([last_prediction_result,
                                                prediction_result])

            last_prediction_result.to_csv('data/prediction_result.csv', index=False)
            
    return last_prediction_result