-- HealthManager canonical schema (no patient data)
-- Generated from the productive SQLite structure on 2026-07-12.
-- This file contains DDL only. Never commit a productive database or data dump.

PRAGMA foreign_keys=ON;

CREATE TABLE apple_health_import_files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_name TEXT,
            file_path TEXT,
            file_hash TEXT UNIQUE,
            record_count INTEGER,
            inserted_count INTEGER,
            skipped_count INTEGER,
            status TEXT,
            error TEXT,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP,
            retry_count INTEGER NOT NULL DEFAULT 0,
            last_attempt_at TEXT
        );

CREATE TABLE apple_health_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            record_type TEXT,
            metric TEXT,
            start_date TEXT,
            end_date TEXT,
            value REAL,
            value_text TEXT,
            unit TEXT,
            source_name TEXT,
            source_version TEXT,
            device TEXT,
            file_name TEXT,
            file_hash TEXT,
            record_hash TEXT UNIQUE,
            raw_json TEXT NOT NULL,
            imported_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

CREATE TABLE arztbesuche (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT NOT NULL,
        arzt TEXT,
        klinik TEXT,
        grund TEXT,
        zusammenfassung TEXT,
        labor_verweis TEXT,
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE auswertungen (id INTEGER PRIMARY KEY, datum TEXT NOT NULL, typ TEXT NOT NULL, parameter TEXT, ergebnis TEXT, notizen TEXT, created_at TEXT DEFAULT (datetime('now','localtime')));

CREATE TABLE 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 dokumente (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            datei_name TEXT NOT NULL,
            dateipfad TEXT NOT NULL,
            daten_typ TEXT NOT NULL CHECK (daten_typ IN ('pdf', 'image')),
            upload_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            groessekbytes INTEGER,
            status TEXT DEFAULT 'neu' CHECK (status IN ('neu', 'eingearbeitet', 'archiviert')),
            verarbeite_datum TIMESTAMP,
            extrahierte_inhalte TEXT, kategorie TEXT, datei_hash TEXT, quelle TEXT, review_status TEXT DEFAULT 'nicht_geprueft', processing_quality TEXT, drive_file_id TEXT, drive_web_url TEXT, local_original_path TEXT, document_date TEXT, institution TEXT,
            FOREIGN KEY (status) REFERENCES dokumente_status
        );

CREATE TABLE dokumente_status (
            status TEXT PRIMARY KEY
        );

CREATE TABLE ernaehrung (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT NOT NULL,
        mahlzeit TEXT,
        beschreibung TEXT NOT NULL,
        wirkung TEXT,
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE 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 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
        );

CREATE TABLE health_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    category TEXT,
    parameter TEXT,
    value TEXT,
    unit TEXT,
    source TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE 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 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 histamine_food_aliases (
    alias TEXT PRIMARY KEY,
    canonical_food TEXT NOT NULL,
    FOREIGN KEY (canonical_food) REFERENCES histamine_food_rules(canonical_food)
);

CREATE TABLE histamine_food_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    canonical_food TEXT NOT NULL UNIQUE,
    sighi_score INTEGER NOT NULL CHECK(sighi_score BETWEEN 0 AND 3),
    category TEXT,
    tags TEXT,
    notes TEXT,
    confidence TEXT DEFAULT 'medium',
    source TEXT DEFAULT 'local_seed_sighi_inspired',
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE korrelationen (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT,
        faktor TEXT,          -- z.B. "Milchprodukte", "Kaffee", "Stress"
        auswirkung TEXT,      -- z.B. "CRP erhöht", "Aphthen"
        stärke TEXT,          -- "stark", "mittel", "schwach"
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE lab_reference_policy (
    id INTEGER PRIMARY KEY CHECK(id=1),
    canonical_rule TEXT NOT NULL,
    xlsx_role TEXT NOT NULL,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE laborwerte (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dokument_id INTEGER,
            parameter_name TEXT NOT NULL,
            wert TEXT NOT NULL,
            einheit TEXT,
            reference_min TEXT,
            reference_max TEXT,
            einheiten TEXT,
            bemerking TEXT,
            ermittlung_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP, abnahme_datum TEXT, befund_datum TEXT, wert_original TEXT, quelle TEXT, validierungsstatus TEXT DEFAULT 'unvalidiert', review_batch_id TEXT, source_type TEXT DEFAULT 'unknown', reference_range_source TEXT DEFAULT 'unknown', verified_against_original INTEGER DEFAULT 0, canonical_document_id INTEGER, provenance_note TEXT,
            FOREIGN KEY (dokument_id) REFERENCES dokumente(id)
        );

CREATE TABLE 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 low_histamine_experiments (
        id INTEGER PRIMARY KEY AUTOINCREMENT, start_date TEXT NOT NULL, end_date TEXT, phase TEXT NOT NULL DEFAULT 'baseline', challenge_food TEXT, hypothesis TEXT, notes TEXT, status TEXT DEFAULT 'active', created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE medication_administrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    datum TEXT NOT NULL,
    medication_name TEXT NOT NULL,
    dose TEXT,
    route TEXT,
    event_type TEXT,
    scheduled_next_date TEXT,
    notes TEXT,
    source TEXT DEFAULT 'manual',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    occurred_at TEXT,
    medication_id INTEGER REFERENCES medikamente(id),
    planned_event_id INTEGER REFERENCES medication_administrations(id),
    planned_dose_value TEXT,
    planned_dose_unit TEXT,
    actual_dose_value TEXT,
    actual_dose_unit TEXT,
    route_original TEXT,
    route_normalized TEXT,
    injection_region TEXT,
    injection_side TEXT,
    injection_detail TEXT,
    lot_number TEXT,
    corrects_event_id INTEGER REFERENCES medication_administrations(id),
    corrected_target_status TEXT,
    correction_reason TEXT,
    business_revision TEXT,
    planned_quantity_value TEXT,
    planned_dosage_form TEXT,
    planned_strength TEXT,
    actual_quantity_value TEXT,
    actual_dosage_form TEXT,
    actual_strength TEXT
);

CREATE TABLE medikamente (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dokument_id INTEGER,
            medikament_name TEXT NOT NULL,
            dosierung TEXT,
            anwendungsform TEXT,
            erhaltungsform TEXT,
            ermittlung_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            prescription_status TEXT,
            prescription_status_source TEXT,
            prescription_status_provenance TEXT,
            business_revision TEXT,
            administration_preset_quantity_value TEXT,
            administration_preset_dosage_form TEXT,
            administration_preset_strength TEXT,
            administration_preset_route_original TEXT,
            administration_preset_route TEXT,
            administration_preset_source TEXT,
            administration_preset_provenance TEXT,
            administration_preset_revision TEXT,
            FOREIGN KEY (dokument_id) REFERENCES dokumente(id)
        );

CREATE TABLE nutrition_action_recommendations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        priority INTEGER NOT NULL,
        recommendation_type TEXT NOT NULL,
        title TEXT NOT NULL,
        rationale TEXT,
        next_step TEXT,
        status TEXT DEFAULT 'open' CHECK(status IN ('open','done','dismissed')),
        computed_at TEXT DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(recommendation_type,title)
    );

CREATE TABLE nutrition_correlation_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metric TEXT NOT NULL,
    target TEXT NOT NULL,
    lag_days INTEGER NOT NULL,
    n INTEGER NOT NULL,
    correlation REAL,
    method TEXT DEFAULT 'pearson',
    interpretation TEXT,
    computed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(metric,target,lag_days)
);

CREATE TABLE 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
        );

