# Database Setup Scripts — AI NIMS

> **For DevOps team.**
> Run Section 1 once to set up the schema, then Section 2 to seed initial data.
> After that, all DB changes happen through the API — no direct SQL writes.

---

## Section 1 — Schema Creation

Run this once on a fresh database.

```sql
-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ── Master: Roles ─────────────────────────
CREATE TABLE mstr_role (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(30) NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Master: Departments ───────────────────────
CREATE TABLE mstr_department (
    id                 UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    name               VARCHAR(100) NOT NULL UNIQUE,
    llm_system_prompt  TEXT        NOT NULL,
    llm_summary_prompt TEXT        NOT NULL,
    is_active          BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Master: Questions ──────────────────
CREATE TABLE mstr_question (
    id         UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    text       TEXT        NOT NULL,
    category   VARCHAR(50) NOT NULL,
    is_active  BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Users (Staff / Admin)────────────────
CREATE TABLE "user" (
    id                   UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    department_id        UUID         NOT NULL REFERENCES mstr_department(id),
    name                 VARCHAR(100) NOT NULL,
    email                VARCHAR(255) NOT NULL UNIQUE,
    pin_hash             VARCHAR(255) NOT NULL,
    role                 VARCHAR(30)  NOT NULL,
    is_active            BOOLEAN      NOT NULL DEFAULT TRUE,
    failed_pin_attempts  SMALLINT     NOT NULL DEFAULT 0,
    locked_at            TIMESTAMPTZ,
    created_by           UUID         REFERENCES "user"(id) ON DELETE SET NULL,
    created_at           TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ── Junction: User ↔ Role ──────────────────────
CREATE TABLE user_role_map (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID        NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    role_id     UUID        NOT NULL REFERENCES mstr_role(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_user_role UNIQUE (user_id, role_id)
);

-- ── Junction: Department ↔ User ───────────────
CREATE TABLE department_user_map (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    department_id  UUID        NOT NULL REFERENCES mstr_department(id) ON DELETE CASCADE,
    user_id        UUID        NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_dept_user UNIQUE (department_id, user_id)
);

-- ── Junction: Question ↔ Department ───────────
CREATE TABLE question_dept_map (
    id              UUID      PRIMARY KEY DEFAULT uuid_generate_v4(),
    question_id     UUID      NOT NULL REFERENCES mstr_question(id) ON DELETE CASCADE,
    department_id   UUID      NOT NULL REFERENCES mstr_department(id) ON DELETE CASCADE,
    sequence_number SMALLINT  NOT NULL,
    is_active       BOOLEAN   NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_question_dept     UNIQUE (question_id, department_id),
    CONSTRAINT uq_dept_sequence     UNIQUE (department_id, sequence_number)
);
CREATE INDEX question_dept_map_dept_idx ON question_dept_map (department_id, is_active, sequence_number);

-- ── Patients ───────────
CREATE TABLE patient (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    daily_id    VARCHAR(50) NOT NULL,
    name        VARCHAR(100) NOT NULL,
    age         SMALLINT    NOT NULL,
    gender      VARCHAR(20) NOT NULL,
    intake_date DATE        NOT NULL DEFAULT CURRENT_DATE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ NOT NULL,
    CONSTRAINT uq_patient_daily_date UNIQUE (daily_id, intake_date)
);

-- ── Junction: Patient ↔ Department ───────────
CREATE TABLE patient_department_map (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_id     UUID        NOT NULL REFERENCES patient(id) ON DELETE CASCADE,
    department_id  UUID        NOT NULL REFERENCES mstr_department(id) ON DELETE CASCADE,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_patient_dept UNIQUE (patient_id, department_id)
);

-- ── Intake Sessions ──────────────────────
CREATE TABLE intake_session (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_id     UUID        NOT NULL UNIQUE REFERENCES patient(id) ON DELETE CASCADE,
    department_id  UUID        NOT NULL REFERENCES mstr_department(id),
    status         VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    started_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at   TIMESTAMPTZ,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX sessions_status_idx ON intake_session (department_id, status);

-- ── Session Contributors ──────────────────
CREATE TABLE session_contributor (
    id              UUID      PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id      UUID      NOT NULL REFERENCES intake_session(id) ON DELETE CASCADE,
    staff_id        UUID      NOT NULL REFERENCES "user"(id),
    sequence_number SMALLINT  NOT NULL,
    joined_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_contributor_session_staff UNIQUE (session_id, staff_id)
);
CREATE INDEX contributors_session_idx ON session_contributor (session_id, sequence_number);

-- ── Junction: Session ↔ Patient ──────────
CREATE TABLE session_patient_map (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id  UUID        NOT NULL REFERENCES intake_session(id) ON DELETE CASCADE,
    patient_id  UUID        NOT NULL REFERENCES patient(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_session_patient UNIQUE (session_id, patient_id)
);

-- ── Question Responses ────────────────────────
CREATE TABLE question_response (
    id                     UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id             UUID         NOT NULL REFERENCES intake_session(id) ON DELETE CASCADE,
    question_id            VARCHAR(255),
    question_text          TEXT         NOT NULL,
    transcribed_text       TEXT         NOT NULL,
    input_type             VARCHAR(10)  NOT NULL DEFAULT 'TEXT',
    input_language         VARCHAR(20)  NOT NULL DEFAULT 'ENGLISH',
    is_flagged             BOOLEAN      NOT NULL DEFAULT FALSE,
    sequence_number        SMALLINT     NOT NULL,
    responded_by_staff_id  UUID         NOT NULL REFERENCES "user"(id),
    created_at             TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX responses_session_idx ON question_response (session_id, sequence_number);

-- ── Patient Summaries ──────────────────────
CREATE TABLE patient_summary (
    id                    UUID    PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id            UUID    NOT NULL REFERENCES intake_session(id) ON DELETE CASCADE,
    summary_text          TEXT    NOT NULL,
    generation_latency_ms INTEGER NOT NULL DEFAULT 0,
    generated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_summary_session UNIQUE (session_id)
);

-- ── Active Sessions (concurrency tracking) ────
CREATE TABLE active_session (
    id         UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    staff_id   UUID        NOT NULL UNIQUE REFERENCES "user"(id) ON DELETE CASCADE,
    session_id UUID        REFERENCES intake_session(id) ON DELETE SET NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX active_session_expires_idx ON active_session (expires_at);

-- ── Patient Locks (write-lock per patient) ────
CREATE TABLE patient_lock (
    id         UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_id UUID        NOT NULL UNIQUE REFERENCES patient(id) ON DELETE CASCADE,
    staff_id   UUID        NOT NULL REFERENCES "user"(id),
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX patient_lock_expires_idx ON patient_lock (expires_at);
```

