Phase 1: Patient Core Implementation Plan

// address
{
  "line1": "123 Main Street",
  "line2": "Apt 4B",
  "city": "Bangalore",
  "state": "Karnataka",
  "postalCode": "560001",
  "country": "IN"
}

// emergency_contact
{
  "name": "Jane Doe",
  "relationship": "spouse",
  "phone": "+919876543210"
}

// communication_preferences
{
  "preferred_language": "en",
  "sms_preferred": true,
  "email_preferred": false
}

// identifiers
[
  { "system": "insurance", "value": "INS123456" },
  { "system": "employer", "value": "EMP789" }
]

CREATE TYPE clinical.subscriber_relationship AS ENUM ('self', 'spouse', 'child', 'other');
CREATE TYPE clinical.coverage_type AS ENUM ('primary', 'secondary', 'tertiary');

{
  "plan_name": "Gold PPO",
  "plan_type": "PPO",
  "in_network_coverage": 80,
  "out_of_network_coverage": 60,
  "max_annual_benefit": 100000,
  "notes": "Pre-authorization required for specialist visits"
}

# frozen_string_literal: true

class CreateClinicalEnums < ActiveRecord::Migration[8.0]
  def up
    execute "SET search_path TO clinical, public"

    execute <<-SQL
      DO $$ BEGIN
        CREATE TYPE clinical.patient_gender AS ENUM ('male', 'female', 'other', 'unknown');
      EXCEPTION
        WHEN duplicate_object THEN null;
      END $$;
    SQL

    execute <<-SQL
      DO $$ BEGIN
        CREATE TYPE clinical.marital_status AS ENUM (
          'single', 'married', 'divorced', 'widowed', 'separated', 'unknown'
        );
      EXCEPTION
        WHEN duplicate_object THEN null;
      END $$;
    SQL

    execute <<-SQL
      DO $$ BEGIN
        CREATE TYPE clinical.subscriber_relationship AS ENUM ('self', 'spouse', 'child', 'other');
      EXCEPTION
        WHEN duplicate_object THEN null;
      END $$;
    SQL

    execute <<-SQL
      DO $$ BEGIN
        CREATE TYPE clinical.coverage_type AS ENUM ('primary', 'secondary', 'tertiary');
      EXCEPTION
        WHEN duplicate_object THEN null;
      END $$;
    SQL

    execute "SET search_path TO public"
    say "Created clinical domain enums", true
  end

  def down
    execute "DROP TYPE IF EXISTS clinical.coverage_type CASCADE"
    execute "DROP TYPE IF EXISTS clinical.subscriber_relationship CASCADE"
    execute "DROP TYPE IF EXISTS clinical.marital_status CASCADE"
    execute "DROP TYPE IF EXISTS clinical.patient_gender CASCADE"
    say "Dropped clinical domain enums", true
  end
end

# frozen_string_literal: true

class CreateClinicalPatients < ActiveRecord::Migration[8.0]
  UUIDV7_DEFAULT = -> { 'generate_uuidv7()' }

  def up
    execute "SET search_path TO clinical, public"

    create_table :patients, id: false do |t|
      t.uuid :id, primary_key: true, null: false, default: UUIDV7_DEFAULT
      t.uuid :tenant_id, null: false
      t.string :mrn, limit: 50
      t.string :abha_id, limit: 50
      t.string :first_name, limit: 100, null: false
      t.string :middle_name, limit: 100
      t.string :last_name, limit: 100
      t.string :display_name, limit: 200
      t.column :gender, :patient_gender
      t.date :birth_date
      t.boolean :deceased, default: false, null: false
      t.date :deceased_date
      t.column :marital_status, :marital_status
      t.string :photo_url, limit: 500
      t.string :email, limit: 255
      t.string :phone_country_code, limit: 5
      t.string :phone_number, limit: 20
      t.jsonb :address, default: {}
      t.jsonb :emergency_contact, default: {}
      t.jsonb :communication_preferences, default: {}
      t.jsonb :identifiers, default: []
      t.boolean :is_active, default: true, null: false
      t.timestamps
    end

    execute <<-SQL
      ALTER TABLE clinical.patients
      ADD CONSTRAINT fk_patients_tenant
      FOREIGN KEY (tenant_id) REFERENCES identity.tenants(id);
    SQL

    # Composite unique index for FK references from child tables
    add_index :patients, %i[tenant_id id], unique: true, name: 'idx_cl_patients_tenant_id'
    add_index :patients, :tenant_id, name: 'idx_cl_patients_tenant'
    add_index :patients, %i[tenant_id mrn], unique: true,
              where: "mrn IS NOT NULL", name: 'idx_cl_patients_tenant_mrn'
    add_index :patients, %i[tenant_id abha_id], unique: true,
              where: "abha_id IS NOT NULL", name: 'idx_cl_patients_tenant_abha'
    add_index :patients, "lower(first_name), lower(last_name)", name: 'idx_cl_patients_name'
    add_index :patients, "lower(display_name)", where: "display_name IS NOT NULL", name: 'idx_cl_patients_display_name'
    add_index :patients, :phone_number, where: "phone_number IS NOT NULL", name: 'idx_cl_patients_phone'
    add_index :patients, :is_active, where: "is_active = true", name: 'idx_cl_patients_active'
    add_index :patients, :birth_date, name: 'idx_cl_patients_dob'

    # CHECK constraints for data integrity
    execute <<-SQL
      ALTER TABLE clinical.patients
      ADD CONSTRAINT chk_patients_deceased_date
      CHECK (deceased_date IS NULL OR deceased = true);
    SQL

    execute <<-SQL
      ALTER TABLE clinical.patients
      ADD CONSTRAINT chk_patients_deceased_after_birth
      CHECK (deceased_date IS NULL OR birth_date IS NULL OR deceased_date >= birth_date);
    SQL

    execute "SET search_path TO public"
    say "Created clinical.patients table", true
  end

  def down
    execute "SET search_path TO clinical, public"
    drop_table :patients, if_exists: true
    execute "SET search_path TO public"
    say "Dropped clinical.patients table", true
  end
