-- ==========================================
-- 1. GLOBAL INFRASTRUCTURE & DIMENSION TABLES
-- ==========================================
CREATE TABLE 
  jurisdictions (
    jurisdiction_id SERIAL PRIMARY KEY, 
    jurisdiction_code VARCHAR(10) NOT NULL UNIQUE, 
    -- 'US-NY', 'GB-ENG', 'JP'
    region_name VARCHAR(100) NOT NULL
  ); 
CREATE TABLE 
  facilities (
    facility_id SERIAL PRIMARY KEY, 
    facility_code VARCHAR(50) NOT NULL UNIQUE, 
    -- 'HOSPITAL_A', 'CLINIC_B'
    facility_name VARCHAR(150) NOT NULL
  ); 
CREATE TABLE 
  specialties (
    specialty_id SERIAL PRIMARY KEY, 
    specialty_name VARCHAR(100) NOT NULL UNIQUE 
    -- 'Cardiology', 'Ophthalmology'
    ); 
CREATE TABLE 
  personnel (
    personnel_id SERIAL PRIMARY KEY, 
    personnel_description TEXT, 
    auth_provider_uid VARCHAR(100) NOT NULL UNIQUE 
    -- Links to system OAuth/IAM ID
    ); 
-- ==========================================
-- 2. CANONCIAL CORE CONCEPTS
-- ==========================================
CREATE TABLE 
  concept_namespaces (
    namespace_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    standard_type VARCHAR(50) NOT NULL, 
    -- 'SNOMED', 'ICD-10', 'LOINC', 'FHIR', 'CUSTOM'
    public_standard BOOLEAN DEFAULT TRUE, 
    -- True = Public, False = Private/Internal
    is_external_private BOOLEAN DEFAULT FALSE, 
    -- Isolates imported external custom schemas
    external_private_source VARCHAR(100), 
    -- Identifies the originating tenant (e.g., 'HOSPITAL_A')
    about TEXT, 
    link VARCHAR(500), 
    -- API ENDPOINT: Production ready API to return the concept display string or metadata given a code
    -- Ex: example.com/resolve?code={code}
    api_url VARCHAR(8192), 
    -- Supports dynamic query construction with parameter placeholders (e.g., '{code}') for flexible API integrations
    api_url_params JSONB, 
    -- A payload of data being sent the API for processing
    api_request_body JSONB, 
    -- Captures the specific path within the API response body where the display string can be found (e.g. 'abc.results[0].text')
    api_response_display_path VARCHAR(500), 
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      -- Constraint ensures a specific vocabulary authority vector is logged exactly once
      CONSTRAINT uq_namespace_authority UNIQUE (
        standard_type, is_external_private, 
        external_private_source
      )
  ); 
CREATE TABLE 
  canonical_concepts (
    concept_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    namespace_id UUID NOT NULL REFERENCES concept_namespaces(namespace_id) ON DELETE RESTRICT, 
    standard_code VARCHAR(100) NOT NULL, 
    -- The unchanging numeric code coordinate (e.g., 'R06.02')
    display VARCHAR(150) NOT NULL, 
    -- Official fallback string definition at this timestamp
    concept_date_designation TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      -- Captures semantic mapping adjustments
      -- Composite unique key tracks semantic code drift dynamically over time within its authority space
      CONSTRAINT uq_versioned_concept UNIQUE (
        namespace_id, standard_code, concept_date_designation
      )
  ); 
CREATE INDEX 
  idx_concepts_traversal_speed ON canonical_concepts (
    namespace_id, standard_code, concept_date_designation DESC
  ); CREATE TYPE concept_translator_relation ENUM (
  'EQUIVALENT', 'NARROWER_THAN', 'WIDER_THAN'
); 
CREATE TABLE 
  concept_translator (
    link_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    concept_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    linked_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    -- 'EQUIVALENT', 'NARROWER_THAN', 'WIDER_THAN'
    relationship_type concept_translator_relation NOT NULL DEFAULT 'EQUIVALENT', 
    active BOOLEAN DEFAULT TRUE, 
    -- Track exactly when this mapping consensus became valid
    mapping_date_designation TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      CONSTRAINT chk_prevent_self_loop CHECK (concept_id <> linked_id), 
      -- Prevent exact duplicate mapping assertions within the same point in time
      UNIQUE(
        concept_id, linked_id, relationship_type, 
        mapping_date_designation
      )
  ); 
