#!/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_single.csv"
blocklist_log_path = "/home/foo/Logs/"
blocklist_log_filename = "iceshrimp_blocklist_single_updated"

# Link to the Blocklist to be used
url = ("https://codeberg.org/oliphant/blocklists/raw/branch/main/blocklists/_unified_tier0_blocklist.csv")

# Go grab the Blocklist
urllib.request.urlretrieve(url, blocklist_path+blocklist_filename)

# Read the CSV file into a pandas DataFrame
df = pd.read_csv(blocklist_path+blocklist_filename)

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

# 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")