#!/usr/bin/env python3
"""
update_rlp_price.py
--------------------
DEPRECATED (2026-07-12): Royal London's publish timing for this fund turned
out to be too inconsistent to rely on for an automated nightly pull (some
days early, some days late, no fixed slot) - reverted to fully manual entry
via the dashboard form (date/bid/offer/fund value -> save_nav.php). REMOVE
THE CRONTAB ENTRY that calls this script - it will still run and write
correct data if left in place, but there's no longer a form-driven reason
to keep it going and it'll just create confusing duplicate-looking rows
next to manual entries.

Nightly price feed for the "RLP Governed Portfolio Growth" widget on the
homepage (see /main/index.php + scripts.js loadRLP()/saveRLP()).

WHY THIS SCRIPT (and not the older scrape_rlp_nav.py / testscrape_rlp_nav.py):
  - Those two scraped markets.ft.com and wrote to a table called `nav_prices`.
  - The live widget reads from a *different* table, `rlp_nav`, via get_nav.php.
  - So even a working FT scrape would never have shown up on the dashboard.
  - FT also only exposes a single NAV price, not the bid/offer split this
    fund actually has. This script instead reads the FundsLibrary widget
    that Royal London's own fund-prices page embeds for this fund (SEDOL
    BMXYXW5 / ISIN GB00BMXYXW53), which does give Bid Price + Offer Price.
  - That page is a JS-rendered app (confirmed - fetching it with plain
    requests/curl returns empty table cells), so we drive a real headless
    browser (Playwright) rather than BeautifulSoup-scraping raw HTML.
  - It posts straight to save_nav.php - the same endpoint the manual "Save"
    button on the dashboard uses - so there is exactly one code path that
    writes to rlp_nav, and the DB credentials only live in one place.

PREREQUISITES (one-off, on the server that will run the cron job):
    pip3 install playwright requests
    python3 -m playwright install --with-deps chromium

USAGE:
    python3 update_rlp_price.py
  Exits non-zero and logs an error if it can't find a Bid Price - it will
  NOT write a guessed/blank value to the database.

CRON (Linux, weekdays only - fund prices don't move on weekends):
    30 18 * * 1-5  /usr/bin/python3 /path/to/update_rlp_price.py >> /var/log/rlp_nav.log 2>&1
"""

import re
import sys
from datetime import datetime

import requests
from playwright.sync_api import sync_playwright

FUND_URL = "https://www.fundslibrary.co.uk/Clients/RoyalLondon/?id=fc04973f-e44a-4822-9ace-61385253fb20"

# Hit the real HTTPS domain directly - going via http://127.0.0.1 causes a
# redirect to https://mawdz09.uk, and that redirect downgrades POST to GET,
# silently dropping the form fields (looks identical to "Invalid bid price").
SAVE_ENDPOINT = "https://mawdz09.uk/rlp/save_nav.php"

MIN_SANE_PENCE = 50
MAX_SANE_PENCE = 500


def log(msg):
    line = f"{datetime.now().isoformat(timespec='seconds')} - {msg}"
    print(line)
    try:
        with open("/var/log/rlp_nav.log", "a") as f:
            f.write(line + "\n")
    except OSError:
        pass  # no write access to /var/log - stdout redirect from cron still captures it


def to_pence(text):
    """'145.00p' -> 145.0, '1.4500' -> 145.0, '£1.45' -> 145.0"""
    if not text:
        return None
    cleaned = re.sub(r"[^\d.]", "", text)
    if not cleaned:
        return None
    val = float(cleaned)
    # Funds are sometimes quoted in pounds (1.4500) rather than pence (145.00p)
    return round(val * 100, 4) if val < 10 else round(val, 4)


def parse_price_date(text):
    text = (text or "").strip()
    for fmt in ("%d/%m/%Y", "%d %b %Y", "%d %B %Y", "%Y-%m-%d"):
        try:
            return datetime.strptime(text, fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    return datetime.today().strftime("%Y-%m-%d")


def scrape_fund_page():
    """Renders the FundsLibrary widget and pulls out its label/value table rows."""
    with sync_playwright() as p:
        browser = p.chromium.launch()
        page = browser.new_page()
        page.goto(FUND_URL, wait_until="networkidle", timeout=30000)
        # Give the Angular/React app a moment to finish populating the table
        # after the network goes idle.
        page.wait_for_timeout(2500)

        data = {}
        for row in page.query_selector_all("table tr"):
            cells = row.query_selector_all("td")
            if len(cells) >= 2:
                label = cells[0].inner_text().strip()
                value = cells[1].inner_text().strip()
                if label and value and label not in data:
                    data[label] = value

        browser.close()
        return data


def find_value(data, *keywords):
    """Case-insensitive lookup: first table row whose label contains any of the keywords."""
    for label, value in data.items():
        low = label.lower()
        if any(kw in low for kw in keywords):
            return label, value
    return None, None


def main():
    try:
        data = scrape_fund_page()
    except Exception as e:
        log(f"ERROR: page scrape failed: {e}")
        sys.exit(1)

    if not data:
        log("ERROR: scraped zero table rows from the page - it may not have finished "
            "rendering, or the page structure has changed entirely.")
        sys.exit(1)

    bid_label, bid_raw = find_value(data, "bid")
    offer_label, offer_raw = find_value(data, "offer")
    date_label, date_raw = find_value(data, "price date", "valuation date")

    bid = to_pence(bid_raw)
    offer = to_pence(offer_raw)
    price_date = parse_price_date(date_raw)

    # Always log what was actually found, before any validation - this is the
    # bit that tells us whether the scrape or the sanity check is the problem.
    log(f"DEBUG scraped rows: {list(data.items())[:20]}")
    log(f"DEBUG bid: label='{bid_label}' raw='{bid_raw}' parsed={bid} | "
        f"offer: label='{offer_label}' raw='{offer_raw}' parsed={offer} | "
        f"date: label='{date_label}' raw='{date_raw}' parsed={price_date}")

    if not bid or not (MIN_SANE_PENCE <= bid <= MAX_SANE_PENCE):
        log(f"ERROR: no sane Bid Price found (raw='{bid_raw}', parsed={bid}). "
            f"Not writing to DB. Page may have changed its layout - see DEBUG line above "
            f"for every row this scrape actually found.")
        sys.exit(1)

    payload = {"bid_pence": bid, "price_date": price_date}
    if offer and MIN_SANE_PENCE <= offer <= MAX_SANE_PENCE:
        payload["offer_pence"] = offer

    try:
        resp = requests.post(SAVE_ENDPOINT, data=payload, timeout=15)
        if resp.history:
            log(f"WARNING: request was redirected ({SAVE_ENDPOINT} -> {resp.url}) - "
                f"a redirect can silently downgrade POST to GET and drop the form "
                f"fields entirely, which looks identical to an 'Invalid bid price' "
                f"error. Point SAVE_ENDPOINT at the final URL directly to rule this out.")
        result = resp.json()
    except Exception as e:
        log(f"ERROR: could not reach save_nav.php: {e}")
        sys.exit(1)

    if result.get("success"):
        log(f"SAVED bid={bid}p offer={offer}p date={price_date}")
    else:
        log(f"ERROR from save_nav.php: {result.get('error')}")
        sys.exit(1)


if __name__ == "__main__":
    main()