CREATE TABLE nutrition_daily_summary_v2 (
    datum TEXT PRIMARY KEY,
    kcal REAL DEFAULT 0,
    protein_g REAL DEFAULT 0,
    carb_g REAL DEFAULT 0,
    fat_g REAL DEFAULT 0,
    item_count INTEGER DEFAULT 0,
    nutrient_json TEXT,
    histamine_score REAL,
    histamine_max INTEGER DEFAULT 0,
    histamine_unknown_count INTEGER DEFAULT 0,
    histamine_label TEXT DEFAULT 'unknown',
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE nutrition_food_insights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    canonical_food TEXT NOT NULL,
    insight_type TEXT NOT NULL CHECK(insight_type IN ('trigger_candidate','safe_candidate','insufficient_data')),
    days_seen INTEGER DEFAULT 0,
    avg_same_day_symptom REAL,
    avg_next_day_symptom REAL,
    avg_followup_symptom REAL,
    histamine_avg REAL,
    confidence TEXT DEFAULT 'low',
    notes TEXT,
    computed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(canonical_food, insight_type)
);

CREATE TABLE nutrition_histamine_scores (
    item_id INTEGER PRIMARY KEY,
    canonical_food TEXT,
    sighi_score INTEGER,
    traffic_light TEXT NOT NULL,
    tags TEXT,
    confidence TEXT DEFAULT 'low',
    reason TEXT,
    scored_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES nutrition_items(id) ON DELETE CASCADE
);

