┌─────────────┐        ┌──────────────┐        ┌─────────────┐
 │  DEPARTMENT │        │    DOCTOR    │        │    ROOM     │
 │─────────────│        │──────────────│        │─────────────│
 │ dept_id  PK │◄───────│ dept_id  FK  │        │ room_id  PK │
 │ name        │  N:1   │ doctor_id PK │        │ room_type   │
 │ location    │        │ full_name    │        │ floor       │
 │ head_doc FK │        │ license_no   │        │ is_available│
 └─────────────┘        │ speciality   │        └──────┬──────┘
                        └──────┬───────┘               │
                               │ 1                      │ 1
                               │                        │
                    ┌──────────┼──────────┐             │ N
                    │          │          │      ┌──────┴──────┐
                    │  N       │          │      │  ADMISSION  │
                    ▼          │          │      │─────────────│
          ┌─────────────────┐  │          │      │ admit_id PK │
          │   APPOINTMENT   │  │          │      │ patient_id  │
          │─────────────────│  │          │      │ room_id  FK │
          │ appt_id      PK │  │          │      │ doctor_id FK│
          │ patient_id   FK │  │          │      │ admit_date  │
          │ doctor_id    FK │  │          │      │ disch_date  │
          │ appt_datetime   │  │          │      └──────┬──────┘
          │ status          │  │          │             │
          │ notes           │  │ N        │             │ N
          └───────┬─────────┘  │          │             │
                  │            ▼          ▼             ▼
                  │ 1   ┌─────────────────────────────────┐
                  │     │            PATIENT              │
                  │     │─────────────────────────────────│
                  │     │ patient_id PK                   │
                  │ N   │ full_name, dob, gender          │
                  ▼     │ phone, email, blood_type        │
          ┌───────────────────┐  insurance_provider      │
          │   PRESCRIPTION    │                           │
          │───────────────────│ └─────────────────────────┘
          │ rx_id          PK │
          │ appt_id        FK │        ┌──────────────┐
          │ medication        │        │     BILL     │
          │ dosage            │        │──────────────│
          │ duration_days     │        │ bill_id   PK │
          │ instructions      │        │ patient_id FK│
          └───────────────────┘        │ appt_id   FK │
                                       │ total_amount │
                                       │ amount_paid  │
                                       │ status       │
                                       └──────────────┘

-- ─────────────────────────────────────────
-- Hospital Management System — Full Schema
-- ─────────────────────────────────────────

CREATE TABLE departments (
    dept_id      SERIAL        PRIMARY KEY,
    name         VARCHAR(100)  NOT NULL UNIQUE,
    location     VARCHAR(100),
    head_doc_id  INT           -- FK added after doctors table
);

CREATE TABLE doctors (
    doctor_id    SERIAL        PRIMARY KEY,
    full_name    VARCHAR(150)  NOT NULL,
    license_no   VARCHAR(50)   NOT NULL UNIQUE,
    speciality   VARCHAR(100),
    dept_id      INT           REFERENCES departments(dept_id) ON DELETE SET NULL,
    phone        VARCHAR(20),
    email        VARCHAR(150)  UNIQUE
);