CREATE INDEX 
  idx_concept_translator_temporal ON concept_translator (
    concept_id, mapping_date_designation, 
    active
  ); 
CREATE TABLE 
  concept_translator_cache (
    cache_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    -- The starting anchor node (e.g., Your proprietary custom code)
    ancestor_concept_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    -- The terminal reachable node (e.g., A global SNOMED or ICD-10 standard code)
    descendant_concept_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    -- 1 = Direct link, 2 = Parent-to-Grandchild link, 3 = Great-Grandchild, etc.
    link_depth INTEGER NOT NULL CHECK (link_depth >= 1), 
    -- Inherited structural logic path tracking from the path chain
    -- e.g., If any link in the path is 'NARROWER_THAN', the global traversal classification updates
    inferred_relationship_type concept_translator_relation NOT NULL DEFAULT 'EQUIVALENT', 
    -- Mirroring the point in time this entire computed track matrix matches validation rules
    mapping_date_designation TIMESTAMP 
    WITH 
      TIME ZONE NOT NULL, 
      active BOOLEAN DEFAULT TRUE, 
      -- Enforce uniqueness of a specific path vector instance at a single coordinate in time
      UNIQUE(
        ancestor_concept_id, descendant_concept_id, 
        inferred_relationship_type, mapping_date_designation
      )
  ); 
-- Compound index optimized for immediate inheritance evaluation during a backward ledger step
CREATE INDEX 
  idx_concept_cache_traversal ON concept_translator_cache (
    ancestor_concept_id, mapping_date_designation, 
    active
  ) INCLUDE (
    descendant_concept_id, link_depth, 
    inferred_relationship_type
  ); 
CREATE TABLE 
  concept_display_registry (
    display_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    concept_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    -- ISO 639-1 or RFC 5646 codes (e.g., 'en', 'en-US', 'ja', 'es-MX')
    language_code VARCHAR(10) NOT NULL DEFAULT 'en', 
    -- The target human-readable string projection
    display_string TEXT NOT NULL, 
    -- Toggle True/False during usage instead of hardcoding inside display string
    reference_standard BOOLEAN DEFAULT FALSE, 
    reference_code BOOLEAN DEFAULT FALSE, 
    -- Architecture Layering: Allows for system defaults vs custom localized variants
    scope_tier VARCHAR(30) NOT NULL DEFAULT 'GLOBAL', 
    -- 'GLOBAL', 'CUSTOM_1', 'FACILITY', etc
    -- Priority weighting for localized string lookups when multiple matches exist
    preference_weight INTEGER DEFAULT 1, 
    active BOOLEAN DEFAULT TRUE
  ); 
-- ==========================================
-- 3. JURISDICTIONAL INTERSECTION
-- ==========================================
CREATE TABLE 
  concept_jurisdictional_displays (
    display_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    concept_id UUID REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    jurisdiction_id INTEGER REFERENCES jurisdictions(jurisdiction_id), 
    preferred_display TEXT NOT NULL, 
    fully_specified_name TEXT NOT NULL, 
    UNIQUE(concept_id, jurisdiction_id)
  ); 
