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 = 45


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) -> 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}")')]
        rows = connection.execute(
            f'SELECT * FROM "{table}" ORDER BY ' + ", ".join(f'"{column}"' for column in columns)
        ).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"
    for table in (
        "portfolio_ingestion_batches", "portfolio_ingestion_items", "market_data_runs",
        "benchmark_snapshots", "portfolio_analysis_snapshots",
    ):
        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)
    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
    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
    sprint5.close()

    print("phase3_migration_gate=PASS")
    print(f"empty_schema={EXPECTED_SCHEMA} integrity=ok ingestion_and_analysis_rows=0")
    print(f"sprint5_schema=40_to_{EXPECTED_SCHEMA} integrity=ok seed_digest={digest_before}")
    print("settings_guard=PASS production_paths_rejected=true")


if __name__ == "__main__":
    main()