"""Table registry & serialization for remote sync.

Maps each syncable table to its model, serializer, and UPSERT configuration.
"""
from __future__ import annotations

from datetime import datetime, date
from decimal import Decimal
from typing import Any


def _dt(val) -> str | None:
    """Serialize datetime/date to ISO string."""
    if val is None:
        return None
    if isinstance(val, datetime):
        return val.isoformat()
    if isinstance(val, date):
        return val.isoformat()
    return str(val)


def _num(val) -> str | None:
    """Serialize Decimal/float to string for precision."""
    if val is None:
        return None
    return str(val)


# ── Serializers ──────────────────────────────────────────────────────────


def serialize_coin(r) -> dict:
    return {
        'id': r.id, 'symbol': r.symbol, 'name': r.name,
        'description': r.description, 'categories': r.categories,
        'genesis_date': _dt(r.genesis_date),
        'market_cap_rank': r.market_cap_rank,
        'image_url': r.image_url, 'icon_thumb_url': r.icon_thumb_url,
        'website': r.website, 'blockchain': r.blockchain,
        'contract_address': r.contract_address,
        'coingecko_score': _num(r.coingecko_score),
        'is_active': r.is_active,
        'asset_type': r.asset_type, 'sector': r.sector,
        'sub_sector': r.sub_sector, 'lot_size': r.lot_size,
        'listing_date': _dt(r.listing_date),
        'created_at': _dt(r.created_at), 'updated_at': _dt(r.updated_at),
    }


def serialize_coin_profile(r) -> dict:
    return {
        'id': r.id, 'asset_id': r.asset_id,
        'current_price_idr': _num(r.current_price_idr),
        'current_price_usd': _num(r.current_price_usd),
        'market_cap_idr': r.market_cap_idr,
        'total_volume_idr': r.total_volume_idr,
        'circulating_supply': _num(r.circulating_supply),
        'total_supply': _num(r.total_supply),
        'max_supply': _num(r.max_supply),
        'price_change_1h': _num(r.price_change_1h),
        'price_change_24h': _num(r.price_change_24h),
        'price_change_7d': _num(r.price_change_7d),
        'price_change_30d': _num(r.price_change_30d),
        'ath_idr': _num(r.ath_idr), 'ath_date': _dt(r.ath_date),
        'atl_idr': _num(r.atl_idr), 'atl_date': _dt(r.atl_date),
        'twitter_followers': r.twitter_followers,
        'reddit_subscribers': r.reddit_subscribers,
        'telegram_users': r.telegram_users,
        'github_stars': r.github_stars,
        'sentiment_votes_up': _num(r.sentiment_votes_up),
        'profile_json': r.profile_json,
        'fetched_at': _dt(r.fetched_at),
    }


def serialize_ohlcv(r) -> dict:
    return {
        'asset_id': r.asset_id, 'timeframe': r.timeframe,
        'source': r.source, 'timestamp': r.timestamp,
        'datetime_wib': _dt(r.datetime_wib),
        'open': _num(r.open), 'high': _num(r.high),
        'low': _num(r.low), 'close': _num(r.close),
        'volume': _num(r.volume),
        'created_at': _dt(r.created_at),
    }


def serialize_signal(r) -> dict:
    return {
        'id': r.id, 'asset_id': r.asset_id,
        'signal_type': r.signal_type, 'confidence': r.confidence,
        'score': _num(r.score),
        'recommended_strategy': r.recommended_strategy,
        'safety_rating': r.safety_rating,
        'entry_price': _num(r.entry_price),
        'stop_loss': _num(r.stop_loss),
        'take_profit_1': _num(r.take_profit_1),
        'take_profit_2': _num(r.take_profit_2),
        'take_profit_3': _num(r.take_profit_3),
        'suggested_size_idr': _num(r.suggested_size_idr),
        'indicators_json': r.indicators_json,
        'algorithm_versions': r.algorithm_versions,
        'status': r.status,
        'created_at': _dt(r.created_at),
        'expires_at': _dt(r.expires_at),
    }