-- ==========================================
-- 4. CDSL EXPRESSIONS & PARSER CONFIG
-- ==========================================
CREATE TYPE expression_target_assignment AS ENUM (
  'MAIN_TERM', 'ATTRIBUTE_MODIFIER', 
  'BOTH'
); 
CREATE TABLE 
  custom_expressions (
    expression_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    personnel_id INTEGER REFERENCES personnel(personnel_id), 
    expression_name VARCHAR(100) NOT NULL, 
    -- 'SOB', 'usual air problem', 'progressive'
    regex_pattern TEXT NOT NULL, 
    -- Engine regex pattern. Should not encode numerical values
    is_case_insensitive BOOLEAN DEFAULT TRUE, 
    -- Crucial Split: Tells the parser exactly where to route this token in the schema
    -- 'MAIN_TERM': maps to observation/vitals concept node
    -- 'ATTRIBUTE_MODIFIER': maps to trajectory, qualifiers, etc
    -- 'BOTH': is a MAIN_TERM only when no other MAIN_TERMS exist, else it is an ATTRIBUTE
    target_assignment expression_target_assignment NOT NULL DEFAULT 'MAIN_TERM', 
    concept_id UUID REFERENCES canonical_concepts(concept_id), 
    facility_id INTEGER REFERENCES facilities(facility_id), 
    specialty_id INTEGER REFERENCES specialties(specialty_id), 
    priority_weight INTEGER DEFAULT 1, 
    active BOOLEAN DEFAULT TRUE, 
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW()
  ); 
CREATE INDEX 
  idx_expressions_parser_route ON custom_expressions (
    expression_name, target_assignment, 
    active
  ); 
-- ==========================================
-- 5. THE SYSTEM TAGGING MATRIX
-- ==========================================
CREATE TABLE 
  expression_tags (
    tag_id VARCHAR(50) PRIMARY KEY, 
    -- 'anatomy', 'symptom', 'drug'
    description TEXT
  ); 
CREATE TABLE 
  expression_tag_matrix (
    expression_id UUID REFERENCES custom_expressions(expression_id) ON DELETE CASCADE, 
    tag_id VARCHAR(50) REFERENCES expression_tags(tag_id), 
    PRIMARY KEY (expression_id, tag_id)
  ); 
-- ==========================================
-- 6. HISTORY TRACKING
-- ==========================================
CREATE TABLE 
  expression_resolution_counters (
    counter_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    expression_id UUID NOT NULL REFERENCES custom_expressions(expression_id) ON DELETE CASCADE, 
    concept_id UUID NOT NULL REFERENCES canonical_concepts(concept_id) ON DELETE CASCADE, 
    -- Hierarchical Context Routing Tiers (Nullable to allow fallback resolution profiling)
    facility_id INTEGER REFERENCES facilities(facility_id) ON DELETE 
    SET 
      NULL, 
      specialty_id INTEGER REFERENCES specialties(specialty_id) ON DELETE 
    SET 
      NULL, 
      personnel_id INTEGER REFERENCES personnel(personnel_id) ON DELETE 
    SET 
      NULL, 
      -- The Atomic Accumulator
      usage_count BIGINT NOT NULL DEFAULT 0 CHECK (usage_count >= 0), 
      -- Tracking the lifecycle of the expression trend
      last_resolved_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      -- Unique composite coordinate matrix ensures one tracking row per unique context intersection
      CONSTRAINT uq_resolution_context_metric UNIQUE(
        expression_id, concept_id, facility_id, 
        specialty_id, personnel_id
      )
  ); 
-- Indexing optimized for the context-autocomplete engine to grab the top-weighted suggestions instantly
CREATE INDEX 
  idx_resolution_weights_speed ON expression_resolution_counters (
    expression_id, specialty_id, facility_id, 
    usage_count DESC
  ) INCLUDE (concept_id); 
