"""
extend_15m_price_data.py
=========================
Extends EURUSD_15M and USDJPY_15M CSVs by pulling fresh bars from MT5.

Must be run on the VPS (where MT5 is authorised).
The live signal monitor MUST be stopped first to avoid MT5 connection conflicts.

What it does:
  1. Reads existing EURUSD_15M_2003_2026.csv and USDJPY_15M_2003_2026.csv
  2. Identifies the latest timestamp in each
  3. Pulls 15M bars from MT5 from that point forward
  4. Appends new bars in the same format as existing (Open/High/Low/Close/Volume,
     trailing space on Volume, dot-separated timestamps)
  5. Creates a timestamped backup of each CSV before overwriting

Run from project root on VPS:
  cd C:\\Users\\Administrator\\OneDrive\\fx_macro_intraday
  python src\\ingestion\\extend_15m_price_data.py

Required: live signal monitor stopped before running.
"""
from __future__ import annotations
import shutil
import sys
from pathlib import Path
from datetime import datetime, timezone, timedelta

import pandas as pd
import MetaTrader5 as mt5

BASE = Path(r"C:\Users\Administrator\OneDrive\fx_macro_intraday")
PRICES_DIR = BASE / "data" / "raw" / "prices"
EU_CSV = PRICES_DIR / "EURUSD" / "EURUSD_15M_2003_2026.csv"
UJ_CSV = PRICES_DIR / "USDJPY" / "USDJPY_15M_2003_2026.csv"

# CSV format constants
TIMESTAMP_FORMAT = "%Y.%m.%d %H:%M:%S"
# Each CSV has its own column convention — EURUSD uses ['datetime','Open','High','Low','Close','Volume ']
# (capitalized, trailing space on Volume), USDJPY uses lowercase ['datetime','open','high','low','close','volume'].
# We preserve each file's original column case/order by reading them at runtime.


def read_existing(csv_path: Path) -> tuple[pd.DataFrame, list[str]]:
    """Read existing CSV, return (DataFrame with parsed datetime, original column order)."""
    df = pd.read_csv(csv_path)
    if "datetime" not in df.columns:
        raise RuntimeError(f"datetime column missing in {csv_path}")
    original_columns = list(df.columns)
    df["datetime"] = pd.to_datetime(df["datetime"], format=TIMESTAMP_FORMAT)
    return df, original_columns


def pull_mt5_bars(symbol: str, since: datetime, target_columns: list[str]) -> pd.DataFrame:
    """Pull 15M bars from MT5 from `since` (broker server time) to now.
    
    `target_columns` is the column convention of the existing CSV — we rename
    MT5's lowercase columns to match (e.g. 'open'->'Open' if existing uses caps).
    """
    # Pull up to 30 days at a time to avoid timeouts on large ranges
    frames = []
    chunk_start = since
    chunk_end_global = datetime.now() + timedelta(hours=4)  # tomorrow ish, broker tz
    
    while chunk_start < chunk_end_global:
        chunk_end = min(chunk_start + timedelta(days=30), chunk_end_global)
        bars = mt5.copy_rates_range(symbol, mt5.TIMEFRAME_M15, chunk_start, chunk_end)
        if bars is None:
            err = mt5.last_error()
            print(f"  [{symbol}] copy_rates_range failed: {err}")
            return None
        if len(bars) == 0:
            print(f"  [{symbol}] empty chunk from {chunk_start.date()} to {chunk_end.date()}")
        else:
            chunk_df = pd.DataFrame(bars)
            frames.append(chunk_df)
            print(f"  [{symbol}] chunk {chunk_start.date()} → {chunk_end.date()}: {len(bars):,} bars")
        chunk_start = chunk_end
    
    if not frames:
        return pd.DataFrame()
    
    df = pd.concat(frames, ignore_index=True)
    
    # MT5 returns: time, open, high, low, close, tick_volume, spread, real_volume
    # We need to rename to match the existing CSV's column convention.
    # Build a rename map: figure out which target column corresponds to each MT5 column
    # by case-insensitive match on the core column NAMES.
    rename_map = {"time": "datetime"}
    # Map MT5 lowercase names to whatever case+spacing the existing CSV uses
    mt5_to_canonical = {
        "open":  "open",
        "high":  "high",
        "low":   "low",
        "close": "close",
        "tick_volume": "volume",   # MT5 uses tick_volume, CSV uses volume
    }
    for mt5_col, canonical in mt5_to_canonical.items():
        # Find target column that matches canonical name case-insensitively+stripped
        match = next(
            (t for t in target_columns if t.strip().lower() == canonical),
            None,
        )
        if match is None:
            raise RuntimeError(f"Cannot find '{canonical}' equivalent in target columns: {target_columns}")
        rename_map[mt5_col] = match
    
    df = df.rename(columns=rename_map)
    
    # MT5 epoch (now under target column name for 'datetime') -> naive datetime
    df["datetime"] = pd.to_datetime(df["datetime"], unit="s")
    
    df = df[target_columns]  # uses the target column ORDER
    df = df.drop_duplicates(subset=["datetime"], keep="first")
    df = df.sort_values("datetime").reset_index(drop=True)
    return df


