import pandas as pd
import pymysql.cursors
import pymysql


def get_jobs_resumes_csv():

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


    # sql_select_Query = "select * from defaultdb.jobs"

    sql_select_Query = "select id, title, details, city, state_province from jobs where tenant_id = 6 order by id desc"

    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    print("Total number of rows is: ", cursor.rowcount)

    #print("\nPrinting each record")
    #for row in records:
    #    print("Id = ", row['id'], )
    #    print("title = ", row['title'])
    #    print("details  = ", row['details'])
    #    print("city  = ", row['city'], "\n")
    #    print("state_province  = ", row['state_province'], "\n")


    # convert tuple into dataframe
    j_df = pd.DataFrame(records, columns=['id', 'title', 'details', 'city', 'state_province'])

    # export into csv
    j_df.to_csv("/Users/rberi/Insync/Development/sites/talenthub/backend/ai/resume-to-job-match/jobs_des.csv")

    sql_select_Query = "select r.id as resume_id, r.person_id, p.job_profile as title, r.resume_details, p.city, p.state_province " \
                        "from " \
                        "resumes r, person p where r.person_id = p.id and r.person_id is not null and r.resume_details " \
                        "is " \
                        "not null order by r.id desc"

    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    print("Total number of rows is: ", cursor.rowcount)

    # convert tuple into dataframe
    r_df = pd.DataFrame(records,
                        columns=['resume_id', 'person_id', 'title', 'resume_details', 'city', 'state_province'])

    # export into csv
    r_df.to_csv("/Users/rberi/Insync/Development/sites/talenthub/backend/ai/resume-to-job-match/resumes_des.csv")

    # disconnect from server
    db.close()