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

# ------------------------
# DB CONFIG
# ------------------------
db = mysql.connector.connect(
    host="192.168.0.41",
    user="mawdz09",
    password="asaxPI1324",
    database="UKStats"
)
cursor = db.cursor(dictionary=True)

# ------------------------
# UPDATE HELPER
# ------------------------
def update_indicator(name, value, unit, source):
    cursor.execute(
        "SELECT value FROM core_indicators WHERE indicator=%s",
        (name,)
    )
    row = cursor.fetchone()

    if row and row["value"] == value:
        return  # no change

    prev = row["value"] if row else None

    cursor.execute("""
        INSERT INTO core_indicators (indicator, value, prev_value, unit, last_updated, source)
        VALUES (%s,%s,%s,%s,%s,%s)
        ON DUPLICATE KEY UPDATE
            prev_value = value,
            value = VALUES(value),
            unit = VALUES(unit),
            last_updated = VALUES(last_updated),
            source = VALUES(source)
    """, (name, value, prev, unit, date.today(), source))

    db.commit()


# ------------------------
# UK BASE RATE (BoE)
# ------------------------
def scrape_base_rate():
    url = (
        "https://www.bankofengland.co.uk/boeapps/database/"
        "_iadb-fromshowcolumns.asp?Travel=NIxIRx&FromSeries=IUDBEDR&CSVF=TN"
    )

    response = requests.get(url, timeout=15)
    lines = response.text.splitlines()

    # Find last numeric rate in CSV
    for line in reversed(lines):
        parts = line.split(",")
        if len(parts) >= 2:
            try:
                rate = float(parts[1])
                update_indicator("BaseRate", str(rate), "%", "Bank of England")
                return
            except ValueError:
                continue

# ------------------------
# CPI & RPI (ONS – simple v1)
# ------------------------
def scrape_cpi_rpi():
    url = "https://www.ons.gov.uk/economy/inflationandpriceindices"
    text = BeautifulSoup(requests.get(url, timeout=15).text, "html.parser").get_text()

    if "CPI" in text:
        cpi = text.split("CPI")[1].split("%")[0].split()[-1]
        update_indicator("CPI", cpi, "%", "ONS")

    if "RPI" in text:
        rpi = text.split("RPI")[1].split("%")[0].split()[-1]
        update_indicator("RPI", rpi, "%", "ONS")

# ------------------------
# Unemployment (ONS – simple v1)
# ---------------------------
def scrape_unemployment():
    url = "https://www.ons.gov.uk/employmentandlabourmarket/peopleinwork/employmentandemployeetypes/bulletins/uklabourmarket/latest"
    r = requests.get(url, timeout=20)

    print("Unemployment page status:", r.status_code)

    if r.status_code != 200:
        return

    text = BeautifulSoup(r.text, "html.parser").get_text(" ").lower()

    # DEBUG: show nearby context
    if "unemployment rate" in text:
        idx = text.find("unemployment rate")
        print("Context:", text[idx:idx+120])
    else:
        print("Phrase 'unemployment rate' not found")

    import re
    match = re.search(r"unemployment rate[^%]{0,80}([0-9]+\.[0-9])%", text)

    if match:
        rate = match.group(1)
        print("Parsed unemployment:", rate)

        update_indicator(
            "Unemployment",
            rate,
            "%",
            "ONS Labour Market"
        )
    else:
        print("Unemployment regex failed")



# ------------------------
# Average earnings (ONS – simple v1)
# ------------------------=-

def scrape_earnings():
    url = (
        "https://www.ons.gov.uk/"
        "employmentandlabourmarket/peopleinwork/"
        "earningsandworkinghours/bulletins/"
        "averageweeklyearningsingreatbritain/latest/print/"
    )

    headers = {
        "User-Agent": "Mozilla/5.0",
        "Accept-Language": "en-GB,en;q=0.9"
    }

    r = requests.get(url, headers=headers, timeout=20)

    if r.status_code != 200:
        print("Earnings fetch failed:", r.status_code)
        return

    text = BeautifulSoup(r.text, "html.parser").get_text(" ").lower()

    import re
    match = re.search(
        r"(regular pay|average weekly earnings)[^%]{0,120}([0-9]+\.[0-9])%",
        text
    )

    if match:
        rate = match.group(2)
        update_indicator(
            "EarningsGrowth",
            rate,
            "%",
            "ONS Earnings"
        )
    else:
        print("Earnings regex failed")



# ------------------------
# RUN
# ------------------------
try:
    scrape_base_rate()      # manual, but harmless to keep
    scrape_cpi_rpi()
    scrape_unemployment()
    scrape_earnings()
except Exception as e:
    print("UK indicator scrape failed:", e)

finally:
    cursor.close()
    db.close()