end

# frozen_string_literal: true

class CreateClinicalPatientFacilities < ActiveRecord::Migration[8.0]
  UUIDV7_DEFAULT = -> { 'generate_uuidv7()' }

  def up
    execute "SET search_path TO clinical, public"

    create_table :patient_facilities, id: false do |t|
      t.uuid :id, primary_key: true, null: false, default: UUIDV7_DEFAULT
      t.uuid :tenant_id, null: false
      t.uuid :patient_id, null: false
      t.uuid :facility_id, null: false
      t.string :facility_mrn, limit: 50
      t.date :registration_date
      t.boolean :is_primary, default: false, null: false
      t.boolean :is_active, default: true, null: false
      t.timestamps
    end

    # Simple FK to tenants
    execute <<-SQL
      ALTER TABLE clinical.patient_facilities
      ADD CONSTRAINT fk_pf_tenant FOREIGN KEY (tenant_id) REFERENCES identity.tenants(id);
    SQL

    # Composite FK to patients (ensures tenant consistency)
    execute <<-SQL
      ALTER TABLE clinical.patient_facilities
      ADD CONSTRAINT fk_pf_patient FOREIGN KEY (tenant_id, patient_id)
      REFERENCES clinical.patients(tenant_id, id);
    SQL

    # Composite FK to facilities (ensures tenant consistency)
    execute <<-SQL
      ALTER TABLE clinical.patient_facilities
      ADD CONSTRAINT fk_pf_facility FOREIGN KEY (tenant_id, facility_id)
      REFERENCES identity.facilities(tenant_id, id);
    SQL

    add_index :patient_facilities, :tenant_id, name: 'idx_cl_pf_tenant'
    add_index :patient_facilities, :patient_id, name: 'idx_cl_pf_patient'
    add_index :patient_facilities, :facility_id, name: 'idx_cl_pf_facility'
    add_index :patient_facilities, %i[tenant_id patient_id facility_id], unique: true,
              name: 'idx_cl_pf_tenant_patient_facility'
    add_index :patient_facilities, %i[tenant_id facility_id facility_mrn], unique: true,
              where: "facility_mrn IS NOT NULL", name: 'idx_cl_pf_facility_mrn'
    # Partial unique index: only one primary facility per patient
    add_index :patient_facilities, %i[tenant_id patient_id], unique: true,
              where: "is_primary = true", name: 'idx_cl_pf_primary_unique'

    execute "SET search_path TO public"
    say "Created clinical.patient_facilities table", true
  end

  def down
    execute "SET search_path TO clinical, public"
    drop_table :patient_facilities, if_exists: true
    execute "SET search_path TO public"
    say "Dropped clinical.patient_facilities table", true
  end
end

# frozen_string_literal: true

