"""
Gera seed_corr_prices.sql com ~91 dias de precos historicos
para a tabela {prefix}daaz_corr_prices do plugin DaazMarkets.

Fontes:
  BTC  -> Binance public API (sem auth)
  Rest -> Yahoo Finance v8/chart (sem auth)

Uso: python tools/generate_sql.py
Saida: tools/seed_corr_prices.sql
"""

import urllib.request
import urllib.error
import json
import datetime
import sys
import os

# ---------------------------------------------------------------------------
DAYS = 91          # quantos dias de historico queremos no banco

HEADERS = {
    'User-Agent': (
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64) '
        'AppleWebKit/537.36 (KHTML, like Gecko) '
        'Chrome/124.0 Safari/537.36'
    ),
    'Accept': 'application/json, text/plain, */*',
}

# (yahoo_encoded_symbol, db_symbol)
YAHOO_SYMBOLS = [
    ('GC%3DF',     'XAU'),   # Gold
    ('SI%3DF',     'XAG'),   # Silver
    ('CL%3DF',     'WTI'),   # Crude Oil WTI
    ('DX-Y.NYB',   'DXY'),   # US Dollar Index
    ('%5EIXIC',    'IXIC'),  # Nasdaq Composite
    ('%5EGSPC',    'SPX'),   # S&P 500
    ('%5ETNX',     'TNX'),   # 10-Year Treasury Yield
    ('%5EVIX',     'VIX'),   # VIX
]
# ---------------------------------------------------------------------------


def fetch_json(url, timeout=30):
    req = urllib.request.Request(url, headers=HEADERS)
    try:
        with urllib.request.urlopen(req, timeout=timeout) as resp:
            return json.loads(resp.read().decode('utf-8'))
    except urllib.error.HTTPError as e:
        raise RuntimeError(f"HTTP {e.code}: {url}") from e
    except urllib.error.URLError as e:
        raise RuntimeError(f"URL error: {e.reason}") from e


def parse_yahoo(encoded_symbol, db_symbol):
    url = (
        f"https://query1.finance.yahoo.com/v8/finance/chart/"
        f"{encoded_symbol}?interval=1d&range=6mo"
    )
    data = fetch_json(url)
    result = data['chart']['result'][0]
    timestamps = result['timestamp']
    closes = result['indicators']['quote'][0]['close']

    rows = []
    for ts, close in zip(timestamps, closes):
        if close is None:
            continue
        date_str = datetime.datetime.utcfromtimestamp(ts).strftime('%Y-%m-%d')
        rows.append((db_symbol, date_str, round(float(close), 6)))

    rows.sort(key=lambda x: x[1])
    return rows[-DAYS:]


def fetch_btc():
    url = (
        "https://api.binance.com/api/v3/klines"
        "?symbol=BTCUSDT&interval=1d&limit=120"
    )
    klines = fetch_json(url)
    rows = []
    for k in klines:
        ts_ms = int(k[0])
        close = round(float(k[4]), 6)
        date_str = datetime.datetime.utcfromtimestamp(ts_ms / 1000).strftime('%Y-%m-%d')
        rows.append(('BTC', date_str, close))

    rows.sort(key=lambda x: x[1])
    return rows[-DAYS:]


def main():
    print("=" * 60)
    print("DaazMarkets - Gerador de SQL seed para corr_prices")
    print("=" * 60)

    all_data = {}

    # BTC
    sym = 'BTC'
    try:
        rows = fetch_btc()
        all_data[sym] = rows
        print(f"[OK] {sym:6s}: {len(rows)} rows | ultimo={rows[-1][1]} | preco={rows[-1][2]:,.2f}")
    except Exception as e:
        print(f"[ERR] {sym}: {e}")
        all_data[sym] = []

    # Yahoo symbols
    for encoded, sym in YAHOO_SYMBOLS:
        try:
            rows = parse_yahoo(encoded, sym)
            all_data[sym] = rows
            last = rows[-1] if rows else None
            price_fmt = f"{last[2]:,.4f}" if last else "N/A"
            print(f"[OK] {sym:6s}: {len(rows)} rows | ultimo={last[1] if last else 'N/A'} | preco={price_fmt}")
        except Exception as e:
            print(f"[ERR] {sym}: {e}")
            all_data[sym] = []

    # Build SQL
    today = datetime.date.today().isoformat()
    lines = [
        "-- ============================================================",
        "-- DaazMarkets: Seed SQL para wp_daaz_corr_prices",
        f"-- Gerado em: {today}",
        "-- IMPORTANTE: ajuste o prefixo 'wp_' caso o seu seja diferente.",
        "-- Execute cada bloco separadamente no phpMyAdmin se preferir.",
        "-- ============================================================",
        "",
        "SET NAMES utf8mb4;",
        "",
    ]

    symbol_order = ['BTC'] + [sym for _, sym in YAHOO_SYMBOLS]
    total = 0

    for sym in symbol_order:
        rows = all_data.get(sym, [])
        if not rows:
            lines.append(f"-- AVISO: sem dados para {sym}")
            lines.append("")
            continue

        last_date = rows[-1][1]
        last_price = rows[-1][2]
        lines.append(
            f"-- {sym}: {len(rows)} registros  "
            f"(mais recente: {last_date}, preco={last_price:,.4f})"
        )
        values_sql = ",\n".join(
            f"('{r[0]}', '{r[1]}', {r[2]})" for r in rows
        )
        lines.append(
            f"INSERT IGNORE INTO `wp_daaz_corr_prices` "
            f"(symbol, price_date, close_price) VALUES\n"
            f"{values_sql};"
        )
        lines.append("")
        total += len(rows)

    lines.append(f"-- Total de registros inseridos: {total}")
    sql_content = "\n".join(lines)

    # Write file
    out_path = os.path.join(os.path.dirname(__file__), 'seed_corr_prices.sql')
    with open(out_path, 'w', encoding='utf-8') as fh:
        fh.write(sql_content)

    print()
    print(f"SQL salvo em: {out_path}")
    print(f"Total de registros: {total}")
    print()
    print("Proximo passo:")
    print("  1. Abra phpMyAdmin -> seu banco WordPress")
    print("  2. Clique em 'SQL' e cole o conteudo do arquivo acima")
    print("  3. Execute. O shortcode de correlacao ja vai funcionar!")


if __name__ == '__main__':
    main()