from __future__ import annotations
from decimal import Decimal, InvalidOperation
from pathlib import Path
from sqlite3 import Connection
from jarvis_finance.crypto.assets import create_crypto_asset
from jarvis_finance.crypto.holdings import create_initial_holding_snapshot
from .common import ImportResult, clean, create_import_session, require_text, source_hash
from .dry_run import read_csv_rows
from .row_hash import compute_row_hash
REQUIRED_COLUMNS={"wallet_name","coin_name","symbol","quantity","verification_status"}

def dec(v,line,field):
    try: return Decimal(clean(v))
    except (InvalidOperation, ValueError) as exc: raise ValueError(f"line {line}: {field} invalid decimal") from exc

def _wallet(conn,name):
    r=conn.execute("SELECT wallet_id FROM crypto_wallets WHERE wallet_name=?",(name,)).fetchone(); return r['wallet_id'] if r else None

def _asset(conn,symbol,cg,coin):
    if cg:
        r=conn.execute("SELECT asset_id FROM crypto_assets WHERE coingecko_id=?",(cg,)).fetchone()
        if r: return r['asset_id']
    rows=conn.execute("SELECT asset_id FROM crypto_assets WHERE symbol=?",(symbol,)).fetchall()
    return rows[0]['asset_id'] if len(rows)==1 else None

def import_crypto_holdings_csv(conn:Connection,path:str|Path,*,commit:bool,source_filename:str|None=None)->ImportResult:
    rows,errors=read_csv_rows(path,REQUIRED_COLUMNS); source_filename=source_filename or Path(path).name
    new=existing=0; vals=[]; seen=set()
    for line,row in enumerate(rows,start=2):
        try:
            rh=compute_row_hash(row)
            if rh in seen: raise ValueError(f"line {line}: duplicate row hash in source file")
            seen.add(rh); wallet=require_text(row,'wallet_name',line); coin=require_text(row,'coin_name',line); symbol=require_text(row,'symbol',line).upper(); qty=dec(row.get('quantity'),line,'quantity')
            if qty<=0: raise ValueError(f"line {line}: quantity must be > 0")
            wid=_wallet(conn,wallet)
            if not wid: raise ValueError(f"line {line}: wallet not found")
            cg=clean(row.get('coingecko_id')) or None; aid=_asset(conn,symbol,cg,coin)
            exists=False
            if aid: exists=conn.execute("SELECT 1 FROM crypto_holdings WHERE wallet_id=? AND asset_id=?",(wid,aid)).fetchone() is not None
            existing += 1 if exists else 0; new += 0 if exists else 1
            vals.append((aid,coin,symbol,cg,wid,qty,require_text(row,'verification_status',line),clean(row.get('last_verified_at')) or None,dec(row.get('legacy_snapshot_value_chf'),line,'legacy_snapshot_value_chf') if clean(row.get('legacy_snapshot_value_chf')) else None,clean(row.get('notes')) or 'Initial crypto snapshot import'))
        except ValueError as exc: errors.append(str(exc))
    status='failed' if errors else ('committed' if commit else 'dry_run_ok')
    if commit and not errors:
        for aid,coin,symbol,cg,wid,qty,vs,last,legacy,note in vals:
            if not aid: aid=create_crypto_asset(conn,coin_name=coin,symbol=symbol,coingecko_id=cg)
            if conn.execute("SELECT 1 FROM crypto_holdings WHERE wallet_id=? AND asset_id=?",(wid,aid)).fetchone(): continue
            create_initial_holding_snapshot(conn,asset_id=aid,wallet_id=wid,quantity=qty,verification_status=vs,last_verified_at=last,legacy_snapshot_value_chf=legacy,note=note)
    sid=create_import_session(conn,import_type='crypto_holdings',source_filename=source_filename,file_hash=source_hash(path),status=status,rows_total=len(rows),rows_imported=new if commit and not errors else 0,rows_failed=len(errors),errors=errors,notes='dry run' if not commit else None)
    return ImportResult(sid,'crypto_holdings',status,len(rows),new if not errors else 0,existing,len(errors),errors)