"""
update_price_data_v1.py — extend the 15M price archives through today
=====================================================================
RUN ON THE VPS (needs MT5 running & logged in):
  python src\\research\\update_price_data_v1.py

TIMEZONE — why no conversion is needed:
  Your archives are in EET ('Time (EET)' convention). MT5's Python API
  returns bars in BROKER SERVER TIME, and IC Markets server time IS
  GMT+2/GMT+3 (EET/EEST) — the same convention your archives use.
  This is also exactly how live_signal_monitor already treats MT5 bars
  (pd.to_datetime(unit='s') with no shift). So: fetch from MT5, append
  raw, no timezone math. Do NOT append UTC data from other sources
  (FRED-side scripts, web downloads) without shifting it +2h/+3h first —
  fetching from MT5 avoids that problem entirely.

  Minor caveat: broker DST switches on the US schedule, EU zones switch
  ~1-2 weeks earlier. Up to ~1 week/year of 1-hour skew at the seams —
  same skew your existing archive already contains. Ignored.

WHAT IT DOES per pair:
  1. Backs up the CSV to <name>.bak_YYYYMMDD
  2. Reads the last timestamp + detects the datetime format & columns
  3. Pulls 15M bars from MT5 from that timestamp to now
  4. Appends only new rows, in the same column order & date format
"""
import sys, shutil
from pathlib import Path
from datetime import datetime, timedelta

import pandas as pd

def resolve_base():
    for b in [Path(r"C:\Users\Administrator\OneDrive\fx_macro_intraday"),
              Path(r"C:\Users\paul_\OneDrive\fx_macro_intraday"),
              Path(__file__).resolve().parents[2]]:
        if b.exists(): return b
    sys.exit("base not found")
BASE = resolve_base()

FILES = {
    "EURUSD": BASE/"data"/"raw"/"prices"/"EURUSD"/"EURUSD_15M_2003_2026.csv",
    "USDJPY": BASE/"data"/"raw"/"prices"/"USDJPY"/"USDJPY_15M_2003_2026.csv",
}

DT_FORMATS = ["%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M", "%Y.%m.%d %H:%M:%S",
              "%Y.%m.%d %H:%M", "%d/%m/%Y %H:%M"]

def detect_format(sample):
    for f in DT_FORMATS:
        try:
            datetime.strptime(str(sample).strip()[:19], f)
            return f
        except ValueError:
            continue
    return None

def main():
    try:
        import MetaTrader5 as mt5
    except ImportError:
        sys.exit("MetaTrader5 package not installed — run this on the VPS.")
    if not mt5.initialize():
        sys.exit(f"MT5 initialize failed: {mt5.last_error()}")

    for symbol, path in FILES.items():
        print(f"\n{'='*64}\n{symbol}: {path.name}\n{'='*64}")
        if not path.exists():
            print("  MISSING — skipped"); continue

        df = pd.read_csv(path)
        dt_col = None
        for c in df.columns:
            if "time" in c.lower() or "date" in c.lower():
                dt_col = c; break
        if dt_col is None:
            print("  No datetime column found — skipped"); continue

        fmt = detect_format(df[dt_col].iloc[-1])
        if fmt is None:
            print(f"  Unrecognised date format: {df[dt_col].iloc[-1]!r} — skipped")
            continue
        last_dt = datetime.strptime(str(df[dt_col].iloc[-1]).strip()[:19], fmt)
        print(f"  Archive: {len(df):,} rows | last bar {last_dt} (EET) | fmt {fmt}")

        if datetime.now() - last_dt < timedelta(hours=1):
            print("  Already current — nothing to do"); continue

        # backup
        bak = path.with_suffix(f".bak_{datetime.now():%Y%m%d}")
        if not bak.exists():
            shutil.copy2(path, bak)
            print(f"  Backup -> {bak.name}")

        # fetch from MT5 (server time = EET convention, no conversion)
        rates = mt5.copy_rates_range(symbol, mt5.TIMEFRAME_M15,
                                     last_dt - timedelta(hours=2),
                                     datetime.now() + timedelta(hours=6))
        if rates is None or len(rates) == 0:
            print(f"  MT5 returned no bars: {mt5.last_error()} — skipped")
            continue
        new = pd.DataFrame(rates)
        new["dt"] = pd.to_datetime(new["time"], unit="s")
        new = new[new["dt"] > last_dt].sort_values("dt")
        if new.empty:
            print("  No new bars after last archive bar"); continue

        # map MT5 fields onto the archive's own columns
        src = {"open":"open", "high":"high", "low":"low", "close":"close",
               "volume":"tick_volume", "vol":"tick_volume",
               "tick_volume":"tick_volume", "spread":"spread"}
        rows = pd.DataFrame()
        for c in df.columns:
            if c == dt_col:
                rows[c] = new["dt"].dt.strftime(fmt)
            else:
                key = src.get(c.lower().strip())
                rows[c] = new[key].values if key in new.columns and key else ""

        rows.to_csv(path, mode="a", header=False, index=False)
        print(f"  Appended {len(rows):,} bars: "
              f"{new['dt'].iloc[0]} -> {new['dt'].iloc[-1]}")

    mt5.shutdown()
    print(f"\n{'='*64}")
    print("Done. OneDrive will sync to the home machine.")
    print("NEXT: on home machine, regenerate features + trade lists")
    print("(your usual backtest pipeline), then run "
          "zscore_live_vs_backtest_v2.py")
    print("="*64)

if __name__ == "__main__":
    main()