from __future__ import annotations

import zipfile
import uuid
from pathlib import Path

from jarvis_finance.dashboard import data as dashboard_data
from jarvis_finance.imports.broker_mapping import (
    assert_productive_import_disabled,
    assert_write_enabled,
    confirm_review_item_account_mapping,
    confirm_review_item_instrument_mapping,
    confirm_review_item_snapshot_date,
    confirm_review_item_mapping,
    create_broker_import_review_item,
    get_broker_review_items,
    get_dry_run_sessions,
    ignore_review_item,
    mark_review_item_resolved,
    run_broker_import_dry_run,
    update_dry_run_session_status,
    update_review_item_isin,
    update_review_item_ticker_exchange,
)
from jarvis_finance.imports.broker_parsers import parse_postfinance_docx, parse_raiffeisen_xlsx, parse_true_wealth_docx
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations, utc_now


def _docx(path: Path, rows: list[list[str]]) -> None:
    def tc(text: str) -> str:
        return f"<w:tc><w:p><w:r><w:t>{text}</w:t></w:r></w:p></w:tc>"
    trs = "".join("<w:tr>" + "".join(tc(c) for c in row) + "</w:tr>" for row in rows)
    xml = f'''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:body><w:tbl>{trs}</w:tbl></w:body></w:document>'''
    with zipfile.ZipFile(path, "w") as zf:
        zf.writestr("[Content_Types].xml", "")
        zf.writestr("word/document.xml", xml)


def _xlsx(path: Path, rows: list[list[str]]) -> None:
    shared: list[str] = []
    index: dict[str, int] = {}
    sheet_rows = []
    for r_idx, row in enumerate(rows, start=1):
        cells = []
        for c_idx, value in enumerate(row):
            if value not in index:
                index[value] = len(shared); shared.append(value)
            ref = chr(ord('A') + c_idx) + str(r_idx)
            cells.append(f'<c r="{ref}" t="s"><v>{index[value]}</v></c>')
        sheet_rows.append(f'<row r="{r_idx}">' + ''.join(cells) + '</row>')
    shared_xml = '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' + ''.join(f'<si><t>{s}</t></si>' for s in shared) + '</sst>'
    sheet_xml = '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>' + ''.join(sheet_rows) + '</sheetData></worksheet>'
    with zipfile.ZipFile(path, "w") as zf:
        zf.writestr("xl/sharedStrings.xml", shared_xml)
        zf.writestr("xl/worksheets/sheet1.xml", sheet_xml)
        zf.writestr("[Content_Types].xml", "")


def conn():
    c = connect_memory()
    apply_migrations(c)
    return c


def seed_instrument(c, *, instrument_id='inst-1', asset_class='etf', isin='CH0000000001', ticker='METF', exchange='SIX', currency='CHF'):
    c.execute("INSERT INTO instruments(instrument_id, asset_class, name, ticker, isin, exchange, currency, created_at) VALUES (?,?,?,?,?,?,?,?)", (instrument_id, asset_class, 'Mapped ETF', ticker, isin, exchange, currency, utc_now()))


def seed_accounts(c):
    c.execute("INSERT INTO platforms(platform_id, name, platform_type, created_at) VALUES ('pf','PostFinance','broker',?)", (utc_now(),))
    c.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct-broker','pf','Brokerage','brokerage','CHF',?)", (utc_now(),))
    c.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, created_at) VALUES ('acct-cash','pf','Cash','cash','CHF',?)", (utc_now(),))


def seed_review_item(c, *, source='PostFinance', asset_class='etf', currency='CHF', flags=None, quantity=True, market_value=True, status='open'):
    dry_id = f"dry-{uuid.uuid4()}"
    c.execute("INSERT INTO broker_import_dry_runs(dry_run_id, source_name, source_platform, source_file_label, source_file_type, source_hash, source_filename_hash, detected_sections_json, rows_total, quality_flags_json, summary_json, created_at, snapshot_date_status) VALUES (?, ?, ?, '[redacted]', 'docx', 'h', 'h', '[]', 1, '{}', '{}', ?, 'missing')", (dry_id, source, source, utc_now()))
    return create_broker_import_review_item(c, dry_run_id=dry_id, source_platform=source, source_file_type='docx', source_row_ref='row-1', source_label='Synthetic Label', detected_asset_class=asset_class, detected_currency=currency, detected_quantity_present=quantity, detected_market_value_present=market_value, quality_flags=flags or ['missing_isin', 'missing_ticker', 'snapshot_only', 'cost_basis_uncertain'], review_status=status)


