ข้ามไปที่เนื้อหา

Database Schema - ระบบห้องปฏิบัติการกลาง (Central Laboratory System with CPOE AI Assist)

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 เพื่อให้มีการบูรณาการที่สมบูรณ์แบบระหว่างโมดูลต่างๆ และรองรับ AI Assist สำหรับการสั่งตรวจ

Table of Contents

  1. ตาราง lab_test_catalog
  2. ตาราง lab_panels
  3. ตาราง lab_orders
  4. ตาราง lab_order_items
  5. ตาราง specimens
  6. ตาราง specimen_tracking
  7. ตาราง lab_results
  8. ตาราง lab_instruments
  9. ตาราง instrument_connections
  10. ตาราง quality_controls
  11. ตาราง qc_results
  12. ตาราง critical_values
  13. ตาราง critical_value_notifications
  14. ตาราง outlab_providers
  15. ตาราง outlab_orders
  16. ตาราง lab_inventory
  17. ตาราง lab_reagents
  18. ตาราง cpoe_ai_suggestions
  19. ตาราง lab_worksheets
  20. ตาราง lab_reports

SHARED FOUNDATION TABLES (จาก Master Schema)

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

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

  1. patients - ข้อมูลผู้ป่วยหลัก (Master Patient Index)
  2. users - บุคลากรทางการแพทย์ (รวม laboratory staff, technicians, pathologists)
  3. medical_visits - ข้อมูลการมารับบริการ (Lab visits)
  4. medical_orders - คำสั่งทางการแพทย์จาก CPOE (รวม lab orders)
  5. departments - โครงสร้างแผนกโรงพยาบาล (รวม Lab departments)
  6. clinics - คลินิกต่างๆ ภายในแผนก
  7. appointments - ระบบนัดหมายหลัก (สำหรับ scheduled lab tests)
  8. queues - ระบบคิวหลัก (integrated กับ lab sample processing queues)
  9. audit_logs - บันทึกการตรวจสอบสำหรับ compliance
  10. digital_signatures - ลายเซ็นอิเล็กทรอนิกส์สำหรับ lab reports
  11. notifications - ระบบแจ้งเตือนแบบ Multi-channel (critical values, completed results)
  12. roles และ permissions - ระบบ RBAC สำหรับ lab permissions

Laboratory Specific Integration Points: - การเชื่อมโยงกับ medical_orders สำหรับ CPOE lab orders และ AI-assisted ordering - การใช้ appointments สำหรับ scheduled lab appointments และ specimen collection - Integration กับ notifications สำหรับ critical value alerts และ result notifications - รองรับ Digital signatures สำหรับ lab report verification และ compliance - การเชื่อมโยงกับ queues สำหรับ specimen processing workflow

Benefits: - ไม่มีการสร้างตารางซ้ำซ้อน - ข้อมูลสอดคล้องกันระหว่าง Clinical และ Laboratory modules - รองรับ Real-time lab result integration กับ EMR - ปฏิบัติตาม Laboratory compliance standards (CAP, ISO 15189) - รองรับ Laboratory Information System (LIS) integration - AI-assisted CPOE integration สำหรับ intelligent test ordering

LABORATORY CORE TABLES

1. ตาราง lab_test_catalog

ตารางหลักสำหรับเก็บข้อมูลรายการตรวจทางห้องปฏิบัติการทั้งหมด (Test Master)

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

    -- Test Identification
    test_code VARCHAR(20) UNIQUE NOT NULL,
    test_name_thai VARCHAR(200) NOT NULL,
    test_name_eng VARCHAR(200),
    test_name_short VARCHAR(50),

    -- Test Classification
    test_category VARCHAR(50) NOT NULL CHECK (test_category IN (
        'Clinical_Chemistry', 'Hematology', 'Microbiology', 'Immunology', 
        'Molecular_Diagnostics', 'Urinalysis', 'Special_Chemistry', 'Parasitology',
        'Blood_Bank', 'Coagulation', 'Serology'
    )),
    test_subcategory VARCHAR(50),

    -- Specimen Requirements
    specimen_types JSONB NOT NULL, -- ['blood', 'serum', 'plasma', 'urine']
    specimen_container VARCHAR(50), -- red_top, lavender_top, yellow_top
    specimen_volume_ml DECIMAL(6,3),
    specimen_volume_min_ml DECIMAL(6,3),

    -- Collection Requirements
    fasting_required BOOLEAN DEFAULT FALSE,
    special_preparation TEXT,
    collection_instructions_thai TEXT,
    collection_instructions_eng TEXT,

    -- Processing Information
    processing_department_id UUID REFERENCES departments(id),
    processing_location VARCHAR(100),
    methodology VARCHAR(100), -- colorimetric, enzymatic, immunoassay, PCR
    analyzer_compatibility JSONB, -- ['AU5800', 'XN9000', 'DxI800']

    -- Turnaround Time (TAT)
    tat_routine_hours INTEGER DEFAULT 24,
    tat_urgent_hours INTEGER DEFAULT 4,
    tat_stat_minutes INTEGER DEFAULT 60,

    -- Reference Values
    reference_range_adult_male VARCHAR(200),
    reference_range_adult_female VARCHAR(200),
    reference_range_child VARCHAR(200),
    reference_range_infant VARCHAR(200),
    reference_unit VARCHAR(50),

    -- Critical Values
    critical_low_value DECIMAL(12,4),
    critical_high_value DECIMAL(12,4),
    panic_low_value DECIMAL(12,4),
    panic_high_value DECIMAL(12,4),

    -- Result Information
    result_type VARCHAR(20) DEFAULT 'numeric' CHECK (result_type IN (
        'numeric', 'text', 'coded', 'range', 'interpretation'
    )),
    result_format VARCHAR(100), -- decimal places, format pattern
    normal_range_display VARCHAR(200),

    -- Clinical Information
    clinical_significance TEXT,
    interfering_substances TEXT,
    limitations TEXT,

    -- Quality Control
    qc_required BOOLEAN DEFAULT TRUE,
    qc_frequency_hours INTEGER DEFAULT 8,
    qc_levels INTEGER DEFAULT 2, -- normal, abnormal

    -- Cost and Billing
    cost_routine DECIMAL(8,2),
    cost_urgent DECIMAL(8,2),
    cost_stat DECIMAL(8,2),
    billing_code VARCHAR(50),

    -- External Lab Information
    available_inhouse BOOLEAN DEFAULT TRUE,
    external_lab_required BOOLEAN DEFAULT FALSE,
    external_lab_provider_id UUID, -- REFERENCES outlab_providers(id)
    external_lab_tat_days INTEGER,

    -- Regulatory and Compliance
    iso15189_scope BOOLEAN DEFAULT TRUE,
    cap_accredited BOOLEAN DEFAULT TRUE,
    regulatory_notes TEXT,

    -- AI/ML Configuration
    ai_interpretation_enabled BOOLEAN DEFAULT FALSE,
    ai_model_version VARCHAR(20),
    ai_confidence_threshold DECIMAL(3,2) DEFAULT 0.85,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id),
    deletion_reason TEXT,

    -- Ordering Information
    orderable BOOLEAN DEFAULT TRUE,
    requires_approval BOOLEAN DEFAULT FALSE,
    age_restrictions JSONB, -- minimum and maximum age restrictions
    gender_restrictions VARCHAR(10), -- M, F, null for both

    -- Integration Settings
    loinc_code VARCHAR(20),
    snomed_ct_code VARCHAR(50),
    icd10_codes JSONB,

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

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            test_name_thai || ' ' || COALESCE(test_name_eng, '') || ' ' ||
            test_code || ' ' || COALESCE(test_name_short, '') || ' ' ||
            COALESCE(test_category, '') || ' ' || COALESCE(methodology, '')
        )
    ) STORED
);

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

Field Type Constraints คำอธิบาย
id UUID PRIMARY KEY รหัสเฉพาะของข้อมูล
test_code VARCHAR(20) UNIQUE, NOT NULL รหัสการตรวจ (Lab Code) เช่น CBC, BUN, Cr
test_name_thai VARCHAR(200) NOT NULL ชื่อการตรวจภาษาไทย
test_name_eng VARCHAR(200) ชื่อการตรวจภาษาอังกฤษ
test_category VARCHAR(50) NOT NULL, CHECK หมวดหมู่การตรวจ (Clinical Chemistry, Hematology, etc.)
specimen_types JSONB NOT NULL ประเภทตัวอย่างที่ต้องการ
specimen_container VARCHAR(50) ประเภทหลอดเก็บตัวอย่าง
specimen_volume_ml DECIMAL(6,3) ปริมาณตัวอย่างที่ต้องการ (มิลลิลิตร)
fasting_required BOOLEAN DEFAULT FALSE ต้องการงดอาหารหรือไม่
processing_department_id UUID FK แผนกที่ทำการตรวจ
methodology VARCHAR(100) วิธีการตรวจ (colorimetric, enzymatic, etc.)
tat_routine_hours INTEGER DEFAULT 24 เวลาการรายงานผลปกติ (ชั่วโมง)
tat_urgent_hours INTEGER DEFAULT 4 เวลาการรายงานผลด่วน (ชั่วโมง)
tat_stat_minutes INTEGER DEFAULT 60 เวลาการรายงานผล STAT (นาที)
reference_range_adult_male VARCHAR(200) ช่วงค่าปกติผู้ชายวัยผู้ใหญ่
reference_range_adult_female VARCHAR(200) ช่วงค่าปกติผู้หญิงวัยผู้ใหญ่
critical_low_value DECIMAL(12,4) ค่าต่ำที่อันตราย
critical_high_value DECIMAL(12,4) ค่าสูงที่อันตราย
result_type VARCHAR(20) CHECK ประเภทผลตรวจ (numeric, text, coded)
cost_routine DECIMAL(8,2) ค่าใช้จ่ายการตรวจปกติ
external_lab_required BOOLEAN DEFAULT FALSE ต้องส่งตรวจนอกหรือไม่
loinc_code VARCHAR(20) รหัส LOINC สำหรับมาตรฐานสากล

2. ตาราง lab_panels

ตารางสำหรับเก็บข้อมูลชุดการตรวจ (Lab Panel/Profile) ที่รวมการตรวจหลายรายการ

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

    -- Panel Identification
    panel_code VARCHAR(20) UNIQUE NOT NULL,
    panel_name_thai VARCHAR(200) NOT NULL,
    panel_name_eng VARCHAR(200),
    panel_description TEXT,

    -- Panel Classification
    panel_category VARCHAR(50) NOT NULL, -- health_checkup, pre_operative, cardiac_marker
    panel_type VARCHAR(20) DEFAULT 'standard' CHECK (panel_type IN ('standard', 'protocol', 'custom')),

    -- Clinical Information
    clinical_indication TEXT,
    target_population VARCHAR(100), -- adult, pediatric, geriatric, pregnant

    -- Cost Information
    panel_cost DECIMAL(10,2),
    discount_percentage DECIMAL(5,2) DEFAULT 0,

    -- Ordering Information
    orderable BOOLEAN DEFAULT TRUE,
    requires_approval BOOLEAN DEFAULT FALSE,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

-- Panel items (many-to-many relationship with lab_test_catalog)
CREATE TABLE lab_panel_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    panel_id UUID NOT NULL REFERENCES lab_panels(id) ON DELETE CASCADE,
    test_id UUID NOT NULL REFERENCES lab_test_catalog(id) ON DELETE CASCADE,

    -- Sequencing and Priority
    sequence_order INTEGER DEFAULT 1,
    is_required BOOLEAN DEFAULT TRUE,
    is_reflex_test BOOLEAN DEFAULT FALSE, -- reflex testing condition
    reflex_condition TEXT,

    -- Cost Override
    item_cost DECIMAL(8,2), -- override individual test cost in panel

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(panel_id, test_id)
);

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

