import subprocess
import sys

# This script installs the required packages for the project.
# It uses pip to install 'pandas', 'lxml', and 'python-dateutil
def install(package):
    subprocess.check_call([sys.executable, "-m", "pip", "install", package])

# If you need to install a specific version, you can modify the package string.
install('pandas')
install('lxml')
install('python-dateutil')

# Importing the installed packages to ensure they are available   
import pandas as pd

# Importing the VIES API module
# Ensure that the viesapi module is installed in your environment
from viesapi import *

# If you need to install the viesapi module, uncomment the following line:
from time import sleep

# Create client object and establish connection to the test system
viesapi = VIESAPIClient() # Paid Version - VIESAPIClient('id', 'key') - if you need to authenticate

# Set the EU VAT number to be validated
euvat = 'PL7171642051'

# Get current account status
account = viesapi.get_account_status()

if account:
    print(account)
else:
    print('Error: ' + viesapi.get_last_error() + ' (code: ' + str(viesapi.get_last_error_code()) + ')')

# Get VIES data from VIES system
vies = viesapi.get_vies_data(euvat)

if vies:
    print(vies)
else:
    print('Error: ' + viesapi.get_last_error() + ' (code: ' + str(viesapi.get_last_error_code()) + ')')

# Extract relevant fields from the single VIESData object
if vies:
    vies_data = [{
        'Country Code': vies.country_code,
        'VAT Number': vies.vat_number,
        'Trader Name': vies.trader_name,
        'Trader Address': vies.trader_address,
        'Date': vies.date.strftime('%Y-%m-%d-%H:%M:%S'),
        'Valid': vies.valid
    }]
else:
    vies_data = [{
        'Country Code': None,
        'VAT Number': None,
        'Trader Name': None,
        'Trader Address': None,
        'Date': None,
        'Valid': False
    }]

# Convert the list of dictionaries to a DataFrame for easier manipulation and display
vies_df = pd.DataFrame(vies_data)

# Display the DataFrame
vies_df.head()

# Create a DataFrame with EU VAT numbers and their corresponding countries
# This DataFrame can be used to validate multiple VAT numbers in a batch process

eu_df = pd.DataFrame({
    'Country Code': ['PL','PL','PL','PL','DE','FR','DK',
               'ES','PT','CZ','IT','BG','HU','RO',
               'EL','FI','HR','LT','LV','SK','NL',
               'BE','AT','EE','IE','LU','MT',
               'SE','SI'],
    'Number': ['7272445205','5213003700','5252242171','7171642051','327990207','10402571889','56314210',
               '38076731R','501613897','7710043187','06903461215','202211464','29312757','14388698',
               '801116623','23064613','79147056526','100005828314','40203202898','2022210311','863726392B01',
               '0835221567','U74581419','100110874','8251135U','22108711','26572515',
               '556037867001','51510847']
})

# The first 5 rows of the DataFrame
eu_df.head()

# Add a new column 'VAT' that combines 'Country Code' and 'Number'
eu_df['VAT'] = eu_df['Country Code'] + eu_df['Number']


# Display the updated DataFrame with the new 'VAT' column
eu_df.head()

# Extract the VAT numbers into a list
eu_numbers = eu_df['VAT'].tolist()

# Display the list of EU VAT numbers
eu_numbers

# Get VIES data for multiple VAT numbers in a batch process
# This will return a token that can be used to check the status of the batch process
token = viesapi.get_vies_data_async(eu_numbers)

if token:
    print('Batch token: ' + token)
else:
    print('Error: ' + viesapi.get_last_error() + ' (code: ' + str(viesapi.get_last_error_code()) + ')')

# Check batch result and download data (at production it usually takes 2-3 min for result to be ready)
while True:
    result = viesapi.get_vies_data_async_result(token)
    if result:
        break

    if viesapi.get_last_error_code() != Error.BATCH_PROCESSING:
        print('Error: ' + viesapi.get_last_error() + ' (code: ' + str(viesapi.get_last_error_code()) + ')')
        break

    print('Batch is still processing, waiting...')
    sleep(10)

# Batch result is ready
print(result)

eu_vat_data = []

for item in result.numbers:
    eu_vat_data.append({
        'Country Code': item.country_code,
        'VAT Number': item.vat_number,
        'Trader Name': item.trader_name,
        'Trader Address': item.trader_address,
        'Date': item.date.strftime('%Y-%m-%d-%H:%M:%S') if item.date else None,
        'Valid': item.valid
    })

# Create DataFrame
eu_df_final = pd.DataFrame(eu_vat_data)

# Display the DataFrame
eu_df_final