#!/usr/bin/env python3
"""Schema migration for JARVIS Health Dashboard v3.

Adds:
- stable original-document linking fields
- period-based health events for Aphthen/Schübe/medication phases
- daily nutrition feature table for adherence/correlation dashboards
"""
from __future__ import annotations
import shutil
import sqlite3
from datetime import datetime
from pathlib import Path

BASE = Path.home() / ".hermes" / "assets" / "Gesundheit"
DB = BASE / "health_data.db"
BACKUP = BASE / "backups" / f"health_data_before_dashboard_v3_{datetime.now():%Y%m%d_%H%M%S}.db"


def add_col(cur: sqlite3.Cursor, table: str, col: str, ddl: str) -> None:
    cols = {r[1] for r in cur.execute(f"PRAGMA table_info({table})")}
    if col not in cols:
        cur.execute(f"ALTER TABLE {table} ADD COLUMN {col} {ddl}")


def main() -> None:
    BASE.joinpath("backups").mkdir(parents=True, exist_ok=True)
    shutil.copy2(DB, BACKUP)
    con = sqlite3.connect(DB)
    cur = con.cursor()

    for col, ddl in {
        "drive_file_id": "TEXT",
        "drive_web_url": "TEXT",
        "local_original_path": "TEXT",
        "document_date": "TEXT",
        "institution": "TEXT",
    }.items():
        add_col(cur, "dokumente", col, ddl)

    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS health_event_periods (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            start_date TEXT NOT NULL,
            end_date TEXT,
            event_type TEXT NOT NULL,
            label TEXT,
            severity TEXT,
            location TEXT,
            source TEXT,
            notes TEXT,
            confidence REAL DEFAULT 1.0,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    cur.execute("CREATE INDEX IF NOT EXISTS idx_health_event_periods_dates ON health_event_periods(start_date, end_date)")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_health_event_periods_type ON health_event_periods(event_type)")

    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS nutrition_daily_features (
            datum TEXT PRIMARY KEY,
            plan_adherence_score REAL,
            histamine_score REAL,
            saturated_fat_score REAL,
            sugar_score REAL,
            gluten_flag INTEGER DEFAULT 0,
            pork_flag INTEGER DEFAULT 0,
            nightshade_flag INTEGER DEFAULT 0,
            alcohol_flag INTEGER DEFAULT 0,
            fiber_proxy REAL,
            protein_proxy REAL,
            omega3_proxy REAL,
            positive_hits TEXT,
            negative_hits TEXT,
            source TEXT,
            computed_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        """
    )

    # Backfill local_original_path from existing dateipfad where possible.
    cur.execute(
        """
        UPDATE dokumente
           SET local_original_path = COALESCE(local_original_path, dateipfad)
         WHERE dateipfad IS NOT NULL
           AND trim(dateipfad) <> ''
           AND (local_original_path IS NULL OR trim(local_original_path) = '')
        """
    )

    con.commit()
    con.close()
    print(f"dashboard_v3 migration ok; backup={BACKUP}")


if __name__ == "__main__":
    main()