backend/database.py

"""SQLite layer for DermaConnect. One file, no ORM, easy to read and reset."""

from __future__ import annotations

import os
import sqlite3
from datetime import datetime, timezone

DB_PATH = os.path.join(os.path.dirname(__file__), "..", "data", "derma.db")


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()


def get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    role        TEXT NOT NULL CHECK (role IN ('patient', 'dermatologist')),
    name        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE,
    created_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS care_links (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    patient_id      INTEGER NOT NULL REFERENCES users(id),
    dermatologist_id INTEGER NOT NULL REFERENCES users(id),
    body_site       TEXT NOT NULL DEFAULT 'general',
    cadence_days    INTEGER NOT NULL DEFAULT 7,
    next_due        TEXT,
    created_at      TEXT NOT NULL,
    UNIQUE (patient_id, dermatologist_id, body_site)
);

CREATE TABLE IF NOT EXISTS readings (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    care_link_id INTEGER NOT NULL REFERENCES care_links(id),
    image_path  TEXT NOT NULL,
    score       REAL NOT NULL,
    category    TEXT NOT NULL,
    confidence  REAL NOT NULL,
    signals     TEXT,
    model       TEXT NOT NULL,
    created_at  TEXT NOT NULL
);
"""


def init_db() -> None:
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = get_conn()
    try:
        conn.executescript(SCHEMA)
        conn.commit()
    finally:
        conn.close()


def create_user(role: str, name: str, email: str) -> int:
    conn = get_conn()
    try:
        cur = conn.execute(
            "INSERT INTO users (role, name, email, created_at) VALUES (?, ?, ?, ?)",
            (role, name, email, _now()),
        )
        conn.commit()
        return cur.lastrowid
    finally:
        conn.close()