from __future__ import annotations

from decimal import Decimal
from pathlib import Path
from sqlite3 import Connection

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.imports.accounts_importer import import_accounts_csv
from jarvis_finance.imports.cash_balances_importer import import_cash_balances_csv
from jarvis_finance.imports.crypto_holdings_importer import import_crypto_holdings_csv
from jarvis_finance.imports.crypto_transactions_importer import import_crypto_transactions_csv
from jarvis_finance.imports.crypto_wallets_importer import import_crypto_wallets_csv
from jarvis_finance.imports.instruments_importer import import_instruments_csv
from jarvis_finance.imports.transactions_importer import import_transactions_csv
from jarvis_finance.imports.watchlist_importer import import_watchlist_csv
from jarvis_finance.imports.common import stable_id, utc_now
from jarvis_finance.crypto.assets import create_crypto_asset
from jarvis_finance.crypto.holdings import create_initial_holding_snapshot
from jarvis_finance.crypto.transactions import record_crypto_transfer
from jarvis_finance.market.providers import PriceQuote, store_crypto_price
from jarvis_finance.quality.alerts import create_alert
from jarvis_finance.storage.migrations import apply_migrations

ROOT = Path(__file__).resolve().parents[3]
DEFAULT_FIXTURES = ROOT / "tests" / "fixtures"


def load_synthetic_demo_data(conn: Connection, fixtures_dir: Path | None = None) -> None:
    """Load deterministic synthetic fixtures into an existing SQLite connection.

    This function is explicit-only: dashboard pages never call it automatically.
    It is safe for tests and local demo DB creation because it reads only tests/fixtures.
    """
    apply_migrations(conn)
    fixtures = fixtures_dir or DEFAULT_FIXTURES
    import_accounts_csv(conn, fixtures / "accounts.csv", commit=True)
    import_instruments_csv(conn, fixtures / "instruments.csv", commit=True)
    import_transactions_csv(conn, fixtures / "transactions.csv", commit=True)
    import_crypto_wallets_csv(conn, fixtures / "crypto_wallets.csv", commit=True)
    # Current canonical crypto importers are intentionally stricter than early fixtures.
    # Seed the local demo DB via domain APIs to keep this explicit and synthetic.
    _add_demo_crypto(conn)
    _add_demo_watchlist(conn)
    import_cash_balances_csv(conn, fixtures / "cash_balances_initial.csv", commit=True)
    _add_demo_prices(conn)
    _add_demo_alerts_and_audit(conn)
    conn.commit()


def _add_demo_crypto(conn: Connection) -> None:
    btc = create_crypto_asset(conn, coin_name="Demo Bitcoin", symbol="DBTC", coingecko_id="demo-bitcoin")
    eth = create_crypto_asset(conn, coin_name="Demo Ether", symbol="DETH", coingecko_id="demo-ether")
    ledger = conn.execute("SELECT wallet_id FROM crypto_wallets WHERE wallet_name='Ledger Demo'").fetchone()
    kraken = conn.execute("SELECT wallet_id FROM crypto_wallets WHERE wallet_name='Kraken Demo'").fetchone()
    if ledger and not conn.execute("SELECT 1 FROM crypto_holdings WHERE wallet_id=? AND asset_id=?", (ledger["wallet_id"], btc)).fetchone():
        create_initial_holding_snapshot(conn, asset_id=btc, wallet_id=ledger["wallet_id"], quantity=Decimal("0.10"), verification_status="verified", last_verified_at="2026-01-31T12:00:00Z", note="Synthetischer Bestand")
    if kraken and not conn.execute("SELECT 1 FROM crypto_holdings WHERE wallet_id=? AND asset_id=?", (kraken["wallet_id"], eth)).fetchone():
        create_initial_holding_snapshot(conn, asset_id=eth, wallet_id=kraken["wallet_id"], quantity=Decimal("1.50"), verification_status="verified", last_verified_at="2026-01-31T12:00:00Z", note="Synthetischer Bestand")
    if ledger and kraken and not conn.execute("SELECT 1 FROM crypto_transactions WHERE tx_hash='synthetic_demo_hash_001'").fetchone():
        record_crypto_transfer(conn, asset_id=btc, from_wallet_id=ledger["wallet_id"], to_wallet_id=kraken["wallet_id"], quantity=Decimal("0.01"), fee_quantity=Decimal("0.0001"), tx_hash="synthetic_demo_hash_001", note="Synthetischer Transfer")


def _add_demo_watchlist(conn: Connection) -> None:
    if conn.execute("SELECT 1 FROM watchlist WHERE name='Demo World ETF'").fetchone():
        return
    now = utc_now()
    conn.execute(
        """
        INSERT INTO watchlist(watchlist_id, name, asset_class, reason, status, target_entry_price,
                              target_entry_currency, investment_case, bear_case, created_at)
        VALUES (?, 'Demo World ETF', 'ETF', 'Core-Kandidat', 'active', '100', 'CHF',
                'Synthetischer Case', 'Synthetischer Bear Case', ?)
        """,
        (stable_id("watch", "Demo World ETF", "ETF"), now),
    )


def _add_demo_prices(conn: Connection) -> None:
    prices = {"demo-bitcoin": Decimal("40000.00"), "demo-ether": Decimal("2500.00")}
    for row in conn.execute("SELECT asset_id, coingecko_id FROM crypto_assets WHERE coingecko_id IS NOT NULL AND coingecko_id != ''").fetchall():
        price = prices.get(row["coingecko_id"], Decimal("1.00"))
        existing = conn.execute("SELECT 1 FROM crypto_prices WHERE asset_id=? AND price_currency='CHF'", (row["asset_id"],)).fetchone()
        if existing:
            continue
        store_crypto_price(conn, asset_id=row["asset_id"], quote=PriceQuote(row["coingecko_id"], "CHF", price, provider="synthetic-cache", provider_timestamp="2026-02-01T00:00:00Z"))
    instrument = conn.execute("SELECT instrument_id FROM instruments ORDER BY name LIMIT 1").fetchone()
    if instrument and not conn.execute("SELECT 1 FROM market_prices WHERE instrument_id=?", (instrument["instrument_id"],)).fetchone():
        now = utc_now()
        conn.execute(
            """
            INSERT INTO market_prices(market_price_id, instrument_id, price_date, close, currency, provider, quality_status, created_at)
            VALUES (?, ?, '2026-02-01', '112.50', 'CHF', 'synthetic-cache', 'ok', ?)
            """,
            (stable_id("mprice", instrument["instrument_id"], "2026-02-01"), instrument["instrument_id"], now),
        )


def _add_demo_alerts_and_audit(conn: Connection) -> None:
    create_alert(
        conn,
        priority="warnung",
        category="market_data",
        entity_type="crypto_price",
        entity_id="synthetic-demo",
        rule_id="demo_stale_price",
        message="Synthetic demo alert: verify price freshness before using real data.",
        evidence={"source": "synthetic"},
        fingerprint="synthetic-demo-stale-price",
    )
    if not conn.execute("SELECT 1 FROM audit_log WHERE source='dashboard_demo'").fetchone():
        record_audit_event(
            conn,
            source="dashboard_demo",
            action="load_synthetic_demo_data",
            entity_type="demo_dataset",
            entity_id="synthetic-fixtures",
            new_values={"fixtures": "tests/fixtures", "real_data": False},
            confirmed=True,
            created_by="system",
        )