Database Schema - ระบบงานรังสีวิทยา (Radiology System with CPOE AI Assist)

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

เอกสารนี้รวบรวมโครงสร้างตารางฐานข้อมูลสำหรับระบบรังสีวิทยา (Radiology System) ที่บูรณาการกับ CPOE AI Assist และรองรับมาตรฐาน DICOM, RIS/PACS integration ครบถ้วนตาม TOR ที่กำหนด

ตาราง radiology_orders
ตาราง radiology_exams
ตาราง patient_screening
ตาราง contraindication_checks
ตาราง ai_recommendations
ตาราง dicom_studies
ตาราง dicom_series
ตาราง dicom_instances
ตาราง radiology_reports
ตาราง critical_findings
ตาราง radiation_exposure
ตาราง exam_protocols
ตาราง preparation_instructions
ตาราง imaging_equipment

INTEGRATION WITH MASTER SCHEMA

ระบบรังสีวิทยาใช้ตารางหลักจาก Master Database Schema:

users - จัดการ radiologists, technologists, referring physicians
patients - ข้อมูลผู้ป่วย (Master Patient Index)
medical_visits - การมารับบริการของผู้ป่วย
medical_orders - คำสั่งทางการแพทย์จากระบบ CPOE
departments - แผนกรังสี และแผนกที่ส่งตรวจ
audit_logs - Audit trail สำหรับ compliance
digital_signatures - ลายเซ็นอิเล็กทรอนิกส์สำหรับรายงาน

1. ตาราง `radiology_orders`

ตารางสำหรับจัดการคำสั่งตรวจรังสีทั้งหมด รองรับการบูรณาการกับ CPOE AI Assist

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

    -- Order Integration
    medical_order_id UUID NOT NULL REFERENCES medical_orders(id) ON DELETE CASCADE,
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Order Information
    radiology_order_number VARCHAR(20) UNIQUE NOT NULL,
    accession_number VARCHAR(30) UNIQUE, -- Generated for RIS integration

    -- Exam Details
    exam_code VARCHAR(20) NOT NULL REFERENCES radiology_exams(code),
    exam_name VARCHAR(200) NOT NULL,
    exam_category VARCHAR(50) NOT NULL CHECK (exam_category IN (
        'X-ray', 'CT', 'MRI', 'Ultrasound', 'Nuclear_Medicine', 'Mammography', 
        'Fluoroscopy', 'Angiography', 'Interventional'
    )),

    -- Clinical Information
    clinical_indication TEXT NOT NULL,
    clinical_history TEXT,
    clinical_question TEXT,
    relevant_symptoms TEXT,

    -- Provider Information
    ordering_physician_id UUID NOT NULL REFERENCES users(id),
    ordering_department_id UUID REFERENCES departments(id),
    referring_diagnosis VARCHAR(200),

    -- Exam Specifications
    body_part VARCHAR(100) NOT NULL,
    laterality VARCHAR(10) CHECK (laterality IN ('Left', 'Right', 'Bilateral', 'NA')),
    anatomical_region VARCHAR(100),

    -- Contrast Information
    contrast_required BOOLEAN DEFAULT FALSE,
    contrast_type VARCHAR(50), -- Iodinated, Gadolinium, Barium, etc.
    contrast_volume DECIMAL(6,2), -- ml
    contrast_concentration VARCHAR(20),

    -- Priority and Timing
    priority VARCHAR(20) DEFAULT 'Routine' CHECK (priority IN ('Stat', 'Urgent', 'Routine', 'Pre-op')),
    requested_date DATE,
    requested_time TIME,
    preferred_time_slot VARCHAR(20), -- Morning, Afternoon, Evening

    -- Patient Preparation
    preparation_required BOOLEAN DEFAULT FALSE,
    fasting_required BOOLEAN DEFAULT FALSE,
    fasting_duration INTEGER, -- hours
    special_instructions TEXT,

    -- Scheduling Information
    scheduled_date_time TIMESTAMP WITH TIME ZONE,
    estimated_duration INTEGER DEFAULT 30, -- minutes
    room_assignment VARCHAR(50),
    equipment_id UUID REFERENCES imaging_equipment(id),

    -- Status Management
    order_status VARCHAR(30) DEFAULT 'Ordered' CHECK (order_status IN (
        'Ordered', 'Scheduled', 'Arrived', 'In_Progress', 'Completed', 
        'Cancelled', 'No_Show', 'Pending_Authorization'
    )),

    -- Authorization and Insurance
    requires_pre_authorization BOOLEAN DEFAULT FALSE,
    pre_authorization_number VARCHAR(50),
    pre_authorization_status VARCHAR(20),
    insurance_coverage_confirmed BOOLEAN DEFAULT FALSE,
    estimated_cost DECIMAL(10,2),

    -- Workflow Timestamps
    ordered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    scheduled_at TIMESTAMP WITH TIME ZONE,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    reported_at TIMESTAMP WITH TIME ZONE,

    -- Technologist Assignment
    assigned_technologist_id UUID REFERENCES users(id),
    performing_technologist_id UUID REFERENCES users(id),

    -- Quality and Safety
    radiation_safety_checked BOOLEAN DEFAULT FALSE,
    pregnancy_screening_done BOOLEAN DEFAULT FALSE,
    allergy_screening_done BOOLEAN DEFAULT FALSE,

    -- External Integration
    sent_to_ris BOOLEAN DEFAULT FALSE,
    ris_order_id VARCHAR(50),
    sent_to_ris_at TIMESTAMP WITH TIME ZONE,
    ris_status VARCHAR(30),

    -- Modification History
    modified_from_order_id UUID REFERENCES radiology_orders(id),
    modification_reason TEXT,

    -- Cancellation Information
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by_user_id UUID REFERENCES users(id),
    cancellation_reason TEXT,
    cancellation_category VARCHAR(50), -- Patient_Request, Medical_Reason, Equipment_Failure

    -- Communication
    patient_notified BOOLEAN DEFAULT FALSE,
    physician_notified BOOLEAN DEFAULT FALSE,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

    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)
);

Indexes สำหรับ Performance:

-- Performance indexes
CREATE INDEX idx_radiology_orders_patient_date ON radiology_orders(patient_id, ordered_at DESC);
CREATE INDEX idx_radiology_orders_physician ON radiology_orders(ordering_physician_id, ordered_at DESC);
CREATE INDEX idx_radiology_orders_status ON radiology_orders(order_status, priority, ordered_at DESC);
CREATE INDEX idx_radiology_orders_exam_type ON radiology_orders(exam_category, exam_code, ordered_at DESC);
CREATE INDEX idx_radiology_orders_scheduled ON radiology_orders(scheduled_date_time) WHERE scheduled_date_time IS NOT NULL;
CREATE INDEX idx_radiology_orders_accession ON radiology_orders(accession_number) WHERE accession_number IS NOT NULL;
CREATE INDEX idx_radiology_orders_equipment ON radiology_orders(equipment_id, scheduled_date_time);

