from __future__ import annotations

import math
import sqlite3
from collections import defaultdict
from datetime import datetime
from pathlib import Path
from typing import Any, Iterable


ORDERBOOK_FEATURES = (
    "l1_imbalance_mean",
    "l1_imbalance_std",
    "l1_spread_bps_mean",
    "l1_spread_bps_p90",
    "l1_microprice_deviation_bps_mean",
    "l1_microprice_deviation_bps_std",
    "l1_sample_count_log1p",
)


def interval_milliseconds(interval: str) -> int:
    normalized = str(interval).strip().upper()
    if normalized.isdigit():
        return max(1, int(normalized)) * 60_000
    units = {
        "D": 86_400_000,
        "W": 7 * 86_400_000,
        "M": 30 * 86_400_000,
    }
    return units.get(normalized, 0)


def load_orderbook_feature_map(
    path: str | Path,
    *,
    interval: str,
    symbols: Iterable[str] | None = None,
    min_samples_per_bucket: int = 20,
) -> tuple[dict[str, dict[int, dict[str, float]]], dict[str, dict[str, Any]]]:
    database_path = Path(path)
    if not database_path.is_file():
        return {}, {}
    selected = sorted({str(symbol).strip().upper() for symbol in symbols or [] if str(symbol).strip()})
    query = (
        "SELECT symbol, bid_price, bid_size, ask_price, ask_size, mid_price, "
        "microprice, spread_bps, imbalance, source_timestamp_ms, created_at "
        "FROM market_observations"
    )
    parameters: list[Any] = []
    if selected:
        placeholders = ",".join("?" for _ in selected)
        query += f" WHERE symbol IN ({placeholders})"
        parameters.extend(selected)
    query += " ORDER BY symbol, source_timestamp_ms, created_at"
    with sqlite3.connect(database_path) as connection:
        connection.row_factory = sqlite3.Row
        try:
            rows = connection.execute(query, parameters).fetchall()
        except sqlite3.Error:
            return {}, {}
    return aggregate_orderbook_observations(
        (dict(row) for row in rows),
        interval=interval,
        min_samples_per_bucket=min_samples_per_bucket,
    )


def aggregate_orderbook_observations(
    rows: Iterable[dict[str, Any]],
    *,
    interval: str,
    min_samples_per_bucket: int = 20,
) -> tuple[dict[str, dict[int, dict[str, float]]], dict[str, dict[str, Any]]]:
    interval_ms = interval_milliseconds(interval)
    if interval_ms <= 0:
        raise ValueError(f"unsupported orderbook aggregation interval: {interval}")
    minimum = max(1, int(min_samples_per_bucket))
    buckets: dict[tuple[str, int], list[tuple[float, float, float]]] = defaultdict(list)
    raw_counts: dict[str, int] = defaultdict(int)
    first_timestamp: dict[str, int] = {}
    last_timestamp: dict[str, int] = {}
    for row in rows:
        symbol = str(row.get("symbol") or "").strip().upper()
        timestamp_ms = _observation_timestamp_ms(row)
        mid_price = _float(row.get("mid_price"))
        microprice = _float(row.get("microprice"), mid_price)
        spread_bps = max(0.0, _float(row.get("spread_bps")))
        imbalance = max(-1.0, min(1.0, _float(row.get("imbalance"))))
        if not symbol or timestamp_ms <= 0 or mid_price <= 0:
            continue
        microprice_deviation_bps = ((microprice - mid_price) / mid_price) * 10_000.0
        if not all(math.isfinite(value) for value in (imbalance, spread_bps, microprice_deviation_bps)):
            continue
        bucket_timestamp = (timestamp_ms // interval_ms) * interval_ms
        buckets[(symbol, bucket_timestamp)].append(
            (imbalance, spread_bps, microprice_deviation_bps)
        )
        raw_counts[symbol] += 1
        first_timestamp[symbol] = min(first_timestamp.get(symbol, timestamp_ms), timestamp_ms)
        last_timestamp[symbol] = max(last_timestamp.get(symbol, timestamp_ms), timestamp_ms)

    features: dict[str, dict[int, dict[str, float]]] = defaultdict(dict)
    rejected_buckets: dict[str, int] = defaultdict(int)
    for (symbol, bucket_timestamp), samples in sorted(buckets.items()):
        if len(samples) < minimum:
            rejected_buckets[symbol] += 1
            continue
        imbalances = [sample[0] for sample in samples]
        spreads = [sample[1] for sample in samples]
        microprice_deviations = [sample[2] for sample in samples]
        features[symbol][bucket_timestamp] = {
            "l1_imbalance_mean": _mean(imbalances),
            "l1_imbalance_std": _standard_deviation(imbalances),
            "l1_spread_bps_mean": _mean(spreads),
            "l1_spread_bps_p90": _percentile(spreads, 0.90),
            "l1_microprice_deviation_bps_mean": _mean(microprice_deviations),
            "l1_microprice_deviation_bps_std": _standard_deviation(microprice_deviations),
            "l1_sample_count_log1p": math.log1p(len(samples)),
        }

    manifest: dict[str, dict[str, Any]] = {}
    all_symbols = sorted(set(raw_counts) | set(features))
    for symbol in all_symbols:
        accepted = features.get(symbol, {})
        manifest[symbol] = {
            "raw_samples": raw_counts.get(symbol, 0),
            "covered_buckets": len(accepted),
            "rejected_buckets": rejected_buckets.get(symbol, 0),
            "first_timestamp_ms": first_timestamp.get(symbol, 0),
            "last_timestamp_ms": last_timestamp.get(symbol, 0),
            "min_samples_per_bucket": minimum,
        }
    return {symbol: dict(rows) for symbol, rows in features.items()}, manifest


def _observation_timestamp_ms(row: dict[str, Any]) -> int:
    source_timestamp = int(_float(row.get("source_timestamp_ms")))
    if source_timestamp > 0:
        return source_timestamp
    raw = str(row.get("created_at") or "").strip()
    if not raw:
        return 0
    try:
        parsed = datetime.fromisoformat(raw.replace("Z", "+00:00"))
    except ValueError:
        return 0
    return int(parsed.timestamp() * 1000)


def _mean(values: list[float]) -> float:
    return sum(values) / len(values) if values else 0.0


def _standard_deviation(values: list[float]) -> float:
    if len(values) < 2:
        return 0.0
    mean = _mean(values)
    return math.sqrt(sum((value - mean) ** 2 for value in values) / len(values))


def _percentile(values: list[float], quantile: float) -> float:
    if not values:
        return 0.0
    ordered = sorted(values)
    position = max(0.0, min(1.0, quantile)) * (len(ordered) - 1)
    lower = int(math.floor(position))
    upper = int(math.ceil(position))
    if lower == upper:
        return ordered[lower]
    fraction = position - lower
    return ordered[lower] * (1.0 - fraction) + ordered[upper] * fraction


def _float(value: Any, default: float = 0.0) -> float:
    try:
        result = float(value)
    except (TypeError, ValueError):
        return default
    return result if math.isfinite(result) else default