apply_omop_views.sh

#!/bin/bash
#
# Apply OMOP Views to UMLS DuckDB
# Creates OMOP CDM-compatible view names in umls_omop schema
#
# Usage: ./apply_omop_views.sh
#

set -e

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
OUTPUT_DB="${SCRIPT_DIR}/umls.duckdb"
VIEWS_SQL="${SCRIPT_DIR}/create_omop_views.sql"
LOG_FILE="${SCRIPT_DIR}/omop_views.log"
DUCKDB_CMD="/opt/homebrew/bin/duckdb"

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# Colored output functions
log_info() {
    echo -e "${BLUE}ℹ️  $1${NC}" | tee -a "$LOG_FILE"
}

log_success() {
    echo -e "${GREEN}✅ $1${NC}" | tee -a "$LOG_FILE"
}

log_warning() {
    echo -e "${YELLOW}⚠️  $1${NC}" | tee -a "$LOG_FILE"
}

log_error() {
    echo -e "${RED}❌ $1${NC}" | tee -a "$LOG_FILE"
}

# Check prerequisites
check_prerequisites() {
    log_info "Checking prerequisites..."
    
    if [ ! -f "$DUCKDB_CMD" ]; then
        log_error "DuckDB CLI not found at $DUCKDB_CMD"
        log_info "Please install DuckDB: brew install duckdb"
        exit 1
    fi
    
    if [ ! -f "$OUTPUT_DB" ]; then
        log_error "UMLS database not found: $OUTPUT_DB"
        log_info "Please run ./convert_umls_to_duckdb.sh first"
        exit 1
    fi
    
    if [ ! -f "$VIEWS_SQL" ]; then
        log_error "OMOP views SQL file not found: $VIEWS_SQL"
        exit 1
    fi
    
    log_success "Prerequisites check passed"
}

# Apply OMOP views
apply_views() {
    log_info "Creating OMOP CDM-compatible views..."
    
    # Initialize log
    echo "Creating OMOP views at $(date)" > "$LOG_FILE"
    
    # Apply the views SQL
    "$DUCKDB_CMD" "$OUTPUT_DB" < "$VIEWS_SQL" >> "$LOG_FILE" 2>&1
    
    if [ $? -eq 0 ]; then
        log_success "OMOP views created successfully"
    else
        log_error "Failed to create views. Check $LOG_FILE for details"
        exit 1
    fi
}

# Show view summary
show_summary() {
    log_info "Generating OMOP views summary..."
    
    echo ""
    echo "===================== UMLS OMOP VIEWS ====================="
    
    # Get list of views in omop schema
    "$DUCKDB_CMD" "$OUTPUT_DB" << 'EOF'
SELECT 
    view_name,
    CASE 
        WHEN view_name LIKE 'concept%' THEN 'Vocabulary'
        WHEN view_name LIKE 'relationship%' OR view_name LIKE 'concept_relationship%' THEN 'Relationships'
        WHEN view_name LIKE 'concept_synonym%' THEN 'Synonyms'
        WHEN view_name LIKE 'concept_ancestor%' THEN 'Hierarchies'
        WHEN view_name LIKE 'vocabulary%' OR view_name LIKE 'domain%' OR view_name LIKE 'concept_class%' THEN 'Metadata'
        WHEN view_name LIKE 'source_to_concept_map%' THEN 'Source Mappings'
        ELSE 'Other'
    END as category
FROM duckdb_views() 
WHERE schema_name = 'umls_omop'
ORDER BY category, view_name;
EOF
    
    echo ""
    echo "============================================================"
    echo ""
    
    # Show sample queries
    echo "💡 Example OMOP CDM Queries:"
    echo ""
    echo "-- Find concepts in a specific vocabulary"
    echo "SELECT * FROM umls_omop.concept"
    echo "WHERE vocabulary_id = 'SNOMED' AND concept_name ILIKE '%diabetes%' LIMIT 10;"
    echo ""
    echo "-- Get concept relationships"
    echo "SELECT c1.concept_name as from_concept, r.relationship_id, c2.concept_name as to_concept"
    echo "FROM umls_omop.concept_relationship cr"
    echo "JOIN umls_omop.concept c1 ON cr.concept_id_1 = c1.concept_id"
    echo "JOIN umls_omop.concept c2 ON cr.concept_id_2 = c2.concept_id"
    echo "JOIN umls_omop.relationship r ON cr.relationship_id = r.relationship_id"
    echo "WHERE c1.concept_id = 'OMOP_CONCEPT_ID' LIMIT 10;"
    echo ""
    echo "-- Browse vocabularies"
    echo "SELECT vocabulary_id, vocabulary_name, vocabulary_concept_id"
    echo "FROM umls_omop.vocabulary ORDER BY vocabulary_name;"
    echo ""
    echo "-- Get concept hierarchy"
    echo "SELECT ancestor.concept_name as ancestor, descendant.concept_name as descendant, ca.min_levels_of_separation"
    echo "FROM umls_omop.concept_ancestor ca"
    echo "JOIN umls_omop.concept ancestor ON ca.ancestor_concept_id = ancestor.concept_id"
    echo "JOIN umls_omop.concept descendant ON ca.descendant_concept_id = descendant.concept_id"
    echo "WHERE ancestor.concept_id = 'OMOP_CONCEPT_ID' LIMIT 10;"
    echo ""
}

# Main function
main() {
    echo "🏥 UMLS OMOP Views Creator"
    echo "========================="
    
    log_info "Starting OMOP views creation..."
    log_info "Database: $OUTPUT_DB"
    log_info "Views SQL: $VIEWS_SQL"
    log_info "Log file: $LOG_FILE"
    
    # Run steps
    check_prerequisites
    apply_views
    show_summary
    
    log_success "🎉 OMOP views created successfully!"
    log_info "💾 Views added to: $OUTPUT_DB"
    log_info "📋 Log saved to: $LOG_FILE"
    
    echo ""
    echo "You can now query UMLS data using OMOP CDM format:"
    echo "  $DUCKDB_CMD $OUTPUT_DB"
    echo "  SELECT * FROM umls_omop.concept LIMIT 5;"
    echo ""
}

# Run main function
main "$@"