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 = "https://www.truepotential.co.uk/fund-administration/#fund-prices"


def get_active_funds(cursor):
    """Load the funds we currently track from the DB instead of a hardcoded list.
    Returns dict: isin_lower -> fund_id
    """
    cursor.execute("SELECT fund_id, isin FROM isa_funds WHERE active = 1")
    return {isin.lower(): fund_id for fund_id, isin in cursor.fetchall()}


def scrape_prices():
    response = requests.get(URL)
    if response.status_code != 200:
        raise RuntimeError(f"Failed to retrieve the page. Status code: {response.status_code}")

    soup = BeautifulSoup(response.content, "html.parser")
    table = soup.find("table")
    if not table:
        raise RuntimeError("Failed to find the table containing fund prices.")

    prices_by_isin = {}
    for row in table.find_all("tr")[1:]:
        columns = row.find_all("td")
        if len(columns) < 4:
            continue
        fund_name = columns[2].text.strip()
        fund_price = columns[3].text.strip()
        fund_name_lower = fund_name.lower()
        if "inc" in fund_name_lower:
            # skip income share classes, same as before
            continue
        prices_by_isin[fund_name_lower] = fund_price

    return prices_by_isin


def main():
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()

    active_funds = get_active_funds(cursor)
    if not active_funds:
        print("No active funds found in isa_funds -- check the table before running this.")
        cursor.close()
        connection.close()
        return

    scraped = scrape_prices()

    today = datetime.now().strftime('%Y-%m-%d')
    matched = 0
    for isin_lower, fund_id in active_funds.items():
        # scraped keys are the raw text from column[2], match on ISIN substring
        price = None
        for name_lower, p in scraped.items():
            if isin_lower in name_lower:
                price = p
                break
        if price is None:
            print(f"No price found for ISIN {isin_lower} (fund_id {fund_id})")
            continue

        try:
            price_val = float(price.replace(',', ''))
        except ValueError:
            print(f"Could not parse price '{price}' for fund_id {fund_id}")
            continue

        cursor.execute(
            """
            INSERT INTO isa_fund_prices (fund_id, price_date, price)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE price = VALUES(price)
            """,
            (fund_id, today, price_val)
        )
        matched += 1
        print(f"Stored fund_id {fund_id} ({isin_lower}): {price_val}")

    connection.commit()
    print(f"Done. {matched}/{len(active_funds)} active funds updated for {today}.")

    cursor.close()
    connection.close()


if __name__ == "__main__":
    try:
        main()
    except Exception as e:
        print(f"Script failed with error: {e}")