Field Type Constraints คำอธิบาย
panel_code VARCHAR(20) UNIQUE, NOT NULL รหัสชุดการตรวจ เช่น LFT, RFT, LIPID
panel_name_thai VARCHAR(200) NOT NULL ชื่อชุดการตรวจภาษาไทย
panel_category VARCHAR(50) NOT NULL หมวดหมู่ชุดการตรวจ
panel_type VARCHAR(20) CHECK ประเภทชุดการตรวจ (standard, protocol, custom)
target_population VARCHAR(100) กลุ่มเป้าหมาย (adult, pediatric, etc.)
panel_cost DECIMAL(10,2) ราคาชุดการตรวจ
sequence_order INTEGER DEFAULT 1 ลำดับการตรวจในชุด
is_reflex_test BOOLEAN DEFAULT FALSE เป็นการตรวจเพิ่มเติมตามเงื่อนไขหรือไม่

3. ตาราง lab_orders

ตารางสำหรับเก็บข้อมูลคำสั่งตรวจทางห้องปฏิบัติการจากระบบ CPOE

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

    -- Order Identification
    lab_order_number VARCHAR(20) UNIQUE NOT NULL,
    medical_order_id UUID NOT NULL REFERENCES medical_orders(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Ordering Information
    ordered_by_user_id UUID NOT NULL REFERENCES users(id),
    ordering_department_id UUID REFERENCES departments(id),

    -- Order Details
    order_date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    priority VARCHAR(20) DEFAULT 'routine' CHECK (priority IN ('stat', 'urgent', 'routine', 'timed')),

    -- Clinical Information
    clinical_indication TEXT,
    clinical_notes TEXT,
    provisional_diagnosis TEXT,
    icd10_codes JSONB,

    -- AI CPOE Integration
    ai_parsed_from_soap TEXT, -- Original SOAP note text that was parsed
    ai_suggestions JSONB, -- AI-generated test suggestions
    ai_confidence_score DECIMAL(3,2), -- 0.00 to 1.00
    manual_override BOOLEAN DEFAULT FALSE,

    -- Patient Preparation
    patient_preparation_required BOOLEAN DEFAULT FALSE,
    preparation_instructions TEXT,
    fasting_required BOOLEAN DEFAULT FALSE,
    fasting_hours INTEGER,
    medication_hold_required BOOLEAN DEFAULT FALSE,
    medications_to_hold TEXT,

    -- Collection Information
    preferred_collection_date DATE,
    preferred_collection_time TIME,
    collection_location VARCHAR(100),
    special_collection_instructions TEXT,

    -- Status Management
    order_status VARCHAR(30) DEFAULT 'ordered' CHECK (order_status IN (
        'ordered', 'specimen_required', 'specimen_collected', 'specimen_received', 
        'in_process', 'resulted', 'verified', 'reported', 'cancelled', 'rejected'
    )),

    -- Cancellation/Rejection
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by_user_id UUID REFERENCES users(id),
    cancellation_reason TEXT,
    rejected_at TIMESTAMP WITH TIME ZONE,
    rejected_by_user_id UUID REFERENCES users(id),
    rejection_reason TEXT,

    -- Verification and Authorization
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by_user_id UUID REFERENCES users(id),
    requires_authorization BOOLEAN DEFAULT FALSE,
    authorized_by_user_id UUID REFERENCES users(id),
    authorized_at TIMESTAMP WITH TIME ZONE,

    -- Financial Information
    total_cost DECIMAL(10,2),
    insurance_type VARCHAR(20),
    copayment_amount DECIMAL(8,2),

    -- External Laboratory
    external_lab_required BOOLEAN DEFAULT FALSE,
    external_lab_provider_id UUID, -- REFERENCES outlab_providers(id)
    sent_to_external BOOLEAN DEFAULT FALSE,
    sent_to_external_at TIMESTAMP WITH TIME ZONE,

    -- Workflow Timestamps
    specimen_collected_at TIMESTAMP WITH TIME ZONE,
    specimen_received_at TIMESTAMP WITH TIME ZONE,
    processing_started_at TIMESTAMP WITH TIME ZONE,
    first_result_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    reported_at TIMESTAMP WITH TIME ZONE,

    -- Performance Metrics
    tat_minutes INTEGER, -- actual turnaround time
    collection_to_result_minutes INTEGER,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
lab_order_number VARCHAR(20) UNIQUE, NOT NULL หมายเลขคำสั่งตรวจห้องปฏิบัติการ
medical_order_id UUID NOT NULL, FK รหัสคำสั่งทางการแพทย์จาก CPOE
priority VARCHAR(20) CHECK ความเร่งด่วน (stat, urgent, routine)
ai_parsed_from_soap TEXT ข้อความจาก SOAP note ที่ AI แปลง
ai_confidence_score DECIMAL(3,2) คะแนนความมั่นใจของ AI (0-1)
order_status VARCHAR(30) CHECK สถานะคำสั่งตรวจ
fasting_required BOOLEAN DEFAULT FALSE ต้องงดอาหารหรือไม่
external_lab_required BOOLEAN DEFAULT FALSE ต้องส่งตรวจภายนอกหรือไม่
tat_minutes INTEGER เวลา TAT จริง (นาที)

4. ตาราง lab_order_items

ตารางสำหรับเก็บรายการตรวจในแต่ละคำสั่งตรวจ (One-to-Many กับ lab_orders)

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

    -- Order Reference
    lab_order_id UUID NOT NULL REFERENCES lab_orders(id) ON DELETE CASCADE,

    -- Test Information
    test_id UUID REFERENCES lab_test_catalog(id),
    panel_id UUID REFERENCES lab_panels(id),

    -- Item Details
    test_code VARCHAR(20) NOT NULL,
    test_name VARCHAR(200) NOT NULL,
    test_category VARCHAR(50),

    -- Specimen Requirements (can override catalog defaults)
    specimen_type VARCHAR(50),
    specimen_container VARCHAR(50),
    specimen_volume_ml DECIMAL(6,3),

    -- Processing Information
    processing_department_id UUID REFERENCES departments(id),
    assigned_to_user_id UUID REFERENCES users(id),

    -- Priority and Timing
    item_priority VARCHAR(20) DEFAULT 'routine',
    sequence_order INTEGER DEFAULT 1,

    -- Collection Information
    collection_required BOOLEAN DEFAULT TRUE,
    collection_date_time TIMESTAMP WITH TIME ZONE,
    collected_by_user_id UUID REFERENCES users(id),
    collection_site VARCHAR(100),

    -- Status Management
    item_status VARCHAR(30) DEFAULT 'ordered' CHECK (item_status IN (
        'ordered', 'specimen_collected', 'specimen_received', 'in_process', 
        'resulted', 'verified', 'reported', 'cancelled', 'rejected'
    )),

    -- Result Information
    result_available BOOLEAN DEFAULT FALSE,
    result_value VARCHAR(500),
    result_unit VARCHAR(50),
    result_flag VARCHAR(10), -- H, L, HH, LL, A (abnormal)
    reference_range VARCHAR(200),

    -- Critical Values
    is_critical_value BOOLEAN DEFAULT FALSE,
    critical_value_notified BOOLEAN DEFAULT FALSE,
    critical_notification_time TIMESTAMP WITH TIME ZONE,

    -- Quality Control
    qc_passed BOOLEAN DEFAULT TRUE,
    qc_notes TEXT,

    -- Verification
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by_user_id UUID REFERENCES users(id),
    verification_notes TEXT,

    -- Cost Information
    item_cost DECIMAL(8,2),

    -- External Lab
    external_lab_item BOOLEAN DEFAULT FALSE,
    external_result_received BOOLEAN DEFAULT FALSE,
    external_result_file_path VARCHAR(500),

    -- Timestamps for workflow
    received_in_lab_at TIMESTAMP WITH TIME ZONE,
    analysis_started_at TIMESTAMP WITH TIME ZONE,
    analysis_completed_at TIMESTAMP WITH TIME ZONE,
    reported_at TIMESTAMP WITH TIME ZONE,

    -- Performance Tracking
    item_tat_minutes INTEGER,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

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

Field Type Constraints คำอธิบาย
lab_order_id UUID NOT NULL, FK รหัสคำสั่งตรวจหลัก
test_id UUID FK รหัสการตรวจจาก catalog
panel_id UUID FK รหัสชุดการตรวจ (ถ้ามี)
item_status VARCHAR(30) CHECK สถานะของรายการตรวจนี้
result_value VARCHAR(500) ผลการตรวจ
result_flag VARCHAR(10) แฟล็กผลตรวจ (H=High, L=Low)
is_critical_value BOOLEAN DEFAULT FALSE เป็นค่า Critical หรือไม่
item_tat_minutes INTEGER เวลา TAT ของรายการนี้

5. ตาราง specimens

ตารางสำหรับเก็บข้อมูลตัวอย่างที่เก็บจากผู้ป่วย

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

    -- Specimen Identification
    specimen_barcode VARCHAR(50) UNIQUE NOT NULL,
    specimen_number VARCHAR(20) UNIQUE NOT NULL,

    -- Order Reference
    lab_order_id UUID NOT NULL REFERENCES lab_orders(id),
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),

    -- Specimen Information
    specimen_type VARCHAR(50) NOT NULL, -- blood, serum, plasma, urine, stool, csf
    specimen_source VARCHAR(100), -- venipuncture, arterial, capillary, catheter
    specimen_container VARCHAR(50), -- red_top, lavender_top, yellow_top, urine_cup
    specimen_volume_ml DECIMAL(6,3),
    specimen_condition VARCHAR(50), -- fresh, frozen, refrigerated

    -- Collection Details
    collection_date_time TIMESTAMP WITH TIME ZONE NOT NULL,
    collected_by_user_id UUID NOT NULL REFERENCES users(id),
    collection_site VARCHAR(100), -- left_arm, right_arm, finger_stick
    collection_method VARCHAR(50), -- venipuncture, capillary, clean_catch
    collection_location VARCHAR(100), -- OPD, IPD, ER, home_care

    -- Patient Condition at Collection
    patient_fasting BOOLEAN DEFAULT FALSE,
    fasting_hours INTEGER,
    patient_position VARCHAR(20), -- sitting, standing, supine
    tourniquet_time_seconds INTEGER,
    collection_attempts INTEGER DEFAULT 1,

    -- Specimen Quality Assessment
    specimen_quality VARCHAR(20) DEFAULT 'acceptable' CHECK (specimen_quality IN (
        'acceptable', 'suboptimal', 'unacceptable'
    )),
    quality_issues JSONB, -- ['hemolyzed', 'lipemic', 'icteric', 'clotted', 'insufficient_volume']
    quality_assessment_by UUID REFERENCES users(id),
    quality_assessment_at TIMESTAMP WITH TIME ZONE,

    -- Chain of Custody
    received_in_lab_at TIMESTAMP WITH TIME ZONE,
    received_by_user_id UUID REFERENCES users(id),
    temperature_on_receipt DECIMAL(4,1), -- Celsius
    transport_conditions VARCHAR(100),

    -- Processing Information
    processing_priority VARCHAR(20) DEFAULT 'routine',
    centrifuged BOOLEAN DEFAULT FALSE,
    centrifuged_at TIMESTAMP WITH TIME ZONE,
    aliquoted BOOLEAN DEFAULT FALSE,
    aliquot_count INTEGER DEFAULT 0,

    -- Storage Information
    storage_location VARCHAR(100),
    storage_temperature DECIMAL(4,1),
    storage_conditions VARCHAR(100), -- refrigerated, frozen, room_temp
    storage_expiry TIMESTAMP WITH TIME ZONE,

    -- Distribution to Departments
    distributed_to_departments JSONB, -- [{'dept': 'chemistry', 'volume': 2.5, 'time': '...'}]

    -- Status Management
    specimen_status VARCHAR(30) DEFAULT 'collected' CHECK (specimen_status IN (
        'collected', 'in_transit', 'received', 'processed', 'stored', 
        'distributed', 'consumed', 'discarded', 'rejected'
    )),

    -- Rejection Information
    rejected BOOLEAN DEFAULT FALSE,
    rejection_reason TEXT,
    rejected_by_user_id UUID REFERENCES users(id),
    rejected_at TIMESTAMP WITH TIME ZONE,

    -- Comments and Notes
    collection_notes TEXT,
    lab_notes TEXT,
    special_handling_instructions TEXT,

    -- Compliance and Safety
    biohazard_level INTEGER DEFAULT 1 CHECK (biohazard_level BETWEEN 1 AND 4),
    isolation_precautions BOOLEAN DEFAULT FALSE,

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

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

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

