Foreign Keys To Add

    add_foreign_key "cavc_remands", "users", column: "created_by_id"
    add_foreign_key "cavc_remands", "users", column: "updated_by_id"
    add_foreign_key "virtual_hearings", "users", column: "created_by_id"

    add_foreign_key "tasks", "users", column: "assigned_by_id"
    add_foreign_key "tasks", "users", column: "cancelled_by_id"

    add_foreign_key "distributions", "users", column: "judge_id"
    add_foreign_key "hearings", "users", column: "judge_id"
    add_foreign_key "hearing_days", "users", column: "judge_id"
    add_foreign_key "sent_hearing_email_events", "users", column: "sent_by_id"

    add_foreign_key "judge_case_reviews", "users", column: "attorney_id"
    add_foreign_key "judge_case_reviews", "users", column: "judge_id"

CavcRemand.includes(:created_by).where.not(created_by_id: nil).select{|cr| cr.created_by == nil}.count
=> 0
CavcRemand.includes(:updated_by).where.not(updated_by_id: nil).select{|cr| cr.updated_by == nil}.count
=> 0
VirtualHearing.includes(:created_by).where.not(created_by_id: nil).select{|cr| cr.created_by == nil}.count
=> 0

Task.includes(:assigned_by).where.not(assigned_by_id: nil).select{|cr| cr.assigned_by == nil}.count
=> 0
Task.includes(:cancelled_by).where.not(cancelled_by_id: nil).select{|cr| cr.cancelled_by == nil}.count
=> 0

Distribution.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count
=> 0
Hearing.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count
=> 0
HearingDay.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count
=> 0
SentHearingEmailEvent.includes(:sent_by).where.not(sent_by_id: nil).select{|cr| cr.sent_by == nil}.count
=> 0

JudgeCaseReview.includes(:attorney).where.not(attorney_id: nil).select{|cr| cr.attorney == nil}.count
=> 0
JudgeCaseReview.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count
=> 0

    add_foreign_key "ihp_drafts", "organizations"
    add_foreign_key "organizations_users", "organizations"
    add_foreign_key "vso_configs", "organizations"    
    
    add_foreign_key "job_notes", "users"
    add_foreign_key "messages", "users"    

IhpDraft.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil}.count
=>0

ou=OrganizationsUser.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil};
ou.count
=>45
org_ids = ou.map(&:organization_id).uniq
=> [130, 139, 408, 163, 171, 190, 412, 320]
=> [130, 139, 408, 163, 171, 183, 190, 212, 412, 320] # Apr 12th

org_ids.count
=> 10
# Check inactive orgs
ou2=Organization.unscoped.find(org_ids).count
=> 10

ou.map(&:user).uniq.count
=>45
ou.map(&:user).map{|u| [u.id, u.station_id, u.css_id, u.status, u.last_login_at]}
ou.map{|r| [r.id, r.created_at, r.updated_at]}.sort_by{|r| r[2]}

OrganizationsUser.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count
=> 0

vc=VsoConfig.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil};
vc.count
=>3
org_ids=vc.map(&:organization_id).uniq
=> [237, 242, 259] 
# All VCs created 05 Mar 2019
orgs2=Organization.unscoped.where(id: org_ids)
=> []
VsoConfig.count
=> 82
# https://github.com/department-of-veterans-affairs/caseflow/blob/ded8b6ad58a5f034ecd1f3e6b0b551746f02d975/app/models/organizations/field_vso.rb#L15
# VsoConfig belongs only to FieldVso organizations.
# Since the FieldVso records no longer exists, these VsoConfig are useless and can be deleted:
vcs=VsoConfig.where(id: vc.pluck(:id))
vcs.destroy_all

# Save
filename="/tmp/VsoConfig_attribs.json"
file_contents = JSON.pretty_generate vc.map{|r| r.attributes}
File.open(filename.to_s, "w") { |file| file.puts file_contents }


