import base64
import requests
import json
import os
from os import path
import shutil
import glob
import pymysql.cursors
import pymysql
import uuid
import csv

db = pymysql.connect (host="cldy-hub-db-prod-do-user-1524670-0.a.db.ondigitalocean.com",
                     user="doadmin",
                     passwd="jmbly6e4obtma5z0",
                     db="defaultdb",
                     port=25060,
                     charset='utf8mb4',
                     cursorclass=pymysql.cursors.DictCursor)

def getData(query):
    with db.cursor() as cursor:
        cursor.execute(query)
        return cursor

def getOrgId(name, website, industry, city, state, country, linkedin):
    sql = "select `id` from organization where lower(name) = lower('" + name + "') limit 1"
    rowcount = getData(sql).rowcount

    if rowcount > 0:
        for record in getData(sql):
            return record["id"]
    else:
        with db.cursor() as cursor:
            sql = "insert into `organization` (`uuid`, `type`, `source`, `name`, `industry`, `legal_name`, `dba`, `city`, `state_province`, `country`, `linkedin_url`, `website`) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (str(uuid.uuid1()), 'Hiring Manager',  'LinkedIn', name, industry, name, name, city, state, country, linkedin, website))
            o_id = cursor.lastrowid
        db.commit()
        return o_id



def getPersonId(org_id, org_name, first_name, last_name, title, email, city, state, country, linkedin_url):
    sql = "select `id` from person where lower(email) = lower('" + email + "') limit 1"
    rowcount = getData(sql).rowcount

    if rowcount > 0:
        for record in getData(sql):
            return record["id"]
    else:
        with db.cursor() as cursor:
            sql = "insert into `person` (`organization`, `org_id`, `uuid`, `type`, `source`, `name`, `first_name`, `last_name`, `job_profile`, `email`, `city`, `state_province`, `country`, `linkedin_url`) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (org_name, org_id, str(uuid.uuid1()), 'Hiring Manager',  'LinkedIn', first_name + ' ' + last_name, first_name, last_name, title, email, city, state, country, linkedin_url))
            o_id = cursor.lastrowid
        db.commit()
        return o_id



with open('lead.csv', 'r') as hotlist:
    reader = csv.reader(hotlist)
    for row in reader:
        c_fname = row[1]
        c_lname = row[2]
        c_title = row[3]
        c_email = row[4]
        c_city = row[5]
        c_state = row[6]
        c_country = row[7]
        c_linkedin_url = row[8]
        o_name = row[9]
        o_website = row[10]
        o_industry = row[11]
        o_city = row[12]
        o_state = row[13]
        o_country = row[14]
        o_linkedin_url = row[15]

        if o_name != '':
            o_id = getOrgId(o_name, o_website, o_industry, o_city, o_state, o_country, o_linkedin_url)
        else:
            o_id = ''

        if c_email != '':
            p_id = getPersonId(o_id, o_name, c_fname, c_lname, c_title, c_email, c_city, c_state, c_country, c_linkedin_url)
        else:
            p_id = ''
        
        print (o_id, p_id, c_email)