-- LEVEL 1: Root Ledger Channels (Top-level polymorphic JSONB columns)
CREATE TYPE ledger_level1_channel AS ENUM (
  'assessment', 'vitals_measurement', 
  'observation', 'exposure_profile', 
  'device_diagnostic_report', 'clinical_evaluation_report', 
  'procedure_order', 'medication_order', 
  'insurance_cost_evaluation', 'clinical_location_followup', 
  'patient_refusal', 'static_context', 
  'temporal_context'
); 
-- LEVEL 2: Macro Context Containers
CREATE TYPE ledger_level2_context AS ENUM (
  -- Core Situational Frameworks (Nests inside temporal_context)
  'temporal_location', 
  -- Maps to temporal_context.location (spatial_type, jurisdiction, setting)
  'environmental_weather', 
  -- Maps to temporal_context.weather (meteo stats, AQI, severe phenomena)
  -- Core Material Frameworks (Nests inside static_context)
  'protective_equipment', 
  -- Maps to static_context.ppe_gear (gear category, post-event integrity)
  -- Clinical Context Sub-Profiles / Complex Bundles
  'subject_of_record', 
  -- Maps to state_metadata.subject_of_record (primary patient vs proxy relative switches)
  'session_variables_log', 
  -- Maps to state_metadata.session_variables (CDSL runtime tracking block instances)
  'merge_resolution_topology', 
  -- Maps to state_metadata.merge_resolution (graph reconciliation paths)
  'algorithmic_evaluation', 
  -- Maps to assessment.algorithmic_results (input concept array + raw/scalar/boolean output payloads)
  -- Exposure Profile Target Profiles
  'nutritional_exposure', 
  -- Maps to exposure_profile.nutritional_profile (dietary substance allergens + quantities)
  'biological_exposure', 
  -- Maps to exposure_profile.biological_exposure_profile (vectors, taxonomy species, toxicity markers)
  'mechanical_injury', 
  -- Maps to exposure_profile.mechanical_injury_profile (blunt, penetrating, avulsion mechanism switches)
  'ballistic_profile', 
  -- Maps to exposure_profile.mechanical_injury_profile.ballistic_profile (weapon platform, caliber, deformation keys)
  'injury_fall_details', 
  -- Maps to exposure_profile.mechanical_injury_profile.fall_details (height vectors, freefall flags)
  -- Administrative Evaluation Triggers
  'clinical_rule_conflict', 
  -- Maps to clinical_evaluation_report (drug conflicts, threshold violations, overrides)
  'insurance_financial_breakdown', 
  -- Maps to insurance_cost_evaluation.financial_breakdown (allowables, copays, OOP responsibility)
  'insurance_accumulator' 
  -- Maps to insurance_cost_evaluation.accumulator_impact (deductible and max-out balances)
  ); 
-- LEVEL 3: Standard Object Components (Global reusable $defs structures)
CREATE TYPE ledger_level3_def AS ENUM (
  'source_type', 'certainty', 'status', 
  'associated_agent', 'temporal_boundary', 
  'time', 'date_range', 'single_measurement', 
  'measurement', 'product_identifiers', 
  'anatomy_locations', 'vehicle', 
  'pharmaceutical'
); CREATE TYPE expression_tier_level AS ENUM (
  'LEVEL_1', 'LEVEL_2', 'LEVEL_3', 'LEVEL_4', 
  'LEVEL_5'
); 
CREATE TABLE 
  expression_hierarchical_routes (
    route_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    expression_id UUID NOT NULL REFERENCES custom_expressions(expression_id) ON DELETE CASCADE, 
    -- The runtime operational level definition
    tier_level expression_tier_level NOT NULL, 
    -- Level targets (Mutually exclusive or strictly cascaded based on constraints)
    target_level1 ledger_level1_channel, 
    target_level2 ledger_level2_context, 
    target_level3 ledger_level3_def, 
    --  Targets a subfield within the JSONB structure defined by the level 1-3 targets (e.g., 'ballistic_profile.firearm_or_ordnance')
    target_level4 VARCHAR(512), 
    target_level5 VARCHAR(512), 
    active BOOLEAN DEFAULT TRUE, 
    ); 
-- Create a composite execution index optimized to instantly pull the routing path definition
-- Highly optimized for query engine execution plans
CREATE INDEX 
  idx_expression_compiler_path ON expression_hierarchical_routes (
    expression_id, active, tier_level
  ) INCLUDE (
    target_level1, target_level2, target_level3, 
    target_level4, target_level5
  ); 

-- ==========================================
-- SCALED CASCADING CONTEXT REGISTRY
-- ==========================================

CREATE TYPE configuration_data_type AS ENUM (
    'int', 'float', 'string', 'bool', 'json', 'uuid'
);