def serialize_prediction(r) -> dict:
    return {
        'id': r.id, 'asset_id': r.asset_id,
        'prediction_date': _dt(r.prediction_date),
        'sequence_num': r.sequence_num,
        'action': r.action,
        'predicted_price': _num(r.predicted_price),
        'quantity': _num(r.quantity),
        'cash_amount': _num(r.cash_amount),
        'fee_amount': _num(r.fee_amount),
        'profit': _num(r.profit),
        'accumulated_units': _num(r.accumulated_units),
        'accumulated_cash': _num(r.accumulated_cash),
        'confidence': _num(r.confidence),
        'reason': r.reason,
        'algorithm_used': r.algorithm_used,
        'is_executed': r.is_executed,
        'created_at': _dt(r.created_at),
    }


def serialize_coin_source(r) -> dict:
    return {
        'id': r.id, 'asset_id': r.asset_id,
        'source': r.source, 'source_asset_id': r.source_asset_id,
        'source_pair': r.source_pair, 'source_url': r.source_url,
        'is_available': r.is_available,
        'last_checked': _dt(r.last_checked),
        'created_at': _dt(r.created_at),
    }


def serialize_ticker(r) -> dict:
    return {
        'id': r.id, 'asset_id': r.asset_id,
        'market_name': r.market_name, 'pair': r.pair,
        'price_idr': _num(r.price_idr),
        'volume_idr': _num(r.volume_idr),
        'spread_pct': _num(r.spread_pct),
        'trust_score': r.trust_score,
        'trade_url': r.trade_url,
        'fetched_at': _dt(r.fetched_at),
    }


def serialize_user(r) -> dict:
    """Serialize user — NEVER includes password_hash."""
    return {
        'id': r.id, 'username': r.username, 'email': r.email,
        'display_name': r.display_name,
        'is_active': r.is_active, 'is_approved': r.is_approved,
        'approved_by': r.approved_by,
        'approved_at': _dt(r.approved_at),
        'created_at': _dt(r.created_at),
        'last_login': _dt(r.last_login),
    }


def serialize_user_settings(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id,
        'setting_key': r.setting_key,
        'setting_value': r.setting_value,
        'category': r.category,
        'updated_at': _dt(r.updated_at),
    }


def serialize_watchlist_group(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id,
        'name': r.name, 'display_order': r.display_order,
        'is_shared': r.is_shared, 'share_token': r.share_token,
        'created_at': _dt(r.created_at),
    }


def serialize_watchlist(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id,
        'asset_id': r.asset_id, 'group_id': r.group_id,
        'display_order': r.display_order, 'notes': r.notes,
        'added_at': _dt(r.added_at),
    }


def serialize_portfolio(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id, 'asset_id': r.asset_id,
        'total_quantity': _num(r.total_quantity),
        'avg_buy_price': _num(r.avg_buy_price),
        'total_invested_idr': _num(r.total_invested_idr),
        'realized_pnl': _num(r.realized_pnl),
        'updated_at': _dt(r.updated_at),
    }


def serialize_trade(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id, 'asset_id': r.asset_id,
        'signal_id': r.signal_id, 'side': r.side,
        'price': _num(r.price), 'quantity': _num(r.quantity),
        'total_value_idr': _num(r.total_value_idr),
        'fee_idr': _num(r.fee_idr),
        'net_value_idr': _num(r.net_value_idr),
        'pnl_idr': _num(r.pnl_idr),
        'is_simulation': r.is_simulation,
        'executed_at': _dt(r.executed_at),
    }


def serialize_capital(r) -> dict:
    return {
        'id': r.id, 'user_id': r.user_id, 'asset_id': r.asset_id,
        'allocated_capital_idr': _num(r.allocated_capital_idr),
        'active_capital_pct': _num(r.active_capital_pct),
        'reserve_capital_pct': _num(r.reserve_capital_pct),
        'updated_at': _dt(r.updated_at),
    }


def serialize_app_settings(r) -> dict:
    return {
        'setting_key': r.setting_key,
        'setting_value': r.setting_value,
        'category': r.category,
        'description': r.description,
        'updated_at': _dt(r.updated_at),
    }


def serialize_range_score(r) -> dict:
    """Serialize range_trading_scores — all columns."""
    cols = [c.name for c in r.__table__.columns]
    d = {}
    for c in cols:
        v = getattr(r, c)
        if isinstance(v, (datetime, date)):
            d[c] = _dt(v)
        elif isinstance(v, Decimal):
            d[c] = _num(v)
        else:
            d[c] = v
    return d