CREATE TABLE nutrition_item_nutrients (
    item_id INTEGER NOT NULL,
    nutrient_key TEXT NOT NULL,
    value REAL,
    unit TEXT,
    raw_value TEXT,
    PRIMARY KEY (item_id, nutrient_key),
    FOREIGN KEY (item_id) REFERENCES nutrition_items(id) ON DELETE CASCADE
);

CREATE TABLE nutrition_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source TEXT NOT NULL DEFAULT 'yazio_api',
    source_item_id TEXT,
    source_product_id TEXT,
    datum TEXT NOT NULL,
    meal TEXT NOT NULL,
    name TEXT NOT NULL,
    brand TEXT,
    amount REAL,
    amount_unit TEXT DEFAULT 'g',
    serving TEXT,
    serving_quantity REAL,
    kcal REAL,
    protein_g REAL,
    carb_g REAL,
    fat_g REAL,
    raw_item_json TEXT,
    raw_product_json TEXT,
    item_hash TEXT NOT NULL UNIQUE,
    imported_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE nutrition_meal_summary (
    datum TEXT NOT NULL,
    meal TEXT NOT NULL,
    kcal REAL DEFAULT 0,
    protein_g REAL DEFAULT 0,
    carb_g REAL DEFAULT 0,
    fat_g REAL DEFAULT 0,
    item_count INTEGER DEFAULT 0,
    nutrient_json TEXT,
    histamine_score REAL,
    histamine_label TEXT DEFAULT 'unknown',
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (datum, meal)
);