def format_for_csv(df: pd.DataFrame, target_columns: list[str]) -> pd.DataFrame:
    """Format datetime column as strings, ensure target column order."""
    df = df.copy()
    df["datetime"] = df["datetime"].dt.strftime(TIMESTAMP_FORMAT)
    df = df[target_columns]
    return df


def extend_pair(symbol: str, csv_path: Path) -> dict:
    """Extend one pair's CSV. Returns summary dict."""
    print(f"\n{'─' * 70}")
    print(f"  {symbol}")
    print(f"{'─' * 70}")
    print(f"  CSV: {csv_path}")
    
    if not csv_path.exists():
        return {"error": "csv not found", "symbol": symbol}
    
    existing, original_columns = read_existing(csv_path)
    print(f"  Existing rows: {len(existing):,}")
    print(f"  Existing columns: {original_columns}")
    last_ts = existing["datetime"].max()
    print(f"  Latest existing timestamp: {last_ts}")
    
    # Pull bars from MT5 starting at last_ts + 15min
    since = last_ts + timedelta(minutes=15)
    print(f"  Pulling MT5 bars from: {since}")
    
    new_bars = pull_mt5_bars(symbol, since, original_columns)
    if new_bars is None:
        return {"error": "MT5 pull failed", "symbol": symbol}
    if new_bars.empty:
        print(f"  No new bars available from MT5")
        return {"symbol": symbol, "new_bars": 0, "appended": False}
    
    # Filter out any bars that are <= last_ts (defensive)
    new_bars = new_bars[new_bars["datetime"] > last_ts].copy()
    if new_bars.empty:
        print(f"  All MT5 bars already in CSV — nothing to append")
        return {"symbol": symbol, "new_bars": 0, "appended": False}
    
    new_bars = new_bars.sort_values("datetime").reset_index(drop=True)
    new_first = new_bars["datetime"].min()
    new_last  = new_bars["datetime"].max()
    print(f"  New bars to append: {len(new_bars):,}")
    print(f"  New range: {new_first} → {new_last}")
    
    # Sanity check: the new range should make sense
    expected_first = last_ts + timedelta(minutes=15)
    gap = (new_first - expected_first).total_seconds() / 60
    if abs(gap) > 60 * 24 * 3:  # more than 3-day gap?
        print(f"  ⚠️  WARNING: gap of {gap/60:.1f} hours between existing and new data")
        print(f"  This may indicate broker historical data limit reached")
    
    # Backup existing CSV
    backup_path = csv_path.with_suffix(
        f".backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
    )
    shutil.copy2(csv_path, backup_path)
    print(f"  Backup saved: {backup_path.name}")
    
    # Format new bars and combine
    existing_formatted = format_for_csv(existing, original_columns)
    new_formatted      = format_for_csv(new_bars, original_columns)
    combined = pd.concat([existing_formatted, new_formatted], ignore_index=True)
    
    # Save with same convention as original
    combined.to_csv(csv_path, index=False)
    print(f"  Updated CSV: {len(combined):,} total rows")
    
    return {
        "symbol":   symbol,
        "new_bars": len(new_bars),
        "new_range": f"{new_first} → {new_last}",
        "total_rows": len(combined),
        "appended": True,
        "backup":   backup_path.name,
    }


def main():
    print("=" * 70)
    print("  EXTEND 15M PRICE DATA")
    print("=" * 70)
    print(f"  Run time: {datetime.now()}")
    print(f"  Base path: {BASE}")
    print()
    print("  CHECK: live signal monitor must be STOPPED before running.")
    print("  Continuing in 5s — Ctrl+C to abort if monitor still running.")
    import time
    time.sleep(5)
    
    if not mt5.initialize():
        print(f"  ❌ MT5 initialize failed: {mt5.last_error()}")
        sys.exit(1)
    
    # Show MT5 server time for sanity
    info = mt5.terminal_info()
    if info:
        print(f"  MT5 broker: {info.company}")
        print(f"  MT5 path:   {info.path}")
    print()
    
    summaries = []
    for symbol, csv_path in [("EURUSD", EU_CSV), ("USDJPY", UJ_CSV)]:
        try:
            summary = extend_pair(symbol, csv_path)
            summaries.append(summary)
        except Exception as e:
            print(f"  ❌ {symbol} failed: {e}")
            summaries.append({"symbol": symbol, "error": str(e)})
    
    mt5.shutdown()
    
    print()
    print("=" * 70)
    print("  SUMMARY")
    print("=" * 70)
    for s in summaries:
        if "error" in s:
            print(f"  {s['symbol']:8} ERROR: {s['error']}")
        elif s.get("appended"):
            print(f"  {s['symbol']:8} appended {s['new_bars']:,} bars, "
                  f"now {s['total_rows']:,} total | backup: {s['backup']}")
        else:
            print(f"  {s['symbol']:8} no new bars to append")
    print()
    print("  Next: restart live signal monitor.")
    print("  OneDrive will sync updated CSVs to your home machine within a few minutes.")


if __name__ == "__main__":
    main()