Database Design for Longitudinal Health Records

# db/migrate/[timestamp]_rename_careplans_to_encounters.rb
class RenameCareplansToEncounters < ActiveRecord::Migration[7.0]
  def change
    # Rename main table
    rename_table :careplans, :encounters
    
    # Rename foreign keys in related tables
    rename_column :careplan_complaints, :careplan_id, :encounter_id
    rename_table :careplan_complaints, :encounter_complaints
    
    rename_column :careplan_medicines, :careplan_id, :encounter_id
    rename_table :careplan_medicines, :encounter_medicines
    
    rename_column :careplan_procedures, :careplan_id, :encounter_id
    rename_table :careplan_procedures, :encounter_procedures
    
    rename_column :careplan_tests, :careplan_id, :encounter_id
    rename_table :careplan_tests, :encounter_tests
    
    rename_column :careplan_test_lists, :careplan_id, :encounter_id
    rename_table :careplan_test_lists, :encounter_test_lists
    
    rename_column :careplan_diets, :careplan_id, :encounter_id
    rename_table :careplan_diets, :encounter_diets
    
    rename_column :careplan_advices, :careplan_id, :encounter_id
    rename_table :careplan_advices, :encounter_advices
    
    # Add new columns to encounters for LHR features
    add_column :encounters, :encounter_type, :string, default: 'routine' # routine/emergency/followup/telemedicine
    add_column :encounters, :visit_type, :string # UI field: walkin/scheduled/telemedicine/home_visit
    add_column :encounters, :urgency, :string, default: 'routine' # UI field: routine/urgent/emergency  
    add_column :encounters, :chief_complaint, :text # UI field: Primary reason for visit (free text)
    add_column :encounters, :health_score_snapshot, :float # Health score at time of encounter
    # Removed: reconciliation_completed - not needed for simple continuity
    add_column :encounters, :fhir_resource_id, :string # For ABDM integration
    add_index :encounters, :fhir_resource_id
    add_index :encounters, :encounter_type
    add_index :encounters, :visit_type
    add_index :encounters, :urgency
    add_index :encounters, :health_score_snapshot
    
    # Update polymorphic references to use new naming
    execute <<-SQL
      UPDATE documents 
      SET documentable_type = 'Encounter' 
      WHERE documentable_type = 'Careplan';
      
      UPDATE activities 
      SET trackable_type = 'Encounter' 
      WHERE trackable_type = 'Careplan';
    SQL
  end
end

# db/migrate/[timestamp]_enhance_encounter_complaints_for_lhr.rb
class EnhanceEncounterComplaintsForLhr < ActiveRecord::Migration[7.0]
  def change
    # Add longitudinal tracking to existing encounter_complaints table
    add_column :encounter_complaints, :problem_status, :string, default: 'active'
    add_column :encounter_complaints, :severity_level, :string # mild, moderate, severe, critical
    add_column :encounter_complaints, :onset_date, :date
    add_column :encounter_complaints, :resolved_date, :date
    add_column :encounter_complaints, :chronic_condition, :boolean, default: false
    
    # Clinical coding support
    add_column :encounter_complaints, :icd10_code, :string
    add_column :encounter_complaints, :icd11_code, :string
    add_column :encounter_complaints, :snomed_code, :string
    add_reference :encounter_complaints, :primary_clinical_code, 
                  foreign_key: { to_table: :clinical_codes }, null: true
    
    # LHR metadata
    add_column :encounter_complaints, :affects_health_score, :boolean, default: true
    add_column :encounter_complaints, :longitudinal_notes, :jsonb, default: {}
    
    # Cross-encounter tracking
    add_column :encounter_complaints, :linked_complaint_ids, :jsonb, default: []
    add_column :encounter_complaints, :problem_trend, :string # improving, stable, worsening
    
    # Note: Removed coding confidence/validation fields for MVP - can be added in future phases
    
    # Performance indexes
    add_index :encounter_complaints, :problem_status
    add_index :encounter_complaints, :severity_level
    add_index :encounter_complaints, :chronic_condition
    add_index :encounter_complaints, :onset_date
    add_index :encounter_complaints, [:problem_status, :severity_level]
    add_index :encounter_complaints, :primary_clinical_code_id
    
    # Full-text search indexes
    add_index :encounter_complaints, :longitudinal_notes, using: :gin
    
    # Note: Enhanced existing table structure while maintaining backward compatibility
    # All existing functionality preserved, new LHR features added seamlessly
  end
end

