Database Schema - งานพยาบาลผู้ป่วยนอก (Outpatient Nursing Services System)

Document Version: 1.0
Date: 29 สิงหาคม 2025
Integration with: MediTech Hospital Information System - MASTER_DATABASE_SCHEMA
Backend: Nest.js + TypeScript + Prisma ORM
Database: PostgreSQL 15+ + Redis 7+

เอกสารนี้รวบรวมโครงสร้างตารางฐานข้อมูล (Database Schema) สำหรับโมดูลงานพยาบาลผู้ป่วยนอก ที่ปรับให้สอดคล้องกับ Master Database Schema ของระบบ MediTech HIS เพื่อให้มีการบูรณาการที่สมบูรณ์แบบระหว่างโมดูลต่างๆ

---

Table of Contents

1. ตาราง nursing_queues
2. ตาราง nursing_stations
3. ตาราง vital_signs_records
4. ตาราง iot_devices
5. ตาราง nursing_assessments
6. ตาราง triage_categories
7. ตาราง chief_complaints
8. ตาราง complaint_templates
9. ตาราง drug_allergies
10. ตาราง chronic_conditions
11. ตาราง nursing_activities
12. ตาราง nursing_procedures
13. ตาราง pre_clinic_orders
14. ตาราง internal_consultations
15. ตาราง patient_education_records
16. ตาราง wound_care_records
17. ตาราง injection_records
18. ตาราง nursing_notes

---

SHARED FOUNDATION TABLES (จาก Master Schema)

หมายเหตุ: ตารางหลักเหล่านี้ถูกกำหนดใน MASTER_DATABASE_SCHEMA.md และใช้ร่วมกันทุกโมดูล

Foundation Tables ที่ใช้ร่วมกัน:

1. patients - ข้อมูลผู้ป่วยหลัก (Master Patient Index)
2. users - บุคลากรทางการแพทย์และพยาบาล (รวม nursing roles)
3. medical_visits - ข้อมูลการมารับบริการ (Clinical Encounters)
4. departments - โครงสร้างแผนกโรงพยาบาล
5. clinics - คลินิกต่างๆ ภายในแผนก
6. appointments - ระบบนัดหมายหลัก
7. queues - ระบบคิวหลัก (integrated กับ nursing_queues)
8. medical_orders - คำสั่งทางการแพทย์จากระบบ CPOE (รวม nursing orders)
9. audit_logs - บันทึกการตรวจสอบสำหรับ compliance
10. digital_signatures - ลายเซ็นอิเล็กทรอนิกส์สำหรับเอกสารทางการแพทย์
11. notifications - ระบบแจ้งเตือนแบบ Multi-channel
12. roles และ permissions - ระบบ RBAC สำหรับ nursing permissions

Nursing-Specific Integration Points: - การเชื่อมโยงกับ queues table สำหรับ nursing queue management - การใช้ medical_orders สำหรับ pre-clinic และ nursing orders - Integration กับ appointments สำหรับ scheduled nursing procedures - รองรับ Digital signatures สำหรับ nursing documentation compliance - Multi-channel notifications สำหรับ patient care coordination

Benefits: - ไม่มีการสร้างตารางซ้ำซ้อน - ข้อมูลสอดคล้องกันระหว่าง OPD, Emergency, และ Nursing modules
- รองรับ Real-time patient care coordination - ปฏิบัติตาม Healthcare documentation standards

---

NURSING-SPECIFIC TABLES

1. ตาราง nursing_queues

ตารางสำหรับจัดการคิูผู้ป่วยเฉพาะสำหรับงานพยาบาล พร้อมระบบจัดลำดับความสำคัญ

