schema.sql

CREATE TABLE IF NOT EXISTS app_user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    preferred_language TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('patient', 'provider'))
);

CREATE TABLE IF NOT EXISTS session (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT,
    FOREIGN KEY (user_id) REFERENCES app_user (id)
);

CREATE TABLE IF NOT EXISTS interaction (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    input_text TEXT,
    detected_language TEXT,
    translated_text TEXT,
    nlu_summary TEXT,
    provider_response TEXT,
    translated_response TEXT,
    timestamp TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES app_user (id)
);