Database Schema - ระบบงานพยาธิวิทยา (Pathology System)

Document Version: 1.0 (Master Schema Aligned)
Date: 1 กันยายน 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 เพื่อให้มีการบูรณาการที่สมบูรณ์แบบกับระบบ CPOE AI Assist, Digital Pathology และ External Lab Integration

---

Table of Contents

1. ตาราง pathology_orders
2. ตาราง pathology_specimens
3. ตาราง specimen_tracking
4. ตาราง pathology_results
5. ตาราง pathology_images
6. ตาราง pathology_reports
7. ตาราง external_pathology_labs
8. ตาราง external_pathology_results
9. ตาราง pathology_consultations
10. ตาราง pathology_quality_control
11. ตาราง pathology_ai_parsing
12. ตาราง digital_pathology_sessions

---

SHARED FOUNDATION TABLES (จาก Master Schema)

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

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

• patients(id) - ข้อมูลผู้ป่วย
• users(id) - บุคลากรทางการแพทย์และเจ้าหน้าที่
• medical_visits(id) - การมารับบริการของผู้ป่วย
• medical_orders(id) - คำสั่งทางการแพทย์จากระบบ CPOE
• departments(id) - แผนกต่างๆ ในโรงพยาบาล
• digital_signatures(id) - ลายเซ็นดิจิตอล
• audit_logs - การบันทึกการตรวจสอบ

---

PATHOLOGY-SPECIFIC TABLES

1. ตาราง pathology_orders

ตารางสำหรับเก็บคำสั่งการตรวจทางพยาธิวิทยาที่ส่งมาจากระบบ CPOE พร้อม AI Assist Integration

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

    -- References to Master Schema
    medical_order_id UUID NOT NULL REFERENCES medical_orders(id),
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    ordered_by_user_id UUID NOT NULL REFERENCES users(id),
    department_id UUID REFERENCES departments(id),

    -- Order Identification
    pathology_order_number VARCHAR(20) UNIQUE NOT NULL,
    accession_number VARCHAR(15) UNIQUE, -- Generated after specimen received

    -- Order Classification
    order_type VARCHAR(30) NOT NULL CHECK (order_type IN (
        'histopathology', 'cytology', 'frozen_section', 
        'immunohistochemistry', 'molecular_pathology', 'special_stains',
        'autopsy', 'consultation'
    )),
    order_subtype VARCHAR(50), -- biopsy, resection, fine_needle_aspiration, etc.

    -- Clinical Information
    clinical_indication TEXT NOT NULL,
    clinical_history TEXT,
    provisional_diagnosis TEXT,
    anatomical_site VARCHAR(200),
    laterality VARCHAR(10) CHECK (laterality IN ('left', 'right', 'bilateral', 'midline', 'not_applicable')),

    -- Procedure Information
    procedure_type VARCHAR(100), -- biopsy, excision, resection, etc.
    procedure_date DATE,
    surgeon_id UUID REFERENCES users(id),
    anesthesia_type VARCHAR(50),

    -- Priority and Timing
    priority_level VARCHAR(20) DEFAULT 'routine' CHECK (priority_level IN (
        'stat', 'urgent', 'routine', 'research'
    )),
    requested_completion_date DATE,

    -- Special Instructions
    special_instructions TEXT,
    handling_instructions TEXT,
    safety_precautions TEXT,

    -- AI Assist Integration
    ai_parsed_from_text BOOLEAN DEFAULT FALSE,
    original_ai_text TEXT,
    ai_confidence_score DECIMAL(3,2) CHECK (ai_confidence_score BETWEEN 0.00 AND 1.00),
    ai_suggestions JSONB,
    physician_approved_ai BOOLEAN DEFAULT FALSE,
    manual_modifications TEXT,

    -- Clinical Decision Support
    risk_factors JSONB, -- patient-specific risk factors
    contraindications JSONB, -- any identified contraindications
    drug_interactions JSONB, -- medications that might affect results
    allergy_alerts JSONB, -- relevant allergies

    -- Status Management
    order_status VARCHAR(20) DEFAULT 'pending' CHECK (order_status IN (
        'pending', 'scheduled', 'specimen_collected', 'received', 'processing',
        'reported', 'amended', 'cancelled', 'hold'
    )),

    -- Financial Information
    estimated_cost DECIMAL(10,2),
    insurance_preauth_required BOOLEAN DEFAULT FALSE,
    insurance_preauth_number VARCHAR(50),

    -- External Lab Information
    send_to_external_lab BOOLEAN DEFAULT FALSE,
    external_lab_id UUID REFERENCES external_pathology_labs(id),
    external_order_id VARCHAR(50),

    -- Status Tracking
    scheduled_at TIMESTAMP WITH TIME ZONE,
    specimen_collected_at TIMESTAMP WITH TIME ZONE,
    received_at TIMESTAMP WITH TIME ZONE,
    processing_started_at TIMESTAMP WITH TIME ZONE,
    reported_at TIMESTAMP WITH TIME ZONE,
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by_user_id UUID REFERENCES users(id),
    cancellation_reason TEXT,

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

    -- Standard 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	คำอธิบาย
pathology_order_number	VARCHAR(20)	UNIQUE, NOT NULL	หมายเลขคำสั่งตรวจพยาธิวิทยา (Auto-generated)
accession_number	VARCHAR(15)	UNIQUE	หมายเลข Accession ที่สร้างหลังรับ specimen
order_type	VARCHAR(30)	NOT NULL, CHECK	ประเภทการตรวจ (histopathology, cytology, etc.)
order_subtype	VARCHAR(50)		ประเภทย่อยของการตรวจ
clinical_indication	TEXT	NOT NULL	เหตุผลทางคลินิกที่ส่งตรวจ
anatomical_site	VARCHAR(200)		ตำแหน่งกายวิภาคที่เก็บตัวอย่าง
laterality	VARCHAR(10)	CHECK	ตำแหน่งข้าง (ซ้าย, ขวา, สองข้าง)
ai_parsed_from_text	BOOLEAN	DEFAULT FALSE	ระบุว่าใช้ AI แปลงจากข้อความหรือไม่
ai_confidence_score	DECIMAL(3,2)		คะแนนความมั่นใจของ AI (0.00-1.00)
risk_factors	JSONB		ปัจจัยเสี่ยงของผู้ป่วย
send_to_external_lab	BOOLEAN	DEFAULT FALSE	ส่งตรวจภายนอกหรือไม่
priority_level	VARCHAR(20)	CHECK	ระดับความสำคัญ (stat, urgent, routine)

Indexes และ Performance Optimization:

