import pyodbc as po
import json
import base64
import time
import shutil
import datetime
from pathlib import Path

with open("./config/config.json") as json_data_file:
    config = json.load(json_data_file)
    server = config['Server']
    database = config['Database']
    username = config['DBUser']
    password = config['DBPassword']
    ServerSIR = config['ServerSIR']
    DatabaseSIR = config['DatabaseSIR']
    UsernameSIR = config['DBUserSIR']
    PasswordSIR = config['DBPasswordSIR']
    ACKPathFiles = config['ACKPathFiles']
    RepositoryArchivoElectronicoOficial = config['RepositoryArchivoElectronicoOficial']

def GetMFile(Referencia):
    MFile=''
    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + ServerSIR
            + ";DATABASE=" + DatabaseSIR
            + ";UID=" + UsernameSIR
            + ";PWD=" + PasswordSIR
        )
        cursor = cnxn.cursor()
        cursor.execute("""SELECT P.sReferencia, p.Archivo, P.Aduana, P.Patente, P.Pedimento, P.[Pedimento Fecha Pago], Peds.nTipoCambio, 
                            P.Clave FROM SIR.Admin.SIR_VT_PedimPagados P 
                            INNER JOIN SIR.SIR.SIR_149_PEDIMENTO AS Peds ON Peds.nIdPedimento149=ID
                            WHERE P.ClienteClave=2096  and p.[Pedimento FechaPago]>=CAST( GETDATE()-2 AS Date ) and sReferencia='23-00253I'""")
        for row in cursor:
            Pedimento = time.strftime("%y", time.localtime())+'-'+row[2]+'0-'+row[3]+'-'+row[4]
            PaymentDate = row[5]
            ExchangeRate = row[6]
            CustomFillingCode = row[7]
            Referencia = row[0]
            MFile = row[1]
            print('Se encontro un registro que conincide con la ocurrence')
             
        DestinyFile = ACKPathFiles+MFile
        print('Pedimento='+Pedimento)
        today = datetime.date.today()
        YEAR = today.year
        shutil.copy2(RepositoryArchivoElectronicoOficial + str(YEAR) + '/' + Referencia + '/' + MFile, DestinyFile)

        path_to_file = DestinyFile
        path = Path(path_to_file)
        if path.is_file():
            with open(DestinyFile, "r") as file:
                MFileBase64 = file.read().encode('utf-8')
                MFileBase64 = base64.b64encode(MFileBase64)

            SidelinePath = GetFilePath(Referencia, 'SIDELINE')
            GlosaPath = GetFilePath(Referencia, 'GLOSA')
            AcknowledgementPath = GetFilePath(Referencia, 'Acknowledgement')
            
            with open(SidelinePath, 'r') as file:
                filedata = file.read()
                filedata = filedata.replace('?Pedimento?', Pedimento)
                filedata = filedata.replace('?ExchangeRate?', str(ExchangeRate))
            with open(SidelinePath, 'w') as file:
                file.write(filedata)

            with open(GlosaPath, 'r') as file:
                filedata = file.read()
                filedata = filedata.replace('?Pedimento?', Pedimento)
                filedata = filedata.replace('?ExchangeRate?', str(ExchangeRate))
            with open(GlosaPath, 'w') as file:
                file.write(filedata)

            with open(AcknowledgementPath, 'r') as file:
                filedata = file.read()
                filedata = filedata.replace('<Document>', '<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">')
                filedata = filedata.replace('?Pedimento?', Pedimento)
                filedata = filedata.replace('?CustomFillingCode?', CustomFillingCode)
                filedata = filedata.replace('?PaymentDate?', str(PaymentDate)[:10])
                filedata = filedata.replace('?ExchangeRate?', str(ExchangeRate))
                filedata = filedata.replace('?ArchivoM?', MFileBase64.decode('utf-8'))

            with open(AcknowledgementPath, 'w') as file:
                file.write(filedata)
        else:
            print('El archivo M no existe, por lo tanto no se pueden procesar los archivos ACK, estan incompletos')
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()


def GetFilePath(Referencia,mode):
    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + server
            + ";DATABASE=" + database
            + ";UID=" + username
            + ";PWD=" + password
        )
        cursor = cnxn.cursor()

        storedProc = """\
            SET NOCOUNT ON;
            DECLARE @RC int;
            EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.ACKs.Generate] @Referencia = ?;
            SELECT @RC AS rc;"""
        params = (Referencia)
        cursor.execute(storedProc, params)
        rc = cursor.fetchval()
        cnxn.commit()
        cursor.execute('SELECT * FROM [Clientes.Amazon.ACKFiles]')
        for row in cursor:
            if row[2] == 'SIDELINE' and 'SIDELINE'==mode:
                FilePath = row[3]
            if row[2] == 'GLOSA' and 'GLOSA'==mode:
                FilePath = row[3]
            if row[2] == 'Acknowledgement' and 'Acknowledgement' == mode:
                FilePath = row[3]
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()
        return FilePath


def CreateACKs(Referencia):
    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + server
            + ";DATABASE=" + database
            + ";UID=" + username
            + ";PWD=" + password
        )
        cursor = cnxn.cursor()

        storedProc = """\
            SET NOCOUNT ON;
            DECLARE @RC int;
            EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.ACKs.Generate] @Referencia = ?;
            SELECT @RC AS rc;"""
        params = (Referencia)
        cursor.execute(storedProc, params)
        rc = cursor.fetchval()
        cnxn.commit()

        cursor.execute('SELECT * FROM [Clientes.Amazon.ACKFiles]')
        for row in cursor:
            if row[2] == 'SIDELINE':
                SidelinePath = row[3]
                SidelineDocumentId = row[4]
                SidelineUniqueReferenceId = row[5]
            if row[2] == 'GLOSA':
                GlosaPath = row[3]
                GlosaDocumentId = row[4]
                GlosaUniqueReferenceId = row[5]
            if row[2] == 'Acknowledgement':
                AcknowledgementPath = row[3]

        with open(SidelinePath, "r") as file:
            SidelineBase64 = file.read().encode('utf-8')
            SidelineBase64 = base64.b64encode(SidelineBase64)
        with open(GlosaPath, "r") as file:
            GlosaBase64 = file.read().encode('utf-8')
            GlosaBase64 = base64.b64encode(GlosaBase64)

        with open(AcknowledgementPath, 'r') as file:
            filedata = file.read()
            filedata = filedata.replace(
                '?SidelineContent?', SidelineBase64.decode('utf-8'))
            filedata = filedata.replace('?SidelineDocId?', SidelineDocumentId)
            filedata = filedata.replace(
                '?SidelineUniqueRef?', SidelineUniqueReferenceId)
        with open(AcknowledgementPath, 'w') as file:
            file.write(filedata)

        with open(AcknowledgementPath, 'r') as file:
            filedata = file.read()
            filedata = filedata.replace(
                '?GlosaContent?', GlosaBase64.decode('utf-8'))
            filedata = filedata.replace('?GlosaDocId?', GlosaDocumentId)
            filedata = filedata.replace(
                '?GlosaUniqueRef?', GlosaUniqueReferenceId)
        with open(AcknowledgementPath, 'w') as file:
            file.write(filedata)

    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()
        return AcknowledgementPath

GetMFile('23-00253I')
CreateACKs('23-00253I')