def serialize_bullish_score(r) -> dict:
    """Serialize bullish_momentum_scores — all columns."""
    cols = [c.name for c in r.__table__.columns]
    d = {}
    for c in cols:
        v = getattr(r, c)
        if isinstance(v, (datetime, date)):
            d[c] = _dt(v)
        elif isinstance(v, Decimal):
            d[c] = _num(v)
        else:
            d[c] = v
    return d


# ── Table Registry ───────────────────────────────────────────────────────


def get_table_registry() -> dict[str, dict]:
    """Lazy-load registry to avoid circular imports."""
    from app.models.asset import Asset, AssetProfile
    from app.models.ohlcv import OHLCVData
    from app.models.signal import TradingSignal
    from app.models.prediction_queue import PredictionQueue
    from app.models.asset_source import AssetSourceMapping
    from app.models.ticker import MarketTicker
    from app.models.user import User
    from app.models.user_settings import UserSettings
    from app.models.watchlist import Watchlist
    from app.models.watchlist_group import WatchlistGroup
    from app.models.portfolio import Portfolio, TradeHistory
    from app.models.settings import AppSettings, CapitalAllocation

    # Try importing score models (may not exist in all deployments)
    try:
        from app.models.range_score import RangeTradingScore
    except ImportError:
        RangeTradingScore = None
    try:
        from app.models.bullish_score import BullishMomentumScore
    except ImportError:
        BullishMomentumScore = None

    registry = {
        # ── Local → Production (computed data) ──
        'assets': {
            'model': Asset,
            'timestamp_col': 'updated_at',
            'serialize': serialize_coin,
            'upsert_key': ['id'],
            'update_cols': [
                'symbol', 'name', 'description', 'categories', 'genesis_date',
                'market_cap_rank', 'image_url', 'icon_thumb_url', 'website',
                'blockchain', 'contract_address', 'coingecko_score', 'is_active',
                'asset_type', 'sector', 'sub_sector', 'lot_size', 'listing_date',
                'updated_at',
            ],
            'batch_size': 500,
            'direction': 'push',
        },
        'coin_source_mappings': {
            'model': AssetSourceMapping,
            'timestamp_col': 'last_checked',
            'serialize': serialize_coin_source,
            'upsert_key': ['asset_id', 'source'],
            'update_cols': [
                'source_asset_id', 'source_pair', 'source_url',
                'is_available', 'last_checked',
            ],
            'batch_size': 500,
            'direction': 'push',
        },
        'ohlcv_data': {
            'model': OHLCVData,
            'timestamp_col': 'created_at',
            'serialize': serialize_ohlcv,
            'upsert_key': ['asset_id', 'timeframe', 'source', 'datetime_wib'],
            'update_cols': ['open', 'high', 'low', 'close', 'volume'],
            'batch_size': 2000,
            'direction': 'push',
        },
        'coin_profiles': {
            'model': AssetProfile,
            'timestamp_col': 'fetched_at',
            'serialize': serialize_coin_profile,
            'upsert_key': ['asset_id', 'fetched_at'],
            'update_cols': [],  # append-only
            'batch_size': 500,
            'direction': 'push',
        },
        'trading_signals': {
            'model': TradingSignal,
            'timestamp_col': 'created_at',
            'serialize': serialize_signal,
            'upsert_key': ['id'],
            'update_cols': [
                'signal_type', 'confidence', 'score', 'recommended_strategy',
                'safety_rating', 'entry_price', 'stop_loss',
                'take_profit_1', 'take_profit_2', 'take_profit_3',
                'suggested_size_idr', 'indicators_json', 'algorithm_versions',
                'status', 'expires_at',
            ],
            'batch_size': 500,
            'direction': 'push',
        },
        'prediction_queues': {
            'model': PredictionQueue,
            'timestamp_col': 'created_at',
            'serialize': serialize_prediction,
            'upsert_key': ['asset_id', 'prediction_date', 'sequence_num'],
            'update_cols': [
                'action', 'predicted_price', 'quantity', 'cash_amount',
                'fee_amount', 'profit', 'accumulated_units', 'accumulated_cash',
                'confidence', 'reason', 'algorithm_used', 'is_executed',
            ],
            'batch_size': 1000,
            'direction': 'push',
        },
        'market_tickers': {
            'model': MarketTicker,
            'timestamp_col': 'fetched_at',
            'serialize': serialize_ticker,
            'upsert_key': ['asset_id', 'market_name', 'pair'],
            'update_cols': [
                'price_idr', 'volume_idr', 'spread_pct',
                'trust_score', 'trade_url', 'fetched_at',
            ],
            'batch_size': 500,
            'direction': 'push',
        },

        # ── Production → Local (user data) ──
        'users': {
            'model': User,
            'timestamp_col': 'created_at',
            'serialize': serialize_user,
            'upsert_key': ['username'],
            'update_cols': [
                'email', 'display_name', 'is_active', 'is_approved',
                'approved_by', 'approved_at', 'last_login',
            ],
            'batch_size': 100,
            'direction': 'pull',
        },
        'user_settings': {
            'model': UserSettings,
            'timestamp_col': 'updated_at',
            'serialize': serialize_user_settings,
            'upsert_key': ['user_id', 'setting_key'],
            'update_cols': ['setting_value', 'category', 'updated_at'],
            'batch_size': 500,
            'direction': 'pull',
        },
        'watchlist_groups': {
            'model': WatchlistGroup,
            'timestamp_col': 'created_at',
            'serialize': serialize_watchlist_group,
            'upsert_key': ['id'],
            'update_cols': ['name', 'display_order', 'is_shared', 'share_token'],
            'batch_size': 100,
            'direction': 'pull',
        },
        'watchlist': {
            'model': Watchlist,
            'timestamp_col': 'added_at',
            'serialize': serialize_watchlist,
            'upsert_key': ['user_id', 'asset_id', 'group_id'],
            'update_cols': ['display_order', 'notes'],
            'batch_size': 500,
            'direction': 'pull',
        },
        'portfolio': {
            'model': Portfolio,
            'timestamp_col': 'updated_at',
            'serialize': serialize_portfolio,
            'upsert_key': ['user_id', 'asset_id'],
            'update_cols': [
                'total_quantity', 'avg_buy_price',
                'total_invested_idr', 'realized_pnl', 'updated_at',
            ],
            'batch_size': 500,
            'direction': 'pull',
        },
        'trade_history': {
            'model': TradeHistory,
            'timestamp_col': 'executed_at',
            'serialize': serialize_trade,
            'upsert_key': ['id'],
            'update_cols': [],  # insert-only, never update trades
            'batch_size': 1000,
            'direction': 'pull',
        },
        'capital_allocations': {
            'model': CapitalAllocation,
            'timestamp_col': 'updated_at',
            'serialize': serialize_capital,
            'upsert_key': ['user_id', 'asset_id'],
            'update_cols': [
                'allocated_capital_idr', 'active_capital_pct',
                'reserve_capital_pct', 'updated_at',
            ],
            'batch_size': 500,
            'direction': 'pull',
        },
        'app_settings': {
            'model': AppSettings,
            'timestamp_col': 'updated_at',
            'serialize': serialize_app_settings,
            'upsert_key': ['setting_key'],
            'update_cols': ['setting_value', 'category', 'description', 'updated_at'],
            'batch_size': 100,
            'direction': 'pull',
        },
    }

    # Score tables (optional — may not exist)
    if RangeTradingScore is not None:
        registry['range_trading_scores'] = {
            'model': RangeTradingScore,
            'timestamp_col': 'computed_at',
            'serialize': serialize_range_score,
            'upsert_key': ['asset_id'],
            'update_cols': '__all__',  # sentinel: update all non-key columns
            'batch_size': 500,
            'direction': 'push',
        }
    if BullishMomentumScore is not None:
        registry['bullish_momentum_scores'] = {
            'model': BullishMomentumScore,
            'timestamp_col': 'computed_at',
            'serialize': serialize_bullish_score,
            'upsert_key': ['asset_id'],
            'update_cols': '__all__',
            'batch_size': 500,
            'direction': 'push',
        }

    return registry


# ── Direction helpers ────────────────────────────────────────────────────

def get_push_tables() -> list[str]:
    """Tables that should be pushed from local → production."""
    reg = get_table_registry()
    return [t for t, cfg in reg.items() if cfg['direction'] == 'push']


def get_pull_tables() -> list[str]:
    """Tables that should be pulled from production → local."""
    reg = get_table_registry()
    return [t for t, cfg in reg.items() if cfg['direction'] == 'pull']