-- Performance indexes for pathology orders
CREATE INDEX idx_pathology_orders_patient ON pathology_orders(patient_id, created_at DESC);
CREATE INDEX idx_pathology_orders_visit ON pathology_orders(visit_id, order_status);
CREATE INDEX idx_pathology_orders_status ON pathology_orders(order_status, priority_level, created_at);
CREATE INDEX idx_pathology_orders_type ON pathology_orders(order_type, order_subtype);
CREATE INDEX idx_pathology_orders_site ON pathology_orders(anatomical_site) WHERE anatomical_site IS NOT NULL;
CREATE INDEX idx_pathology_orders_external ON pathology_orders(external_lab_id) WHERE send_to_external_lab = TRUE;
CREATE INDEX idx_pathology_orders_ai ON pathology_orders(ai_parsed_from_text, ai_confidence_score) WHERE ai_parsed_from_text = TRUE;
CREATE INDEX idx_pathology_orders_accession ON pathology_orders(accession_number) WHERE accession_number IS NOT NULL;

---

2. ตาราง pathology_specimens

ตารางสำหรับจัดการข้อมูล Specimen และการติดตามสถานะตลอดกระบวนการตรวจ

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

    -- References
    pathology_order_id UUID NOT NULL REFERENCES pathology_orders(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Specimen Identification
    specimen_number VARCHAR(20) UNIQUE NOT NULL,
    barcode VARCHAR(30) UNIQUE NOT NULL,
    container_id VARCHAR(20),

    -- Collection Information
    collection_method VARCHAR(50), -- biopsy, surgical, fna, brushing, etc.
    collection_date_time TIMESTAMP WITH TIME ZONE NOT NULL,
    collected_by_user_id UUID REFERENCES users(id),
    collection_site_details TEXT,

    -- Specimen Characteristics
    specimen_type VARCHAR(50) NOT NULL, -- tissue, cell_block, fluid, smear, etc.
    specimen_description TEXT,
    number_of_pieces INTEGER DEFAULT 1,
    specimen_size_mm VARCHAR(50), -- dimensions in millimeters
    specimen_weight_grams DECIMAL(8,3),
    specimen_volume_ml DECIMAL(8,3),

    -- Container and Fixation
    container_type VARCHAR(30), -- formalin, cytolyt, dry, etc.
    fixative_type VARCHAR(30) DEFAULT 'formalin',
    fixation_duration_hours INTEGER,
    fixation_adequacy VARCHAR(20) CHECK (fixation_adequacy IN ('adequate', 'suboptimal', 'poor', 'unfixed')),

    -- Processing Information
    processing_priority VARCHAR(20) DEFAULT 'routine' CHECK (processing_priority IN ('stat', 'urgent', 'routine')),
    processing_protocol VARCHAR(50), -- standard_tissue, bone, fat, etc.
    dehydration_completed_at TIMESTAMP WITH TIME ZONE,
    embedding_completed_at TIMESTAMP WITH TIME ZONE,
    sectioning_completed_at TIMESTAMP WITH TIME ZONE,
    staining_completed_at TIMESTAMP WITH TIME ZONE,

    -- Quality Control
    adequacy_for_diagnosis VARCHAR(20) CHECK (adequacy_for_diagnosis IN ('adequate', 'limited', 'inadequate', 'unsatisfactory')),
    technical_quality VARCHAR(20) CHECK (technical_quality IN ('excellent', 'good', 'acceptable', 'poor')),
    artifact_presence BOOLEAN DEFAULT FALSE,
    artifact_description TEXT,

    -- Special Processing
    requires_decalcification BOOLEAN DEFAULT FALSE,
    decalcification_method VARCHAR(30),
    special_stains_required BOOLEAN DEFAULT FALSE,
    ihc_required BOOLEAN DEFAULT FALSE,
    molecular_testing_required BOOLEAN DEFAULT FALSE,

    -- Frozen Section Information
    frozen_section_requested BOOLEAN DEFAULT FALSE,
    frozen_section_completed_at TIMESTAMP WITH TIME ZONE,
    frozen_section_diagnosis TEXT,

    -- Status Tracking
    specimen_status VARCHAR(20) DEFAULT 'collected' CHECK (specimen_status IN (
        'collected', 'received', 'registered', 'processing', 'sectioning', 
        'staining', 'ready_for_review', 'reviewed', 'reported', 'archived', 'discarded'
    )),

    -- Location Tracking
    current_location VARCHAR(50), -- lab_bench, pathologist_desk, archive, etc.
    storage_temperature VARCHAR(20), -- room_temp, refrigerated, frozen

    -- Chain of Custody
    custody_transfers JSONB, -- Array of transfer records

    -- External Lab Handling
    sent_to_external_lab BOOLEAN DEFAULT FALSE,
    external_lab_id UUID REFERENCES external_pathology_labs(id),
    shipped_at TIMESTAMP WITH TIME ZONE,
    tracking_number VARCHAR(50),

    -- Comments and Notes
    collection_comments TEXT,
    processing_comments TEXT,
    pathologist_comments TEXT,

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

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

Indexes สำหรับ Specimen Tracking:

-- Specimen tracking and performance indexes
CREATE INDEX idx_specimens_barcode ON pathology_specimens(barcode);
CREATE INDEX idx_specimens_order ON pathology_specimens(pathology_order_id, specimen_status);
CREATE INDEX idx_specimens_status ON pathology_specimens(specimen_status, processing_priority, collection_date_time);
CREATE INDEX idx_specimens_location ON pathology_specimens(current_location, specimen_status);
CREATE INDEX idx_specimens_external ON pathology_specimens(external_lab_id, shipped_at) WHERE sent_to_external_lab = TRUE;
CREATE INDEX idx_specimens_frozen ON pathology_specimens(frozen_section_requested, frozen_section_completed_at) WHERE frozen_section_requested = TRUE;
CREATE INDEX idx_specimens_collection_date ON pathology_specimens(collection_date_time DESC);

---

3. ตาราง specimen_tracking

ตารางสำหรับบันทึกการเคลื่อนไหวและการติดตาม Specimen แบบ Real-time

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

    -- References
    specimen_id UUID NOT NULL REFERENCES pathology_specimens(id),

    -- Tracking Information
    tracking_event VARCHAR(50) NOT NULL, -- received, processed, stained, reviewed, etc.
    event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    performed_by_user_id UUID REFERENCES users(id),

    -- Location Information
    from_location VARCHAR(50),
    to_location VARCHAR(50),
    current_station VARCHAR(50), -- grossing, processing, microtomy, staining, etc.

    -- Event Details
    event_description TEXT,
    event_data JSONB, -- Additional structured data

    -- Quality Information
    quality_check_performed BOOLEAN DEFAULT FALSE,
    quality_status VARCHAR(20) CHECK (quality_status IN ('pass', 'fail', 'conditional')),
    quality_notes TEXT,

    -- Equipment Information
    equipment_used VARCHAR(100),
    equipment_id VARCHAR(50),
    batch_number VARCHAR(30),

    -- Environmental Conditions
    temperature_celsius DECIMAL(5,2),
    humidity_percent DECIMAL(5,2),

    -- System Information
    automated_event BOOLEAN DEFAULT FALSE,
    system_source VARCHAR(50), -- manual, barcode_scanner, lims, etc.

    -- Integration Data
    external_system_id VARCHAR(50),
    correlation_id UUID,

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

Indexes สำหรับ Real-time Tracking:

-- Real-time 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);
CREATE INDEX idx_specimen_tracking_location ON specimen_tracking(to_location, event_timestamp DESC);
CREATE INDEX idx_specimen_tracking_station ON specimen_tracking(current_station, event_timestamp DESC);

