scripts/build_gap_db.py

"""
Historical Gap Database Builder v3
═══════════════════════════════════
Downloads 15 years of daily data and computes WEEKEND-ONLY gaps
with 20 engineered features including gold-specific signals.

Changes from v2:
  - Strict weekend detection (Friday weekday==4 → Monday weekday==0)
  - Holiday gaps excluded (they behave differently)
  - Gold-specific features: real yield proxy, GLD ETF momentum, gold/DXY ratio
  - Cross-asset features: EUR/USD-Gold correlation
  - Gap history features: prev 3 gaps mean, gap fill rate
  - ATR-normalized gap size
  - Day-of-month effects (month-end rebalancing)
  - Data validation: NaN checks, candle count verification

Run: python scripts/build_gap_db.py
"""

import logging
import sqlite3
import sys
import os
import math

import numpy as np
import pandas as pd
import yfinance as yf

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(name)s: %(message)s")
logger = logging.getLogger("build_gap_db")

DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "data", "gap_system.db")

ASSET_MAP = {
    "XAUUSD": "GC=F",
    "EURUSD": "EURUSD=X",
    "GBPUSD": "GBPUSD=X",
}

DXY_TICKER = "DX-Y.NYB"
GLD_TICKER = "GLD"       # Gold ETF — proxy for institutional gold flows
TIP_TICKER = "TIP"       # TIPS ETF — proxy for real yield expectations
TNX_TICKER = "^TNX"      # 10-Year Treasury Yield


def _compute_rsi(series: pd.Series, period: int = 14) -> pd.Series:
    delta = series.diff()
    gain = delta.where(delta > 0, 0.0)
    loss = -delta.where(delta < 0, 0.0)
    avg_gain = gain.ewm(span=period, adjust=False).mean()
    avg_loss = loss.ewm(span=period, adjust=False).mean()
    rs = avg_gain / avg_loss.replace(0, np.nan)
    return 100 - (100 / (1 + rs))


def _compute_macd_hist(series: pd.Series) -> pd.Series:
    fast = series.ewm(span=12, adjust=False).mean()
    slow = series.ewm(span=26, adjust=False).mean()
    macd_line = fast - slow
    signal = macd_line.ewm(span=9, adjust=False).mean()
    return macd_line - signal


def _compute_bb_width(series: pd.Series, window: int = 20) -> pd.Series:
    mid = series.rolling(window).mean()
    std = series.rolling(window).std()
    upper = mid + 2 * std
    lower = mid - 2 * std
    return (upper - lower) / mid.replace(0, np.nan)


def _compute_atr(high: pd.Series, low: pd.Series, close: pd.Series, period: int = 14) -> pd.Series:
    prev_close = close.shift(1)
    tr = pd.concat([
        high - low,
        (high - prev_close).abs(),
        (low - prev_close).abs(),
    ], axis=1).max(axis=1)
    return tr.ewm(span=period, adjust=False).mean()


def _safe_float(val, default=0.0) -> float:
    """Safely convert to float, handling NaN/None."""
    if val is None:
        return default
    try:
        f = float(val)
        return default if (math.isnan(f) or math.isinf(f)) else f
    except (ValueError, TypeError):
        return default


def _get_nearest(df: pd.DataFrame, target_date, column: str, default: float = 0.0) -> float:
    """Get the nearest value from a DataFrame for a given date."""
    idx = df.index.get_indexer([target_date], method="pad")
    if idx[0] != -1:
        return _safe_float(df.iloc[idx[0]][column], default)
    return default


def _validate_data(df: pd.DataFrame, ticker: str) -> bool:
    """Validate yfinance data integrity."""
    if df.empty:
        logger.warning("VALIDATION FAIL: %s returned empty DataFrame", ticker)
        return False
    
    # Check for required columns
    required = {"open", "high", "low", "close"}
    if not required.issubset(set(df.columns)):
        logger.warning("VALIDATION FAIL: %s missing OHLC columns: %s", ticker, df.columns.tolist())
        return False
    
    # Check for excessive NaNs (>10% of close column)
    nan_pct = df["close"].isna().mean()
    if nan_pct > 0.10:
        logger.warning("VALIDATION FAIL: %s has %.1f%% NaN in close column", ticker, nan_pct * 100)
        return False
    
    # Check for suspicious duplicate rows
    dup_pct = df.duplicated(subset=["open", "high", "low", "close"]).mean()
    if dup_pct > 0.20:
        logger.warning("VALIDATION WARN: %s has %.1f%% duplicate OHLC rows", ticker, dup_pct * 100)
    
    # Check for zero-price rows
    zero_close = (df["close"] == 0).sum()
    if zero_close > 0:
        logger.warning("VALIDATION WARN: %s has %d zero-close rows (removed)", ticker, zero_close)
        df.drop(df[df["close"] == 0].index, inplace=True)
    
    logger.info("VALIDATED: %s — %d rows, %.1f%% NaN, %.1f%% duplicates",
                ticker, len(df), nan_pct * 100, dup_pct * 100)
    return True