JobNote.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count
=>0
Message.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count
=>0

    add_foreign_key "task_timers", "tasks"
    add_foreign_key "tasks", "tasks", column: "parent_id"
    add_foreign_key "hearing_task_associations", "tasks", column: "hearing_task_id"
    # Refers to the tasks table for AMA appeals, but something like `4107503-2021-05-31` for legacy appeals: add_foreign_key "judge_case_reviews", "tasks"

    add_foreign_key "distributed_cases", "tasks"
    add_foreign_key "distributed_cases", "distributions"

tt=TaskTimer.includes(:task).where.not(task_id: nil).select{|r| r.task == nil};
tt.count
=>7
tt.pluck(:id, :task_id, :processed_at, :error)
=> [[37, 50355, Fri, 15 Feb 2019 00:30:07 EST -05:00, nil],
 [38, 50357, Fri, 15 Feb 2019 00:30:08 EST -05:00, nil],
 [23, 50207, Fri, 15 Feb 2019 10:08:55 EST -05:00, nil],
 [56, 50567, Fri, 22 Feb 2019 00:30:05 EST -05:00, nil],
 [62, 50661, Wed, 27 Feb 2019 00:30:04 EST -05:00, nil],
 [32, 50339, Fri, 15 Feb 2019 00:30:03 EST -05:00, nil],
 [68, 50724, Thu, 28 Feb 2019 00:30:04 EST -05:00, nil]]
# All are processed. Can be deleted.
filename="/tmp/TaskTimer_attribs.json"
file_contents = JSON.pretty_generate tt.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }
tt_ids = tt.pluck(:id)
=> [37, 38, 23, 56, 62, 32, 68]
TaskTimer.where(id: tt_ids).destroy_all

tp_ids=Task.includes(:parent).where.not(parent_id: nil).select{|r| r.parent == nil}.pluck(:id);
# Takes a good while
tp_ids.count
=> 0

hta_ids=HearingTaskAssociation.includes(:hearing_task).where.not(hearing_task_id: nil).select{|r| r.hearing_task == nil}.pluck(:id);
hta_ids.count
=> 9
hta_ids.sort
=> [85, 254, 1082, 1688, 2587, 2947, 3900, 16697, 17900]
htas=HearingTaskAssociation.where(id: hta_ids)
#--
hta_ids2=HearingTaskAssociation.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil}.pluck(:id);
hta_ids2.count
=> 1
hta_ids2
=> [1082]
#--
filename="/tmp/HearingTaskAssociation_attribs.json"
file_contents = JSON.pretty_generate htas.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }
htas.delete_all

dc_ids=DistributedCase.includes(:task).where.not(task_id: nil).select{|r| r.task == nil}.pluck(:id);
dc_ids.count
=> 4
dc_ids
=> [1629, 99374, 109126, 28633]
dcs=DistributedCase.where(id: dc_ids)
dcs.pluck(:id, :case_id, :task_id)
=> [[1629, "34b45d8c-fe16-4c6e-b594-e0bc9e34a0d9", 75071],
 [28633, "967ddcf7-ff07-4ba6-b75b-e00755a37ddd-attempt1", 220040],
 [99374, "114a1577-1bce-4f64-9fc7-a5cec8f31057", 608057],
 [109126, "b0b12766-045e-4edd-82c9-8fcdfbcb08ee", 678322]]
# Appeals no longer exist for these 3
dcs.map{|dc| Appeal.find_by_uuid(dc.case_id.sub("-attempt1","")).nil?}
=> [true, false, true, true]
dc=DistributedCase.find(28633)
=> #<DistributedCase:0x00007f9427e4bd18
 id: 28633,
 distribution_id: 1506,
 case_id: "967ddcf7-ff07-4ba6-b75b-e00755a37ddd-attempt1",
 docket: "hearing",
 priority: false,
 genpop: true,
 genpop_query: "any",
 docket_index: nil,
 ready_at: Thu, 30 May 2019 09:04:29 EDT -04:00,
 task_id: 220040,
 created_at: nil,
 updated_at: Fri, 26 Jun 2020 10:55:10 EDT -04:00>
 