CREATE TABLE hierarchical_configuration_registry (
    config_key VARCHAR(100) NOT NULL,            -- 'DEFAULT_SEVERITY_SCALE', 'DEFAULT_TEMP_UNIT'
    config_type configuration_data_type NOT NULL,-- 'int', 'float', 'string', 'bool', 'json'
    config_value TEXT NOT NULL,                  -- The raw string value to pass to the type caster
    
    -- Cascading Scoping Matrix Tiers (All nullable to allow fallback routing execution)
    jurisdiction_id INTEGER REFERENCES jurisdictions(jurisdiction_id) ON DELETE CASCADE,
    facility_id INTEGER REFERENCES facilities(facility_id) ON DELETE CASCADE,
    specialty_id INTEGER REFERENCES specialties(specialty_id) ON DELETE CASCADE,
    
    description TEXT,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Prevents duplicate rules at the exact same architectural resolution layer
    CONSTRAINT uq_scoped_config_coordinate UNIQUE (
        config_key, jurisdiction_id, facility_id, specialty_id
    )
);

-- Index optimized for the cascading compiler fallback matching engine
CREATE INDEX idx_config_inheritance_speed ON hierarchical_configuration_registry (
    config_key, specialty_id, facility_id, jurisdiction_id
);
-- ==========================================
-- 1. TEMPLATE REGISTRY (Metadata Wrappers)
-- ==========================================
CREATE TABLE 
  note_templates (
    template_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    template_code VARCHAR(100) NOT NULL UNIQUE, 
    -- 'T-CARDIO-SOAP-01', 'T-PEDS-HPI-02'
    description TEXT, 
    specialty_id INTEGER REFERENCES specialties(specialty_id), 
    -- Optional domain restriction
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW()
  ); 
-- ==========================================
-- 2. TEMPLATE VERSIONS (Timeline Control)
-- ==========================================
CREATE TABLE 
  template_versions (
    version_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    template_id UUID REFERENCES note_templates(template_id) ON DELETE CASCADE, 
    semantic_version VARCHAR(20) NOT NULL, 
    -- '1.0.0', '1.1.0'
    is_active BOOLEAN DEFAULT FALSE, 
    -- Controls runtime loading
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      UNIQUE(template_id, semantic_version)
  ); 
-- ==========================================
-- 3. TEMPLATE SLOTS (The Jinja Render Blocks)
-- ==========================================
CREATE TABLE 
  template_slots (
    slot_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    language_id VARCHAR(5) DEFAULT 'en', 
    version_id UUID REFERENCES template_versions(version_id) ON DELETE CASCADE, 
    document_location VARCHAR(50) NOT NULL, 
    -- 'subjective', 'objective', 'assessment', 'plan', 'full_document', etc
    slot_key VARCHAR(50) NOT NULL, 
    -- 'opening', 'continuing', 'closing', 'full_paragraph'
    jinja_template_text TEXT NOT NULL, 
    -- The actual string rendering syntax
    UNIQUE(version_id, slot_key)
  ); 
CREATE TABLE 
  template_expression_matrix (
    version_id UUID REFERENCES template_versions(version_id) ON DELETE CASCADE, 
    expression_id UUID REFERENCES custom_expressions(expression_id) ON DELETE CASCADE, 
    PRIMARY KEY (version_id, expression_id)
  ); 
-- ==========================================
-- 1. MACRO REGISTRY
-- ==========================================
CREATE TABLE 
  macro_registry (
    macro_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    personnel_id INTEGER REFERENCES personnel(personnel_id), 
    macro_name VARCHAR(100) NOT NULL, 
    -- 'severe_shortness_of_breath', 'high_fever', etc
    macro_template TEXT NOT NULL, 
    -- The template string with variable placeholders, and variable delimiters, independent of parser syntax
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      UNIQUE(macro_name)
  ); 