-- Search optimization
CREATE INDEX idx_radiology_orders_text_search ON radiology_orders USING gin(
    to_tsvector('thai', 
        clinical_indication || ' ' || COALESCE(clinical_history, '') || ' ' ||
        COALESCE(body_part, '') || ' ' || COALESCE(referring_diagnosis, '')
    )
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`id`	UUID	PRIMARY KEY	รหัสเฉพาะของคำสั่งตรวจรังสี
`medical_order_id`	UUID	NOT NULL, FK	รหัสคำสั่งทางการแพทย์จากระบบ CPOE
`visit_id`	UUID	NOT NULL, FK	รหัสการมารับบริการของผู้ป่วย
`patient_id`	UUID	NOT NULL, FK	รหัสผู้ป่วย
`radiology_order_number`	VARCHAR(20)	UNIQUE, NOT NULL	หมายเลขคำสั่งตรวจรังสี
`accession_number`	VARCHAR(30)	UNIQUE	หมายเลขอ้างอิงสำหรับ RIS Integration
`exam_code`	VARCHAR(20)	NOT NULL, FK	รหัสการตรวจ
`exam_name`	VARCHAR(200)	NOT NULL	ชื่อการตรวจ
`exam_category`	VARCHAR(50)	NOT NULL, CHECK	ประเภทการตรวจ (X-ray, CT, MRI, Ultrasound, Nuclear_Medicine, Mammography, Fluoroscopy, Angiography, Interventional)
`clinical_indication`	TEXT	NOT NULL	ข้อบ่งชี้ทางคลินิก
`clinical_history`	TEXT		ประวัติคลินิก
`clinical_question`	TEXT		คำถามทางคลินิก
`relevant_symptoms`	TEXT		อาการที่เกี่ยวข้อง
`ordering_physician_id`	UUID	NOT NULL, FK	แพทย์ที่สั่งตรวจ
`ordering_department_id`	UUID	FK	แผนกที่สั่งตรวจ
`referring_diagnosis`	VARCHAR(200)		การวินิจฉัยเบื้องต้น
`body_part`	VARCHAR(100)	NOT NULL	อวัยวะที่ตรวจ
`laterality`	VARCHAR(10)	CHECK	ข้างของการตรวจ (Left, Right, Bilateral, NA)
`anatomical_region`	VARCHAR(100)		บริเวณทางกายวิภาค
`contrast_required`	BOOLEAN		ต้องการสารทึบรังสีหรือไม่
`contrast_type`	VARCHAR(50)		ประเภทสารทึบรังสี (Iodinated, Gadolinium, Barium)
`contrast_volume`	DECIMAL(6,2)		ปริมาณสารทึบรังสี (มล.)
`contrast_concentration`	VARCHAR(20)		ความเข้มข้นสารทึบรังสี
`priority`	VARCHAR(20)	CHECK	ลำดับความสำคัญ (Stat, Urgent, Routine, Pre-op)
`requested_date`	DATE		วันที่ขอตรวจ
`requested_time`	TIME		เวลาที่ขอตรวจ
`preferred_time_slot`	VARCHAR(20)		ช่วงเวลาที่ต้องการ (Morning, Afternoon, Evening)
`preparation_required`	BOOLEAN		ต้องการการเตรียมตัวหรือไม่
`fasting_required`	BOOLEAN		ต้องอดอาหารหรือไม่
`fasting_duration`	INTEGER		ระยะเวลาอดอาหาร (ชั่วโมง)
`special_instructions`	TEXT		คำแนะนำพิเศษ
`scheduled_date_time`	TIMESTAMP		วันเวลาที่นัดหมาย
`estimated_duration`	INTEGER		ระยะเวลาประมาณการ (นาที)
`room_assignment`	VARCHAR(50)		ห้องที่จัดให้
`equipment_id`	UUID	FK	เครื่องมือที่ใช้ตรวจ
`order_status`	VARCHAR(30)	CHECK	สถานะคำสั่ง (Ordered, Scheduled, Arrived, In_Progress, Completed, Cancelled, No_Show, Pending_Authorization)
`requires_pre_authorization`	BOOLEAN		ต้องการอนุมัติล่วงหน้าหรือไม่
`pre_authorization_number`	VARCHAR(50)		หมายเลขอนุมัติล่วงหน้า
`pre_authorization_status`	VARCHAR(20)		สถานะการอนุมัติล่วงหน้า
`insurance_coverage_confirmed`	BOOLEAN		ยืนยันสิทธิการรักษาแล้วหรือไม่
`estimated_cost`	DECIMAL(10,2)		ค่าใช้จ่ายประมาณการ
`ordered_at`	TIMESTAMP	DEFAULT NOW()	เวลาที่สั่งตรวจ
`scheduled_at`	TIMESTAMP		เวลาที่นัดหมาย
`started_at`	TIMESTAMP		เวลาที่เริ่มตรวจ
`completed_at`	TIMESTAMP		เวลาที่ตรวจเสร็จ
`reported_at`	TIMESTAMP		เวลาที่รายงานผล
`assigned_technologist_id`	UUID	FK	นักรังสีการแพทย์ที่ได้รับมอบหมาย
`performing_technologist_id`	UUID	FK	นักรังสีการแพทย์ที่ทำการตรวจ
`radiation_safety_checked`	BOOLEAN		ตรวจสอบความปลอดภัยรังสีแล้วหรือไม่
`pregnancy_screening_done`	BOOLEAN		ตรวจสอบการตั้งครรภ์แล้วหรือไม่
`allergy_screening_done`	BOOLEAN		ตรวจสอบการแพ้แล้วหรือไม่
`sent_to_ris`	BOOLEAN		ส่งข้อมูลไป RIS แล้วหรือไม่
`ris_order_id`	VARCHAR(50)		รหัสคำสั่งใน RIS
`sent_to_ris_at`	TIMESTAMP		เวลาที่ส่งไป RIS
`ris_status`	VARCHAR(30)		สถานะใน RIS
`modified_from_order_id`	UUID	FK	คำสั่งเดิมที่ถูกแก้ไข
`modification_reason`	TEXT		เหตุผลการแก้ไข
`cancelled_at`	TIMESTAMP		เวลาที่ยกเลิก
`cancelled_by_user_id`	UUID	FK	ผู้ที่ยกเลิก
`cancellation_reason`	TEXT		เหตุผลการยกเลิก
`cancellation_category`	VARCHAR(50)		หมวดหมู่การยกเลิก (Patient_Request, Medical_Reason, Equipment_Failure)
`patient_notified`	BOOLEAN		แจ้งผู้ป่วยแล้วหรือไม่
`physician_notified`	BOOLEAN		แจ้งแพทย์แล้วหรือไม่
`is_active`	BOOLEAN		สถานะการใช้งาน
`is_deleted`	BOOLEAN		ถูกลบหรือไม่
`created_at`	TIMESTAMP	DEFAULT NOW()	วันเวลาที่สร้างข้อมูล
`updated_at`	TIMESTAMP	DEFAULT NOW()	วันเวลาที่แก้ไขข้อมูลล่าสุด
`created_by`	UUID	FK	ผู้สร้างข้อมูล
`updated_by`	UUID	FK	ผู้แก้ไขข้อมูลล่าสุด

2. ตาราง `radiology_exams`

ตารางข้อมูลมาตรฐานการตรวจรังสีทั้งหมดที่รองรับในระบบ

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

    -- Exam Identification
    code VARCHAR(20) UNIQUE NOT NULL, -- e.g., CT_CHEST_PLAIN, MRI_BRAIN_GAD
    name_thai VARCHAR(200) NOT NULL,
    name_english VARCHAR(200) NOT NULL,
    short_name VARCHAR(50),

    -- Exam Classification
    category VARCHAR(50) NOT NULL CHECK (category IN (
        'X-ray', 'CT', 'MRI', 'Ultrasound', 'Nuclear_Medicine', 'Mammography',
        'Fluoroscopy', 'Angiography', 'Interventional', 'Bone_Densitometry'
    )),
    subcategory VARCHAR(100),

    -- Exam Details
    body_part VARCHAR(100) NOT NULL,
    anatomical_region VARCHAR(100),
    exam_description TEXT,
    clinical_applications TEXT,

    -- Technical Specifications
    modality VARCHAR(20) NOT NULL, -- CT, MR, XR, US, NM, MG
    procedure_type VARCHAR(50), -- Plain, Contrast, Both
    typical_duration INTEGER DEFAULT 30, -- minutes

    -- Contrast Information
    contrast_types JSONB, -- Available contrast types for this exam
    default_contrast_volume DECIMAL(6,2),
    contrast_required BOOLEAN DEFAULT FALSE,
    contrast_optional BOOLEAN DEFAULT FALSE,

    -- Patient Preparation
    preparation_template_id UUID, -- Link to preparation instructions
    fasting_required BOOLEAN DEFAULT FALSE,
    fasting_hours INTEGER DEFAULT 0,
    special_preparation TEXT,

    -- Safety and Contraindications
    contraindications JSONB, -- List of contraindications
    precautions JSONB, -- Special precautions
    pregnancy_category VARCHAR(10), -- A, B, C, D, X for pregnancy risk

    -- Radiation Information (for ionizing radiation exams)
    uses_ionizing_radiation BOOLEAN DEFAULT FALSE,
    typical_dose_range VARCHAR(50), -- e.g., "5-15 mGy"
    dose_length_product_range VARCHAR(50), -- For CT exams

    -- Equipment Requirements
    required_equipment_type VARCHAR(100),
    minimum_equipment_specs JSONB,

    -- Scheduling Information
    default_appointment_duration INTEGER DEFAULT 30, -- minutes
    turnaround_time INTEGER DEFAULT 60, -- minutes for reporting
    requires_scheduling BOOLEAN DEFAULT TRUE,

    -- Authorization and Insurance
    requires_pre_authorization BOOLEAN DEFAULT FALSE,
    cpt_code VARCHAR(10), -- CPT procedure code
    icd10_procedure_code VARCHAR(20),

    -- Cost Information
    base_cost DECIMAL(10,2),
    contrast_additional_cost DECIMAL(10,2),

    -- Quality and Compliance
    accreditation_required BOOLEAN DEFAULT FALSE,
    quality_metrics JSONB, -- Quality indicators for this exam

    -- Reporting
    typical_report_template TEXT,
    structured_reporting_available BOOLEAN DEFAULT FALSE,

    -- Status and Metadata
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    effective_date DATE DEFAULT CURRENT_DATE,
    discontinuation_date DATE,

    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)
);

Sample Data:

-- Sample radiology exams
INSERT INTO radiology_exams (code, name_thai, name_english, category, modality, body_part, contrast_required) VALUES
('CT_CHEST_PLAIN', 'ซีที ปอด ไม่ฉีดสี', 'CT Chest Plain', 'CT', 'CT', 'Chest', FALSE),
('CT_CHEST_CONTRAST', 'ซีที ปอด ฉีดสี', 'CT Chest with Contrast', 'CT', 'CT', 'Chest', TRUE),
('MRI_BRAIN_GAD', 'เอ็มอาร์ไอ สมอง ฉีดสี', 'MRI Brain with Gadolinium', 'MRI', 'MR', 'Brain', TRUE),
('XRAY_CHEST_PA', 'เอกซเรย์ ปอด ด้านหน้า', 'Chest X-ray PA view', 'X-ray', 'XR', 'Chest', FALSE),
('US_ABDOMEN', 'อัลตราซาวด์ ช่องท้อง', 'Abdominal Ultrasound', 'Ultrasound', 'US', 'Abdomen', FALSE);

3. ตาราง `patient_screening`

