from __future__ import annotations

import hashlib
import io
import json
import os
import shutil
import sqlite3
import subprocess
import sys
import tarfile
from pathlib import Path

from jarvis_finance.config.settings import load_settings
from jarvis_finance.storage.migrations import apply_migrations, get_schema_version

SPRINT5_COMMIT = "480d7a1b72a950e864c4bb021d3f58af8f8c15f9"
EXPECTED_SCHEMA = 50


def _assert_tmp_path(path: Path) -> Path:
    resolved = path.resolve()
    if resolved == Path("/tmp") or Path("/tmp") not in resolved.parents:
        raise RuntimeError(f"CI path must be isolated below /tmp: {resolved}")
    return resolved


def _connect(path: Path) -> sqlite3.Connection:
    connection = sqlite3.connect(path)
    connection.row_factory = sqlite3.Row
    return connection


def _integrity(connection: sqlite3.Connection) -> str:
    return str(connection.execute("PRAGMA integrity_check").fetchone()[0])


def _seed_digest(connection: sqlite3.Connection, *, include_performance_metadata: bool = False) -> str:
    payload: dict[str, list[list[object]]] = {}
    for table in ("platforms", "accounts"):
        columns = [row[1] for row in connection.execute(f'PRAGMA table_info("{table}")')]
        selected_columns = columns
        if table == "accounts" and not include_performance_metadata:
            selected_columns = [column for column in columns if column != "performance_included"]
        selected = ", ".join(f'"{column}"' for column in selected_columns)
        rows = connection.execute(f'SELECT {selected} FROM "{table}" ORDER BY {selected}').fetchall()
        payload[table] = [[row[column] for column in selected_columns] for row in rows]
    encoded = json.dumps(payload, ensure_ascii=True, separators=(",", ":"), default=str).encode()
    return hashlib.sha256(encoded).hexdigest()


def _database_digest(connection: sqlite3.Connection) -> str:
    payload: dict[str, list[list[object]]] = {}
    tables = [
        str(row[0])
        for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
        )
    ]
    for table in tables:
        columns = [str(row[1]) for row in connection.execute(f'PRAGMA table_info("{table}")')]
        if not columns:
            continue
        selected = ", ".join(f'"{column}"' for column in columns)
        rows = connection.execute(f'SELECT {selected} FROM "{table}" ORDER BY {selected}').fetchall()
        payload[table] = [[row[column] for column in columns] for row in rows]
    encoded = json.dumps(payload, ensure_ascii=True, separators=(",", ":"), default=str).encode()
    return hashlib.sha256(encoded).hexdigest()


def _create_sprint5_database(repo: Path, export_dir: Path, database: Path) -> None:
    archive = subprocess.check_output(["git", "-C", str(repo), "archive", SPRINT5_COMMIT])
    export_dir.mkdir(parents=True)
    with tarfile.open(fileobj=io.BytesIO(archive), mode="r:") as bundle:
        for member in bundle.getmembers():
            destination = (export_dir / member.name).resolve()
            if export_dir.resolve() not in destination.parents and destination != export_dir.resolve():
                raise RuntimeError("Unsafe path in Git archive")
        bundle.extractall(export_dir)
    code = """
import sqlite3
import sys
from jarvis_finance.storage.migrations import apply_migrations, get_schema_version

path = sys.argv[1]
conn = sqlite3.connect(path)
conn.row_factory = sqlite3.Row
apply_migrations(conn)
assert get_schema_version(conn) == 40
conn.execute(
    "INSERT INTO platforms(platform_id,name,platform_type,country,default_currency,is_active,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?)",
    ("ci-platform", "Synthetic CI Platform", "bank", "CH", "CHF", 1, "synthetic", "2026-01-01T00:00:00Z", None),
)
conn.execute(
    "INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,performance_included,is_health_reserve,is_active,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
    ("ci-account", "ci-platform", "Synthetic CI Account", "brokerage", "CHF", 1, 0, 1, "synthetic", "2026-01-01T00:00:00Z", None),
)
conn.commit()
assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
print("sprint5_fixture_schema=40")
"""
    environment = os.environ.copy()
    environment["PYTHONPATH"] = str(export_dir / "src")
    subprocess.run(
        [sys.executable, "-c", code, str(database)],
        cwd=export_dir,
        env=environment,
        check=True,
    )