CREATE TABLE nutrition_review_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    normalized_name TEXT NOT NULL UNIQUE,
    example_name TEXT NOT NULL,
    occurrence_count INTEGER DEFAULT 0,
    first_seen TEXT,
    last_seen TEXT,
    suggested_canonical_food TEXT,
    suggested_score INTEGER,
    reason TEXT,
    status TEXT DEFAULT 'open' CHECK(status IN ('open','mapped','ignored')),
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE nutrition_treatment_phase_summary (
        phase TEXT PRIMARY KEY,
        start_date TEXT,
        end_date TEXT,
        nutrition_days INTEGER DEFAULT 0,
        avg_histamine_score REAL,
        avg_symptom_score REAL,
        avg_kcal REAL,
        avg_protein_g REAL,
        notes TEXT,
        computed_at TEXT DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE personal_food_tolerance (
    canonical_food TEXT PRIMARY KEY,
    personal_status TEXT NOT NULL DEFAULT 'unknown' CHECK(personal_status IN ('unknown','documented_tolerated','documented_not_tolerated','unclear','safe','problematic')),
    evidence_level TEXT DEFAULT 'user_documented' CHECK(evidence_level IN ('low','medium','high','user_documented')),
    notes TEXT,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE nutrition_mapping_action_log (
    action_id TEXT PRIMARY KEY,
    action_hash TEXT NOT NULL,
    queue_key TEXT NOT NULL,
    decision TEXT,
    alias TEXT,
    canonical_food TEXT,
    sighi_score INTEGER,
    confidence TEXT,
    mapping_method TEXT,
    source_label TEXT,
    source_version TEXT,
    note TEXT,
    ingredient_review_required INTEGER,
    personal_tolerance_status TEXT,
    personal_tolerance_note TEXT,
    definition_version TEXT,
    applied_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE nutrition_mapping_provenance (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action_id TEXT NOT NULL,
    normalized_name TEXT NOT NULL,
    decision TEXT,
    alias TEXT,
    canonical_food TEXT,
    sighi_score INTEGER,
    confidence TEXT,
    mapping_method TEXT,
    source_label TEXT,
    source_version TEXT,
    note TEXT,
    ingredient_review_required INTEGER,
    personal_tolerance_status TEXT,
    personal_tolerance_note TEXT,
    definition_version TEXT,
    affected_day TEXT NOT NULL,
    applied_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(action_id, affected_day)
);

CREATE TABLE nutrition_composite_product_review (
    normalized_name TEXT PRIMARY KEY,
    canonical_food TEXT,
    status TEXT NOT NULL DEFAULT 'needs_ingredient_review' CHECK(status IN ('needs_ingredient_review','reviewed')),
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

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,
    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 TABLE 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 symptom_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT NOT NULL,
        symptom TEXT NOT NULL,
        schwergrad TEXT,
        kontext TEXT,
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE symptome (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT NOT NULL,
        kategori TEXT,
        symptom TEXT NOT NULL,
        schwergrad TEXT,
        wirkung TEXT,
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE sync_state (
            key TEXT PRIMARY KEY,
            value TEXT,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

CREATE TABLE tagebuch (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datum TEXT NOT NULL,
        kategori TEXT NOT NULL,  -- 'ernaehrung', 'symptom', 'medikament', 'sport', 'schlaf', 'stress', 'sonstiges'
        titel TEXT NOT NULL,
        inhalt TEXT,
        wirkung TEXT,           -- wie hat es sich ausgewirkt?
        verknuepft TEXT,        -- Verknüpfung zu anderen Einträgen (ID)
        notizen TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );

CREATE TABLE vitalzeichen (id INTEGER PRIMARY KEY, datum TEXT NOT NULL, parameter TEXT NOT NULL, wert TEXT, einheit TEXT, notizen TEXT, created_at TEXT DEFAULT (datetime('now','localtime')));

CREATE INDEX idx_apple_health_file_hash ON apple_health_records(file_hash);

CREATE INDEX idx_apple_health_metric_date ON apple_health_records(metric,start_date);

CREATE INDEX idx_apple_health_type_date ON apple_health_records(record_type,start_date);

CREATE INDEX idx_document_insights_doc ON document_insights(dokument_id);

CREATE INDEX idx_health_category ON health_events(category);

CREATE INDEX idx_health_date ON health_events(date);

CREATE INDEX idx_health_event_periods_dates ON health_event_periods(start_date, end_date);

CREATE INDEX idx_health_event_periods_type ON health_event_periods(event_type);

CREATE INDEX idx_health_insights_date ON health_insights(insight_date);

CREATE INDEX idx_health_param ON health_events(parameter);

CREATE INDEX idx_labor_staging_batch ON laborwerte_staging(batch_id);

CREATE INDEX idx_labor_staging_status ON laborwerte_staging(status);

CREATE INDEX idx_laborwerte_abnahme ON laborwerte(abnahme_datum);

CREATE INDEX idx_multimodal_correlations_status ON multimodal_correlation_results(status, target, medication_phase);

CREATE UNIQUE INDEX idx_symptom_log_daily_dimension ON symptom_log(datum, symptom, kontext);

CREATE INDEX idx_nutrition_items_day_meal ON nutrition_items(datum, meal);

CREATE INDEX idx_nutrition_items_product ON nutrition_items(source_product_id);

CREATE INDEX idx_nutrition_nutrients_key ON nutrition_item_nutrients(nutrient_key);