Field Type Constraints คำอธิบาย
specimen_barcode VARCHAR(50) UNIQUE, NOT NULL บาร์โค้ดตัวอย่าง
specimen_type VARCHAR(50) NOT NULL ประเภทตัวอย่าง (blood, urine, etc.)
collection_date_time TIMESTAMP NOT NULL วันเวลาที่เก็บตัวอย่าง
collected_by_user_id UUID NOT NULL, FK ผู้เก็บตัวอย่าง
specimen_quality VARCHAR(20) CHECK คุณภาพตัวอย่าง
quality_issues JSONB ปัญหาคุณภาพตัวอย่าง
specimen_status VARCHAR(30) CHECK สถานะตัวอย่าง
storage_location VARCHAR(100) ตำแหน่งเก็บตัวอย่าง
biohazard_level INTEGER CHECK ระดับความอันตราย (1-4)

6. ตาราง specimen_tracking

ตารางสำหรับติดตามการเคลื่อนไหวของตัวอย่างตลอดกระบวนการ

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

    -- Specimen Reference
    specimen_id UUID NOT NULL REFERENCES specimens(id) ON DELETE CASCADE,

    -- Tracking Information
    tracking_event VARCHAR(50) NOT NULL CHECK (tracking_event IN (
        'collected', 'labeled', 'transported', 'received_lab', 'quality_checked', 
        'centrifuged', 'aliquoted', 'distributed', 'analyzed', 'stored', 'discarded'
    )),

    -- Event Details
    event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    performed_by_user_id UUID NOT NULL REFERENCES users(id),
    location VARCHAR(100),
    department_id UUID REFERENCES departments(id),

    -- Event-specific Data
    event_details JSONB, -- specific details for each event type
    temperature DECIMAL(4,1),
    volume_before_ml DECIMAL(6,3),
    volume_after_ml DECIMAL(6,3),

    -- Equipment Used
    equipment_id VARCHAR(50),
    equipment_name VARCHAR(100),

    -- Quality Information
    quality_check_passed BOOLEAN DEFAULT TRUE,
    quality_issues TEXT,

    -- Comments
    notes TEXT,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    -- Timestamp
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

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

Field Type Constraints คำอธิบาย
specimen_id UUID NOT NULL, FK รหัสตัวอย่างที่ติดตาม
tracking_event VARCHAR(50) NOT NULL, CHECK เหตุการณ์ที่เกิดขึ้น
event_timestamp TIMESTAMP DEFAULT NOW() เวลาที่เกิดเหตุการณ์
performed_by_user_id UUID NOT NULL, FK ผู้ที่ดำเนินการ
event_details JSONB รายละเอียดเพิ่มเติมของเหตุการณ์
volume_before_ml DECIMAL(6,3) ปริมาตรก่อนการดำเนินการ
volume_after_ml DECIMAL(6,3) ปริมาตรหลังการดำเนินการ

7. ตาราง lab_results

ตารางสำหรับเก็บผลการตรวจทางห้องปฏิบัติการ

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

    -- Order and Item Reference
    lab_order_id UUID NOT NULL REFERENCES lab_orders(id),
    lab_order_item_id UUID NOT NULL REFERENCES lab_order_items(id),
    patient_id UUID NOT NULL REFERENCES patients(id),
    specimen_id UUID REFERENCES specimens(id),

    -- Test Information
    test_id UUID NOT NULL REFERENCES lab_test_catalog(id),
    test_code VARCHAR(20) NOT NULL,
    test_name VARCHAR(200) NOT NULL,

    -- Result Data
    result_value_numeric DECIMAL(15,6),
    result_value_text TEXT,
    result_unit VARCHAR(50),
    result_status VARCHAR(20) DEFAULT 'preliminary' CHECK (result_status IN (
        'preliminary', 'final', 'corrected', 'cancelled', 'entered_in_error'
    )),

    -- Reference Values (at time of result)
    reference_range VARCHAR(200),
    reference_low DECIMAL(15,6),
    reference_high DECIMAL(15,6),

    -- Result Interpretation
    abnormal_flag VARCHAR(10), -- N=Normal, H=High, L=Low, HH=Critical High, LL=Critical Low, A=Abnormal
    result_interpretation TEXT,
    clinical_significance TEXT,

    -- Critical Values
    is_critical_value BOOLEAN DEFAULT FALSE,
    is_panic_value BOOLEAN DEFAULT FALSE,
    critical_value_called BOOLEAN DEFAULT FALSE,
    critical_call_time TIMESTAMP WITH TIME ZONE,
    critical_called_to_user_id UUID REFERENCES users(id),
    critical_callback_received BOOLEAN DEFAULT FALSE,
    critical_callback_time TIMESTAMP WITH TIME ZONE,

    -- Delta Checks
    delta_check_performed BOOLEAN DEFAULT FALSE,
    delta_check_result VARCHAR(20), -- normal, flagged, critical
    delta_percentage DECIMAL(6,2),
    previous_result_value DECIMAL(15,6),
    previous_result_date TIMESTAMP WITH TIME ZONE,

    -- Analysis Information
    analyzer_id VARCHAR(50),
    analyzer_name VARCHAR(100),
    test_run_id VARCHAR(50),
    analysis_date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Methodology and Calibration
    methodology VARCHAR(100),
    reagent_lot VARCHAR(50),
    calibrator_lot VARCHAR(50),

    -- Quality Control Association
    qc_batch_id VARCHAR(50),
    qc_status VARCHAR(20) DEFAULT 'passed',
    qc_comments TEXT,

    -- Technical Information
    dilution_factor DECIMAL(6,2) DEFAULT 1.0,
    repeat_analysis BOOLEAN DEFAULT FALSE,
    repeat_reason TEXT,
    measurement_uncertainty DECIMAL(8,4),

    -- Verification and Authorization
    technologist_id UUID REFERENCES users(id),
    verified_by_user_id UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    authorized_by_user_id UUID REFERENCES users(id),
    authorized_at TIMESTAMP WITH TIME ZONE,

    -- Comments and Notes
    technical_comments TEXT,
    interpretation_comments TEXT,
    lab_notes TEXT,

    -- External Lab Results
    external_lab_result BOOLEAN DEFAULT FALSE,
    external_lab_name VARCHAR(200),
    external_result_file_path VARCHAR(500),
    external_received_date TIMESTAMP WITH TIME ZONE,

    -- Result History and Corrections
    corrected_from_result_id UUID REFERENCES lab_results(id),
    correction_reason TEXT,
    version_number INTEGER DEFAULT 1,

    -- Performance Metrics
    analysis_duration_minutes INTEGER,
    result_to_verification_minutes INTEGER,

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

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

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('simple', 
            test_code || ' ' || test_name || ' ' || 
            COALESCE(result_value_text, '') || ' ' || COALESCE(result_unit, '') || ' ' ||
            COALESCE(abnormal_flag, '')
        )
    ) STORED
);

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

Field Type Constraints คำอธิบาย
result_value_numeric DECIMAL(15,6) ผลตรวจเป็นตัวเลข
result_value_text TEXT ผลตรวจเป็นข้อความ
result_status VARCHAR(20) CHECK สถานะผลตรวจ (preliminary, final)
abnormal_flag VARCHAR(10) แฟล็กผิดปกติ (N, H, L, HH, LL, A)
is_critical_value BOOLEAN DEFAULT FALSE เป็นค่า Critical หรือไม่
critical_value_called BOOLEAN DEFAULT FALSE โทรแจ้งค่า Critical แล้วหรือไม่
delta_check_performed BOOLEAN DEFAULT FALSE ตรวจสอบ Delta check แล้วหรือไม่
analyzer_id VARCHAR(50) รหัสเครื่องมือที่ใช้ตรวจ
external_lab_result BOOLEAN DEFAULT FALSE เป็นผลจาก Lab ภายนอกหรือไม่

8. ตาราง lab_instruments

ตารางสำหรับเก็บข้อมูลเครื่องมือห้องปฏิบัติการ

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

    -- Instrument Identification
    instrument_id VARCHAR(50) UNIQUE NOT NULL,
    instrument_name VARCHAR(200) NOT NULL,
    instrument_model VARCHAR(100),
    serial_number VARCHAR(100),
    manufacturer VARCHAR(100),

    -- Location and Department
    department_id UUID NOT NULL REFERENCES departments(id),
    location VARCHAR(100),
    room_number VARCHAR(20),

    -- Instrument Classification
    instrument_type VARCHAR(50) NOT NULL CHECK (instrument_type IN (
        'chemistry_analyzer', 'hematology_analyzer', 'coagulation_analyzer',
        'immunoassay_analyzer', 'microbiology_analyzer', 'molecular_analyzer',
        'urinalysis_analyzer', 'blood_gas_analyzer', 'microscope', 'centrifuge'
    )),
    analyzer_category VARCHAR(50), -- high_throughput, medium_throughput, point_of_care

    -- Technical Specifications
    test_capabilities JSONB, -- list of tests this instrument can perform
    sample_throughput_per_hour INTEGER,
    sample_types JSONB, -- ['serum', 'plasma', 'whole_blood', 'urine']

    -- Communication and Integration
    lis_interface_available BOOLEAN DEFAULT FALSE,
    communication_protocol VARCHAR(50), -- HL7, ASTM, TCP/IP, Serial
    interface_type VARCHAR(30), -- bidirectional, unidirectional, manual
    ip_address INET,
    port_number INTEGER,

    -- Software Information
    software_version VARCHAR(50),
    firmware_version VARCHAR(50),
    last_software_update DATE,

    -- Operational Status
    operational_status VARCHAR(20) DEFAULT 'operational' CHECK (operational_status IN (
        'operational', 'maintenance', 'calibration', 'down', 'retired'
    )),

    -- Maintenance Information
    installation_date DATE,
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    maintenance_frequency_days INTEGER DEFAULT 90,
    service_contract_vendor VARCHAR(200),
    service_contract_expiry DATE,

    -- Quality Control Requirements
    qc_frequency_hours INTEGER DEFAULT 8,
    qc_levels_required INTEGER DEFAULT 2,
    calibration_frequency_days INTEGER DEFAULT 30,

    -- Performance Metrics
    uptime_percentage DECIMAL(5,2),
    monthly_test_volume INTEGER,
    error_rate_percentage DECIMAL(5,2),

    -- Regulatory Compliance
    fda_approved BOOLEAN DEFAULT TRUE,
    ce_marked BOOLEAN DEFAULT TRUE,
    thai_fda_registered BOOLEAN DEFAULT TRUE,
    registration_number VARCHAR(50),

    -- Environmental Requirements
    operating_temperature_min DECIMAL(4,1),
    operating_temperature_max DECIMAL(4,1),
    operating_humidity_min DECIMAL(4,1),
    operating_humidity_max DECIMAL(4,1),

    -- Cost Information
    purchase_cost DECIMAL(12,2),
    annual_service_cost DECIMAL(10,2),
    cost_per_test DECIMAL(8,4),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
instrument_id VARCHAR(50) UNIQUE, NOT NULL รหัสเครื่องมือ
instrument_name VARCHAR(200) NOT NULL ชื่อเครื่องมือ
instrument_type VARCHAR(50) NOT NULL, CHECK ประเภทเครื่องมือ
test_capabilities JSONB รายการตรวจที่สามารถทำได้
lis_interface_available BOOLEAN DEFAULT FALSE มีการเชื่อมต่อ LIS หรือไม่
communication_protocol VARCHAR(50) โปรโตคอลการสื่อสาร
operational_status VARCHAR(20) CHECK สถานะการใช้งาน
qc_frequency_hours INTEGER DEFAULT 8 ความถี่ QC (ชั่วโมง)