CREATE TABLE nursing_queues (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Queue Information
    queue_number VARCHAR(20) NOT NULL,
    queue_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Patient and Visit References (from Master Schema)
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID REFERENCES medical_visits(id),

    -- Location and Department
    department_id UUID NOT NULL REFERENCES departments(id),
    nursing_station_id UUID REFERENCES nursing_stations(id),
    clinic_id UUID REFERENCES clinics(id),

    -- Queue Management
    queue_type VARCHAR(20) DEFAULT 'screening' CHECK (queue_type IN (
        'screening', 'vital_signs', 'consultation', 'procedure', 'medication', 'discharge', 'admission'
    )),
    queue_status VARCHAR(20) DEFAULT 'waiting' CHECK (queue_status IN (
        'waiting', 'called', 'in_progress', 'completed', 'cancelled', 'no_show', 'transferred'
    )),

    -- Priority System (Higher number = Higher priority)
    priority INTEGER DEFAULT 5 CHECK (priority BETWEEN 1 AND 10),
    priority_reason TEXT,

    -- Triage Classification
    triage_level VARCHAR(10) CHECK (triage_level IN ('1', '2', '3', '4', '5')),
    triage_color VARCHAR(10) CHECK (triage_color IN ('red', 'orange', 'yellow', 'green', 'blue')),
    triage_assessed_by UUID REFERENCES users(id),
    triage_assessed_at TIMESTAMP WITH TIME ZONE,

    -- Nursing Staff Assignment
    assigned_nurse_id UUID REFERENCES users(id),
    screening_nurse_id UUID REFERENCES users(id),

    -- Timing Information
    estimated_wait_time INTEGER, -- minutes
    estimated_service_time INTEGER, -- minutes

    -- Queue Flow Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    called_at TIMESTAMP WITH TIME ZONE,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,

    -- Service Details
    chief_complaint_brief TEXT,
    special_needs TEXT,
    isolation_required BOOLEAN DEFAULT FALSE,
    wheelchair_needed BOOLEAN DEFAULT FALSE,

    -- Alert Flags
    allergy_alert BOOLEAN DEFAULT FALSE,
    fall_risk BOOLEAN DEFAULT FALSE,
    infection_control_alert BOOLEAN DEFAULT FALSE,

    -- Notes and Comments
    nursing_notes TEXT,
    handover_notes TEXT,

    -- Integration
    external_queue_id VARCHAR(50),

    -- Timestamps
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

คำอธิบายฟิลด์:

Field	Type	Constraints	คำอธิบาย
queue_number	VARCHAR(20)	NOT NULL	หมายเลขคิวสำหรับงานพยาบาล
queue_type	VARCHAR(20)	CHECK	ประเภทคิว (screening, vital_signs, consultation, procedure, medication, discharge, admission)
queue_status	VARCHAR(20)	CHECK	สถานะคิว (waiting, called, in_progress, completed, cancelled, no_show, transferred)
priority	INTEGER	CHECK 1-10	ระดับความสำคัญ (1=ต่ำสุด, 10=สูงสุด)
triage_level	VARCHAR(10)	CHECK 1-5	ระดับการคัดแยก (1=วิกฤติ, 5=ไม่เร่งด่วน)
triage_color	VARCHAR(10)	CHECK	สีการคัดแยก (red, orange, yellow, green, blue)
assigned_nurse_id	UUID	FK	รหัสพยาบาลที่รับผิดชอบ
screening_nurse_id	UUID	FK	รหัสพยาบาลคัดกรอง
allergy_alert	BOOLEAN		แจ้งเตือนการแพ้ยา
fall_risk	BOOLEAN		ความเสี่ยงการหกล้ม
infection_control_alert	BOOLEAN		การควบคุมการติดเชื้อ

---

2. ตาราง nursing_stations

ตารางสำหรับจัดการสถานีพยาบาลและการกระจายงาน

CREATE TABLE nursing_stations (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Station Information
    station_code VARCHAR(20) UNIQUE NOT NULL,
    station_name_thai VARCHAR(100) NOT NULL,
    station_name_eng VARCHAR(100),
    description TEXT,

    -- Location
    department_id UUID NOT NULL REFERENCES departments(id),
    building VARCHAR(50),
    floor VARCHAR(10),
    room_range VARCHAR(50), -- "101-120, 201-220"

    -- Capacity and Equipment
    max_nurses INTEGER DEFAULT 3,
    current_nurses INTEGER DEFAULT 0,
    max_patients_per_shift INTEGER DEFAULT 30,

    -- Service Types
    services_provided JSONB, -- ["screening", "vital_signs", "procedures", "medication"]
    specialties JSONB, -- ["general", "pediatric", "geriatric"]

    -- Operating Schedule
    operating_hours JSONB, -- {"monday": {"start": "08:00", "end": "20:00"}}
    shift_patterns JSONB,

    -- Equipment and Resources
    available_equipment JSONB,
    required_certifications JSONB,

    -- Status and Settings
    is_active BOOLEAN DEFAULT TRUE,
    is_emergency_backup BOOLEAN DEFAULT FALSE,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

3. ตาราง vital_signs_records

ตารางสำหรับบันทึก Vital Signs พร้อมการเชื่อมต่อ IoT และการแจ้งเตือนอัตโนมัติ

CREATE TABLE vital_signs_records (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Measurement Information
    measured_by UUID NOT NULL REFERENCES users(id),
    measurement_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    measurement_location VARCHAR(100), -- "Screening Room A", "Bedside"

    -- Basic Vital Signs
    systolic_bp INTEGER CHECK (systolic_bp BETWEEN 50 AND 300),
    diastolic_bp INTEGER CHECK (diastolic_bp BETWEEN 30 AND 200),
    mean_arterial_pressure INTEGER, -- calculated
    pulse_rate INTEGER CHECK (pulse_rate BETWEEN 30 AND 200),
    respiratory_rate INTEGER CHECK (respiratory_rate BETWEEN 8 AND 60),
    body_temperature DECIMAL(4,1) CHECK (body_temperature BETWEEN 30.0 AND 45.0),
    oxygen_saturation INTEGER CHECK (oxygen_saturation BETWEEN 70 AND 100),

    -- Physical Measurements
    weight DECIMAL(5,2) CHECK (weight BETWEEN 0.5 AND 500.0), -- kg
    height DECIMAL(5,2) CHECK (height BETWEEN 30.0 AND 250.0), -- cm
    bmi DECIMAL(4,1) GENERATED ALWAYS AS (
        CASE 
            WHEN height IS NOT NULL AND height > 0 AND weight IS NOT NULL 
            THEN ROUND((weight / POWER(height/100, 2))::NUMERIC, 1)
            ELSE NULL 
        END
    ) STORED,

    -- Additional Measurements
    waist_circumference DECIMAL(5,2) CHECK (waist_circumference BETWEEN 30.0 AND 200.0), -- cm
    head_circumference DECIMAL(5,2) CHECK (head_circumference BETWEEN 25.0 AND 70.0), -- cm (pediatric)

    -- Women's Health
    last_menstrual_period DATE,
    gestational_age_weeks INTEGER CHECK (gestational_age_weeks BETWEEN 1 AND 50),

    -- Pain Assessment
    pain_score INTEGER CHECK (pain_score BETWEEN 0 AND 10),
    pain_location VARCHAR(200),
    pain_quality VARCHAR(100), -- "sharp", "dull", "burning", "cramping"
    pain_duration VARCHAR(50), -- "continuous", "intermittent", "< 1 hour"
    pain_aggravating_factors TEXT,
    pain_relieving_factors TEXT,

    -- Device and Method Information
    measurement_method VARCHAR(50), -- "manual", "automatic", "iot_device"
    device_id UUID REFERENCES iot_devices(id),
    device_serial_number VARCHAR(100),
    device_calibration_date DATE,

    -- Position and Context
    patient_position VARCHAR(20) CHECK (patient_position IN (
        'sitting', 'standing', 'supine', 'left_lateral', 'right_lateral', 'prone'
    )),
    arm_used VARCHAR(10) CHECK (arm_used IN ('left', 'right', 'both')),
    cuff_size VARCHAR(20), -- for blood pressure

    -- Quality and Validation
    measurement_quality VARCHAR(20) DEFAULT 'good' CHECK (measurement_quality IN (
        'excellent', 'good', 'fair', 'poor', 'invalid'
    )),
    validation_status VARCHAR(20) DEFAULT 'pending' CHECK (validation_status IN (
        'pending', 'validated', 'flagged', 'rejected'
    )),
    validated_by UUID REFERENCES users(id),
    validated_at TIMESTAMP WITH TIME ZONE,
    validation_notes TEXT,

    -- Alert System
    alerts_generated JSONB, -- [{"type": "hypertension", "level": "moderate", "message": "BP > 140/90"}]
    critical_alerts BOOLEAN DEFAULT FALSE,
    alert_acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by UUID REFERENCES users(id),
    acknowledged_at TIMESTAMP WITH TIME ZONE,

    -- Data Source and Integration
    source_system VARCHAR(50), -- "manual_entry", "iot_omron_bp", "iot_tanita_scale"
    integration_id VARCHAR(100),
    raw_data JSONB, -- original data from IoT devices

    -- Repeat Measurements
    is_repeat_measurement BOOLEAN DEFAULT FALSE,
    original_measurement_id UUID REFERENCES vital_signs_records(id),
    repeat_reason VARCHAR(100), -- "abnormal_values", "patient_request", "technical_error"

    -- Clinical Context
    pre_medication BOOLEAN DEFAULT FALSE,
    post_medication BOOLEAN DEFAULT FALSE,
    related_medication TEXT,
    clinical_context VARCHAR(100), -- "pre_surgery", "post_procedure", "routine_screening"

    -- Notes and Comments
    measurement_notes TEXT,
    patient_cooperation VARCHAR(20) CHECK (patient_cooperation IN (
        'excellent', 'good', 'fair', 'poor', 'uncooperative'
    )),
    environmental_factors TEXT, -- "noisy environment", "patient anxious", "equipment malfunction"

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

4. ตาราง iot_devices

ตารางสำหรับจัดการอุปกรณ์ IoT ที่ใช้วัด Vital Signs

CREATE TABLE iot_devices (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Device Information
    device_name VARCHAR(100) NOT NULL,
    device_type VARCHAR(50) NOT NULL CHECK (device_type IN (
        'blood_pressure_monitor', 'weight_scale', 'height_measure', 'thermometer', 
        'pulse_oximeter', 'glucometer', 'ecg_monitor'
    )),
    manufacturer VARCHAR(100) NOT NULL,
    model_number VARCHAR(50) NOT NULL,
    serial_number VARCHAR(100) UNIQUE NOT NULL,

    -- Location and Assignment
    nursing_station_id UUID REFERENCES nursing_stations(id),
    current_location VARCHAR(100),
    assigned_to_user UUID REFERENCES users(id),

    -- Technical Specifications
    firmware_version VARCHAR(20),
    software_version VARCHAR(20),
    communication_protocol VARCHAR(20) CHECK (communication_protocol IN (
        'bluetooth', 'wifi', 'usb', 'serial', 'zigbee'
    )),
    mac_address VARCHAR(17),
    ip_address INET,

    -- Connectivity and Status
    connection_status VARCHAR(20) DEFAULT 'disconnected' CHECK (connection_status IN (
        'connected', 'disconnected', 'error', 'maintenance'
    )),
    last_communication TIMESTAMP WITH TIME ZONE,
    battery_level INTEGER CHECK (battery_level BETWEEN 0 AND 100),
    signal_strength INTEGER CHECK (signal_strength BETWEEN 0 AND 100),

    -- Calibration and Maintenance
    last_calibration_date DATE,
    calibration_due_date DATE,
    calibration_interval_days INTEGER DEFAULT 365,
    calibration_status VARCHAR(20) DEFAULT 'valid' CHECK (calibration_status IN (
        'valid', 'due', 'overdue', 'invalid'
    )),

    -- Maintenance Schedule
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    maintenance_interval_days INTEGER DEFAULT 90,
    warranty_expiry_date DATE,

    -- Performance Metrics
    total_measurements INTEGER DEFAULT 0,
    successful_transmissions INTEGER DEFAULT 0,
    failed_transmissions INTEGER DEFAULT 0,
    average_response_time DECIMAL(8,2), -- milliseconds
    last_error_message TEXT,
    last_error_timestamp TIMESTAMP WITH TIME ZONE,

    -- Configuration
    device_settings JSONB,
    measurement_units JSONB, -- {"weight": "kg", "height": "cm", "temperature": "celsius"}
    alert_thresholds JSONB,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_operational BOOLEAN DEFAULT TRUE,

    -- Timestamps
    installed_date DATE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

5. ตaราง nursing_assessments

ตารางสำหรับการประเมินผู้ป่วยทางพยาบาลและระบบ Triage

CREATE TABLE nursing_assessments (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Assessment Information
    assessment_type VARCHAR(30) NOT NULL CHECK (assessment_type IN (
        'initial_screening', 'triage', 'focused', 'comprehensive', 'discharge', 'fall_risk'
    )),
    assessed_by UUID NOT NULL REFERENCES users(id),
    assessment_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Triage Assessment (ESI - Emergency Severity Index)
    triage_level INTEGER CHECK (triage_level BETWEEN 1 AND 5),
    triage_color VARCHAR(10) CHECK (triage_color IN ('red', 'orange', 'yellow', 'green', 'blue')),
    triage_rationale TEXT,
    immediate_intervention_needed BOOLEAN DEFAULT FALSE,

    -- Primary Assessment (ABCDE)
    airway_status VARCHAR(20) CHECK (airway_status IN ('patent', 'compromised', 'obstructed')),
    breathing_status VARCHAR(20) CHECK (breathing_status IN ('adequate', 'labored', 'inadequate')),
    circulation_status VARCHAR(20) CHECK (circulation_status IN ('stable', 'unstable', 'shock')),
    disability_status VARCHAR(20) CHECK (disability_status IN ('alert', 'verbal', 'pain', 'unresponsive')),
    exposure_concerns BOOLEAN DEFAULT FALSE,

    -- Glasgow Coma Scale (GCS)
    gcs_eye_opening INTEGER CHECK (gcs_eye_opening BETWEEN 1 AND 4),
    gcs_verbal_response INTEGER CHECK (gcs_verbal_response BETWEEN 1 AND 5),
    gcs_motor_response INTEGER CHECK (gcs_motor_response BETWEEN 1 AND 6),
    gcs_total INTEGER GENERATED ALWAYS AS (
        COALESCE(gcs_eye_opening, 0) + COALESCE(gcs_verbal_response, 0) + COALESCE(gcs_motor_response, 0)
    ) STORED,

    -- Pain Assessment (Comprehensive)
    pain_scale_used VARCHAR(20) CHECK (pain_scale_used IN (
        'numeric_0_10', 'wong_baker', 'flacc', 'behavioral'
    )),
    pain_score INTEGER CHECK (pain_score BETWEEN 0 AND 10),
    pain_onset VARCHAR(20) CHECK (pain_onset IN ('sudden', 'gradual', 'chronic')),
    pain_frequency VARCHAR(20) CHECK (pain_frequency IN ('constant', 'intermittent', 'episodic')),
    pain_radiation BOOLEAN DEFAULT FALSE,
    pain_radiation_pattern TEXT,

    -- Functional Assessment
    mobility_level VARCHAR(20) CHECK (mobility_level IN (
        'independent', 'assistance_needed', 'wheelchair', 'bedbound'
    )),
    fall_risk_score INTEGER CHECK (fall_risk_score BETWEEN 0 AND 25), -- Morse Fall Scale
    fall_risk_level VARCHAR(10) CHECK (fall_risk_level IN ('low', 'moderate', 'high')),

    -- Cognitive Assessment
    mental_status VARCHAR(20) CHECK (mental_status IN (
        'alert_oriented', 'confused', 'agitated', 'lethargic', 'unconscious'
    )),
    orientation_person BOOLEAN DEFAULT TRUE,
    orientation_place BOOLEAN DEFAULT TRUE,
    orientation_time BOOLEAN DEFAULT TRUE,
    memory_impairment BOOLEAN DEFAULT FALSE,

    -- Nutritional Screening
    nutritional_risk VARCHAR(20) CHECK (nutritional_risk IN ('low', 'moderate', 'high')),
    appetite_level VARCHAR(20) CHECK (appetite_level IN ('good', 'fair', 'poor', 'none')),
    swallowing_ability VARCHAR(20) CHECK (swallowing_ability IN ('normal', 'impaired', 'unsafe')),
    weight_loss_recent BOOLEAN DEFAULT FALSE,

    -- Skin Assessment
    skin_integrity VARCHAR(20) CHECK (skin_integrity IN ('intact', 'compromised', 'breakdown')),
    pressure_ulcer_risk VARCHAR(10) CHECK (pressure_ulcer_risk IN ('low', 'moderate', 'high')),
    braden_scale_score INTEGER CHECK (braden_scale_score BETWEEN 6 AND 23),
    wound_present BOOLEAN DEFAULT FALSE,

    -- Psychosocial Assessment
    anxiety_level VARCHAR(20) CHECK (anxiety_level IN ('none', 'mild', 'moderate', 'severe')),
    depression_screening VARCHAR(20) CHECK (depression_screening IN ('negative', 'mild', 'moderate', 'severe')),
    family_support VARCHAR(20) CHECK (family_support IN ('excellent', 'good', 'limited', 'none')),
    social_concerns BOOLEAN DEFAULT FALSE,

    -- Cultural and Language Needs
    primary_language VARCHAR(50),
    interpreter_needed BOOLEAN DEFAULT FALSE,
    cultural_considerations TEXT,
    religious_dietary_restrictions BOOLEAN DEFAULT FALSE,

    -- Assessment Tools Used
    assessment_tools_used JSONB, -- ["morse_fall_scale", "braden_scale", "esi_triage"]
    structured_data JSONB, -- detailed assessment data in JSON format

    -- Clinical Priorities
    nursing_diagnoses JSONB, -- NANDA-I nursing diagnoses
    priority_interventions JSONB,
    immediate_needs JSONB,

    -- Communication and Handoff
    key_findings TEXT,
    recommendations TEXT,
    handoff_communication TEXT,
    family_notification_needed BOOLEAN DEFAULT FALSE,

    -- Follow-up Requirements
    reassessment_interval INTEGER, -- hours
    next_assessment_due TIMESTAMP WITH TIME ZONE,
    escalation_criteria TEXT,

    -- Quality and Validation
    assessment_completeness VARCHAR(20) DEFAULT 'complete' CHECK (assessment_completeness IN (
        'complete', 'partial', 'incomplete', 'deferred'
    )),
    reviewed_by UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,

    -- Documentation and Notes
    detailed_notes TEXT,
    objective_findings TEXT,
    subjective_findings TEXT,
    plan_of_care TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

6. ตาราง triage_categories

ตารางสำหรับจัดการหมวดหมู่การคัดแยกผู้ป่วย

CREATE TABLE triage_categories (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Category Information
    category_code VARCHAR(10) UNIQUE NOT NULL, -- "ESI-1", "ESI-2", etc.
    category_name_thai VARCHAR(100) NOT NULL,
    category_name_eng VARCHAR(100),
    triage_level INTEGER NOT NULL CHECK (triage_level BETWEEN 1 AND 5),
    color_code VARCHAR(10) NOT NULL CHECK (color_code IN ('red', 'orange', 'yellow', 'green', 'blue')),

    -- Clinical Criteria
    clinical_description TEXT,
    inclusion_criteria JSONB,
    exclusion_criteria JSONB,

    -- Timing Requirements
    target_assessment_time INTEGER, -- minutes from arrival
    target_physician_time INTEGER, -- minutes from triage
    maximum_wait_time INTEGER, -- minutes before re-triage

    -- Resource Requirements
    required_interventions JSONB,
    resource_needs JSONB,
    monitoring_frequency INTEGER, -- minutes between checks

    -- Examples and Guidelines
    clinical_examples JSONB,
    decision_tree JSONB,
    age_specific_criteria JSONB,

    -- Department Specific
    department_id UUID REFERENCES departments(id),
    applies_to_age_groups JSONB, -- ["adult", "pediatric", "geriatric"]

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    effective_date DATE DEFAULT CURRENT_DATE,
    version INTEGER DEFAULT 1,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

7. ตาราง chief_complaints

ตารางสำหรับบันทึกอาการสำคัญของผู้ป่วย

CREATE TABLE chief_complaints (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Recording Information
    recorded_by UUID NOT NULL REFERENCES users(id),
    recorded_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    department_id UUID NOT NULL REFERENCES departments(id),

    -- Complaint Details
    chief_complaint TEXT NOT NULL,
    complaint_category VARCHAR(50), -- "pain", "respiratory", "gastrointestinal", etc.
    template_used_id UUID REFERENCES complaint_templates(id),

    -- Symptom Characteristics
    onset_datetime TIMESTAMP WITH TIME ZONE,
    duration VARCHAR(50), -- "2 hours", "3 days", "chronic"
    onset_type VARCHAR(20) CHECK (onset_type IN ('sudden', 'gradual', 'insidious')),

    -- Severity Assessment
    severity_scale VARCHAR(20) CHECK (severity_scale IN ('mild', 'moderate', 'severe')),
    severity_score INTEGER CHECK (severity_score BETWEEN 1 AND 10),
    progression VARCHAR(20) CHECK (progression IN ('improving', 'stable', 'worsening')),

    -- Symptom Details
    primary_symptoms JSONB, -- [{"symptom": "chest pain", "severity": 8, "location": "substernal"}]
    associated_symptoms JSONB,
    aggravating_factors JSONB,
    relieving_factors JSONB,

    -- Location and Quality
    primary_location VARCHAR(100),
    radiation_pattern TEXT,
    quality_description TEXT, -- "sharp", "dull", "burning", "cramping"

    -- Patient's Perspective
    patient_concerns TEXT,
    patient_expectations TEXT,
    impact_on_activities TEXT,

    -- Clinical Context
    previous_episodes BOOLEAN DEFAULT FALSE,
    similar_episodes_history TEXT,
    recent_changes TEXT,
    current_medications_related TEXT,

    -- Triage Implications
    red_flag_symptoms JSONB, -- warning signs requiring immediate attention
    triage_impact VARCHAR(20) CHECK (triage_impact IN ('none', 'minor', 'moderate', 'major')),
    urgent_features BOOLEAN DEFAULT FALSE,

    -- Documentation Standards
    history_completeness VARCHAR(20) DEFAULT 'complete' CHECK (history_completeness IN (
        'complete', 'partial', 'limited', 'deferred'
    )),
    language_barrier BOOLEAN DEFAULT FALSE,
    interpreter_used BOOLEAN DEFAULT FALSE,
    informant VARCHAR(50) DEFAULT 'patient', -- "patient", "family", "caregiver", "translator"

    -- Structured Data
    icd_10_suggestions JSONB, -- suggested ICD-10 codes based on complaint
    nursing_diagnoses_suggestions JSONB,
    structured_complaint_data JSONB,

    -- Follow-up and Handoff
    key_points_for_physician TEXT,
    nursing_priorities TEXT,
    immediate_actions_taken JSONB,

    -- Quality and Review
    reviewed_by UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    quality_score INTEGER CHECK (quality_score BETWEEN 1 AND 5),

    -- Additional Notes
    additional_notes TEXT,
    family_input TEXT,
    environmental_factors TEXT,

    -- Search and Classification
    search_keywords TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('thai', 
            chief_complaint || ' ' || 
            COALESCE(additional_notes, '') || ' ' ||
            COALESCE(patient_concerns, '')
        )
    ) STORED,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

8. ตาราง complaint_templates

ตารางสำหรับเก็บ Template การบันทึกอาการสำคัญตามแผนก

CREATE TABLE complaint_templates (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Template Information
    template_name VARCHAR(200) NOT NULL,
    template_code VARCHAR(50) UNIQUE NOT NULL,
    template_category VARCHAR(50) NOT NULL, -- "general", "cardiology", "respiratory", "pediatric"

    -- Department and Specialty
    department_id UUID REFERENCES departments(id),
    specialty VARCHAR(100),
    target_age_group VARCHAR(20) CHECK (target_age_group IN ('all', 'pediatric', 'adult', 'geriatric')),

    -- Template Structure
    template_fields JSONB NOT NULL, -- detailed field definitions
    required_fields JSONB, -- list of mandatory fields
    conditional_fields JSONB, -- fields that appear based on responses

    -- Clinical Guidelines
    assessment_guidelines TEXT,
    red_flag_indicators JSONB,
    triage_scoring_rules JSONB,

    -- Common Presentations
    common_complaints JSONB, -- frequently used phrases/symptoms
    quick_select_options JSONB, -- dropdown options for common selections
    default_values JSONB,

    -- Validation Rules
    validation_rules JSONB,
    required_combinations JSONB, -- fields that must be completed together

    -- Usage Statistics
    usage_count INTEGER DEFAULT 0,
    last_used_date DATE,
    average_completion_time INTEGER, -- seconds

    -- Version Control
    version VARCHAR(20) DEFAULT '1.0',
    parent_template_id UUID REFERENCES complaint_templates(id),
    is_current_version BOOLEAN DEFAULT TRUE,

    -- Status and Permissions
    is_active BOOLEAN DEFAULT TRUE,
    is_published BOOLEAN DEFAULT FALSE,
    access_level VARCHAR(20) DEFAULT 'department' CHECK (access_level IN (
        'public', 'department', 'specialty', 'restricted'
    )),

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

9. ตาราง drug_allergies

ตารางสำหรับจัดการประวัติการแพ้ยาของผู้ป่วย

CREATE TABLE drug_allergies (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient Reference
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Drug Information
    drug_name VARCHAR(200) NOT NULL,
    drug_generic_name VARCHAR(200),
    drug_code VARCHAR(50), -- TMT code, WHO ATC code
    drug_category VARCHAR(100), -- "Antibiotics", "NSAIDs", "ACE Inhibitors"

    -- Allergy Classification
    allergy_type VARCHAR(30) NOT NULL CHECK (allergy_type IN (
        'true_allergy', 'intolerance', 'adverse_reaction', 'sensitivity', 'unknown'
    )),
    reaction_mechanism VARCHAR(30) CHECK (reaction_mechanism IN (
        'ige_mediated', 'non_ige_mediated', 'mixed', 'unknown'
    )),

    -- Reaction Details
    reaction_description TEXT NOT NULL,
    reaction_severity VARCHAR(20) NOT NULL CHECK (reaction_severity IN (
        'mild', 'moderate', 'severe', 'life_threatening'
    )),

    -- Clinical Manifestations
    reaction_symptoms JSONB, -- ["rash", "hives", "swelling", "anaphylaxis"]
    body_systems_affected JSONB, -- ["skin", "respiratory", "cardiovascular", "gastrointestinal"]

    -- Timing and Circumstances
    onset_date DATE,
    onset_time_after_dose VARCHAR(50), -- "immediate", "within 1 hour", "delayed"
    dose_when_occurred VARCHAR(100),
    route_of_administration VARCHAR(30) CHECK (route_of_administration IN (
        'oral', 'intravenous', 'intramuscular', 'subcutaneous', 'topical', 'inhalation'
    )),

    -- Clinical Context
    indication_for_drug TEXT,
    concurrent_medications TEXT,
    previous_exposure BOOLEAN,
    number_of_exposures INTEGER,

    -- Documentation and Verification
    reported_by UUID REFERENCES users(id),
    reported_date DATE NOT NULL,
    information_source VARCHAR(30) CHECK (information_source IN (
        'patient', 'family', 'medical_record', 'previous_hospital', 'pharmacy'
    )),
    reliability VARCHAR(20) CHECK (reliability IN (
        'definite', 'probable', 'possible', 'doubtful', 'unknown'
    )),

    -- Medical Verification
    verified_by_physician UUID REFERENCES users(id),
    verification_date DATE,
    verification_notes TEXT,

    -- Cross-Sensitivity and Contraindications
    cross_sensitivity_drugs JSONB, -- drugs to avoid due to cross-reactivity
    related_drug_classes JSONB,
    absolute_contraindications BOOLEAN DEFAULT FALSE,

    -- Emergency Information
    emergency_treatment_required BOOLEAN DEFAULT FALSE,
    recommended_emergency_treatment TEXT,
    carries_emergency_medication BOOLEAN DEFAULT FALSE, -- epinephrine auto-injector

    -- Status and Management
    allergy_status VARCHAR(20) DEFAULT 'active' CHECK (allergy_status IN (
        'active', 'inactive', 'resolved', 'questionable'
    )),
    inactivated_date DATE,
    inactivated_by UUID REFERENCES users(id),
    inactivation_reason TEXT,

    -- Alert Settings
    alert_level VARCHAR(20) DEFAULT 'high' CHECK (alert_level IN (
        'critical', 'high', 'moderate', 'low'
    )),
    display_prominently BOOLEAN DEFAULT TRUE,
    include_in_wristband BOOLEAN DEFAULT TRUE,

    -- Additional Information
    patient_awareness BOOLEAN DEFAULT TRUE,
    family_awareness BOOLEAN DEFAULT FALSE,
    documented_in_external_systems JSONB, -- systems where this allergy is recorded

    -- Quality Assurance
    last_reviewed_date DATE,
    last_reviewed_by UUID REFERENCES users(id),
    review_interval_months INTEGER DEFAULT 12,
    next_review_due DATE,

    -- Notes and Comments
    clinical_notes TEXT,
    patient_description TEXT,
    nursing_considerations TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

10. ตาราง chronic_conditions

ตารางสำหรับจัดการโรคประจำตัวของผู้ป่วย

CREATE TABLE chronic_conditions (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient Reference
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Condition Information
    condition_name VARCHAR(200) NOT NULL,
    icd_10_code VARCHAR(10),
    condition_category VARCHAR(100), -- "cardiovascular", "endocrine", "neurological"

    -- Diagnosis Information
    diagnosed_date DATE,
    diagnosed_by VARCHAR(200), -- doctor name or hospital
    diagnosis_method VARCHAR(50), -- "clinical", "laboratory", "imaging", "biopsy"

    -- Disease Characteristics
    severity VARCHAR(20) CHECK (severity IN ('mild', 'moderate', 'severe')),
    stage VARCHAR(20), -- disease stage if applicable
    functional_impact VARCHAR(30) CHECK (functional_impact IN (
        'none', 'minimal', 'moderate', 'severe', 'disabling'
    )),

    -- Current Status
    disease_status VARCHAR(20) DEFAULT 'active' CHECK (disease_status IN (
        'active', 'stable', 'improving', 'worsening', 'remission', 'resolved'
    )),
    control_status VARCHAR(20) CHECK (control_status IN (
        'well_controlled', 'partially_controlled', 'poorly_controlled', 'uncontrolled'
    )),
    last_assessment_date DATE,

    -- Monitoring and Complications
    complications JSONB, -- list of complications that have occurred
    risk_factors JSONB,
    monitoring_requirements JSONB,
    target_parameters JSONB, -- {"hba1c": "<7%", "bp": "<130/80"}

    -- Treatment Information
    current_medications JSONB,
    treatment_adherence VARCHAR(20) CHECK (treatment_adherence IN (
        'excellent', 'good', 'fair', 'poor', 'unknown'
    )),
    lifestyle_modifications JSONB,

    -- Provider Information
    primary_managing_provider VARCHAR(200),
    specialist_involved VARCHAR(200),
    last_specialist_visit DATE,
    next_appointment_due DATE,

    -- Patient Education and Self-Management
    patient_understanding VARCHAR(20) CHECK (patient_understanding IN (
        'excellent', 'good', 'fair', 'poor', 'none'
    )),
    self_monitoring_capability BOOLEAN DEFAULT FALSE,
    family_support_available BOOLEAN DEFAULT FALSE,

    -- Impact on Current Visit
    affects_current_treatment BOOLEAN DEFAULT FALSE,
    nursing_considerations TEXT,
    precautions_needed JSONB,
    drug_interactions_potential JSONB,

    -- Emergency Information
    emergency_protocols TEXT,
    warning_signs JSONB,
    emergency_medications JSONB,

    -- Quality of Life
    activity_limitations JSONB,
    work_impact VARCHAR(20) CHECK (work_impact IN (
        'none', 'minimal', 'moderate', 'severe', 'unable_to_work'
    )),
    social_impact TEXT,

    -- Documentation and Sources
    information_source VARCHAR(50) CHECK (information_source IN (
        'patient', 'family', 'medical_records', 'previous_hospital', 'specialist_report'
    )),
    documentation_quality VARCHAR(20) CHECK (documentation_quality IN (
        'excellent', 'good', 'fair', 'poor', 'incomplete'
    )),

    -- Review and Updates
    last_reviewed_date DATE,
    last_reviewed_by UUID REFERENCES users(id),
    review_frequency_months INTEGER DEFAULT 6,
    next_review_due DATE,

    -- Status Management
    is_active BOOLEAN DEFAULT TRUE,
    inactivated_date DATE,
    inactivated_reason TEXT,

    -- Notes
    clinical_notes TEXT,
    patient_reported_symptoms TEXT,
    family_history_relevant TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

---

11. ตาราง nursing_activities

ตารางสำหรับบันทึกกิจกรรมและหัตถการทางพยาบาล

CREATE TABLE nursing_activities (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Activity Information
    activity_type VARCHAR(30) NOT NULL CHECK (activity_type IN (
        'medication', 'injection', 'iv_therapy', 'wound_care', 'vital_signs', 
        'procedure', 'education', 'counseling', 'assessment', 'discharge_planning'
    )),
    activity_name VARCHAR(200) NOT NULL,
    activity_code VARCHAR(50), -- standardized nursing intervention code

    -- Execution Details
    performed_by UUID NOT NULL REFERENCES users(id),
    assisted_by UUID REFERENCES users(id),
    performed_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    duration_minutes INTEGER,
    location VARCHAR(100),

    -- Clinical Context
    indication TEXT,
    physician_order_id UUID REFERENCES medical_orders(id),
    nursing_diagnosis VARCHAR(200),
    expected_outcome TEXT,

    -- Activity Specifics
    procedure_details JSONB, -- detailed procedure information
    medications_given JSONB, -- if medication administration
    supplies_used JSONB,
    equipment_used JSONB,

    -- Patient Response and Outcomes
    patient_response TEXT,
    patient_tolerance VARCHAR(20) CHECK (patient_tolerance IN (
        'excellent', 'good', 'fair', 'poor'
    )),
    complications_occurred BOOLEAN DEFAULT FALSE,
    complications_details TEXT,
    adverse_reactions JSONB,

    -- Education and Counseling (if applicable)
    education_topic VARCHAR(200),
    education_method VARCHAR(50) CHECK (education_method IN (
        'verbal', 'written', 'demonstration', 'return_demonstration', 'multimedia'
    )),
    patient_understanding VARCHAR(20) CHECK (patient_understanding IN (
        'excellent', 'good', 'fair', 'poor', 'unable_to_assess'
    )),
    family_involved BOOLEAN DEFAULT FALSE,
    materials_provided JSONB,

    -- Quality and Safety
    safety_measures_taken JSONB,
    infection_control_measures JSONB,
    patient_identification_verified BOOLEAN DEFAULT TRUE,
    consent_obtained BOOLEAN DEFAULT TRUE,

    -- Documentation Standards
    documentation_completeness VARCHAR(20) DEFAULT 'complete' CHECK (
        documentation_completeness IN ('complete', 'partial', 'pending')
    ),
    signature_required BOOLEAN DEFAULT FALSE,
    cosignature_required BOOLEAN DEFAULT FALSE,
    cosigned_by UUID REFERENCES users(id),
    cosigned_at TIMESTAMP WITH TIME ZONE,

    -- Billing and Charges
    billable_activity BOOLEAN DEFAULT FALSE,
    charge_code VARCHAR(20),
    charge_amount DECIMAL(10,2),
    charge_description VARCHAR(200),

    -- Follow-up Requirements
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_instructions TEXT,
    next_scheduled_activity TIMESTAMP WITH TIME ZONE,
    frequency VARCHAR(50), -- "once", "daily", "BID", "PRN"

    -- Quality Metrics
    activity_status VARCHAR(20) DEFAULT 'completed' CHECK (activity_status IN (
        'planned', 'in_progress', 'completed', 'cancelled', 'deferred'
    )),
    completion_notes TEXT,
    variance_from_standard TEXT,

    -- Integration and Communication
    communicated_to_physician BOOLEAN DEFAULT FALSE,
    communication_method VARCHAR(30),
    handoff_information TEXT,

    -- Notes and Comments
    nursing_notes TEXT,
    patient_feedback TEXT,
    family_questions_concerns TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

12. ตaราง nursing_procedures

ตารางสำหรับบันทึกหัตถการทางพยาบาลที่ซับซ้อน

CREATE TABLE nursing_procedures (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    activity_id UUID REFERENCES nursing_activities(id),

    -- Procedure Information
    procedure_name VARCHAR(200) NOT NULL,
    procedure_code VARCHAR(50), -- CPT or internal procedure code
    procedure_category VARCHAR(50) CHECK (procedure_category IN (
        'invasive', 'non_invasive', 'diagnostic', 'therapeutic', 'preventive'
    )),

    -- Authorization and Orders
    physician_order_id UUID REFERENCES medical_orders(id),
    consent_obtained BOOLEAN DEFAULT FALSE,
    consent_type VARCHAR(20) CHECK (consent_type IN ('verbal', 'written', 'implied')),
    consent_witnessed_by UUID REFERENCES users(id),

    -- Procedure Team
    primary_nurse UUID NOT NULL REFERENCES users(id),
    assisting_nurses JSONB, -- array of user IDs
    supervising_nurse UUID REFERENCES users(id),
    physician_present UUID REFERENCES users(id),

    -- Pre-procedure Preparation
    pre_procedure_assessment_completed BOOLEAN DEFAULT FALSE,
    patient_preparation_completed BOOLEAN DEFAULT FALSE,
    equipment_prepared BOOLEAN DEFAULT FALSE,
    site_preparation_completed BOOLEAN DEFAULT FALSE,

    -- Procedure Execution
    start_datetime TIMESTAMP WITH TIME ZONE NOT NULL,
    end_datetime TIMESTAMP WITH TIME ZONE,
    procedure_duration INTEGER, -- minutes
    anesthesia_used VARCHAR(50), -- "local", "topical", "none"

    -- Anatomical Information
    body_site VARCHAR(100),
    anatomical_location TEXT,
    laterality VARCHAR(10) CHECK (laterality IN ('left', 'right', 'bilateral', 'midline')),

    -- Technique and Approach
    technique_used VARCHAR(100),
    approach_method VARCHAR(50) CHECK (approach_method IN (
        'percutaneous', 'open', 'minimally_invasive', 'endoscopic'
    )),
    guidance_method VARCHAR(50), -- "ultrasound", "fluoroscopy", "palpation", "landmark"

    -- Equipment and Supplies
    equipment_used JSONB,
    supplies_consumed JSONB,
    medications_used JSONB,
    lot_numbers JSONB, -- for traceability

    -- Procedure Steps
    procedure_steps JSONB, -- detailed step-by-step documentation
    variations_from_standard TEXT,
    technical_difficulties TEXT,

    -- Patient Monitoring
    vital_signs_monitoring BOOLEAN DEFAULT TRUE,
    monitoring_frequency INTEGER, -- minutes between checks
    complications_during_procedure BOOLEAN DEFAULT FALSE,

    -- Immediate Outcomes
    procedure_success VARCHAR(20) CHECK (procedure_success IN (
        'complete_success', 'partial_success', 'unsuccessful', 'aborted'
    )),
    immediate_results TEXT,
    specimens_obtained JSONB,

    -- Post-procedure Care
    post_procedure_monitoring_required BOOLEAN DEFAULT TRUE,
    monitoring_duration INTEGER, -- minutes
    discharge_criteria_met BOOLEAN DEFAULT FALSE,
    post_procedure_instructions JSONB,

    -- Complications and Adverse Events
    complications_occurred BOOLEAN DEFAULT FALSE,
    complication_details JSONB,
    adverse_events JSONB,
    interventions_for_complications JSONB,

    -- Quality and Safety
    timeout_performed BOOLEAN DEFAULT FALSE, -- universal protocol
    site_marked BOOLEAN DEFAULT FALSE,
    infection_prevention_measures JSONB,
    safety_checklist_completed BOOLEAN DEFAULT FALSE,

    -- Documentation and Reporting
    procedure_report TEXT,
    pathology_sent BOOLEAN DEFAULT FALSE,
    cultures_sent BOOLEAN DEFAULT FALSE,
    imaging_obtained BOOLEAN DEFAULT FALSE,

    -- Education and Follow-up
    patient_education_provided BOOLEAN DEFAULT FALSE,
    family_education_provided BOOLEAN DEFAULT FALSE,
    discharge_instructions_given JSONB,
    follow_up_appointments_scheduled JSONB,

    -- Billing and Coding
    billable BOOLEAN DEFAULT FALSE,
    billing_code VARCHAR(20),
    billing_modifier VARCHAR(10),
    units_billed INTEGER DEFAULT 1,

    -- Quality Metrics
    procedure_outcome_score INTEGER CHECK (procedure_outcome_score BETWEEN 1 AND 5),
    patient_satisfaction_score INTEGER CHECK (patient_satisfaction_score BETWEEN 1 AND 5),

    -- Notes
    procedural_notes TEXT,
    nursing_observations TEXT,
    patient_feedback TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

13. ตาราง pre_clinic_orders

ตารางสำหรับจัดการคำสั่งตรวจล่วงหน้าก่อนพบแพทย์

CREATE TABLE pre_clinic_orders (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Order Information
    order_type VARCHAR(30) NOT NULL CHECK (order_type IN (
        'laboratory', 'radiology', 'ecg', 'echocardiogram', 'pulmonary_function', 
        'endoscopy', 'biopsy', 'consultation'
    )),
    order_name VARCHAR(200) NOT NULL,
    order_code VARCHAR(50),
    order_set_name VARCHAR(100), -- if part of a standard order set

    -- Ordering Information
    ordered_by UUID NOT NULL REFERENCES users(id), -- usually a nurse based on protocol
    ordering_protocol VARCHAR(100), -- "chest pain protocol", "diabetes screening"
    authorized_by UUID REFERENCES users(id), -- physician authorization if required
    nursing_protocol_id UUID, -- reference to nursing protocol allowing this order

    -- Clinical Context
    indication TEXT NOT NULL,
    clinical_urgency VARCHAR(20) DEFAULT 'routine' CHECK (clinical_urgency IN (
        'stat', 'urgent', 'routine', 'scheduled'
    )),
    clinical_priority INTEGER DEFAULT 5 CHECK (clinical_priority BETWEEN 1 AND 10),

    -- Target Department and Scheduling
    target_department_id UUID NOT NULL REFERENCES departments(id),
    preferred_datetime TIMESTAMP WITH TIME ZONE,
    appointment_required BOOLEAN DEFAULT TRUE,
    fasting_required BOOLEAN DEFAULT FALSE,
    preparation_required BOOLEAN DEFAULT FALSE,
    preparation_instructions TEXT,

    -- Order Status and Tracking
    order_status VARCHAR(20) DEFAULT 'pending' CHECK (order_status IN (
        'pending', 'scheduled', 'in_progress', 'completed', 'cancelled', 'expired'
    )),
    scheduled_datetime TIMESTAMP WITH TIME ZONE,
    started_datetime TIMESTAMP WITH TIME ZONE,
    completed_datetime TIMESTAMP WITH TIME ZONE,

    -- Results and Communication
    results_available BOOLEAN DEFAULT FALSE,
    results_received_datetime TIMESTAMP WITH TIME ZONE,
    results_reviewed_by UUID REFERENCES users(id),
    critical_results BOOLEAN DEFAULT FALSE,

    -- Integration with External Systems
    external_order_id VARCHAR(100),
    lis_order_id VARCHAR(50), -- Laboratory Information System
    ris_order_id VARCHAR(50), -- Radiology Information System
    integration_status VARCHAR(20) DEFAULT 'pending' CHECK (integration_status IN (
        'pending', 'sent', 'acknowledged', 'failed'
    )),

    -- Electronic Requisition
    electronic_requisition JSONB, -- structured order data
    paper_requisition_required BOOLEAN DEFAULT FALSE,
    requisition_printed BOOLEAN DEFAULT FALSE,
    barcode_generated VARCHAR(100),

    -- Patient Communication
    patient_notified BOOLEAN DEFAULT FALSE,
    notification_method VARCHAR(30) CHECK (notification_method IN (
        'verbal', 'written', 'phone', 'sms', 'email'
    )),
    patient_instructions_given JSONB,
    patient_questions_answered BOOLEAN DEFAULT FALSE,

    -- Quality and Safety
    contraindications_checked BOOLEAN DEFAULT TRUE,
    allergies_reviewed BOOLEAN DEFAULT TRUE,
    drug_interactions_checked BOOLEAN DEFAULT TRUE,
    pregnancy_status_verified BOOLEAN DEFAULT FALSE, -- for imaging
    consent_required BOOLEAN DEFAULT FALSE,
    consent_obtained BOOLEAN DEFAULT FALSE,

    -- Special Requirements
    isolation_precautions VARCHAR(50),
    special_transport_needed BOOLEAN DEFAULT FALSE,
    escort_required BOOLEAN DEFAULT FALSE,
    equipment_needed_bedside JSONB,

    -- Follow-up Actions
    automatic_callback BOOLEAN DEFAULT FALSE,
    callback_criteria JSONB,
    follow_up_orders JSONB, -- orders to be triggered after completion
    next_appointment_trigger BOOLEAN DEFAULT FALSE,

    -- Nursing Workflow
    pre_order_checklist_completed BOOLEAN DEFAULT FALSE,
    patient_preparation_completed BOOLEAN DEFAULT FALSE,
    transport_arranged BOOLEAN DEFAULT FALSE,
    results_follow_up_completed BOOLEAN DEFAULT FALSE,

    -- Documentation
    nursing_notes TEXT,
    patient_response TEXT,
    family_concerns TEXT,

    -- Error Handling and Cancellation
    cancelled_by UUID REFERENCES users(id),
    cancellation_reason TEXT,
    cancellation_datetime TIMESTAMP WITH TIME ZONE,
    rescheduled_to UUID REFERENCES pre_clinic_orders(id),

    -- Quality Metrics
    turnaround_time INTEGER, -- minutes from order to completion
    patient_satisfaction INTEGER CHECK (patient_satisfaction BETWEEN 1 AND 5),
    process_efficiency INTEGER CHECK (process_efficiency BETWEEN 1 AND 5),

    -- Timestamps
    ordered_datetime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

14. ตาราง internal_consultations

ตารางสำหรับจัดการการ Consult ระหว่างแผนกภายในโรงพยาบาล

CREATE TABLE internal_consultations (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    original_queue_id UUID REFERENCES nursing_queues(id),

    -- Consultation Request
    requesting_department_id UUID NOT NULL REFERENCES departments(id),
    consulting_department_id UUID NOT NULL REFERENCES departments(id),
    requested_by UUID NOT NULL REFERENCES users(id), -- nurse who initiated
    authorized_by UUID REFERENCES users(id), -- physician authorization

    -- Clinical Information
    consultation_reason TEXT NOT NULL,
    clinical_question TEXT NOT NULL,
    urgency_level VARCHAR(20) DEFAULT 'routine' CHECK (urgency_level IN (
        'emergency', 'urgent', 'semi_urgent', 'routine'
    )),
    patient_condition_summary TEXT,

    -- Background Information
    relevant_history TEXT,
    current_medications JSONB,
    recent_investigations JSONB,
    working_diagnosis VARCHAR(200),

    -- Consultation Management
    consultation_type VARCHAR(20) CHECK (consultation_type IN (
        'opinion_only', 'co_management', 'transfer_care', 'procedure_request'
    )),
    return_to_original BOOLEAN DEFAULT TRUE,
    transfer_patient BOOLEAN DEFAULT FALSE,

    -- Scheduling and Timing
    requested_datetime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    preferred_datetime TIMESTAMP WITH TIME ZONE,
    maximum_wait_time INTEGER, -- minutes

    -- Status Tracking
    consultation_status VARCHAR(20) DEFAULT 'requested' CHECK (consultation_status IN (
        'requested', 'accepted', 'scheduled', 'in_progress', 'completed', 'declined', 'cancelled'
    )),
    accepted_by UUID REFERENCES users(id),
    accepted_datetime TIMESTAMP WITH TIME ZONE,
    scheduled_datetime TIMESTAMP WITH TIME ZONE,

    -- Consultation Execution
    consulting_physician_id UUID REFERENCES users(id),
    consultation_started TIMESTAMP WITH TIME ZONE,
    consultation_completed TIMESTAMP WITH TIME ZONE,
    consultation_duration INTEGER, -- minutes

    -- Consultation Findings
    consultation_findings TEXT,
    recommendations TEXT NOT NULL,
    additional_investigations_needed JSONB,
    treatment_modifications JSONB,

    -- Follow-up Requirements
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_timeframe VARCHAR(50),
    follow_up_instructions TEXT,
    return_criteria TEXT,

    -- Communication and Handoff
    verbal_report_given BOOLEAN DEFAULT FALSE,
    written_report_completed BOOLEAN DEFAULT FALSE,
    emergency_contact_made BOOLEAN DEFAULT FALSE,
    family_notification_required BOOLEAN DEFAULT FALSE,

    -- Patient Flow Management
    patient_location_during VARCHAR(50), -- "original_department", "consulting_department"
    escort_provided BOOLEAN DEFAULT FALSE,
    transport_method VARCHAR(30),
    patient_returned BOOLEAN DEFAULT FALSE,
    return_datetime TIMESTAMP WITH TIME ZONE,

    -- Quality and Outcome
    consultation_outcome VARCHAR(20) CHECK (consultation_outcome IN (
        'problem_solved', 'ongoing_management', 'transferred_care', 'no_action_needed'
    )),
    patient_condition_change VARCHAR(20) CHECK (patient_condition_change IN (
        'improved', 'unchanged', 'worsened', 'stabilized'
    )),
    consultation_helpful BOOLEAN DEFAULT TRUE,

    -- Billing and Documentation
    billable_consultation BOOLEAN DEFAULT FALSE,
    consultation_fee DECIMAL(10,2),
    procedure_performed BOOLEAN DEFAULT FALSE,
    procedure_codes JSONB,

    -- Nursing Coordination
    nursing_handoff_completed BOOLEAN DEFAULT FALSE,
    nursing_instructions JSONB,
    patient_education_needs JSONB,
    discharge_planning_impact TEXT,

    -- System Integration
    consultation_documented_in_emr BOOLEAN DEFAULT FALSE,
    orders_modified BOOLEAN DEFAULT FALSE,
    care_plan_updated BOOLEAN DEFAULT FALSE,

    -- Quality Metrics
    response_time INTEGER, -- minutes from request to acceptance
    total_consultation_time INTEGER, -- minutes from request to completion
    patient_satisfaction INTEGER CHECK (patient_satisfaction BETWEEN 1 AND 5),
    referring_satisfaction INTEGER CHECK (referring_satisfaction BETWEEN 1 AND 5),

    -- Notes and Comments
    nursing_notes TEXT,
    consultation_notes TEXT,
    patient_feedback TEXT,
    family_input TEXT,

    -- Decline/Cancellation Information
    declined_by UUID REFERENCES users(id),
    decline_reason TEXT,
    alternative_suggestions TEXT,
    cancelled_by UUID REFERENCES users(id),
    cancellation_reason TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

15. ตาราง patient_education_records

ตารางสำหรับบันทึกการให้ความรู้และคำแนะนำแก่ผู้ป่วย

CREATE TABLE patient_education_records (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Education Session Information
    educator_id UUID NOT NULL REFERENCES users(id), -- nurse providing education
    education_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    session_duration INTEGER, -- minutes
    session_location VARCHAR(100),

    -- Education Content
    education_topic VARCHAR(200) NOT NULL,
    education_category VARCHAR(50) CHECK (education_category IN (
        'disease_management', 'medication', 'procedure_preparation', 'post_procedure', 
        'lifestyle', 'prevention', 'discharge_planning', 'safety'
    )),
    learning_objectives JSONB,

    -- Teaching Methods
    teaching_methods JSONB CHECK (
        teaching_methods::jsonb <@ '["verbal", "written", "demonstration", "video", "interactive", "hands_on"]'::jsonb
    ),
    educational_materials_used JSONB,
    language_used VARCHAR(20) DEFAULT 'thai',
    interpreter_used BOOLEAN DEFAULT FALSE,

    -- Participants
    patient_participated BOOLEAN DEFAULT TRUE,
    family_members_present JSONB, -- [{"relationship": "spouse", "name": "John Doe"}]
    caregiver_involved BOOLEAN DEFAULT FALSE,
    translator_present BOOLEAN DEFAULT FALSE,

    -- Patient Assessment
    baseline_knowledge_level VARCHAR(20) CHECK (baseline_knowledge_level IN (
        'none', 'minimal', 'basic', 'good', 'excellent'
    )),
    learning_style_preference VARCHAR(20) CHECK (learning_style_preference IN (
        'visual', 'auditory', 'kinesthetic', 'mixed'
    )),
    learning_barriers JSONB, -- ["language", "hearing_impaired", "anxiety", "cognitive_impairment"]
    motivation_level VARCHAR(20) CHECK (motivation_level IN (
        'very_high', 'high', 'moderate', 'low', 'very_low'
    )),

    -- Education Delivery
    content_covered JSONB, -- detailed breakdown of topics covered
    key_points_emphasized JSONB,
    demonstrations_performed JSONB,
    return_demonstrations JSONB,

    -- Patient Understanding and Response
    understanding_level VARCHAR(20) CHECK (understanding_level IN (
        'complete', 'good', 'partial', 'minimal', 'none'
    )),
    questions_asked JSONB,
    concerns_expressed JSONB,
    misconceptions_identified JSONB,
    emotional_response VARCHAR(20) CHECK (emotional_response IN (
        'positive', 'neutral', 'anxious', 'overwhelmed', 'resistant'
    )),

    -- Knowledge Assessment
    pre_education_score INTEGER CHECK (pre_education_score BETWEEN 0 AND 100),
    post_education_score INTEGER CHECK (post_education_score BETWEEN 0 AND 100),
    improvement_score INTEGER GENERATED ALWAYS AS (post_education_score - pre_education_score) STORED,
    competency_demonstrated BOOLEAN DEFAULT FALSE,

    -- Follow-up and Reinforcement
    reinforcement_needed BOOLEAN DEFAULT FALSE,
    follow_up_education_scheduled BOOLEAN DEFAULT FALSE,
    next_education_date TIMESTAMP WITH TIME ZONE,
    home_practice_assigned JSONB,

    -- Educational Materials Provided
    written_materials_given JSONB,
    digital_resources_shared JSONB,
    contact_information_provided JSONB,
    support_group_referrals JSONB,

    -- Outcome Measures
    behavior_change_goals JSONB,
    confidence_level INTEGER CHECK (confidence_level BETWEEN 1 AND 10),
    readiness_to_learn VARCHAR(20) CHECK (readiness_to_learn IN (
        'very_ready', 'ready', 'somewhat_ready', 'not_ready'
    )),

    -- Quality Indicators
    education_effectiveness VARCHAR(20) CHECK (education_effectiveness IN (
        'highly_effective', 'effective', 'somewhat_effective', 'ineffective'
    )),
    patient_satisfaction INTEGER CHECK (patient_satisfaction BETWEEN 1 AND 5),
    family_satisfaction INTEGER CHECK (family_satisfaction BETWEEN 1 AND 5),

    -- Special Considerations
    cultural_factors JSONB,
    health_literacy_level VARCHAR(20) CHECK (health_literacy_level IN (
        'adequate', 'marginal', 'inadequate'
    )),
    accessibility_accommodations JSONB,

    -- Documentation Quality
    documentation_completeness VARCHAR(20) DEFAULT 'complete' CHECK (
        documentation_completeness IN ('complete', 'partial', 'minimal')
    ),
    teaching_plan_followed BOOLEAN DEFAULT TRUE,
    deviations_from_plan TEXT,

    -- Integration with Care Plan
    care_plan_updated BOOLEAN DEFAULT FALSE,
    discharge_plan_modified BOOLEAN DEFAULT FALSE,
    nursing_care_plan_adjusted BOOLEAN DEFAULT FALSE,

    -- Notes and Comments
    education_notes TEXT,
    patient_feedback TEXT,
    family_feedback TEXT,
    recommendations_for_future TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

16. ตาราง wound_care_records

ตารางสำหรับบันทึกการดูแลแผลและการประเมินแผล

CREATE TABLE wound_care_records (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    nursing_activity_id UUID REFERENCES nursing_activities(id),

    -- Wound Identification
    wound_id VARCHAR(50) NOT NULL, -- unique identifier for tracking same wound over time
    wound_name VARCHAR(100), -- descriptive name for the wound

    -- Wound Location and Characteristics
    anatomical_location VARCHAR(100) NOT NULL,
    body_region VARCHAR(50) CHECK (body_region IN (
        'head_neck', 'chest', 'abdomen', 'back', 'upper_extremity', 'lower_extremity', 'perineum'
    )),
    laterality VARCHAR(10) CHECK (laterality IN ('left', 'right', 'bilateral', 'midline')),
    specific_location_description TEXT,

    -- Wound Classification
    wound_type VARCHAR(30) NOT NULL CHECK (wound_type IN (
        'surgical', 'traumatic', 'pressure_ulcer', 'diabetic_ulcer', 'venous_ulcer', 
        'arterial_ulcer', 'burn', 'laceration', 'puncture', 'abrasion', 'other'
    )),
    wound_etiology VARCHAR(50), -- cause or origin of the wound

    -- Wound Assessment
    assessment_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    assessed_by UUID NOT NULL REFERENCES users(id),

    -- Wound Dimensions
    length_cm DECIMAL(5,2) CHECK (length_cm >= 0),
    width_cm DECIMAL(5,2) CHECK (width_cm >= 0),
    depth_cm DECIMAL(5,2) CHECK (depth_cm >= 0),
    wound_area_cm2 DECIMAL(8,2) GENERATED ALWAYS AS (length_cm * width_cm) STORED,
    undermining BOOLEAN DEFAULT FALSE,
    undermining_location VARCHAR(50), -- clock position (e.g., "2-4 o'clock")
    tunneling BOOLEAN DEFAULT FALSE,
    tunneling_depth_cm DECIMAL(5,2),

    -- Wound Bed Assessment
    wound_bed_percentage JSONB, -- {"granulation": 60, "slough": 30, "eschar": 10}
    granulation_tissue_quality VARCHAR(20) CHECK (granulation_tissue_quality IN (
        'healthy_red', 'pale_pink', 'dusky_red', 'absent'
    )),
    necrotic_tissue_present BOOLEAN DEFAULT FALSE,
    necrotic_tissue_type VARCHAR(20) CHECK (necrotic_tissue_type IN (
        'eschar', 'slough', 'mixed'
    )),

    -- Exudate Assessment
    exudate_amount VARCHAR(20) CHECK (exudate_amount IN ('none', 'scant', 'light', 'moderate', 'heavy')),
    exudate_color VARCHAR(20) CHECK (exudate_color IN (
        'clear', 'serous', 'serosanguineous', 'sanguineous', 'purulent', 'other'
    )),
    exudate_odor VARCHAR(20) CHECK (exudate_odor IN ('none', 'mild', 'moderate', 'strong', 'foul')),
    exudate_consistency VARCHAR(20) CHECK (exudate_consistency IN (
        'thin', 'thick', 'tenacious', 'creamy'
    )),

    -- Surrounding Skin
    periwound_skin_condition VARCHAR(30) CHECK (periwound_skin_condition IN (
        'intact', 'erythematous', 'macerated', 'excoriated', 'indurated', 'edematous'
    )),
    periwound_skin_color VARCHAR(20),
    periwound_skin_temperature VARCHAR(20) CHECK (periwound_skin_temperature IN (
        'normal', 'warm', 'cool', 'hot'
    )),
    skin_integrity_compromised BOOLEAN DEFAULT FALSE,

    -- Pain Assessment
    pain_level INTEGER CHECK (pain_level BETWEEN 0 AND 10),
    pain_during_assessment BOOLEAN DEFAULT FALSE,
    pain_during_dressing_change BOOLEAN DEFAULT FALSE,
    pain_at_rest INTEGER CHECK (pain_at_rest BETWEEN 0 AND 10),
    pain_medication_given BOOLEAN DEFAULT FALSE,

    -- Infection Signs
    signs_of_infection JSONB, -- ["erythema", "warmth", "purulent_drainage", "fever"]
    infection_suspected BOOLEAN DEFAULT FALSE,
    culture_obtained BOOLEAN DEFAULT FALSE,
    culture_type VARCHAR(30), -- "wound_swab", "tissue_biopsy"
    antibiotic_therapy BOOLEAN DEFAULT FALSE,

    -- Wound Care Performed
    cleaning_solution VARCHAR(50),
    irrigation_performed BOOLEAN DEFAULT FALSE,
    irrigation_solution VARCHAR(50),
    irrigation_pressure VARCHAR(20) CHECK (irrigation_pressure IN ('low', 'moderate', 'high')),
    debridement_performed BOOLEAN DEFAULT FALSE,
    debridement_type VARCHAR(30) CHECK (debridement_type IN (
        'sharp', 'mechanical', 'enzymatic', 'autolytic', 'biological'
    )),

    -- Dressing Information
    primary_dressing VARCHAR(100),
    secondary_dressing VARCHAR(100),
    dressing_change_frequency VARCHAR(50), -- "daily", "every 2 days", "PRN"
    next_dressing_change TIMESTAMP WITH TIME ZONE,
    special_tape_used BOOLEAN DEFAULT FALSE,
    securing_method VARCHAR(50),

    -- Treatment Modalities
    topical_treatments JSONB, -- medications or treatments applied
    negative_pressure_therapy BOOLEAN DEFAULT FALSE,
    hyperbaric_oxygen BOOLEAN DEFAULT FALSE,
    growth_factors_applied BOOLEAN DEFAULT FALSE,
    other_treatments JSONB,

    -- Healing Progress
    healing_stage VARCHAR(30) CHECK (healing_stage IN (
        'inflammatory', 'proliferative', 'maturation', 'chronic', 'deteriorating'
    )),
    healing_progress VARCHAR(20) CHECK (healing_progress IN (
        'excellent', 'good', 'slow', 'stalled', 'deteriorating'
    )),
    expected_healing_time VARCHAR(50),
    factors_affecting_healing JSONB,

    -- Patient Factors
    patient_mobility VARCHAR(20) CHECK (patient_mobility IN (
        'fully_mobile', 'limited_mobility', 'chairbound', 'bedbound'
    )),
    nutritional_status VARCHAR(20) CHECK (nutritional_status IN (
        'adequate', 'marginal', 'poor', 'malnourished'
    )),
    compliance_with_treatment VARCHAR(20) CHECK (compliance_with_treatment IN (
        'excellent', 'good', 'fair', 'poor'
    )),

    -- Education and Self-Care
    patient_education_provided BOOLEAN DEFAULT FALSE,
    family_education_provided BOOLEAN DEFAULT FALSE,
    self_care_demonstrated BOOLEAN DEFAULT FALSE,
    return_demonstration_successful BOOLEAN DEFAULT FALSE,

    -- Photography and Documentation
    photographs_taken BOOLEAN DEFAULT FALSE,
    photograph_consent BOOLEAN DEFAULT FALSE,
    wound_tracing_performed BOOLEAN DEFAULT FALSE,
    measurement_tools_used JSONB,

    -- Quality Indicators
    standard_of_care_met BOOLEAN DEFAULT TRUE,
    evidence_based_practice BOOLEAN DEFAULT TRUE,
    patient_comfort_maintained BOOLEAN DEFAULT TRUE,
    infection_prevention_measures JSONB,

    -- Follow-up and Referrals
    follow_up_required BOOLEAN DEFAULT FALSE,
    specialist_referral_needed BOOLEAN DEFAULT FALSE,
    wound_clinic_referral BOOLEAN DEFAULT FALSE,
    home_health_referral BOOLEAN DEFAULT FALSE,

    -- Notes and Comments
    assessment_notes TEXT,
    care_notes TEXT,
    patient_response TEXT,
    plan_of_care TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

17. ตาราง injection_records

ตารางสำหรับบันทึกการฉีดยาและการให้ยาต่างๆ

CREATE TABLE injection_records (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    nursing_activity_id UUID REFERENCES nursing_activities(id),
    medical_order_id UUID REFERENCES medical_orders(id),

    -- Injection Information
    injection_type VARCHAR(30) NOT NULL CHECK (injection_type IN (
        'intramuscular', 'subcutaneous', 'intradermal', 'intravenous', 'intraosseous', 'epidural', 'intrathecal'
    )),
    injection_purpose VARCHAR(30) CHECK (injection_purpose IN (
        'therapeutic', 'prophylactic', 'diagnostic', 'immunization', 'contraceptive'
    )),

    -- Administration Details
    administered_by UUID NOT NULL REFERENCES users(id),
    witnessed_by UUID REFERENCES users(id), -- for high-risk medications
    administration_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Medication Information
    medication_name VARCHAR(200) NOT NULL,
    medication_generic_name VARCHAR(200),
    medication_code VARCHAR(50),
    strength VARCHAR(50), -- "25mg/ml", "1000 units/ml"
    dosage_administered VARCHAR(50), -- actual dose given
    volume_administered DECIMAL(6,3), -- ml

    -- Batch and Safety Information
    lot_number VARCHAR(50),
    expiration_date DATE,
    manufacturer VARCHAR(100),
    ndc_number VARCHAR(20), -- National Drug Code

    -- Injection Site
    injection_site VARCHAR(100) NOT NULL,
    anatomical_location VARCHAR(50) CHECK (anatomical_location IN (
        'deltoid', 'vastus_lateralis', 'ventrogluteal', 'dorsogluteal', 'abdomen', 'thigh', 'arm'
    )),
    laterality VARCHAR(10) CHECK (laterality IN ('left', 'right', 'bilateral')),
    site_condition_pre VARCHAR(30) CHECK (site_condition_pre IN (
        'normal', 'erythema', 'swelling', 'induration', 'previous_injection_site'
    )),

    -- Equipment Used
    needle_gauge VARCHAR(10), -- "25G", "22G"
    needle_length VARCHAR(10), -- "1 inch", "5/8 inch"
    syringe_size VARCHAR(10), -- "1ml", "3ml", "5ml"
    filter_needle_used BOOLEAN DEFAULT FALSE,
    safety_needle_used BOOLEAN DEFAULT TRUE,

    -- Pre-Administration Checks
    patient_identity_verified BOOLEAN DEFAULT TRUE,
    allergy_checked BOOLEAN DEFAULT TRUE,
    medication_verified BOOLEAN DEFAULT TRUE,
    dosage_calculated BOOLEAN DEFAULT TRUE,
    expiration_date_checked BOOLEAN DEFAULT TRUE,

    -- Administration Technique
    skin_preparation VARCHAR(30) CHECK (skin_preparation IN (
        'alcohol_swab', 'betadine', 'chlorhexidine', 'none'
    )),
    injection_angle INTEGER CHECK (injection_angle IN (15, 45, 90)), -- degrees
    aspiration_performed BOOLEAN DEFAULT FALSE,
    injection_speed VARCHAR(20) CHECK (injection_speed IN ('slow', 'moderate', 'fast')),

    -- Patient Response - Immediate
    immediate_reaction VARCHAR(30) DEFAULT 'none' CHECK (immediate_reaction IN (
        'none', 'mild_discomfort', 'pain', 'vasovagal', 'allergic_reaction', 'anaphylaxis'
    )),
    pain_level_during INTEGER CHECK (pain_level_during BETWEEN 0 AND 10),
    patient_cooperation VARCHAR(20) CHECK (patient_cooperation IN (
        'excellent', 'good', 'fair', 'poor', 'required_restraint'
    )),

    -- Post-Administration Assessment
    site_condition_post VARCHAR(30) CHECK (site_condition_post IN (
        'normal', 'minimal_bleeding', 'bruising', 'swelling', 'redness'
    )),
    bleeding_controlled BOOLEAN DEFAULT TRUE,
    bandage_applied BOOLEAN DEFAULT FALSE,
    ice_applied BOOLEAN DEFAULT FALSE,

    -- Observation Period
    observation_required BOOLEAN DEFAULT FALSE,
    observation_duration INTEGER, -- minutes
    observation_completed BOOLEAN DEFAULT FALSE,
    vital_signs_monitored BOOLEAN DEFAULT FALSE,

    -- Adverse Reactions
    adverse_reaction BOOLEAN DEFAULT FALSE,
    reaction_type VARCHAR(30) CHECK (reaction_type IN (
        'local', 'systemic', 'allergic', 'anaphylactic', 'vasovagal'
    )),
    reaction_severity VARCHAR(20) CHECK (reaction_severity IN (
        'mild', 'moderate', 'severe', 'life_threatening'
    )),
    reaction_onset_time INTEGER, -- minutes after injection
    reaction_description TEXT,

    -- Intervention for Reactions
    intervention_required BOOLEAN DEFAULT FALSE,
    interventions_performed JSONB,
    emergency_response_activated BOOLEAN DEFAULT FALSE,
    physician_notified BOOLEAN DEFAULT FALSE,

    -- Immunization Specific
    vaccine_name VARCHAR(100),
    vaccine_series VARCHAR(50), -- "1st dose", "2nd dose", "booster"
    immunization_schedule VARCHAR(100),
    next_dose_due_date DATE,
    immunization_registry_reported BOOLEAN DEFAULT FALSE,

    -- Patient Education
    pre_injection_education BOOLEAN DEFAULT TRUE,
    post_injection_instructions BOOLEAN DEFAULT TRUE,
    side_effects_explained BOOLEAN DEFAULT TRUE,
    when_to_seek_help_explained BOOLEAN DEFAULT TRUE,
    written_instructions_given BOOLEAN DEFAULT FALSE,

    -- Quality and Safety
    right_patient BOOLEAN DEFAULT TRUE,
    right_medication BOOLEAN DEFAULT TRUE,
    right_dose BOOLEAN DEFAULT TRUE,
    right_route BOOLEAN DEFAULT TRUE,
    right_time BOOLEAN DEFAULT TRUE,
    right_documentation BOOLEAN DEFAULT TRUE,

    -- Waste Management
    sharps_disposed_safely BOOLEAN DEFAULT TRUE,
    medication_wasted VARCHAR(50), -- amount and reason if any
    witness_waste UUID REFERENCES users(id),

    -- Follow-up Requirements
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_timeframe VARCHAR(50),
    callback_instructions BOOLEAN DEFAULT FALSE,
    return_visit_scheduled BOOLEAN DEFAULT FALSE,

    -- Documentation and Reporting
    incident_report_filed BOOLEAN DEFAULT FALSE,
    adverse_event_reported BOOLEAN DEFAULT FALSE,
    lot_number_tracked BOOLEAN DEFAULT TRUE,

    -- Notes and Comments
    administration_notes TEXT,
    patient_comments TEXT,
    nursing_observations TEXT,
    special_circumstances TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

18. ตาราง nursing_notes

ตารางสำหรับบันทึกหมายเหตุทางพยาบาลแบบ Narrative

CREATE TABLE nursing_notes (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient and Visit References
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Note Information
    note_type VARCHAR(30) NOT NULL CHECK (note_type IN (
        'admission', 'shift', 'progress', 'discharge', 'incident', 'care_plan', 'handoff'
    )),
    note_title VARCHAR(200),

    -- Author Information
    authored_by UUID NOT NULL REFERENCES users(id),
    authored_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shift VARCHAR(10) CHECK (shift IN ('day', 'evening', 'night')),

    -- Note Content (SOAP Format)
    subjective TEXT, -- Patient's reported symptoms, concerns, complaints
    objective TEXT, -- Observable, measurable data (vital signs, assessments)
    assessment TEXT, -- Professional judgment, nursing diagnoses
    plan TEXT, -- Planned interventions, goals, follow-up

    -- Narrative Note
    narrative_note TEXT NOT NULL,

    -- Clinical Focus Areas
    focus_areas JSONB, -- ["pain_management", "mobility", "patient_education"]
    nursing_diagnoses JSONB, -- NANDA-I nursing diagnoses addressed
    interventions_performed JSONB,
    patient_responses JSONB,

    -- Patient Status
    overall_condition VARCHAR(20) CHECK (overall_condition IN (
        'stable', 'improving', 'declining', 'critical', 'unchanged'
    )),
    comfort_level VARCHAR(20) CHECK (comfort_level IN (
        'comfortable', 'mild_discomfort', 'moderate_discomfort', 'severe_discomfort'
    )),
    activity_level VARCHAR(20) CHECK (activity_level IN (
        'bed_rest', 'limited_activity', 'moderate_activity', 'full_activity'
    )),

    -- Communication
    patient_communication VARCHAR(30) CHECK (patient_communication IN (
        'clear_appropriate', 'confused', 'anxious', 'cooperative', 'uncooperative'
    )),
    family_involved BOOLEAN DEFAULT FALSE,
    family_communication TEXT,

    -- Priority Issues
    priority_concerns JSONB,
    safety_issues JSONB,
    immediate_needs JSONB,

    -- Handoff Information
    key_information_for_next_shift TEXT,
    pending_tasks TEXT,
    follow_up_required TEXT,

    -- Quality Indicators
    pain_addressed BOOLEAN DEFAULT FALSE,
    patient_education_provided BOOLEAN DEFAULT FALSE,
    safety_measures_implemented BOOLEAN DEFAULT FALSE,
    family_involvement_appropriate BOOLEAN DEFAULT FALSE,

    -- Documentation Quality
    note_completeness VARCHAR(20) DEFAULT 'complete' CHECK (note_completeness IN (
        'complete', 'partial', 'brief', 'addendum'
    )),
    late_entry BOOLEAN DEFAULT FALSE,
    amended_note BOOLEAN DEFAULT FALSE,
    original_note_id UUID REFERENCES nursing_notes(id),
    amendment_reason TEXT,

    -- Electronic Signature
    electronically_signed BOOLEAN DEFAULT FALSE,
    signature_timestamp TIMESTAMP WITH TIME ZONE,
    cosigner_required BOOLEAN DEFAULT FALSE,
    cosigned_by UUID REFERENCES users(id),
    cosign_timestamp TIMESTAMP WITH TIME ZONE,

    -- Privacy and Confidentiality
    confidential_information BOOLEAN DEFAULT FALSE,
    restricted_access BOOLEAN DEFAULT FALSE,

    -- Search and Indexing
    search_keywords TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('thai', 
            COALESCE(narrative_note, '') || ' ' ||
            COALESCE(subjective, '') || ' ' ||
            COALESCE(objective, '') || ' ' ||
            COALESCE(assessment, '') || ' ' ||
            COALESCE(plan, '')
        )
    ) STORED,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

---

PERFORMANCE OPTIMIZATION

Indexes และ Performance Optimization

-- Nursing Queues Performance Indexes
CREATE INDEX idx_nursing_queues_patient_date ON nursing_queues(patient_id, queue_date DESC);
CREATE INDEX idx_nursing_queues_department_status ON nursing_queues(department_id, queue_status, priority DESC);
CREATE INDEX idx_nursing_queues_triage ON nursing_queues(triage_level, triage_color, created_at DESC);
CREATE INDEX idx_nursing_queues_nurse_assignment ON nursing_queues(assigned_nurse_id, queue_status);
CREATE INDEX idx_nursing_queues_alerts ON nursing_queues(allergy_alert, fall_risk, infection_control_alert) 
    WHERE (allergy_alert = TRUE OR fall_risk = TRUE OR infection_control_alert = TRUE);

-- Vital Signs Performance Indexes
CREATE INDEX idx_vital_signs_patient_date ON vital_signs_records(patient_id, DATE(measurement_datetime) DESC);
CREATE INDEX idx_vital_signs_visit ON vital_signs_records(visit_id);
CREATE INDEX idx_vital_signs_device ON vital_signs_records(device_id, measurement_datetime DESC);
CREATE INDEX idx_vital_signs_alerts ON vital_signs_records(critical_alerts) WHERE critical_alerts = TRUE;
CREATE INDEX idx_vital_signs_validation ON vital_signs_records(validation_status, measured_by);

-- Nursing Assessments Performance Indexes
CREATE INDEX idx_nursing_assessments_patient_type ON nursing_assessments(patient_id, assessment_type, assessment_datetime DESC);
CREATE INDEX idx_nursing_assessments_triage ON nursing_assessments(triage_level, triage_color, assessment_datetime DESC);
CREATE INDEX idx_nursing_assessments_nurse ON nursing_assessments(assessed_by, assessment_datetime DESC);
CREATE INDEX idx_nursing_assessments_fall_risk ON nursing_assessments(fall_risk_level) WHERE fall_risk_level IN ('moderate', 'high');

-- Chief Complaints Performance Indexes
CREATE INDEX idx_chief_complaints_patient ON chief_complaints(patient_id, recorded_datetime DESC);
CREATE INDEX idx_chief_complaints_search ON chief_complaints USING gin(search_keywords);
CREATE INDEX idx_chief_complaints_department ON chief_complaints(department_id, recorded_datetime DESC);
CREATE INDEX idx_chief_complaints_template ON chief_complaints(template_used_id);

-- Drug Allergies Performance Indexes
CREATE INDEX idx_drug_allergies_patient_active ON drug_allergies(patient_id, allergy_status) WHERE allergy_status = 'active';
CREATE INDEX idx_drug_allergies_drug_name ON drug_allergies(drug_name, allergy_status) WHERE allergy_status = 'active';
CREATE INDEX idx_drug_allergies_severity ON drug_allergies(severity, alert_level) WHERE allergy_status = 'active';

-- Nursing Activities Performance Indexes
CREATE INDEX idx_nursing_activities_patient_date ON nursing_activities(patient_id, DATE(performed_datetime) DESC);
CREATE INDEX idx_nursing_activities_nurse_date ON nursing_activities(performed_by, DATE(performed_datetime) DESC);
CREATE INDEX idx_nursing_activities_type ON nursing_activities(activity_type, activity_status, performed_datetime DESC);
CREATE INDEX idx_nursing_activities_visit ON nursing_activities(visit_id);

-- Pre-clinic Orders Performance Indexes
CREATE INDEX idx_pre_clinic_orders_patient_status ON pre_clinic_orders(patient_id, order_status, ordered_datetime DESC);
CREATE INDEX idx_pre_clinic_orders_department ON pre_clinic_orders(target_department_id, order_status, ordered_datetime DESC);
CREATE INDEX idx_pre_clinic_orders_results ON pre_clinic_orders(results_available, results_received_datetime DESC);

-- Internal Consultations Performance Indexes
CREATE INDEX idx_consultations_patient_status ON internal_consultations(patient_id, consultation_status, requested_datetime DESC);
CREATE INDEX idx_consultations_requesting_dept ON internal_consultations(requesting_department_id, consultation_status, requested_datetime DESC);
CREATE INDEX idx_consultations_consulting_dept ON internal_consultations(consulting_department_id, consultation_status, accepted_datetime DESC);

-- Nursing Notes Search Indexes
CREATE INDEX idx_nursing_notes_patient_date ON nursing_notes(patient_id, authored_datetime DESC);
CREATE INDEX idx_nursing_notes_search ON nursing_notes USING gin(search_keywords);
CREATE INDEX idx_nursing_notes_author ON nursing_notes(authored_by, authored_datetime DESC);
CREATE INDEX idx_nursing_notes_type ON nursing_notes(note_type, authored_datetime DESC);

-- IoT Devices Status Indexes
CREATE INDEX idx_iot_devices_station_type ON iot_devices(nursing_station_id, device_type, is_active);
CREATE INDEX idx_iot_devices_status ON iot_devices(connection_status, last_communication DESC);
CREATE INDEX idx_iot_devices_calibration ON iot_devices(calibration_status, calibration_due_date);

---

ENUMS และ TYPES

-- Create custom types for nursing system
CREATE TYPE queue_type_enum AS ENUM (
    'screening', 'vital_signs', 'consultation', 'procedure', 'medication', 'discharge', 'admission'
);

CREATE TYPE queue_status_enum AS ENUM (
    'waiting', 'called', 'in_progress', 'completed', 'cancelled', 'no_show', 'transferred'
);

CREATE TYPE validation_status_enum AS ENUM (
    'pending', 'validated', 'flagged', 'rejected'
);

CREATE TYPE allergy_type_enum AS ENUM (
    'true_allergy', 'intolerance', 'adverse_reaction', 'sensitivity', 'unknown'
);

CREATE TYPE severity_enum AS ENUM (
    'mild', 'moderate', 'severe', 'life_threatening'
);

CREATE TYPE allergy_status_enum AS ENUM (
    'active', 'inactive', 'resolved', 'questionable'
);

CREATE TYPE activity_type_enum AS ENUM (
    'medication', 'injection', 'iv_therapy', 'wound_care', 'vital_signs', 
    'procedure', 'education', 'counseling', 'assessment', 'discharge_planning'
);

CREATE TYPE activity_status_enum AS ENUM (
    'planned', 'in_progress', 'completed', 'cancelled', 'deferred'
);

CREATE TYPE order_type_enum AS ENUM (
    'laboratory', 'radiology', 'ecg', 'echocardiogram', 'pulmonary_function', 
    'endoscopy', 'biopsy', 'consultation'
);

CREATE TYPE order_status_enum AS ENUM (
    'pending', 'scheduled', 'in_progress', 'completed', 'cancelled', 'expired'
);

CREATE TYPE urgency_enum AS ENUM (
    'emergency', 'urgent', 'semi_urgent', 'routine'
);

CREATE TYPE consultation_status_enum AS ENUM (
    'requested', 'accepted', 'scheduled', 'in_progress', 'completed', 'declined', 'cancelled'
);

CREATE TYPE assessment_type_enum AS ENUM (
    'initial_screening', 'triage', 'focused', 'comprehensive', 'discharge', 'fall_risk'
);

---

BUSINESS RULES และ CONSTRAINTS

-- Business Rules for Nursing Queues
ALTER TABLE nursing_queues 
ADD CONSTRAINT chk_priority_triage_consistency 
CHECK (
    (triage_level = 1 AND priority >= 9) OR
    (triage_level = 2 AND priority >= 7) OR
    (triage_level = 3 AND priority >= 5) OR
    (triage_level = 4 AND priority >= 3) OR
    (triage_level = 5 AND priority >= 1)
);

-- Time Logic Validation
ALTER TABLE nursing_queues
ADD CONSTRAINT chk_queue_time_logic
CHECK (
    (called_at IS NULL OR called_at >= created_at) AND
    (started_at IS NULL OR started_at >= COALESCE(called_at, created_at)) AND
    (completed_at IS NULL OR completed_at >= COALESCE(started_at, called_at, created_at))
);

-- Vital Signs Reasonable Ranges
ALTER TABLE vital_signs_records
ADD CONSTRAINT chk_vital_signs_reasonable
CHECK (
    (systolic_bp IS NULL OR systolic_bp BETWEEN 50 AND 300) AND
    (diastolic_bp IS NULL OR diastolic_bp BETWEEN 30 AND 200) AND
    (pulse_rate IS NULL OR pulse_rate BETWEEN 30 AND 200) AND
    (respiratory_rate IS NULL OR respiratory_rate BETWEEN 8 AND 60) AND
    (body_temperature IS NULL OR body_temperature BETWEEN 30.0 AND 45.0) AND
    (oxygen_saturation IS NULL OR oxygen_saturation BETWEEN 70 AND 100) AND
    (pain_score IS NULL OR pain_score BETWEEN 0 AND 10)
);

-- BMI Calculation Logic
ALTER TABLE vital_signs_records
ADD CONSTRAINT chk_bmi_calculation
CHECK (
    (height IS NULL OR weight IS NULL) OR
    (height > 0 AND weight > 0)
);

-- Drug Allergy Alert Level Logic
ALTER TABLE drug_allergies
ADD CONSTRAINT chk_allergy_alert_logic
CHECK (
    (severity = 'life_threatening' AND alert_level IN ('critical', 'high')) OR
    (severity = 'severe' AND alert_level IN ('critical', 'high', 'moderate')) OR
    (severity IN ('moderate', 'mild'))
);

-- Nursing Assessment GCS Score Logic
ALTER TABLE nursing_assessments
ADD CONSTRAINT chk_gcs_components
CHECK (
    (gcs_eye_opening IS NULL OR gcs_eye_opening BETWEEN 1 AND 4) AND
    (gcs_verbal_response IS NULL OR gcs_verbal_response BETWEEN 1 AND 5) AND
    (gcs_motor_response IS NULL OR gcs_motor_response BETWEEN 1 AND 6) AND
    (gcs_total IS NULL OR gcs_total BETWEEN 3 AND 15)
);

-- Pre-clinic Orders Completion Logic
ALTER TABLE pre_clinic_orders
ADD CONSTRAINT chk_order_completion_logic
CHECK (
    (order_status = 'completed' AND completed_datetime IS NOT NULL) OR
    (order_status != 'completed')
);

-- Consultation Request Logic
ALTER TABLE internal_consultations
ADD CONSTRAINT chk_consultation_departments
CHECK (requesting_department_id != consulting_department_id);

-- Wound Care Measurement Logic
ALTER TABLE wound_care_records
ADD CONSTRAINT chk_wound_measurements
CHECK (
    (length_cm IS NULL OR length_cm >= 0) AND
    (width_cm IS NULL OR width_cm >= 0) AND
    (depth_cm IS NULL OR depth_cm >= 0) AND
    (undermining IS FALSE OR undermining_location IS NOT NULL) AND
    (tunneling IS FALSE OR tunneling_depth_cm IS NOT NULL)
);

---

TRIGGERS และ FUNCTIONS

-- Auto-generate queue numbers
CREATE OR REPLACE FUNCTION generate_nursing_queue_number()
RETURNS TRIGGER AS $$
DECLARE
    new_queue_number VARCHAR(20);
    queue_date_str VARCHAR(8);
    sequence_num INTEGER;
BEGIN
    -- Generate queue number: DEPT-YYYYMMDD-NNN
    queue_date_str := TO_CHAR(NEW.queue_date, 'YYYYMMDD');

    -- Get next sequence number for this department and date
    SELECT COALESCE(MAX(CAST(SUBSTRING(queue_number FROM LENGTH(queue_number)-2) AS INTEGER)), 0) + 1
    INTO sequence_num
    FROM nursing_queues 
    WHERE department_id = NEW.department_id 
    AND queue_date = NEW.queue_date;

    -- Generate queue number
    SELECT CONCAT(
        (SELECT code FROM departments WHERE id = NEW.department_id),
        '-',
        queue_date_str,
        '-',
        LPAD(sequence_num::TEXT, 3, '0')
    ) INTO new_queue_number;

    NEW.queue_number := new_queue_number;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_generate_nursing_queue_number
    BEFORE INSERT ON nursing_queues
    FOR EACH ROW
    EXECUTE FUNCTION generate_nursing_queue_number();

-- Auto-calculate MAP (Mean Arterial Pressure)
CREATE OR REPLACE FUNCTION calculate_mean_arterial_pressure()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.systolic_bp IS NOT NULL AND NEW.diastolic_bp IS NOT NULL THEN
        NEW.mean_arterial_pressure := ROUND((NEW.diastolic_bp + (NEW.systolic_bp - NEW.diastolic_bp) / 3)::NUMERIC);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_calculate_map
    BEFORE INSERT OR UPDATE ON vital_signs_records
    FOR EACH ROW
    EXECUTE FUNCTION calculate_mean_arterial_pressure();

-- Update queue status timestamps
CREATE OR REPLACE FUNCTION update_nursing_queue_timestamps()
RETURNS TRIGGER AS $$
BEGIN
    -- Update timestamps based on status changes
    IF OLD.queue_status != NEW.queue_status THEN
        CASE NEW.queue_status
            WHEN 'called' THEN
                NEW.called_at := CURRENT_TIMESTAMP;
            WHEN 'in_progress' THEN
                NEW.started_at := CURRENT_TIMESTAMP;
            WHEN 'completed' THEN
                NEW.completed_at := CURRENT_TIMESTAMP;
        END CASE;
    END IF;

    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_nursing_queue_timestamps
    BEFORE UPDATE ON nursing_queues
    FOR EACH ROW
    EXECUTE FUNCTION update_nursing_queue_timestamps();

-- Validate vital signs and generate alerts
CREATE OR REPLACE FUNCTION validate_vital_signs_and_alert()
RETURNS TRIGGER AS $$
DECLARE
    alert_messages JSONB := '[]'::JSONB;
    patient_age INTEGER;
BEGIN
    -- Get patient age
    SELECT age INTO patient_age FROM patients WHERE id = NEW.patient_id;

    -- Check for critical vital signs and generate alerts
    IF NEW.systolic_bp > 180 OR NEW.diastolic_bp > 110 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'hypertensive_crisis', 'level', 'critical', 'message', 'Blood pressure critically high')::jsonb;
        NEW.critical_alerts := TRUE;
    END IF;

    IF NEW.systolic_bp < 90 OR NEW.diastolic_bp < 60 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'hypotension', 'level', 'high', 'message', 'Blood pressure dangerously low')::jsonb;
        NEW.critical_alerts := TRUE;
    END IF;

    IF NEW.pulse_rate > 120 OR NEW.pulse_rate < 50 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'abnormal_pulse', 'level', 'moderate', 'message', 'Pulse rate outside normal range')::jsonb;
    END IF;

    IF NEW.body_temperature > 38.5 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'fever', 'level', 'moderate', 'message', 'High fever detected')::jsonb;
    END IF;

    IF NEW.oxygen_saturation < 90 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'hypoxemia', 'level', 'critical', 'message', 'Oxygen saturation critically low')::jsonb;
        NEW.critical_alerts := TRUE;
    END IF;

    IF NEW.pain_score >= 8 THEN
        alert_messages := alert_messages || jsonb_build_object('type', 'severe_pain', 'level', 'high', 'message', 'Severe pain reported')::jsonb;
    END IF;

    NEW.alerts_generated := alert_messages;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_vital_signs
    BEFORE INSERT OR UPDATE ON vital_signs_records
    FOR EACH ROW
    EXECUTE FUNCTION validate_vital_signs_and_alert();

---

INTEGRATION WITH MASTER SCHEMA

Foundation Tables ที่ใช้จาก Master Schema

1. patients - ข้อมูลผู้ป่วยหลัก (Master Patient Index)
2. users - บุคลากรทางการแพทย์และพยาบาล
3. medical_visits - การมารับบริการ (Clinical Encounters)
4. departments - โครงสร้างแผนกโรงพยาบาล
5. medical_orders - คำสั่งทางการแพทย์จากระบบ CPOE
6. audit_logs - บันทึกการตรวจสอบสำหรับ compliance
7. digital_signatures - ลายเซ็นอิเล็กทรอนิกส์

Nursing-Specific Tables

1. nursing_queues - จัดการคิวผู้ป่วยเฉพาะสำหรับงานพยาบาล
2. nursing_stations - สถานีพยาบาลและการกระจายงาน
3. vital_signs_records - บันทึก Vital Signs พร้อม IoT Integration
4. iot_devices - จัดการอุปกรณ์ IoT สำหรับวัด Vital Signs
5. nursing_assessments - การประเมินผู้ป่วยทางพยาบาลและ Triage
6. chief_complaints - บันทึกอาการสำคัญของผู้ป่วย
7. drug_allergies - จัดการประวัติการแพ้ยา
8. chronic_conditions - โรคประจำตัวของผู้ป่วย
9. nursing_activities - กิจกรรมและหัตถการทางพยาบาล
10. nursing_procedures - หัตถการทางพยาบาลที่ซับซ้อน
11. pre_clinic_orders - คำสั่งตรวจล่วงหน้าก่อนพบแพทย์
12. internal_consultations - การ Consult ระหว่างแผนก
13. patient_education_records - การให้ความรู้แก่ผู้ป่วย
14. wound_care_records - การดูแลแผลและการประเมินแผล
15. injection_records - การฉีดยาและการให้ยา
16. nursing_notes - หมายเหตุทางพยาบาลแบบ Narrative

---

สรุป Benefits ของการบูรณาการ

✅ ครอบคลุมความต้องการทั้งหมด: รองรับทุกฟีเจอร์ตาม SRS
✅ มาตรฐานการพยาบาล: รองรับมาตรฐานการดูแลพยาบาลสากล
✅ Real-time Integration: การทำงานร่วมกับระบบอื่นแบบ Real-time
✅ IoT Support: รองรับการเชื่อมต่ออุปกรณ์วัด Vital Signs
✅ ปฏิบัติตาม Compliance: Built-in audit และ retention policies
✅ Performance Optimization: Strategic indexing สำหรับการใช้งานจริง
✅ Security: ความปลอดภัยและการควบคุมการเข้าถึง

การบูรณาการนี้ทำให้ระบบงานพยาบาลผู้ป่วยนอกสามารถทำงานร่วมกับโมดูลอื่นๆ ใน MediTech HIS ได้อย่างลื่นไหลและมีประสิทธิภาพ ตอบสนองความต้องการของพยาบาลในการดูแลผู้ป่วยอย่างครอบคลุมและปลอดภัย

---