#!/usr/bin/env python3
"""Migration for the health management system.

Adds explicit review/staging/insight/report tables without destroying existing data.
Run repeatedly; it is idempotent.
"""
from __future__ import annotations

import os
import shutil
import sqlite3
from datetime import datetime
from pathlib import Path

BASE = Path(os.path.expanduser("~/.hermes/assets/Gesundheit"))
DB = BASE / "health_data.db"
BACKUP_DIR = BASE / "backups"


def column_exists(conn: sqlite3.Connection, table: str, column: str) -> bool:
    return any(row[1] == column for row in conn.execute(f"PRAGMA table_info({table})"))


def add_column(conn: sqlite3.Connection, table: str, column: str, ddl: str) -> None:
    if not column_exists(conn, table, column):
        conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {ddl}")


def main() -> None:
    if not DB.exists():
        raise SystemExit(f"DB not found: {DB}")
    BACKUP_DIR.mkdir(parents=True, exist_ok=True)
    stamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup = BACKUP_DIR / f"health_data_before_review_workflow_{stamp}.db"
    shutil.copy2(DB, backup)

    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA foreign_keys=ON")

    # Add non-breaking columns to existing core tables.
    add_column(conn, "laborwerte", "abnahme_datum", "TEXT")
    add_column(conn, "laborwerte", "befund_datum", "TEXT")
    add_column(conn, "laborwerte", "wert_original", "TEXT")
    add_column(conn, "laborwerte", "quelle", "TEXT")
    add_column(conn, "laborwerte", "validierungsstatus", "TEXT DEFAULT 'unvalidiert'")
    add_column(conn, "laborwerte", "verified_against_original", "INTEGER NOT NULL DEFAULT 0")
    add_column(conn, "laborwerte", "reference_range_source", "TEXT")
    add_column(conn, "laborwerte", "review_batch_id", "TEXT")
    add_column(conn, "dokumente", "quelle", "TEXT")
    add_column(conn, "dokumente", "review_status", "TEXT DEFAULT 'nicht_geprueft'")
    add_column(conn, "dokumente", "processing_quality", "TEXT")

    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS health_processing_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            batch_id TEXT UNIQUE NOT NULL,
            run_type TEXT NOT NULL,
            source TEXT,
            source_ref TEXT,
            started_at TEXT DEFAULT (datetime('now','localtime')),
            finished_at TEXT,
            status TEXT DEFAULT 'running',
            summary TEXT,
            artifacts_json TEXT
        );

        CREATE TABLE IF NOT EXISTS laborwerte_staging (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            batch_id TEXT NOT NULL,
            dokument_id INTEGER,
            parameter_name TEXT NOT NULL,
            kategorie TEXT,
            wert_extrahiert TEXT,
            wert_korrigiert TEXT,
            wert_final TEXT,
            einheit TEXT,
            referenzbereich TEXT,
            reference_min TEXT,
            reference_max TEXT,
            flag TEXT,
            abnahme_datum TEXT,
            befund_datum TEXT,
            dokumentseite TEXT,
            confidence REAL,
            extraktionsmethode TEXT,
            quelle TEXT,
            kommentar TEXT,
            status TEXT DEFAULT 'zur_pruefung',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            reviewed_at TEXT,
            UNIQUE(batch_id, parameter_name, abnahme_datum, einheit)
        );

        CREATE TABLE IF NOT EXISTS document_insights (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dokument_id INTEGER,
            batch_id TEXT,
            insight_type TEXT NOT NULL,
            datum TEXT,
            titel TEXT NOT NULL,
            inhalt TEXT,
            severity TEXT DEFAULT 'info',
            source TEXT,
            created_at TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS health_daily_summary (
            datum TEXT PRIMARY KEY,
            nutrition_json TEXT,
            vitals_json TEXT,
            symptoms_json TEXT,
            medications_json TEXT,
            notes TEXT,
            generated_at TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS health_insights (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            insight_date TEXT NOT NULL,
            insight_type TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            evidence_json TEXT,
            confidence TEXT DEFAULT 'niedrig',
            severity TEXT DEFAULT 'info',
            status TEXT DEFAULT 'offen',
            created_at TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS report_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            report_type TEXT NOT NULL,
            period_start TEXT,
            period_end TEXT,
            output_path TEXT,
            drive_file_id TEXT,
            status TEXT DEFAULT 'created',
            summary TEXT,
            created_at TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS 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,
            expected_target_days INTEGER NOT NULL DEFAULT 0,
            target_coverage REAL NOT NULL DEFAULT 0,
            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)
        );

        CREATE INDEX IF NOT EXISTS idx_multimodal_correlations_status
            ON multimodal_correlation_results(status,target,medication_phase);
        CREATE INDEX IF NOT EXISTS idx_labor_staging_status ON laborwerte_staging(status);
        CREATE INDEX IF NOT EXISTS idx_labor_staging_batch ON laborwerte_staging(batch_id);
        CREATE INDEX IF NOT EXISTS idx_laborwerte_abnahme ON laborwerte(abnahme_datum);
        CREATE INDEX IF NOT EXISTS idx_health_insights_date ON health_insights(insight_date);
        CREATE INDEX IF NOT EXISTS idx_document_insights_doc ON document_insights(dokument_id);
        """
    )

    add_column(conn, "multimodal_correlation_results", "expected_target_days", "INTEGER NOT NULL DEFAULT 0")
    add_column(conn, "multimodal_correlation_results", "target_coverage", "REAL NOT NULL DEFAULT 0")
    duplicate_quick_rows = conn.execute(
        """SELECT COUNT(*) FROM (
            SELECT datum,symptom,kontext FROM symptom_log
            GROUP BY datum,symptom,kontext HAVING COUNT(*) > 1
        )"""
    ).fetchone()[0]
    if duplicate_quick_rows == 0:
        conn.execute(
            "CREATE UNIQUE INDEX IF NOT EXISTS idx_symptom_log_daily_dimension ON symptom_log(datum,symptom,kontext)"
        )

    conn.commit()
    conn.close()
    print(f"Migration OK. Backup: {backup}")


if __name__ == "__main__":
    main()