DistributedCase.find(1629).update_attribute('task_id', nil)
DistributedCase.find(99374).update_attribute('task_id', nil)
DistributedCase.find(109126).update_attribute('task_id', nil)
# Since case_id is not a valid reference, task is not useful
DistributedCase.find(28633).update_attribute('task_id', nil)

dc_ids2=DistributedCase.includes(:distribution).where.not(distribution_id: nil).select{|r| r.distribution == nil}.pluck(:id);
dc_ids2.count
=> 0

    add_foreign_key "cavc_remands", "appeals", column: "remand_appeal_id"
    add_foreign_key "cavc_remands", "appeals", column: "source_appeal_id"

CavcRemand.includes(:remand_appeal).where.not(remand_appeal_id: nil).select{|r| r.remand_appeal == nil}.count
=>0
CavcRemand.includes(:source_appeal).where.not(source_appeal_id: nil).select{|r| r.source_appeal == nil}.count
=>0

    add_foreign_key "hearing_issue_notes", "request_issues"
    add_foreign_key "hearing_issue_notes", "hearings"
    
    # See AppealStreamSnapshot -- add_foreign_key "hearing_appeal_stream_snapshots", "legacy_hearings", column: "hearing_id"
    
    add_foreign_key "hearings", "hearing_days"
    add_foreign_key "transcriptions", "hearings"
    add_foreign_key "virtual_hearing_establishments", "virtual_hearings"

hins=HearingIssueNote.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil};
hins.count
=> 1 # May 20th

hinhs=HearingIssueNote.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil};
hinhs.count
=> 1 # May 20th

[hins.first.id, hinhs.first.id]
=> [148, 148]

# Save
filename="/tmp/HearingIssueNote_attribs.json"
file_contents = JSON.pretty_generate hins.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }

# Delete
hins.first.delete


hhds=Hearing.includes(:hearing_day).where.not(hearing_day_id: nil).select{|r| r.hearing_day == nil};
hhds.count
=> 0


ths=Transcription.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil};
ths.count
=> 0

vhes=VirtualHearingEstablishment.includes(:virtual_hearing).where.not(virtual_hearing_id: nil).select{|r| r.virtual_hearing == nil};
vhes.count
=> 0

    add_foreign_key "dispatch_tasks", "legacy_appeals", column: "appeal_id"
    add_foreign_key "legacy_hearings", "legacy_appeals", column: "appeal_id"
    add_foreign_key "hearings", "appeals", column: "appeal_id"

dts=Dispatch::Task.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil};
dts.count
# takes 20 seconds
=> 5
dts.pluck(:id, :type, :appeal_id, :aasm_state, :completion_status, :completed_at)
=> [[1139, "EstablishClaim", 21568, "completed", "invalidated", nil],
 [1741, "EstablishClaim", 23716, "completed", "expired", nil],
 [14935, "EstablishClaim", 24336, "completed", "invalidated", nil],
 [44536, "EstablishClaim", 145063, "completed", "invalidated", nil],
 [83981, "EstablishClaim", 357772, "completed", "routed_to_arc", Thu, 04 Oct 2018 10:08:14 EDT -04:00]]

# Backup
filename="/tmp/Dispatch_Task_attribs.json"
file_contents = JSON.pretty_generate dts.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }
# Deleting
dts.map(&:delete)

lhs=LegacyHearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil};
lhs.count
=> 15
=> 14 # Apr 13th
filename="/tmp/LegacyHearing_attribs.json"
file_contents = JSON.pretty_generate lhs.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }

lh_ids=LegacyHearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.pluck :id
=> [10942, 12633, 13925, 18661, 20198, 26147, 38200, 40532, 45040, 47428, 51277, 102441, 103706, 150368]
lh_ids.count
=> 14
LegacyHearing.where(id: lh_ids).delete_all
=> 14


hs=Hearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil};
hs.count
=> 0

    add_foreign_key "legacy_issue_optins", "request_issues"
    add_foreign_key "legacy_issues", "request_issues"
    add_foreign_key "request_decision_issues", "request_issues"
    add_foreign_key "request_decision_issues", "decision_issues"
    
    add_foreign_key "request_issues", "request_issues", column: "corrected_by_request_issue_id"
    add_foreign_key "request_issues", "request_issues", column: "ineligible_due_to_id"

lio=LegacyIssueOptin.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil};
lio.count
=> 0

LegacyIssue.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}.pluck(:id)
=> []

# To prevent PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
ActiveRecord::Base.connection.execute   "SET statement_timeout = 1800000"
# Don't show extremely long SQL statement
ActiveRecord::Base.logger.level = :warn

RequestDecisionIssue.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}.pluck(:id)
=> []

RequestDecisionIssue.includes(:decision_issue).where.not(decision_issue_id: nil).select{|r| r.decision_issue == nil}.pluck(:id)
=> []

RequestIssue.includes(:correction_request_issue).where.not(corrected_by_request_issue_id: nil).select{|r| r.correction_request_issue == nil}.pluck(:id)
=> []

RequestIssue.includes(:ineligible_due_to).where.not(ineligible_due_to_id: nil).select{|r| r.ineligible_due_to == nil}.pluck(:id)
=> [2799, 2800, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 10300, 43613, 43614]
# Sally says these can be deleted: https://dsva.slack.com/archives/C54QCEHAL/p1618360956115700?thread_ts=1618357083.114200&cid=C54QCEHAL
# Wednesday, April 28
=> [2799, 2800, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 10300, 43613, 43614]
# Tuesday, May 4 - ineligible_due_to_id has been removed from all records above
=> []

add_foreign_key "request_issues", "decision_issues", column: "contested_decision_issue_id"
    
add_foreign_key "request_issues", "end_product_establishments"

RequestIssue.includes(:contested_decision_issue).where.not(contested_decision_issue_id: nil).select{|r| r.contested_decision_issue == nil}.pluck(:id)
=> [972638, 1932358, 5183, 972639, 1591821, 1758654, 1758655]
# Sally fixed a few
# Wednesday, April 28
=> [5183, 972639, 1758655, 972638, 1932358, 1591821, 1758654]
# 2 more have popped up: ACTION: identify root cause; if it's in our code, we need to fix it before deploying FK
# Tuesday, June 1
=> []
# Caused by manual updates made outside of Casflow


RequestIssue.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.pluck(:id)
# Long runtime ...
[766681, 767166, 767171, 767172, 767534, 767540, 767541, 768069, 780817, 780820, 780949, 780950, 780951, 780955, 780956, 780957, 780958, 780959, 780960, 780961, 780963, 780964, 780965, 780966, 780967, 780968, 780969, 780974, 780975, 782647, 784263, 784266, 784336, 784339, 784907, 784993, 785162, 785239, 767542, 780952, 780962, 780954]
# Wednesday, April 28
[766681, 767166, 767171, 767172, 767534, 767540, 767541, 768069, 780817, 780820, 780949, 780950, 780951, 780955, 780956, 780957, 780958, 780959, 780960, 780961, 780963, 780964, 780965, 780966, 780967, 780968, 780969, 780974, 780975, 782647, 784263, 784266, 784336, 784339, 784907, 784993, 785162, 785239, 767542, 780952, 780962, 780954]
# Will: almost positive these can be deleted; hasn't happened in 16 months
# We can create a separate PR for this one FK, in case we need to revert
# Ask Sally: If we added a FK, what would break?
# ACTION: why is this happening? 
# Tuesday, June 1
=> []
# Caused by bug, fix has been made

  add_foreign_key "end_product_code_updates", "end_product_establishments"