-- ==========================================
-- 1. PARSER PROFILES
-- ==========================================
CREATE TABLE 
  parser_syntax_profiles (
    profile_id UUID PRIMARY KEY, 
    personnel_id INTEGER REFERENCES personnel(personnel_id), 
    -- [TAGTOKEN]: Delimiter to identify the start of a tagged expression (e.g., '#drug', '#anatomy') 
    tag_token VARCHAR(5) DEFAULT '#' 
    -- [STATE_DELIMITER]: Delimiter identity a new state
    state_delimiter VARCHAR(5) DEFAULT '||',
    
    -- 1. [STATE_START] Outer Delimiter (e.g. '|' or '<' or '##')
    state_start_delimiter VARCHAR(5) DEFAULT '|', 
    -- [STATE_END] Closing Delimiter (e.g. '|' or '>' or '##')
    state_end_delimiter VARCHAR(5) DEFAULT '|', 
    
    -- '|' or '>' or '##', etc
    -- 2. [COMMENT]: Comment tokens 
    comment_start_token VARCHAR(5) DEFAULT '//', 
    comment_end_token VARCHAR(5) DEFAULT ';', 
    -- 3. Macro Tokens (ex: ^macro($1, $2) -> @term; severity of $1, /set custom_vars{x=$2})
    macro_start_token VARCHAR(5) DEFAULT '^', 
    -- [MACRO_PLACEHOLDER]: 'Triggers a macro to prefill text string and inputs'
    macro_placeholder VARCHAR(5) DEFAULT '[__]', 
    -- 'Opens a variable fill block within the evaluated macro string if not defined'
    -- 4. [VAR_START]: Variable Assignment tokens. Opens a variable evaluation block
    variable_start_token VARCHAR(5) DEFAULT '{', 
    -- [VAR_END]: Closes a variable evaluation block
    variable_end_token VARCHAR(5) DEFAULT '}', 
    -- [VAR_DELIMITER]: Delimiter between multiple variable assignments within the block
    variable_delimiter VARCHAR(5) DEFAULT ',', 
    -- Delimiter between multiple variable assignments within the block
    --  (name{x=10,y=True,z>5,a->{1,2,3, @SOB}, c={e,f,g}})
    -- Ex: test{x=10,y=True} -> eval test{x=5} -> error; eval a=5 -> error
    -- Does not need to be Turing Complete; does not perform mathematical operations on the RHS, meant for clincian expectations
    ---
    ---
    -- Term Tokens (custom expressions or display names) 
    -- 5. [TERM_CODE]: '@@ICD-10#R06.02' 
    start_term_code_delimiter VARCHAR(5) DEFAULT '@@', 
    -- [TERM_DISPLAY]: '@#R06.02
    start_term_display_delimiter VARCHAR(5) DEFAULT '@#', 
    -- [TERM_CODE_SEPARATOR]: Delimiter between the code system and the code in the inline specification (e.g., '@@SNOMED#123456')
    start_term_code_separator VARCHAR(5) DEFAULT '#', 
    -- [TERM]: '~', '@' etc (@SOB, @Shortness of Breath, etc)
    start_term_delimiter VARCHAR(5) DEFAULT '@', 
    -- [TERM_END]: Delimiter to signify the end of a term token (e.g., ';' or ',', ';;' etc)
    end_term_delimiter VARCHAR(5) DEFAULT ';', 
    -- 
    -- 6. [ATTRIBUTE]: Attribute Tokens (e.g. ',' or ';' )
    attribute_delimiter VARCHAR(5) DEFAULT ',', 
    --
    -- Checks if the profile is active  
    is_active BOOLEAN DEFAULT TRUE, 
    -- Constraint to prevent delimiter collisions that would break the parsing logic
    CONSTRAINT chk_bulletproof_lexer_boundaries CHECK (
      -- Protect global line blocks from colliding with inline node tags
      state_delimiter <> state_start_delimiter
      AND state_delimiter <> state_end_delimiter
      
      -- Protect elements from matching core syntax tags
      AND state_start_delimiter <> start_term_delimiter
      AND variable_start_token <> state_start_delimiter
      AND start_term_code_delimiter <> start_term_delimiter 
      AND start_term_display_delimiter <> start_term_delimiter 
      AND start_term_code_delimiter <> start_term_display_delimiter 
      
      -- Ensure variable assignment blocks do not collide with state blocks or term tags
      AND variable_start_token <> state_start_delimiter 
      AND variable_end_token <> state_end_delimiter 
      
      -- Prevent comment wrappers from matching macro tags
      AND comment_start_token <> macro_start_token
    )
); 
-- ==========================================================
-- 2. PARSER STOP WORDS
-- ==========================================================
CREATE TABLE 
  parser_stop_words (
    stop_word_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
    -- The literal token to strip during lexing (e.g., 'patient', 'reports', 'with')
    word_token VARCHAR(100) NOT NULL, 
    -- Hierarchical Scoping Tiers (Cascading Nullable Foreign Keys)
    jurisdiction_id INTEGER REFERENCES jurisdictions(jurisdiction_id) ON DELETE CASCADE, 
    facility_id INTEGER REFERENCES facilities(facility_id) ON DELETE CASCADE, 
    specialty_id INTEGER REFERENCES specialties(specialty_id) ON DELETE CASCADE, 
    personnel_id INTEGER REFERENCES personnel(personnel_id) ON DELETE CASCADE, 
    is_active BOOLEAN DEFAULT TRUE, 
    created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW(), 
      -- Enforce uniqueness of a stop word within a specific contextual silo
      CONSTRAINT uq_stop_word_context UNIQUE (
        word_token, jurisdiction_id, facility_id, 
        specialty_id, personnel_id
      )
  ); 
