schema.sql

-- Hospitals (Orgs)
CREATE TABLE IF NOT EXISTS hospitals (
    id TEXT PRIMARY KEY, 
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone_number TEXT UNIQUE,
    tin TEXT NOT NULL, 
    reg_no TEXT NOT NULL, 
    mobile TEXT NOT NULL,
    license_status TEXT DEFAULT 'active',
    daily_secret_key TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Patients
CREATE TABLE IF NOT EXISTS patients (
    id TEXT PRIMARY KEY, -- 10-digit: DDMMYYXXXX
    hospital_id TEXT,
    full_name TEXT NOT NULL,
    mobile TEXT NOT NULL,
    dob TEXT NOT NULL, 
    govt_id_last_four TEXT NOT NULL,
    govt_id_full TEXT, 
    gender TEXT,
    next_of_kin_mobile TEXT,
    medical_history_summary TEXT,
    discharge_date TEXT,
    symptoms_detected TEXT, -- AI Analysis (Repo 2)
    medications TEXT,       -- JSON string
    monitoring_status TEXT DEFAULT 'active',
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id)
);

-- Call Logs (UPDATED FOR REPO 3 COMPATIBILITY)
CREATE TABLE IF NOT EXISTS call_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Changed to UUID for distributed safety
    patient_id TEXT,
    call_type TEXT,                -- Added: 'routine_checkup', 'emergency'
    status TEXT DEFAULT 'pending', -- Renamed from call_status to match Agent code
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Added
    ended_at TIMESTAMP WITH TIME ZONE,                 -- Added
    duration_seconds INTEGER,                          -- Added
    agent_summary TEXT,
    symptoms_reported TEXT,        -- Added: What the patient SAID (Repo 3)
    symptoms_detected TEXT,        -- What the AI DETECTED (Repo 2)
    adherence_confirmed BOOLEAN,
    escalation_reason TEXT,        -- Added: For the Safety Tool
    FOREIGN KEY (patient_id) REFERENCES patients(id)
);