ตารางสำหรับบันทึกการซักประวัติและคัดกรองผู้ป่วยก่อนการตรวจ

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

    -- Patient and Order Reference
    patient_id UUID NOT NULL REFERENCES patients(id),
    radiology_order_id UUID NOT NULL REFERENCES radiology_orders(id) ON DELETE CASCADE,

    -- Screening Information
    screening_date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    screened_by_user_id UUID NOT NULL REFERENCES users(id),
    screening_type VARCHAR(30) DEFAULT 'Pre_Exam' CHECK (screening_type IN (
        'Pre_Order', 'Pre_Exam', 'Pre_Contrast', 'Post_Exam'
    )),

    -- Basic Demographics Check
    patient_weight DECIMAL(5,2), -- kg
    patient_height DECIMAL(5,2), -- cm
    bmi DECIMAL(4,1) GENERATED ALWAYS AS (
        CASE WHEN patient_height > 0 THEN patient_weight / (patient_height/100)^2 ELSE NULL END
    ) STORED,

    -- Pregnancy Screening (for women of childbearing age)
    pregnancy_screening_required BOOLEAN DEFAULT FALSE,
    pregnancy_status VARCHAR(20) CHECK (pregnancy_status IN (
        'Not_Pregnant', 'Pregnant', 'Possibly_Pregnant', 'Unknown', 'Not_Applicable'
    )),
    pregnancy_weeks INTEGER,
    lmp_date DATE, -- Last Menstrual Period
    pregnancy_test_done BOOLEAN DEFAULT FALSE,
    pregnancy_test_result VARCHAR(20),
    pregnancy_screening_method VARCHAR(50), -- Questionnaire, Lab_Test, Clinical_Assessment

    -- Allergy and Adverse Reaction History
    allergy_screening_done BOOLEAN DEFAULT FALSE,
    contrast_allergy_history BOOLEAN DEFAULT FALSE,
    previous_contrast_reactions JSONB, -- Details of previous reactions
    allergy_severity VARCHAR(20) CHECK (allergy_severity IN (
        'None', 'Mild', 'Moderate', 'Severe', 'Life_Threatening'
    )),

    -- Medication History
    current_medications JSONB,
    anticoagulant_use BOOLEAN DEFAULT FALSE,
    metformin_use BOOLEAN DEFAULT FALSE,
    medication_adjustments_needed BOOLEAN DEFAULT FALSE,

    -- Renal Function Assessment
    renal_function_screening BOOLEAN DEFAULT FALSE,
    latest_creatinine DECIMAL(5,2), -- mg/dL
    latest_gfr DECIMAL(5,1), -- mL/min/1.73m²
    creatinine_date DATE,
    renal_impairment_risk VARCHAR(20) CHECK (renal_impairment_risk IN (
        'Low', 'Moderate', 'High', 'Severe'
    )),

    -- Previous Imaging History
    previous_contrast_exams JSONB, -- History of contrast-enhanced studies
    cumulative_radiation_dose DECIMAL(8,2), -- mGy (for radiation tracking)
    recent_similar_exams JSONB, -- Similar exams in last 6 months

    -- MRI Safety Screening
    mri_safety_screening BOOLEAN DEFAULT FALSE,
    mri_contraindications JSONB, -- Pacemaker, implants, etc.
    mri_safety_questionnaire_complete BOOLEAN DEFAULT FALSE,
    mri_safety_cleared BOOLEAN DEFAULT FALSE,

    -- Risk Assessment Results
    overall_risk_level VARCHAR(20) DEFAULT 'Low' CHECK (overall_risk_level IN (
        'Low', 'Moderate', 'High', 'Contraindicated'
    )),
    risk_factors JSONB,

    -- Premedication and Preparation
    premedication_required BOOLEAN DEFAULT FALSE,
    premedication_protocol JSONB,
    preparation_instructions_given BOOLEAN DEFAULT FALSE,
    patient_education_completed BOOLEAN DEFAULT FALSE,

    -- Consent and Documentation
    informed_consent_obtained BOOLEAN DEFAULT FALSE,
    consent_form_signed BOOLEAN DEFAULT FALSE,
    consent_witness_id UUID REFERENCES users(id),

    -- Special Considerations
    special_needs JSONB, -- Language barrier, mobility issues, etc.
    isolation_precautions BOOLEAN DEFAULT FALSE,
    infection_control_measures JSONB,

    -- Clinical Notes
    screening_notes TEXT,
    additional_precautions TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,

    -- Screening Results
    screening_passed BOOLEAN DEFAULT TRUE,
    screening_warnings JSONB,
    screening_recommendations JSONB,

    -- Status and Audit
    is_complete BOOLEAN DEFAULT FALSE,
    reviewed_by_physician BOOLEAN DEFAULT FALSE,
    reviewed_by_user_id UUID REFERENCES users(id),
    review_notes TEXT,

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

Indexes:

CREATE INDEX idx_patient_screening_patient ON patient_screening(patient_id, screening_date_time DESC);
CREATE INDEX idx_patient_screening_order ON patient_screening(radiology_order_id);
CREATE INDEX idx_patient_screening_type ON patient_screening(screening_type, screening_date_time DESC);
CREATE INDEX idx_patient_screening_risk ON patient_screening(overall_risk_level) WHERE overall_risk_level != 'Low';
CREATE INDEX idx_patient_screening_pregnancy ON patient_screening(pregnancy_status) WHERE pregnancy_screening_required = TRUE;

4. ตาราง `contraindication_checks`

ตารางสำหรับบันทึกการตรวจสอบข้อห้ามและความเสี่ยงอัตโนมัติ

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

    -- Reference Information
    radiology_order_id UUID NOT NULL REFERENCES radiology_orders(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Check Information
    check_type VARCHAR(50) NOT NULL CHECK (check_type IN (
        'Allergy_Check', 'Renal_Function', 'Pregnancy_Check', 'MRI_Safety',
        'Drug_Interaction', 'Previous_Exam', 'Radiation_Exposure', 'Equipment_Compatibility'
    )),

    -- Check Details
    check_performed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    check_method VARCHAR(50), -- Automated, Manual, AI_Assisted
    data_sources JSONB, -- Sources of information used for checking

    -- Check Results
    contraindication_found BOOLEAN DEFAULT FALSE,
    risk_level VARCHAR(20) CHECK (risk_level IN ('None', 'Low', 'Moderate', 'High', 'Absolute')),

    -- Specific Contraindication Details
    contraindication_type VARCHAR(100),
    contraindication_description TEXT,
    contraindication_code VARCHAR(20), -- Standardized code if available

    -- Related Clinical Data
    clinical_values JSONB, -- Relevant lab values, vital signs, etc.
    reference_ranges JSONB, -- Normal ranges for comparison

    -- Recommendations and Actions
    recommended_action VARCHAR(100), -- Proceed, Modify_Protocol, Cancel, Consult_Physician
    alternative_suggestions JSONB, -- Alternative exams or protocols
    risk_mitigation_strategies JSONB,

    -- Premedication Recommendations
    premedication_recommended BOOLEAN DEFAULT FALSE,
    premedication_protocol JSONB,
    monitoring_requirements JSONB,

    -- Physician Override
    physician_override BOOLEAN DEFAULT FALSE,
    override_physician_id UUID REFERENCES users(id),
    override_reason TEXT,
    override_timestamp TIMESTAMP WITH TIME ZONE,

    -- Documentation and Evidence
    supporting_documentation JSONB,
    literature_references JSONB,
    clinical_guidelines_followed VARCHAR(200),

    -- Alert and Notification
    alert_severity VARCHAR(20) CHECK (alert_severity IN ('Info', 'Warning', 'Critical', 'Block')),
    alert_displayed BOOLEAN DEFAULT FALSE,
    alert_acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by_user_id UUID REFERENCES users(id),
    acknowledgment_timestamp TIMESTAMP WITH TIME ZONE,

    -- Follow-up Actions
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_instructions TEXT,
    reassessment_required BOOLEAN DEFAULT FALSE,
    reassessment_interval INTEGER, -- hours

    -- Integration with AI System
    ai_confidence_score DECIMAL(3,2), -- 0.00 to 1.00
    ai_model_version VARCHAR(20),
    ai_processing_time_ms INTEGER,

    -- Audit and Quality
    check_accuracy_verified BOOLEAN DEFAULT FALSE,
    false_positive_flag BOOLEAN DEFAULT FALSE,
    false_negative_flag BOOLEAN DEFAULT FALSE,
    quality_review_notes TEXT,

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

Indexes:

CREATE INDEX idx_contraindication_checks_order ON contraindication_checks(radiology_order_id, check_performed_at DESC);
CREATE INDEX idx_contraindication_checks_patient ON contraindication_checks(patient_id, check_type);
CREATE INDEX idx_contraindication_checks_found ON contraindication_checks(contraindication_found, risk_level);
CREATE INDEX idx_contraindication_checks_type ON contraindication_checks(check_type, check_performed_at DESC);
CREATE INDEX idx_contraindication_checks_alert ON contraindication_checks(alert_severity) WHERE alert_severity IN ('Critical', 'Block');

5. ตาราง `ai_recommendations`

ตารางสำหรับบันทึกคำแนะนำจาก CPOE AI Assist

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

    -- Reference Information
    radiology_order_id UUID NOT NULL REFERENCES radiology_orders(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- AI Processing Information
    ai_model_name VARCHAR(100) NOT NULL,
    ai_model_version VARCHAR(20) NOT NULL,
    processing_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processing_time_ms INTEGER,

    -- Input Data Analysis
    input_data JSONB NOT NULL, -- Clinical indication, symptoms, patient data used
    data_completeness_score DECIMAL(3,2), -- 0.00 to 1.00
    data_quality_flags JSONB,

    -- Recommendation Categories
    recommendation_type VARCHAR(50) NOT NULL CHECK (recommendation_type IN (
        'Exam_Selection', 'Protocol_Optimization', 'Contraindication_Alert',
        'Alternative_Suggestion', 'Preparation_Guidance', 'Timing_Recommendation'
    )),

    -- Primary Recommendations
    primary_recommendation TEXT NOT NULL,
    confidence_score DECIMAL(3,2) NOT NULL, -- 0.00 to 1.00
    evidence_level VARCHAR(20) CHECK (evidence_level IN ('High', 'Moderate', 'Low', 'Expert_Opinion')),

    -- Alternative Recommendations
    alternative_recommendations JSONB,
    comparative_analysis JSONB, -- Pros/cons of alternatives

    -- Clinical Decision Support
    clinical_guidelines_referenced JSONB,
    literature_evidence JSONB,
    best_practice_alignment VARCHAR(20) CHECK (best_practice_alignment IN (
        'Fully_Aligned', 'Mostly_Aligned', 'Partially_Aligned', 'Not_Aligned'
    )),

    -- Risk Assessment
    risk_factors_identified JSONB,
    risk_mitigation_suggestions JSONB,
    safety_considerations JSONB,

    -- Protocol Recommendations
    suggested_protocol JSONB, -- Detailed imaging parameters
    contrast_recommendations JSONB,
    positioning_suggestions JSONB,
    acquisition_parameters JSONB,

    -- Patient-Specific Factors
    patient_factors_considered JSONB, -- Age, weight, medical history, etc.
    customization_applied BOOLEAN DEFAULT FALSE,
    special_considerations TEXT,

    -- Cost-Effectiveness Analysis
    cost_analysis JSONB,
    resource_utilization_impact VARCHAR(50),
    efficiency_recommendations JSONB,

    -- Quality Metrics
    expected_diagnostic_yield DECIMAL(3,2), -- 0.00 to 1.00
    image_quality_prediction VARCHAR(20),
    technical_success_probability DECIMAL(3,2),

    -- Physician Interaction
    physician_reviewed BOOLEAN DEFAULT FALSE,
    physician_acceptance VARCHAR(20) CHECK (physician_acceptance IN (
        'Accepted', 'Partially_Accepted', 'Rejected', 'Modified', 'Pending'
    )),
    acceptance_timestamp TIMESTAMP WITH TIME ZONE,
    acceptance_notes TEXT,

    -- Override Information
    physician_override BOOLEAN DEFAULT FALSE,
    override_reason TEXT,
    override_details JSONB,

    -- Learning and Feedback
    outcome_tracked BOOLEAN DEFAULT FALSE,
    actual_outcome JSONB, -- What actually happened
    recommendation_accuracy DECIMAL(3,2), -- How accurate was the recommendation
    feedback_provided BOOLEAN DEFAULT FALSE,
    feedback_details JSONB,

    -- System Integration
    integrated_with_cpoe BOOLEAN DEFAULT TRUE,
    cpoe_workflow_step VARCHAR(50),
    auto_applied BOOLEAN DEFAULT FALSE,

    -- Alert and Notification
    alert_generated BOOLEAN DEFAULT FALSE,
    alert_type VARCHAR(20) CHECK (alert_type IN ('Info', 'Warning', 'Critical')),
    alert_message TEXT,

    -- Performance Metrics
    user_interaction_time INTEGER, -- seconds
    decision_impact VARCHAR(50), -- How the recommendation influenced decision
    workflow_efficiency_impact VARCHAR(50),

    -- Audit and Compliance
    recommendation_rationale TEXT,
    evidence_citations JSONB,
    regulatory_compliance_checked BOOLEAN DEFAULT FALSE,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP WITH TIME ZONE,

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

Indexes:

CREATE INDEX idx_ai_recommendations_order ON ai_recommendations(radiology_order_id, processing_timestamp DESC);
CREATE INDEX idx_ai_recommendations_patient ON ai_recommendations(patient_id, recommendation_type);
CREATE INDEX idx_ai_recommendations_model ON ai_recommendations(ai_model_name, ai_model_version);
CREATE INDEX idx_ai_recommendations_confidence ON ai_recommendations(confidence_score DESC, recommendation_type);
CREATE INDEX idx_ai_recommendations_acceptance ON ai_recommendations(physician_acceptance) WHERE physician_reviewed = TRUE;
CREATE INDEX idx_ai_recommendations_outcome ON ai_recommendations(outcome_tracked, recommendation_accuracy);

6. ตาราง `dicom_studies`

ตารางสำหรับจัดการข้อมูล DICOM Study ระดับสูงสุด

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

    -- DICOM Identifiers
    study_instance_uid VARCHAR(128) UNIQUE NOT NULL,
    study_id VARCHAR(64),
    accession_number VARCHAR(64),

    -- Patient and Order Reference
    patient_id UUID NOT NULL REFERENCES patients(id),
    radiology_order_id UUID REFERENCES radiology_orders(id),

    -- Study Information
    study_date DATE NOT NULL,
    study_time TIME,
    study_datetime TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS (
        (study_date + study_time)::TIMESTAMP WITH TIME ZONE
    ) STORED,

    -- Study Details
    study_description VARCHAR(200),
    modality VARCHAR(20) NOT NULL, -- CT, MR, XR, US, NM, etc.
    modalities_in_study VARCHAR(200), -- Multiple modalities if applicable

    -- Patient Information (DICOM tags)
    patient_name VARCHAR(200),
    patient_birth_date DATE,
    patient_sex CHAR(1),
    patient_age VARCHAR(10),
    patient_weight DECIMAL(6,2),
    patient_size DECIMAL(6,2),

    -- Referring Physician
    referring_physician_name VARCHAR(200),
    referring_physician_id UUID REFERENCES users(id),

    -- Institution Information
    institution_name VARCHAR(200),
    institutional_department_name VARCHAR(200),
    station_name VARCHAR(64),

    -- Study Statistics
    number_of_series INTEGER DEFAULT 0,
    number_of_instances INTEGER DEFAULT 0,
    study_size_bytes BIGINT DEFAULT 0,

    -- Study Status
    study_status VARCHAR(20) DEFAULT 'In_Progress' CHECK (study_status IN (
        'Scheduled', 'In_Progress', 'Completed', 'Cancelled', 'Archived'
    )),
    completion_status VARCHAR(20) DEFAULT 'Partial' CHECK (completion_status IN (
        'Partial', 'Complete', 'Verified'
    )),

    -- Quality Control
    image_quality VARCHAR(20) CHECK (image_quality IN ('Excellent', 'Good', 'Acceptable', 'Poor')),
    technical_quality VARCHAR(20) CHECK (technical_quality IN ('Optimal', 'Adequate', 'Suboptimal')),
    artifacts_present BOOLEAN DEFAULT FALSE,
    artifacts_description TEXT,

    -- Storage Information
    storage_location VARCHAR(500),
    storage_type VARCHAR(50) DEFAULT 'Local' CHECK (storage_type IN ('Local', 'Cloud', 'Archive', 'Distributed')),
    compression_applied BOOLEAN DEFAULT FALSE,
    compression_ratio DECIMAL(4,2),

    -- PACS Integration
    sent_to_pacs BOOLEAN DEFAULT FALSE,
    pacs_storage_commitment BOOLEAN DEFAULT FALSE,
    pacs_study_uid VARCHAR(128),
    pacs_status VARCHAR(30),

    -- Access and Security
    access_level VARCHAR(20) DEFAULT 'Standard' CHECK (access_level IN (
        'Public', 'Standard', 'Restricted', 'Confidential'
    )),
    anonymized BOOLEAN DEFAULT FALSE,
    anonymization_method VARCHAR(50),

    -- Workflow Information
    acquisition_started_at TIMESTAMP WITH TIME ZONE,
    acquisition_completed_at TIMESTAMP WITH TIME ZONE,
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by_user_id UUID REFERENCES users(id),

    -- Clinical Information
    clinical_indication TEXT,
    study_comments TEXT,
    interpretation_status VARCHAR(30) DEFAULT 'Unread' CHECK (interpretation_status IN (
        'Unread', 'Preliminary', 'Final', 'Amended', 'Dictated'
    )),

    -- Research and Teaching
    research_study BOOLEAN DEFAULT FALSE,
    teaching_file BOOLEAN DEFAULT FALSE,
    patient_consent_for_teaching BOOLEAN DEFAULT FALSE,

    -- Retention and Archival
    retention_period INTEGER DEFAULT 3650, -- days (10 years default)
    archive_eligible_date DATE GENERATED ALWAYS AS (study_date + retention_period) STORED,
    archived BOOLEAN DEFAULT FALSE,
    archived_at TIMESTAMP WITH TIME ZONE,
    archive_location VARCHAR(500),

    -- Audit and Compliance
    phi_removed BOOLEAN DEFAULT FALSE, -- Protected Health Information
    audit_trail JSONB,

    -- Status and Metadata
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

    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)
);

