import importlib.util
import sqlite3
from pathlib import Path

import pytest

SCRIPT = Path(__file__).resolve().parents[1] / "scripts/health/health_system_migrate.py"


@pytest.fixture
def migration(tmp_path, monkeypatch):
    spec = importlib.util.spec_from_file_location("health_system_migrate_sprint2", SCRIPT)
    assert spec and spec.loader
    module = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(module)

    db = tmp_path / "health.db"
    connection = sqlite3.connect(db)
    connection.executescript(
        """
        CREATE TABLE laborwerte (id INTEGER PRIMARY KEY);
        CREATE TABLE dokumente (id INTEGER PRIMARY KEY);
        CREATE TABLE symptom_log (
            id INTEGER PRIMARY KEY,
            datum TEXT NOT NULL,
            symptom TEXT NOT NULL,
            kontext TEXT
        );
        """
    )
    connection.close()
    monkeypatch.setattr(module, "DB", db)
    monkeypatch.setattr(module, "BACKUP_DIR", tmp_path / "backups")
    return module, db


def test_sprint2_migration_is_idempotent_and_additive(migration):
    module, db = migration

    module.main()
    module.main()

    connection = sqlite3.connect(db)
    try:
        lab_columns = {row[1] for row in connection.execute("PRAGMA table_info(laborwerte)")}
        result_columns = {
            row[1]
            for row in connection.execute("PRAGMA table_info(multimodal_correlation_results)")
        }
        assert {"verified_against_original", "reference_range_source"} <= lab_columns
        assert {"expected_target_days", "target_coverage", "quality_flags"} <= result_columns
        assert connection.execute(
            "SELECT 1 FROM sqlite_master WHERE type='index' AND name='idx_symptom_log_daily_dimension'"
        ).fetchone()
        assert connection.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    finally:
        connection.close()


def test_existing_old_result_schema_is_upgraded_without_losing_rows(migration):
    module, db = migration
    connection = sqlite3.connect(db)
    connection.executescript(
        """
        CREATE TABLE multimodal_correlation_results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            predictor TEXT NOT NULL,
            target TEXT NOT NULL,
            lag_days INTEGER NOT NULL,
            medication_phase TEXT NOT NULL,
            n INTEGER NOT NULL,
            eligible_target_days INTEGER NOT NULL,
            missing_pairs INTEGER NOT NULL,
            rho REAL,
            p_value REAL,
            q_value REAL,
            status TEXT NOT NULL,
            method TEXT NOT NULL,
            quality_flags TEXT NOT NULL,
            interpretation TEXT NOT NULL,
            computed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(predictor,target,lag_days,medication_phase)
        );
        INSERT INTO multimodal_correlation_results
            (predictor,target,lag_days,medication_phase,n,eligible_target_days,
             missing_pairs,status,method,quality_flags,interpretation)
        VALUES
            ('nutrition:histamine_score','symptom_total',0,'unknown',0,1,
             1,'insufficient_n','old-safe-method','[]','old aggregate row');
        """
    )
    connection.close()

    module.main()
    module.main()

    connection = sqlite3.connect(db)
    try:
        columns = {
            row[1]: row
            for row in connection.execute("PRAGMA table_info(multimodal_correlation_results)")
        }
        assert columns["expected_target_days"][4] == "0"
        assert columns["target_coverage"][4] == "0"
        row = connection.execute(
            """SELECT predictor,target,n,expected_target_days,target_coverage
               FROM multimodal_correlation_results"""
        ).fetchone()
        assert row == ("nutrition:histamine_score", "symptom_total", 0, 0, 0.0)
        assert connection.execute(
            "SELECT COUNT(*) FROM multimodal_correlation_results"
        ).fetchone()[0] == 1
        assert connection.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    finally:
        connection.close()


def test_migration_does_not_create_unique_index_over_existing_duplicates(migration):
    module, db = migration
    connection = sqlite3.connect(db)
    connection.executemany(
        "INSERT INTO symptom_log(id,datum,symptom,kontext) VALUES(?,?,?,?)",
        [
            (1, "2026-01-01", "GI/Darm", "daily_quick_score"),
            (2, "2026-01-01", "GI/Darm", "daily_quick_score"),
        ],
    )
    connection.commit()
    connection.close()

    module.main()

    connection = sqlite3.connect(db)
    try:
        assert connection.execute(
            "SELECT 1 FROM sqlite_master WHERE type='index' AND name='idx_symptom_log_daily_dimension'"
        ).fetchone() is None
        assert connection.execute("SELECT COUNT(*) FROM symptom_log").fetchone()[0] == 2
    finally:
        connection.close()