9. ตาราง instrument_connections

ตารางสำหรับจัดการการเชื่อมต่อระหว่างเครื่องมือกับระบบ LIS

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

    -- Instrument Reference
    instrument_id UUID NOT NULL REFERENCES lab_instruments(id),

    -- Connection Configuration
    connection_name VARCHAR(100) NOT NULL,
    connection_type VARCHAR(30) NOT NULL CHECK (connection_type IN (
        'tcp_ip', 'serial', 'file_transfer', 'web_service', 'direct_database'
    )),

    -- Network Configuration
    host_address VARCHAR(255),
    port_number INTEGER,
    protocol VARCHAR(20), -- HL7, ASTM, POCT1-A, Custom

    -- Authentication
    authentication_required BOOLEAN DEFAULT FALSE,
    username VARCHAR(100),
    password_hash VARCHAR(255),

    -- Data Exchange Configuration
    data_direction VARCHAR(20) CHECK (data_direction IN ('bidirectional', 'send_only', 'receive_only')),
    message_format VARCHAR(50), -- HL7v2.5, ASTM_E1381, JSON, XML
    encoding VARCHAR(20) DEFAULT 'UTF-8',

    -- Connection Settings
    connection_timeout_seconds INTEGER DEFAULT 30,
    retry_attempts INTEGER DEFAULT 3,
    retry_interval_seconds INTEGER DEFAULT 60,
    heartbeat_interval_seconds INTEGER DEFAULT 300,

    -- Data Mapping
    test_code_mapping JSONB, -- mapping between instrument codes and lab test codes
    unit_conversion_rules JSONB, -- unit conversion rules if needed

    -- Status and Monitoring
    connection_status VARCHAR(20) DEFAULT 'inactive' CHECK (connection_status IN (
        'active', 'inactive', 'error', 'maintenance'
    )),
    last_successful_connection TIMESTAMP WITH TIME ZONE,
    last_error_message TEXT,
    last_error_time TIMESTAMP WITH TIME ZONE,

    -- Performance Metrics
    total_messages_sent INTEGER DEFAULT 0,
    total_messages_received INTEGER DEFAULT 0,
    failed_messages INTEGER DEFAULT 0,
    average_response_time_ms DECIMAL(8,2),

    -- Quality Control Integration
    qc_result_auto_import BOOLEAN DEFAULT TRUE,
    patient_result_auto_import BOOLEAN DEFAULT TRUE,
    auto_verification_enabled BOOLEAN DEFAULT FALSE,

    -- Error Handling
    error_notification_enabled BOOLEAN DEFAULT TRUE,
    error_notification_recipients JSONB, -- list of user IDs to notify

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

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

Field Type Constraints คำอธิบาย
connection_type VARCHAR(30) NOT NULL, CHECK ประเภทการเชื่อมต่อ
data_direction VARCHAR(20) CHECK ทิศทางข้อมูล
test_code_mapping JSONB การแมปรหัสตรวจ
connection_status VARCHAR(20) CHECK สถานะการเชื่อมต่อ
auto_verification_enabled BOOLEAN DEFAULT FALSE เปิดการตรวจสอบอัตโนมัติหรือไม่

10. ตาราง quality_controls

ตารางสำหรับจัดการข้อมูล Quality Control Materials และ Calibrators

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

    -- QC Material Identification
    qc_material_code VARCHAR(50) UNIQUE NOT NULL,
    qc_material_name VARCHAR(200) NOT NULL,
    manufacturer VARCHAR(100),
    catalog_number VARCHAR(50),

    -- QC Classification
    qc_type VARCHAR(30) NOT NULL CHECK (qc_type IN (
        'normal', 'abnormal', 'pathological', 'calibrator', 'blank', 'linearity'
    )),
    qc_level VARCHAR(20) NOT NULL, -- Level 1, Level 2, Level 3, etc.

    -- Applicable Tests
    applicable_test_categories JSONB, -- ['chemistry', 'hematology', 'immunology']
    applicable_tests JSONB, -- specific test codes this QC applies to
    applicable_instruments JSONB, -- instrument IDs this QC is used on

    -- Lot Information
    lot_number VARCHAR(50) NOT NULL,
    expiry_date DATE NOT NULL,
    opening_date DATE,
    stability_after_opening_days INTEGER,
    storage_temperature_min DECIMAL(4,1),
    storage_temperature_max DECIMAL(4,1),
    storage_conditions TEXT,

    -- Target Values and Ranges
    target_values JSONB, -- {test_code: {mean: value, sd: value, cv: value}}
    acceptable_ranges JSONB, -- {test_code: {min: value, max: value}}

    -- QC Rules Configuration
    westgard_rules JSONB DEFAULT '["1-2s", "1-3s", "2-2s", "R-4s", "4-1s", "10-x"]',
    control_limits JSONB, -- custom control limits if different from standard

    -- Usage Information
    frequency_per_day INTEGER DEFAULT 2,
    required_before_patient_testing BOOLEAN DEFAULT TRUE,
    required_after_calibration BOOLEAN DEFAULT TRUE,
    required_after_maintenance BOOLEAN DEFAULT TRUE,

    -- Cost Information
    cost_per_vial DECIMAL(8,2),
    volume_per_vial_ml DECIMAL(6,2),

    -- Supplier Information
    supplier_name VARCHAR(200),
    supplier_contact TEXT,

    -- Status
    qc_status VARCHAR(20) DEFAULT 'active' CHECK (qc_status IN (
        'active', 'expired', 'recalled', 'discontinued'
    )),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
qc_material_code VARCHAR(50) UNIQUE, NOT NULL รหัสวัสดุ QC
qc_type VARCHAR(30) NOT NULL, CHECK ประเภท QC (normal, abnormal, etc.)
qc_level VARCHAR(20) NOT NULL ระดับ QC (Level 1, 2, 3)
target_values JSONB ค่าเป้าหมายสำหรับแต่ละการตรวจ
westgard_rules JSONB กฎ Westgard ที่ใช้
expiry_date DATE NOT NULL วันหมดอายุ

11. ตาราง qc_results

ตารางสำหรับเก็บผล Quality Control

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

    -- QC Reference
    qc_material_id UUID NOT NULL REFERENCES quality_controls(id),
    instrument_id UUID NOT NULL REFERENCES lab_instruments(id),

    -- Test Information
    test_id UUID NOT NULL REFERENCES lab_test_catalog(id),
    test_code VARCHAR(20) NOT NULL,
    test_name VARCHAR(200) NOT NULL,

    -- QC Run Information
    qc_run_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    qc_batch_number VARCHAR(50),
    operator_user_id UUID NOT NULL REFERENCES users(id),

    -- QC Result Data
    qc_result_value DECIMAL(15,6) NOT NULL,
    qc_result_unit VARCHAR(50),
    target_value DECIMAL(15,6),
    target_sd DECIMAL(15,6),
    target_cv DECIMAL(8,4), -- coefficient of variation

    -- Statistical Analysis
    z_score DECIMAL(8,4), -- (result - mean) / SD
    control_limit_1s BOOLEAN DEFAULT TRUE, -- within 1SD
    control_limit_2s BOOLEAN DEFAULT TRUE, -- within 2SD
    control_limit_3s BOOLEAN DEFAULT TRUE, -- within 3SD

    -- Westgard Rule Evaluation
    westgard_rule_violations JSONB, -- list of violated rules
    qc_status VARCHAR(20) DEFAULT 'acceptable' CHECK (qc_status IN (
        'acceptable', 'warning', 'unacceptable', 'rejected'
    )),

    -- Actions Taken
    action_required BOOLEAN DEFAULT FALSE,
    actions_taken TEXT,

    -- Review and Approval
    reviewed_by_user_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    review_comments TEXT,
    approved_for_patient_testing BOOLEAN DEFAULT TRUE,

    -- Repeat Analysis
    repeat_required BOOLEAN DEFAULT FALSE,
    repeat_reason TEXT,
    original_qc_result_id UUID REFERENCES qc_results(id),

    -- Environmental Conditions
    room_temperature DECIMAL(4,1),
    room_humidity DECIMAL(4,1),

    -- Technical Information
    reagent_lot VARCHAR(50),
    calibrator_lot VARCHAR(50),
    dilution_factor DECIMAL(6,2) DEFAULT 1.0,

    -- Comments
    technical_comments TEXT,
    supervisor_comments TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

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

Field Type Constraints คำอธิบาย
qc_result_value DECIMAL(15,6) NOT NULL ค่าผล QC
z_score DECIMAL(8,4) คะแนน Z-score
westgard_rule_violations JSONB กฎ Westgard ที่ละเมิด
qc_status VARCHAR(20) CHECK สถานะผล QC
approved_for_patient_testing BOOLEAN DEFAULT TRUE อนุมัติให้ตรวจผู้ป่วยได้หรือไม่

12. ตาราง critical_values

ตารางสำหรับกำหนด Critical Value Ranges สำหรับการตรวจแต่ละประเภท

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

    -- Test Reference
    test_id UUID NOT NULL REFERENCES lab_test_catalog(id),
    test_code VARCHAR(20) NOT NULL,
    test_name VARCHAR(200) NOT NULL,

    -- Critical Value Ranges
    critical_low_value DECIMAL(15,6),
    critical_high_value DECIMAL(15,6),
    panic_low_value DECIMAL(15,6),
    panic_high_value DECIMAL(15,6),

    -- Patient Demographics Specific
    patient_age_min INTEGER, -- minimum age in years
    patient_age_max INTEGER, -- maximum age in years
    patient_gender CHAR(1) CHECK (patient_gender IN ('M', 'F', 'U')),

    -- Clinical Context Specific
    patient_location VARCHAR(50), -- OPD, IPD, ER, ICU
    department_id UUID REFERENCES departments(id),

    -- Notification Requirements
    notification_required BOOLEAN DEFAULT TRUE,
    notification_urgency VARCHAR(20) DEFAULT 'urgent' CHECK (notification_urgency IN (
        'routine', 'urgent', 'immediate', 'stat'
    )),
    max_notification_time_minutes INTEGER DEFAULT 30,

    -- Notification Recipients
    primary_physician_required BOOLEAN DEFAULT TRUE,
    attending_physician_required BOOLEAN DEFAULT TRUE,
    nursing_staff_required BOOLEAN DEFAULT FALSE,
    laboratory_supervisor_required BOOLEAN DEFAULT TRUE,

    -- Escalation Rules
    escalation_enabled BOOLEAN DEFAULT TRUE,
    escalation_time_minutes INTEGER DEFAULT 15,
    escalation_recipients JSONB, -- user IDs for escalation

    -- Documentation Requirements
    callback_verification_required BOOLEAN DEFAULT TRUE,
    documentation_required BOOLEAN DEFAULT TRUE,
    read_back_required BOOLEAN DEFAULT TRUE,

    -- Clinical Guidelines
    clinical_action_recommended TEXT,
    follow_up_tests_recommended JSONB,

    -- Effective Period
    effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
    effective_until DATE,

    -- Approval and Review
    approved_by_user_id UUID NOT NULL REFERENCES users(id),
    approved_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    review_date DATE,
    review_frequency_months INTEGER DEFAULT 12,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
critical_low_value DECIMAL(15,6) ค่าต่ำที่อันตราย
critical_high_value DECIMAL(15,6) ค่าสูงที่อันตราย
panic_low_value DECIMAL(15,6) ค่าต่ำที่ panic
panic_high_value DECIMAL(15,6) ค่าสูงที่ panic
notification_urgency VARCHAR(20) CHECK ความเร่งด่วนการแจ้งเตือน
callback_verification_required BOOLEAN DEFAULT TRUE ต้องการ callback verification หรือไม่
escalation_enabled BOOLEAN DEFAULT TRUE เปิดใช้ escalation หรือไม่

