#!/usr/bin/env ruby
#
# cleanup script for hermes database
#
# removes notifications in 3 passes:
#
# 1. remove every row from generated_notifications that was sent prior to 
#    the date set by -d (default 7 days ago)
# 2. remove every row from notification that has no corresponding
#    notification_id in generated_notifications
# 3. remove old starship messages
#

require 'optparse'
require 'ostruct'

options = OpenStruct.new
options.days = 7
options.starship_days = 30*1
options.env = "development"
options.slicelength = 10000

OptionParser.new do |opts|
  opts.banner = "Usage: script/cleanup [options]"

  opts.on("-d", "--days N", "number of days to keep (default: #{options.days})") do |days|
    options.days = days
  end

  opts.on("-t", "--starshipdays N", "number of days to keep starship messages (default: #{options.starship_days})") do |days|
    options.starship_days = days
  end

  opts.on("-e", "--env ENV", "rails environment to use (default: #{options.env})") do |env|
    options.env = env
  end

  opts.on("-s", "--slicelength LEN", "slice length (default: #{options.slicelength})") do |sl|
    options.slicelength = sl.to_i
  end

  opts.on_tail("-h", "--help", "show this message") do
    puts opts
    exit
  end
end.parse!

puts "Initializing #{options.env} environment..."
RAILS_ENV = options.env
require File.dirname(__FILE__) + '/../config/boot'
require RAILS_ROOT+"/config/environment"

RAILS_DEFAULT_LOGGER.silence do
delete_time = Time.now - options.days.to_i*60*60*24
slice_length = options.slicelength

####
# 1. Delete all GeneratedNotifications that exceed the maximum age

gen_not = GeneratedNotification.find :all, :select => :id, :conditions => ["sent < ? AND sent > ?", delete_time, "0000-00-00 00:00:00"]
gen_not.map! {|n| n.id}
@gncount = 0

puts "Deleting #{gen_not.length} generated notifications older than #{delete_time} in blocks of #{slice_length}..."
while( not (slice = gen_not.slice!(0,slice_length)).empty? )
  GeneratedNotification.delete slice
  @gncount += slice.length
  puts " #@gncount generated notifications deleted, #{gen_not.length} remaining."
end

####
# 2. Delete all Notifications that are older than maxage and not referenced
#    from a GeneratedNotification

notifications = Notification.find_by_sql <<-END_SQL
  SELECT n.id
  FROM notifications n
  LEFT OUTER JOIN generated_notifications gn
    ON n.id = gn.notification_id
  WHERE ISNULL(gn.notification_id)
  AND n.received < '#{delete_time.to_s(:db)}'
END_SQL
notifications.map! {|n| n.id}
@ncount = @npcount = 0

puts "Deleting #{notifications.length} raw notifications older than #{delete_time} in blocks of #{slice_length}..."
while( not (slice = notifications.slice!(0,slice_length)).empty? )
  Notification.delete slice
  @ncount += slice.length
  print "  #@ncount raw notifications deleted, #{notifications.length} remaining. Deleting notification parameters... "
  STDOUT.flush
  nc = NotificationParameter.delete_all :notification_id => slice
  @npcount += nc
  puts "#{nc} deleted."
end

puts "#@ncount raw notifications with #@npcount parameters deleted.\n\n"

####
# 3. Delete all StarshipMessages that exceed the maximum age

delete_time = Time.now - options.starship_days.to_i*60*60*24
gen_not = StarshipMessage.find :all, :select => :id, :conditions => ["created < ? ", delete_time]
gen_not.map! {|n| n.id}
@gncount = 0

puts "Deleting #{gen_not.length} starship messages older than #{delete_time} in blocks of #{slice_length}..."
while( not (slice = gen_not.slice!(0,slice_length)).empty? )
  StarshipMessage.delete slice
  @gncount += slice.length
  puts " #@gncount starship messages deleted, #{gen_not.length} remaining."
end


puts "\nOptimizing tables..."
ActiveRecord::Base.connection.execute "OPTIMIZE TABLE notifications"
puts "  notifications optimized."
ActiveRecord::Base.connection.execute "OPTIMIZE TABLE notification_parameters"
puts "  notification_parameters optimized."
ActiveRecord::Base.connection.execute "OPTIMIZE TABLE generated_notifications"
puts "  generated_notifications optimized."
ActiveRecord::Base.connection.execute "OPTIMIZE TABLE starship_messages"
puts "  starship_messages optimized."
puts "done."
end
