text: # -*- coding: utf-8 -*-
  """
  Created on Tue May 10 11:37:46 2022
  
  @author: tobia
  """
  import boto3 as bt
  import os
  import pandas as pd
  from io import StringIO
  from datetime import datetime
  import requests
  import json
  from datetime import timedelta
  
  
  def lambda_handler(event, context):
          
  #Load data from s3
      client = bt.client(
          's3',
          aws_access_key_id= os.getenv("ACCESS_KEY"),
          aws_secret_access_key= os.getenv("SECRET_KEY")
          )
      
      #Load data
      cleaned_data =client.get_object(Bucket="civil-service-jobs", Key="data/cleaned_data/cleaned_data.csv")
      body = cleaned_data['Body']
      csv_string = body.read().decode('utf-8')
      cleaned_data_df = pd.read_csv(StringIO(csv_string))
      
      key_words_data =client.get_object(Bucket="civil-service-jobs", Key="data/key_words_data/key_words_data.csv")
      body = key_words_data['Body']
      csv_string = body.read().decode('utf-8')
      key_words_df = pd.read_csv(StringIO(csv_string))
      
      
      key_words_context =client.get_object(Bucket="civil-service-jobs", Key="data/key_words_context/key_words_context.csv")
      body = key_words_context['Body']
      csv_string = body.read().decode('utf-8')
      key_words_context_df = pd.read_csv(StringIO(csv_string))
      
      #Merge Keywords
      key_words_all = pd.merge(key_words_df, key_words_context_df, on = "label")
      key_words_grouped = key_words_all.groupby(by = ['job_ref', 'Cause area'], as_index=False).sum()
      
      
      #Filter based on rules (date and key words or other system)
      big_key_words = key_words_grouped[key_words_grouped['Strength of association 1-9 (currently subjective)']>9]
      jobs_with_key_words = cleaned_data_df[cleaned_data_df['job_ref'].isin(list(big_key_words['job_ref']))]
      jobs_with_key_words['closingdate'] =  pd.to_datetime(jobs_with_key_words['closingdate']) 
      current_jobs = jobs_with_key_words[jobs_with_key_words['closingdate'] >= datetime.now()]
      current_jobs['closingdate'] = current_jobs['closingdate'].dt.strftime('%d-%m-%Y')
      current_jobs['job_ref'] = current_jobs['job_ref'].astype(str)
      
      
      base_key = os.getenv("BASE_KEY") 
      api_key =   os.getenv("AIRTABLE_KEY") 
      table_name = 'Civil Service Jobs'
      
      url = "https://api.airtable.com/v0/" + base_key + "/" + table_name
      headers = {"Authorization": "Bearer " + api_key}
      
      params = ()
      existing_job_refs =[]
      run = True
      
      while run is True:
          response = requests.get(url, params=params, headers=headers)
          airtable_data = response.json()
          existing_job_refs += [item.get('job_ref') for item in [item.get('fields') for item in airtable_data['records']]]
          if'offset' in airtable_data:
              run = True
              params = (('offset', airtable_data['offset']),)
          else:
              run = False
      
          
      #Filter to new jobs data
      newdata = current_jobs[~current_jobs['job_ref'].isin(existing_job_refs)]
      
      #Data converted to upload format
      current_jobs_dict = newdata[['job_ref', 'closingdate', 'grade', 'link','department', 'title', 'Type of role']].to_dict('index')
      
      #Get already uploaded ids
      
      #Add to Airtable
  
      upload_headers = {"Authorization" : "Bearer " + api_key,
       "Content-Type" : "application/json"}
      for record_id, upload_data in current_jobs_dict.items():
          job_ref = upload_data['job_ref']
          policy_areas = list(big_key_words[big_key_words['job_ref'].astype(str) == job_ref]['Cause area'])
          words = key_words_all[key_words_all['job_ref'].astype(str) == job_ref][['label','Strength of association 1-9 (currently subjective)' ]]
          words_string = ", ".join(list(words['label'].map(str) + ":" + words['Strength of association 1-9 (currently subjective)'].map(str)))
          upload_data['Key words'] = words_string
          upload_data['Status'] = "New"
          upload_data['Policy area tags'] = policy_areas
          upload_dict = {"records" : [{"fields" : upload_data}]}
          upload_json = json.dumps(upload_dict)
          response = requests.post(url, data=upload_json, headers=upload_headers)
          print(response) 
         
          
      
  