#%%
import pymysql.cursors
import pymysql
import uuid
from PIL import Image, ImageDraw, ImageFont
from textwrap3 import wrap
import pyshorteners
import qrcode
from PIL import Image

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)

img2=Image.open("/var/www/html/talenthub/backend/ads/background.jpg")
img2=img2.resize((1620, 840))
img=img2.copy()
draw = ImageDraw.Draw(img)

font_hiring = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Regular.ttf', 48)
font_jobtitle = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Black.ttf', 68)
font_jobtitle_alt = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Black.ttf', 42)
font_location = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Regular.ttf', 52)
font_applynow = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Regular.ttf', 32)
font_url = ImageFont.truetype('/var/www/html/talenthub/backend/ads/Roboto-Regular.ttf', 72)

s = pyshorteners.Shortener()

# %%
def write_text(coordinate, message, font):
    _, h0 = draw.textsize(str(message[0]), font=font)
    current_h, pad = 10+coordinate[1], 8

    for line in message:
        w, h2 = draw.textsize(str(line), font=font)
        draw.text(((int(- w) / 2 )+coordinate[0], current_h), str(line), font=font, fill=(255,255,255))
        current_h += h2*0.8 + pad

def wrap_txt():
    line = 20

    message=[data['company'].upper()+' IS HIRING']
    write_text([810,line],message,font_hiring)
    line += 80

    if len(data['title']) > 40:        
        message=[data['title'].upper()]
        write_text([810,line],message,font_jobtitle_alt)
        line += 50
    else:
        message=[data['title'].upper()]
        write_text([810,line],message,font_jobtitle)
        line += 80

    if data['location'] == None:
        location = "Remote"
    else:
        location = data['location']

    #message = [data['location'].upper() + ' · ' + data['experience_level'].upper()]
    message = [location.upper()]
    write_text([810,line], message, font_location)
    line += 80

    shortURL = 'https://app.gigminds.com/jobs/' + str(data['id'])

    message = ['TO APPLY SCAN NOW']
    write_text([810,line], message, font_applynow)
    line += 60

    qr = qrcode.QRCode(
        version=1,
        box_size=7,
        border=1)
    qr.add_data(shortURL)
    qr.make(fit=True)
    img3 = qr.make_image(fill='black', back_color='white')
    img.paste(img3, (705, line), mask = img3)
    line += 240


    message=['OR APPLY ONLINE AT']
    write_text([810,line],message,font_applynow)
    line += 25

    
    message=[shortURL]
    write_text([810, line], message, font_url)
    line += 60


    #img.save('qrcode001.png')


    #message=location.upper()
    #write_text([810,240],message,font)

    #message=['EXPERIENCE']
    #write_text([810,340],message,font3)

    #message=[data['experience_level'].upper()]
    #write_text([810,380],message,font)

    #message=['..................................................................................................................................................................................................................................................................................................']
    #write_text([810,460],message,font31)

    ## message=['SUMMARY',*data['summary']]
    ## write_text([600,290],message,font2)

    #message=['APPLY ONLINE']
    #write_text([810,520],message,font1)
    
    ##shortURL=s.tinyurl.short('https://app.gigminds.com/jobs/'+data['uuid'])
    #shortURL = 'https://app.gigminds.com/jobs/' + str(data['id'])
    ##write_text([600,460],message,font21)
    #message=[shortURL]
    #write_text([810, 580], message, font)
    
    ##message=['* * * * *   Powered by Gigminds | www.gigminds.com   * * * * *']
    ##write_text([600,560],message,font31)

#%%
max_W=299
with db.cursor() as cursor:
    sql = "select t.name company, j.uuid, j.location, j.id, COALESCE(j.city, '--') as city, COALESCE(j.state_province, '--') as state_province, COALESCE(j.country, '--') as country, j.title, j.summary, j.type, j.preferred_employment, j.duration, j.experience_level, j.recruiter_id, j.sourcer_id, s.name sourcer_name, s.email sourcer_email from jobs j left join users s on j.sourcer_id = s.id left join tenants t on j.tenant_id = t.id order by id desc limit 15;"
    cursor.execute(sql)
    records = cursor.fetchall()
    for data in records:
        #lines=wrap(data['summary'], 100)
        #if len(lines)>3:
        #    lines[2]=lines[2]+'...'
        #    data['summary']=lines[:5]
        #else:
        #    data['summary']=lines
        img=img2.copy()
        draw = ImageDraw.Draw(img)
        wrap_txt()
        img.save('/var/www/html/talenthub/public/images/jobs/ads/'+data['uuid']+'.png')
        
        with db.cursor() as cursor:
            upsql = "update jobs set ad_img1 = 'https://app.gigminds.com/images/jobs/ads/" + data['uuid'] + ".png' where id = '" + str(data['id']) + "'"
            count = cursor.execute(upsql)
            db.commit()

        # print(record)