Indexes:

CREATE INDEX idx_dicom_studies_patient ON dicom_studies(patient_id, study_date DESC);
CREATE INDEX idx_dicom_studies_modality ON dicom_studies(modality, study_date DESC);
CREATE INDEX idx_dicom_studies_status ON dicom_studies(study_status, interpretation_status);
CREATE INDEX idx_dicom_studies_accession ON dicom_studies(accession_number) WHERE accession_number IS NOT NULL;
CREATE INDEX idx_dicom_studies_uid ON dicom_studies(study_instance_uid);
CREATE INDEX idx_dicom_studies_referring ON dicom_studies(referring_physician_id, study_date DESC);
CREATE INDEX idx_dicom_studies_archive ON dicom_studies(archive_eligible_date) WHERE archived = FALSE;

7. ตาราง `dicom_series`

ตารางสำหรับจัดการ DICOM Series ภายใต้แต่ละ Study

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

    -- DICOM Identifiers
    series_instance_uid VARCHAR(128) UNIQUE NOT NULL,
    series_number INTEGER,

    -- Study Reference
    study_id UUID NOT NULL REFERENCES dicom_studies(id) ON DELETE CASCADE,
    study_instance_uid VARCHAR(128) NOT NULL,

    -- Series Information
    modality VARCHAR(20) NOT NULL,
    series_description VARCHAR(200),
    series_date DATE,
    series_time TIME,

    -- Acquisition Information
    protocol_name VARCHAR(200),
    scanning_sequence VARCHAR(50),
    sequence_variant VARCHAR(50),
    scan_options VARCHAR(200),

    -- Image Characteristics
    number_of_instances INTEGER DEFAULT 0,
    image_orientation VARCHAR(100),
    pixel_spacing_x DECIMAL(10,6),
    pixel_spacing_y DECIMAL(10,6),
    slice_thickness DECIMAL(8,3),
    slice_spacing DECIMAL(8,3),

    -- Technical Parameters (CT specific)
    kvp DECIMAL(6,2), -- Kilovoltage Peak
    tube_current INTEGER, -- mA
    exposure_time DECIMAL(8,3), -- ms
    exposure DECIMAL(8,3), -- mAs

    -- Technical Parameters (MR specific)
    magnetic_field_strength DECIMAL(6,2), -- Tesla
    repetition_time DECIMAL(8,3), -- TR in ms
    echo_time DECIMAL(8,3), -- TE in ms
    flip_angle DECIMAL(6,2), -- degrees

    -- Contrast Information
    contrast_bolus_agent VARCHAR(100),
    contrast_bolus_volume DECIMAL(8,2),
    contrast_bolus_start_time TIME,
    contrast_flow_rate DECIMAL(6,2),

    -- Image Processing
    reconstruction_algorithm VARCHAR(100),
    convolution_kernel VARCHAR(50),
    reconstruction_diameter DECIMAL(8,2),

    -- Series Status
    series_status VARCHAR(20) DEFAULT 'In_Progress' CHECK (series_status IN (
        'Scheduled', 'In_Progress', 'Completed', 'Cancelled'
    )),

    -- Quality Information
    image_quality_rating INTEGER CHECK (image_quality_rating BETWEEN 1 AND 5),
    motion_artifacts BOOLEAN DEFAULT FALSE,
    noise_level VARCHAR(20) CHECK (noise_level IN ('Low', 'Acceptable', 'High')),

    -- Storage Information
    series_size_bytes BIGINT DEFAULT 0,
    compression_type VARCHAR(50),
    transfer_syntax_uid VARCHAR(128),

    -- Processing Information
    processed BOOLEAN DEFAULT FALSE,
    processing_applied JSONB, -- List of processing steps
    reconstructions_available JSONB, -- Available reconstructions

    -- Display Information
    window_center INTEGER,
    window_width INTEGER,
    display_format VARCHAR(20),

    -- Series Workflow
    acquisition_started_at TIMESTAMP WITH TIME ZONE,
    acquisition_completed_at TIMESTAMP WITH TIME ZONE,
    processing_completed_at TIMESTAMP WITH TIME ZONE,

    -- Equipment Information
    manufacturer VARCHAR(100),
    manufacturer_model_name VARCHAR(100),
    device_serial_number VARCHAR(50),
    software_versions VARCHAR(200),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

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

Indexes:

CREATE INDEX idx_dicom_series_study ON dicom_series(study_id, series_number);
CREATE INDEX idx_dicom_series_uid ON dicom_series(series_instance_uid);
CREATE INDEX idx_dicom_series_modality ON dicom_series(modality, series_date DESC);
CREATE INDEX idx_dicom_series_status ON dicom_series(series_status);
CREATE INDEX idx_dicom_series_quality ON dicom_series(image_quality_rating, motion_artifacts);

