schema.sql

CREATE TABLE IF NOT EXISTS Patient (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dob DATE NOT NULL,
    nhs_number TEXT UNIQUE NOT NULL,
    medical_history JSON,
    contact_details TEXT
);

CREATE TABLE IF NOT EXISTS Cases (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('open', 'pending', 'running', 'converged', 'human_review', 'error', 'closed')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    total_cycles INTEGER DEFAULT 0,
    final_output JSON,
    FOREIGN KEY (patient_id) REFERENCES Patient(id)
);

CREATE TABLE IF NOT EXISTS Scans (
    id INTEGER PRIMARY KEY,
    case_id INTEGER NOT NULL,
    scan_type TEXT NOT NULL,
    date TEXT NOT NULL,
    images_file TEXT NOT NULL,
    is_latest INTEGER DEFAULT 0,
    meta_data JSON,
    FOREIGN KEY (case_id) REFERENCES Cases(id)
);

CREATE TABLE IF NOT EXISTS Iteration_steps (
    id                    INTEGER PRIMARY KEY,
    case_id               INTEGER NOT NULL,
    step_number           INTEGER NOT NULL,
    new_deduction         TEXT,
    new_reasoning         TEXT,
    cumulative_deductions JSON,
    cumulative_reasoning  JSON,
    flagged_regions       JSON,
    FOREIGN KEY (case_id) REFERENCES Cases(id)
);


CREATE TABLE IF NOT EXISTS Conclusion_runs (
    id INTEGER PRIMARY KEY,
    case_id INTEGER NOT NULL,
    run_number INTEGER NOT NULL,
    triggered_by_iteration_id INTEGER,
    output_text TEXT,
    FOREIGN KEY (case_id) REFERENCES Cases(id),
    FOREIGN KEY (triggered_by_iteration_id) REFERENCES Iteration_steps(id)
);