---

4. ตาราง pathology_results

ตารางสำหรับเก็บผลการตรวจทางพยาธิวิทยาแบบครบถ้วน

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

    -- References
    pathology_order_id UUID NOT NULL REFERENCES pathology_orders(id),
    specimen_id UUID NOT NULL REFERENCES pathology_specimens(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Result Identification
    result_id VARCHAR(20) UNIQUE NOT NULL,
    version_number INTEGER DEFAULT 1,
    amendment_of UUID REFERENCES pathology_results(id),

    -- Reporting Information
    reported_by_pathologist_id UUID NOT NULL REFERENCES users(id),
    reviewed_by_pathologist_id UUID REFERENCES users(id),
    reported_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP WITH TIME ZONE,

    -- Clinical Information
    clinical_correlation TEXT,
    gross_description TEXT,
    microscopic_description TEXT,

    -- Diagnosis Information
    primary_diagnosis TEXT NOT NULL,
    secondary_diagnoses JSONB, -- Array of additional diagnoses

    -- Histopathology Specific
    histologic_type VARCHAR(100),
    histologic_grade VARCHAR(20), -- G1, G2, G3, GX
    tumor_size_mm VARCHAR(50),
    invasion_depth_mm DECIMAL(8,3),

    -- Staging Information (if applicable)
    tnm_t_stage VARCHAR(10),
    tnm_n_stage VARCHAR(10),
    tnm_m_stage VARCHAR(10),
    overall_stage VARCHAR(10),
    staging_system VARCHAR(50), -- AJCC 8th edition, WHO, etc.

    -- Margins and Resection
    margin_status VARCHAR(20) CHECK (margin_status IN ('negative', 'positive', 'close', 'cannot_assess')),
    margin_distance_mm DECIMAL(8,3),
    resection_status VARCHAR(10) CHECK (resection_status IN ('R0', 'R1', 'R2', 'RX')),

    -- Immunohistochemistry Results
    ihc_results JSONB, -- Structured IHC marker results
    ihc_interpretation TEXT,

    -- Molecular Pathology Results
    molecular_results JSONB, -- Genetic/molecular test results
    molecular_interpretation TEXT,
    biomarker_status JSONB, -- Therapeutic biomarkers

    -- Special Stains
    special_stains_results JSONB, -- Results of special stains

    -- Prognostic Information
    prognostic_factors JSONB,
    predictive_factors JSONB,

    -- Critical Values
    is_critical_result BOOLEAN DEFAULT FALSE,
    critical_value_type VARCHAR(50), -- malignancy, high_grade, etc.
    critical_notification_required BOOLEAN DEFAULT FALSE,
    critical_notified_at TIMESTAMP WITH TIME ZONE,
    notified_to_user_id UUID REFERENCES users(id),

    -- Quality and Limitations
    diagnostic_confidence VARCHAR(20) CHECK (diagnostic_confidence IN ('definitive', 'probable', 'possible', 'uncertain')),
    limitations TEXT,
    technical_comments TEXT,

    -- Recommendations
    additional_testing_recommended TEXT,
    clinical_follow_up_recommended TEXT,
    consultation_recommended BOOLEAN DEFAULT FALSE,
    consultation_specialty VARCHAR(50),

    -- External Review
    sent_for_external_consultation BOOLEAN DEFAULT FALSE,
    external_consultation_lab VARCHAR(100),
    external_opinion TEXT,

    -- Report Status
    result_status VARCHAR(20) DEFAULT 'preliminary' CHECK (result_status IN (
        'preliminary', 'final', 'corrected', 'amended', 'cancelled'
    )),

    -- Amendment Information
    amendment_reason TEXT,
    amendment_date TIMESTAMP WITH TIME ZONE,
    amendment_by_user_id UUID REFERENCES users(id),

    -- Digital Signature
    digital_signature_id UUID REFERENCES digital_signatures(id),
    signed_at TIMESTAMP WITH TIME ZONE,

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

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

Indexes สำหรับ Results:

-- Pathology results indexes
CREATE INDEX idx_pathology_results_order ON pathology_results(pathology_order_id, result_status);
CREATE INDEX idx_pathology_results_patient ON pathology_results(patient_id, reported_at DESC);
CREATE INDEX idx_pathology_results_pathologist ON pathology_results(reported_by_pathologist_id, reported_at DESC);
CREATE INDEX idx_pathology_results_diagnosis ON pathology_results USING gin(to_tsvector('english', primary_diagnosis));
CREATE INDEX idx_pathology_results_critical ON pathology_results(is_critical_result, critical_notified_at) WHERE is_critical_result = TRUE;
CREATE INDEX idx_pathology_results_status ON pathology_results(result_status, reported_at DESC);
CREATE INDEX idx_pathology_results_amendment ON pathology_results(amendment_of) WHERE amendment_of IS NOT NULL;

---

5. ตาราง pathology_images

ตารางสำหรับจัดการภาพ Digital Pathology และ Microscopic Images

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

    -- References
    pathology_result_id UUID REFERENCES pathology_results(id),
    specimen_id UUID REFERENCES pathology_specimens(id),

    -- Image Identification
    image_id VARCHAR(30) UNIQUE NOT NULL,
    dicom_study_uid VARCHAR(100),
    dicom_series_uid VARCHAR(100),
    dicom_instance_uid VARCHAR(100),

    -- Image Information
    image_type VARCHAR(30) CHECK (image_type IN (
        'whole_slide', 'microscopic_field', 'gross_specimen', 
        'frozen_section', 'ihc_stain', 'special_stain', 'fluorescence'
    )),
    stain_type VARCHAR(50), -- H&E, IHC marker name, special stain name
    magnification VARCHAR(10), -- 4x, 10x, 20x, 40x, 100x
    objective_power INTEGER,

    -- File Information
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size_bytes BIGINT,
    file_format VARCHAR(10) CHECK (file_format IN ('DICOM', 'TIFF', 'SVS', 'JPEG', 'PNG', 'NDPI')),
    compression_type VARCHAR(20),

    -- Image Properties
    image_width_pixels INTEGER,
    image_height_pixels INTEGER,
    pixel_spacing_x DECIMAL(10,6), -- micrometers per pixel
    pixel_spacing_y DECIMAL(10,6),
    bit_depth INTEGER,
    color_space VARCHAR(20), -- RGB, CMYK, Grayscale

    -- Pyramid/Multi-resolution Information
    is_pyramid BOOLEAN DEFAULT FALSE,
    pyramid_levels INTEGER,
    tile_width INTEGER,
    tile_height INTEGER,

    -- Acquisition Information
    acquired_at TIMESTAMP WITH TIME ZONE,
    acquired_by_user_id UUID REFERENCES users(id),
    scanner_model VARCHAR(100),
    scanner_serial VARCHAR(50),
    scan_settings JSONB,

    -- Image Quality
    quality_score DECIMAL(3,2), -- 0.00 to 1.00
    quality_issues JSONB, -- focus, artifacts, staining, etc.

    -- Annotations
    has_annotations BOOLEAN DEFAULT FALSE,
    annotations JSONB, -- Structured annotation data
    annotation_count INTEGER DEFAULT 0,

    -- Viewing Statistics
    view_count INTEGER DEFAULT 0,
    last_viewed_at TIMESTAMP WITH TIME ZONE,
    last_viewed_by UUID REFERENCES users(id),

    -- Archival Information
    archived BOOLEAN DEFAULT FALSE,
    archived_at TIMESTAMP WITH TIME ZONE,
    archive_location VARCHAR(200),

    -- Integration Information
    external_system_id VARCHAR(50),
    pacs_integration BOOLEAN DEFAULT FALSE,
    pacs_study_id VARCHAR(100),

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

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

Indexes สำหรับ Digital Images:

-- Digital pathology image indexes
CREATE INDEX idx_pathology_images_result ON pathology_images(pathology_result_id, image_type);
CREATE INDEX idx_pathology_images_specimen ON pathology_images(specimen_id, stain_type);
CREATE INDEX idx_pathology_images_type ON pathology_images(image_type, magnification);
CREATE INDEX idx_pathology_images_dicom ON pathology_images(dicom_study_uid, dicom_series_uid);
CREATE INDEX idx_pathology_images_file ON pathology_images(file_format, file_size_bytes);
CREATE INDEX idx_pathology_images_quality ON pathology_images(quality_score DESC) WHERE quality_score IS NOT NULL;
CREATE INDEX idx_pathology_images_viewing ON pathology_images(view_count DESC, last_viewed_at DESC);
CREATE INDEX idx_pathology_images_archived ON pathology_images(archived, archived_at) WHERE archived = TRUE;

---

6. ตาราง pathology_reports

ตารางสำหรับจัดการรายงานพยาธิวิทยาแบบครบถ้วน

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

    -- References
    pathology_result_id UUID NOT NULL REFERENCES pathology_results(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Report Identification
    report_number VARCHAR(20) UNIQUE NOT NULL,
    report_type VARCHAR(30) CHECK (report_type IN (
        'surgical', 'cytology', 'autopsy', 'frozen_section', 
        'consultation', 'amended', 'supplemental'
    )),

    -- Report Content
    report_template_id UUID,
    report_title VARCHAR(200),

    -- Structured Report Sections
    clinical_information TEXT,
    gross_examination TEXT,
    microscopic_examination TEXT,
    immunohistochemistry TEXT,
    molecular_findings TEXT,
    diagnosis_section TEXT NOT NULL,
    comment_section TEXT,

    -- Synoptic Reporting (for Cancer Cases)
    is_synoptic_report BOOLEAN DEFAULT FALSE,
    synoptic_protocol VARCHAR(50), -- CAP protocol identifier
    synoptic_data JSONB, -- Structured synoptic data elements

    -- Report Status
    report_status VARCHAR(20) DEFAULT 'draft' CHECK (report_status IN (
        'draft', 'pending_review', 'finalized', 'amended', 'corrected', 'cancelled'
    )),

    -- Authoring Information
    authored_by_pathologist_id UUID NOT NULL REFERENCES users(id),
    reviewed_by_pathologist_id UUID REFERENCES users(id),

    -- Timing Information
    drafted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP WITH TIME ZONE,
    finalized_at TIMESTAMP WITH TIME ZONE,

    -- Amendment Information
    original_report_id UUID REFERENCES pathology_reports(id),
    amendment_sequence INTEGER DEFAULT 1,
    amendment_reason TEXT,
    significant_amendment BOOLEAN DEFAULT FALSE,

    -- Digital Signature
    pathologist_signature_id UUID REFERENCES digital_signatures(id),
    reviewer_signature_id UUID REFERENCES digital_signatures(id),
    signed_at TIMESTAMP WITH TIME ZONE,

    -- Distribution
    distributed_to JSONB, -- List of recipients
    distributed_at TIMESTAMP WITH TIME ZONE,

    -- Format and Export
    report_format VARCHAR(20) DEFAULT 'html' CHECK (report_format IN ('html', 'pdf', 'rtf', 'xml')),
    pdf_file_path VARCHAR(500),
    pdf_generated_at TIMESTAMP WITH TIME ZONE,

    -- Quality Metrics
    report_quality_score DECIMAL(3,2),
    completeness_score DECIMAL(3,2),

    -- External Sharing
    shared_with_external BOOLEAN DEFAULT FALSE,
    external_sharing_log JSONB,

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

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

Indexes สำหรับ Reports:

-- Pathology reports indexes
CREATE INDEX idx_pathology_reports_result ON pathology_reports(pathology_result_id, report_status);
CREATE INDEX idx_pathology_reports_patient ON pathology_reports(patient_id, finalized_at DESC);
CREATE INDEX idx_pathology_reports_pathologist ON pathology_reports(authored_by_pathologist_id, drafted_at DESC);
CREATE INDEX idx_pathology_reports_type ON pathology_reports(report_type, is_synoptic_report);
CREATE INDEX idx_pathology_reports_amendment ON pathology_reports(original_report_id, amendment_sequence) WHERE original_report_id IS NOT NULL;
CREATE INDEX idx_pathology_reports_signature ON pathology_reports(pathologist_signature_id, signed_at) WHERE pathologist_signature_id IS NOT NULL;
CREATE INDEX idx_pathology_reports_content ON pathology_reports USING gin(to_tsvector('english', diagnosis_section || ' ' || COALESCE(comment_section, '')));

---

7. ตาราง external_pathology_labs

ตารางสำหรับจัดการข้อมูลห้องพยาธิวิทยาภายนอก

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

    -- Lab Information
    lab_name VARCHAR(200) NOT NULL,
    lab_code VARCHAR(20) UNIQUE NOT NULL,
    lab_type VARCHAR(30) CHECK (lab_type IN ('hospital', 'commercial', 'reference', 'academic')),

    -- Contact Information
    contact_person VARCHAR(100),
    phone_number VARCHAR(20),
    email VARCHAR(100),
    fax_number VARCHAR(20),

    -- Address Information
    address TEXT,
    city VARCHAR(100),
    state_province VARCHAR(50),
    postal_code VARCHAR(10),
    country VARCHAR(50) DEFAULT 'Thailand',

    -- Accreditation and Certification
    accreditation_bodies JSONB, -- CAP, ISO 15189, etc.
    certifications JSONB,
    license_numbers JSONB,

    -- Specialties and Services
    specialties JSONB, -- List of pathology specialties offered
    test_menu JSONB, -- Available tests and procedures
    turnaround_times JSONB, -- Expected TAT for different test types

    -- Integration Capabilities
    hl7_capable BOOLEAN DEFAULT FALSE,
    fhir_capable BOOLEAN DEFAULT FALSE,
    api_endpoint VARCHAR(500),
    api_version VARCHAR(10),
    authentication_method VARCHAR(30), -- api_key, oauth2, basic_auth

    -- Connection Settings
    connection_config JSONB, -- Technical connection parameters
    message_format VARCHAR(20) CHECK (message_format IN ('HL7v2', 'FHIR', 'PDF', 'XML', 'JSON')),

    -- Quality and Performance
    quality_rating DECIMAL(3,2), -- 0.00 to 5.00
    performance_metrics JSONB,

    -- Financial Information
    billing_contact VARCHAR(100),
    billing_address TEXT,
    payment_terms VARCHAR(50),

    -- Contract Information
    contract_start_date DATE,
    contract_end_date DATE,
    contract_status VARCHAR(20) CHECK (contract_status IN ('active', 'pending', 'expired', 'terminated')),

    -- Status and Preferences
    preferred_lab BOOLEAN DEFAULT FALSE,
    auto_send_eligible BOOLEAN DEFAULT FALSE,

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

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

Indexes สำหรับ External Labs:

-- External labs indexes
CREATE INDEX idx_external_labs_code ON external_pathology_labs(lab_code);
CREATE INDEX idx_external_labs_type ON external_pathology_labs(lab_type, is_active);
CREATE INDEX idx_external_labs_integration ON external_pathology_labs(hl7_capable, fhir_capable) WHERE is_active = TRUE;
CREATE INDEX idx_external_labs_preferred ON external_pathology_labs(preferred_lab, quality_rating DESC) WHERE preferred_lab = TRUE;
CREATE INDEX idx_external_labs_contract ON external_pathology_labs(contract_status, contract_end_date);

---

8. ตาราง external_pathology_results

ตารางสำหรับจัดการผลการตรวจจากห้องพยาธิวิทยาภายนอก

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

    -- References
    pathology_order_id UUID REFERENCES pathology_orders(id),
    external_lab_id UUID NOT NULL REFERENCES external_pathology_labs(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- External Result Identification
    external_result_id VARCHAR(50) NOT NULL,
    external_accession_number VARCHAR(30),
    external_report_number VARCHAR(30),

    -- Message Information
    hl7_message_id VARCHAR(100),
    message_type VARCHAR(20), -- ORU^R01, etc.
    message_control_id VARCHAR(50),

    -- Result Content
    result_text TEXT,
    structured_result JSONB, -- Parsed structured data
    result_format VARCHAR(20) CHECK (result_format IN ('HL7', 'FHIR', 'PDF', 'Text', 'XML', 'JSON')),

    -- Report Files
    pdf_file_path VARCHAR(500),
    additional_files JSONB, -- Array of additional file paths

    -- Timing Information
    external_reported_date DATE,
    external_reported_time TIME,
    received_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Processing Status
    processing_status VARCHAR(20) DEFAULT 'received' CHECK (processing_status IN (
        'received', 'parsed', 'validated', 'integrated', 'rejected', 'pending_review'
    )),

    -- Validation and Quality
    validation_status VARCHAR(20) CHECK (validation_status IN ('valid', 'warning', 'error')),
    validation_messages JSONB,

    -- Integration Information
    matched_to_order BOOLEAN DEFAULT FALSE,
    matching_confidence DECIMAL(3,2),
    matching_method VARCHAR(30), -- automatic, manual, assisted
    matched_by_user_id UUID REFERENCES users(id),
    matched_at TIMESTAMP WITH TIME ZONE,

    -- Review Information
    requires_review BOOLEAN DEFAULT FALSE,
    reviewed_by_pathologist_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    review_comments TEXT,

    -- Critical Values
    contains_critical_values BOOLEAN DEFAULT FALSE,
    critical_value_details JSONB,
    critical_notification_sent BOOLEAN DEFAULT FALSE,

    -- Error Handling
    processing_errors JSONB,
    retry_count INTEGER DEFAULT 0,
    last_retry_at TIMESTAMP WITH TIME ZONE,

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

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

    -- Unique constraint for external results
    UNIQUE(external_lab_id, external_result_id)
);

Indexes สำหรับ External Results:

-- External results indexes
CREATE INDEX idx_external_results_lab ON external_pathology_results(external_lab_id, received_at DESC);
CREATE INDEX idx_external_results_order ON external_pathology_results(pathology_order_id, processing_status);
CREATE INDEX idx_external_results_patient ON external_pathology_results(patient_id, external_reported_date DESC);
CREATE INDEX idx_external_results_status ON external_pathology_results(processing_status, requires_review);
CREATE INDEX idx_external_results_matching ON external_pathology_results(matched_to_order, matching_confidence);
CREATE INDEX idx_external_results_critical ON external_pathology_results(contains_critical_values, critical_notification_sent) WHERE contains_critical_values = TRUE;
CREATE INDEX idx_external_results_hl7 ON external_pathology_results(hl7_message_id) WHERE hl7_message_id IS NOT NULL;

---

9. ตาราง pathology_consultations

ตารางสำหรับจัดการการ Consultation ทางพยาธิวิทยา

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

    -- References
    pathology_result_id UUID NOT NULL REFERENCES pathology_results(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Consultation Information
    consultation_type VARCHAR(30) CHECK (consultation_type IN (
        'internal', 'external', 'multidisciplinary', 'tumor_board', 'case_conference'
    )),

    -- Requesting Information
    requested_by_pathologist_id UUID NOT NULL REFERENCES users(id),
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    urgency_level VARCHAR(20) DEFAULT 'routine' CHECK (urgency_level IN ('stat', 'urgent', 'routine')),

    -- Consultant Information
    consultant_pathologist_id UUID REFERENCES users(id),
    consulting_institution VARCHAR(200),
    external_consultant_name VARCHAR(100),
    external_consultant_credentials VARCHAR(100),

    -- Consultation Details
    reason_for_consultation TEXT NOT NULL,
    clinical_question TEXT,
    areas_of_focus TEXT,

    -- Materials Sent
    slides_sent INTEGER,
    blocks_sent INTEGER,
    images_sent INTEGER,
    reports_sent BOOLEAN DEFAULT TRUE,
    additional_materials TEXT,

    -- Shipping Information (for external consultations)
    shipped_at TIMESTAMP WITH TIME ZONE,
    shipping_method VARCHAR(50),
    tracking_number VARCHAR(50),
    received_by_consultant_at TIMESTAMP WITH TIME ZONE,

    -- Consultation Response
    consultant_opinion TEXT,
    consultant_diagnosis TEXT,
    recommendations TEXT,
    additional_testing_suggested TEXT,

    -- Agreement Assessment
    diagnostic_agreement VARCHAR(20) CHECK (diagnostic_agreement IN (
        'complete', 'partial', 'minor_difference', 'major_difference', 'pending'
    )),
    agreement_details TEXT,

    -- Response Timing
    responded_at TIMESTAMP WITH TIME ZONE,
    turnaround_time_hours INTEGER,

    -- Follow-up
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_plan TEXT,

    -- Final Disposition
    consultation_status VARCHAR(20) DEFAULT 'requested' CHECK (consultation_status IN (
        'requested', 'materials_sent', 'in_review', 'completed', 'cancelled'
    )),
    final_diagnosis_changed BOOLEAN DEFAULT FALSE,
    impact_on_patient_care TEXT,

    -- Quality and Learning
    educational_value DECIMAL(3,2), -- 1.00 to 5.00 rating
    case_complexity DECIMAL(3,2), -- 1.00 to 5.00 rating

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

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

Indexes สำหรับ Consultations:

-- Consultation indexes
CREATE INDEX idx_consultations_result ON pathology_consultations(pathology_result_id, consultation_status);
CREATE INDEX idx_consultations_requesting ON pathology_consultations(requested_by_pathologist_id, requested_at DESC);
CREATE INDEX idx_consultations_consultant ON pathology_consultations(consultant_pathologist_id, responded_at DESC);
CREATE INDEX idx_consultations_type ON pathology_consultations(consultation_type, urgency_level);
CREATE INDEX idx_consultations_status ON pathology_consultations(consultation_status, requested_at DESC);
CREATE INDEX idx_consultations_agreement ON pathology_consultations(diagnostic_agreement, final_diagnosis_changed);

---

10. ตาราง pathology_quality_control

ตารางสำหรับระบบควบคุมคุณภาพทางพยาธิวิทยา

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

    -- References
    specimen_id UUID REFERENCES pathology_specimens(id),
    pathologist_id UUID REFERENCES users(id),

    -- QC Information
    qc_type VARCHAR(30) CHECK (qc_type IN (
        'daily_positive_control', 'negative_control', 'proficiency_test', 
        'inter_observer', 'intra_observer', 'equipment_check', 'reagent_check'
    )),
    qc_date DATE NOT NULL,

    -- Test Information
    test_name VARCHAR(100),
    stain_batch_number VARCHAR(50),
    reagent_lot_numbers JSONB,
    equipment_used VARCHAR(100),

    -- Control Material
    control_type VARCHAR(30), -- positive, negative, known_case, proficiency
    control_source VARCHAR(100),
    expected_result TEXT,

    -- QC Results
    observed_result TEXT,
    qc_status VARCHAR(20) CHECK (qc_status IN ('pass', 'fail', 'borderline', 'repeat')),

    -- Performance Metrics
    accuracy_score DECIMAL(5,2),
    precision_score DECIMAL(5,2),
    reproducibility_score DECIMAL(5,2),

    -- Scoring Information (for proficiency tests)
    total_possible_score INTEGER,
    achieved_score INTEGER,
    score_percentage DECIMAL(5,2),

    -- Corrective Actions
    corrective_action_required BOOLEAN DEFAULT FALSE,
    corrective_actions_taken TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,

    -- Reviewer Information
    reviewed_by_supervisor_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    supervisor_comments TEXT,

    -- Documentation
    images_captured INTEGER DEFAULT 0,
    documentation_complete BOOLEAN DEFAULT FALSE,

    -- External QC (Proficiency Testing)
    external_program VARCHAR(100),
    survey_number VARCHAR(50),
    submission_deadline DATE,
    submitted_at TIMESTAMP WITH TIME ZONE,

    -- Results and Feedback
    external_feedback TEXT,
    performance_rating VARCHAR(20),
    improvement_recommendations TEXT,

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

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

Indexes สำหรับ Quality Control:

-- QC indexes
CREATE INDEX idx_pathology_qc_date ON pathology_quality_control(qc_date DESC, qc_type);
CREATE INDEX idx_pathology_qc_pathologist ON pathology_quality_control(pathologist_id, qc_date DESC);
CREATE INDEX idx_pathology_qc_status ON pathology_quality_control(qc_status, corrective_action_required);
CREATE INDEX idx_pathology_qc_external ON pathology_quality_control(external_program, submission_deadline) WHERE external_program IS NOT NULL;
CREATE INDEX idx_pathology_qc_specimen ON pathology_quality_control(specimen_id) WHERE specimen_id IS NOT NULL;

---

11. ตาราง pathology_ai_parsing

ตารางสำหรับจัดการ AI Parsing และ Machine Learning ในระบบพยาธิวิทยา

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

    -- References
    pathology_order_id UUID REFERENCES pathology_orders(id),
    user_id UUID NOT NULL REFERENCES users(id),

    -- AI Model Information
    ai_model_name VARCHAR(100) NOT NULL,
    model_version VARCHAR(20),
    model_deployment_id VARCHAR(50),

    -- Input Data
    input_text TEXT NOT NULL,
    input_source VARCHAR(30) CHECK (input_source IN ('soap_note', 'plan', 'free_text', 'dictation')),
    input_language VARCHAR(5) DEFAULT 'th' CHECK (input_language IN ('th', 'en')),

    -- AI Processing
    processing_started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processing_completed_at TIMESTAMP WITH TIME ZONE,
    processing_duration_ms INTEGER,

    -- AI Results
    parsed_output JSONB NOT NULL, -- Structured AI output
    confidence_scores JSONB, -- Confidence scores for different elements
    overall_confidence DECIMAL(3,2) CHECK (overall_confidence BETWEEN 0.00 AND 1.00),

    -- Extracted Information
    identified_tests JSONB, -- Array of identified pathology tests
    anatomical_sites JSONB, -- Identified anatomical locations
    clinical_indications JSONB, -- Identified clinical reasons
    urgency_indicators JSONB, -- Identified urgency markers

    -- Quality Assessment
    parsing_quality VARCHAR(20) CHECK (parsing_quality IN ('excellent', 'good', 'acceptable', 'poor')),
    ambiguities_detected JSONB, -- Unclear or ambiguous elements

    -- Human Review and Feedback
    human_reviewed BOOLEAN DEFAULT FALSE,
    reviewed_by_user_id UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,

    -- Corrections and Feedback
    corrections_made BOOLEAN DEFAULT FALSE,
    correction_details JSONB, -- What was corrected
    feedback_rating INTEGER CHECK (feedback_rating BETWEEN 1 AND 5),
    feedback_comments TEXT,

    -- Learning and Improvement
    used_for_training BOOLEAN DEFAULT FALSE,
    training_label VARCHAR(20) CHECK (training_label IN ('positive', 'negative', 'neutral')),

    -- Error Handling
    processing_errors JSONB,
    error_resolved BOOLEAN DEFAULT TRUE,

    -- Performance Metrics
    accuracy_assessment DECIMAL(3,2),
    recall_assessment DECIMAL(3,2),
    precision_assessment DECIMAL(3,2),

    -- Usage Statistics
    acceptance_rate DECIMAL(3,2), -- How much of AI output was accepted
    modification_rate DECIMAL(3,2), -- How much was modified

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

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

Indexes สำหรับ AI Parsing:

-- AI parsing indexes
CREATE INDEX idx_ai_parsing_model ON pathology_ai_parsing(ai_model_name, model_version, processing_completed_at DESC);
CREATE INDEX idx_ai_parsing_confidence ON pathology_ai_parsing(overall_confidence DESC, parsing_quality);
CREATE INDEX idx_ai_parsing_user ON pathology_ai_parsing(user_id, processing_completed_at DESC);
CREATE INDEX idx_ai_parsing_review ON pathology_ai_parsing(human_reviewed, reviewed_at DESC);
CREATE INDEX idx_ai_parsing_feedback ON pathology_ai_parsing(feedback_rating, corrections_made) WHERE feedback_rating IS NOT NULL;
CREATE INDEX idx_ai_parsing_training ON pathology_ai_parsing(used_for_training, training_label) WHERE used_for_training = TRUE;
CREATE INDEX idx_ai_parsing_performance ON pathology_ai_parsing(accuracy_assessment DESC, processing_completed_at DESC);

---

12. ตาราง digital_pathology_sessions

ตารางสำหรับจัดการ Digital Pathology Viewing Sessions

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

    -- References
    pathology_image_id UUID NOT NULL REFERENCES pathology_images(id),
    user_id UUID NOT NULL REFERENCES users(id),
    pathology_result_id UUID REFERENCES pathology_results(id),

    -- Session Information
    session_id VARCHAR(100) UNIQUE NOT NULL,
    session_type VARCHAR(30) CHECK (session_type IN (
        'diagnostic', 'consultation', 'teaching', 'research', 'quality_review'
    )),

    -- Viewing Information
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP WITH TIME ZONE,
    duration_seconds INTEGER,

    -- Interaction Data
    zoom_events INTEGER DEFAULT 0,
    pan_events INTEGER DEFAULT 0,
    annotation_events INTEGER DEFAULT 0,
    measurement_events INTEGER DEFAULT 0,

    -- Viewing Statistics
    total_viewing_time_seconds INTEGER DEFAULT 0,
    areas_viewed JSONB, -- Coordinates of viewed areas
    magnifications_used JSONB, -- Array of magnification levels used

    -- Annotations Made
    annotations_created INTEGER DEFAULT 0,
    annotations_modified INTEGER DEFAULT 0,
    annotations_deleted INTEGER DEFAULT 0,
    annotation_data JSONB, -- Detailed annotation information

    -- Measurements
    measurements_taken INTEGER DEFAULT 0,
    measurement_data JSONB, -- Distance, area, and other measurements

    -- Digital Tools Used
    tools_used JSONB, -- List of digital pathology tools used
    filters_applied JSONB, -- Image filters or enhancements applied

    -- Collaboration
    shared_session BOOLEAN DEFAULT FALSE,
    collaborators JSONB, -- List of other users in shared session
    chat_messages INTEGER DEFAULT 0,

    -- Quality and Performance
    image_load_time_ms INTEGER,
    session_quality_score DECIMAL(3,2), -- User experience rating
    performance_issues JSONB, -- Any performance problems encountered

    -- Device and Browser Information
    device_type VARCHAR(30), -- desktop, tablet, mobile
    browser_info VARCHAR(200),
    screen_resolution VARCHAR(20),

    -- Clinical Context
    diagnostic_purpose TEXT,
    diagnostic_conclusions TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,

    -- Educational Value (for teaching sessions)
    educational_objectives TEXT,
    learning_outcomes TEXT,

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

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

Indexes สำหรับ Digital Pathology Sessions:

-- Digital pathology session indexes
CREATE INDEX idx_digital_sessions_image ON digital_pathology_sessions(pathology_image_id, started_at DESC);
CREATE INDEX idx_digital_sessions_user ON digital_pathology_sessions(user_id, session_type, started_at DESC);
CREATE INDEX idx_digital_sessions_duration ON digital_pathology_sessions(duration_seconds DESC) WHERE ended_at IS NOT NULL;
CREATE INDEX idx_digital_sessions_annotations ON digital_pathology_sessions(annotations_created DESC, annotations_modified DESC);
CREATE INDEX idx_digital_sessions_shared ON digital_pathology_sessions(shared_session, started_at DESC) WHERE shared_session = TRUE;
CREATE INDEX idx_digital_sessions_type ON digital_pathology_sessions(session_type, started_at DESC);
CREATE INDEX idx_digital_sessions_performance ON digital_pathology_sessions(session_quality_score DESC, image_load_time_ms);

---

BUSINESS RULES และ CONSTRAINTS

Pathology-Specific Business Rules

-- Business rule: Specimen must be collected before processing
ALTER TABLE pathology_specimens 
ADD CONSTRAINT chk_specimen_collection_before_processing 
CHECK (
    processing_started_at IS NULL OR 
    (collection_date_time IS NOT NULL AND processing_started_at >= collection_date_time)
);

-- Business rule: Results must be reported before finalization
ALTER TABLE pathology_results 
ADD CONSTRAINT chk_result_reporting_sequence 
CHECK (
    result_status != 'final' OR 
    (reported_at IS NOT NULL AND reported_by_pathologist_id IS NOT NULL)
);

-- Business rule: Critical results must have notification tracking
ALTER TABLE pathology_results 
ADD CONSTRAINT chk_critical_result_notification 
CHECK (
    is_critical_result = FALSE OR 
    (critical_notification_required = TRUE AND critical_notified_at IS NOT NULL)
);

-- Business rule: AI confidence score validation
ALTER TABLE pathology_ai_parsing 
ADD CONSTRAINT chk_ai_confidence_reasonable 
CHECK (
    overall_confidence IS NULL OR 
    (overall_confidence >= 0.00 AND overall_confidence <= 1.00)
);

-- Business rule: External lab results must reference valid lab
ALTER TABLE external_pathology_results 
ADD CONSTRAINT chk_external_result_valid_lab 
CHECK (
    external_lab_id IS NOT NULL
);

-- Business rule: Amendment sequence must be incremental
ALTER TABLE pathology_reports 
ADD CONSTRAINT chk_amendment_sequence_increment 
CHECK (
    original_report_id IS NULL OR 
    amendment_sequence > 1
);

Data Validation Functions

-- Pathology order validation function
CREATE OR REPLACE FUNCTION validate_pathology_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate AI confidence score if AI was used
    IF NEW.ai_parsed_from_text = TRUE AND NEW.ai_confidence_score IS NULL THEN
        RAISE EXCEPTION 'AI confidence score required when order is parsed from text';
    END IF;

    -- Validate external lab requirements
    IF NEW.send_to_external_lab = TRUE AND NEW.external_lab_id IS NULL THEN
        RAISE EXCEPTION 'External lab must be specified when sending to external lab';
    END IF;

    -- Validate frozen section timing
    IF NEW.order_type = 'frozen_section' AND NEW.priority_level != 'stat' THEN
        NEW.priority_level := 'stat';
    END IF;

    NEW.updated_at := CURRENT_TIMESTAMP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Specimen tracking validation
CREATE OR REPLACE FUNCTION validate_specimen_tracking()
RETURNS TRIGGER AS $$
BEGIN
    -- Ensure barcode uniqueness across active specimens
    IF EXISTS (
        SELECT 1 FROM pathology_specimens 
        WHERE barcode = NEW.barcode 
        AND id != NEW.id 
        AND is_deleted = FALSE
    ) THEN
        RAISE EXCEPTION 'Barcode % is already in use', NEW.barcode;
    END IF;

    -- Validate fixation adequacy for tissue specimens
    IF NEW.specimen_type = 'tissue' AND NEW.fixation_adequacy = 'unfixed' THEN
        RAISE EXCEPTION 'Tissue specimens must be fixed';
    END IF;

    NEW.updated_at := CURRENT_TIMESTAMP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create triggers
CREATE TRIGGER trg_validate_pathology_order
    BEFORE INSERT OR UPDATE ON pathology_orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_pathology_order();

CREATE TRIGGER trg_validate_specimen_tracking
    BEFORE INSERT OR UPDATE ON pathology_specimens
    FOR EACH ROW
    EXECUTE FUNCTION validate_specimen_tracking();

Sequence Generators

-- Pathology order number sequence
CREATE SEQUENCE pathology_order_seq START 100001 INCREMENT 1;

-- Accession number sequence  
CREATE SEQUENCE pathology_accession_seq START 25000001 INCREMENT 1;

-- Specimen barcode sequence
CREATE SEQUENCE specimen_barcode_seq START 1000001 INCREMENT 1;

-- Auto-generation functions
CREATE OR REPLACE FUNCTION generate_pathology_order_number()
RETURNS TRIGGER AS $$
DECLARE
    new_order_number VARCHAR(20);
    year_prefix VARCHAR(4);
BEGIN
    IF NEW.pathology_order_number IS NULL THEN
        year_prefix := EXTRACT(YEAR FROM CURRENT_DATE)::TEXT;
        SELECT 'PO' || year_prefix || LPAD(nextval('pathology_order_seq')::TEXT, 6, '0') 
        INTO new_order_number;

        NEW.pathology_order_number := new_order_number;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for auto-generation
CREATE TRIGGER trg_generate_pathology_order_number
    BEFORE INSERT ON pathology_orders
    FOR EACH ROW
    EXECUTE FUNCTION generate_pathology_order_number();

---

INTEGRATION WITH MEDITECH MASTER SCHEMA

Foundation Tables Used from Master Schema

1. patients(id) - Master Patient Index for all pathology orders and results
2. users(id) - Pathologists, technologists, and staff authentication
3. medical_visits(id) - Clinical context for pathology orders
4. medical_orders(id) - CPOE integration for pathology orders
5. departments(id) - Hospital organization structure
6. digital_signatures(id) - Legal signatures for pathology reports
7. audit_logs - Comprehensive audit trail for compliance

Pathology-Specific Tables Added

1. pathology_orders - CPOE AI Assist integrated pathology orders
2. pathology_specimens - Complete specimen lifecycle management
3. specimen_tracking - Real-time barcode tracking system
4. pathology_results - Comprehensive results with IHC, molecular data
5. pathology_images - Digital pathology image management
6. pathology_reports - Structured and synoptic reporting
7. external_pathology_labs - External laboratory management
8. external_pathology_results - HL7/FHIR result integration
9. pathology_consultations - Internal and external consultations
10. pathology_quality_control - CAP and ISO 15189 compliance
11. pathology_ai_parsing - AI learning and improvement tracking
12. digital_pathology_sessions - Digital microscopy usage analytics

Cross-Module Integration Points

โมดูล	ตารางที่อ้างอิง	ความสัมพันธ์
ระบบผู้ดูแลระบบ	users, roles, permissions	User management และ pathologist access control
ระบบ EMR	patients, medical_visits	Patient demographics และ clinical context
ระบบ CPOE	medical_orders	AI-assisted pathology order placement
ระบบห้องปฏิบัติการกลาง	lab_orders, specimens	Specimen sharing และ coordination
ระบบรังสีวิทยา	radiology_orders, imaging_studies	Correlative imaging studies
ระบบการเงิน	billing_items, insurance_claims	Pathology service billing

Key Benefits of Integration

✅ Seamless CPOE Integration: AI-assisted pathology ordering through unified medical orders system
✅ Digital Pathology Excellence: Complete image management with DICOM support
✅ External Lab Connectivity: HL7/FHIR integration for seamless result exchange
✅ Quality Assurance: Built-in CAP and ISO 15189 compliance tracking
✅ Real-time Specimen Tracking: Barcode-based chain of custody
✅ Advanced AI Learning: Continuous improvement of AI parsing accuracy
✅ Comprehensive Reporting: Structured and synoptic reporting capabilities
✅ Clinical Decision Support: Integration with risk assessment and guidelines

The pathology schema provides a complete, CAP-compliant digital pathology platform that seamlessly integrates with the MediTech HIS ecosystem while supporting advanced features like AI-assisted ordering, digital microscopy, and external laboratory connectivity.

---

PERFORMANCE OPTIMIZATION STRATEGY

Partitioning Strategy for High-Volume Tables

-- Partition pathology_results by year for performance
CREATE TABLE pathology_results_template (LIKE pathology_results INCLUDING ALL);

-- Yearly partitions for pathology results
CREATE TABLE pathology_results_2025 PARTITION OF pathology_results
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE pathology_results_2026 PARTITION OF pathology_results
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Monthly partitions for specimen tracking (high frequency)
CREATE TABLE specimen_tracking_template (LIKE specimen_tracking INCLUDING ALL);

CREATE TABLE specimen_tracking_2025_09 PARTITION OF specimen_tracking
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

Search and Analytics Optimization

-- Full-text search for pathology results
CREATE INDEX idx_pathology_results_fulltext 
ON pathology_results USING gin(
    to_tsvector('english', primary_diagnosis || ' ' || 
                COALESCE(microscopic_description, '') || ' ' || 
                COALESCE(gross_description, ''))
);

-- Composite indexes for common query patterns
CREATE INDEX idx_pathology_complex_search 
ON pathology_orders(patient_id, order_type, anatomical_site, created_at DESC);

CREATE INDEX idx_specimen_workflow_tracking 
ON pathology_specimens(specimen_status, processing_priority, collection_date_time DESC);

---

สรุป: Database Schema สำหรับระบบงานพยาธิวิทยานี้ได้รับการออกแบบให้รองรับ workflow ครบวงจรตั้งแต่การส่งตรวจผ่าน CPOE AI Assist ไปจนถึงการรายงานผลและ Digital Pathology Integration โดยปฏิบัติตามมาตรฐาน CAP, ISO 15189 และกฎระเบียบการคุ้มครองข้อมูลส่วนบุคคล พร้อมทั้งรองรับการเชื่อมต่อกับห้องพยาธิวิทยาภายนอกและระบบ AI Learning สำหรับการพัฒนาอย่างต่อเนื่อง