EndProductCodeUpdate.unscoped.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.pluck(:id)
=> [20717, 21139, 21654, 42451, 23784]
# Wednesday, April 28
=> [20717, 21139, 21654, 42451, 23784]
# Wednesday, June 2
=> []
# Safe to delete. Useless without an existing End Product Establishment

EndProductCodeUpdate.count
=> 25443
# Wednesday, April 28
=> 25872
# Asked Foxtrot: https://dsva.slack.com/archives/C54QCEHAL/p1618405353117800

  
    add_foreign_key "remand_reasons", "decision_issues"

# To prevent PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
ActiveRecord::Base.connection.execute   "SET statement_timeout = 1800000"
# Don't show extremely long SQL statement
ActiveRecord::Base.logger.level = :warn

RemandReason.includes(:decision_issue).where.not(decision_issue_id: nil).select{|r| r.decision_issue == nil}.pluck(:id).count
=> 35994  # Yikes!
# Wednesday, April 28
=> 36831

RemandReason.count
=> 67118
# Wednesday, April 28
=> 68593

    add_foreign_key "docket_tracers", "docket_snapshots"
    add_foreign_key "non_availabilities", "schedule_periods"
    add_foreign_key "user_quotas", "team_quotas"
    add_foreign_key "ramp_election_rollbacks", "ramp_elections"

DocketTracer.unscoped.includes(:docket_snapshot).where.not(docket_snapshot_id: nil).select{|r| r.docket_snapshot == nil}.pluck(:id)
=> [] #Prod
=> [] #Preprod
=> [] #UAT

NonAvailability.unscoped.includes(:schedule_period).where.not(schedule_period_id: nil).select{|r| r.schedule_period == nil}.pluck(:id)
=> [] #Prod
=> [] #Preprod
=> [] #UAT

UserQuota.unscoped.includes(:team_quota).where.not(team_quota_id: nil).select{|r| r.team_quota == nil}.pluck(:id)
=> [] #Prod
=> [] #Preprod
=> [] #UAT

RampElectionRollback.unscoped.includes(:ramp_election).where.not(ramp_election_id: nil).select{|r| r.ramp_election == nil}.pluck(:id)
=> [5, 8, 17, 21, 22, 35, 45, 48] #Prod
=> [] #Preprod
=> [1, 2, 6, 3, 4, 5] #UAT
# All records in Prod were created in 2018 and appeals that were rolled back have since been opted in and closed. Safe to delete
# All records in UAT were created in 2018 and do not correspond to an existing appeal. Safe to delete

    add_foreign_key "document_views", "documents"
    add_foreign_key "documents_tags", "documents"
    add_foreign_key "documents_tags", "tags"

DocumentView.unscoped.includes(:document).where.not(document_id: nil).select{|r| r.document == nil}.pluck(:id)

DocumentTag.unscoped.includes(:document).where.not(document_id: nil).select{|r| r.document == nil}.pluck(:id)

DocumentTag.unscoped.includes(:tag).where.not(tag_id: nil).select{|r| r.tag == nil}.pluck(:id)

    # ignore polymorphic: add_foreign_key "special_issue_lists", "appeals"
    
    # ignore polymorphic: add_foreign_key "vbms_uploaded_documents", "appeals"
    
    add_foreign_key "hearing_appeal_stream_snapshots", "legacy_appeals", column: "appeal_id"
    add_foreign_key "hearing_appeal_stream_snapshots", "legacy_hearings", column: "hearing_id"

SpecialIssueList.count
=> 210796
sil_ids=SpecialIssueList.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.pluck(:id)
sil_ids.count
# takes about 10 seconds
=> 128
sil_ids.min
=> 1
sil_ids.max
=> 130
sil = SpecialIssueList.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil};
# Backup records
filename="/tmp/SpecialIssueList_attribs.json"
file_contents = JSON.pretty_generate sil.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }

# Can we fix them?
sil.pluck(:id, :appeal_id, :appeal_type, :created_at, :updated_at)
sil.pluck(:id, :appeal_id, :appeal_type).min
=> [1, 154, nil]
sil.pluck(:id, :appeal_id, :appeal_type).max
=> [130, 162, nil]
SpecialIssueList.where(id: [131..150]).pluck(:id, :appeal_id, :appeal_type)
# appeal_ids are high and are for LegacyAppeals
SpecialIssueList.where(id: [131..150]).pluck(:appeal_id, :appeal_type).min
=> [5779, "LegacyAppeal"]
SpecialIssueList.where(id: [131..150]).pluck(:appeal_id, :appeal_type).max
=> [488997, "LegacyAppeal"]
# I'm guessing the problematic sils are AMA appeals
# Find the first sils with AMA appeals
SpecialIssueList.where(id: [150..500], appeal_type: "Appeal").pluck(:appeal_id, :appeal_type).min
=> [564, "Appeal"]

# Check if existing SIL maps to any of the appeal_id
sil_appeal_ids = sil.map(&:appeal_id)
sil_appeal_ids.count
=> 128
SpecialIssueList.where(appeal_id: sil_appeal_ids, appeal_type: "Appeal")
=> []
SpecialIssueList.where(appeal_id: sil_appeal_ids, appeal_type: "LegacyAppeal").count
=> 43
# SILs for legacies already exist, so let's set appeal_type to "Appeal"
sil.map{|s| s.update(appeal_type: "Appeal")}
sil.select{|s| s.reload.appeal == nil}
=> []

sil.map{|s| [s.id, s.attributes.select{|k,v| v==true}]}
sil.select{|s| s.attributes.select{|k,v| v==true}.size>0}.map(&:id).count
=> 50
# Only 50 have some true boolean; the 78 others don't have useful info
# ALL FIXED


VbmsUploadedDocument.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count
=> 21
vdoc=VbmsUploadedDocument.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}
vdoc.count
=> 102 # April 29
=> 145 # May 20
# Backup
filename="/tmp/VbmsUploadedDocument_attribs.json"
file_contents = JSON.pretty_generate vdoc.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }

vdoc.pluck(:id, :document_type, :error, :processed_at, :appeal_id, :appeal_type)
p=vdoc.partition{|v| v.appeal_type=="Appeal"}
p.map(&:count)
=> [2, 100] # 2 are AMA; the rest are legacies
# The purpose of the record is to track document uploads to VBMS and is not used afterwards.
vdoc.pluck(:processed_at, :id, :document_type, :error, :appeal_id, :appeal_type).max
=> [Mon, 19 Apr 2021 07:26:23 EDT -04:00, 95438, "AMA Notification Letter", nil, 1391381, "LegacyAppeal"]
# Should investigate why these are going missing.
# Waiting to delete them? VbmsUploadedDocument.where(id: vdoc.pluck(:id)).delete_all



ass=AppealStreamSnapshot.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}
ass.count
=> 12
assh=AppealStreamSnapshot.unscoped.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil}
assh.count
=> 6
assh.count
=> 15 # Apr 29th
# Asked Tango: https://dsva.slack.com/archives/C3EAF3Q15/p1619720929484500

# Save
ass2=ass+assh
ass2.count
=> 27
filename="/tmp/AppealStreamSnapshot_attribs.json"
file_contents = JSON.pretty_generate ass2.map{|r| r.attributes};
File.open(filename.to_s, "w") { |file| file.puts file_contents }
# These always refer to LegacyAppeals (and LegacyHearings), which may be associated with deleted cases in VACOLS
# Adding a FK for this may present errors for the user!
# Can be deleted b/c they'll be recreated: https://dsva.slack.com/archives/C3EAF3Q15/p1619800095001300?thread_ts=1619720929.484500&cid=C3EAF3Q15