8. ตาราง `dicom_instances`

ตารางสำหรับจัดการ DICOM Instance (ภาพแต่ละใบ)

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

    -- DICOM Identifiers
    sop_instance_uid VARCHAR(128) UNIQUE NOT NULL,
    sop_class_uid VARCHAR(128) NOT NULL,
    instance_number INTEGER,

    -- Series Reference
    series_id UUID NOT NULL REFERENCES dicom_series(id) ON DELETE CASCADE,
    series_instance_uid VARCHAR(128) NOT NULL,

    -- Instance Information
    instance_creation_date DATE,
    instance_creation_time TIME,
    content_date DATE,
    content_time TIME,

    -- Image Characteristics
    rows INTEGER, -- Image height in pixels
    columns INTEGER, -- Image width in pixels
    bits_allocated INTEGER,
    bits_stored INTEGER,
    pixel_representation INTEGER,
    photometric_interpretation VARCHAR(50),

    -- Spatial Information
    image_position_x DECIMAL(12,6),
    image_position_y DECIMAL(12,6),
    image_position_z DECIMAL(12,6),
    slice_location DECIMAL(12,6),

    -- Image Content
    image_type VARCHAR(200),
    acquisition_number INTEGER,
    temporal_position_identifier INTEGER,

    -- File Information
    file_path VARCHAR(1000),
    file_name VARCHAR(500),
    file_size_bytes BIGINT,
    file_hash VARCHAR(128), -- SHA-256 hash for integrity

    -- Transfer and Storage
    transfer_syntax_uid VARCHAR(128),
    compressed BOOLEAN DEFAULT FALSE,
    compression_ratio DECIMAL(6,2),
    lossless_compression BOOLEAN DEFAULT TRUE,

    -- Image Processing
    window_center INTEGER,
    window_width INTEGER,
    rescale_intercept DECIMAL(12,6),
    rescale_slope DECIMAL(12,6),
    rescale_type VARCHAR(20),

    -- Presentation Information
    presentation_lut_shape VARCHAR(20),
    pixel_intensity_relationship VARCHAR(50),

    -- Quality Metrics
    image_quality_score DECIMAL(3,2), -- 0.00 to 1.00
    signal_to_noise_ratio DECIMAL(6,2),
    contrast_to_noise_ratio DECIMAL(6,2),

    -- Processing Status
    processed BOOLEAN DEFAULT FALSE,
    processing_applied JSONB,
    derived_images JSONB, -- References to derived/processed images

    -- Verification and Integrity
    checksum_verified BOOLEAN DEFAULT FALSE,
    dicom_conformance_verified BOOLEAN DEFAULT FALSE,

    -- Access Information
    access_count INTEGER DEFAULT 0,
    last_accessed_at TIMESTAMP WITH TIME ZONE,
    last_accessed_by_user_id UUID REFERENCES users(id),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

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

Indexes:

CREATE INDEX idx_dicom_instances_series ON dicom_instances(series_id, instance_number);
CREATE INDEX idx_dicom_instances_uid ON dicom_instances(sop_instance_uid);
CREATE INDEX idx_dicom_instances_location ON dicom_instances(slice_location);
CREATE INDEX idx_dicom_instances_file ON dicom_instances(file_path);
CREATE INDEX idx_dicom_instances_quality ON dicom_instances(image_quality_score DESC);
CREATE INDEX idx_dicom_instances_access ON dicom_instances(last_accessed_at DESC);

9. ตาราง `radiology_reports`

ตารางสำหรับจัดการรายงานการตรวจรังสี

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

    -- Study and Order Reference
    study_id UUID NOT NULL REFERENCES dicom_studies(id),
    radiology_order_id UUID NOT NULL REFERENCES radiology_orders(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Report Identification
    report_number VARCHAR(20) UNIQUE NOT NULL,
    accession_number VARCHAR(30),

    -- Report Content
    preliminary_report TEXT,
    final_report TEXT NOT NULL,
    addendum TEXT,
    clinical_history TEXT,

    -- Report Structure
    findings TEXT NOT NULL,
    impression TEXT NOT NULL,
    recommendations TEXT,
    technique TEXT,
    comparison TEXT,

    -- Report Metadata
    report_type VARCHAR(20) DEFAULT 'Final' CHECK (report_type IN (
        'Preliminary', 'Final', 'Addendum', 'Amended', 'Corrected'
    )),
    report_status VARCHAR(20) DEFAULT 'Draft' CHECK (report_status IN (
        'Draft', 'Preliminary', 'Final', 'Signed', 'Amended', 'Cancelled'
    )),

    -- Dictation Information
    dictated BOOLEAN DEFAULT FALSE,
    dictation_method VARCHAR(30), -- Voice_Recognition, Manual_Typing, Template
    dictated_at TIMESTAMP WITH TIME ZONE,
    dictated_by_user_id UUID REFERENCES users(id),

    -- Authoring Information
    authored_by_user_id UUID NOT NULL REFERENCES users(id),
    authored_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Review and Verification
    reviewed_by_user_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    review_comments TEXT,

    -- Transcription (if applicable)
    transcribed_by_user_id UUID REFERENCES users(id),
    transcribed_at TIMESTAMP WITH TIME ZONE,
    transcription_accuracy_score DECIMAL(3,2), -- 0.00 to 1.00

    -- Report Templates and Standards
    template_used VARCHAR(100),
    structured_reporting BOOLEAN DEFAULT FALSE,
    reporting_standard VARCHAR(50), -- RadLex, BI-RADS, etc.

    -- Clinical Context
    clinical_urgency VARCHAR(20) CHECK (clinical_urgency IN ('Routine', 'Urgent', 'Critical')),
    clinical_correlation_needed BOOLEAN DEFAULT FALSE,
    follow_up_recommended BOOLEAN DEFAULT FALSE,
    follow_up_interval VARCHAR(50),
    follow_up_modality VARCHAR(50),

    -- Quality and Completeness
    report_completeness_score DECIMAL(3,2), -- 0.00 to 1.00
    critical_findings_present BOOLEAN DEFAULT FALSE,
    discrepancy_with_preliminary BOOLEAN DEFAULT FALSE,

    -- Communication
    critical_results_notified BOOLEAN DEFAULT FALSE,
    notification_method VARCHAR(50),
    notification_timestamp TIMESTAMP WITH TIME ZONE,
    notified_physician_id UUID REFERENCES users(id),

    -- Referring Physician Communication
    communicated_to_referrer BOOLEAN DEFAULT FALSE,
    communication_method VARCHAR(50),
    communication_timestamp TIMESTAMP WITH TIME ZONE,

    -- Digital Signature
    digitally_signed BOOLEAN DEFAULT FALSE,
    signature_id UUID REFERENCES digital_signatures(id),
    signed_at TIMESTAMP WITH TIME ZONE,
    signature_verified BOOLEAN DEFAULT FALSE,

    -- Version Control
    version INTEGER DEFAULT 1,
    previous_version_id UUID REFERENCES radiology_reports(id),
    version_change_reason TEXT,

    -- Report Metrics
    turnaround_time_minutes INTEGER,
    reading_time_minutes INTEGER,
    complexity_score INTEGER CHECK (complexity_score BETWEEN 1 AND 10),

    -- Billing and Coding
    procedure_codes JSONB, -- CPT codes
    diagnosis_codes JSONB, -- ICD-10 codes
    modifier_codes JSONB,

    -- Research and Quality
    research_flag BOOLEAN DEFAULT FALSE,
    teaching_case BOOLEAN DEFAULT FALSE,
    quality_review_flag BOOLEAN DEFAULT FALSE,

    -- Export and Distribution
    exported_to_emr BOOLEAN DEFAULT FALSE,
    export_timestamp TIMESTAMP WITH TIME ZONE,
    pdf_generated BOOLEAN DEFAULT FALSE,
    pdf_file_path VARCHAR(1000),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

    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)
);

Indexes:

CREATE INDEX idx_radiology_reports_study ON radiology_reports(study_id);
CREATE INDEX idx_radiology_reports_order ON radiology_reports(radiology_order_id);
CREATE INDEX idx_radiology_reports_patient ON radiology_reports(patient_id, authored_at DESC);
CREATE INDEX idx_radiology_reports_author ON radiology_reports(authored_by_user_id, authored_at DESC);
CREATE INDEX idx_radiology_reports_status ON radiology_reports(report_status, report_type);
CREATE INDEX idx_radiology_reports_critical ON radiology_reports(critical_findings_present) WHERE critical_findings_present = TRUE;
CREATE INDEX idx_radiology_reports_pending ON radiology_reports(report_status) WHERE report_status IN ('Draft', 'Preliminary');
CREATE INDEX idx_radiology_reports_turnaround ON radiology_reports(turnaround_time_minutes);

-- Full-text search on report content
CREATE INDEX idx_radiology_reports_search ON radiology_reports USING gin(
    to_tsvector('thai', 
        COALESCE(final_report, '') || ' ' || COALESCE(findings, '') || ' ' || 
        COALESCE(impression, '') || ' ' || COALESCE(recommendations, '')
    )
);

10. ตาราง `critical_findings`