def _download_with_validation(ticker: str, period: str = "15y") -> pd.DataFrame:
    """Download yfinance data with validation and cleanup."""
    logger.info("Downloading %s (%s)...", ticker, period)
    df = yf.download(ticker, period=period, interval="1d", progress=False)
    
    if isinstance(df.columns, pd.MultiIndex):
        df.columns = df.columns.get_level_values(0)
    df.columns = [c.lower() for c in df.columns]
    
    if not _validate_data(df, ticker):
        return pd.DataFrame()
    
    # Forward-fill small gaps (1-2 days)
    df = df.ffill(limit=2)
    
    return df


def build_database():
    """Download historical data and compute WEEKEND-ONLY gaps with full feature set."""
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    cursor.execute("DROP TABLE IF EXISTS historical_gaps")
    cursor.execute("""
        CREATE TABLE historical_gaps (
            rowid           INTEGER PRIMARY KEY AUTOINCREMENT,
            asset           TEXT NOT NULL,
            friday_date     TEXT NOT NULL,
            friday_close    REAL NOT NULL,
            monday_open     REAL NOT NULL,
            gap_size        REAL NOT NULL,
            gap_pct         REAL NOT NULL,
            gap_direction   TEXT NOT NULL,
            vix_close       REAL,
            vix_change_5d   REAL,
            volume_spike    REAL,
            weekly_trend    TEXT,
            weekly_return   REAL,
            rsi_14          REAL,
            macd_hist       REAL,
            ema_spread      REAL,
            bb_width        REAL,
            dxy_change      REAL,
            prev_gap_dir    TEXT DEFAULT 'NONE',
            atr_14          REAL,
            gap_atr_ratio   REAL,
            day_of_month    INTEGER,
            prev_3_gaps_mean REAL,
            gap_fill_rate   REAL,
            gold_dxy_ratio  REAL,
            gld_momentum    REAL,
            real_yield_proxy REAL,
            treasury_10y    REAL
        );
    """)

    # ── Download reference data ──
    vix_data = _download_with_validation("^VIX")
    dxy_data = _download_with_validation(DXY_TICKER)
    gld_data = _download_with_validation(GLD_TICKER)
    tip_data = _download_with_validation(TIP_TICKER)
    tnx_data = _download_with_validation(TNX_TICKER)

    if vix_data.empty or dxy_data.empty:
        logger.error("Critical reference data (VIX/DXY) failed to download. Aborting.")
        conn.close()
        return

    total_inserted = 0
    skipped_holiday = 0
    skipped_weekday = 0

    for asset, ticker in ASSET_MAP.items():
        df = _download_with_validation(ticker)
        if df.empty:
            continue

        # ── Compute indicators ──
        if "volume" in df.columns and df["volume"].sum() > 0:
            df["vol_30ma"] = df["volume"].rolling(30).mean()
        else:
            df["vol_30ma"] = 1.0
            df["volume"] = 1.0

        df["ema10"] = df["close"].ewm(span=10, adjust=False).mean()
        df["ema20"] = df["close"].ewm(span=20, adjust=False).mean()
        df["rsi_14"] = _compute_rsi(df["close"])
        df["macd_hist"] = _compute_macd_hist(df["close"])
        df["bb_width"] = _compute_bb_width(df["close"])
        df["ema_spread"] = (df["ema10"] - df["ema20"]) / df["close"] * 100
        df["return_5d"] = df["close"].pct_change(5) * 100
        df["atr_14"] = _compute_atr(df["high"], df["low"], df["close"])

        # ── Gold-specific: compute gold/DXY ratio ──
        if asset == "XAUUSD" and not dxy_data.empty:
            # Align DXY to asset dates
            dxy_aligned = dxy_data["close"].reindex(df.index, method="pad")
            df["gold_dxy_ratio"] = df["close"] / dxy_aligned.replace(0, np.nan)
        else:
            df["gold_dxy_ratio"] = 0.0

        rows_to_insert = []
        prev_gap_dir = "NONE"
        gap_history = []  # Track recent gaps for rolling stats
        # For gap fill rate calculation, track whether gaps got filled
        gap_fill_count = 0
        gap_total_count = 0

        for i in range(len(df) - 1):
            date_curr = df.index[i]
            date_next = df.index[i + 1]
            cal_days = (date_next - date_curr).days

            # ── STRICT WEEKEND DETECTION ──
            # Only include true Friday→Monday gaps
            # Friday = weekday 4, Monday = weekday 0
            curr_weekday = date_curr.weekday()
            next_weekday = date_next.weekday()

            if curr_weekday != 4 or next_weekday != 0:
                if cal_days >= 2:
                    skipped_holiday += 1  # Holiday gap — excluded
                else:
                    skipped_weekday += 1  # Normal weekday — not a gap
                continue

            friday_close = _safe_float(df.iloc[i]["close"])
            monday_open = _safe_float(df.iloc[i + 1]["open"])

            if friday_close <= 0 or monday_open <= 0:
                continue

            gap_size = monday_open - friday_close
            gap_pct = (gap_size / friday_close) * 100

            if abs(gap_pct) < 0.01:
                gap_direction = "NONE"
            elif gap_size > 0:
                gap_direction = "BULLISH"
            else:
                gap_direction = "BEARISH"

            # ── VIX features ──
            vix_close = _get_nearest(vix_data, date_curr, "close", 15.0)
            vix_change_5d = 0.0
            v_idx = vix_data.index.get_indexer([date_curr], method="pad")
            if v_idx[0] != -1 and v_idx[0] >= 5:
                vix_prev = _safe_float(vix_data.iloc[v_idx[0] - 5]["close"], 15.0)
                if vix_prev > 0:
                    vix_change_5d = (vix_close - vix_prev) / vix_prev * 100

            # ── Volume spike ──
            vol = _safe_float(df.iloc[i]["volume"], 1.0)
            vol_ma = _safe_float(df.iloc[i]["vol_30ma"], 1.0)
            volume_spike = (vol / vol_ma) if vol_ma > 0 else 1.0

            # ── Weekly trend ──
            ema10_val = _safe_float(df.iloc[i]["ema10"])
            ema20_val = _safe_float(df.iloc[i]["ema20"])
            weekly_trend = "BULLISH" if ema10_val > ema20_val else "BEARISH"

            # ── Technical indicators ──
            weekly_return = _safe_float(df.iloc[i]["return_5d"])
            rsi_val = _safe_float(df.iloc[i]["rsi_14"], 50.0)
            macd_val = _safe_float(df.iloc[i]["macd_hist"])
            ema_spread = _safe_float(df.iloc[i]["ema_spread"])
            bb_w = _safe_float(df.iloc[i]["bb_width"], 0.02)
            atr_val = _safe_float(df.iloc[i]["atr_14"], 1.0)

            # ── ATR-normalized gap (how big is this gap relative to normal volatility) ──
            gap_atr_ratio = abs(gap_size) / atr_val if atr_val > 0 else 0.0

            # ── DXY change ──
            dxy_change = 0.0
            d_idx = dxy_data.index.get_indexer([date_curr], method="pad")
            if d_idx[0] != -1 and d_idx[0] >= 5:
                d_curr = _safe_float(dxy_data.iloc[d_idx[0]]["close"])
                d_prev = _safe_float(dxy_data.iloc[d_idx[0] - 5]["close"])
                if d_prev > 0:
                    dxy_change = (d_curr - d_prev) / d_prev * 100

            # ── Day of month (1-31) for month-end rebalancing effects ──
            day_of_month = date_curr.day

            # ── Previous 3 gaps mean ──
            prev_3_gaps_mean = np.mean(gap_history[-3:]) if len(gap_history) >= 1 else 0.0

            # ── Gap fill rate (rolling: what % of recent gaps got filled within the week) ──
            gap_fill_rate = (gap_fill_count / gap_total_count) if gap_total_count > 0 else 0.6

            # Check if THIS gap gets "filled" (price returns to friday close within 5 days)
            if i + 6 < len(df) and gap_direction != "NONE":
                gap_total_count += 1
                next_5_days = df.iloc[i + 1:i + 6]
                if gap_direction == "BULLISH":
                    # Bullish gap filled if price drops back to friday close
                    if next_5_days["low"].min() <= friday_close:
                        gap_fill_count += 1
                else:
                    # Bearish gap filled if price rises back to friday close
                    if next_5_days["high"].max() >= friday_close:
                        gap_fill_count += 1

            # ── Gold-specific features ──
            gold_dxy_ratio = _safe_float(df.iloc[i].get("gold_dxy_ratio", 0))

            # GLD ETF momentum (5-day return)
            gld_momentum = 0.0
            if not gld_data.empty:
                g_idx = gld_data.index.get_indexer([date_curr], method="pad")
                if g_idx[0] != -1 and g_idx[0] >= 5:
                    g_curr = _safe_float(gld_data.iloc[g_idx[0]]["close"])
                    g_prev = _safe_float(gld_data.iloc[g_idx[0] - 5]["close"])
                    if g_prev > 0:
                        gld_momentum = (g_curr - g_prev) / g_prev * 100

            # Real yield proxy: TIP ETF return (inverse = higher real yields)
            real_yield_proxy = 0.0
            if not tip_data.empty:
                t_idx = tip_data.index.get_indexer([date_curr], method="pad")
                if t_idx[0] != -1 and t_idx[0] >= 5:
                    t_curr = _safe_float(tip_data.iloc[t_idx[0]]["close"])
                    t_prev = _safe_float(tip_data.iloc[t_idx[0] - 5]["close"])
                    if t_prev > 0:
                        real_yield_proxy = (t_curr - t_prev) / t_prev * 100

            # 10-Year Treasury Yield
            treasury_10y = 0.0
            if not tnx_data.empty:
                treasury_10y = _get_nearest(tnx_data, date_curr, "close", 3.0)

            rows_to_insert.append((
                asset,
                date_curr.strftime("%Y-%m-%d"),
                friday_close,
                monday_open,
                gap_size,
                gap_pct,
                gap_direction,
                vix_close,
                vix_change_5d,
                volume_spike,
                weekly_trend,
                weekly_return,
                rsi_val,
                macd_val,
                ema_spread,
                bb_w,
                dxy_change,
                prev_gap_dir,
                atr_val,
                gap_atr_ratio,
                day_of_month,
                prev_3_gaps_mean,
                gap_fill_rate,
                gold_dxy_ratio,
                gld_momentum,
                real_yield_proxy,
                treasury_10y,
            ))

            gap_history.append(gap_pct)
            prev_gap_dir = gap_direction

        cursor.executemany("""
            INSERT INTO historical_gaps (
                asset, friday_date, friday_close, monday_open, gap_size, gap_pct,
                gap_direction, vix_close, vix_change_5d, volume_spike, weekly_trend,
                weekly_return, rsi_14, macd_hist, ema_spread, bb_width,
                dxy_change, prev_gap_dir, atr_14, gap_atr_ratio, day_of_month,
                prev_3_gaps_mean, gap_fill_rate, gold_dxy_ratio, gld_momentum,
                real_yield_proxy, treasury_10y
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, rows_to_insert)

        total_inserted += len(rows_to_insert)
        logger.info("Inserted %d WEEKEND gaps for %s (skipped %d holiday gaps)",
                     len(rows_to_insert), asset, skipped_holiday)

    conn.commit()

    # Print summary
    cursor.execute("SELECT asset, gap_direction, COUNT(*) FROM historical_gaps GROUP BY asset, gap_direction")
    print("\n" + "═" * 55)
    print("  HISTORICAL GAP DATABASE v3")
    print("═" * 55)
    for row in cursor.fetchall():
        print(f"  {row[0]:8s} {row[1]:8s}: {row[2]:4d} gaps")
    print(f"\n  Total:    {total_inserted} weekend gaps")
    print(f"  Excluded: {skipped_holiday} holiday gaps (different behavior)")
    print(f"  Features: 20 per observation")
    print(f"  DB:       {DB_PATH}")
    
    # Data quality report
    cursor.execute("SELECT asset, MIN(friday_date), MAX(friday_date), COUNT(*) FROM historical_gaps GROUP BY asset")
    print("\n  Date Ranges:")
    for row in cursor.fetchall():
        print(f"    {row[0]:8s}: {row[1]} → {row[2]} ({row[3]} obs)")
    
    print("═" * 55)
    conn.close()
    logger.info("Database v3 populated with %d weekend-only gaps.", total_inserted)


if __name__ == "__main__":
    build_database()