class CreateClinicalPatientInsuranceCoverages < ActiveRecord::Migration[8.0]
  UUIDV7_DEFAULT = -> { 'generate_uuidv7()' }

  def up
    execute "SET search_path TO clinical, public"

    create_table :patient_insurance_coverages, id: false do |t|
      t.uuid :id, primary_key: true, null: false, default: UUIDV7_DEFAULT
      t.uuid :tenant_id, null: false
      t.uuid :patient_id, null: false
      t.uuid :insurance_plan_id
      t.string :policy_number, limit: 100, null: false
      t.string :group_number, limit: 100
      t.string :subscriber_id, limit: 100
      t.string :subscriber_name, limit: 200
      t.column :relationship_to_subscriber, :subscriber_relationship, null: false
      t.column :coverage_type, :coverage_type, null: false
      t.string :payer_name, limit: 200
      t.string :payer_type, limit: 50
      t.date :effective_date
      t.date :termination_date
      t.decimal :copay_amount, precision: 10, scale: 2
      t.decimal :deductible_amount, precision: 10, scale: 2
      t.jsonb :coverage_details, default: {}
      t.boolean :is_verified, default: false, null: false
      t.datetime :verified_at
      t.boolean :is_active, default: true, null: false
      t.timestamps
    end

    # Simple FK to tenants
    execute <<-SQL
      ALTER TABLE clinical.patient_insurance_coverages
      ADD CONSTRAINT fk_pic_tenant FOREIGN KEY (tenant_id) REFERENCES identity.tenants(id);
    SQL

    # Composite FK to patients (ensures tenant consistency)
    execute <<-SQL
      ALTER TABLE clinical.patient_insurance_coverages
      ADD CONSTRAINT fk_pic_patient FOREIGN KEY (tenant_id, patient_id)
      REFERENCES clinical.patients(tenant_id, id);
    SQL

    # CHECK constraint for date validity
    execute <<-SQL
      ALTER TABLE clinical.patient_insurance_coverages
      ADD CONSTRAINT chk_pic_dates
      CHECK (termination_date IS NULL OR effective_date IS NULL OR termination_date >= effective_date);
    SQL

    add_index :patient_insurance_coverages, :tenant_id, name: 'idx_cl_pic_tenant'
    add_index :patient_insurance_coverages, :patient_id, name: 'idx_cl_pic_patient'
    add_index :patient_insurance_coverages, :policy_number, name: 'idx_cl_pic_policy'
    # Partial unique: only one ACTIVE coverage per type per patient (allows history)
    add_index :patient_insurance_coverages, %i[tenant_id patient_id coverage_type], unique: true,
              where: "is_active = true", name: 'idx_cl_pic_active_unique'
    add_index :patient_insurance_coverages, %i[effective_date termination_date], name: 'idx_cl_pic_effective'

    execute "SET search_path TO public"
    say "Created clinical.patient_insurance_coverages table", true
  end

  def down
    execute "SET search_path TO clinical, public"
    drop_table :patient_insurance_coverages, if_exists: true
    execute "SET search_path TO public"
    say "Dropped clinical.patient_insurance_coverages table", true
  end
end

# frozen_string_literal: true

class SetupClinicalRls < ActiveRecord::Migration[8.0]
  TENANT_SCOPED_TABLES = %w[patients patient_facilities patient_insurance_coverages].freeze

  def up
    execute "SET search_path TO clinical, public"

    TENANT_SCOPED_TABLES.each do |table|
      execute "ALTER TABLE clinical.#{table} ENABLE ROW LEVEL SECURITY;"
      execute "ALTER TABLE clinical.#{table} FORCE ROW LEVEL SECURITY;"

      execute <<-SQL
        CREATE POLICY tenant_isolation ON clinical.#{table}
          FOR ALL
          USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
          WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);
      SQL
    end

    execute "SET search_path TO public"
    say "Set up clinical schema RLS policies", true
  end

  def down
    execute "SET search_path TO clinical, public"

    TENANT_SCOPED_TABLES.each do |table|
      execute "DROP POLICY IF EXISTS tenant_isolation ON clinical.#{table}"
      execute "ALTER TABLE clinical.#{table} DISABLE ROW LEVEL SECURITY"
    end

    execute "SET search_path TO public"
    say "Removed clinical schema RLS policies", true
  end
end

Rails.application.routes.draw do
  scope :clinical, module: 'clinical' do
    resources :patients do
      collection do
        get :search
      end
      member do
        get :facilities
        get :insurance_coverages
      end
    end

    resources :patient_facilities, only: %i[index create show update destroy]

    resources :patient_insurance_coverages, only: %i[index create show update destroy] do
      member do
        post :verify
      end
    end
  end
end