def test_synthetic_postfinance_parser_recognizes_candidates_and_quality_flags(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_postfinance.docx"
    _docx(path, [["Produkt", "Menge", "Währung", "Marktwert", "Stichtag 31.12.2025"], ["Synthetic ETF", "Menge vorhanden", "CHF", "Marktwert vorhanden", "Einstand legacy"], ["Cash Konto", "", "CHF", "", ""]])
    parsed = parse_postfinance_docx(path)
    assert parsed.source_platform == "PostFinance"
    assert parsed.candidate_positions == 1
    assert parsed.candidate_cash_rows == 1
    flags = parsed.quality_flags_summary
    assert flags["missing_isin"] == 1
    assert flags["missing_ticker"] == 1
    assert flags["market_value_legacy"] == 1


def test_synthetic_true_wealth_parser_recognizes_candidates_and_missing_fx(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_true_wealth.docx"
    _docx(path, [["Position", "Anteile", "Währung", "Wert", "Stichtag 31.12.2025"], ["Global ETF", "Anteile vorhanden", "USD", "Wert vorhanden", "Portfolio"], ["Cash USD", "", "USD", "Wert vorhanden", ""]])
    parsed = parse_true_wealth_docx(path)
    assert parsed.candidate_positions == 1
    assert parsed.candidate_cash_rows == 1
    assert parsed.quality_flags_summary["missing_fx"] == 2
    assert parsed.quality_flags_summary["cost_basis_uncertain"] == 1


def test_synthetic_raiffeisen_parser_recognizes_cash_and_aggregate_control_snapshot(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_raiffeisen.xlsx"
    _xlsx(path, [["Label", "Typ", "Währung", "Saldo", "Datum 31.12.2025"], ["Privatkonto", "Konto", "CHF", "Saldo vorhanden", ""], ["Depot Anlagen gesamt", "Anlage", "CHF", "Wert vorhanden", ""]])
    parsed = parse_raiffeisen_xlsx(path)
    assert parsed.candidate_cash_rows == 1
    assert parsed.candidate_positions == 1
    assert parsed.quality_flags_summary["cash_snapshot_candidate"] == 1
    assert parsed.quality_flags_summary["aggregate_only"] == 1
    assert parsed.quality_flags_summary["missing_instrument_details"] == 1


def test_dry_run_pipeline_writes_summary_and_review_items_without_productive_positions(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_postfinance.docx"
    _docx(path, [["Produkt", "Menge", "Währung", "Marktwert"], ["Name Only ETF", "Menge vorhanden", "CHF", "Marktwert vorhanden"], ["Cash Konto", "", "CHF", ""]])
    c = conn()
    summary = run_broker_import_dry_run(c, parse_postfinance_docx(path), source_filename=path.name)
    assert summary["rows_total"] == 3
    assert summary["review_items_created"] == 2
    assert summary["blocked_positions"] == 1
    assert c.execute("SELECT COUNT(*) AS n FROM broker_import_dry_runs").fetchone()["n"] == 1
    assert c.execute("SELECT COUNT(*) AS n FROM broker_import_review_items").fetchone()["n"] == 2
    assert c.execute("SELECT COUNT(*) AS n FROM positions_snapshot").fetchone()["n"] == 0


def test_name_without_isin_stays_needs_manual_review_via_review_item(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_postfinance.docx"
    _docx(path, [["Produkt", "Menge", "Währung"], ["Name Only ETF", "Menge vorhanden", "CHF"]])
    c = conn()
    run_broker_import_dry_run(c, parse_postfinance_docx(path), source_filename=path.name)
    item = c.execute("SELECT * FROM broker_import_review_items").fetchone()
    assert item["review_status"] == "open"
    assert "missing_isin" in item["quality_flags_json"]


def test_raiffeisen_aggregate_only_is_blocked_not_productive_position(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_raiffeisen.xlsx"
    _xlsx(path, [["Label", "Typ", "Währung"], ["Depot Anlagen gesamt", "Anlage", "CHF"]])
    c = conn()
    run_broker_import_dry_run(c, parse_raiffeisen_xlsx(path), source_filename=path.name)
    row = c.execute("SELECT review_status, quality_flags_json FROM broker_import_review_items").fetchone()
    assert row["review_status"] == "blocked"
    assert "aggregate_only" in row["quality_flags_json"]
    assert c.execute("SELECT COUNT(*) AS n FROM positions_snapshot").fetchone()["n"] == 0


def test_manual_review_queue_and_data_quality_aggregate_review_flags(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_true_wealth.docx"
    _docx(path, [["Position", "Währung"], ["Global ETF", "USD"]])
    c = conn()
    run_broker_import_dry_run(c, parse_true_wealth_docx(path), source_filename=path.name)
    assert get_broker_review_items(c)
    dq = {r["check"]: r["count"] for r in dashboard_data.get_data_quality(c)}
    assert dq["Review Flag: missing_isin"] == "1"
    assert dq["Review Flag: missing_fx"] == "1"


def test_data_quality_includes_blocked_aggregate_only_review_flags(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_raiffeisen.xlsx"
    _xlsx(path, [["Label", "Typ", "Währung"], ["Depot Anlagen gesamt", "Anlage", "CHF"]])
    c = conn()
    run_broker_import_dry_run(c, parse_raiffeisen_xlsx(path), source_filename=path.name)
    dq = {r["check"]: r["count"] for r in dashboard_data.get_data_quality(c)}
    assert dq["Review Flag: aggregate_only"] == "1"
    assert dq["Review Flag: missing_instrument_details"] == "1"


def test_safe_mode_default_read_only_and_commit_disabled() -> None:
    c = conn()
    status = dashboard_data.get_safe_mode_status(c)
    assert status["write_mode"] == "Read-only"
    try:
        assert_write_enabled(write_enabled=False)
    except PermissionError:
        pass
    else:
        raise AssertionError("read-only commit guard did not block")


def test_review_item_mapping_confirmation_writes_audit(tmp_path: Path) -> None:
    path = tmp_path / "synthetic_postfinance.docx"
    _docx(path, [["Produkt", "Menge", "Währung"], ["Mapped ETF", "Menge vorhanden", "CHF"]])
    c = conn(); seed_instrument(c)
    run_broker_import_dry_run(c, parse_postfinance_docx(path), source_filename=path.name)
    review_item_id = c.execute("SELECT review_item_id FROM broker_import_review_items").fetchone()["review_item_id"]
    audit_id = confirm_review_item_mapping(c, review_item_id=review_item_id, instrument_id="inst-1", note="Synthetic B2 confirmation")
    assert audit_id
    assert c.execute("SELECT COUNT(*) AS n FROM audit_log WHERE entity_id=?", (review_item_id,)).fetchone()["n"] == 1


def test_no_real_broker_files_in_repo() -> None:
    repo = Path('/home/agent/.hermes/repos/FinanceManager')
    forbidden = [p for p in repo.rglob('*') if p.suffix.lower() in {'.xlsx', '.docx', '.pdf'} and 'tests' not in p.parts and 'examples' not in p.parts]
    assert forbidden == []


def test_b4_review_queue_filters_by_source_dry_run_and_quality_flag(tmp_path: Path) -> None:
    c = conn()
    p1 = tmp_path / "pf.docx"; _docx(p1, [["Produkt", "Menge", "Währung"], ["ETF A", "Menge", "CHF"]])
    p2 = tmp_path / "tw.docx"; _docx(p2, [["Position", "Währung"], ["ETF B", "USD"]])
    s1 = run_broker_import_dry_run(c, parse_postfinance_docx(p1), source_filename=p1.name)
    s2 = run_broker_import_dry_run(c, parse_true_wealth_docx(p2), source_filename=p2.name)
    assert len(get_broker_review_items(c, source_platform="PostFinance")) == 1
    assert len(get_broker_review_items(c, dry_run_id=s2["dry_run_id"])) == 1
    assert len(get_broker_review_items(c, quality_flag="missing_fx")) == 1


def test_b4_isin_ticker_exchange_updates_write_audit_and_name_only_not_mapped() -> None:
    c = conn(); item = seed_review_item(c)
    update_review_item_isin(c, review_item_id=item, isin="CH0000000001", note="synthetic isin")
    update_review_item_ticker_exchange(c, review_item_id=item, ticker="METF", note="synthetic ticker")
    row = c.execute("SELECT * FROM broker_import_review_items WHERE review_item_id=?", (item,)).fetchone()
    assert row["isin"] == "CH0000000001"
    assert row["ticker"] == "METF"
    assert "missing_isin" not in row["quality_flags_json"]
    assert row["review_status"] == "open"
    assert c.execute("SELECT COUNT(*) AS n FROM audit_log WHERE entity_id=?", (item,)).fetchone()["n"] == 2


def test_b4_ticker_without_exchange_stays_review_needed() -> None:
    c = conn(); item = seed_review_item(c)
    update_review_item_ticker_exchange(c, review_item_id=item, ticker="METF", note="ticker only")
    row = c.execute("SELECT import_readiness_status, quality_flags_json FROM broker_import_review_items WHERE review_item_id=?", (item,)).fetchone()
    assert row["import_readiness_status"] in {"not_ready", "review_needed"}
    assert "ticker_without_exchange" in row["quality_flags_json"]


def test_b4_instrument_mapping_with_isin_can_be_ready_for_import_for_equity() -> None:
    c = conn(); seed_instrument(c, asset_class="equity"); seed_accounts(c); item = seed_review_item(c, asset_class="equity")
    update_review_item_isin(c, review_item_id=item, isin="CH0000000001", note="isin")
    confirm_review_item_account_mapping(c, review_item_id=item, account_id="acct-broker", note="account")
    confirm_review_item_snapshot_date(c, review_item_id=item, note="snapshot confirmed")
    confirm_review_item_instrument_mapping(c, review_item_id=item, instrument_id="inst-1", note="instrument")
    row = c.execute("SELECT import_readiness_status FROM broker_import_review_items WHERE review_item_id=?", (item,)).fetchone()
    assert row["import_readiness_status"] == "ready_for_import"


def test_b4_ready_for_import_for_etf_and_account_mapping_audit() -> None:
    c = conn(); seed_instrument(c); seed_accounts(c); item = seed_review_item(c, asset_class="etf")
    update_review_item_isin(c, review_item_id=item, isin="CH0000000001", note="isin")
    audit = confirm_review_item_account_mapping(c, review_item_id=item, account_id="acct-broker", note="account")
    confirm_review_item_snapshot_date(c, review_item_id=item, note="snapshot")
    confirm_review_item_instrument_mapping(c, review_item_id=item, instrument_id="inst-1", note="instrument")
    assert audit
    assert c.execute("SELECT import_readiness_status FROM broker_import_review_items WHERE review_item_id=?", (item,)).fetchone()["import_readiness_status"] == "ready_for_import"


def test_b4_ready_for_import_for_cash() -> None:
    c = conn(); seed_accounts(c); item = seed_review_item(c, asset_class="cash", flags=["cash_snapshot_candidate"], quantity=False, market_value=True)
    confirm_review_item_account_mapping(c, review_item_id=item, account_id="acct-cash", note="cash account")
    confirm_review_item_snapshot_date(c, review_item_id=item, note="snapshot")
    # cash has no instrument mapping; reviewer confirmation is modelled by explicit metadata confirmation here
    c.execute("UPDATE broker_import_review_items SET reviewer_confirmed=1 WHERE review_item_id=?", (item,))
    from jarvis_finance.imports.broker_mapping import refresh_review_item_readiness
    assert refresh_review_item_readiness(c, item) == "ready_for_import"


def test_b4_ignore_requires_note_and_mark_resolved_requires_ready() -> None:
    c = conn(); item = seed_review_item(c)
    try:
        ignore_review_item(c, review_item_id=item, note="")
    except ValueError:
        pass
    else:
        raise AssertionError("ignore without note accepted")
    ignored_audit = ignore_review_item(c, review_item_id=item, note="synthetic ignore")
    assert ignored_audit
    item2 = seed_review_item(c, source="True Wealth")
    try:
        mark_review_item_resolved(c, review_item_id=item2, note="too early")
    except ValueError:
        pass
    else:
        raise AssertionError("resolved before ready accepted")


def test_b4_aggregate_only_remains_blocked_for_instrument_import() -> None:
    c = conn(); item = seed_review_item(c, source="Raiffeisen", asset_class="other", flags=["aggregate_only", "missing_instrument_details"], status="blocked")
    row = c.execute("SELECT import_readiness_status, review_status FROM broker_import_review_items WHERE review_item_id=?", (item,)).fetchone()
    from jarvis_finance.imports.broker_mapping import refresh_review_item_readiness
    assert refresh_review_item_readiness(c, item) == "blocked"


def test_b4_dry_run_session_archive_discard_current_writes_audit(tmp_path: Path) -> None:
    c = conn()
    p = tmp_path / "pf.docx"; _docx(p, [["Produkt", "Menge", "Währung"], ["ETF A", "Menge", "CHF"]])
    summary = run_broker_import_dry_run(c, parse_postfinance_docx(p), source_filename=p.name)
    update_dry_run_session_status(c, dry_run_id=summary["dry_run_id"], action="mark_current", note="current")
    update_dry_run_session_status(c, dry_run_id=summary["dry_run_id"], action="archive", note="archive")
    sessions = get_dry_run_sessions(c)
    assert sessions[0]["session_status"] == "archived"
    assert c.execute("SELECT COUNT(*) AS n FROM audit_log WHERE entity_id=?", (summary["dry_run_id"],)).fetchone()["n"] == 2


def test_b4_data_quality_readiness_counts_and_productive_import_disabled() -> None:
    c = conn(); item = seed_review_item(c)
    rows = {r["check"]: r["count"] for r in dashboard_data.get_data_quality(c)}
    assert "Review Readiness: not_ready" in rows
    assert assert_productive_import_disabled() is True
    assert c.execute("SELECT COUNT(*) AS n FROM positions_snapshot").fetchone()["n"] == 0