13. ตาราง critical_value_notifications

ตารางสำหรับบันทึกการแจ้งเตือน Critical Values

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

    -- Result Reference
    lab_result_id UUID NOT NULL REFERENCES lab_results(id),
    critical_value_id UUID NOT NULL REFERENCES critical_values(id),

    -- Patient and Test Information
    patient_id UUID NOT NULL REFERENCES patients(id),
    test_code VARCHAR(20) NOT NULL,
    critical_value DECIMAL(15,6) NOT NULL,
    critical_threshold_type VARCHAR(20) CHECK (critical_threshold_type IN (
        'critical_low', 'critical_high', 'panic_low', 'panic_high'
    )),

    -- Notification Details
    notification_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    notification_method VARCHAR(30) CHECK (notification_method IN (
        'phone_call', 'sms', 'email', 'system_alert', 'fax', 'secure_message'
    )),

    -- Recipient Information
    notified_user_id UUID NOT NULL REFERENCES users(id),
    notified_user_name VARCHAR(200) NOT NULL,
    notified_user_role VARCHAR(100),
    recipient_phone VARCHAR(15),

    -- Call Details (for phone notifications)
    call_attempted_at TIMESTAMP WITH TIME ZONE,
    call_connected BOOLEAN DEFAULT FALSE,
    call_duration_seconds INTEGER,
    call_answered_by VARCHAR(200),

    -- Verification and Callback
    read_back_performed BOOLEAN DEFAULT FALSE,
    read_back_correct BOOLEAN DEFAULT FALSE,
    callback_number VARCHAR(15),
    callback_verified BOOLEAN DEFAULT FALSE,
    verification_code VARCHAR(10),

    -- Acknowledgment
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_at TIMESTAMP WITH TIME ZONE,
    acknowledged_by_user_id UUID REFERENCES users(id),
    acknowledgment_method VARCHAR(30),

    -- Clinical Response
    clinical_action_taken TEXT,
    follow_up_orders JSONB, -- new orders placed as result of critical value
    patient_status_change TEXT,

    -- Escalation
    escalated BOOLEAN DEFAULT FALSE,
    escalated_at TIMESTAMP WITH TIME ZONE,
    escalated_to_user_id UUID REFERENCES users(id),
    escalation_reason TEXT,

    -- Failure and Retry
    notification_successful BOOLEAN DEFAULT FALSE,
    failure_reason TEXT,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    next_retry_at TIMESTAMP WITH TIME ZONE,

    -- Documentation
    conversation_summary TEXT,
    special_instructions TEXT,

    -- Compliance and Quality
    notification_within_target_time BOOLEAN DEFAULT TRUE,
    target_notification_time INTEGER, -- minutes
    actual_notification_time INTEGER, -- minutes from result availability

    -- Status and Performance
    notification_status VARCHAR(20) DEFAULT 'pending' CHECK (notification_status IN (
        'pending', 'sent', 'delivered', 'acknowledged', 'failed', 'escalated'
    )),

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

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

Field Type Constraints คำอธิบาย
critical_threshold_type VARCHAR(20) CHECK ประเภทค่า critical ที่ละเมิด
notification_method VARCHAR(30) CHECK วิธีการแจ้งเตือน
read_back_performed BOOLEAN DEFAULT FALSE ทำ read back หรือไม่
acknowledged BOOLEAN DEFAULT FALSE รับทราบแล้วหรือไม่
escalated BOOLEAN DEFAULT FALSE escalate แล้วหรือไม่
notification_within_target_time BOOLEAN DEFAULT TRUE แจ้งเตือนภายในเวลาที่กำหนดหรือไม่

14. ตาราง outlab_providers

ตารางสำหรับเก็บข้อมูลห้องปฏิบัติการภายนอก

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

    -- Provider Information
    provider_code VARCHAR(20) UNIQUE NOT NULL,
    provider_name_thai VARCHAR(200) NOT NULL,
    provider_name_eng VARCHAR(200),
    provider_type VARCHAR(50) CHECK (provider_type IN (
        'reference_lab', 'hospital_lab', 'specialty_lab', 'research_lab'
    )),

    -- Contact Information
    address TEXT,
    phone VARCHAR(15),
    fax VARCHAR(15),
    email VARCHAR(100),
    contact_person VARCHAR(100),
    emergency_contact VARCHAR(15),

    -- Business Information
    license_number VARCHAR(50),
    tax_id VARCHAR(20),

    -- Accreditation and Certification
    iso15189_certified BOOLEAN DEFAULT FALSE,
    cap_accredited BOOLEAN DEFAULT FALSE,
    thai_accreditation BOOLEAN DEFAULT FALSE,
    accreditation_expiry DATE,

    -- Service Capabilities
    available_test_categories JSONB, -- ['microbiology', 'molecular', 'genetics']
    available_tests JSONB, -- specific test codes available
    speciality_areas JSONB, -- areas of expertise

    -- Logistics and TAT
    sample_pickup_available BOOLEAN DEFAULT TRUE,
    pickup_schedule VARCHAR(200), -- daily pickup times
    courier_service VARCHAR(100),
    routine_tat_days INTEGER DEFAULT 3,
    urgent_tat_days INTEGER DEFAULT 1,

    -- Cost and Billing
    pricing_tier VARCHAR(20) DEFAULT 'standard',
    payment_terms VARCHAR(50), -- 30_days, 60_days, prepaid
    discount_percentage DECIMAL(5,2) DEFAULT 0,

    -- Communication and Integration
    online_ordering_available BOOLEAN DEFAULT FALSE,
    result_transmission_method VARCHAR(50), -- email, fax, api, web_portal
    api_endpoint VARCHAR(500),
    api_authentication JSONB,

    -- Quality and Performance
    quality_rating DECIMAL(3,2), -- 1.00 to 5.00
    on_time_delivery_rate DECIMAL(5,2), -- percentage
    error_rate DECIMAL(5,2), -- percentage
    customer_satisfaction DECIMAL(3,2),

    -- Service Level Agreement
    sla_document_path VARCHAR(500),
    sla_effective_from DATE,
    sla_effective_until DATE,

    -- Preferred Status
    preferred_provider BOOLEAN DEFAULT FALSE,
    preferred_for_categories JSONB, -- categories where this is preferred

    -- Status and Review
    provider_status VARCHAR(20) DEFAULT 'active' CHECK (provider_status IN (
        'active', 'inactive', 'suspended', 'under_review'
    )),
    last_performance_review DATE,
    next_review_date DATE,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
provider_code VARCHAR(20) UNIQUE, NOT NULL รหัสผู้ให้บริการ Lab ภายนอก
provider_type VARCHAR(50) CHECK ประเภทผู้ให้บริการ
iso15189_certified BOOLEAN DEFAULT FALSE ได้รับ ISO 15189 หรือไม่
available_test_categories JSONB หมวดหมู่การตรวจที่ให้บริการ
routine_tat_days INTEGER DEFAULT 3 TAT ปกติ (วัน)
online_ordering_available BOOLEAN DEFAULT FALSE มีระบบ online ordering หรือไม่
quality_rating DECIMAL(3,2) คะแนนคุณภาพ (1-5)

15. ตาราง outlab_orders

ตารางสำหรับการส่งตรวจภายนอก

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

    -- Internal Order Reference
    lab_order_id UUID NOT NULL REFERENCES lab_orders(id),
    specimen_id UUID REFERENCES specimens(id),

    -- External Provider
    outlab_provider_id UUID NOT NULL REFERENCES outlab_providers(id),

    -- External Order Information
    external_order_number VARCHAR(50),
    external_accession_number VARCHAR(50),

    -- Tests Ordered
    tests_ordered JSONB NOT NULL, -- list of test codes sent to external lab
    test_count INTEGER DEFAULT 1,

    -- Shipping Information
    shipped_date DATE,
    shipped_by_user_id UUID REFERENCES users(id),
    courier_service VARCHAR(100),
    tracking_number VARCHAR(100),

    -- Chain of Custody
    custody_sealed BOOLEAN DEFAULT TRUE,
    custody_temperature VARCHAR(20), -- ambient, refrigerated, frozen
    custody_form_completed BOOLEAN DEFAULT TRUE,
    custody_form_path VARCHAR(500),

    -- Expected Results
    expected_result_date DATE,
    expected_tat_days INTEGER,

    -- Result Receipt
    result_received BOOLEAN DEFAULT FALSE,
    result_received_date DATE,
    result_received_by_user_id UUID REFERENCES users(id),
    result_format VARCHAR(30), -- pdf, fax, email, api, portal
    result_file_paths JSONB, -- array of file paths

    -- Result Import
    results_imported BOOLEAN DEFAULT FALSE,
    imported_by_user_id UUID REFERENCES users(id),
    imported_at TIMESTAMP WITH TIME ZONE,
    import_method VARCHAR(30), -- manual, automated, api

    -- Communication Log
    communication_log JSONB, -- log of all communications with external lab

    -- Cost Information
    quoted_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'THB',

    -- Quality and Performance
    result_on_time BOOLEAN DEFAULT TRUE,
    result_quality_acceptable BOOLEAN DEFAULT TRUE,
    quality_issues TEXT,

    -- Status Management
    outlab_status VARCHAR(30) DEFAULT 'ordered' CHECK (outlab_status IN (
        'ordered', 'shipped', 'received_by_lab', 'in_process', 'resulted',
        'result_received', 'imported', 'completed', 'cancelled', 'lost'
    )),

    -- Cancellation
    cancelled BOOLEAN DEFAULT FALSE,
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by_user_id UUID REFERENCES users(id),
    cancellation_reason TEXT,

    -- Follow-up Requirements
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,
    follow_up_notes TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

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

Field Type Constraints คำอธิบาย
external_order_number VARCHAR(50) หมายเลขคำสั่งของ Lab ภายนอก
tests_ordered JSONB NOT NULL รายการตรวจที่ส่งไป
custody_sealed BOOLEAN DEFAULT TRUE ปิดผนึกตัวอย่างหรือไม่
result_received BOOLEAN DEFAULT FALSE ได้รับผลกลับมาแล้วหรือไม่
results_imported BOOLEAN DEFAULT FALSE นำเข้าผลลงระบบแล้วหรือไม่
outlab_status VARCHAR(30) CHECK สถานะการส่งตรวจนอก

16. ตาราง lab_inventory

ตารางสำหรับจัดการสินค้าคงคลังของห้องปฏิบัติการ

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

    -- Item Identification
    item_code VARCHAR(50) UNIQUE NOT NULL,
    item_name_thai VARCHAR(200) NOT NULL,
    item_name_eng VARCHAR(200),
    item_description TEXT,

    -- Item Classification
    item_category VARCHAR(50) NOT NULL CHECK (item_category IN (
        'reagent', 'control', 'calibrator', 'consumable', 'spare_part', 'equipment'
    )),
    item_subcategory VARCHAR(50),

    -- Manufacturer Information
    manufacturer VARCHAR(100),
    supplier VARCHAR(100),
    catalog_number VARCHAR(100),

    -- Inventory Details
    current_stock INTEGER DEFAULT 0,
    minimum_stock_level INTEGER DEFAULT 10,
    maximum_stock_level INTEGER DEFAULT 100,
    reorder_point INTEGER DEFAULT 20,
    reorder_quantity INTEGER DEFAULT 50,

    -- Unit Information
    unit_of_measure VARCHAR(20), -- pieces, ml, boxes, kits
    package_size INTEGER DEFAULT 1,
    unit_cost DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'THB',

    -- Storage Requirements
    storage_location VARCHAR(100),
    storage_conditions VARCHAR(100), -- refrigerated, frozen, room_temp, dark
    storage_temperature_min DECIMAL(4,1),
    storage_temperature_max DECIMAL(4,1),

    -- Expiration and Stability
    shelf_life_months INTEGER,
    stability_after_opening_days INTEGER,
    expiry_alert_days INTEGER DEFAULT 30, -- alert X days before expiry

    -- Usage Information
    monthly_usage_average INTEGER DEFAULT 0,
    last_used_date DATE,
    usage_trend VARCHAR(20), -- increasing, stable, decreasing

    -- Applicable Instruments
    compatible_instruments JSONB, -- instrument IDs this item works with
    applicable_test_codes JSONB, -- test codes this item is used for

    -- Quality and Regulatory
    quality_grade VARCHAR(20), -- clinical_grade, research_grade, reagent_grade
    regulatory_approvals JSONB, -- ['FDA', 'CE', 'Thai_FDA']
    msds_document_path VARCHAR(500),

    -- Supplier Information
    lead_time_days INTEGER DEFAULT 7,
    minimum_order_quantity INTEGER DEFAULT 1,
    preferred_supplier_id UUID,
    alternative_suppliers JSONB,

    -- Cost and Budget
    annual_budget_allocation DECIMAL(12,2),
    year_to_date_spending DECIMAL(12,2),
    cost_per_test DECIMAL(8,4),

    -- Status and Review
    item_status VARCHAR(20) DEFAULT 'active' CHECK (item_status IN (
        'active', 'inactive', 'discontinued', 'backordered'
    )),
    criticality_level VARCHAR(20) DEFAULT 'normal' CHECK (criticality_level IN (
        'critical', 'high', 'normal', 'low'
    )),

    -- Automation
    auto_reorder_enabled BOOLEAN DEFAULT FALSE,
    auto_reorder_supplier_id UUID,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted 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)
);

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