ตารางสำหรับจัดการผลการตรวจที่มีความสำคัญฉุกเฉิน

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

    -- Reference Information
    radiology_report_id UUID NOT NULL REFERENCES radiology_reports(id),
    study_id UUID NOT NULL REFERENCES dicom_studies(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Finding Details
    finding_category VARCHAR(50) NOT NULL CHECK (finding_category IN (
        'Acute_Stroke', 'Acute_MI', 'Pulmonary_Embolism', 'Aortic_Aneurysm',
        'Free_Air', 'Mass_Lesion', 'Fracture', 'Hemorrhage', 'Pneumothorax',
        'Bowel_Obstruction', 'Infection', 'Other_Critical'
    )),

    finding_description TEXT NOT NULL,
    anatomical_location VARCHAR(200),
    severity_level VARCHAR(20) CHECK (severity_level IN ('High', 'Medium', 'Low')),

    -- Clinical Significance
    clinical_urgency VARCHAR(20) NOT NULL CHECK (clinical_urgency IN ('Immediate', 'Within_1_hour', 'Within_4_hours', 'Same_day')),
    potential_consequences TEXT,
    recommended_actions TEXT NOT NULL,

    -- Discovery Information
    discovered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    discovered_by_user_id UUID NOT NULL REFERENCES users(id),
    discovery_method VARCHAR(50), -- Reading, AI_Detection, Peer_Review

    -- AI Detection (if applicable)
    ai_detected BOOLEAN DEFAULT FALSE,
    ai_confidence_score DECIMAL(3,2), -- 0.00 to 1.00
    ai_model_name VARCHAR(100),
    ai_detection_timestamp TIMESTAMP WITH TIME ZONE,

    -- Verification
    verified BOOLEAN DEFAULT FALSE,
    verified_by_user_id UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    verification_comments TEXT,

    -- Notification Process
    notification_required BOOLEAN DEFAULT TRUE,
    notification_priority VARCHAR(20) CHECK (notification_priority IN ('Critical', 'Urgent', 'High')),

    -- Primary Notification (to ordering physician)
    primary_notified BOOLEAN DEFAULT FALSE,
    primary_notification_method VARCHAR(50),
    primary_notification_timestamp TIMESTAMP WITH TIME ZONE,
    primary_notified_user_id UUID REFERENCES users(id),
    primary_acknowledgment_received BOOLEAN DEFAULT FALSE,
    primary_acknowledgment_timestamp TIMESTAMP WITH TIME ZONE,

    -- Additional Notifications
    additional_notifications JSONB, -- Other physicians/staff notified
    notification_attempts INTEGER DEFAULT 0,
    escalation_required BOOLEAN DEFAULT FALSE,
    escalation_timestamp TIMESTAMP WITH TIME ZONE,
    escalation_to_user_id UUID REFERENCES users(id),

    -- Communication Documentation
    communication_log JSONB,
    phone_call_made BOOLEAN DEFAULT FALSE,
    email_sent BOOLEAN DEFAULT FALSE,
    sms_sent BOOLEAN DEFAULT FALSE,
    in_person_notification BOOLEAN DEFAULT FALSE,

    -- Follow-up
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_instructions TEXT,
    follow_up_timeline VARCHAR(100),
    follow_up_completed BOOLEAN DEFAULT FALSE,
    follow_up_notes TEXT,

    -- Documentation and Compliance
    documentation_complete BOOLEAN DEFAULT FALSE,
    policy_compliance_verified BOOLEAN DEFAULT FALSE,
    regulatory_reporting_required BOOLEAN DEFAULT FALSE,

    -- Quality and Review
    peer_review_requested BOOLEAN DEFAULT FALSE,
    peer_review_completed BOOLEAN DEFAULT FALSE,
    peer_reviewer_id UUID REFERENCES users(id),
    peer_review_outcome VARCHAR(50),
    peer_review_comments TEXT,

    -- Outcome Tracking
    patient_outcome_tracked BOOLEAN DEFAULT FALSE,
    clinical_outcome VARCHAR(100),
    outcome_comments TEXT,

    -- Learning and Improvement
    case_discussion_scheduled BOOLEAN DEFAULT FALSE,
    teaching_case_potential BOOLEAN DEFAULT FALSE,
    quality_improvement_flag BOOLEAN DEFAULT FALSE,

    -- Status
    finding_status VARCHAR(20) DEFAULT 'Active' CHECK (finding_status IN (
        'Active', 'Resolved', 'False_Positive', 'Under_Review'
    )),
    resolution_timestamp TIMESTAMP WITH TIME ZONE,
    resolution_notes TEXT,

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

Indexes:

CREATE INDEX idx_critical_findings_patient ON critical_findings(patient_id, discovered_at DESC);
CREATE INDEX idx_critical_findings_report ON critical_findings(radiology_report_id);
CREATE INDEX idx_critical_findings_category ON critical_findings(finding_category, severity_level);
CREATE INDEX idx_critical_findings_urgency ON critical_findings(clinical_urgency, discovered_at DESC);
CREATE INDEX idx_critical_findings_notification ON critical_findings(primary_notified, notification_required);
CREATE INDEX idx_critical_findings_status ON critical_findings(finding_status, discovered_at DESC);
CREATE INDEX idx_critical_findings_ai ON critical_findings(ai_detected, ai_confidence_score DESC);

11. ตาราง `radiation_exposure`

ตารางสำหรับติดตามปริมาณรังสีที่ผู้ป่วยได้รับ

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

    -- Patient and Study Reference
    patient_id UUID NOT NULL REFERENCES patients(id),
    study_id UUID REFERENCES dicom_studies(id),
    radiology_order_id UUID REFERENCES radiology_orders(id),

    -- Exposure Event Information
    exposure_date DATE NOT NULL,
    exposure_time TIME,
    exam_type VARCHAR(100) NOT NULL,
    modality VARCHAR(20) NOT NULL,
    body_part_examined VARCHAR(100) NOT NULL,

    -- Radiation Dose Metrics
    dose_area_product DECIMAL(12,4), -- Gy⋅cm²
    entrance_dose DECIMAL(12,4), -- mGy
    effective_dose DECIMAL(10,4), -- mSv
    organ_dose JSONB, -- Organ-specific doses

    -- CT-Specific Metrics
    ctdi_vol DECIMAL(10,4), -- CT Dose Index Volume (mGy)
    dlp DECIMAL(12,2), -- Dose Length Product (mGy⋅cm)
    ctdi_w DECIMAL(10,4), -- Weighted CTDI

    -- Technical Parameters
    kvp INTEGER, -- Peak kilovoltage
    mas DECIMAL(8,2), -- Milliampere-seconds
    tube_current INTEGER, -- mA
    exposure_time_ms DECIMAL(8,3), -- milliseconds
    filtration VARCHAR(50),

    -- Examination Details
    number_of_exposures INTEGER DEFAULT 1,
    fluoroscopy_time DECIMAL(8,2), -- seconds
    cine_runs INTEGER,
    dsa_runs INTEGER,

    -- Pregnant Patient Considerations
    patient_pregnant BOOLEAN DEFAULT FALSE,
    fetal_dose_estimated BOOLEAN DEFAULT FALSE,
    estimated_fetal_dose DECIMAL(10,4), -- mGy
    pregnancy_weeks INTEGER,

    -- Pediatric Considerations
    patient_age_at_exam INTEGER,
    pediatric_dose_optimization BOOLEAN DEFAULT FALSE,
    size_based_protocol_used BOOLEAN DEFAULT FALSE,

    -- Equipment Information
    equipment_id UUID REFERENCES imaging_equipment(id),
    manufacturer VARCHAR(100),
    model_name VARCHAR(100),
    serial_number VARCHAR(50),

    -- Protocol Information
    protocol_name VARCHAR(200),
    acquisition_parameters JSONB,
    dose_optimization_techniques JSONB,

    -- Quality and Safety
    dose_reference_level_exceeded BOOLEAN DEFAULT FALSE,
    dose_alert_triggered BOOLEAN DEFAULT FALSE,
    dose_optimization_applied BOOLEAN DEFAULT FALSE,

    -- Notification and Review
    high_dose_notification BOOLEAN DEFAULT FALSE,
    radiologist_review_required BOOLEAN DEFAULT FALSE,
    medical_physicist_review BOOLEAN DEFAULT FALSE,

    -- Cumulative Tracking
    cumulative_dose_current_year DECIMAL(10,4), -- mSv
    cumulative_dose_lifetime DECIMAL(12,4), -- mSv
    previous_exams_considered BOOLEAN DEFAULT FALSE,

    -- Regulatory and Compliance
    dose_registry_reported BOOLEAN DEFAULT FALSE,
    dose_registry_id VARCHAR(50),
    regulatory_limits_checked BOOLEAN DEFAULT FALSE,

    -- Comparison and Benchmarking
    institutional_average_dose DECIMAL(10,4),
    national_average_dose DECIMAL(10,4),
    dose_variation_percentage DECIMAL(6,2),

    -- Quality Improvement
    dose_optimization_opportunity BOOLEAN DEFAULT FALSE,
    optimization_recommendations TEXT,
    peer_review_flag BOOLEAN DEFAULT FALSE,

    -- Data Sources and Validation
    dose_data_source VARCHAR(50) CHECK (dose_data_source IN (
        'DICOM_RDSR', 'Equipment_Log', 'Manual_Entry', 'Calculated'
    )),
    dose_data_validated BOOLEAN DEFAULT FALSE,
    validation_method VARCHAR(50),

    -- Error Handling
    dose_calculation_error BOOLEAN DEFAULT FALSE,
    error_description TEXT,
    correction_applied BOOLEAN DEFAULT FALSE,

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

Indexes:

CREATE INDEX idx_radiation_exposure_patient ON radiation_exposure(patient_id, exposure_date DESC);
CREATE INDEX idx_radiation_exposure_study ON radiation_exposure(study_id);
CREATE INDEX idx_radiation_exposure_date ON radiation_exposure(exposure_date DESC);
CREATE INDEX idx_radiation_exposure_modality ON radiation_exposure(modality, exposure_date DESC);
CREATE INDEX idx_radiation_exposure_dose ON radiation_exposure(effective_dose DESC);
CREATE INDEX idx_radiation_exposure_high_dose ON radiation_exposure(dose_reference_level_exceeded) WHERE dose_reference_level_exceeded = TRUE;
CREATE INDEX idx_radiation_exposure_pregnant ON radiation_exposure(patient_pregnant, exposure_date DESC) WHERE patient_pregnant = TRUE;
CREATE INDEX idx_radiation_exposure_pediatric ON radiation_exposure(patient_age_at_exam, exposure_date DESC) WHERE patient_age_at_exam < 18;

12. ตาราง `exam_protocols`

ตารางสำหรับจัดการ imaging protocols สำหรับการตรวจแต่ละประเภท

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

    -- Protocol Identification
    protocol_name VARCHAR(200) NOT NULL,
    protocol_code VARCHAR(50) UNIQUE NOT NULL,
    protocol_version VARCHAR(20) DEFAULT '1.0',

    -- Exam Association
    exam_code VARCHAR(20) NOT NULL REFERENCES radiology_exams(code),
    modality VARCHAR(20) NOT NULL,
    body_part VARCHAR(100) NOT NULL,

    -- Protocol Details
    protocol_description TEXT,
    clinical_indications JSONB,
    contraindications JSONB,

    -- Technical Parameters
    acquisition_parameters JSONB NOT NULL, -- Detailed technical settings
    reconstruction_parameters JSONB,
    post_processing_steps JSONB,

    -- Contrast Protocol
    contrast_protocol JSONB, -- Contrast timing, volume, rate
    contrast_required BOOLEAN DEFAULT FALSE,
    contrast_optional BOOLEAN DEFAULT FALSE,

    -- Patient Preparation
    preparation_instructions TEXT,
    fasting_required BOOLEAN DEFAULT FALSE,
    fasting_duration INTEGER, -- hours
    medication_instructions TEXT,

    -- Positioning and Setup
    patient_positioning VARCHAR(200),
    landmarks_and_coverage VARCHAR(500),
    field_of_view VARCHAR(100),

    -- Quality Standards
    image_quality_criteria JSONB,
    acceptance_criteria JSONB,
    dose_optimization_guidelines JSONB,

    -- Pediatric Considerations
    pediatric_modifications JSONB,
    age_weight_based_adjustments BOOLEAN DEFAULT FALSE,
    pediatric_dose_limits JSONB,

    -- Safety Guidelines
    safety_precautions JSONB,
    emergency_procedures TEXT,
    staff_safety_requirements JSONB,

    -- Equipment Requirements
    minimum_equipment_specifications JSONB,
    preferred_equipment_id UUID REFERENCES imaging_equipment(id),
    software_requirements JSONB,

    -- Workflow Integration
    estimated_scan_time INTEGER, -- minutes
    post_processing_time INTEGER, -- minutes
    technologist_requirements JSONB,

    -- Quality Assurance
    qa_checkpoints JSONB,
    performance_metrics JSONB,
    success_criteria JSONB,

    -- Clinical Guidelines
    evidence_base JSONB, -- Supporting literature/guidelines
    guideline_compliance VARCHAR(100),
    peer_review_status VARCHAR(50),

    -- Usage Statistics
    usage_frequency INTEGER DEFAULT 0,
    success_rate DECIMAL(5,2), -- Percentage
    average_image_quality_score DECIMAL(3,2),

    -- Version Control
    parent_protocol_id UUID REFERENCES exam_protocols(id),
    change_log JSONB,
    approval_status VARCHAR(30) DEFAULT 'Draft' CHECK (approval_status IN (
        'Draft', 'Under_Review', 'Approved', 'Active', 'Deprecated'
    )),

    -- Approval Workflow
    created_by_user_id UUID NOT NULL REFERENCES users(id),
    reviewed_by_user_id UUID REFERENCES users(id),
    approved_by_user_id UUID REFERENCES users(id),
    approved_at TIMESTAMP WITH TIME ZONE,

    -- Effective Dates
    effective_from DATE DEFAULT CURRENT_DATE,
    effective_until DATE,

    -- Training and Competency
    training_required BOOLEAN DEFAULT FALSE,
    competency_requirements JSONB,
    certification_needed BOOLEAN DEFAULT FALSE,

    -- Cost and Resource Planning
    estimated_cost DECIMAL(10,2),
    resource_requirements JSONB,
    consumables_needed JSONB,

    -- External Standards
    accreditation_standards JSONB,
    regulatory_compliance JSONB,
    international_guidelines JSONB,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

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

Indexes:

CREATE INDEX idx_exam_protocols_exam ON exam_protocols(exam_code, modality);
CREATE INDEX idx_exam_protocols_body_part ON exam_protocols(body_part, modality);
CREATE INDEX idx_exam_protocols_status ON exam_protocols(approval_status, is_active);
CREATE INDEX idx_exam_protocols_effective ON exam_protocols(effective_from, effective_until);
CREATE INDEX idx_exam_protocols_usage ON exam_protocols(usage_frequency DESC);

13. ตาราง `preparation_instructions`

ตารางสำหรับจัดการคำแนะนำการเตรียมตัวของผู้ป่วย

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

    -- Instruction Identification
    instruction_code VARCHAR(50) UNIQUE NOT NULL,
    instruction_name_thai VARCHAR(200) NOT NULL,
    instruction_name_english VARCHAR(200),

    -- Associated Exams
    exam_codes JSONB NOT NULL, -- Array of exam codes this applies to
    modality VARCHAR(20),
    body_parts JSONB, -- Array of body parts

    -- Instruction Categories
    category VARCHAR(50) NOT NULL CHECK (category IN (
        'Fasting', 'Medication', 'Clothing', 'Contrast_Prep', 'Bowel_Prep',
        'Hydration', 'Positioning', 'Removal_Items', 'Special_Needs'
    )),

    -- Instruction Content
    instruction_text_thai TEXT NOT NULL,
    instruction_text_english TEXT,
    detailed_instructions JSONB,

    -- Timing Information
    timing_before_exam INTEGER NOT NULL, -- hours before exam
    duration_required INTEGER, -- hours of preparation needed
    critical_timing BOOLEAN DEFAULT FALSE,

    -- Patient-Specific Variations
    adult_instructions TEXT,
    pediatric_instructions TEXT,
    elderly_instructions TEXT,
    pregnancy_instructions TEXT,

    -- Medical Condition Modifications
    diabetes_modifications TEXT,
    renal_disease_modifications TEXT,
    cardiac_disease_modifications TEXT,
    other_condition_modifications JSONB,

    -- Contrast-Specific Instructions
    contrast_types JSONB, -- Which contrasts this applies to
    pre_contrast_instructions TEXT,
    post_contrast_instructions TEXT,
    hydration_requirements JSONB,

    -- Medication Management
    medications_to_continue JSONB,
    medications_to_hold JSONB,
    medication_timing_adjustments JSONB,

    -- Dietary Instructions
    fasting_solids_hours INTEGER,
    fasting_liquids_hours INTEGER,
    clear_liquids_allowed BOOLEAN DEFAULT FALSE,
    special_diet_requirements TEXT,

    -- Physical Preparation
    clothing_requirements TEXT,
    items_to_remove JSONB, -- jewelry, metal objects, etc.
    positioning_preparation TEXT,
    mobility_requirements TEXT,

    -- Emergency Preparation
    emergency_contact_required BOOLEAN DEFAULT FALSE,
    escort_required BOOLEAN DEFAULT FALSE,
    transportation_arrangements TEXT,

    -- Communication Preferences
    delivery_methods JSONB, -- phone, sms, email, print, app
    language_versions JSONB,
    visual_aids_available BOOLEAN DEFAULT FALSE,

    -- Verification and Compliance
    verification_required BOOLEAN DEFAULT FALSE,
    verification_methods JSONB,
    compliance_tracking BOOLEAN DEFAULT FALSE,

    -- Quality and Effectiveness
    patient_understanding_score DECIMAL(3,2),
    compliance_rate DECIMAL(5,2),
    effectiveness_metrics JSONB,

    -- Content Management
    template_based BOOLEAN DEFAULT FALSE,
    template_id VARCHAR(50),
    customizable_fields JSONB,

    -- Approval and Review
    content_reviewed BOOLEAN DEFAULT FALSE,
    reviewed_by_user_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,

    clinical_review_required BOOLEAN DEFAULT FALSE,
    clinically_reviewed BOOLEAN DEFAULT FALSE,
    clinical_reviewer_id UUID REFERENCES users(id),

    -- Version Control
    version VARCHAR(20) DEFAULT '1.0',
    previous_version_id UUID REFERENCES preparation_instructions(id),
    change_summary TEXT,

    -- Usage Statistics
    usage_count INTEGER DEFAULT 0,
    patient_feedback_score DECIMAL(3,2),
    staff_feedback_score DECIMAL(3,2),

    -- Status and Scheduling
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    effective_date DATE DEFAULT CURRENT_DATE,
    expiration_date DATE,

    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)
);

