# %%
# !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'
global table1
global table2
global table3

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


# Set Tables to Sheets
def AppendtablestoSheets():
  gc=AuthorizeGoogleSheets()[2]
  # createSheet()
  ws = gc.open_by_key('1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc')
  global table1
  global table2
  global table3
  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
def GenerateTables():
    """
    Generate tables and sort projects (table1) by the newest section creation date.
    """
    dict1 = APIValues()

    # Collect data for each table
    table1_data=[]
    for item in dict1:
       if not item.get('hide'):
            table1_data.append([ item.get('id'), item.get('projectname')])

    table2_data = [
        [item.get('id'), part.get('id'), part.get('name')]
        for item in dict1
        for part in item.get('projectparts', [])
    ]

    table3_data = [
        [item.get('id'), part.get('id'), section.get('section')]
        for item in dict1
        for part in item.get('projectparts', [])
        for section in part.get('projectsections', [])
    ]

    # Collect latest 'createdon' timestamps per project
    project_createdon = {}
    for item in dict1:
        project_id = item.get('id')
        created_dates = [
            section.get('createdon')
            for part in item.get('projectparts', [])
            for section in part.get('projectsections', [])
            if section.get('createdon')
        ]
        if created_dates:
            created_dates = pd.to_datetime(created_dates)
            project_createdon[project_id] = max(created_dates)

    # Create table1 and add 'CreatedOn' for sorting
    table1 = pd.DataFrame(table1_data, columns=['ProjectId', 'ProjectName'])
    table1['CreatedOn'] = table1['ProjectId'].map(project_createdon)

    # Sort by newest created date
    table1.sort_values(by='CreatedOn', ascending=False, inplace=True)

    # Drop 'CreatedOn' column if not needed
    table1.drop(columns='CreatedOn', inplace=True)

    # Create other tables
    global table2, table3
    table2 = pd.DataFrame(table2_data, columns=['ProjectId', 'ProjectPartId', 'ProjectPart'])
    table3 = pd.DataFrame(table3_data, columns=['ProjectId', 'ProjectPartId', 'ProjectSection'])

    return table1, table2, table3


table1,table2,table3= GenerateTables()
def getPrjNames(progress_callback=None):
    """
    Returns a list of project names and IDs.
    """
    if progress_callback:
        progress_callback(40)
    global table1
    if progress_callback:
        progress_callback(60)
    return table1['ProjectName'].tolist(), table1['ProjectId'].tolist()


def getprjParts(chosenprjid):
    """
    Returns parts and their IDs for a given project ID.
    """
    global table2
    df_parts = table2[table2['ProjectId'] == int(chosenprjid)]
    return df_parts['ProjectPart'].tolist(), df_parts['ProjectPartId'].tolist()


def getprjSections(chosenprjid, chosenpartid):
    """
    Returns sections for a given project ID and part ID.
    """
    global table3
    if chosenprjid and chosenpartid:
        df_sections = table3[
            (table3['ProjectId'] == int(chosenprjid)) &
            (table3['ProjectPartId'] == int(chosenpartid))
        ]
        return df_sections['ProjectSection'].tolist()
    return None


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