Field Type Constraints คำอธิบาย
item_category VARCHAR(50) NOT NULL, CHECK หมวดหมู่สินค้า
current_stock INTEGER DEFAULT 0 จำนวนคงเหลือปัจจุบัน
reorder_point INTEGER DEFAULT 20 จุดสั่งซื้อใหม่
storage_conditions VARCHAR(100) เงื่อนไขการเก็บรักษา
shelf_life_months INTEGER อายุการเก็บรักษา (เดือน)
auto_reorder_enabled BOOLEAN DEFAULT FALSE เปิดใช้การสั่งซื้ออัตโนมัติหรือไม่
criticality_level VARCHAR(20) CHECK ระดับความสำคัญ

17. ตาราง lab_reagents

ตารางสำหรับติดตาม Lot Numbers และการใช้งาน Reagents

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

    -- Inventory Reference
    inventory_item_id UUID NOT NULL REFERENCES lab_inventory(id),

    -- Lot Information
    lot_number VARCHAR(50) NOT NULL,
    expiry_date DATE NOT NULL,
    manufacture_date DATE,
    opening_date DATE,
    opened_by_user_id UUID REFERENCES users(id),

    -- Storage and Location
    storage_location VARCHAR(100) NOT NULL,
    current_location VARCHAR(100),
    storage_temperature DECIMAL(4,1),

    -- Quantity Tracking
    initial_quantity DECIMAL(10,3),
    current_quantity DECIMAL(10,3),
    reserved_quantity DECIMAL(10,3) DEFAULT 0,
    unit_of_measure VARCHAR(20),

    -- Quality Control
    qc_performed BOOLEAN DEFAULT FALSE,
    qc_performed_by UUID REFERENCES users(id),
    qc_performed_at TIMESTAMP WITH TIME ZONE,
    qc_results JSONB, -- QC test results for this lot
    qc_approved BOOLEAN DEFAULT FALSE,

    -- Usage Tracking
    tests_performed INTEGER DEFAULT 0,
    last_used_date DATE,
    estimated_remaining_tests INTEGER,

    -- Instruments Used
    approved_instruments JSONB, -- instrument IDs approved for this lot
    usage_by_instrument JSONB, -- usage statistics per instrument

    -- Stability and Expiry
    stability_after_opening_days INTEGER,
    stability_expiry DATE, -- calculated from opening date + stability period
    expiry_warning_sent BOOLEAN DEFAULT FALSE,

    -- Cost Information
    unit_cost DECIMAL(10,4),
    total_cost DECIMAL(10,2),
    cost_per_test DECIMAL(8,4),

    -- Status Management
    reagent_status VARCHAR(20) DEFAULT 'available' CHECK (reagent_status IN (
        'quarantine', 'available', 'in_use', 'expired', 'consumed', 'discarded'
    )),

    -- Disposal Information
    disposed BOOLEAN DEFAULT FALSE,
    disposal_date DATE,
    disposal_method VARCHAR(50), -- normal_waste, hazardous_waste, incineration
    disposed_by_user_id UUID REFERENCES users(id),
    disposal_reason TEXT,

    -- Comments and Notes
    receiving_notes TEXT,
    usage_notes TEXT,
    quality_notes TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

    UNIQUE(inventory_item_id, lot_number)
);

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

Field Type Constraints คำอธิบาย
lot_number VARCHAR(50) NOT NULL หมายเลข Lot
opening_date DATE วันที่เปิดใช้
current_quantity DECIMAL(10,3) ปริมาณคงเหลือ
qc_approved BOOLEAN DEFAULT FALSE ผ่าน QC แล้วหรือไม่
stability_expiry DATE วันหมดอายุการใช้งานหลังเปิด
reagent_status VARCHAR(20) CHECK สถานะรีเอเจนต์
disposal_method VARCHAR(50) วิธีการทิ้ง

18. ตาราง cpoe_ai_suggestions

ตารางสำหรับเก็บข้อมูล AI Suggestions จากระบบ CPOE AI Assist

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

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

    -- Input Information
    soap_note_text TEXT NOT NULL,
    chief_complaint TEXT,
    provisional_diagnosis TEXT,
    icd10_codes JSONB,

    -- AI Processing
    ai_model_version VARCHAR(50) NOT NULL,
    ai_engine VARCHAR(50) DEFAULT 'MediTech_AI',
    processing_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processing_time_ms INTEGER,

    -- AI Analysis Results
    extracted_symptoms JSONB, -- symptoms identified by AI
    extracted_conditions JSONB, -- conditions/diagnoses identified
    confidence_score DECIMAL(4,3), -- overall confidence 0.000 to 1.000

    -- Test Suggestions
    suggested_tests JSONB NOT NULL, -- array of suggested test codes with confidence scores
    suggestion_rationale JSONB, -- reasoning for each suggestion
    clinical_decision_rules JSONB, -- rules that influenced suggestions

    -- Contextual Information
    patient_age_at_suggestion INTEGER,
    patient_gender CHAR(1),
    patient_medical_history JSONB, -- relevant history from EMR
    current_medications JSONB, -- current medications that might affect testing

    -- Risk Assessment
    clinical_risk_level VARCHAR(20) DEFAULT 'moderate' CHECK (clinical_risk_level IN (
        'low', 'moderate', 'high', 'critical'
    )),
    risk_factors JSONB, -- identified risk factors
    contraindications JSONB, -- tests that should be avoided

    -- User Interaction
    presented_to_user_id UUID NOT NULL REFERENCES users(id),
    suggestions_accepted JSONB, -- which suggestions were accepted
    suggestions_rejected JSONB, -- which suggestions were rejected
    manual_additions JSONB, -- tests added manually by physician

    -- Feedback and Learning
    user_feedback_rating INTEGER CHECK (user_feedback_rating BETWEEN 1 AND 5),
    user_feedback_comments TEXT,
    clinical_outcome_feedback JSONB, -- feedback on clinical outcomes

    -- Compliance and Audit
    guideline_adherence JSONB, -- adherence to clinical guidelines
    cost_effectiveness_score DECIMAL(4,3),
    duplicate_test_warnings JSONB, -- warnings about duplicate recent tests

    -- Performance Metrics
    suggestion_accuracy DECIMAL(4,3), -- measured against final orders
    time_to_decision_minutes INTEGER, -- time from suggestion to order placement
    suggestion_uptake_rate DECIMAL(4,3), -- percentage of suggestions accepted

    -- Status and Review
    suggestion_status VARCHAR(20) DEFAULT 'active' CHECK (suggestion_status IN (
        'active', 'accepted', 'rejected', 'partially_accepted', 'expired'
    )),
    reviewed_by_supervisor BOOLEAN DEFAULT FALSE,
    supervisor_notes TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            COALESCE(soap_note_text, '') || ' ' || COALESCE(chief_complaint, '') || ' ' ||
            COALESCE(provisional_diagnosis, '')
        )
    ) STORED
);

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

Field Type Constraints คำอธิบาย
soap_note_text TEXT NOT NULL ข้อความ SOAP note ที่ AI วิเคราะห์
ai_model_version VARCHAR(50) NOT NULL เวอร์ชันโมเดล AI
suggested_tests JSONB NOT NULL การตรวจที่ AI แนะนำ
confidence_score DECIMAL(4,3) คะแนนความมั่นใจโดยรวม
clinical_risk_level VARCHAR(20) CHECK ระดับความเสี่ยงทางคลินิก
suggestions_accepted JSONB ข้อแนะนำที่ผู้ใช้ยอมรับ
user_feedback_rating INTEGER CHECK 1-5 คะแนนความพอใจของผู้ใช้

19. ตาราง lab_worksheets

ตารางสำหรับจัดการ Worksheets และ Batch Processing

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

    -- Worksheet Identification
    worksheet_number VARCHAR(20) UNIQUE NOT NULL,
    worksheet_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Department and Test Information
    department_id UUID NOT NULL REFERENCES departments(id),
    test_category VARCHAR(50) NOT NULL,
    instrument_id UUID REFERENCES lab_instruments(id),

    -- Worksheet Details
    worksheet_type VARCHAR(30) DEFAULT 'routine' CHECK (worksheet_type IN (
        'routine', 'urgent', 'stat', 'qc_only', 'maintenance', 'calibration'
    )),
    batch_size INTEGER DEFAULT 0,

    -- Quality Control
    qc_samples_included INTEGER DEFAULT 0,
    calibrators_included INTEGER DEFAULT 0,
    controls_passed BOOLEAN DEFAULT TRUE,
    qc_batch_id VARCHAR(50),

    -- Processing Information
    prepared_by_user_id UUID NOT NULL REFERENCES users(id),
    processed_by_user_id UUID REFERENCES users(id),
    reviewed_by_user_id UUID REFERENCES users(id),

    -- Timing
    preparation_started_at TIMESTAMP WITH TIME ZONE,
    processing_started_at TIMESTAMP WITH TIME ZONE,
    processing_completed_at TIMESTAMP WITH TIME ZONE,
    review_completed_at TIMESTAMP WITH TIME ZONE,

    -- Status Management
    worksheet_status VARCHAR(30) DEFAULT 'prepared' CHECK (worksheet_status IN (
        'prepared', 'in_process', 'completed', 'reviewed', 'cancelled', 'failed'
    )),

    -- Technical Information
    reagent_lots JSONB, -- lots of reagents used in this worksheet
    calibration_data JSONB, -- calibration information
    environmental_conditions JSONB, -- temperature, humidity at time of processing

    -- Performance Metrics
    processing_duration_minutes INTEGER,
    samples_processed INTEGER DEFAULT 0,
    samples_failed INTEGER DEFAULT 0,
    repeat_rate_percentage DECIMAL(5,2),

    -- Comments and Notes
    preparation_notes TEXT,
    processing_notes TEXT,
    technical_issues TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,

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

-- Worksheet items (specimens/samples included in the worksheet)
CREATE TABLE lab_worksheet_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    worksheet_id UUID NOT NULL REFERENCES lab_worksheets(id) ON DELETE CASCADE,

    -- Sample Information
    specimen_id UUID REFERENCES specimens(id),
    lab_order_item_id UUID REFERENCES lab_order_items(id),
    position_number INTEGER NOT NULL, -- position in the worksheet
    sample_type VARCHAR(30) DEFAULT 'patient' CHECK (sample_type IN (
        'patient', 'qc_normal', 'qc_abnormal', 'calibrator', 'blank'
    )),

    -- Processing Status
    processing_status VARCHAR(20) DEFAULT 'pending' CHECK (processing_status IN (
        'pending', 'processed', 'failed', 'repeated'
    )),

    -- Result Information
    raw_result_value DECIMAL(15,6),
    processed_result_value DECIMAL(15,6),
    result_flags JSONB,

    -- Comments
    sample_notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(worksheet_id, position_number)
);

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