-- Now add the head doctor FK
ALTER TABLE departments
    ADD CONSTRAINT fk_head_doc
    FOREIGN KEY (head_doc_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL;

CREATE TABLE patients (
    patient_id         SERIAL       PRIMARY KEY,
    full_name          VARCHAR(150) NOT NULL,
    dob                DATE         NOT NULL,
    gender             CHAR(1)      CHECK (gender IN ('M', 'F', 'O')),
    phone              VARCHAR(20),
    email              VARCHAR(150),
    blood_type         VARCHAR(5),
    insurance_provider VARCHAR(100),
    insurance_no       VARCHAR(80),
    registered_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rooms (
    room_id      SERIAL      PRIMARY KEY,
    room_number  VARCHAR(10) NOT NULL UNIQUE,
    room_type    VARCHAR(20) CHECK (room_type IN ('ICU', 'Ward', 'Private', 'Semi-Private')),
    floor        SMALLINT,
    is_available BOOLEAN     DEFAULT TRUE
);

CREATE TABLE appointments (
    appt_id       SERIAL      PRIMARY KEY,
    patient_id    INT         NOT NULL REFERENCES patients(patient_id) ON DELETE CASCADE,
    doctor_id     INT         NOT NULL REFERENCES doctors(doctor_id) ON DELETE RESTRICT,
    appt_datetime TIMESTAMP   NOT NULL,
    status        VARCHAR(20) DEFAULT 'scheduled'
                              CHECK (status IN ('scheduled', 'completed', 'cancelled', 'no-show')),
    notes         TEXT,
    created_at    TIMESTAMP   DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE prescriptions (
    rx_id          SERIAL       PRIMARY KEY,
    appt_id        INT          NOT NULL REFERENCES appointments(appt_id) ON DELETE CASCADE,
    medication     VARCHAR(200) NOT NULL,
    dosage         VARCHAR(100),
    duration_days  SMALLINT     CHECK (duration_days > 0),
    instructions   TEXT,
    prescribed_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admissions (
    admit_id      SERIAL    PRIMARY KEY,
    patient_id    INT       NOT NULL REFERENCES patients(patient_id) ON DELETE CASCADE,
    room_id       INT       NOT NULL REFERENCES rooms(room_id),
    doctor_id     INT       NOT NULL REFERENCES doctors(doctor_id),
    admit_date    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    discharge_date TIMESTAMP,
    diagnosis     TEXT,
    CONSTRAINT chk_dates CHECK (discharge_date IS NULL OR discharge_date > admit_date)
);

CREATE TABLE bills (
    bill_id       SERIAL         PRIMARY KEY,
    patient_id    INT            NOT NULL REFERENCES patients(patient_id),
    appt_id       INT            REFERENCES appointments(appt_id),
    total_amount  DECIMAL(10,2)  NOT NULL CHECK (total_amount >= 0),
    amount_paid   DECIMAL(10,2)  DEFAULT 0 CHECK (amount_paid >= 0),
    status        VARCHAR(20)    DEFAULT 'pending'
                                 CHECK (status IN ('pending', 'partial', 'paid', 'overdue')),
    issued_at     TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    due_date      DATE
);

SELECT
    a.appt_id,
    p.full_name          AS patient_name,
    a.appt_datetime,
    a.status,
    a.notes
FROM appointments a
JOIN patients p ON a.patient_id = p.patient_id
JOIN doctors  d ON a.doctor_id  = d.doctor_id
WHERE d.full_name = 'Dr. Sarah Smith'
  AND a.appt_datetime >= DATE_TRUNC('week', CURRENT_DATE)
  AND a.appt_datetime <  DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '7 days'
ORDER BY a.appt_datetime;

 appt_id | patient_name   | appt_datetime        | status    | notes
---------+----------------+----------------------+-----------+--------
 1042    | James Carter   | 2024-06-03 09:00:00  | scheduled | Follow-up
 1051    | Maria Gonzalez | 2024-06-04 14:30:00  | scheduled | NULL
 1063    | Wei Zhang      | 2024-06-06 11:00:00  | scheduled | Post-op check

SELECT
    p.patient_id,
    p.full_name,
    p.phone,
    b.bill_id,
    b.total_amount,
    b.amount_paid,
    (b.total_amount - b.amount_paid) AS balance_due,
    b.due_date
FROM bills b
JOIN patients p ON b.patient_id = p.patient_id
WHERE b.status IN ('pending', 'overdue')
  AND (b.total_amount - b.amount_paid) > 1000
ORDER BY balance_due DESC;

 patient_id | full_name      | bill_id | total_amount | amount_paid | balance_due | due_date
------------+----------------+---------+--------------+-------------+-------------+------------
 304        | Robert Hall    | 2891    | 8500.00      | 0.00        | 8500.00     | 2024-06-15
 219        | Linda Torres   | 2744    | 3200.00      | 1000.00     | 2200.00     | 2024-05-30
 512        | Ahmed Hassan   | 3012    | 1450.00      | 0.00        | 1450.00     | 2024-06-20

SELECT
    d.name             AS department,
    COUNT(a.appt_id)   AS total_appointments,
    COUNT(CASE WHEN a.status = 'completed' THEN 1 END)  AS completed,
    COUNT(CASE WHEN a.status = 'cancelled' THEN 1 END)  AS cancelled
FROM departments d
JOIN doctors     dc ON dc.dept_id  = d.dept_id
JOIN appointments a ON a.doctor_id = dc.doctor_id
GROUP BY d.dept_id, d.name
ORDER BY total_appointments DESC;

 department       | total_appointments | completed | cancelled
------------------+--------------------+-----------+-----------
 Cardiology       | 1842               | 1701      | 89
 Orthopedics      | 1234               | 1190      | 31
 Pediatrics       | 987                | 942       | 22
 Neurology        | 756                | 701       | 44

SELECT
    d.doctor_id,
    d.full_name,
    d.speciality,
    dept.name AS department
FROM doctors d
JOIN departments dept ON d.dept_id = dept.dept_id
WHERE d.doctor_id NOT IN (
    SELECT doctor_id FROM appointments
    WHERE appt_datetime BETWEEN '2024-06-05 10:00:00' AND '2024-06-05 11:00:00'
      AND status = 'scheduled'
)
ORDER BY d.speciality, d.full_name;

BEGIN;

-- Step 1: Find an available room
SELECT room_id INTO _room FROM rooms
WHERE room_type = 'Ward' AND is_available = TRUE
LIMIT 1 FOR UPDATE;

-- Step 2: Mark room as occupied
UPDATE rooms SET is_available = FALSE WHERE room_id = _room;

-- Step 3: Create admission record
INSERT INTO admissions (patient_id, room_id, doctor_id, diagnosis)
VALUES (304, _room, 12, 'Acute appendicitis — pre-op observation');

-- Step 4: Generate initial bill
INSERT INTO bills (patient_id, total_amount, status, due_date)
VALUES (304, 2500.00, 'pending', CURRENT_DATE + INTERVAL '30 days');

COMMIT;
-- If anything fails: ROLLBACK — room stays available, no orphan admission record

-- High-traffic lookup: patient appointments by date
CREATE INDEX idx_appt_doctor_date    ON appointments (doctor_id, appt_datetime);
CREATE INDEX idx_appt_patient        ON appointments (patient_id);

-- Billing queries
CREATE INDEX idx_bills_patient_status ON bills (patient_id, status);

-- Admission lookups
CREATE INDEX idx_admission_patient   ON admissions (patient_id);
CREATE INDEX idx_rooms_available_type ON rooms (is_available, room_type);

-- Doctor search by department
CREATE INDEX idx_doctors_dept        ON doctors (dept_id);

{
  "_id": "patient_304",
  "full_name": "Robert Hall",
  "appointments": [
    {
      "date": "2024-05-15",
      "doctor": "Dr. Smith",
      "prescriptions": [
        { "medication": "Amoxicillin", "dosage": "500mg", "days": 7 }
      ],
      "bill": { "amount": 350, "status": "paid" }
    }
  ],
  "admissions": [...]
}