#!/usr/bin/env python
# -*- coding: utf-8 -*-

# The url library
import urllib.request

# The pandas library (pip install pandas)
import pandas as pd

# The PostgreSQL library (pip install psycopg2)
import psycopg2

# Local path and filenames
blocklist_path = "/home/foo/path/to/update_iceshrimp_blocklist/"
blocklist_filename = "blocklist_multiple.txt"
blocklist_log_path = "/home/foo/Logs/"
blocklist_log_filename = "iceshrimp_blocklist_multiple_updated"

# Python pathname pattern matching
import glob

# Download all of the *.csv files in 'blocklist.txt' into the current directory
with open(blocklist_path+blocklist_filename) as f:
    for line in f:
        url = line
        blocklist_filename = url.split('/', -1)[-1]
        urllib.request.urlretrieve(url, blocklist_path+blocklist_filename.rstrip('\n'))

# Put all of the *.csv files in the current directory into a single DataFrame
files = glob.glob(blocklist_path+"*.csv")
content = []

# Loop through all the *.csv files
for filename in files:
    df = pd.read_csv(filename, index_col=None)
    print(len(df.index), "rows in", filename)
    content.append(df)

# Convert 'content' to a DataFrame
df = pd.concat(content)

# Print the # of DataFrame rows
print(len(df.index), "rows in the DataFrame")

# Select the first column (column 0) and convert it to a text Series
blocklist_series = df.iloc[:, 0].astype(str)

# Make the Series unique
blocklist_series = pd.unique(blocklist_series)

# Print the # of rows in the Series
print(pd.Series(blocklist_series).count(), "unique rows in the Series")

# Join the text Series into a single string with a comma
blocklist_string = ','.join(blocklist_series)

# Establish a Postgres DB connection
conn = psycopg2.connect(host="127.0.0.1", database="iceshrimp_db", user="iceshrimp_user", password="iceshrimp_db_user_password", port= '5432')

# Create a cursor object using the cursor() method
cursor = conn.cursor()

# Prepare the SQL query to UPDATE the 'blockedHosts' field in the 'meta' table in the database
table_modification = """UPDATE meta SET "blockedHosts" = '{""" + blocklist_string + """}' WHERE id = 'x'"""

# Execute and error-trap
try:
    # Execute the SQL command
    cursor.execute(table_modification)
    print("Executed and UPDATED")

    # Commit the changes to the database
    conn.commit()
    print("UPDATE Committed")

    # Create a log file to show that the UPDATE has successfully completed
    open(blocklist_log_path+blocklist_log_filename, 'w')
    print("UPDATE log file created")

except:
    # Roll back in case of error
    conn.rollback()
    print("UPDATE Failed and rolled back")

# Close the connection
conn.close()
print("Connection CLOSED")