# db/migrate/[timestamp]_enhance_encounter_medicines_for_lhr.rb
class EnhanceEncounterMedicinesForLhr < ActiveRecord::Migration[7.0]
  def change
    # Add medication continuity tracking to existing encounter_medicines
    add_column :encounter_medicines, :medication_status, :string, default: 'active'
    add_column :encounter_medicines, :prescription_type, :string # acute, chronic, prn
    add_column :encounter_medicines, :generic_name, :string
    add_column :encounter_medicines, :start_date, :date
    add_column :encounter_medicines, :end_date, :date
    add_column :encounter_medicines, :discontinuation_reason, :string
    add_column :encounter_medicines, :parent_prescription_id, :integer
    
    # Drug safety tracking
    add_column :encounter_medicines, :drug_interactions, :jsonb, default: []
    add_column :encounter_medicines, :allergy_warnings, :jsonb, default: []
    
    # Clinical coding for medications
    add_reference :encounter_medicines, :rxnorm_code, 
                  foreign_key: { to_table: :clinical_codes }, null: true
    add_reference :encounter_medicines, :generic_rxnorm_code,
                  foreign_key: { to_table: :clinical_codes }, null: true
    add_column :encounter_medicines, :ndc_code, :string # National Drug Code
    
    # Simple continuity tracking
    # Note: reconciliation fields removed - using only medication_status and prescription_type
    
    # Indexes for performance and longitudinal queries
    add_index :encounter_medicines, :medication_status
    add_index :encounter_medicines, :prescription_type
    add_index :encounter_medicines, :parent_prescription_id
    add_index :encounter_medicines, [:start_date, :end_date]
    # Removed: reconciliation_status index - not needed
    add_index :encounter_medicines, [:medication_status, :prescription_type]
    add_index :encounter_medicines, :rxnorm_code_id
    add_index :encounter_medicines, :generic_rxnorm_code_id
    add_index :encounter_medicines, :ndc_code
    
    # JSONB indexes for drug interactions
    add_index :encounter_medicines, :drug_interactions, using: :gin
    
    # Foreign key for parent prescription relationship
    add_foreign_key :encounter_medicines, :encounter_medicines, column: :parent_prescription_id
    
    # Note: Enhanced existing table while preserving all current functionality
    # New LHR features enable medication continuity across encounters
  end
end

# db/migrate/[timestamp]_create_patient_health_scores.rb
class CreatePatientHealthScores < ActiveRecord::Migration[7.0]
  def change
    create_table :patient_health_scores do |t|
      # Patient and clinic references
      t.references :patient, null: false, foreign_key: true
      t.references :clinic_patient, foreign_key: true, null: true
      t.references :encounter, foreign_key: { to_table: :encounters }, null: true
      
      # Overall and component scores (0-100 scale)
      t.float :overall_score, null: false
      t.float :vital_score
      t.float :problem_score
      t.float :medication_score
      t.float :preventive_score
      
      # Detailed breakdown stored as JSONB for flexibility
      t.jsonb :score_components, default: {}
      t.jsonb :risk_factors, default: {}
      
      # Score calculation metadata
      t.date :calculated_date, null: false
      t.string :calculation_method, default: 'standard_algorithm'
      t.text :calculation_notes
      
      # Data quality indicators
      t.integer :data_completeness_percentage # What % of data was available for calculation
      t.jsonb :missing_data_elements, default: [] # Which elements were missing
      
      t.timestamps
    end
    
    # Indexes for efficient querying
    add_index :patient_health_scores, [:patient_id, :calculated_date]
    add_index :patient_health_scores, :overall_score
    add_index :patient_health_scores, :calculated_date
    add_index :patient_health_scores, [:patient_id, :overall_score]
    add_index :patient_health_scores, :encounter_id
    
    # JSONB indexes for complex queries
    add_index :patient_health_scores, :score_components, using: :gin
    add_index :patient_health_scores, :risk_factors, using: :gin
    
    # Unique constraint to prevent duplicate scores for same patient/date
    add_index :patient_health_scores, [:patient_id, :calculated_date, :encounter_id], 
              unique: true, name: 'idx_unique_patient_health_score'
  end
end