---

## Section 2 — Seed Data

Run after Section 1. Replace the placeholder values before running.

### 2a — Department & Questions

```sql
-- Cardio Thoracic Surgery department (fixed ID for reference)
INSERT INTO mstr_department (id, name, llm_system_prompt, llm_summary_prompt, is_active)
VALUES (
    '11111111-1111-1111-1111-111111111111',
    'Cardio Thoracic Surgery',
    'You are a clinical intake assistant supporting Doctor''s Staff in a Cardio Thoracic Surgery department.

You do not speak to the patient directly. The Doctor''s Staff reads your question aloud to the patient and relays the patient''s answer back to you as transcribed text.

Go through every question in the standard question bank one by one in order. For questions that have sub-questions embedded, ask the main question first. If the answer is YES, ask all sub-questions before moving on. If the answer is NO, move directly to the next question.

After all standard questions are covered, review the full conversation and ask any additional clinical follow-up questions your judgment suggests. When fully satisfied, return exactly: INTAKE_COMPLETE

Rules: Ask one question at a time. Never diagnose. Never recommend treatment. Never address the patient directly. Always respond in English.',
    'You are a clinical documentation assistant. Given a patient''s responses to intake questions, generate a structured SOAP-format clinical summary for the senior consultant.

Follow this structure strictly:

---
**PATIENT SUMMARY FOR CONSULTANT REVIEW**
Date: [date]

**S — SUBJECTIVE** (What the patient reports)
- Chief Complaint (CC): Primary reason for visit in patient''s own words
- History of Present Illness (HPI): Onset, location, duration, character, aggravating/relieving factors, timing, severity (use OLDCARTS framework)
- Past Medical History (PMH): Prior diagnoses, hospitalizations, surgeries
- Medications: Current medications, dosages, frequency
- Allergies: Drug/food/environmental allergies and reaction type
- Family History (FH): Relevant hereditary conditions
- Social History (SH): Smoking, alcohol, occupation, lifestyle

**O — OBJECTIVE** (Measurable/observable — populate if data available, else mark N/A)
- Vitals, lab results, imaging if submitted by patient

Rules:
- Use clinical terminology appropriate for a senior physician
- Do NOT diagnose — frame assessments as ''probable'' or ''suggestive of''
- Flag contradictions or gaps in the patient''s responses
- Keep the summary under 400 words unless complexity demands more',
    TRUE
);

-- CT Surgery questions
INSERT INTO mstr_question (id, text, category, is_active) VALUES
  ('aaaaaaaa-0001-0001-0001-000000000001', 'What brings the patient in today?', 'CHIEF_COMPLAINT', TRUE),
  ('aaaaaaaa-0002-0002-0002-000000000002', 'When did the symptoms start?', 'CHIEF_COMPLAINT', TRUE),
  ('aaaaaaaa-0003-0003-0003-000000000003', 'Does the patient have chest pain? If yes — ask: is it sharp, dull, or pressure-like? Where is it located? Does it radiate to the arm or jaw? How long does it last? What triggers it?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0004-0004-0004-000000000004', 'Does the patient have shortness of breath? If yes — ask: is it at rest or only on exertion? How many steps before becoming breathless?', 'RESPIRATORY', TRUE),
  ('aaaaaaaa-0005-0005-0005-000000000005', 'Does the patient have palpitations? If yes — ask: are they fast, irregular, or skipping beats?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0006-0006-0006-000000000006', 'Has the patient had any episodes of dizziness or fainting?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0007-0007-0007-000000000007', 'Does the patient have leg or ankle swelling?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0008-0008-0008-000000000008', 'Does the patient feel unusually fatigued?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0009-0009-0009-000000000009', 'Does the patient wake up at night unable to breathe?', 'CARDIAC', TRUE),
  ('aaaaaaaa-0010-0010-0010-000000000010', 'Does the patient have difficulty breathing when lying flat?', 'RESPIRATORY', TRUE),
  ('aaaaaaaa-0011-0011-0011-000000000011', 'Does the patient have a cough? If yes — ask: is it dry or with phlegm? Any blood in the cough?', 'RESPIRATORY', TRUE),
  ('aaaaaaaa-0012-0012-0012-000000000012', 'Does the patient have wheezing?', 'RESPIRATORY', TRUE),
  ('aaaaaaaa-0013-0013-0013-000000000013', 'Does the patient have any previous heart conditions such as heart attack, heart failure, valve disease, or irregular heart rhythm?', 'HISTORY', TRUE),
  ('aaaaaaaa-0014-0014-0014-000000000014', 'Does the patient have any previous lung conditions such as COPD, asthma, TB, or pneumonia?', 'HISTORY', TRUE),
  ('aaaaaaaa-0015-0015-0015-000000000015', 'Has the patient had any prior cardiac or thoracic surgeries?', 'HISTORY', TRUE),
  ('aaaaaaaa-0016-0016-0016-000000000016', 'Does the patient have hypertension?', 'HISTORY', TRUE),
  ('aaaaaaaa-0017-0017-0017-000000000017', 'Does the patient have diabetes?', 'HISTORY', TRUE),
  ('aaaaaaaa-0018-0018-0018-000000000018', 'Does the patient have high cholesterol?', 'HISTORY', TRUE),
  ('aaaaaaaa-0019-0019-0019-000000000019', 'Does the patient have any kidney problems?', 'HISTORY', TRUE),
  ('aaaaaaaa-0020-0020-0020-000000000020', 'Is there any history of heart disease in the family?', 'FAMILY_HISTORY', TRUE),
  ('aaaaaaaa-0021-0021-0021-000000000021', 'Has there been any sudden cardiac death in the family?', 'FAMILY_HISTORY', TRUE),
  ('aaaaaaaa-0022-0022-0022-000000000022', 'What medications is the patient currently taking? (especially blood thinners, beta blockers, diuretics)', 'MEDICATIONS', TRUE),
  ('aaaaaaaa-0023-0023-0023-000000000023', 'Does the patient have any known allergies?', 'HISTORY', TRUE),
  ('aaaaaaaa-0024-0024-0024-000000000024', 'Does the patient smoke or have a history of smoking? If yes — ask: current or past? How many years? How many per day?', 'LIFESTYLE', TRUE),
  ('aaaaaaaa-0025-0025-0025-000000000025', 'Does the patient consume alcohol?', 'LIFESTYLE', TRUE),
  ('aaaaaaaa-0026-0026-0026-000000000026', 'What is the patient''s exercise tolerance? How much activity before symptoms appear?', 'LIFESTYLE', TRUE),
  ('aaaaaaaa-0027-0027-0027-000000000027', 'Has the patient had any previous ECG, echocardiogram, or angiography done?', 'INVESTIGATIONS', TRUE),
  ('aaaaaaaa-0028-0028-0028-000000000028', 'Does the patient have any known coronary artery disease or valve problems?', 'INVESTIGATIONS', TRUE);

-- Map questions to department with sequence order
INSERT INTO question_dept_map (id, question_id, department_id, sequence_number, is_active) VALUES
  (uuid_generate_v4(), 'aaaaaaaa-0001-0001-0001-000000000001', '11111111-1111-1111-1111-111111111111', 1,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0002-0002-0002-000000000002', '11111111-1111-1111-1111-111111111111', 2,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0003-0003-0003-000000000003', '11111111-1111-1111-1111-111111111111', 3,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0004-0004-0004-000000000004', '11111111-1111-1111-1111-111111111111', 4,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0005-0005-0005-000000000005', '11111111-1111-1111-1111-111111111111', 5,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0006-0006-0006-000000000006', '11111111-1111-1111-1111-111111111111', 6,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0007-0007-0007-000000000007', '11111111-1111-1111-1111-111111111111', 7,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0008-0008-0008-000000000008', '11111111-1111-1111-1111-111111111111', 8,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0009-0009-0009-000000000009', '11111111-1111-1111-1111-111111111111', 9,  TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0010-0010-0010-000000000010', '11111111-1111-1111-1111-111111111111', 10, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0011-0011-0011-000000000011', '11111111-1111-1111-1111-111111111111', 11, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0012-0012-0012-000000000012', '11111111-1111-1111-1111-111111111111', 12, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0013-0013-0013-000000000013', '11111111-1111-1111-1111-111111111111', 13, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0014-0014-0014-000000000014', '11111111-1111-1111-1111-111111111111', 14, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0015-0015-0015-000000000015', '11111111-1111-1111-1111-111111111111', 15, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0016-0016-0016-000000000016', '11111111-1111-1111-1111-111111111111', 16, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0017-0017-0017-000000000017', '11111111-1111-1111-1111-111111111111', 17, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0018-0018-0018-000000000018', '11111111-1111-1111-1111-111111111111', 18, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0019-0019-0019-000000000019', '11111111-1111-1111-1111-111111111111', 19, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0020-0020-0020-000000000020', '11111111-1111-1111-1111-111111111111', 20, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0021-0021-0021-000000000021', '11111111-1111-1111-1111-111111111111', 21, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0022-0022-0022-000000000022', '11111111-1111-1111-1111-111111111111', 22, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0023-0023-0023-000000000023', '11111111-1111-1111-1111-111111111111', 23, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0024-0024-0024-000000000024', '11111111-1111-1111-1111-111111111111', 24, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0025-0025-0025-000000000025', '11111111-1111-1111-1111-111111111111', 25, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0026-0026-0026-000000000026', '11111111-1111-1111-1111-111111111111', 26, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0027-0027-0027-000000000027', '11111111-1111-1111-1111-111111111111', 27, TRUE),
  (uuid_generate_v4(), 'aaaaaaaa-0028-0028-0028-000000000028', '11111111-1111-1111-1111-111111111111', 28, TRUE);
```

