!pip install pandas

import warnings
warnings.filterwarnings("ignore")

import pandas as pd
import re

raw_data = {
    "exchange": ["Bybit",     "Kraken",    "OKX",            "Binance",  "Coinbase",  "Bitfinex",  "Deribit"              ],
    "symbol":   ["BTCUSDT",   "XBT/USD",   "BTC-USDT-SWAP",  "ETHUSDT",  "BTC-USD",   "tBTCUSD",   "BTC-31JAN25-50000-C" ],
    "open":     [42100.0,     42150.0,     42200.0,           3200.0,     42180.0,     42170.0,     42160.0               ],
    "high":     [42300.0,     42350.0,     42400.0,           3250.0,     42380.0,     42370.0,     42360.0               ],
    "low":      [41900.0,     41950.0,     42000.0,           3150.0,     41980.0,     41970.0,     41960.0               ],
    "close":    [42200.0,     42250.0,     42300.0,           3220.0,     42280.0,     42270.0,     42260.0               ],
    "volume":   [10.5,        8.2,         9.1,               150.3,      7.6,         6.4,         3.2                   ]
}

df_raw = pd.DataFrame(raw_data)

print("--- Raw Symbol Formats ---")
display(df_raw[["exchange", "symbol"]])

ALIAS_MAP = {
    "XBT":  "BTC",
    "XETH": "ETH",
    "XXBT": "BTC",
    "ZUSD": "USD",
}

QUOTE_CURRENCIES = ["USDT", "USDC", "USD", "BTC", "ETH", "BUSD", "EUR"]

def normalize_symbol(raw_symbol: str, exchange: str) -> dict:
    symbol = raw_symbol.upper().strip()

    # Strip Bitfinex leading 't' prefix
    if exchange.lower() == "bitfinex" and symbol.startswith("T"):
        symbol = symbol[1:]

    # Detect Deribit options/futures by expiry date pattern
    if re.search(r"-\d{2}[A-Z]{3}\d{2}-", symbol):
        parts = symbol.split("-")
        base  = ALIAS_MAP.get(parts[0], parts[0])
        return {
            "canonical": symbol,
            "base":      base,
            "quote":     "USD",
            "type":      "option" if symbol.endswith(("-C", "-P")) else "future",
        }

    # Normalize slash delimiter to hyphen
    symbol = symbol.replace("/", "-")

    # Strip instrument type suffixes
    for suffix in ["-SWAP", "-PERP", "-PERPETUAL"]:
        if symbol.endswith(suffix):
            symbol = symbol[: -len(suffix)]
            break

    # Split on hyphen delimiter
    if "-" in symbol:
        parts = symbol.split("-", 1)
        base  = ALIAS_MAP.get(parts[0], parts[0])
        quote = ALIAS_MAP.get(parts[1], parts[1])
        return {"canonical": f"{base}-{quote}", "base": base, "quote": quote, "type": "spot"}

    # Split delimiter-free symbols using known quote currency list
    for q in QUOTE_CURRENCIES:
        if symbol.endswith(q):
            base  = ALIAS_MAP.get(symbol[: -len(q)], symbol[: -len(q)])
            quote = ALIAS_MAP.get(q, q)
            return {"canonical": f"{base}-{quote}", "base": base, "quote": quote, "type": "spot"}

    return {"canonical": symbol, "base": symbol, "quote": "UNKNOWN", "type": "unknown"}


def handle_symbols(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()

    normalized = df.apply(
        lambda row: normalize_symbol(row["symbol"], row["exchange"]), axis=1
    )

    df["canonical"] = normalized.apply(lambda x: x["canonical"])
    df["base"]      = normalized.apply(lambda x: x["base"])
    df["quote"]     = normalized.apply(lambda x: x["quote"])
    df["type"]      = normalized.apply(lambda x: x["type"])

    return df[["exchange", "symbol", "canonical", "base", "quote", "type",
               "open", "high", "low", "close", "volume"]]

df_normalized = handle_symbols(df_raw)

print("--- Normalized Symbol Mapping ---")
display(df_normalized[["exchange", "symbol", "canonical", "base", "quote", "type"]])

print("\n--- Full Output with OHLCV ---")
display(df_normalized)

print("\n--- Schema Summary ---")
df_normalized.info()