Indexes:

CREATE INDEX idx_preparation_instructions_exam ON preparation_instructions USING gin(exam_codes);
CREATE INDEX idx_preparation_instructions_category ON preparation_instructions(category, modality);
CREATE INDEX idx_preparation_instructions_timing ON preparation_instructions(timing_before_exam);
CREATE INDEX idx_preparation_instructions_active ON preparation_instructions(is_active, effective_date);
CREATE INDEX idx_preparation_instructions_usage ON preparation_instructions(usage_count DESC);

14. ตาราง `imaging_equipment`

ตารางสำหรับจัดการข้อมูลเครื่องตรวจรังสี

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

    -- Equipment Identification
    equipment_code VARCHAR(50) UNIQUE NOT NULL,
    equipment_name VARCHAR(200) NOT NULL,
    asset_number VARCHAR(50),
    serial_number VARCHAR(100),

    -- Equipment Classification
    modality VARCHAR(20) NOT NULL CHECK (modality IN (
        'CT', 'MR', 'XR', 'US', 'NM', 'MG', 'FL', 'AN', 'PET', 'SPECT'
    )),
    equipment_type VARCHAR(100),
    category VARCHAR(50), -- Diagnostic, Interventional, Portable

    -- Manufacturer Information
    manufacturer VARCHAR(100) NOT NULL,
    model_name VARCHAR(100) NOT NULL,
    software_version VARCHAR(50),
    firmware_version VARCHAR(50),

    -- Technical Specifications
    technical_specs JSONB NOT NULL,
    performance_characteristics JSONB,
    imaging_capabilities JSONB,

    -- Installation Information
    installation_date DATE,
    commissioned_date DATE,
    acceptance_testing_completed BOOLEAN DEFAULT FALSE,
    warranty_expiration DATE,

    -- Location and Environment
    department_id UUID REFERENCES departments(id),
    room_number VARCHAR(50),
    building VARCHAR(50),
    floor VARCHAR(20),
    room_specifications JSONB,

    -- Operational Status
    operational_status VARCHAR(30) DEFAULT 'Active' CHECK (operational_status IN (
        'Active', 'Inactive', 'Maintenance', 'Repair', 'Decommissioned'
    )),
    availability_status VARCHAR(30) DEFAULT 'Available' CHECK (availability_status IN (
        'Available', 'In_Use', 'Maintenance', 'Out_of_Order', 'Reserved'
    )),

    -- Scheduling Information
    available_hours JSONB, -- Operating hours by day
    booking_calendar_id VARCHAR(100),
    advance_booking_days INTEGER DEFAULT 30,

    -- Capacity and Utilization
    daily_capacity INTEGER, -- Number of studies per day
    average_utilization DECIMAL(5,2), -- Percentage
    current_utilization DECIMAL(5,2),

    -- Quality Assurance
    qa_schedule JSONB,
    last_qa_date DATE,
    next_qa_due_date DATE,
    qa_status VARCHAR(30),
    qa_results JSONB,

    -- Maintenance Information
    maintenance_schedule JSONB,
    last_preventive_maintenance DATE,
    next_maintenance_due DATE,
    maintenance_contract_id VARCHAR(100),

    -- Service History
    service_calls INTEGER DEFAULT 0,
    downtime_hours INTEGER DEFAULT 0,
    mean_time_between_failures INTEGER, -- hours
    mean_time_to_repair INTEGER, -- hours

    -- Performance Metrics
    image_quality_score DECIMAL(3,2),
    patient_satisfaction_score DECIMAL(3,2),
    staff_satisfaction_score DECIMAL(3,2),
    throughput_efficiency DECIMAL(5,2),

    -- Safety and Compliance
    radiation_safety_checked BOOLEAN DEFAULT FALSE,
    last_radiation_survey_date DATE,
    safety_certifications JSONB,
    regulatory_compliance_status VARCHAR(50),

    -- Network and Integration
    network_configuration JSONB,
    dicom_configuration JSONB,
    pacs_connected BOOLEAN DEFAULT FALSE,
    ris_connected BOOLEAN DEFAULT FALSE,

    -- Dose Management (for CT/X-ray)
    dose_tracking_enabled BOOLEAN DEFAULT FALSE,
    dose_reference_levels JSONB,
    dose_optimization_features JSONB,

    -- AI and Advanced Features
    ai_features_available JSONB,
    advanced_processing_capabilities JSONB,
    automated_features JSONB,

    -- Cost and Financial
    acquisition_cost DECIMAL(12,2),
    annual_maintenance_cost DECIMAL(10,2),
    cost_per_study DECIMAL(8,2),
    roi_metrics JSONB,

    -- Staffing Requirements
    required_technologist_level VARCHAR(50),
    training_requirements JSONB,
    certification_needed JSONB,

    -- Research and Teaching
    research_capable BOOLEAN DEFAULT FALSE,
    teaching_features JSONB,
    research_protocols_supported JSONB,

    -- Environmental Impact
    power_consumption INTEGER, -- kW
    cooling_requirements JSONB,
    environmental_conditions JSONB,

    -- Future Planning
    upgrade_schedule JSONB,
    replacement_due_date DATE,
    lifecycle_stage VARCHAR(50), -- New, Mature, Legacy, End_of_Life

    -- Contact Information
    primary_contact_user_id UUID REFERENCES users(id),
    service_contact_info JSONB,
    vendor_support_info JSONB,

    -- Documentation
    manuals_location VARCHAR(500),
    training_materials_location VARCHAR(500),
    procedure_documents JSONB,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

    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)
);