# db/migrate/[timestamp]_create_patient_health_summaries.rb
class CreatePatientHealthSummaries < ActiveRecord::Migration[7.0]
  def change
    create_table :patient_health_summaries do |t|
      # Patient identification
      t.references :patient, null: false, foreign_key: true
      t.references :clinic_patient, foreign_key: true, null: true
      
      # Current health state snapshot
      t.float :current_health_score
      t.string :health_score_trend # improving, stable, declining
      t.date :last_health_score_date
      
      # Longitudinal health trends (JSONB for flexibility)
      t.jsonb :vital_trends, default: {} # BP trends, weight trends, etc
      t.jsonb :active_problems_summary, default: []
      t.jsonb :current_medications_summary, default: []
      # Note: Removed health_alerts field for MVP - can be added in future phases
      
      # Encounter statistics
      t.integer :total_encounters_count, default: 0
      t.date :last_encounter_date
      t.date :next_appointment_date
      t.integer :encounters_this_year, default: 0
      
      # Key longitudinal metrics
      t.jsonb :key_metrics, default: {} # Flexible storage for important metrics
      t.jsonb :chronic_conditions_summary, default: []
      t.jsonb :medication_adherence_summary, default: {}
      
      # Care quality indicators
      # Removed: medication reconciliation fields - using simple continuity instead
      t.boolean :preventive_care_up_to_date, default: false
      t.date :last_preventive_care_check
      
      # ABDM/FHIR Integration
      t.jsonb :fhir_bundle, default: {} # Complete FHIR representation
      t.datetime :last_fhir_export_date
      t.string :fhir_patient_id
      
      # Data freshness tracking
      t.datetime :summary_generated_at
      t.datetime :last_updated_from_encounter
      t.boolean :needs_recalculation, default: false
      
      t.timestamps
    end
    
    # Ensure one summary per patient
    add_index :patient_health_summaries, :patient_id, unique: true
    
    # Performance indexes
    add_index :patient_health_summaries, :current_health_score
    add_index :patient_health_summaries, :health_score_trend
    add_index :patient_health_summaries, :last_encounter_date
    add_index :patient_health_summaries, :needs_recalculation
    # Removed: medication_reconciliation_up_to_date index
    add_index :patient_health_summaries, :preventive_care_up_to_date
    
    # JSONB indexes for complex queries
    add_index :patient_health_summaries, :vital_trends, using: :gin
    add_index :patient_health_summaries, :active_problems_summary, using: :gin
    add_index :patient_health_summaries, :current_medications_summary, using: :gin
    add_index :patient_health_summaries, :fhir_bundle, using: :gin
  end
end

# db/migrate/[timestamp]_create_clinical_codes.rb
# See clinical-coding.md for complete implementation
class CreateClinicalCodes < ActiveRecord::Migration[7.0]
  def change
    create_table :clinical_codes do |t|
      # Core fields
      t.string :code_system, null: false # 'ICD10', 'ICD11', 'SNOMED_CT', 'RxNorm'
      t.string :code, null: false # The actual code (e.g., 'E11.9', '73211009')
      t.string :display_name, null: false # Human readable name
      t.text :description # Detailed description
      
      # ... (see clinical-coding.md for complete schema)
      
      t.timestamps
    end
    
    # ... (indexes defined in clinical-coding.md)
  end
end

# Note: Full medication reconciliation table removed for MVP
# Using existing encounter_medicines fields for basic continuity:
# - medication_status: active/discontinued
# - prescription_type: chronic/acute/prn
# - parent_prescription_id: links to previous prescription
# No additional tables needed for basic medication continuity

# db/migrate/[timestamp]_create_clinical_code_mappings.rb
# See clinical-coding.md for complete implementation

# db/migrate/[timestamp]_add_lhr_constraints.rb
class AddLhrConstraints < ActiveRecord::Migration[7.0]
  def change
    # Health score constraints
    execute <<-SQL
      ALTER TABLE patient_health_scores 
      ADD CONSTRAINT check_overall_score_range 
      CHECK (overall_score >= 0 AND overall_score <= 100);
      
      ALTER TABLE patient_health_scores 
      ADD CONSTRAINT check_component_scores_range 
      CHECK (
        (vital_score IS NULL OR (vital_score >= 0 AND vital_score <= 100)) AND
        (problem_score IS NULL OR (problem_score >= 0 AND problem_score <= 100)) AND
        (medication_score IS NULL OR (medication_score >= 0 AND medication_score <= 100)) AND
        (preventive_score IS NULL OR (preventive_score >= 0 AND preventive_score <= 100))
      );
    SQL
    
    # Encounter constraints
    execute <<-SQL
      ALTER TABLE encounters 
      ADD CONSTRAINT check_health_score_snapshot_range 
      CHECK (health_score_snapshot IS NULL OR (health_score_snapshot >= 0 AND health_score_snapshot <= 100));
    SQL
    
    # Date constraints for logical consistency
    execute <<-SQL
      ALTER TABLE encounter_complaints 
      ADD CONSTRAINT check_complaint_dates 
      CHECK (resolved_date IS NULL OR onset_date IS NULL OR resolved_date >= onset_date);
      
      ALTER TABLE encounter_medicines 
      ADD CONSTRAINT check_medicine_dates 
      CHECK (end_date IS NULL OR start_date IS NULL OR end_date >= start_date);
    SQL
    
  end
  
  def down
    execute "ALTER TABLE patient_health_scores DROP CONSTRAINT IF EXISTS check_overall_score_range;"
    execute "ALTER TABLE patient_health_scores DROP CONSTRAINT IF EXISTS check_component_scores_range;"
    execute "ALTER TABLE encounters DROP CONSTRAINT IF EXISTS check_health_score_snapshot_range;"
    execute "ALTER TABLE encounter_complaints DROP CONSTRAINT IF EXISTS check_complaint_dates;"
    execute "ALTER TABLE encounter_medicines DROP CONSTRAINT IF EXISTS check_medicine_dates;"
  end
