#!/usr/bin/env python3
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.style import WD_STYLE_TYPE
from docx.shared import Inches, Pt
from datetime import date
import time
import os
import mysql.connector
import sys
import subprocess
import pwd
import grp

db_config = {
    "host": "localhost",
    "user": "rsghihjbaoh",
    "password": "dD8X/p99JY1w",
    "database": "asexpresstms"
}

def convert_docx_to_pdf(input_docx, output_pdf, parentDir):
    try:
        env_vars = {'HOME': '/var/www/html/tms/public/driversdocs/temp'}
        subprocess.run(['libreoffice', '--headless', '--convert-to', 'pdf', input_docx, '--outdir', parentDir], env=env_vars)
        time.sleep(1)
        os.rename(parentDir+'/temppreview.pdf', output_pdf)
    except Exception as e:
        print(f"Error during conversion: {e}")
        sys.exit()

def lease_pdf(carrier,driver,parentDir):
    today = date.today()
    datetoday = today.strftime("%m/%d/%Y")
    doc = Document()
    doc.styles['Normal'].font.name = "Arial MT"
    doc.styles['Normal'].font.size = Pt(11)
    para = doc.add_paragraph()
    text1 = para.add_run('LEASE AGREEMENT.')
    text1.bold = True
    text1.font.name = 'Arial'
    text1.font.size = Pt(18)
    para.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    para = doc.add_paragraph()
    text2 = para.add_run('Note꞉ This Lease Agreement should be maintained in the Equipment during the term of the Agreement.')
    text2.bold = True
    text2.font.name = 'Arial'
    text2.font.size = Pt(7.5)
    para.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    para = doc.add_paragraph('        I.    I, ' + carrier['companyname'] + ' (Carrier/Registrant) Address꞉ ' + carrier['address'] + ' and ')
    run = para.add_run('_____'+driver['owner']+'___')
    run.underline = True
    run = para.add_run('. (Equipment Owner) are parties to a written Lease Agreement (Agreement), whereby the Equipment Owner has leased to the Carrier certain motor vehicle equipment listed below, owned and controlled by the Equipment Owner, whereby the Equipment Owner is providing the Carrier as operator or operators of the Equipment for the purpose of loading, transporting and unloading freight.\n')
    para = doc.add_paragraph('        II.    The Original Agreement is on file at the Carrier’s General Office. A copy of this Lease Agreement and receipt for the Equipment must be carried on the Equipment as required by 49 CFR §376. Carrier verifies that the Equipment is being operated by the Carrier, pursuant to the terms of the Agreement.\n')
    para = doc.add_paragraph('        III.    Equipment Owner/Equipment Information.')
    para = doc.add_paragraph('Name:')
    run = para.add_run('___'+driver['owner']+'______')
    run.underline = True
    para = doc.add_paragraph('dba:_________________________________________')
    para = doc.add_paragraph('Address:')
    run = para.add_run('__'+driver['address']+'______')
    run.underline = True

    para = doc.add_paragraph('Year:')
    run = para.add_run('__'+str(driver['year'])+'__')
    run.underline = True

    run = para.add_run('Make:')
    run = para.add_run('__'+driver['make']+'__')
    run.underline = True

    run = para.add_run('VIN:__')
    run = para.add_run(driver['vin']+'__\n')
    run.underline = True

    para = doc.add_paragraph(
        '        IV.    Duration of Lease Agreement and Termination. The Lease Agreement shall begin on the date below and shall remain in effect until terminated by either party, giv‑ ing notice to that effect. Notice may be given personally, by mail or by fax at the address or fax number shown in the Lease Agreement.\n')
    table = doc.add_table(rows=1, cols=2)
    para = table.rows[0].cells[0].paragraphs[0]
    run = para.add_run("MOTOR CARRIER/REGISTER").bold=True
    run = para.add_run("\nBy:")
    run = para.add_run().add_picture(parentDir+'/public/driversdocs/temp/carrier_sign.png')
    run = para.add_run("\n"+carrier['companyname']+" (manager)\nDate: " + carrier['date'] + '\nMC #꞉ '+ str(carrier['mc']) +'\nUSDOT #꞉ ' + str(carrier['usdot']))
    para = table.rows[0].cells[1].paragraphs[0]
    para.add_run("EQUIPMENT OWNER").bold=True
    para.add_run("\n\nBy:________________\n\nDate:")
    run = para.add_run('__'+datetoday+'__')
    run.underline = True
    para = doc.add_paragraph()
    text3 = para.add_run('Printed by authority of the State of Illinois. November 2011 — 21 — VSD 683.225.')
    text3.bold = True
    text3.font.size = Pt(6)
    para.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER
    doc.save(parentDir+'/public/driversdocs/temp/temppreview.docx')
    os.chmod(parentDir+'/public/driversdocs/temp/temppreview.docx', 0o755)
    time.sleep(1)
    convert_docx_to_pdf(parentDir+'/public/driversdocs/temp/temppreview.docx', parentDir+"/public/driversdocs/temp/preview.pdf", parentDir+"/public/driversdocs/temp")
    time.sleep(1)
    os.remove(parentDir+"/public/driversdocs/temp/temppreview.docx")

def getTruckInformation():
    cursor = connection.cursor()
    select_query = "SELECT number, make, year, vin, lease_name, lease_address FROM trucks LIMIT 1"
    cursor.execute(select_query)
    res = cursor.fetchone()
    truck = {
        'make': res[1],
        'year': res[2],
        'vin': res[3].upper(),
        'owner': res[4],
        'address': res[5],
    }
    cursor.close()
    return truck

def getCarrierInfo():
    cursor = connection.cursor()
    select_query = "SELECT iname, colvalue FROM settings WHERE type = 'fakecompany'"
    cursor.execute(select_query)
    companyRes = cursor.fetchall()
    company = {
        'companyname': companyRes[0][1],
        'address': companyRes[1][1],
        'date': companyRes[2][1],
        'mc': companyRes[3][1],
        'usdot': companyRes[4][1],
        }
    cursor.close()
    return company

parentDir = '/var/www/html/tms'
connection = mysql.connector.connect(**db_config)
if os.path.isfile(parentDir + '/public/driversdocs/temp/preview.pdf'):
    os.remove(parentDir + '/public/driversdocs/temp/preview.pdf')
try:
    truck = getTruckInformation()
    carrier = getCarrierInfo()
    if carrier:
        if truck:
            lease_pdf(carrier, truck, parentDir)

    connection.close()
    sys.exit()
except Exception as e:
    print(f"An exception occurred: {e}")
finally:
    connection.close()
    sys.exit()