Indexes:

CREATE INDEX idx_imaging_equipment_modality ON imaging_equipment(modality, operational_status);
CREATE INDEX idx_imaging_equipment_department ON imaging_equipment(department_id, is_active);
CREATE INDEX idx_imaging_equipment_status ON imaging_equipment(operational_status, availability_status);
CREATE INDEX idx_imaging_equipment_maintenance ON imaging_equipment(next_maintenance_due_date) WHERE operational_status = 'Active';
CREATE INDEX idx_imaging_equipment_qa ON imaging_equipment(next_qa_due_date) WHERE operational_status = 'Active';
CREATE INDEX idx_imaging_equipment_utilization ON imaging_equipment(average_utilization DESC);

BUSINESS RULES และ CONSTRAINTS

1. Order Management Rules

-- Business rule: Accession number must be unique and auto-generated
CREATE OR REPLACE FUNCTION generate_accession_number()
RETURNS TRIGGER AS $$
DECLARE
    new_accession VARCHAR(30);
    prefix VARCHAR(10);
BEGIN
    IF NEW.accession_number IS NULL THEN
        -- Generate accession number: YYYYMMDD + Sequential number
        prefix := TO_CHAR(CURRENT_DATE, 'YYYYMMDD');

        SELECT prefix || LPAD((COUNT(*) + 1)::TEXT, 4, '0')
        INTO new_accession
        FROM radiology_orders 
        WHERE DATE(created_at) = CURRENT_DATE;

        NEW.accession_number := new_accession;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_generate_accession_number
    BEFORE INSERT ON radiology_orders
    FOR EACH ROW
    EXECUTE FUNCTION generate_accession_number();

2. Critical Finding Notification Rules

-- Business rule: Critical findings must be notified within required timeframe
ALTER TABLE critical_findings 
ADD CONSTRAINT chk_notification_timeframe 
CHECK (
    (clinical_urgency = 'Immediate' AND 
     (primary_notification_timestamp IS NULL OR 
      primary_notification_timestamp <= discovered_at + INTERVAL '15 minutes')) OR
    (clinical_urgency = 'Within_1_hour' AND 
     (primary_notification_timestamp IS NULL OR 
      primary_notification_timestamp <= discovered_at + INTERVAL '1 hour')) OR
    (clinical_urgency = 'Within_4_hours' AND 
     (primary_notification_timestamp IS NULL OR 
      primary_notification_timestamp <= discovered_at + INTERVAL '4 hours'))
);

3. Radiation Exposure Tracking

-- Function to update cumulative radiation dose
CREATE OR REPLACE FUNCTION update_cumulative_dose()
RETURNS TRIGGER AS $$
BEGIN
    -- Update current year cumulative dose
    UPDATE radiation_exposure 
    SET cumulative_dose_current_year = (
        SELECT COALESCE(SUM(effective_dose), 0)
        FROM radiation_exposure re2
        WHERE re2.patient_id = NEW.patient_id 
        AND EXTRACT(YEAR FROM re2.exposure_date) = EXTRACT(YEAR FROM NEW.exposure_date)
    )
    WHERE patient_id = NEW.patient_id 
    AND EXTRACT(YEAR FROM exposure_date) = EXTRACT(YEAR FROM NEW.exposure_date);

    -- Update lifetime cumulative dose
    UPDATE radiation_exposure 
    SET cumulative_dose_lifetime = (
        SELECT COALESCE(SUM(effective_dose), 0)
        FROM radiation_exposure re2
        WHERE re2.patient_id = NEW.patient_id
    )
    WHERE patient_id = NEW.patient_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_cumulative_dose
    AFTER INSERT OR UPDATE ON radiation_exposure
    FOR EACH ROW
    EXECUTE FUNCTION update_cumulative_dose();

PERFORMANCE OPTIMIZATION STRATEGIES

1. Partitioning for Large Tables

-- Partition radiology_orders by date for performance
CREATE TABLE radiology_orders_2025 PARTITION OF radiology_orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Partition dicom_studies by date for performance  
CREATE TABLE dicom_studies_2025 PARTITION OF dicom_studies
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

2. Advanced Indexing

-- Composite index for common query patterns
CREATE INDEX idx_orders_patient_status_date ON radiology_orders(
    patient_id, order_status, ordered_at DESC
) WHERE is_active = TRUE;

-- Partial index for pending orders
CREATE INDEX idx_orders_pending ON radiology_orders(ordered_at DESC) 
WHERE order_status IN ('Ordered', 'Scheduled') AND is_active = TRUE;

-- Index for AI recommendation queries
CREATE INDEX idx_ai_recommendations_confidence ON ai_recommendations(
    radiology_order_id, confidence_score DESC, recommendation_type
) WHERE confidence_score >= 0.7;

INTEGRATION WITH EXTERNAL SYSTEMS

1. HL7 FHIR Compliance

ระบบรองรับการแลกเปลี่ยนข้อมูลผ่าน HL7 FHIR Resources:

DiagnosticRequest - คำสั่งตรวจรังสี
DiagnosticReport - รายงานผลการตรวจ
ImagingStudy - ข้อมูล DICOM Study
Patient - ข้อมูลผู้ป่วย

2. DICOM Integration

รองรับ DICOM Services ครบถ้วน:

C-STORE - รับภาพจากเครื่องตรวจ
C-FIND - ค้นหา Studies และ Series
C-MOVE - ดึงภาพสำหรับการแสดงผล
WADO - Web access สำหรับ browser-based viewer

SUMMARY

Database schema นี้ออกแบบมาเพื่อรองรับระบบรังสีวิทยาที่ครบถ้วน รวมถึง:

✅ CPOE AI Assist Integration - การแนะนำและตรวจสอบอัตโนมัติ
✅ DICOM Standard Compliance - รองรับมาตรฐาน DICOM 3.0
✅ RIS/PACS Integration - เชื่อมต่อระบบรังสีภายนอก
✅ Radiation Safety Tracking - ติดตามปริมาณรังสี
✅ Critical Finding Management - จัดการผลการตรวจฉุกเฉิน
✅ Comprehensive Audit Trail - การติดตาม compliance
✅ Performance Optimization - Strategic indexing และ partitioning

Schema นี้เป็นพื้นฐานที่มั่นคงสำหรับการพัฒนาระบบรังสีวิทยาระดับโรงพยาบาลจังหวัด ที่ทันสมัยและปลอดภัย

Database Schema - ระบบงานรังสีวิทยา (Radiology System with CPOE AI Assist)

Table of Contents

INTEGRATION WITH MASTER SCHEMA

1. ตาราง radiology_orders

2. ตาราง radiology_exams

3. ตาราง patient_screening

4. ตาราง contraindication_checks

5. ตาราง ai_recommendations

6. ตาราง dicom_studies

7. ตาราง dicom_series

8. ตาราง dicom_instances

9. ตาราง radiology_reports

10. ตาราง critical_findings

11. ตาราง radiation_exposure

12. ตาราง exam_protocols

13. ตาราง preparation_instructions

14. ตาราง imaging_equipment

BUSINESS RULES และ CONSTRAINTS

1. Order Management Rules

2. Critical Finding Notification Rules

3. Radiation Exposure Tracking

PERFORMANCE OPTIMIZATION STRATEGIES

1. Partitioning for Large Tables

2. Advanced Indexing

INTEGRATION WITH EXTERNAL SYSTEMS

1. HL7 FHIR Compliance

2. DICOM Integration

SUMMARY

1. ตาราง `radiology_orders`

2. ตาราง `radiology_exams`

3. ตาราง `patient_screening`

4. ตาราง `contraindication_checks`

5. ตาราง `ai_recommendations`

6. ตาราง `dicom_studies`

7. ตาราง `dicom_series`

8. ตาราง `dicom_instances`

9. ตาราง `radiology_reports`

10. ตาราง `critical_findings`

11. ตาราง `radiation_exposure`

12. ตาราง `exam_protocols`

13. ตาราง `preparation_instructions`

14. ตาราง `imaging_equipment`