def _verify_settings_guard(repo: Path, root: Path) -> None:
    accepted = {
        "JARVIS_FINANCE_ENV": "test",
        "JARVIS_FINANCE_RUNTIME_DIR": str(root / "guard-runtime"),
        "JARVIS_FINANCE_DB_PATH": str(root / "guard.sqlite3"),
    }
    settings = load_settings(repo_root=repo, environ=accepted)
    assert settings.db_path == (root / "guard.sqlite3").resolve()

    rejected = {
        "JARVIS_FINANCE_ENV": "test",
        "JARVIS_FINANCE_RUNTIME_DIR": "/home/agent/jarvis_runtime/finance-system",
        "JARVIS_FINANCE_DB_PATH": "/home/agent/jarvis_runtime/finance-system/data/finance.sqlite3",
    }
    try:
        load_settings(repo_root=repo, environ=rejected)
    except ValueError:
        pass
    else:
        raise AssertionError("Productive paths were not rejected in test mode")


def main() -> None:
    repo = Path(__file__).resolve().parents[1]
    root = _assert_tmp_path(Path(os.environ.get("JARVIS_FINANCE_CI_ROOT", "/tmp/financemanager-phase3-ci")))
    if root.exists():
        shutil.rmtree(root)
    root.mkdir(parents=True)

    _verify_settings_guard(repo, root)

    empty_path = root / "empty.sqlite3"
    empty = _connect(empty_path)
    apply_migrations(empty)
    empty.commit()
    assert get_schema_version(empty) == EXPECTED_SCHEMA
    assert _integrity(empty) == "ok"
    account_default = next(
        row for row in empty.execute('PRAGMA table_info("accounts")') if row[1] == "performance_included"
    )
    assert str(account_default[4]).strip("()") == "0"
    assert empty.execute(
        "SELECT COUNT(*) FROM sqlite_master WHERE type='trigger' AND name='accounts_performance_insert_requires_exclusion'"
    ).fetchone()[0] == 1
    for table in (
        "portfolio_ingestion_batches", "portfolio_ingestion_items", "market_data_runs",
        "benchmark_snapshots", "portfolio_analysis_snapshots", "performance_scope_classifications",
        "performance_cashflow_coverage",
    ):
        assert empty.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] == 0
    empty.close()

    sprint5_path = root / "sprint5.sqlite3"
    _create_sprint5_database(repo, root / "sprint5-source", sprint5_path)
    sprint5 = _connect(sprint5_path)
    assert get_schema_version(sprint5) == 40
    digest_before = _seed_digest(sprint5)
    assert sprint5.execute(
        "SELECT performance_included FROM accounts WHERE account_id='ci-account'"
    ).fetchone()[0] == 1
    counts_before = {
        table: sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0]
        for table in ("platforms", "accounts", "transactions", "portfolio_valuation_snapshots")
    }
    apply_migrations(sprint5)
    sprint5.commit()
    assert get_schema_version(sprint5) == EXPECTED_SCHEMA
    assert _integrity(sprint5) == "ok"
    assert _seed_digest(sprint5) == digest_before
    assert sprint5.execute(
        "SELECT performance_included FROM accounts WHERE account_id='ci-account'"
    ).fetchone()[0] == 0
    classification = sprint5.execute(
        """SELECT included,classification_role,decision_version,audit_id
           FROM performance_scope_classifications WHERE account_id='ci-account'"""
    ).fetchone()
    assert tuple(classification[:3]) == (
        0,
        "not_in_investment_performance_scope",
        "investment_performance_scope_v1",
    )
    assert sprint5.execute(
        "SELECT COUNT(*) FROM audit_log WHERE audit_id=? AND entity_type='performance_scope_classification'",
        (classification[3],),
    ).fetchone()[0] == 1
    counts_after = {
        table: sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0]
        for table in counts_before
    }
    assert counts_after == counts_before
    for table in (
        "portfolio_ingestion_batches", "portfolio_ingestion_items", "market_data_runs",
        "benchmark_snapshots", "portfolio_analysis_snapshots",
    ):
        assert sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] == 0
    first_digest = _database_digest(sprint5)
    first_migration_rows = sprint5.execute("SELECT COUNT(*) FROM schema_migrations").fetchone()[0]
    apply_migrations(sprint5)
    sprint5.commit()
    assert get_schema_version(sprint5) == EXPECTED_SCHEMA
    assert _integrity(sprint5) == "ok"
    assert _database_digest(sprint5) == first_digest
    assert sprint5.execute("SELECT COUNT(*) FROM schema_migrations").fetchone()[0] == first_migration_rows
    sprint5.close()

    print("phase3_migration_gate=PASS")
    print(f"empty_schema={EXPECTED_SCHEMA} integrity=ok ingestion_analysis_scope_rows=0 default_performance_included=0")
    print(
        f"sprint5_schema=40_to_{EXPECTED_SCHEMA} integrity=ok business_digest={digest_before} "
        "scope_normalized=true audit_bound=true second_run_noop=true"
    )
    print("settings_guard=PASS production_paths_rejected=true")


if __name__ == "__main__":
    main()