-- Indexing optimized for instant hash-table loading during IDE session initialization
CREATE INDEX 
  idx_stop_words_session_load ON parser_stop_words (
    personnel_id, specialty_id, facility_id, 
    jurisdiction_id
  ) 
WHERE 
  is_active = TRUE; 
-- ==========================================================
-- 1. THE IDENTITY MATRIX
-- ==========================================================
CREATE TYPE organism_domain AS ENUM ('human', 'animal', 'plant'); CREATE TYPE agent_lifecycle_status AS ENUM (
  'active', 'deceased', 'inactive_archived'
); CREATE TYPE gender_classification AS ENUM (
  'male', 'female', 'undetermined', 
  'not_applicable'
); CREATE TYPE date_precision_tier AS ENUM (
  'exact_timestamp', 'day', 'month', 
  'year', 'estimated_epoch'
); CREATE TYPE ledger_mutation_operation AS ENUM (
  'add', 'update', 'remove', 'merge', 
  'branch', 'archive'
); CREATE TYPE clinical_doc_section AS ENUM (
  'initial_context', 'subjective', 
  'objective', 'assessment', 'plan', 
  'other'
); CREATE TYPE clinical_workflow_stage AS ENUM (
  'history_taking', 'observation_gathering', 
  'measurement_recording', 'assessment_reasoning', 
  'plan_formulation', 'intervention_ordering', 
  'disposition_planning', 'other'
); 
CREATE TABLE 
  registry_agents (
    agent_id VARCHAR(2048) PRIMARY KEY, 
    initial_state_id VARCHAR(2048) DEFAULT NULL, 
    organism_type organism_domain NOT NULL, 
    status agent_lifecycle_status NOT NULL DEFAULT 'active', 
    registration_timestamp TIMESTAMP 
    WITH 
      TIME ZONE NOT NULL DEFAULT NOW(), 
      created_at TIMESTAMP 
    WITH 
      TIME ZONE DEFAULT NOW()
  ); 
CREATE INDEX 
  idx_agents_cohort_lookup ON registry_agents (organism_type, status); 
CREATE TABLE 
  agent_demographics (
    agent_id VARCHAR(2048) PRIMARY KEY REFERENCES registry_agents(agent_id) ON DELETE CASCADE, 
    administrative_gender gender_classification NOT NULL DEFAULT 'undetermined', 
    origination_date TIMESTAMP 
    WITH 
      TIME ZONE NOT NULL, 
      origination_precision date_precision_tier NOT NULL DEFAULT 'exact_timestamp', 
      is_origination_date_estimated BOOLEAN NOT NULL DEFAULT FALSE, 
      legal_name_json JSONB NOT NULL, 
      organism_attributes JSONB NOT NULL, 
      organism_type organism_domain NOT NULL, 
      FOREIGN KEY (agent_id, organism_type) REFERENCES registry_agents(agent_id, organism_type) ON DELETE CASCADE, 
      CONSTRAINT chk_strict_organism_payloads CHECK (
        (
          organism_type = 'plant' 
          AND organism_attributes ? 'taxonomy' 
          AND organism_attributes ? 'propagation_method'
        ) 
        OR (
          organism_type IN ('human', 'animal') 
          AND administrative_gender IS NOT NULL
        )
      )
  ); 
