class Maintenance::SchoolDeleteJob
  include Sidekiq::Worker

  def perform(id)
    school = School.find(id)
    school.destroy_directories
    purge_attachments(school)
    objects = [school]
    school.school_years.each { |y| objects << y }

    objects.each do |object|
      object.class.reflections.each do |association, reflection|
        dependent_delete(object, association, reflection) if association.match?(/^admission_/)
      end
    end

    clear_legacy_tables(school.id)

    nil
  end

  private
    def purge_attachments(school)
      school.facility_buildings.each { |b| b.photo.purge }
      school.facility_locations.each { |l| l.photo.purge }
      school.facility_rooms.each { |r| r.photo.purge }
      school.users.each { |u| u.reports.purge }
    end

    def recursive_delete(object)
      object.class.reflections.each do |association, reflection|
        dependent_delete(object, association, reflection)
      end

      object.delete
    end

    def dependent_delete(object, association, reflection)
      if reflection.is_a? ActiveRecord::Reflection::HasManyReflection
        object.public_send(association).each do |record|
          recursive_delete(record)
        end
      elsif reflection.is_a? ActiveRecord::Reflection::HasOneReflection
        record = object.public_send(association)
        recursive_delete(record) unless record.nil?
      end
    end

    def clear_legacy_tables(id)
      tables = []

      rows = ActiveRecord::Base.connection.execute(<<~SQL)
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = 'SchoolID'
        AND TABLE_SCHEMA = 'kidows'
      SQL
      rows.each { |row| tables << row.first }

      references = {}

      rows = ActiveRecord::Base.connection.execute(<<~SQL)
        SELECT DISTINCT REFERENCED_TABLE_NAME, TABLE_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_SCHEMA = 'kidows'
      SQL
      rows.each do |row|
        parent = row.first
        child = row.second
        next unless tables.include?(parent)
        next unless tables.include?(child)

        references[parent] = [] unless references.key?(parent)
        references[parent] << child
      end

      stack = references.keys
      until stack.empty?
        current = stack.last
        if references.include?(current)
          stack += references.delete(current)
        else
          delete_all_from(current, id) if tables.delete(current)
          stack.pop
        end
      end

      tables.each do |table|
        delete_all_from(table, id)
      end

      nil
    end

    def delete_all_from(table, id)
      ActiveRecord::Base.connection.execute(<<~SQL)
        DELETE FROM #{table}
        WHERE SchoolID = #{id}
      SQL
    end
end