add_foreign_key "board_grant_effectuations", "appeals"

add_foreign_key "board_grant_effectuations", "decision_documents"

add_foreign_key "board_grant_effectuations", "end_product_establishments"

add_foreign_key "board_grant_effectuations", "decision_issues", column: "granted_decision_issue_id"

BoardGrantEffectuation.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count
=> 0 #Prod
=> 0 #UAT
=> 0 #Preprod

BoardGrantEffectuation.unscoped.includes(:decision_document).where.not(decision_document_id: nil).select{|r| r.decision_document == nil}.count
=> 0 #Prod
=> 0 #UAT
=> 0 #Preprod

BoardGrantEffectuation.unscoped.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.count
=> 0 #Prod
=> 0 #UAT
=> 0 #Preprod

BoardGrantEffectuation.unscoped.includes(:granted_decision_issue).where.not(granted_decision_issue_id: nil).select{|r| r.granted_decision_issue == nil}.count
=> 0 #Prod
=> 0 #UAT
=> 0 #Preprod

add_foreign_key "attorney_case_reviews", "users", column: "attorney_id"
add_foreign_key "attorney_case_reviews", "users", column: "reviewing_judge_id"

AttorneyCaseReview.unscoped.includes(:attorney).where.not(attorney_id: nil).select{|r| r.attorney == nil}.count
=> 0 #Prod
=> 0 #Preprod
=> 0 #UAT

AttorneyCaseReview.unscoped.includes(:reviewing_judge).where.not(reviewing_judge_id: nil).select{|r| r.reviewing_judge == nil}.count
=> 0 #Prod
=> 0 #Preprod
=> 0 #UAT

# Add FK to clumn file_number in veterans table
add_foreign_key "available_hearing_locations", "veterans", column_id: "veteran_file_number"
AvailableHearingLocations.unscoped.includes(:veteran).where.not(veteran_file_number: nil).select{|r| r.veteran == nil}.count
=> 429,482 #Prod. All records. Appears to be an issue with the ActiveRecord relationship
=> 0 #Preprod. No records
=> 244 #UAT. All records


# Add FK to legacy_appeals
add_foreign_key "worksheet_issues", "appeals"
WorksheetIssue.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count
=> 65 #Prod
=> 23 #Preprod
=> 3,716 #UAT

# Add FK to dispatch_tasks table (not the tasks table)
add_foreign_key "claim_establishments", "tasks"
ClaimEstablishment.unscoped.includes(:task).where.not(task_id: nil).select{|r| r.task == nil}.count
=> 7 #Prod
=> 924 #Preprod
=> 450,231 #UAT

# Investigate these next and add foreign key if possible.
add_foreign_key "advance_on_docket_motions", "person_id"
add_foreign_key "ramp_issues", "source_issue_id"
add_foreign_key "remand_reasons", "decision_issue_id"
add_foreign_key "certification_cancellations", "certification_id"

# The documents table is extremely large -- investigate these later
add_foreign_key "annotations", "document_id"
add_foreign_key "document_views", "document_id"
add_foreign_key "documents_tags", "document_id"
add_foreign_key "documents_tags", "tag_id"

add_foreign_key "sent_hearing_email_events", "hearing_id"
add_foreign_key "hearing_email_recipients", "hearing_id"
add_foreign_key "special_issue_lists", "appeal_id"
add_foreign_key "tasks", "appeal_id"
add_foreign_key "vbms_uploaded_documents", "appeal_id"

add_foreign_key "judge_case_reviews", "task_id"
add_foreign_key "attorney_case_reviews", "task_id"

# Don't need FKs on a cache table
add_foreign_key "cached_appeal_attributes", "appeal_id"
add_foreign_key "cached_appeal_attributes", "vacols_id"