database/db.py

import sqlite3
import json
from pathlib import Path
from datetime import datetime

DB_PATH = Path("/data/alzheimer.db")


def get_connection():
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    conn = get_connection()
    cur = conn.cursor()
    cur.executescript("""
        CREATE TABLE IF NOT EXISTS physicians (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            full_name     TEXT NOT NULL,
            email         TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            specialty     TEXT,
            hospital      TEXT,
            license_no    TEXT,
            avatar_url    TEXT,
            created_at    TEXT DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS patients (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            physician_id    INTEGER,
            full_name       TEXT,
            age             INTEGER,
            gender          TEXT,
            height_cm       REAL,
            weight_kg       REAL,
            education       TEXT,
            smoking         TEXT,
            alcohol         TEXT,
            activity        TEXT,
            sleep_hours     TEXT,
            medical_history TEXT,
            created_at      TEXT DEFAULT (datetime('now')),
            FOREIGN KEY (physician_id) REFERENCES physicians(id)
        );

        CREATE TABLE IF NOT EXISTS analyses (
            id                INTEGER PRIMARY KEY AUTOINCREMENT,
            patient_id        INTEGER NOT NULL,
            physician_id      INTEGER,
            scan_filename     TEXT,
            denoised_filename TEXT,
            stage             TEXT,
            risk_level        TEXT,
            confidence        REAL,
            region            TEXT,
            class_probs       TEXT,
            raw_label         TEXT,
            detected          INTEGER DEFAULT 0,
            description       TEXT,
            recommendations   TEXT,
            notes             TEXT,
            created_at        TEXT DEFAULT (datetime('now')),
            FOREIGN KEY (patient_id)   REFERENCES patients(id),
            FOREIGN KEY (physician_id) REFERENCES physicians(id)
        );

        CREATE TABLE IF NOT EXISTS denoising_jobs (
            id                INTEGER PRIMARY KEY AUTOINCREMENT,
            physician_id      INTEGER,
            original_filename TEXT,
            denoised_filename TEXT,
            psnr              REAL,
            ssim              REAL,
            created_at        TEXT DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS notifications (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            physician_id INTEGER NOT NULL,
            type        TEXT,
            title       TEXT,
            body        TEXT,
            analysis_id INTEGER,
            read        INTEGER DEFAULT 0,
            created_at  TEXT DEFAULT (datetime('now')),
            FOREIGN KEY (physician_id) REFERENCES physicians(id)
        );

        CREATE TABLE IF NOT EXISTS notification_settings (
            physician_id       INTEGER PRIMARY KEY,
            analysis_complete  INTEGER DEFAULT 1,
            weekly_summary     INTEGER DEFAULT 0,
            FOREIGN KEY (physician_id) REFERENCES physicians(id)
        );
    """)

    # --- Migrations for pre-existing databases ---
    # CREATE TABLE IF NOT EXISTS never alters an existing table, so a persistent
    # DB created by an older schema can be missing columns added later. Ensure
    # every expected column exists on the tables that have evolved.
    # NOTE: SQLite's ALTER TABLE ADD COLUMN only accepts constant defaults, so
    # non-constant defaults like created_at's datetime('now') are omitted here.
    _ensure_columns(cur, "patients", [
        ("physician_id",    "INTEGER"),
        ("full_name",       "TEXT"),
        ("age",             "INTEGER"),
        ("gender",          "TEXT"),
        ("height_cm",       "REAL"),
        ("weight_kg",       "REAL"),
        ("education",       "TEXT"),
        ("smoking",         "TEXT"),
        ("alcohol",         "TEXT"),
        ("activity",        "TEXT"),
        ("sleep_hours",     "TEXT"),
        ("medical_history", "TEXT"),
        ("created_at",      "TEXT"),
    ])
    _ensure_columns(cur, "analyses", [
        ("patient_id",        "INTEGER"),
        ("physician_id",      "INTEGER"),
        ("scan_filename",     "TEXT"),
        ("denoised_filename", "TEXT"),
        ("stage",             "TEXT"),
        ("risk_level",        "TEXT"),
        ("confidence",        "REAL"),
        ("region",            "TEXT"),
        ("class_probs",       "TEXT"),
        ("raw_label",         "TEXT"),
        ("detected",          "INTEGER DEFAULT 0"),
        ("description",       "TEXT"),
        ("recommendations",   "TEXT"),
        ("notes",             "TEXT"),
        ("created_at",        "TEXT"),
    ])

    conn.commit()
    conn.close()
    print(f"Database initialised at {DB_PATH}")


def _ensure_columns(cur, table, columns):
    existing = {row[1] for row in cur.execute(f"PRAGMA table_info({table})").fetchall()}
    for column, coltype in columns:
        if column not in existing:
            cur.execute(f"ALTER TABLE {table} ADD COLUMN {column} {coltype}")
            print(f"Migration: added {table}.{column}")