### 2b — Admin Accounts

> **Important:** Replace the `pin_hash` values with bcrypt hashes of the actual PINs.
> Generate a hash using: `python -c "import bcrypt; print(bcrypt.hashpw(b'YOUR_PIN', bcrypt.gensalt(12)).decode())"`

```sql
-- Admin 1
INSERT INTO "user" (id, department_id, name, email, pin_hash, role, is_active, failed_pin_attempts)
VALUES (
    '22222222-2222-2222-2222-222222222222',
    '11111111-1111-1111-1111-111111111111',
    'vidhatri',                  -- ← replace
    'saividhatri@hospital.com',    -- ← replace
    '$2b$12$PObF2K73..DT0goeLXpH5OUgz4y4KmzFcQLsITaZqkD6S0.hN0MAa',           -- ← replace (bcrypt hash of your PIN)
    'ADMIN',
    TRUE,
    0
);

-- Admin 2 
INSERT INTO "user" (id, department_id, name, email, pin_hash, role, is_active, failed_pin_attempts)
VALUES (
    '33333333-3333-3333-3333-333333333333',
    '11111111-1111-1111-1111-111111111111',
    'Sahithi',             -- ← replace
    'sahithi@hospital.com', -- ← replace
    '$2b$12$td2698uG5QHRBHSZNZckjO3bfJYELkkqFmIUQpB9p.1XnAt7WwtVe',      -- ← replace (bcrypt hash of  PIN)
    'ADMIN',
    TRUE,
    0
);
```

