import pandas as pd
import numpy as np
import sqlalchemy as db
import urllib.parse
import requests


def send_sms(details):
        seeker_message = """Hello {} you have successfully been matched with  a job vacancy at {}. Your details have been received and you would be contacted for further information.""".format(details['full_name'], details['firm'])
        seeker_payload = {
                        "username" : "jobsmatch",
                        "password" : "npontu@1match",
                        "source" : "Jobs Match",
                        "destination" : details['seeker_contact'],
                        "message" : seeker_message
                    }
        employer_message = """Hello {}, your Job Posting has successfully been matched with a job seeker with name: {}. Details can be accessed on your portal.""".format(details['name'], details['full_name'])
        employer_payload = {
                        "username" : "jobsmatch",
                        "password" : "npontu@1match",
                        "source" : "Jobs Match",
                        "destination" : details['employer_contact'],
                        "message" : employer_message
                    }



        requests.post('https://deywuro.com/api/sms', json=seeker_payload)
        requests.post('https://deywuro.com/api/sms', json=employer_payload)




def JobMatcher():
    cnx_1 = db.create_engine('mysql+pymysql://' + 'evans' + ':' + urllib.parse.quote('M0nk3Y@D#1uFFY') + '@' + '148.251.89.119' + ':' + '3306' + '/' + 'jobmatch')
    conn_1 = cnx_1.connect().execution_options(stream_results=True)

    seekers = pd.read_sql('select * from JobMatcher_jobseeker', conn_1)
    employers = pd.read_sql('select * from JobMatcher_employer', conn_1)
    conn_1.close()

    seekers = seekers[['full_name', 'work_area', 'jobs_category', 'education', 'contact']]
    seekers = seekers.rename(columns={'contact':'seeker_contact', 'jobs_category':'job_category'})
    employers = employers[['name', 'firm', 'job_category', 'education', 'work_area', 'position', 'contact']]
    employers = employers.rename(columns={'contact':'employer_contact'})
    print(seekers.head())
    print(employers.head())

    merge_df = pd.merge(seekers, employers, on=['job_category', 'work_area', 'education'], how='inner')
    print(merge_df.head())

    for detail in merge_df.to_dict(orient='records'):
        send_sms(detail)