-- ==========================================================
-- 6. THE CENTRAL EVENT LEDGER LAYER
-- ==========================================================
CREATE TABLE 
  medical_event_ledger (
    state_id VARCHAR(2048) PRIMARY KEY, 
    previous_state_id VARCHAR(2048) REFERENCES medical_event_ledger(state_id), 
    mutation_parent_ids VARCHAR(2048) [] DEFAULT '{}', 
    -- Tracks multi-parent histories (Merges)
    operation ledger_mutation_operation NOT NULL, 
    -- Transaction Provenance
    state_context TEXT, 
    state_timestamp_utc TIMESTAMP 
    WITH 
      TIME ZONE NOT NULL DEFAULT NOW(), 
      agent_id VARCHAR(2048) NOT NULL REFERENCES registry_agents(agent_id), 
      personnel_id INTEGER REFERENCES personnel(personnel_id), 
      facility_id INTEGER REFERENCES facilities(facility_id), 
      clinical_document_location clinical_doc_section NOT NULL DEFAULT 'other', 
      clinical_workflow_phase clinical_workflow_stage NOT NULL DEFAULT 'other', 
      state_metadata_session_variables JSONB DEFAULT NULL, 
      merge_resolution JSONB DEFAULT NULL, 
      static_context JSONB DEFAULT NULL, 
      temporal_context JSONB DEFAULT NULL, 
      -- Clinical Streams
      assessment JSONB DEFAULT NULL, 
      vitals_measurement JSONB DEFAULT NULL, 
      observation JSONB DEFAULT NULL, 
      exposure_profile JSONB DEFAULT NULL, 
      device_diagnostic_report JSONB DEFAULT NULL, 
      clinical_evaluation_report JSONB DEFAULT NULL, 
      procedure_order JSONB DEFAULT NULL, 
      medication_order JSONB DEFAULT NULL, 
      insurance_cost_evaluation JSONB DEFAULT NULL, 
      clinical_location_followup JSONB DEFAULT NULL, 
      patient_refusal JSONB DEFAULT NULL, 
      -- ==========================================================
      -- CONSTRAINT ENFORCEMENT AT THE DATABASE PORTAL
      -- ==========================================================
      -- Enforce that merge details exist if and only if the operation type is a merge
      CONSTRAINT chk_merge_data_integrity CHECK (
        (
          operation = 'merge' 
          AND merge_resolution IS NOT NULL
        ) 
        OR (
          operation <> 'merge' 
          AND merge_resolution IS NULL
        )
      ), 
      -- Enforce that if the operation is an update, the previous_state_id must exist, mutation_parent_ids must not be empty
      CONSTRAINT chk_update_requires_previous CHECK (
        (
          operation = 'update' 
          AND previous_state_id IS NOT NULL 
          AND array_length(mutation_parent_ids, 1) > 0
        ) 
        OR (operation <> 'update')
      ), 
      -- Enforce that if the operation is a remove, the previous_state_id must exist, mutation_parent_ids must not be empty
      CONSTRAINT chk_remove_requires_previous_and_null_context CHECK (
        (
          operation = 'remove' 
          AND previous_state_id IS NOT NULL 
          AND array_length(mutation_parent_ids, 1) > 0
        ) 
        OR (operation <> 'remove')
      )
  ); 
CREATE INDEX 
  idx_ledger_agent_timeline ON medical_event_ledger (
    agent_id, state_timestamp_utc DESC
  ); 
ALTER TABLE 
  registry_agents 
ADD 
  CONSTRAINT fk_initial_state_ledger FOREIGN KEY (initial_state_id) REFERENCES medical_event_ledger(state_id);