from __future__ import annotations

from pathlib import Path
from sqlite3 import Connection

from .common import ImportResult, clean, create_import_session, require_text, source_hash, stable_id, utc_now
from .dry_run import read_csv_rows

REQUIRED_COLUMNS = {"asset_class", "name", "currency"}


def _instrument_key(row: dict[str, str]) -> tuple[str, str, str]:
    isin = clean(row.get("isin")).upper()
    ticker = clean(row.get("ticker")).upper()
    name = clean(row.get("name"))
    return isin, ticker, name


def _existing_instrument(conn: Connection, row: dict[str, str]) -> bool:
    isin, ticker, name = _instrument_key(row)
    if isin:
        existing = conn.execute("SELECT 1 FROM instruments WHERE isin = ? LIMIT 1", (isin,)).fetchone()
        if existing:
            return True
    if ticker:
        existing = conn.execute("SELECT 1 FROM instruments WHERE ticker = ? AND name = ? LIMIT 1", (ticker, name)).fetchone()
        if existing:
            return True
    return False


def import_instruments_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
    rows_new = rows_existing = 0
    validated: list[dict[str, str]] = []
    for line, row in enumerate(rows, start=2):
        try:
            asset_class = require_text(row, "asset_class", line)
            name = require_text(row, "name", line)
            currency = require_text(row, "currency", line).upper()
            isin, ticker, _ = _instrument_key(row)
            instrument_id = stable_id("instrument", isin or ticker or name, currency)
            if _existing_instrument(conn, {**row, "name": name, "ticker": ticker, "isin": isin}):
                rows_existing += 1
            else:
                rows_new += 1
            validated.append({
                "instrument_id": instrument_id,
                "asset_class": asset_class,
                "name": name,
                "ticker": ticker,
                "isin": isin,
                "exchange": clean(row.get("exchange")),
                "currency": currency,
                "country": clean(row.get("country")),
                "sector": clean(row.get("sector")),
                "data_provider_primary": clean(row.get("data_provider_primary")),
                "notes": clean(row.get("notes")),
            })
        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:
        now = utc_now()
        for item in validated:
            conn.execute(
                """
                INSERT OR IGNORE INTO instruments(
                    instrument_id, asset_class, name, ticker, isin, exchange, currency,
                    country, sector, data_provider_primary, notes, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    item["instrument_id"], item["asset_class"], item["name"], item["ticker"], item["isin"],
                    item["exchange"], item["currency"], item["country"], item["sector"],
                    item["data_provider_primary"], item["notes"], now,
                ),
            )
    session_id = create_import_session(
        conn,
        import_type="instruments",
        source_filename=source_filename,
        file_hash=source_hash(path),
        status=status,
        rows_total=len(rows),
        rows_imported=rows_new if commit and not errors else 0,
        rows_failed=len(errors),
        errors=errors,
        notes="dry run" if not commit else None,
    )
    return ImportResult(session_id, "instruments", status, len(rows), rows_new if not errors else 0, rows_existing, len(errors), errors)