# %%
# !pip install requests

# %%
# pip install pygsheets

# %%
import requests
# import json
import pandas as pd
# import threading
# from threading import Timer
# from threading import Thread
import queue
# import time
# from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
import pygsheets
import numpy as np
# from datetime import datetime
# from save_thread_result import ThreadWithResult
# from threading import Thread, Event

prjnamesURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=0'
prjpartsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=1481952020'
prjsectionsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=175272726'

dfNames=pd.DataFrame()
dfParts=pd.DataFrame()
que= queue.Queue()
returnString=''
#FIRST TIME ONLY , DONT CALL UNLESS NEEDED
def AuthorizeGoogleSheets():
  SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive'
  ]
  credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
  spreadsheet_service = build('sheets', 'v4', credentials=credentials)
  drive_service = build('drive', 'v3', credentials=credentials)
  gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json')
  print('ayhagaaa')
  return spreadsheet_service,drive_service,gc

# %% #FIRST TIME ONLY , DONT CALL UNLESS NEEDED
def createSheet():
  spreadsheet_service,drive_service,gc=AuthorizeGoogleSheets()
  #create sheet
  titles=gc.spreadsheet_titles()
  if 'API tables Output' in titles:
    ws=gc.open(str('API tables Output'))
    spreadsheetId=ws.id
  else:
    spreadsheet_details = {
    'properties': {
        'title': 'API tables Output'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()

    spreadsheetId = sheet.get('spreadsheetId')
    permission1 = {
    'type': 'anyone',
    'role': 'writer',
    # 'emailAddress': 'marthe.adr@gmail.com'
    }
    drive_service.permissions().create(fileId=spreadsheetId, body=permission1).execute()

    ws = gc.open_by_key(spreadsheetId)
    projectNamesSheet = ws.worksheet(0)

    projectNamesSheet.title='Project Names'#first sheet
    ws.add_worksheet("Project Parts")  # secondsheet
    ws.add_worksheet("Sections")  # thirdsheet
  print('SPREADHSEET IDDDD=====',spreadsheetId)
  return ws,gc,spreadsheetId


# %% [markdown]
# # **New** API

# %%
def APIValues():
  query={'loginname':'Marthe','password':'marthemain'}
  #token="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiQURSIiwibmJmIjoiMTY4MDg1OTgzMyIsImV4cCI6IjE2ODA5MDMwMzMifQ.-FULhKD_M-cyIdSMMIYDlEo9DoZtc00yIlyXIPuyLZI"
  head={'Content-Type':'application/json'}
  response=requests.post(url="https://console.trevorsadd.co.uk/account/login",json=query,headers=head,verify=False)
  key=response.text

  head={'Content-Type':'application/json;charset=UTF-8',"Authorization":'Bearer {}'.format(key)}
  response=requests.post(url="https://console.trevorsadd.co.uk/project/getprojectslist",json=query,headers=head,verify=False)
  dict1=response.json()
  # print(dict1)
  return dict1

# %% [markdown]
# ### Generate Tables

# %%
def GenerateTables():
 
  dict1=APIValues()

  ##First table should ONLY contain: All project Ids and names
  #Second table should contain ProjId,ProjectPartId,ProjectPart
  #Third table should contain ProjId,ProjectPartId,ProjectSection
  table1=pd.DataFrame(columns= ['ProjectId','ProjectName'])
  table2=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectPart'])
  table3=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectSection'])
  for item in dict1:
      table1 = pd.concat([table1, pd.DataFrame([[item.get('id'),item.get('projectname')]], columns=table1.columns)], ignore_index=True)
      projectPartsList=item.get('projectparts')
      for part in projectPartsList:
        table2=  pd.concat([table2,pd.DataFrame([[item.get('id'),part.get('id'),part.get('name')]], columns=table2.columns)], ignore_index=True)
        sections=part.get('projectsections')
        for section in sections:
          table3=  pd.concat([table3,pd.DataFrame([[item.get('id'),section.get('projectPartId'),section.get('section')]], columns=table3.columns)], ignore_index=True)
  # table1 = pd.concat([table1, pd.DataFrame([['2222','trial1']], columns=table1.columns)], ignore_index=True)
  # table2=  pd.concat([table2,pd.DataFrame([['2222','66','partname']], columns=table2.columns)], ignore_index=True)
  # table3=  pd.concat([table3,pd.DataFrame([['2222','66','newSectionsss']], columns=table3.columns)], ignore_index=True)
  return table1,table2,table3  

# Set Tables to Sheets
def AppendtablestoSheets():
  gc=AuthorizeGoogleSheets()[2]
  # createSheet()
  ws = gc.open_by_key('1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc')
  table1,table2,table3= GenerateTables()
  projectNamesSheet=ws.worksheet_by_title('Project Names')
  projectPartsSheet=ws.worksheet_by_title('Project Parts')
  projectSectionsSheet=ws.worksheet_by_title('Sections')
  projectNamesSheet.clear()
  projectPartsSheet.clear()

  #append tables to google sheets
  table1 = table1.drop_duplicates()
  table2 = table2.drop_duplicates()
  table3 = table3.drop_duplicates()
  projectNamesSheet.set_dataframe(start='A1',df=table1)
  projectPartsSheet.set_dataframe(start='A1',df=table2)
  projectSectionsSheet.clear()
  projectSectionsSheet.set_dataframe(start='A1',df=table3)
  
  # return table1,table2,table3
# %%
#-------------------------------------------------------------------------------CALL THIS FUNCTION TO BEGIN----------------------------------------------------------------------
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
def getPrjNames(url=prjnamesURL):
  #url first sheet of names 
  # Read the CSV data from the Google Sheet URL
  df = pd.read_csv(prjnamesURL)
  return df['ProjectName'].tolist(), df['ProjectId'].tolist()

# %%
def getprjParts(chosenprjid,urlNames=prjnamesURL,urlparts=prjpartsURL):

  # url_1 = urlNames.replace('/edit#gid=', '/export?format=csv&gid=')
  # global dfNames
  # dfNames = pd.read_csv(url_1)
  # chosenprjname=chosenprjname.replace('"', '')
  # chosenprjnameList=chosenprjname.split(' ')
  # for i, row in dfNames.iterrows():
  #   prjnameString=dfNames['ProjectName'].loc[i]
  #   if all(name in prjnameString for name in chosenprjnameList):
  #     projectNameID=dfNames.iloc[i]['ProjectId']
  # projectNameID=dfNames.iloc[np.where(dfNames['ProjectName'].str.startswith(chosenprjnameList[1]))]['ProjectId']  #ProjectName == chosenprjname
  # url_2 = urlparts.replace('/edit#gid=', '/export?format=csv&gid=')
  global dfParts
  dfParts = pd.read_csv(prjpartsURL)
  print(chosenprjid,dfParts)
  dfParts= dfParts.iloc[np.where(dfParts['ProjectId']==int(chosenprjid))]
  parts=dfParts['ProjectPart'].tolist()
  partsIds=dfParts['ProjectPartId'].tolist()
  print(parts)
  print(partsIds)
#Get projectName Id
  
  return parts,partsIds

# %%
def getprjSections(chosenprjid, chosenpartid,urlSection=prjsectionsURL):
  #GetprojectSections
  # url_1 = urlSection.replace('/edit#gid=', '/export?format=csv&gid=')
  dfSections = pd.read_csv(prjsectionsURL)
  # chosenprjname=chosenprjname.replace('"', '')
  # chosenpart=chosenpart.replace('"', '')
  # projectId=''
  # projectPartID=''
  # chosenprjnameList=chosenprjname.split(' ')
  # chosenpartList=chosenpart.split(' ')
  # for i, row in dfNames.iterrows():
  #   prjnameString=dfNames['ProjectName'].loc[i]
  #   if all(name in prjnameString for name in chosenprjnameList):
  #     projectId=dfNames.iloc[i]['ProjectId']
  # # projectId=dfNames.iloc[np.where(dfNames['ProjectName'].astype(str)==chosenprjname)]['ProjectId']  #ProjectName == chosenprjname
  # for i, row in dfParts.iterrows():
  #   prjpartString=dfParts['ProjectPart'].loc[i]
  #   if all(part in prjpartString for part in chosenpartList):
  #     projectPartID=dfParts['ProjectPartId'].loc[i]
  # projectPartID=dfParts.iloc[np.where(dfParts['ProjectPart'].astype(str)==chosenpart)]['ProjectPartId']
  if chosenprjid and chosenpartid:
    dfSections=dfSections.iloc[np.where((dfSections['ProjectId']==int(chosenprjid)) & (dfSections['ProjectPartId']==int(chosenpartid)) ) ]
    sections=dfSections['ProjectSection'].tolist()
    return sections
  else:
    return


# ##############################################################################