Field Type Constraints คำอธิบาย
worksheet_number VARCHAR(20) UNIQUE, NOT NULL หมายเลข Worksheet
worksheet_type VARCHAR(30) CHECK ประเภท Worksheet
batch_size INTEGER DEFAULT 0 ขนาด Batch
controls_passed BOOLEAN DEFAULT TRUE QC ผ่านหรือไม่
worksheet_status VARCHAR(30) CHECK สถานะ Worksheet
samples_processed INTEGER DEFAULT 0 จำนวนตัวอย่างที่ประมวลผล

20. ตาราง lab_reports

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

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

    -- Report Identification
    report_number VARCHAR(20) UNIQUE NOT NULL,
    report_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Patient and Order Information
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    lab_order_id UUID NOT NULL REFERENCES lab_orders(id),

    -- Report Type and Format
    report_type VARCHAR(30) DEFAULT 'standard' CHECK (report_type IN (
        'standard', 'cumulative', 'interim', 'final', 'amended', 'addendum'
    )),
    report_format VARCHAR(20) DEFAULT 'pdf' CHECK (report_format IN (
        'pdf', 'html', 'xml', 'hl7', 'fhir'
    )),

    -- Content Information
    tests_included INTEGER DEFAULT 0,
    critical_values_count INTEGER DEFAULT 0,
    abnormal_results_count INTEGER DEFAULT 0,

    -- Report Generation
    generated_by_user_id UUID NOT NULL REFERENCES users(id),
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    auto_generated BOOLEAN DEFAULT FALSE,
    generation_time_seconds INTEGER,

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

    -- Verification and Review
    verified_by_user_id UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    pathologist_review_required BOOLEAN DEFAULT FALSE,
    pathologist_reviewed_by UUID REFERENCES users(id),
    pathologist_reviewed_at TIMESTAMP WITH TIME ZONE,

    -- Delivery Information
    delivery_method VARCHAR(30) CHECK (delivery_method IN (
        'print', 'email', 'fax', 'portal', 'api', 'mobile_app'
    )),
    delivered BOOLEAN DEFAULT FALSE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    delivered_to VARCHAR(200),

    -- File Management
    report_file_path VARCHAR(500),
    file_size_bytes INTEGER,
    file_hash VARCHAR(255),

    -- Amendment and Corrections
    amended_from_report_id UUID REFERENCES lab_reports(id),
    amendment_reason TEXT,
    version_number INTEGER DEFAULT 1,

    -- Distribution
    copied_to_physicians JSONB, -- user IDs of physicians who received copies
    distribution_list JSONB,

    -- Compliance and Audit
    retention_period_years INTEGER DEFAULT 7,
    legal_hold BOOLEAN DEFAULT FALSE,

    -- Integration
    sent_to_his BOOLEAN DEFAULT FALSE,
    sent_to_his_at TIMESTAMP WITH TIME ZONE,
    hl7_message_sent BOOLEAN DEFAULT FALSE,

    -- Performance Metrics
    order_to_report_hours DECIMAL(8,2),
    collection_to_report_hours DECIMAL(8,2),

    -- Status
    report_status VARCHAR(20) DEFAULT 'draft' CHECK (report_status IN (
        'draft', 'pending_review', 'approved', 'delivered', 'amended', 'cancelled'
    )),

    -- Comments
    report_comments TEXT,
    internal_notes TEXT,

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

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

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

Field Type Constraints คำอธิบาย
report_number VARCHAR(20) UNIQUE, NOT NULL หมายเลขรายงาน
report_type VARCHAR(30) CHECK ประเภทรายงาน
digitally_signed BOOLEAN DEFAULT FALSE มีลายเซ็นดิจิทัลหรือไม่
pathologist_review_required BOOLEAN DEFAULT FALSE ต้องการ Pathologist review หรือไม่
delivery_method VARCHAR(30) CHECK วิธีการส่งรายงาน
report_status VARCHAR(20) CHECK สถานะรายงาน
retention_period_years INTEGER DEFAULT 7 ระยะเวลาเก็บรักษา (ปี)

INDEXES และ PERFORMANCE OPTIMIZATION

สร้าง Indexes สำหรับ Performance

-- Lab Test Catalog Indexes
CREATE INDEX idx_lab_test_catalog_code ON lab_test_catalog(test_code) WHERE is_active = TRUE;
CREATE INDEX idx_lab_test_catalog_category ON lab_test_catalog(test_category) WHERE is_active = TRUE;
CREATE INDEX idx_lab_test_catalog_search ON lab_test_catalog USING gin(search_vector);
CREATE INDEX idx_lab_test_catalog_external ON lab_test_catalog(external_lab_required) WHERE external_lab_required = TRUE;

-- Lab Orders Indexes
CREATE INDEX idx_lab_orders_patient ON lab_orders(patient_id, order_date_time DESC);
CREATE INDEX idx_lab_orders_visit ON lab_orders(visit_id, order_date_time DESC);
CREATE INDEX idx_lab_orders_status ON lab_orders(order_status, order_date_time DESC);
CREATE INDEX idx_lab_orders_priority ON lab_orders(priority, order_date_time ASC);
CREATE INDEX idx_lab_orders_department ON lab_orders(ordering_department_id, order_date_time DESC);
CREATE INDEX idx_lab_orders_external ON lab_orders(external_lab_required) WHERE external_lab_required = TRUE;

-- Lab Order Items Indexes
CREATE INDEX idx_lab_order_items_order ON lab_order_items(lab_order_id, sequence_order);
CREATE INDEX idx_lab_order_items_test ON lab_order_items(test_id, item_status);
CREATE INDEX idx_lab_order_items_status ON lab_order_items(item_status, created_at DESC);
CREATE INDEX idx_lab_order_items_critical ON lab_order_items(is_critical_value) WHERE is_critical_value = TRUE;

-- Specimens Indexes
CREATE INDEX idx_specimens_barcode ON specimens(specimen_barcode) WHERE is_active = TRUE;
CREATE INDEX idx_specimens_patient ON specimens(patient_id, collection_date_time DESC);
CREATE INDEX idx_specimens_order ON specimens(lab_order_id);
CREATE INDEX idx_specimens_status ON specimens(specimen_status, collection_date_time DESC);
CREATE INDEX idx_specimens_quality ON specimens(specimen_quality) WHERE specimen_quality != 'acceptable';

-- Specimen Tracking Indexes
CREATE INDEX idx_specimen_tracking_specimen ON specimen_tracking(specimen_id, event_timestamp DESC);
CREATE INDEX idx_specimen_tracking_event ON specimen_tracking(tracking_event, event_timestamp DESC);
CREATE INDEX idx_specimen_tracking_user ON specimen_tracking(performed_by_user_id, event_timestamp DESC);

-- Lab Results Indexes
CREATE INDEX idx_lab_results_order ON lab_results(lab_order_id, analysis_date_time DESC);
CREATE INDEX idx_lab_results_patient ON lab_results(patient_id, analysis_date_time DESC);
CREATE INDEX idx_lab_results_test ON lab_results(test_id, analysis_date_time DESC);
CREATE INDEX idx_lab_results_status ON lab_results(result_status, analysis_date_time DESC);
CREATE INDEX idx_lab_results_critical ON lab_results(is_critical_value, analysis_date_time DESC) WHERE is_critical_value = TRUE;
CREATE INDEX idx_lab_results_abnormal ON lab_results(abnormal_flag, analysis_date_time DESC) WHERE abnormal_flag != 'N';
CREATE INDEX idx_lab_results_search ON lab_results USING gin(search_vector);

-- Quality Control Indexes
CREATE INDEX idx_qc_results_material ON qc_results(qc_material_id, qc_run_date DESC);
CREATE INDEX idx_qc_results_instrument ON qc_results(instrument_id, qc_run_date DESC);
CREATE INDEX idx_qc_results_test ON qc_results(test_id, qc_run_date DESC);
CREATE INDEX idx_qc_results_status ON qc_results(qc_status, qc_run_date DESC);
CREATE INDEX idx_qc_results_violations ON qc_results USING gin(westgard_rule_violations);

-- Critical Values Indexes
CREATE INDEX idx_critical_values_test ON critical_values(test_id) WHERE is_active = TRUE;
CREATE INDEX idx_critical_values_department ON critical_values(department_id) WHERE is_active = TRUE;

-- Critical Value Notifications Indexes
CREATE INDEX idx_critical_notifications_result ON critical_value_notifications(lab_result_id);
CREATE INDEX idx_critical_notifications_patient ON critical_value_notifications(patient_id, notification_timestamp DESC);
CREATE INDEX idx_critical_notifications_status ON critical_value_notifications(notification_status, notification_timestamp DESC);
CREATE INDEX idx_critical_notifications_user ON critical_value_notifications(notified_user_id, notification_timestamp DESC);

-- Outlab Indexes
CREATE INDEX idx_outlab_orders_provider ON outlab_orders(outlab_provider_id, shipped_date DESC);
CREATE INDEX idx_outlab_orders_status ON outlab_orders(outlab_status, created_at DESC);
CREATE INDEX idx_outlab_orders_received ON outlab_orders(result_received, result_received_date DESC);

-- Inventory Indexes
CREATE INDEX idx_lab_inventory_category ON lab_inventory(item_category) WHERE is_active = TRUE;
CREATE INDEX idx_lab_inventory_stock ON lab_inventory(current_stock, minimum_stock_level) WHERE current_stock <= reorder_point;
CREATE INDEX idx_lab_inventory_expiry ON lab_inventory(item_code, created_at DESC);

-- Reagent Indexes
CREATE INDEX idx_lab_reagents_lot ON lab_reagents(lot_number, expiry_date);
CREATE INDEX idx_lab_reagents_inventory ON lab_reagents(inventory_item_id, expiry_date);
CREATE INDEX idx_lab_reagents_status ON lab_reagents(reagent_status) WHERE reagent_status IN ('available', 'in_use');
CREATE INDEX idx_lab_reagents_expiry ON lab_reagents(expiry_date) WHERE expiry_date <= CURRENT_DATE + INTERVAL '30 days';

-- AI Suggestions Indexes
CREATE INDEX idx_cpoe_ai_patient ON cpoe_ai_suggestions(patient_id, processing_timestamp DESC);
CREATE INDEX idx_cpoe_ai_user ON cpoe_ai_suggestions(presented_to_user_id, processing_timestamp DESC);
CREATE INDEX idx_cpoe_ai_search ON cpoe_ai_suggestions USING gin(search_vector);
CREATE INDEX idx_cpoe_ai_confidence ON cpoe_ai_suggestions(confidence_score DESC, processing_timestamp DESC);

-- Worksheet Indexes
CREATE INDEX idx_lab_worksheets_date ON lab_worksheets(worksheet_date DESC, department_id);
CREATE INDEX idx_lab_worksheets_instrument ON lab_worksheets(instrument_id, worksheet_date DESC);
CREATE INDEX idx_lab_worksheets_status ON lab_worksheets(worksheet_status, created_at DESC);

-- Report Indexes
CREATE INDEX idx_lab_reports_patient ON lab_reports(patient_id, report_date DESC);
CREATE INDEX idx_lab_reports_order ON lab_reports(lab_order_id);
CREATE INDEX idx_lab_reports_status ON lab_reports(report_status, generated_at DESC);
CREATE INDEX idx_lab_reports_signed ON lab_reports(digitally_signed, signed_at DESC) WHERE digitally_signed = TRUE;

BUSINESS RULES และ CONSTRAINTS

Data Validation Functions