---

## Section 3 — API Reference (No Direct DB Writes After Setup)

All ongoing operations go through the API. Direct SQL writes to these tables are not allowed after initial setup.

| Operation | API Endpoint |
|-----------|-------------|
| Login | `POST /auth/login` |
| Logout | `POST /auth/logout` |
| Create staff account | `POST /staff` |
| Update / deactivate staff | `PATCH /staff/{id}` |
| Start patient intake | `POST /sessions` |
| Submit answer | `POST /sessions/{id}/respond` |
| Complete session & generate summary | `POST /sessions/{id}/complete` |
| View session + history | `GET /sessions/{id}` |
| View summary | `GET /sessions/{id}/summary` |
| Ask follow-up questions | `POST /sessions/{id}/ask-more` |
| List all sessions | `GET /sessions` |
| List departments | `GET /departments` |

---

## Section 4 — Scheduled Maintenance (Auto)

The application runs an **hourly background job** that automatically cleans up:

```sql
-- These run automatically via APScheduler — DO NOT run manually
DELETE FROM active_session WHERE expires_at < NOW();
DELETE FROM patient_lock   WHERE expires_at < NOW();
DELETE FROM patient        WHERE expires_at < NOW();  -- cascades to all child rows
```

Patient data is retained for **48 hours** from intake, then auto-deleted.