end

# db/migrate/[timestamp]_add_lhr_performance_indexes.rb
class AddLhrPerformanceIndexes < ActiveRecord::Migration[7.0]
  def change
    # Composite indexes for common LHR queries
    add_index :encounters, [:patient_id, :encounter_type, :created_at], 
              name: 'idx_encounters_patient_type_date'
    add_index :encounters, [:clinic_id, :health_score_snapshot, :created_at],
              name: 'idx_encounters_clinic_score_date'
    
    # Problem tracking indexes
    add_index :encounter_complaints, [:patient_id, :problem_status, :chronic_condition],
              name: 'idx_complaints_patient_status_chronic'
    add_index :encounter_complaints, [:onset_date, :problem_status],
              name: 'idx_complaints_onset_status'
    
    # Medication continuity indexes
    add_index :encounter_medicines, [:patient_id, :medication_status, :prescription_type],
              name: 'idx_medicines_patient_status_type'
    add_index :encounter_medicines, [:medication_status, :end_date],
              name: 'idx_medicines_status_end_date'
    # Removed: reconciliation indexes - using simpler approach
    
    # Health score trend analysis
    add_index :patient_health_scores, [:patient_id, :calculated_date, :overall_score],
              name: 'idx_health_scores_patient_date_score'
    
    # Patient summary optimization
    add_index :patient_health_summaries, [:current_health_score, :health_score_trend],
              name: 'idx_summaries_score_trend'
    add_index :patient_health_summaries, [:needs_recalculation, :last_encounter_date],
              name: 'idx_summaries_recalc_encounter_date'
  end
  
  def down
    remove_index :encounters, name: 'idx_encounters_patient_type_date'
    remove_index :encounters, name: 'idx_encounters_clinic_score_date'
    remove_index :encounter_complaints, name: 'idx_complaints_patient_status_chronic'
    remove_index :encounter_complaints, name: 'idx_complaints_onset_status'
    remove_index :encounter_medicines, name: 'idx_medicines_patient_status_type'
    remove_index :encounter_medicines, name: 'idx_medicines_status_end_date'
    remove_index :encounter_medicines, name: 'idx_medicines_recon_status_date'
    remove_index :patient_health_scores, name: 'idx_health_scores_patient_date_score'
    remove_index :patient_health_summaries, name: 'idx_summaries_score_trend'
    remove_index :patient_health_summaries, name: 'idx_summaries_recalc_encounter_date'
  end
end

# Execute migrations in this order to avoid dependency issues:

1. create_clinical_codes.rb                        # Base lookup table
2. rename_careplans_to_encounters.rb              # Core table rename
3. enhance_encounter_complaints_for_lhr.rb        # Enhance complaints
4. enhance_encounter_medicines_for_lhr.rb         # Enhance medicines  
5. create_patient_health_scores.rb                # Health scoring
6. create_patient_health_summaries.rb             # Patient summaries
# 7. Removed: medication reconciliation table - using existing fields
8. create_clinical_code_mappings.rb               # Code mappings
9. add_lhr_constraints.rb                         # Data integrity
10. add_lhr_performance_indexes.rb                # Performance optimization

# Note: Each migration is designed to be safely rolled back if needed
# All migrations include proper indexes and constraints for production use

-- Estimated additional storage for LHR features (per 10,000 encounters):

-- Enhanced encounter_complaints: ~2MB additional
-- Enhanced encounter_medicines: ~3MB additional  
-- Patient health scores: ~5MB
-- Patient health summaries: ~2MB
-- Clinical codes: ~10MB (one-time, shared across all encounters)
-- Medication continuity: uses existing tables, no additional storage

-- Total estimated additional storage: ~25MB per 10,000 encounters
-- This is conservative - JSONB compression and efficient indexing reduce actual usage