import json
import pandas as pd
from datetime import datetime

from firebase_admin import credentials, auth
import firebase_admin
from firebase_admin import credentials
from firebase_admin import db
from firebase_admin import messaging
import time
import threading
import pandas as pd
import os
import datetime
from zipfile import ZipFile
import json
from numba import jit, cuda
from google.cloud import storage
import os
from firebase_admin import db
import cv2
import scipy.ndimage
from google.oauth2 import service_account
import datetime
import requests
import json
import traceback
from datetime import date
import openpyxl
from openpyxl import Workbook
storage_client = storage.Client.from_service_account_json("servicekey.json"); 

bucket_name = 'farmbase-b2f7e.appspot.com'
cred = credentials.Certificate('servicekey.json')
try:
    firebase_admin.initialize_app(cred, {'databaseURL': 'https://farmbase-b2f7e-31c0c.firebaseio.com/'})
except:
    print('fire running')


from datetime import datetime as dt

# def json_to_excel(data,uuid, field_id,lat,long, excel_file_path):
#     temp_obj={}
#     temp_obj['uuid']=uuid
#     temp_obj['field_id']=field_id
#     temp_obj['lat']=lat
#     temp_obj['long']=long
#     # Create a DataFrame with the specified columns and dates
#     columns = ['uuid','field_id','lat','long','date', 'rainfall_mm', 'avg_temp_c', 'max_temp_c', 'min_temp_c']
#     dates = list(data['ndvi'].keys())
#     formatted_dates = [dt.strptime(date, '%Y%m%d').strftime('%d-%m-%Y') for date in dates]

#     df = pd.DataFrame(index=formatted_dates, columns=columns)

#     # Populate the DataFrame with the values
#     for column in columns:
#         for date in dates:
#             formatted_date = dt.strptime(date, '%Y%m%d').strftime('%d-%m-%Y')
            
#             if column not in ['uuid', 'field_id', 'lat', 'long']:
#                 if column == 'soc':
#                     sum_avg = float(data['ndvi'].get(date, 0)) + float(data['evi'].get(date, 0)) + float(data['savi'].get(date, 0)) + float(data['ndre'].get(date, 0)) + float(data['ndmi'].get(date, 0))
#                     new_soc = min(0.3, round(float(sum_avg/500),3))
#                     df.at[formatted_date, column] = new_soc
#                 else:
#                     df.at[formatted_date, column] = round(float(data[column].get(date, 0))/100,3)

#             else:
#                 df.at[formatted_date, column] = temp_obj[column]

#     # Write the DataFrame to an Excel file
#     df.to_excel(excel_file_path, sheet_name='Sheet1')


def array_to_xlsx(array, filename):
    """
    Convert an array to an XLSX file.

    Parameters:
    array (list of list): The array to be converted into an XLSX file.
    filename (str): The name of the output XLSX file.

    Returns:
    None
    """
    workbook = Workbook()
    sheet = workbook.active
    
    for row in array:
        sheet.append(row)
    
    workbook.save(filename)
    print(f"File saved as {filename}")
# uid = 'ipRHhCOFIDV2pxgg7Nfz1ufZBmV2'
# gavl_list = db.reference('PaidMonitoredFields').child('PMF').child(uid).get(False,False)

filename = "UUID_FieldID_coordinates.xlsx"
df3 = pd.read_excel(filename)

api_key = "dd6e11f5d20e4694bed72932241007"

file_count = 0
field_ids = []
total_done = 0

for index, row in df3.iterrows():
    main_arr = []
    main_arr.append(['date','uuid','field_id','lat','long','rainfall_mm', 'avg_temp_c', 'max_temp_c', 'min_temp_c'])
    field_id = str(row["Field ID"])
    uuid = row["UUID"]
    lat = row["Latitude"]
    long = row["Longitude"]
    file_name = "ifpri_weather/"+ str(uuid) + "_" + str(field_id) + "_FieldID_weatherdata.xlsx" 
    df_weather = pd.read_excel(file_name)

    #columns = ['uuid','field_id','lat','long','date', 'rainfall_mm', 'avg_temp_c', 'max_temp_c', 'min_temp_c']
   # df = pd.DataFrame(index=formatted_dates, columns=columns)
    for index_weather, row_weather in df_weather.iterrows():
        weather_date = row_weather["date"]
        rainfall_mm = row_weather["rainfall_mm"]
        avg_temp_c = row_weather["avg_temp_c"]
        max_temp_c = row_weather["max_temp_c"]
        min_temp_c = row_weather["min_temp_c"]
        main_arr.append([weather_date,uuid, field_id, lat, long,rainfall_mm, avg_temp_c, max_temp_c, min_temp_c])
    #field_ids.append(str(field_id))
    new_filename = "new_ifpri_weather/"+ str(uuid) + "_" + str(field_id) + "_weatherdata.xlsx" 
    array_to_xlsx(main_arr, new_filename)

    print(uuid, field_id)
    total_done = total_done + 1

    # health_obj = db.reference('PaidMonitoredFields').child('PMF').child(uid).child(field_id).child('Health').get()
    # if health_obj is not None:
    #     try:
    #         json_to_excel(health_obj,uuid, field_id,lat,long, ('ifpri_satellite/'+ str(uuid)+ '_' + str(field_id) + '.xlsx'))
    #         total_done = total_done + 1
    #         print(total_done)
    #         #time.sleep(100000)
    #     except:
    #         print(traceback.format_exc())
    # else:
    #     print('no data', uuid, field_id)

print(total_done)
time.sleep(100000)