import requests
from bs4 import BeautifulSoup
import mysql.connector
from datetime import datetime

# Database connection details
db_config = {
    'user': 'admin',
    'password': 'asaxPI1324',
    'host': '192.168.0.41',
    'database': 'TPFundPrices'
}

# URL of the page to scrape
url = "https://www.truepotential.co.uk/fund-administration/#fund-prices"


# Send a GET request to the URL
response = requests.get(url)

# Check if the request was successful
if response.status_code != 200:
    print(f"Failed to retrieve the page. Status code: {response.status_code}")
    exit()

# Parse the HTML content
soup = BeautifulSoup(response.content, "html.parser")

# Find the table containing the fund prices
table = soup.find("table")
if not table:
    print("Failed to find the table containing fund prices.")
    exit()

# List of fund names to filter
target_funds = [
    "GB00BYNYY264",
    "GB00BV9FRD45",
    "GB00BD6DNV57", 
    "GB00BMF19937",
    "GB00BV9FRJ07",
    "GB00BYM58175"
]

# Convert target_funds to lowercase for case-insensitive comparison
target_funds_lower = [fund.lower() for fund in target_funds]

# Extract fund names and prices
funds = {}
for row in table.find_all("tr")[1:]:
    columns = row.find_all("td")
    if len(columns) < 4:
        print(f"Skipping row with insufficient columns: {row}")
        continue
        print(f"Skipping row with insufficient columns: {row}")
        continue
    isin = columns[2].text.strip()
    price = columns[3].text.strip()
    isin_lower = isin.lower()
    if isin_lower in target_funds_lower:
        print(f"Match found: {isin} with price: {price}")
        funds[isin] = price
    else:
        print(f"No match: {isin}")

# Check if any funds were found
if not funds:
    print("No matching funds found.")
    exit()

# Connect to the database
try:
    connection = mysql.connector.connect(**db_config)
    if connection.is_connected():
        print("Successfully connected to the database!")
except mysql.connector.Error as err:
    print(f"Error: {err}")
    connection = None  # Ensure connection is defined even if there's an error

# Check if the connection was successful before proceeding
if connection and connection.is_connected():
    cursor = connection.cursor()
    # Prepare the SQL query
    sql = """
    INSERT INTO fundvalues (Date, tpAG, tpTBG, tpGAG, tpPG, tpSEIG, tpUBSG)
    VALUES (%s, %s, %s, %s, %s, %s, %s)
    """
    # Prepare the data to insert
    date = datetime.now().strftime('%Y-%m-%d')
    data = (
        date,
        funds.get("GB00BYNYY264", None),
        funds.get("GB00BV9FRD45", None),
        funds.get("GB00BD6DNV57", None),
        funds.get("GB00BMF19937", None),
        funds.get("GB00BV9FRJ07", None),
        funds.get("GB00BYM58175", None)
    )
    # Execute the query
    cursor.execute(sql, data)
    connection.commit()
    print("Data inserted successfully")
else:
    print("Failed to connect to the database. Exiting.")

# Close the database connection
if connection and connection.is_connected():
    cursor.close()
    connection.close()
    print("MySQL connection is closed")

try:
    # Your existing script logic here
    print("Script started.")
    # ... (rest of your script)
    print("Script completed successfully!")
except Exception as e:
    print(f"Script failed with error: {e}")