-- LAB ORDER VALIDATION
CREATE OR REPLACE FUNCTION validate_lab_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if patient exists and is active
    IF NOT EXISTS (SELECT 1 FROM patients WHERE id = NEW.patient_id AND is_active = TRUE) THEN
        RAISE EXCEPTION 'Patient ID % not found or inactive', NEW.patient_id;
    END IF;

    -- Check if medical visit exists
    IF NOT EXISTS (SELECT 1 FROM medical_visits WHERE id = NEW.visit_id AND is_active = TRUE) THEN
        RAISE EXCEPTION 'Medical visit ID % not found or inactive', NEW.visit_id;
    END IF;

    -- Generate lab order number if not provided
    IF NEW.lab_order_number IS NULL THEN
        NEW.lab_order_number := 'LAB' || TO_CHAR(NOW(), 'YYYYMMDD') || LPAD(nextval('lab_order_seq')::TEXT, 6, '0');
    END IF;

    -- Set default values based on priority
    IF NEW.priority = 'stat' AND NEW.preferred_collection_time IS NULL THEN
        NEW.preferred_collection_time = CURRENT_TIME;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_lab_order
    BEFORE INSERT OR UPDATE ON lab_orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_lab_order();

-- SPECIMEN VALIDATION
CREATE OR REPLACE FUNCTION validate_specimen()
RETURNS TRIGGER AS $$
BEGIN
    -- Generate barcode if not provided
    IF NEW.specimen_barcode IS NULL THEN
        NEW.specimen_barcode := 'SP' || TO_CHAR(NOW(), 'YYYYMMDD') || LPAD(nextval('specimen_barcode_seq')::TEXT, 8, '0');
    END IF;

    -- Validate collection time is not in the future
    IF NEW.collection_date_time > CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION 'Collection time cannot be in the future';
    END IF;

    -- Check specimen volume constraints
    IF NEW.specimen_volume_ml IS NOT NULL AND NEW.specimen_volume_ml <= 0 THEN
        RAISE EXCEPTION 'Specimen volume must be positive';
    END IF;

    -- Auto-set received time if status is changed to received
    IF NEW.specimen_status = 'received' AND OLD.specimen_status != 'received' THEN
        NEW.received_in_lab_at := CURRENT_TIMESTAMP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_specimen
    BEFORE INSERT OR UPDATE ON specimens
    FOR EACH ROW
    EXECUTE FUNCTION validate_specimen();

-- CRITICAL VALUE NOTIFICATION
CREATE OR REPLACE FUNCTION check_critical_values()
RETURNS TRIGGER AS $$
DECLARE
    cv_record RECORD;
    critical_found BOOLEAN := FALSE;
BEGIN
    -- Check if result value falls within critical ranges
    FOR cv_record IN 
        SELECT * FROM critical_values 
        WHERE test_id = NEW.test_id 
        AND is_active = TRUE
        AND (patient_age_min IS NULL OR NEW.patient_id IN (
            SELECT id FROM patients WHERE EXTRACT(YEAR FROM AGE(birth_date)) >= cv_record.patient_age_min
        ))
        AND (patient_age_max IS NULL OR NEW.patient_id IN (
            SELECT id FROM patients WHERE EXTRACT(YEAR FROM AGE(birth_date)) <= cv_record.patient_age_max
        ))
    LOOP
        -- Check for critical low value
        IF NEW.result_value_numeric IS NOT NULL AND cv_record.critical_low_value IS NOT NULL 
           AND NEW.result_value_numeric <= cv_record.critical_low_value THEN
            NEW.is_critical_value := TRUE;
            critical_found := TRUE;
        END IF;

        -- Check for critical high value
        IF NEW.result_value_numeric IS NOT NULL AND cv_record.critical_high_value IS NOT NULL 
           AND NEW.result_value_numeric >= cv_record.critical_high_value THEN
            NEW.is_critical_value := TRUE;
            critical_found := TRUE;
        END IF;

        -- Check for panic values
        IF NEW.result_value_numeric IS NOT NULL THEN
            IF (cv_record.panic_low_value IS NOT NULL AND NEW.result_value_numeric <= cv_record.panic_low_value) OR
               (cv_record.panic_high_value IS NOT NULL AND NEW.result_value_numeric >= cv_record.panic_high_value) THEN
                NEW.is_panic_value := TRUE;
                critical_found := TRUE;
            END IF;
        END IF;

        EXIT WHEN critical_found;
    END LOOP;

    -- Create notification record if critical value found
    IF critical_found THEN
        INSERT INTO critical_value_notifications (
            lab_result_id, 
            critical_value_id, 
            patient_id, 
            test_code, 
            critical_value,
            critical_threshold_type,
            notification_status
        ) VALUES (
            NEW.id,
            cv_record.id,
            NEW.patient_id,
            NEW.test_code,
            NEW.result_value_numeric,
            CASE 
                WHEN NEW.result_value_numeric <= COALESCE(cv_record.panic_low_value, cv_record.critical_low_value) THEN 'panic_low'
                WHEN NEW.result_value_numeric >= COALESCE(cv_record.panic_high_value, cv_record.critical_high_value) THEN 'panic_high'
                WHEN NEW.result_value_numeric <= cv_record.critical_low_value THEN 'critical_low'
                WHEN NEW.result_value_numeric >= cv_record.critical_high_value THEN 'critical_high'
            END,
            'pending'
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_critical_values
    AFTER INSERT OR UPDATE ON lab_results
    FOR EACH ROW
    EXECUTE FUNCTION check_critical_values();

-- QC RESULT VALIDATION
CREATE OR REPLACE FUNCTION validate_qc_result()
RETURNS TRIGGER AS $$
DECLARE
    target_mean DECIMAL;
    target_std DECIMAL;
    violation_rules JSONB := '[]'::jsonb;
BEGIN
    -- Get target values for the test
    SELECT 
        (target_values->NEW.test_code->>'mean')::DECIMAL,
        (target_values->NEW.test_code->>'sd')::DECIMAL
    INTO target_mean, target_std
    FROM quality_controls 
    WHERE id = NEW.qc_material_id;

    IF target_mean IS NOT NULL AND target_std IS NOT NULL THEN
        -- Calculate Z-score
        NEW.z_score := (NEW.qc_result_value - target_mean) / target_std;

        -- Check control limits
        NEW.control_limit_1s := ABS(NEW.z_score) <= 1.0;
        NEW.control_limit_2s := ABS(NEW.z_score) <= 2.0;
        NEW.control_limit_3s := ABS(NEW.z_score) <= 3.0;

        -- Determine QC status
        IF ABS(NEW.z_score) > 3.0 THEN
            NEW.qc_status := 'unacceptable';
            NEW.action_required := TRUE;
        ELSIF ABS(NEW.z_score) > 2.0 THEN
            NEW.qc_status := 'warning';
        ELSE
            NEW.qc_status := 'acceptable';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_qc_result
    BEFORE INSERT OR UPDATE ON qc_results
    FOR EACH ROW
    EXECUTE FUNCTION validate_qc_result();

SEQUENCE GENERATORS

-- Sequences for auto-generating numbers
CREATE SEQUENCE lab_order_seq START 1000001 INCREMENT 1;
CREATE SEQUENCE specimen_barcode_seq START 10000001 INCREMENT 1;
CREATE SEQUENCE worksheet_seq START 1001 INCREMENT 1;
CREATE SEQUENCE report_seq START 100001 INCREMENT 1;

-- Functions for generating formatted numbers
CREATE OR REPLACE FUNCTION generate_lab_order_number()
RETURNS VARCHAR AS $$
BEGIN
    RETURN 'LAB' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('lab_order_seq')::TEXT, 6, '0');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_specimen_barcode()
RETURNS VARCHAR AS $$
BEGIN
    RETURN 'SP' || TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MI') || LPAD(nextval('specimen_barcode_seq')::TEXT, 6, '0');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_worksheet_number(dept_code VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN dept_code || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('worksheet_seq')::TEXT, 4, '0');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_report_number()
RETURNS VARCHAR AS $$
BEGIN
    RETURN 'RPT' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('report_seq')::TEXT, 6, '0');
END;
$$ LANGUAGE plpgsql;

TABLE COMMENTS

-- Table comments for documentation
COMMENT ON TABLE lab_test_catalog IS 'ตารางหลักข้อมูลรายการตรวจทางห้องปฏิบัติการ - รองรับ AI CPOE และ LOINC';
COMMENT ON TABLE lab_orders IS 'คำสั่งตรวจทางห้องปฏิบัติการจาก CPOE พร้อม AI Assist';
COMMENT ON TABLE specimens IS 'ตัวอย่างที่เก็บจากผู้ป่วย พร้อมการติดตาม Chain of Custody';
COMMENT ON TABLE lab_results IS 'ผลการตรวจทางห้องปฏิบัติการ รองรับ Critical Value Management';
COMMENT ON TABLE quality_controls IS 'วัสดุควบคุมคุณภาพและ Calibrators ตามมาตรฐาน ISO 15189';
COMMENT ON TABLE critical_value_notifications IS 'การแจ้งเตือน Critical Values แบบ Real-time';
COMMENT ON TABLE outlab_orders IS 'การส่งตรวจภายนอกและการนำเข้าผล';
COMMENT ON TABLE lab_inventory IS 'การจัดการสินค้าคงคลังรีเอเจนต์และอุปกรณ์';
COMMENT ON TABLE cpoe_ai_suggestions IS 'ข้อเสนอแนะการตรวจจาก AI CPOE Assistant';

-- Column comments for critical fields
COMMENT ON COLUMN lab_orders.ai_confidence_score IS 'คะแนนความมั่นใจของ AI ในการแปล SOAP note (0.00-1.00)';
COMMENT ON COLUMN specimens.specimen_barcode IS 'บาร์โค้ดตัวอย่าง ใช้สำหรับการติดตาม Chain of Custody';
COMMENT ON COLUMN lab_results.is_critical_value IS 'ระบุว่าผลตรวจนี้เป็น Critical Value ที่ต้องแจ้งเตือน';
COMMENT ON COLUMN qc_results.westgard_rule_violations IS 'รายการกฎ Westgard ที่ละเมิด เก็บเป็น JSON Array';
COMMENT ON COLUMN critical_value_notifications.read_back_performed IS 'การทำ Read Back เพื่อยืนยันการรับทราบ Critical Value';

สรุป Schema Components

ระบบห้องปฏิบัติการกลาง (Central Laboratory System) ประกอบด้วย:

🧪 Core Laboratory Functions (8 tables)

  • Test Catalog & Panels: รายการตรวจและชุดการตรวจ
  • Order Management: การสั่งตรวจผ่าน CPOE พร้อม AI Assist
  • Specimen Management: การจัดการตัวอย่างแบบครบวงจร
  • Result Management: ผลตรวจและการรายงาน

🔬 Quality Control (4 tables)

  • QC Materials: วัสดุควบคุมคุณภาพ
  • QC Results: ผล QC พร้อม Westgard Rules
  • Critical Values: การจัดการค่า Critical
  • Notifications: การแจ้งเตือนแบบ Real-time

🏭 Instrument Integration (3 tables)

  • Instruments: เครื่องมือห้องปฏิบัติการ
  • Connections: การเชื่อมต่อ LIS
  • Worksheets: การประมวลผลแบบ Batch

🌐 External Integration (2 tables)

  • Outlab Providers: ห้องปฏิบัติการภายนอก
  • Outlab Orders: การส่งตรวจภายนอก

📦 Inventory Management (2 tables)

  • Inventory: สินค้าคงคลัง
  • Reagents: การติดตาม Lot Numbers

🤖 AI Integration (1 table)

  • AI Suggestions: ข้อแนะนำจาก CPOE AI Assist

📊 Reporting & Analytics (1 table)

  • Reports: รายงานผลตรวจแบบดิจิทัล

🔐 Compliance Features

  • ✅ ISO 15189:2022 compliance
  • ✅ CAP requirements
  • ✅ PDPA data protection
  • ✅ 7-year audit trail retention
  • ✅ Digital signature support
  • ✅ Critical value management

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